ClickHouse是一个非常强大的OLAP数据库，查询速度极快，但是它也有很多坑。本文是我使用ClickHouse两年多的踩坑总结，包括建表、数据写入、查询、运维、数据类型等各个方面的坑，以及对应的解决方法。这些都是我亲身经历过的问题，希望能帮到正在使用或者准备使用ClickHouse的你，让你少走弯路。

一、写在前面

先说说我和ClickHouse的故事。

我们公司有一个数据分析平台，最开始用的是MySQL，数据量小的时候还可以，后来数据量到了几千万条，查询就越来越慢了。后来我们调研了几个OLAP数据库，最终选择了ClickHouse。

ClickHouse的查询速度确实非常快，上亿条数据的聚合查询，几秒钟就能出结果。但是它也有很多坑，我们在使用过程中踩了不少坑，有些坑还造成了线上故障。

这篇文章就是我这两年多踩坑的总结。每个坑我都会说清楚问题是什么、原因是什么、怎么解决的。希望能帮到大家。

二、建表相关的坑

坑1：ORDER BY和PARTITION BY搞反了

最开始建表的时候，我对ClickHouse的ORDER BY和PARTITION BY理解不深，搞反了两者的作用。

我把一个高基数的字段（比如用户ID）放在了PARTITION BY里，结果每个分区只有几条数据，分区数量爆炸，查询速度反而变慢了，而且元数据占用了大量内存。

后来才搞清楚：PARTITION BY是用来分区的，应该用低基数的字段，比如按日期分区，每个分区包含大量数据；ORDER BY是用来排序的，决定了数据在磁盘上的物理顺序，应该用查询中经常用来过滤和分组的字段。

正确的做法是：PARTITION BY用日期（toYYYYMM(created_at)），ORDER BY用查询中常用的过滤和分组字段的组合。

坑2：ORDER BY字段顺序不对

ORDER BY的字段顺序非常重要，它决定了数据的物理排序，直接影响查询性能。

我最开始建表的时候，ORDER BY的字段顺序是随便排的，结果很多查询都用不上索引，需要全表扫描，速度很慢。

后来才明白，ORDER BY的字段顺序应该遵循"等值过滤字段在前，范围过滤字段在后，分组字段也要考虑"的原则。查询中经常用来做等值过滤的字段放在前面，这样查询的时候可以快速定位到数据块。

比如我们的查询经常按categoryid和createdat过滤，那ORDER BY就应该是(categoryid, createdat)，而不是(createdat, categoryid)。

坑3：主键不是唯一的

很多人从MySQL转过来，以为ClickHouse的主键（ORDER BY的字段）是唯一的，其实不是。ClickHouse的主键只是排序键，不是唯一键，相同主键的数据可以重复插入。

我最开始不知道这个，以为主键会去重，结果插入了很多重复数据，查询结果不对。

解决方法是：如果需要去重，可以使用ReplacingMergeTree引擎，指定一个版本字段（比如updated_at），合并的时候保留最新版本的数据。但是要注意，ReplacingMergeTree的去重是在合并的时候发生的，不是实时的，查询的时候可能还是会有重复数据，需要用FINAL关键字或者自己写去重逻辑。

如果需要严格的唯一约束，可以在应用层做去重，或者使用外部的去重系统（比如Redis的布隆过滤器）。

坑4：字符串字段没有指定编码

ClickHouse的字符串字段默认是没有编码概念的，就是原始的字节序列。我最开始不知道，插入中文数据的时候，有时候会出现乱码。

后来才搞清楚，ClickHouse的String类型存储的是原始字节，编码由客户端决定。如果客户端用UTF-8编码写入，读出来也是UTF-8，就不会有问题。如果写入和读取的编码不一致，就会出现乱码。

解决方法是：统一使用UTF-8编码，客户端连接的时候指定UTF-8，写入和读取都用UTF-8，就不会有问题。

三、数据写入的坑

坑5：单条插入性能极差

