ClickHouse作为目前最流行的列式存储OLAP数据库之一，以其极致的查询性能，受到了很多公司的青睐。但在实际使用中，ClickHouse也有很多坑，稍不注意，就会导致性能问题甚至数据错误。我在使用ClickHouse的过程中，踩了很多坑，有些坑甚至让我熬夜排查。今天，就把这些踩坑经历分享出来，希望大家能少走弯路。

先说说背景。我们公司做数据分析，之前用的是MySQL，数据量小的时候，还能凑合，但数据量到了亿级之后，MySQL的查询性能，就完全跟不上了，一个简单的聚合查询，要跑几分钟，根本没法用。

后来，我们调研了很多OLAP数据库，包括ClickHouse、Druid、Kylin、Presto等，最后选择了ClickHouse，因为它的查询性能，确实非常极致，亿级数据，聚合查询，几百毫秒就能出结果，而且安装简单，运维成本低，SQL支持也比较完善。

于是，我们把数据迁移到了ClickHouse，开始用它做数据分析。最开始，用得很顺利，查询速度很快，大家都很满意。但慢慢地，随着数据量越来越大，查询越来越复杂，各种问题也来了，有些问题，非常诡异，让我熬了好几个通宵，才排查清楚。

今天，就把这些踩坑经历，分享出来。

一、踩坑一：ORDER BY 字段顺序不对，查询慢十倍

第一个坑，是建表的时候，ORDER BY字段的顺序，没有设计好，导致查询性能很差。

ClickHouse的表，最核心的配置，就是ORDER BY，它决定了数据在磁盘上的排序方式，也决定了查询的性能。ClickHouse的MergeTree引擎，是按照ORDER BY的字段，来排序存储数据的，查询的时候，如果过滤条件和ORDER BY的字段匹配，就能利用索引，快速定位数据，查询就很快。如果不匹配，就需要全表扫描，查询就很慢。

我们最开始建表的时候，对ORDER BY的重要性，认识不足，随便选了几个字段，作为ORDER BY，顺序也没有仔细考虑。结果，建表之后，发现很多查询，都很慢，尤其是一些常用的查询，要好几秒才能出结果，和ClickHouse宣传的几百毫秒，差远了。

我排查了很久，才发现，是ORDER BY的字段顺序，不对。我们的ORDER BY，第一个字段是userid，第二个字段是eventtype，第三个字段是eventtime。但我们最常用的查询，是按eventtime过滤，按eventtype聚合，userid很少作为过滤条件。

这样，数据是按userid排序的，查询的时候，按eventtime过滤，就没法利用索引，需要全表扫描，所以很慢。

找到原因之后，我们重新建表，把ORDER BY的顺序，改成了eventtime、eventtype、user_id，把最常用的过滤字段，放在最前面。改完之后，查询性能，提升了十倍多，原来要好几秒的查询，现在几百毫秒就出结果了。

这个坑，让我深刻认识到，ClickHouse的ORDER BY，是表设计中最重要的部分，一定要根据查询模式，仔细设计，把最常用的过滤字段，放在最前面，把高基数的字段，放在后面。ORDER BY设计得好，查询性能就好；设计得不好，查询性能就会很差。

而且，ClickHouse的ORDER BY，一旦建表，就不能修改了，如果要改，只能重建表，重新导入数据，非常麻烦。所以，建表之前，一定要仔细分析查询模式，设计好ORDER BY，不要等建完表，发现不对了，再改，那就太麻烦了。

二、踩坑二：分区键设计不合理，数据倾斜严重

第二个坑，是分区键（PARTITION BY）设计不合理，导致数据倾斜严重，查询性能差，合并也慢。

ClickHouse的分区，是把数据按照分区键，分成不同的目录，每个分区一个目录，查询的时候，如果过滤条件包含分区键，就可以只扫描相关的分区，跳过其他分区，提升查询性能。而且，分区也方便数据的管理和删除，比如按天分区，要删除某天的数据，直接删除分区就行，非常快。

