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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ClickHouse 查询效果比 MySQL 稳定

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

总结

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