如何利用ClickHouse高速汇总查询MySQL数据
概要介绍
直连到MySQL的功能现在已被添加到ClickHouse中。所以可以直接从ClickHouse查询MySQL表中的数据。
ClickHouse是一个超高性能的海量数据快速查询的分布式实时处理平台，在数据汇总查询方面(如GROUP BY)，ClickHouse的查询更快，因此通常情况下在MySQL上进行聚合。
下面是在自己的虚拟环境中做的测试记录。既然是虚拟环境非生产环境，请看参考应用级别，而不是严格的测试。
准备测试样本数据
正确准备和测试大约1000万个数据项。
#!/usr/bin/perlusestrict;usewarnings;srand(0);
my$RECORD_COUNT=10000000;
for(my$i=1;$i<=$RECORD_COUNT;$i++){my@field;push(@field,$i);
push(@field,sprintf("%08d\@example.com",$i));
push(@field,int(rand(5))+1);
push(@field,int(rand(10))+1);
push(@field,int(rand(100000))+1);
push(@field,int(rand(1000000))+1);
push(@field,int(rand(10000000))+1);
printjoin("\t",@field),"\n";
}
执行脚本以创建样本数据。这是约500 MB。
$ ./makedata.pl > data.tsv
$ ls -lh data.tsv
-rw-rw-r-- 1 mikage mikage 512M Jan 18 07:44 data.tsv
$ head -2 data.tsv
1 00000001@example.com 1 8 9638 870466 5773036
2 00000002@example.com 4 7 36877 873905 7450951
使用 MySQL 测试
在MySQL中创建一个表并导入它。
由于似乎字符串数据比数字值更容易汇总，所以内容是数字的，但它们是可变的类型。
然后执行计算操作。
mysql> LOAD DATA LOCAL INFILE 'data.tsv' INTO TABLE testdata;
Query OK,10000000 rows affected(44.98 sec)
Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT data1,COUNT(*)FROM testdata GROUP BY data1;
+-------+----------+
| data1 | COUNT(*)|
+-------+----------+
| 1 | 2000453 |
| 2 | 1999993 |
| 3 | 1998988 |
| 4 | 1999013 |
| 5 | 2001553 |
+-------+----------+
5 rows in set(3.22 sec)
mysql> SELECT data2,COUNT(*)FROM testdata GROUP BY data2;
+-------+----------+
| data2 | COUNT(*)|
+-------+----------+
| 1 | 1000530 |
| 10 | 999424 |
| 2 | 1000648 |
| 3 | 1000357 |
| 4 | 998349 |
| 5 | 998889 |
| 6 | 999786 |
| 7 | 998774 |
| 8 | 1001805 |
| 9 | 1001438 |
+-------+----------+
10 rows in set(4.01 sec)
mysql> SELECT data3,COUNT(*)FROM testdata GROUP BY data3;
-- 結果省略
100000 rows in set(3 min 32.82 sec)
mysql> SELECT data1,COUNT(DISTINCT data5)FROM testdata GROUP BY data1;
+-------+-----------------------+
| data1 | COUNT(DISTINCT data5)|
+-------+-----------------------+
| 1 | 1813005 |
| 2 | 1812503 |
| 3 | 1812072 |
| 4 | 1811674 |
| 5 | 1814106 |
+-------+-----------------------+
5 rows in set(3 min 3.56 sec)
用BLOB类型做GROUPBY似乎很慢。
使用 Clickhouse 测试
您可以在ClickHouse上使用MySQL数据，只需在下面的表格中指定它，而不是表名。
mysql('host:port', 'database', 'table', 'user', 'password)
启动ClickHouse客户端并进行测试。
如果您在启动时不添加--multiline选项，则不会放置多行查询，因此请继续。
$ clickhouse-client --multiline
SELECT
data1,
COUNT(*)
FROM mysql('localhost','mikage','testdata','mikage','')
GROUP BY data1
┌─data1─┬─COUNT()─┐
│ 4│1999013│
│ 3│1998988│
│ 2│1999993│
│ 5│2001553│
│ 1│2000453│
└───────┴─────────┘
5 rows in set. Elapsed: 2.685 sec. Processed 10.00 million rows,40.00 MB(3.72 million rows/s.,14.90 MB/s.)
SELECT
data2,
COUNT(*)
FROM mysql('localhost','mikage','testdata','mikage','')
GROUP BY data2
┌─data2─┬─COUNT()─┐
│6 │ 999786│
│8 │1001805│
│9 │1001438│
│3 │1000357│
│2 │1000648│
│4 │ 998349│
│5 │ 998889│
│10 │ 999424│
│1 │1000530│
│7 │ 998774│
└───────┴─────────┘
10 rows in set. Elapsed: 2.692 sec. Processed 10.00 million rows,101.00 MB(3.71 million rows/s.,37.52 MB/s.)
SELECT
data3,
COUNT(*)
FROM mysql('localhost','mikage','testdata','mikage','')
GROUP BY data3
-- 結果省略
100000 rows in set. Elapsed: 5.236 sec. Processed 10.00 million rows,138.89 MB(1.91 million rows/s.,26.52 MB/s.)
SELECT
data1,
uniqExact(data5)
FROM mysql('localhost','mikage','testdata','mikage','')
GROUP BY data1
┌─data1─┬─uniqExact(data5)─┐
│ 4│ 1811674│
│ 3│ 1812072│
│ 2│ 1812503│
│ 5│ 1814106│
│ 1│ 1813005│
└───────┴──────────────────┘
5 rows in set. Elapsed: 12.944 sec. Processed 10.00 million rows,198.89 MB(772.55 thousand rows/s.,15.37 MB/s.)
-- ClickHouse有一个函数来粗略计算一个唯一的数字，所以让我们来计算一下。
SELECT
data1,
uniq(data5)
FROM mysql('localhost','mikage','testdata','mikage','')
GROUP BY data1
┌─data1─┬─uniq(data5)─┐
│ 4│ 1812684│
│ 3│ 1811607│
│ 2│ 1817432│
│ 5│ 1815763│
│ 1│ 1815300│
└───────┴─────────────┘
5 rows in set. Elapsed: 6.026 sec. Processed 10.00 million rows,198.89 MB(1.66 million rows/s.,33.00 MB/s.)
在MySQL中，相当耗时的查询也可以在很短的时间内处理。
重复统计时，最好将数据复制到ClickHouse一次。
如果您复制它，后续查询将更快。
建议暂时使用StripeLog引擎。
CREATE TABLE testdata
ENGINE = StripeLog AS
SELECT *
FROM mysql('localhost','mikage','testdata','mikage','')
Ok.
0 rows in set. Elapsed: 9.823 sec. Processed 10.00 million rows,917.66 MB(1.02 million rows/s.,93.42 MB/s.)
如果您有一个主键，您可能还想要使用MergeTree表。这是在ClickHouse中最常用的引擎。
有必要用ORDER BY指定数据的排序顺序（即使有重复也没有问题）。
CREATE TABLE testdata
ENGINE = MergeTree
ORDER BY id AS
SELECT *
FROM mysql('localhost','mikage','testdata','mikage','')
Ok.
0 rows in set. Elapsed: 20.466 sec. Processed 10.00 million rows,917.66 MB(488.61 thousand rows/s.,44.84 MB/s.)
我会尝试以前的查询。以下是StripeLog引擎的测试结果。
SELECT
data1,
COUNT(*)
FROM testdata
GROUP BY data1
┌─data1─┬─COUNT()─┐
│ 4│1999013│
│ 3│1998988│
│ 2│1999993│
│ 5│2001553│
│ 1│2000453│
└───────┴─────────┘
5 rows in set. Elapsed: 0.071 sec. Processed 10.00 million rows,40.00 MB(141.13 million rows/s.,564.53 MB/s.)
SELECT
data2,
COUNT(*)
FROM testdata
GROUP BY data2
┌─data2─┬─COUNT()─┐
│6 │ 999786│
│8 │1001805│
│9 │1001438│
│3 │1000357│
│2 │1000648│
│4 │ 998349│
│5 │ 998889│
│10 │ 999424│
│1 │1000530│
│7 │ 998774│
└───────┴─────────┘
10 rows in set. Elapsed: 0.177 sec. Processed 10.00 million rows,101.00 MB(56.34 million rows/s.,569.05 MB/s.)
SELECT
data3,
COUNT(*)
FROM testdata
GROUP BY data3
-- 結果省略
100000 rows in set. Elapsed: 0.779 sec. Processed 10.00 million rows,138.89 MB(12.84 million rows/s.,178.29 MB/s.)
SELECT
data1,
uniqExact(data5)
FROM testdata
GROUP BY data1
┌─data1─┬─uniqExact(data5)─┐
│ 5│ 1814106│
└───────┴──────────────────┘
┌─data1─┬─uniqExact(data5)─┐
│ 1│ 1813005│
└───────┴──────────────────┘
┌─data1─┬─uniqExact(data5)─┐
│ 4│ 1811674│
└───────┴──────────────────┘
┌─data1─┬─uniqExact(data5)─┐
│ 3│ 1812072│
└───────┴──────────────────┘
┌─data1─┬─uniqExact(data5)─┐
│ 2│ 1812503│
└───────┴──────────────────┘
5 rows in set. Elapsed: 1.725 sec. Processed 10.00 million rows,198.89 MB(5.80 million rows/s.,115.32 MB/s.)
SELECT
data1,
uniq(data5)
FROM testdata
GROUP BY data1
┌─data1─┬─uniq(data5)─┐
│ 4│ 1812684│
│ 3│ 1811607│
│ 2│ 1817432│
│ 5│ 1815763│
│ 1│ 1815300│
└───────┴─────────────┘
5 rows in set. Elapsed: 0.285 sec. Processed 10.00 million rows,198.89 MB(35.03 million rows/s.,696.62 MB/s.)
测试纪要
测试耗时总结如下:
我认为这个错误很大，因为它在VM环境下仅测试了一次
从左边开始为，(1)MySQL中的时间 (2) ClickHouse从MySQL读取和处理数据的时间 (3) 在ClickHouse上处理复制数据的时间。
执行查询 MySQL处理时间(秒) MySQL->ClickHouse处理时间(秒) ClickHouse处理时间(秒)
groupby(data1) 3.22 2.685 0.071
groupby(data2) 4.01 2.692 0.177
groupby(data3) 212.82 5.236 0.779
groupby(data1)+uniq(data5) 183.56 12.944 1.725
groupby(data1)+uniq(data5)概算 (无此功能) 6.026 0.285
当引用MySQL数据时，如果没有对应于ClickHouse的类型，它似乎是String类型。
没有相应的类型如Decimal类型，所以它也是String类型。
如果你想把它作为一个数值，精度将会改变，但是似乎有必要在MySQL端保持Double类型。
而且，Date和DateTime类型在MySQL和ClickHouse之间的范围也是不同的。
ClickHouse日期类型是1970 - 2038年之间。
如果有超出范围的数据，可能需要使其成为字符串类型，按年份，月份，日期分列，并将其作为数值复制。
参照源码如下：
MySQL类型 ClickHouse 类型 参考
tinyint UInt8 / Int8
smallint UInt16 / Int16
int / mediumint UInt32 / Int32
bigint UInt64 / Int64
float Float32
double Float64
dateDate 有可以表达的范围差异
datetime DateTime有可以表达的范围差异
timestamp DateTime
binaryFixed String
除上述以外 String