我们最开始建表的时候，分区键选的是event_time的日期，按天分区，看起来很合理。但用了一段时间之后，发现数据倾斜很严重，有些天的数据量特别大，有些天的数据量特别小。数据量大的分区，有几十亿条数据，数据量小的分区，只有几万条。

数据倾斜，导致了两个问题：一是查询的时候，扫描大分区，还是很慢，因为大分区的数据量太大了；二是合并的时候，大分区的合并，非常慢，占用很多资源，影响其他查询。

我排查了很久，才发现，是分区键的粒度太粗了。按天分区，对于我们这种数据量很大的场景，粒度太粗了，一天的数据量，就有几十亿，一个分区太大了。

找到原因之后，我们重新建表，把分区键，改成了按小时分区，每个小时一个分区，这样，每个分区的数据量，就小很多了，大概几亿条，比较均匀。改完之后，查询性能，提升了不少，合并也快了很多。

当然，分区也不是越细越好，分区太细，会导致分区数量太多，元数据管理开销大，启动慢，合并也频繁。所以，分区键的粒度，要根据数据量来选择，数据量大，分区就细一点；数据量小，分区就粗一点。一般来说，每个分区的数据量，在1亿到10亿条之间，比较合适。

这个坑，让我认识到，分区键的设计，也很重要，要根据数据量和查询模式，选择合适的粒度，不要太粗，也不要太细。太粗，数据倾斜，查询慢；太细，分区太多，管理开销大。

三、踩坑三：JOIN操作，性能差到怀疑人生

第三个坑，是JOIN操作，性能非常差，差到怀疑人生。

ClickHouse虽然支持JOIN，但它的JOIN，和MySQL等关系型数据库的JOIN，不一样，性能也差很多。因为ClickHouse是分布式的，JOIN的时候，需要把数据在节点之间传输，开销很大。而且，ClickHouse的JOIN，是把右表加载到内存里，然后和左表做匹配，如果右表很大，内存就会爆掉。

我们最开始，不了解ClickHouse的JOIN特性，写了一个大表JOIN大表的查询，两个表，都有几十亿条数据，结果，查询跑了一个多小时，还没出结果，最后，内存爆了，查询失败了。

我排查了很久，查了很多文档，才知道，ClickHouse的JOIN，有很多限制和注意事项：

  1. 右表不能太大：ClickHouse的JOIN，是把右表加载到内存里的，所以右表不能太大，最好是小表，或者经过过滤之后的小表。如果右表很大，就会占用很多内存，甚至OOM。
  1. 大表JOIN大表，要避免：如果两个表都很大，最好不要用JOIN，可以考虑在数据导入的时候，就把数据关联好，存成一张宽表，查询的时候，直接查宽表，不需要JOIN。ClickHouse是OLAP数据库，适合存宽表，不适合做复杂的JOIN。
  1. 用GLOBAL JOIN：如果是分布式表，普通的JOIN，会把右表发到每个节点，每个节点都做一次JOIN，数据传输量很大。用GLOBAL JOIN，会先把右表在一个节点汇总，然后再发到各个节点，数据传输量小一些，性能更好。
  1. 注意JOIN的顺序：ClickHouse的JOIN，左表是驱动表，右表是被加载到内存的表。所以，要把大表放在左边，小表放在右边，这样，小表加载到内存，占用内存少，大表作为驱动表，扫描数据。如果搞反了，把大表放在右边，就会OOM。
  1. 用字典（Dictionary）代替JOIN：如果右表是维度表，数据量不大，可以考虑把它做成字典，查询的时候，用dictGet函数，获取维度信息，比JOIN快很多，也不占内存。

了解了这些之后，我们把那个大表JOIN大表的查询，改成了宽表，在数据导入的时候，就把两个表关联好，存成一张宽表，查询的时候，直接查宽表，不需要JOIN。改完之后，查询性能，从一个多小时，降到了几百毫秒，提升了成千上万倍。

