使用ClickHouse一键接管MySQL数据分析

https://www.jianshu.com/p/5bfb043a075d



数据导入

第一组
# du出的表大小 5.5G article_clientuser_sum.ibd # ClickHouse操作语句 CREATE TABLE
article_clientuser_sum ENGINE = MergeTree ORDER BY id AS SELECT * FROM mysql(
'host:port', 'db', 'article_clientuser_sum', 'user', 'password') # 耗时和平均速度 0
rowsin set. Elapsed: 137.251 sec. Processed 18.59 million rows, 7.34 GB (135.43
thousand rows/s., 53.48 MB/s.)
第二组
# 另一个表 20G xx_httpcode_minf.ibd CREATE TABLE xx_httpcode_minf ENGINE =
MergeTree ORDER BY id AS SELECT * FROM mysql('host:port', 'db', 'tb', 'user',
'password') # 不知道为啥这表这么快就导入了 貌似是行少，但是表的总大小大啊 0 rows in set. Elapsed: 44.389
sec. Processed 13.03 million rows, 1.44 GB (293.44 thousand rows/s., 32.35
MB/s.)
PK之count(*)

第一组
# 1800w # ClickHouse SELECT count(*) FROM article_clientuser_sum ┌──count()─┐ │
18587381 │ └──────────┘ 1 rows in set. Elapsed: 0.033 sec. Processed 18.59
millionrows, 74.35 MB (556.76 million rows/s., 2.23 GB/s.) # MySQL mysql> select
count(*) from article_clientuser_sum ; +----------+ | count(*) | +----------+ |
18587381 | +----------+ 1 row in set (39.48 sec) # 性能 1196X
第二组
# 1300w # ClickHouse SELECT count(*) FROM xx_httpcode_minf ┌──count()─┐ │
13025469 │ └──────────┘ 1 rows in set. Elapsed: 0.032 sec. Processed 13.03
millionrows, 52.10 MB (406.68 million rows/s., 1.63 GB/s.) # MySQL mysql> SELECT
count(*) -> FROM xx_httpcode_minf; +----------+ | count(*) | +----------+ |
13025469 | +----------+ 1 row in set (1 min 46.87 sec) # 性能 3340X
PK之复杂查询

第一组
# ClickHouse SELECT SUM(size) AS size FROM xx_network_flow WHERE (date >=
'2018-01-01') AND (date <= '2018-01-31') AND (netstat = 0) AND (project LIKE
'保密%') Row 1: ────── size: 4132888693 1 rows in set. Elapsed: 0.039 sec.
Processed841.66 thousand rows, 9.46 MB (21.67 million rows/s., 243.70 MB/s.) #
MySQL +------------+ | size | +------------+ | 4132888693 | +------------+ 1 row
in set (2.34 sec) # 性能 60X
* SQL太长，截图示例
* SQL里的xxx均为脱敏数据# ClickHouse ┌─────size─┐ │ 76888224 │ └──────────┘ 1 rows in
set. Elapsed: 0.137 sec. Processed 841.66 thousand rows, 9.46 MB (6.13 million
rows/s., 68.97 MB/s.)# MySQL +----------+ | size | +----------+ | 76888224 |
+----------+ 1 rowin set (2.86 sec) # 性能 21X
第二组
# ClickHouse SELECT project, idc, minf, http_code, sum(sumhit) AS num FROM
xx_httpcode_minfWHERE (date = '2018-01-16') AND (httptype = 'download') AND \
(minf >=0) AND (minf <= 288) AND \ (http_code IN ('200', '500', '404', '502',
'503', '504')) GROUP BY project, idc, minf, http_code ORDER BY num DESC LIMIT 3
┌─project─────────────────────────────────────┬─idc────┬─minf─┬─http_code─┬────
num─┐ │ 域名1xxxx │ .1xx │ 195 │ 200 │ 247522 │ │ 域名2xxxx │ .2xx │ 185 │ 200 │
246613 │ │ 域名3xxxx │ .3xx │ 188 │ 200 │ 245808 │
└─────────────────────────────────────────────┴────────┴──────┴───────────┴────────┘
3 rows in set. Elapsed: 0.161 sec. Processed 13.03 million rows, 284.63 MB (
80.94 million rows/s., 1.77 GB/s.) # MySQL +
---------------------------------------------+--------+------+-----------+--------+
|project | idc | minf | http_code | num | +
---------------------------------------------+--------+------+-----------+--------+
| 域名1xxxx| .1.xx | 195 | 200 | 247522 | | 域名2xxxx | .2.xxx | 185 | 200 | 246613
| | 域名3xxxx | .3xx | 188 | 200 | 245808 | +
---------------------------------------------+--------+------+-----------+--------+
3 rows in set (12.02 sec) # 性能 75X
* SQL太长，截图示例
*
SQL里的xxx均为脱敏数据