最开始我们用ClickHouse的时候，像用MySQL一样，单条单条地插入数据，结果性能极差，每秒只能插入几十条，CPU占用还很高。

后来才知道，ClickHouse是列式存储，不适合单条插入。每次插入都会生成一个新的数据片段（part），后台需要不断地合并这些片段，开销很大。

正确的做法是：批量插入，每次插入几千到几万条数据。我们后来改成了批量插入，每次插入1万条，写入性能提升了几百倍，每秒能插入几十万条。

如果是实时数据，可以用一个缓冲区，攒够一批再写入，或者用Kafka表引擎来缓冲。

坑6：插入太频繁导致part太多

虽然改成了批量插入，但是有一段时间我们的并发写入太多，导致数据片段（part）的数量增长很快，出现了"Too many parts"的错误。

ClickHouse对每个分区的part数量有限制，默认是300个。如果写入太频繁，part来不及合并，就会超过这个限制，报错。

解决方法是：

  • 减少写入并发，控制写入频率
  • 增大每次写入的数据量，减少写入次数
  • 调整合并参数，比如增大maxbytestomergeatmaxspaceinpool
  • 如果是实时写入，用Kafka表引擎缓冲，由Kafka引擎批量写入

坑7：数据写入后查询不到

有一次我们遇到了一个奇怪的问题：数据写入成功了，但是查询的时候查不到，过了几分钟又能查到了。

排查了很久才发现，是因为我们用了分布式表，写入的时候写到了一个节点，但是查询的时候路由到了另一个节点，而数据还没有同步过去。

ClickHouse的分布式表写入有两种模式：一种是写入分布式表，由分布式表把数据分发到各个节点；另一种是直接写本地表，需要自己控制数据分布。我们最开始用的是直接写本地表，但是没有考虑数据分布的问题，导致数据都写到了一个节点上。

解决方法是：使用分布式表写入，让ClickHouse自动分发数据；或者使用一致性哈希，自己控制数据分布，保证查询和写入的路由一致。

四、查询的坑

*坑8：SELECT 很慢**

ClickHouse是列式存储，查询的时候只读取需要的列，速度很快。但是如果用SELECT *，就需要读取所有列，速度会慢很多，特别是列很多的表。

我最开始图省事，经常用SELECT *，结果查询速度很慢。后来改成只查询需要的列，速度提升了好几倍。

所以在ClickHouse中，一定要避免SELECT *，只查询你需要的列。这不仅是性能问题，也是好习惯。

坑9：JOIN性能差

ClickHouse的JOIN性能和MySQL不一样，很多人用MySQL的方式写JOIN，结果性能很差。

我最开始写了一个大表JOIN小表的查询，结果跑了几分钟都没出来。后来才知道，ClickHouse的JOIN是把右表加载到内存中，然后和左表做匹配。如果右表很大，内存会爆掉，性能也很差。

解决方法是：

  • 小表放右边，大表放左边
  • 右表尽量小，只包含需要的列和行
  • 如果右表很大，可以考虑用字典（Dictionary）代替JOIN
  • 使用ANY INNER JOIN代替普通的INNER JOIN，减少匹配次数
  • 对于大表JOIN大表，可以考虑预计算，把JOIN的结果存成一张新表

坑10：IN子查询很慢

有一次我写了一个查询，用了IN子查询，结果速度很慢。比如：

SELECT * FROM big_table WHERE user_id IN (SELECT user_id FROM small_table WHERE condition)

这个查询在MySQL中很快，但是在ClickHouse中很慢。

原因是ClickHouse的IN子查询不会自动优化，它会先执行子查询，然后把结果放到内存中，再和外层查询做匹配。如果子查询的结果很大，性能就会很差。

解决方法是：把IN子查询改成JOIN，或者用GLOBAL IN（分布式场景下）。比如上面的查询可以改成：

SELECT b.* FROM big_table b INNER JOIN small_table s ON b.user_id = s.user_id WHERE s.condition

这样性能会好很多。

坑11：LIMIT不起作用