这个坑，让我深刻认识到，ClickHouse不是关系型数据库，不要用关系型数据库的思维，来用ClickHouse。ClickHouse适合存宽表，做聚合查询，不适合做复杂的JOIN。能用宽表解决的，就不要用JOIN；必须用JOIN的，也要注意JOIN的方式和顺序，避免大表JOIN大表。

四、踩坑四：数据去重，不是你想的那样

第四个坑，是数据去重，ClickHouse的去重，和我们想象的不一样，稍不注意，就会有重复数据。

我们的数据，是从Kafka导入的，用的是ClickHouse的Kafka引擎，自动消费Kafka的数据，写入到MergeTree表。因为Kafka可能会有重复消息，所以，我们需要对数据去重。

最开始，我们用的是ReplacingMergeTree引擎，以为用了这个引擎，就会自动去重，不会有重复数据。结果，用了一段时间之后，发现数据里，还是有很多重复的，查询的时候，count一下，发现数据量，比预期的多了很多。

我排查了很久，才发现，ReplacingMergeTree的去重，不是实时的，而是在合并的时候，才会去重。在合并之前，重复数据，还是存在的，查询的时候，还是会查到。而且，即使合并了，也不能保证所有的重复数据，都被去掉了，因为合并是在后台异步进行的，可能有些分区，还没合并。

而且，ReplacingMergeTree去重的时候，是按ORDER BY的字段去重的，ORDER BY字段相同的，会保留最后一条（或者按版本字段，保留版本最大的一条）。如果你的ORDER BY字段，设计得不对，去重就会出错。

了解了这些之后，我们才知道，ReplacingMergeTree，不能保证实时去重，也不能保证完全去重，它只是在合并的时候，尽量去重。如果需要严格去重，需要在查询的时候，加上FINAL关键字，强制在查询的时候，做去重，但这样，查询性能会差很多。

或者，用SummingMergeTree，或者在数据导入的时候，就做去重，保证导入的数据，没有重复。

我们最后，是在数据导入的时候，加了一个去重的步骤，用Redis记录已经导入的消息ID，导入的时候，先检查Redis，如果已经导入过了，就跳过，这样，保证导入的数据，没有重复。虽然这样，导入速度慢了一些，但数据准确了。

这个坑，让我认识到，ClickHouse的去重，不是实时的，也不是完全可靠的，不要以为用了ReplacingMergeTree，就万事大吉了。如果对数据准确性要求高，需要在导入的时候，就做去重，或者查询的时候，用FINAL关键字，但要注意性能影响。

五、踩坑五：NULL值处理，一不小心就出错

第五个坑，是NULL值的处理，ClickHouse对NULL值的处理，和其他数据库不一样，一不小心，就会出错。

我们的数据里，有一些字段，是可能为NULL的，比如用户的手机号、邮箱等，有些用户填了，有些没填，就是NULL。最开始，我们建表的时候，这些字段，用的是Nullable类型，允许为NULL。

结果，用了一段时间之后，发现查询结果，经常不对，比如，统计手机号不为空的用户数，结果比预期的少很多；按手机号分组，发现NULL值的分组，数据不对。

我排查了很久，才发现，ClickHouse对NULL值的处理，有很多特殊的地方：

  1. NULL值不参与索引：Nullable类型的字段，NULL值是不参与索引的，查询的时候，过滤条件是IS NULL或者IS NOT NULL，都没法利用索引，需要全表扫描，性能很差。
  1. NULL值的比较：在ClickHouse里，NULL和任何值比较，结果都是NULL，不是0也不是1。比如，NULL = '123'，结果是NULL，不是0；NULL != '123'，结果也是NULL。这和其他数据库不一样，很容易出错。
  1. 聚合函数对NULL的处理：count()会统计所有行，包括NULL值的行；count(字段)只会统计字段不为NULL的行。如果字段是Nullable的，count(字段)的结果，会比count()少，这一点，和其他数据库一样，但很容易被忽略。
  1. NULL值的存储开销大：Nullable类型的字段，除了存储值本身，还要存储一个标记位，标记是不是NULL，所以，存储开销比非Nullable类型大。而且，Nullable类型的字段，性能也比非Nullable类型差。

