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

偏执的太偏执、 2022-05-23 04:08 161阅读 0赞

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 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均为脱敏数据

![Image 1][]

# 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均为脱敏数据
    
      
    
    
    ![Image 1][]

# 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

## 压缩对比 ##

<table> 
 <thead> 
  <tr> 
   <th style="text-align:center;">表名</th> 
   <th style="text-align:center;">MySQL表容量</th> 
   <th style="text-align:center;">ClickHouse表容量</th> 
   <th style="text-align:center;">压缩倍数</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="text-align:center;">article_clientuser_sum</td> 
   <td style="text-align:center;">5.5GB</td> 
   <td style="text-align:center;">1.2G</td> 
   <td style="text-align:center;">4.6</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">xx_httpcode_minf</td> 
   <td style="text-align:center;">20GB</td> 
   <td style="text-align:center;">243M</td> 
   <td style="text-align:center;">84</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">xx_network_flow</td> 
   <td style="text-align:center;">189MB</td> 
   <td style="text-align:center;">25M</td> 
   <td style="text-align:center;">7.56</td> 
  </tr> 
 </tbody> 
</table>

*  **注**：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 ##

*  [MySQLのデータをClickHouseで高速に集計する][MySQL_ClickHouse]

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

[Image 1]: 
[MySQL_ClickHouse]: https://link.jianshu.com?t=https%3A%2F%2Fqiita.com%2Fmikage%2Fitems%2Ffba19b3b8697fc6f73f0