ClickHouse 与 MySQL 数据库适用场景对比总结

kumfo
English

目前来说,网上有很多相关的资料证明ClickHouse数据库查询响应速度比MySQL快上一百到几百倍。实际上,ClickHouseMySQL具有不同的应用场景和局限性,最近在研究这个ClickHouse打算应用于大量数据的表来做查询的时候,踩了些坑,于是在此做个总结,用于后续做数据存储以及处理的时候作为备忘,以及对想要用ClickHouse替换MySQL数据库某部分数据存储的时候做个参考。

采用 VersionedCollapsingMergeTree 引擎来做会产生修改但是不常修改的数据

本身 ClickHouse 不适合处理数据的频繁修改以及删除操作,对于删除和修改会消耗大量的性能,特别是频繁的单条数据修改。所以,通常我们看到很多资料上说数据尽量批量写入,不管是以1000条,10000条还是多少条为一批,用以提升ClickHouse的性能。

另外,本身ClickHouse的数据写入、修改、删除是异步的,对于操作写入、修改、删除后的数据需要做及时查询的,不适合用ClickHouse来做存储,且ClickHouse不支持事务,所以ClickHouse不要用于数据一致性较高的场景。

在某些场景,可能数据通常来说是不需要修改的,但是某些场景需要修改一次或者几次,然后为了响应速度,希望切换到ClickHouse来的话,可以采用VersionedCollapsingMergeTree引擎来做数据存储,关于这个存储引擎,我这里可以简单介绍一下,具体的可以参考ClickHouse的详细文档。

这个存储引擎的大致原理是,通过提供一个sign和一个version标记,sign存储的时候,值为1-1version存储数据的版本号,具体应用于以下两种场景的规则为:

  1. 需要删除的情况下,重新插入这一行的数据,version保持不变,sign设置为-1,那么数据表就会存在两条除了sign不一样的重复数据。然后ClickHouse会定期执行合并折叠,把这样两条数据一致,但是两条sign相加为0的数据删掉。这里就需要注意一点,是定时合并清除的,所以查询的时候需要用group by之后,再做having(sign)>0)来手动排除删除的数据。
  2. 修改的情况下,在做以上操作的同时,插入新数据,sign标记为1version在上一次的基础上增加1即可。当然查询的时候,也需要进行手动排除前一个版本的数据。

稀疏索引不适合用于精确查询

在说这个稀疏索引不适合做精确查询之前,先来说以下我说的这种精确查询的场景:

  1. 需要根据某个加了索引的条件,比如id或者user_id来查询当前行的数据;
  2. 需要根据某个加了索引的条件,如id或者user_id来取数据列表做分页

ClickHouse 用的是稀疏索引,和MySQLB+树不一样。(关于稀疏索引和B+树索引我这里不做介绍,这两个东西如果做介绍不是一时半会能解释清楚,如果有人看到我这篇文章不了解的话,可以自行去先研究一下。),所以再做精确条件查询的时候,ClickHouse扫描数据量会很大,实际响应速度并不会达到理想的状态。

而对于ClickHouse采用了稀疏索引的情况,特别适合用group by来做查询,经过几次group by 之后,就能排除大量的数据,所以通常情况下最适合的场景就是用于处理统计查询,在这种情况下,大量数据情况下响应速度比MySQL快几十倍,几百倍就能提现出来了。

列式数据库不适合一次性查询大量列

另外就是ClickHouse的列式数据库的特性,基于以上说的,需要精确查询的场景,一次性需要查询大量的字段情况下,响应速度也没有想象的理想。就算式增加了很多个group by条件,最后由于需要扫描的列很多,在MySQL正确加索引的情况下,ClickHouse响应速度通常没有MySQL快。

ClickHouse 查询效果比 MySQL 稳定

具体的测试我这里就不做多说,主要说一下场景。在两种数据表都正确做好索引的情况下,在做2亿数据列表查询的时候,MySQL在做分页数据查询的时候,开始的几页查询会明显比较耗时,大概在500ms800ms不等,但是后续的的分页查询基本上能达到50ms80ms,这里应该是MySQL数据预热起了作用。但是ClickHouse基本上都是稳定在230ms300ms

总结

以目前的测试和观察来看,如果需要做统计查询,且数据不是频繁修改的情况下,采用ClickHouse来存储和处理数据查询。如果需要频繁修改或是做大数据列表查询的场景,最好的方案还是用MySQL查询,并对数据进行分表处理,得到的数据响应性能会比ClickHouse好太多。

阅读 1.3k

Kumfo的杂货铺
做一些经验总结和一些学习心得分享,主要围绕PHP。 现在正在学习机器学习,会增加一些机器学习的思考分享。
avatar
kumfo
SegmentFault 后端工程师

程序生存法则:ฏ๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎...

6.4k 声望
4k 粉丝
0 条评论
avatar
kumfo
SegmentFault 后端工程师

程序生存法则:ฏ๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎๎...

6.4k 声望
4k 粉丝
文章目录
宣传栏