了解了这些之后，我们把那些可能为NULL的字段，都改成了非Nullable类型，用默认值代替NULL，比如，手机号为空的，用空字符串''代替；数值为空的，用0或者-1代替。这样，既避免了NULL值的各种问题，也提升了查询性能，减少了存储开销。

当然，不是所有情况，都能用默认值代替NULL，有些场景，确实需要区分NULL和默认值，这时候，还是要用Nullable类型，但要注意NULL值的各种坑，查询的时候，要特别小心。

这个坑，让我认识到，ClickHouse的NULL值处理，和其他数据库不一样，有很多特殊的地方，稍不注意，就会出错。如果可能，尽量不要用Nullable类型，用默认值代替NULL；如果必须用，就要特别注意NULL值的比较、聚合、索引等问题。

六、踩坑六：分布式表写入，数据重复和丢失

第六个坑，是分布式表的写入，一不小心，就会导致数据重复或者丢失。

我们的ClickHouse集群，是3个节点，用的是分布式表，数据分片存储在各个节点上。最开始，我们写入数据的时候，是直接写分布式表，以为这样，ClickHouse会自动把数据分片，写到各个节点上。

结果，用了一段时间之后，发现数据有重复，也有丢失。有些数据，在多个节点上都有；有些数据，哪个节点上都没有。查询的时候，数据量忽多忽少，非常不稳定。

我排查了很久，才发现，直接写分布式表，有很多问题：

  1. 写入性能差：直接写分布式表，数据会先写到一个节点，然后这个节点，再把数据分发到其他节点，多了一次网络传输，写入性能差，而且，中间节点的压力很大。
  1. 容易重复和丢失：如果在数据分发的过程中，某个节点故障，或者网络异常，就可能导致数据重复或者丢失。因为分布式表的写入，不是事务性的，中间出了问题，没有回滚机制，就会导致数据不一致。
  1. 顺序无法保证：直接写分布式表，数据的顺序，无法保证，因为数据是异步分发的，先写的数据，可能后到，后写的数据，可能先到。如果对数据顺序有要求，就会出问题。

了解了这些之后，我们改成了，直接写本地表，而不是写分布式表。就是在客户端，做分片，根据分片键，计算出数据应该写到哪个节点，然后直接连接那个节点，写它的本地表。这样，写入性能好，也不会有重复和丢失的问题，因为数据直接写到目标节点，不需要中间转发。

当然，这样，客户端就需要知道集群的拓扑结构，需要自己做分片逻辑，稍微复杂一点。但为了数据的准确性和写入性能，这点复杂度，是值得的。

另外，我们还加了数据校验，写入之后，定期检查各个节点的数据量，和源数据对比，确保没有重复和丢失。如果发现问题，及时修复。

这个坑，让我认识到，ClickHouse的分布式表，查询的时候很好用，但写入的时候，最好不要直接写分布式表，而是直接写本地表，在客户端做分片。这样，写入性能好，数据也准确。如果直接写分布式表，很容易出现数据重复和丢失的问题。

七、踩坑七：内存不足，查询频繁OOM

第七个坑，是内存不足，查询频繁OOM（Out Of Memory）。

ClickHouse的查询，是很吃内存的，尤其是聚合、排序、JOIN等操作，需要大量的内存。如果内存不够，查询就会OOM，直接失败。

我们最开始，给每个ClickHouse节点，配了32G内存，以为够用了。结果，用了一段时间之后，发现很多查询，都OOM了，尤其是一些复杂的聚合查询，或者数据量比较大的查询，经常跑到一半，就因为内存不足，失败了。