有一次我写了一个查询，加了LIMIT 10，但是查询还是很慢，扫描了全表。

原因是ClickHouse的LIMIT是在查询结果之后才做的，它不会因为有LIMIT就提前停止扫描。如果查询需要排序或者聚合，它还是会扫描所有符合条件的数据，然后再取前10条。

解决方法是：尽量在WHERE条件中过滤更多的数据，减少扫描的数据量。如果只是想看一下数据的样例，可以用SAMPLE子句做采样查询，速度会快很多。

坑12：日期函数用错了

ClickHouse的日期函数很多，我最经常用错的是toDate和toDateTime的区别，以及各种日期截断函数。

有一次我用toDate(createdat)来过滤，但是createdat是DateTime类型，结果查询的时候没有命中分区索引，因为分区是按toYYYYMM(createdat)建的，而toDate(createdat)和分区键不匹配。

解决方法是：过滤条件尽量用和分区键、排序键相同的表达式。比如分区键是toYYYYMM(createdat)，那过滤的时候就用createdat >= '2021-01-01' AND created_at < '2021-02-01'，不要用函数包裹。

五、数据类型的坑

坑13：用String存数字

最开始建表的时候，我图省事，把所有字段都设成了String类型，包括数字类型的字段。结果查询的时候需要做类型转换，性能很差，而且聚合计算的结果也不对。

比如把金额存成了String，求和的时候需要用toFloat64(amount)转换，不仅慢，而且如果有脏数据还会报错。

解决方法是：用正确的数据类型。整数用Int32或Int64，浮点数用Float64或Decimal，日期用Date或DateTime，字符串用String。正确的数据类型不仅性能好，而且能自动校验数据，减少脏数据。

坑14：Decimal精度不够

有一次我们用Decimal(10, 2)来存金额，结果有一笔大额交易插入的时候报错了，因为数值超过了Decimal的精度范围。

Decimal(10, 2)表示总共10位，小数2位，整数部分最多8位，最大能存99999999.99。如果金额超过这个数，就会报错。

解决方法是：根据业务需求选择合适的精度。如果金额可能很大，就用Decimal(18, 2)或者更大的精度。ClickHouse支持最大Decimal(128, S)，足够用了。

坑15：Nullable字段影响性能

我最开始建表的时候，把很多字段都设成了Nullable，因为觉得可能会有空值。结果查询性能比非Nullable的字段差了很多。

原因是Nullable字段需要额外存储一个null标记，读取的时候需要额外处理，而且很多优化对Nullable字段不生效。

解决方法是：尽量不要用Nullable，用默认值代替空值。比如数字类型用0作为默认值，字符串用空字符串作为默认值。如果确实需要表示空值，再用Nullable，但是要控制Nullable字段的数量。

六、运维的坑

坑16：磁盘空间不足

ClickHouse的数据压缩率很高，但是数据量增长很快，如果不注意监控，磁盘空间很容易不足。

有一次我们的磁盘满了，导致ClickHouse无法写入，查询也变慢了，差点造成线上故障。

解决方法是：

  • 监控磁盘使用率，设置告警，超过80%就告警
  • 设置数据的TTL，自动删除过期数据
  • 定期清理不需要的临时表和测试数据
  • 磁盘快满的时候，及时扩容或者删除旧数据
  • 可以用多盘存储，把数据分散到多个磁盘上

坑17：内存不足导致OOM

ClickHouse是内存数据库，查询的时候会把大量数据加载到内存中。如果查询的数据量太大，或者并发查询太多，很容易导致内存不足，被系统OOM杀掉。

我们遇到过几次，一个大查询把内存占满了，导致整个ClickHouse实例挂掉，所有查询都失败了。

解决方法是：

  • 限制单查询的内存使用，设置maxmemoryusage参数
  • 限制用户的内存使用，设置maxmemoryusageforuser
  • 对大查询做限流，或者放到低峰期执行
  • 查询的时候尽量过滤更多数据，减少扫描的数据量
  • 监控内存使用率，设置告警

