presto VS clickhouse查询性能对比 clickhouse和presto

众所周知，MySQL 的用户群体很大，为了能够增强数据的实时性，很多解决方案会利用 binlog 将数据写入到 ClickHouse。

为了能够监听 binlog 事件，我们需要用到类似 canal 这样的第三方中间件，这无疑增加了系统的复杂度。

在不久的将来，这一现状可能会发生改观。因为目前，编号 10851 的 PR 进入了 review 阶段。

(https://github.com/ClickHouse/ClickHouse/pull/10851)

该 PR 将为 ClickHouse 带来原生消费 binlog 日志的能力，是不是王炸功能?

这次是新增了一个名为 MaterializeMySQL 的 database 引擎，该 database 能映射到 MySQL 中的某个 database，并自动在 ClickHouse 中创建对应的 ReplacingMergeTree。

MaterializeMySQL 同时支持全量和增量同步，在 database 创建之初会全量同步 MySQL 中的表和数据，之后则会通过 binlog 进行增量同步。

MaterializeMySQL database 为其所创建的每张 ReplacingMergeTree 自动增加了 _sign 和 _version 字段。

其中， _version 用作 ReplacingMergeTree 的 ver 版本参数，每当监听到 insert、update 和 delete 事件时，在 databse 内全局自增。而 _sign 则用于标记是否被删除，取值 1 或者 -1。

目前 MaterializeMySQL 支持如下几种 binlog 事件:

MYSQL_WRITE_ROWS_EVENT

_sign = 1，_version ++

MYSQL_DELETE_ROWS_EVENT

_sign = -1，_version ++

MYSQL_UPDATE_ROWS_EVENT

新数据 _sign = 1

MYSQL_QUERY_EVENT

支持 CREATE TABLE 、DROP TABLE 、RENAME TABLE等。

虽然该 PR 目前还没有被 merge 到主线，但是我已忍不住想要尝鲜，接下来就让我们一睹它的芳容吧。

首先准备一个 MySQL 实例

#拉取镜像
 docker pull mysql:5.7.31
 
 #运行镜像
 docker run -p 3306:3306 --name mysql5.7 -v {your-path}/mysql/conf:/etc/mysql -v {your-path}/mysql/logs:/logs -v {your-path}/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7.31

确保 MySQL 开启了 binlog 功能，且格式为 ROW

#conf/my.cnf
server-id=1 


# Uncomment the following if you want to log updates  
log-bin=mysql-bin


# binary logging format - mixed recommended  
#binlog_format=mixed  
binlog_format=ROW

现在开始测试，首先在 MySQL 中创建数据表并写入数据

CREATE TABLE `t_organization` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` int NOT NULL,
  `name` text DEFAULT NULL,
  `updatetime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`code`)
) ENGINE=InnoDB;


INSERT INTO t_organization (code, name,updatetime) VALUES(1000,'Realinsight',NOW());
INSERT INTO t_organization (code, name,updatetime) VALUES(1001,'Realindex',NOW());
INSERT INTO t_organization (code, name,updatetime) VALUES(1002,'EDT',NOW());

