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

  • 为啥有这篇文章?
    • 很多人好奇ClickHouse,都听说过很快,但是到底有多恐怖?
    • 新建表还要理解ClickHouse的引擎和数据类型,好麻烦
    • 今天,用一个简单粗暴的功能,帮你一键导入MySQL的数据,无需人肉建表

数据导入

第一组

# 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 rows in 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 million rows, 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 million rows, 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. Processed 841.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 row in set (2.86 sec)

# 性能 21X

第二组

# ClickHouse
SELECT
    project,
    idc,
    minf,
    http_code,
    sum(sumhit) AS num
FROM xx_httpcode_minf
WHERE (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_sum 5.5GB 1.2G 4.6
xx_httpcode_minf 20GB 243M 84
xx_network_flow 189MB 25M 7.56
  • :xx_httpcode_minf这个表的MySQL文件20个G,应该是有大量的空洞造成的,这也就是Facebook的人开发MyRocks的原因:减少空洞,节省磁盘

风险

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

讨论

  • ClickHouse为啥快?

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

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

  • 太™快了,还不赶紧来试试

Reference

广告

  • 提供专业的ClickHouse商业资讯、案例分析和课程培训,详情见微信(扫码请备注公司-姓名):


推荐阅读更多精彩内容