我排查了很久，发现有几个原因，导致内存不足：

  1. 分组（GROUP BY）占用大量内存：ClickHouse的GROUP BY，是把分组的key和聚合的结果，存在内存里的，如果分组的key很多，比如，按userid分组，有几千万个不同的userid，就会占用大量的内存，甚至OOM。
  1. 排序（ORDER BY）占用大量内存：ClickHouse的ORDER BY，如果数据量很大，排序的时候，需要把数据加载到内存里，如果内存不够，就会OOM。虽然ClickHouse支持外部排序，把数据写到磁盘上排序，但性能会差很多。
  1. JOIN占用大量内存：前面说过，ClickHouse的JOIN，是把右表加载到内存里的，如果右表很大，就会占用大量内存，甚至OOM。
  1. 并发查询太多：如果同时有很多查询在跑，每个查询都占用一些内存，加起来，就会超过总内存，导致OOM。

找到原因之后，我们做了以下优化：

  1. 增加内存：最直接的方法，就是增加内存，把每个节点的内存，从32G，升到了128G。内存大了，能处理的查询就多了，OOM也少了。
  1. 优化GROUP BY：对于分组key很多的查询，尽量减少分组的维度，或者用近似算法，比如uniqHLL12，代替uniqExact，用更少的内存，计算近似的去重数。
  1. 优化ORDER BY：排序的时候，尽量只排序需要的字段，不要排序大字段。如果数据量很大，可以用LIMIT，只排序前N条，减少内存占用。
  1. 避免大表JOIN：前面说过，尽量用宽表代替JOIN，必须用JOIN的，右表要小，或者用字典代替。
  1. 限制并发查询数：配置maxconcurrentqueries，限制同时运行的查询数，避免太多查询同时跑，把内存占满。
  1. 配置内存限制：配置maxmemoryusage，限制每个查询的最大内存使用量，超过就报错，避免一个查询，把所有内存都占了，影响其他查询。

经过这些优化之后，OOM的问题，基本解决了，查询也稳定了很多。

这个坑，让我认识到，ClickHouse是很吃内存的，尤其是聚合、排序、JOIN等操作，需要大量的内存。所以，用ClickHouse，内存一定要给够，不要太小。同时，也要优化查询，减少内存占用，避免不必要的大查询。

八、写在最后

今天，分享了我在使用ClickHouse过程中，踩过的七个坑，包括ORDER BY设计、分区键设计、JOIN操作、数据去重、NULL值处理、分布式表写入、内存不足等。这些坑，有些让我熬了好几个通宵，才排查清楚，印象非常深刻。

当然，ClickHouse的坑，远不止这些，还有很多，比如，数据类型转换、索引使用、合并优化、备份恢复、集群运维等，每个方面，都有很多需要注意的地方。以后有机会，再继续分享。

虽然ClickHouse有很多坑，但不可否认，它确实是一款非常优秀的OLAP数据库，查询性能极致，安装简单，运维成本低，SQL支持完善，对于数据分析场景来说，是非常好的选择。只要我们了解了它的特性，避开了这些坑，就能发挥它的最大威力。

最后，给大家几点使用ClickHouse的建议：

  1. 建表之前，仔细分析查询模式：ORDER BY、分区键、主键等，都要根据查询模式来设计，建表之前，一定要想清楚，不要等建完表，发现不对了，再改，那就太麻烦了。
  1. 尽量用宽表，少用JOIN：ClickHouse适合存宽表，做聚合查询，不适合做复杂的JOIN。能用宽表解决的，就不要用JOIN。
  1. 注意数据准确性：ClickHouse的很多操作，比如去重、分布式写入等，都不是完全可靠的，对数据准确性要求高的场景，要在导入和查询的时候，做额外的校验和处理。
  1. 内存要给够：ClickHouse很吃内存，内存一定要给够，不要太小。同时，也要优化查询，减少内存占用。
  1. 多测试，多验证：上线之前，一定要用真实的数据量，做充分的测试，验证查询性能、数据准确性、稳定性等，不要等上线了，才发现问题。

希望这篇踩坑记，能帮助大家少走弯路，用好ClickHouse。如果有什么问题或者不同的看法，欢迎在评论区交流。