继续创建第二张表

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` int,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


INSERT INTO t_test (code) VALUES(1);

现在轮到 ClickHouse 这边了，创建  MaterializeMySQL 数据库

CREATE DATABASE test_binlog ENGINE = MaterializeMySQL('127.0.0.1:3306','htap','root','root');

其中 4 个参数分别是 MySQL地址、databse、username 和 password。

执行之后可以观察一下它执行日志

2020.07.2020.07.29 01:29:53.571991 [ 868116 ] {} <Debug> executeQuery: (internal) /*Materialize MySQL step 1: execute MySQL DDL for dump data*/ EXTERNAL DDL FROM MySQL(test_binlog, htap) CREATE TABLE `t_organization` ( `id` int(11) NOT NULL AUTO_INCREMENT, `code` int(11) NOT NULL, `name` text, `updatetime` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `code` (`code`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
2020.07.29 01:29:53.577595 [ 868116 ] {} <Debug> executeQuery: (internal) /* Rewritten MySQL DDL Query */ CREATE TABLE test_binlog.t_organization (`id` Int32, `code` Int32, `name` Nullable(String), `updatetime` Nullable(DateTime), `_sign` Int8, `_version` UInt64) ENGINE = ReplacingMergeTree(_version) PARTITION BY intDiv(id, 4294967) ORDER BY (code, id)

可以看到，ClickHouse dump 出了 MySQL 的表结构，并将其转换成了 ReplacingMergeTree。

在这个过程中，不仅各字段的数据类型进行了映射，还多出了 _sign 和 _version 两个字段。

MySQL 表的 PRIMARY KEY 作为了 ReplacingMergeTree 的 PARTITION BY，并且按照类型大小除以1000整除; 

MySQL 表的  PRIMARY KEY 同时也作为了 ReplacingMergeTree 的 ORDER BY，如果 MySQL 表中还有 UNIQUE KEY (此例中的 code 字段)，它也会一同被加入到 ORDER BY。

现在我们查询 ClickHouse 的 test_binlog 数据库

use test_binlog;


show tables;


┌─name───────────┐
│ t_organization │
│ t_user         │
└────────────────┘

MySQL 的表已经被同步过来了，接着试试查询数据

select * from t_organization;


SELECT *
FROM t_organization


┌─id─┬─code─┬─name────────┬──────────updatetime─┐
│  1 │ 1000 │ Realinsight │ 2020-07-28 17:29:47 │
└────┴──────┴─────────────┴─────────────────────┘
┌─id─┬─code─┬─name──────┬──────────updatetime─┐
│  2 │ 1001 │ Realindex │ 2020-07-28 17:29:48 │
└────┴──────┴───────────┴─────────────────────┘
┌─id─┬─code─┬─name─┬──────────updatetime─┐
│  3 │ 1002 │ EDT  │ 2020-07-28 17:29:49 │
└────┴──────┴──────┴─────────────────────┘


3 rows in set. Elapsed: 0.032 sec.

接下来进一步测试 binlog 的同步功能。

首先在 MySQL 中修改数据:

update t_organization set name = CONCAT(name,'-v1')  where id = 1

查看 ClickHouse 日志会发现 binlog 监听事件:

=== UpdateRowsEventV2 ===
Timestamp: 1595958048
Event Type: 31
Server ID: 1
Event Size: 93
Log Pos: 20454
Flags: 0
Schema: htap
Table: t_organization
Row[0]: (1, 1000, 'Realinsight', 1595928587)
Row[1]: (1, 1000, 'Realinsight-v1', 1595928587)

查询 ClickHouse 的数据表:

select * from t_organization;
 
┌─id─┬─code─┬─name───────────┬──────────updatetime─┐
│  1 │ 1000 │ Realinsight-v1 │ 2020-07-28 17:29:47 │
└────┴──────┴────────────────┴─────────────────────┘
┌─id─┬─code─┬─name──────┬──────────updatetime─┐
│  2 │ 1001 │ Realindex │ 2020-07-28 17:29:48 │
└────┴──────┴───────────┴─────────────────────┘
┌─id─┬─code─┬─name─┬──────────updatetime─┐
│  3 │ 1002 │ EDT  │ 2020-07-28 17:29:49 │
└────┴──────┴──────┴─────────────────────┘

可以看到 id = 1 的数据被修改了。

现在再次回到 MySQL，尝试删除数据:

DELETE FROM t_organization where id = 2

回到 ClicKHouse，同样会发现 DeleteRows 的 binlog 监听事件:

=== DeleteRowsEventV2 ===
Timestamp: 1595958230
Event Type: 32
Server ID: 1
Event Size: 60
Log Pos: 20744
Flags: 0
Schema: htap
Table: t_organization
Row[0]: (2, 1001, 'Realindex', 1595928588)

查询 ClickHouse的 t_organization:

select * from t_organization;


SELECT *
FROM t_organization


┌─id─┬─code─┬─name───────────┬──────────updatetime─┐
│  1 │ 1000 │ Realinsight-v1 │ 2020-07-28 17:29:47 │
└────┴──────┴────────────────┴─────────────────────┘
┌─id─┬─code─┬─name─┬──────────updatetime─┐
│  3 │ 1002 │ EDT  │ 2020-07-28 17:29:49 │
└────┴──────┴──────┴─────────────────────┘

id = 2 的数据被删除了。

这是怎么实现的呢?  在刚才的查询中增加 _sign 和 _version 虚拟字段，一切将会真相大白。

select *,_sign,_version from t_organization order by _sign desc,_version desc


SELECT 
    *,
    _sign,
    _version
FROM t_organization
ORDER BY 
    _sign DESC,
    _version DESC


┌─id─┬─code─┬─name───────────┬──────────updatetime─┬─_sign─┬─_version─┐
│  1 │ 1000 │ Realinsight-v1 │ 2020-07-28 17:29:47 │     1 │        2 │
└────┴──────┴────────────────┴─────────────────────┴───────┴──────────┘
┌─id─┬─code─┬─name────────┬──────────updatetime─┬─_sign─┬─_version─┐
│  1 │ 1000 │ Realinsight │ 2020-07-28 17:29:47 │     1 │        1 │
│  2 │ 1001 │ Realindex   │ 2020-07-28 17:29:48 │     1 │        1 │
│  3 │ 1002 │ EDT         │ 2020-07-28 17:29:49 │     1 │        1 │
└────┴──────┴─────────────┴─────────────────────┴───────┴──────────┘
┌─id─┬─code─┬─name──────┬──────────updatetime─┬─_sign─┬─_version─┐
│  2 │ 1001 │ Realindex │ 2020-07-28 17:29:48 │    -1 │        3 │
└────┴──────┴───────────┴─────────────────────┴───────┴──────────┘


5 rows in set. Elapsed: 0.048 sec.

在查询时，对于已经被删除的数据，ClickHouse 会自动重写 SQL，将 _sign = -1 的数据过滤掉;

对于修改的数据，则自动重写 SQL，为其增加 FINAL 修饰符。

select * from t_organization
等同于
select * from t_organization final where _sign = 1

在 20.5 版本中，final 查询已经支持多线程，性能有很大的提升。

大家应该会发现，目前在 ReplacingMergeTree 中被删除的数据只是被过滤掉了，并没有物理删除。经与作者大神 zhang2014 咨询，将来会通过类似 GC 的思路通过另外的线程定期删除 _sign = -1 的数据。

这项功能如果被 merge 进主线，无疑会增强 ClicKHouse 更加自动化的属性。

关于作者：

朱凯，ClickHouse贡献者之一，ClickHouse布道者，资深架构师， 十多年IT从业经验，对大数据领域主流技术与解决方案有深入研究，擅长分布式系统的架构设计与整合。

著有《企业级大数据平台构建：架构与实现》《ClickHouse原理解析与应用实践》。现就职于远光软件股份有限公司，任大数据事业部平台开发部总经理。

《ClickHouse原理解析与应用实践》