# ClickHouse
┌─project────────────────────────────┬─idc────┬─minf─┬─http_code─┬───num─┐ │ 域名1
│1xxx│ 154 │ 404 │ 10792 │ │ 域名1 │ 2xxx │ 155 │ 404 │ 10395 │ │ 域名1│ 3xxx │ 272
│404 │ 10313 │
└────────────────────────────────────┴────────┴──────┴───────────┴───────┘3 rows
in set. Elapsed: 0.119 sec. Processed 13.03 million rows, 283.15 MB (109.10
million rows/s.,2.37 GB/s.) # MySQL
+------------------------------------+--------+------+-----------+-------+|
project | idc | minf | http_code | num |
+------------------------------------+--------+------+-----------+-------+| 域名1
| .1zz | 154 | 404 | 10792 | | 域名1 | .3xx | 155 | 404 | 10395 | | 域名1 | .3rr |
272 | 404 | 10313 |
+------------------------------------+--------+------+-----------+-------+3 rows
in set (2.19 sec) # 性能 18X
压缩对比

表名MySQL表容量ClickHouse表容量压缩倍数
article_clientuser_sum5.5GB1.2G4.6
xx_httpcode_minf20GB243M84
xx_network_flow189MB25M7.56
* 注
：xx_httpcode_minf这个表的MySQL文件20个G，应该是有大量的空洞造成的，这也就是Facebook的人开发MyRocks的原因：减少空洞，节省磁盘
风险

*
目前该功能还处于初级阶段，有不完善的地方，比如数据导入的方式比较粗暴，中间如果有异常，需要重新执行（使用的ClickHouse版本为：1.1.54342）
* MySQL的参数需要修改，如max_allowed_packet
* 数据导入时需要注意带宽，实测可以达到50MB/S
* 如果MySQL里的字段有decimal字符类型会怎么样？ClickHouse没有双精度的类型
* 部分SQL需要改写
* 如双引号改单引号
讨论

*
ClickHouse为啥快？

* MySQL单条SQL是单线程的，只能跑满一个core，ClickHouse相反，有多少CPU，吃多少资源，所以飞快
* ClickHouse不支持事务，不存在隔离级别。这里要额外说一下，
有人觉得，你一个数据库都不支持事务，不支持ACID还玩个毛。ClickHouse的定位是分析性数据库，而不是严格的关系型数据库。又有人要问了，数据都不一致，统计个毛。举个例子，汽车的油表是100%准确么？为了获得一个100%准确的值，难道每次测量你都要停车检查么？统计数据的意义在于用大量的数据看规律，看趋势，而不是100%准确。
*
IO方面，MySQL是行存储，ClickHouse是列存储，后者在count()这类操作天然有优势，同时，在IO方面，MySQL需要大量随机IO，ClickHouse基本是顺序IO。
*
有人可能觉得上面的数据导入的时候，数据肯定缓存在内存里了，这个的确，但是ClickHouse基本上是顺序IO，用过就知道了，对IO基本没有太高要求，当然，磁盘越快，上层处理越快，但是99%的情况是，CPU先跑满了（数据库里太少见了，大多数都是IO不够用）。
*
说到MySQL上跑的各种复杂查询，那是相当痛苦的回忆。从索引层面，很难对这些SQL进行优化，这也是我从MySQL DBA转做数据分析后要解决的第一个问题

*
专业的事情让专业的数据库来做，放开MySQL吧~

*
太™快了，还不赶紧来试试

Reference

* MySQLのデータをClickHouseで高速に集計する
<https://link.jianshu.com?t=https%3A%2F%2Fqiita.com%2Fmikage%2Fitems%2Ffba19b3b8697fc6f73f0>

作者：JackpGao
链接：https://www.jianshu.com/p/5bfb043a075d
來源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

• « 上一篇：python基础系列教程——数据结构（列表、元组、字典、集合、链表）
• » 下一篇：Matlab进行t检验