坑18：副本同步延迟

我们用了ReplicatedMergeTree引擎，做了多副本，保证高可用。但是有一次副本同步延迟很大，主副本写入的数据，从副本很久都查不到。

排查发现是因为网络波动，导致副本之间的同步中断了，恢复之后有大量的数据需要同步，延迟越来越大。

解决方法是：

  • 监控副本同步延迟，设置告警
  • 如果延迟太大，可以先停止写入，等同步追上来再恢复
  • 保证副本之间的网络稳定，尽量在同一个机房
  • 如果延迟一直追不上，可以删除从副本的数据，重新同步

七、其他坑

坑19：ALTER TABLE很慢

ClickHouse的ALTER TABLE操作（比如加列、改列类型）比MySQL慢很多，特别是大表。我最开始不知道，在线上直接ALTER TABLE加列，结果表被锁了很久，查询和写入都受到了影响。

原因是ClickHouse的ALTER TABLE需要修改所有的数据文件，数据量越大，耗时越长。

解决方法是：

  • 加列尽量在低峰期执行
  • 加列是比较快的，因为只需要修改元数据，不需要修改已有数据（新列用默认值填充）
  • 改列类型或者删列比较慢，需要重写所有数据，尽量避免
  • 如果必须改，可以新建一张表，把数据导过去，然后替换表名

坑20：不支持事务

ClickHouse不支持事务，也不支持回滚。我最开始从MySQL转过来，习惯性地用事务，结果发现不支持，闹了笑话。

ClickHouse是OLAP数据库，设计目标是高吞吐的写入和快速的查询，事务不是它的强项。如果需要事务，应该用MySQL等OLTP数据库。

解决方法是：在应用层保证数据的一致性。比如写入失败了就重试，或者用幂等写入，保证重复写入不会有问题。如果需要严格的事务一致性，就不要用ClickHouse，用MySQL或者其他支持事务的数据库。

八、给新手的建议

如果你是ClickHouse新手，我有几个建议。

1. 认真看官方文档

ClickHouse的官方文档非常详细，也非常好。遇到问题先看文档，大部分问题都能在文档中找到答案。不要凭MySQL的经验来用ClickHouse，两者的设计理念和使用方式差别很大。

2. 从小规模开始

不要一开始就把所有数据都迁到ClickHouse，先从小规模开始，熟悉它的特性和坑，然后再逐步扩大规模。可以先做一个非核心的业务，跑一段时间，稳定了再迁核心业务。

3. 做好监控

ClickHouse的监控很重要，磁盘、内存、CPU、查询延迟、写入延迟、副本同步等都要监控。出了问题能及时发现，及时处理，避免小问题变成大故障。

4. 多测试

上线之前多测试，特别是建表和查询。测试不同的建表方式对性能的影响，测试查询的性能，测试异常情况。测试做得越充分，上线后出问题的概率就越小。

5. 社区活跃，多交流

ClickHouse的社区很活跃，有问题可以在GitHub、Stack Overflow、中文社区等地方提问，很多人会热心解答。也可以多看看别人的踩坑经验，避免自己踩同样的坑。

九、写在最后

ClickHouse是一个非常优秀的OLAP数据库，查询速度极快，能处理海量数据的分析查询。但是它也有很多坑，需要我们在使用过程中不断学习和总结。

这篇文章总结了我这两年多踩过的20个坑，每个坑都是亲身经历过的，希望能帮到大家。如果你正在使用或者准备使用ClickHouse，希望这篇文章能让你少走弯路。

当然，ClickHouse在不断发展，很多坑在新版本中已经被修复或者优化了。这篇文章基于的是2021年的版本，如果你用的是更新的版本，有些坑可能已经不存在了，具体情况还要看你用的版本。

最后用一句话结束本文："ClickHouse很强大，但是强大的工具也需要正确的使用方式。"希望每一个使用ClickHouse的人，都能发挥它的最大价值，少踩坑，多产出。