Clickhouse专业避坑指南&最佳实践

原文地址：Clickhouse专业避坑指南&最佳实践

前言

本文通过列举一些用户在使用Clickhouse时经常遇到的问题，通过分析这些问题并提供最佳实践方式，从而帮助读者更好的将Clickhouse融入到生产环境中。

1.数据块过多

这是ClickHouse中经常出现的一个错误，通常是因为CickHouse的使用方式不正确、没有遵循最佳实践的方式进行数据写入，触发这个错误，并且会出现在ClickHouse日志或插入请求的响应中。要理解这个错误，用户需要对ClickHouse中“数据块”（data part）的概念有一个基本的了解。

在ClickHouse中，一个表由用户指定的主键排序的数据块组成，默认情况下，在创建表时通过ORDER BY子句指定。当数据插入表中时，会创建单独的数据块，并且每个数据块都按照主键的字典顺序进行排序。例如，如果主键是(CounterID, Date)，则数据块中的数据首先按CounterID进行排序，然后在每个CounterID值内按Date进行排序。在后台，ClickHouse合并数据块以实现更高效的存储，类似于日志结构化合并树（Log-structured merge tree）。每个数据块都有自己的主索引（ primary indexes），以便于高效地扫描和确定值在块中的位置，当块合并时，合并后的块的主索引也会合并。

随着数据块数量的增加，查询速度必然会减慢，因为需要评估更多的索引并读取更多的文件。如果块数量很高，则用户可能还会在启动时遇到缓慢的情况。因此，创建过多的数据块会导致更多的内部合并和“压力”，以保持块数量低和查询性能高。虽然合并是并发的，在误用或配置不当的情况下，数据块数量可能超过内部可配置的限制（相关的配置项为parts_to_throw_insert与max_parts_in_total）。虽然可以调整这些限制，但会牺牲查询性能，需要读者根据场景进行取舍。块数量过多除了导致查询性能下降外，还可能在复制配置中对ClickHouse Keeper产生更大的压力。

为什么你创建的表会出现数据块过多错误呢？以下列举集中原因以及解决方案。

选择错误的分区键 

使用过高基数的分区键是是导致数据块过多的原因之一。在创建表时，用户可以指定一个列作为分区键，用于分隔数据。每个键值将创建一个新的文件系统目录。这通常是一种数据管理技术，允许用户在表中逻辑地清晰分离数据，例如按天分割。随后的操作，如DROP PARTITION，允许快速删除数据子集。然而，这个强大的功能很容易被误用，用户将其解释为查询的简单优化技术。值得注意的是，属于不同分区的数据部分永远不会被合并。如果选择了基数较高的键，例如date_time_ms，则分散在数千个文件夹中的数据部分永远不会成为合并候选项，超过预配置的限制，导致后续插入时出现可怕的“Too many inactive parts (N). Parts cleaning are processing significantly slower than inserts”错误。解决这个问题很简单：选择一个基数小于1000的合理分区键。

频繁的写入

除了选择错误的分区键外，频繁的小批量数据写入也可能引起数据块过多错误。每次向ClickHouse写入数据都会导致一个写入块被转换为一个数据块。为了使块数量可控，用户应该在客户端缓冲数据并批量写入数据——最好是至少1k行（甚至10k行），尽管这应该进行调整。如果客户端缓冲不可行，用户可以通过异步写入将此任务推迟到ClickHouse。在这种情况下，ClickHouse将在本地磁盘上缓冲写入，然后将它们合并在一起，以便写入到底层表中。

可以选择缓存表（Buffer tables）作为一种替代方案，但是由于它们在内存中保存写入操作，直到刷新发生，所以它们比异步插入方法不太具有容错性。它们确实比异步插入具有一些优点——主要是在缓冲期间可以查询数据以及它们作为物化视图的目标表的缓冲兼容性。

滥用物化视图

数据块过多的其他可能原因是滥用物化视图造成的。物化视图实际上是在块插入到表中时运行的触发器。它们会对数据进行转换，例如通过GROUP BY，在将结果插入到另一个表中之前进行聚合。通常使用此技术加速某些查询，通过预计算插入时间的聚合来实现。用户可以创建这些物化视图，可能导致许多数据块。一般来说，我们建议用户在创建视图时了解成本，并在可能的情况下进行合并。

以上只是列举了造成数据块过多的部分原因。例如，突变（mutations ）可能导致合并压力和块堆积。最后，我们应该注意，这个错误虽然最常见，但只是上述错误配置的一个表现。例如，用户可能会因为选择了不合适的分区键而遇到其他问题。例如：文件系统上没有可用的inode、备份时间过长以及复制延迟（和ClickHouse Keeper的高负载）。

2.过早地进行横向扩展

许多用户在规划Clickhouse时，一开始就着手将集群扩展到几十甚至上百个节点。虽然像Kubernetes这样的技术使得部署多个无状态应用程序实例相对简单，但在几乎所有情况下，这种模式不应该在ClickHouse中使用。与其他数据库可能因为内在限制（如JVM堆大小）而限制在机器尺寸上不同，ClickHouse从一开始就被设计成充分利用机器的全部资源。我们经常发现成功的ClickHouse部署在配备数百个核心、数千兆字节的RAM和拥有PB级磁盘空间的服务器上。大多数分析查询都有排序、过滤和聚合阶段。每个阶段可以独立并行化，并且默认情况下将使用与核心数量相同的线程，从而利用查询的全部机器资源。

首先垂直扩展有很多好处，主要包括成本效益、拥有成本（关于运维）较低以及查询性能更好，因为在JOIN等操作中数据在网络上传输的量更小。当然，用户需要在基础设施中具备冗余，但是两台机器对于除最大的用例之外的所有用例应该足够了。简而言之，在进行水平扩展之前先进行垂直扩展！

3.变更的痛点

在OLAP用例中很少出现需要修改数据的情况。但是，有时候这个需求是不可避免的。为了解决这个需求，ClickHouse提供了变更（mutation）功能，允许用户通过ALTER查询修改插入的数据。ClickHouse在不可变数据上表现最佳，任何需要在插入后更新数据的设计模式都应该仔细审查。

在内部，变更通过重新编写整个数据块来实现。这个过程依赖于与合并相同的线程池。还要注意，默认情况下变更需要在所有副本上应用。因此，变更既消耗CPU，又消耗IO，并且应该谨慎安排，只有管理员才能运行。由于变更会导致合并队列的堆积，从而导致之前提到的“数据块过多”的问题。此外，用户可能会遇到复制延迟。system.mutations表为管理员提供当前变更的进度信息。请注意，变更可以取消，但不能回滚，可通过KILL MUTATION查询实现。

ClickHouse中的去重 

我们经常看到用户需要调度合并作业以处理重复数据。通常，我们建议用户在插入ClickHouse之前解决这个问题，并进行去重。如果这不可行，用户有几个选择：在查询时进行去重，或利用ReplacingMergeTree。

在查询时进行去重可以通过在唯一标识行的字段上对数据进行分组，并使用带有日期字段的argMax函数来确定其他字段的最后一个值。ReplacingMergeTree允许在合并时对具有相同排序键（ORDER BY键）的行进行去重。请注意，这只是“尽力而为”的功能：有时合并进程会在非确定性间隔执行。因此，它不能保证不存在重复项。用户还可以在SELECT时使用FINAL修饰符强制进行此去重（此功能需谨慎使用，因为它需要消耗大量资源并可能很慢），或通过OPTIMIZE FINAL在磁盘上强制合并。

如果需要从ClickHouse中删除数据（例如出于合规性或去重原因），用户还可以使用轻量级删除而不是变更。采用DELETE语句的形式，接受WHERE子句来过滤行。仅将行标记为已删除，这些标记将在查询时用于过滤行，并在合并部件时删除。

Note: 此功能为实验性质，并需要设置 SET allow_experimental_lightweight_delete = true;。在大多数情况下，它比使用mutation更有效，但如果你要进行大规模批量删除，则有例外情况。

4.谨慎复杂类型

除了支持通常的基本类型外，ClickHouse 还支持 Nested、Tuple、Map 甚至 JSON 等复杂类型。这些类型有其支持的好处——有时候，没有其他方式来建模数据，但我们建议尽可能的使用基本类型，因为它们提供了最佳的插入和查询时间性能。

例如，我们最近看到用户热衷于利用 22.4 版本中添加的 JSON 功能。这个强大的功能允许表模式从数据中动态推断出来，避免了用户需要指定列类型的需求。但是要谨慎使用这个功能，不要用它来代替明确指定列。具体来说，这个功能有一些用户应该注意的限制：

• 插入时间成本增加，因为需要动态创建列；

• 类型使用不够优化，即没有编解码器和不必要的 Nullable 使用；

• 无法将 JSON 列用作主键。

最后两个限制都会导致较差的压缩和查询/插入性能。不要将其用于所有行，而是选择性地使用该特定类型的列，例如 Kubernetes 标签，其中数据可能会发生变化。总之，如果你知道你的模式，请明确指定它！

注意：JSON Object 类型是实验性的，正在进行改进。这个功能的建议正在不断发展，因此可能会在以后的版本中更改。

此外，我们经常看到用户使用 Nullable 类型。这允许将值 Null 与类型的默认值区分开。这可能很有用，但需要一个额外的 Uint8 列来确定哪些值为空。这会产生与存储相关的额外字节（虽然它会压缩得很好），并增加查询时间的开销。只有在真正需要时才使用 Nullable！

5.糟糕的主键选择

新手使用ClickHouse时，通常会难以完全理解其独特的主键概念。与针对快速定位特定行进行优化的基于B(+)-Tree的OLTP数据库不同，ClickHouse使用了一种稀疏索引，旨在支持每秒数百万行的插入速度和百万亿级别的数据集。与OLTP数据库不同，这个索引依赖于磁盘上的数据排序，以快速识别可能与查询匹配的行组 ，这是分析查询中的常见需求。实际上，该索引允许在将匹配部分的分段文件流式传输到处理引擎之前快速识别它们。有关磁盘上数据布局的更多详细信息，请参见官方文档：data-is-stored-on-disk-ordered-by-primary-key-columns

https://clickhouse.com/docs/en/optimize/sparse-primary-indexes#data-is-stored-on-disk-ordered-by-primary-key-columns

这种方法的有效性，无论是对于查询性能还是压缩，都依赖于用户在创建表时通过ORDER BY子句选择良好的主键列。一般来说，用户应选择他们经常过滤的列作为主键，这系列通常不会超过2到3个。这些列的顺序很重要，可能会影响第一个条目以外的列的压缩和过滤。为了在查询中高效地过滤次要关键列和表列文件的压缩率，最好将主键中的列按基数升序排列。有关原因的完整解释可以查询官方文档：sparse-primary-indexes

https://clickhouse.com/docs/en/optimize/sparse-primary-indexes

6.滥用跳数索引

跳数索引（Data Skipping indices）是 ClickHouse 中一种加速查询的工具，但不应该被滥用。虽然主键通常是加速查询的首选工具，但是表只能有一个主键，查询访问模式可能会使主键失效，即对于多种用例，无法高效利用主键的查询是不可避免的。在这种情况下，当应用 WHERE 子句条件时，ClickHouse 可能被迫对每个列执行全表扫描。通常情况下，这仍然足够快，但在某些情况下，用户会使用跳数索引，希望以此轻松加速这些查询。

这些索引添加了数据结构，允许 ClickHouse 跳过那些已经被保证没有匹配值的大块数据的读取。更具体地说，它们在块粒度上创建索引（实际上是标记），允许在 WHERE 子句不满足时跳过这些块。

这种情况下，这些索引可能会加速特定查询，但通常被滥用，不直观，并且需要仔细设计才能发挥作用。因此，我们经常看到它们只是复杂化表设计，并降低插入性能，而很少（即使有）提高查询性能。因此，在确定使用跳数索引前，建议读者先研究官方文档：

https://clickhouse.com/docs/en/optimize/skipping-indexes#skip-best-practiceshttps://clickhouse.com/docs/en/optimize/sparse-primary-indexes#note-about-data-skipping-index

在大多数情况下，只有在尝试了其他替代方案之后才应该考虑使用跳数索引——具体而言，应在修改主键（参见创建其他主索引的选项）、使用Project或物化视图等其他替代方案之后才考虑使用此高级功能。参考官方文档：options-for-creating-additional-primary-indexes

https://clickhouse.com/docs/en/optimize/sparse-primary-indexes#options-for-creating-additional-primary-indexes

一般来说，只有在主键和目标非主键列/表达式之间存在强烈的相关性时，才考虑跳数索引。在没有任何实际相关性的情况下，跳数索引将匹配大多blocks——导致所有标记被读入内存并进行评估。在这种情况下，徒增索引成本，但没有任何好处，实际上降低了全表扫描的速度。

7.LIMIT语句不总是能短路+点查

我们经常发现OLTP用户在开始使用ClickHouse时会使用LIMIT子句来通过限制返回结果数量来优化查询。如果是从OLTP数据库转过来的，这种做法应该是直观的：返回的数据越少，结果就越快，对吗？也对也不对。

这种技术的有效性取决于查询是否能以流式方式运行。例如，SELECT * FROM table LIMIT 10 这样的查询只会扫描前几个部分的几个数据块，然后找到10个结果后返回给用户。这也适用于用户按主键字段排序的情况，因为 optimize_in_read_order 默认为1。然而，如果用户运行 SELECT a from table ORDER BY b LIMIT N，其中表按a而不是b排序，ClickHouse将无法避免读取整个表，即查询无法提前终止。

对于聚合，情况会更加复杂。除非用户按主键进行分组并设置 optimize_aggregation_in_order=1，否则还是需要进行全表扫描。在这种情况下，一旦获得了足够的结果，就会发送一个传播信号。只要查询的前面几个步骤能够以流式方式处理数据，例如过滤，这种机制就能起作用，查询就会提前终止。但通常情况下，聚合必须消耗所有的表数据才能返回并应用LIMIT作为最后一个阶段。

以一个例子来说明，我们使用包含2755万行的 UK Property Price Paid 教程中的表格进行创建和数据加载。

当 optimize_aggregation_in_order=0 时，对主键进行分组的以下聚合查询需要在应用 LIMIT 1 之前进行全表扫描：

clickhouse-cloud :) SELECT    postcode1, postcode2,    formatReadableQuantity(avg(price)) AS avg_priceFROM uk_price_paidGROUP BY postcode1, postcode2LIMIT 1;
┌─postcode1─┬─postcode2─┬─avg_price───────┐│ AL4       │ 0DE       │ 335.39 thousand │└───────────┴───────────┴─────────────────┘ Elapsed: 3.028 sec, read 27.55 million rows, 209.01 MB.

设置 optimize_aggregation_in_order=1 后，查询能够进行优化，从而处理更少的数据：

clickhouse-cloud :) SELECT       postcode1, postcode2,       formatReadableQuantity(avg(price))  AS avg_priceFROM uk_price_paidGROUP BY postcode1, postcode2LIMIT 1SETTINGS optimize_aggregation_in_order = 1; ┌─postcode1─┬─postcode2─┬─avg_price───────┐│ AL4       │ 0DE       │ 335.39 thousand │└───────────┴───────────┴─────────────────┘ Elapsed: 0.999 sec, read 4.81 million rows, 36.48 MB.

在集群环境中，我们还经常看到即使是有经验的用户也会被LIMIT的不太明显的行为所困扰，尤其是在表具有多个分片（shard）的情况下。分片允许用户将数据拆分或复制到多个ClickHouse实例中。当查询带有LIMIT N子句的分片表时，例如通过分布式表发送查询时，该子句将向下传播到每个分片。每个分片都需要汇总前N个结果，并将它们返回给协调节点。当用户运行需要全表扫描的查询时，这可能会特别消耗资源。通常，这些是“点查找”，即查询旨在仅识别少量行的情况。虽然在ClickHouse中可以通过精心的索引设计来实现这一点，但非优化版本结合LIMIT子句可能会非常消耗资源。

8.只读表

这种情况发生在集复制环境中，当一个节点失去与ZooKeeper的连接时。通常，这几乎总是ZooKeeper问题的结果。虽然ClickHouse Keeper的发布解决了与ZooKeeper相关的许多问题，但这个组件的资源不足仍然会导致这个问题出现。常见原因是将Keeper托管在与ClickHouse相同的主机上，或者是ZooKeeper JVM资源调整不当。这通常可以通过确保该组件在专用硬件上分离并获得足够的资源来轻松解决。

9.查询的内存限制超出

对于新用户来说，ClickHouse经常会像魔术一样，即使在最大的数据集和最雄心勃勃的查询上，每个查询都超级快。然而，现实世界的使用不可避免地会考验ClickHouse的极限。查询超过内存限制可能是多种原因导致的。最常见的情况是高基数字段上的大型连接或聚合操作。如果性能至关重要，并且确实需要这些查询，可以通过简单地扩展集群规模，以确保查询保持响应性。但是，我们也明白，这有时并不是轻松的，因此，退而求其次，用户有以下几个选项。

Aggregations（聚合）

对于内存密集的聚合或排序场景，用户可以使用max_bytes_before_external_group_by和max_bytes_before_external_sort设置。max_bytes_before_external_group_by确保任何聚合可以在超过内存阈值时“溢出”到磁盘上。这肯定会影响查询性能，但可以帮助确保查询不会OOM。max_bytes_before_external_sort排序设置帮助解决内存密集的排序类似问题。这在分布式环境中尤为重要，因为协调节点会从子分片接收已排序的响应。在这种情况下，协调服务器可能被要求对大于其可用内存的数据集进行排序。使用max_bytes_before_external_sort，可以允许排序溢出到磁盘上。此设置对于在GROUP BY之后具有ORDER BY且带有LIMIT的情况尤其有用，尤其是在查询被分发的情况下。

Joins（连接）

对于JOIN操作，用户可以选择不同的JOIN算法，以帮助降低所需内存。默认情况下，JOIN使用hash join算法，它在功能上最为完整，通常具有最佳性能。该算法将JOIN操作的右侧表加载到内存hash表中，然后针对左侧表进行评估。为了最小化内存占用，用户应该将较小的表放在右侧。然而，在内存受限的情况下，这种方法仍然有限制。在这些情况下，可以通过join_algorithm设置启用partial_merge join。这种派生自sort-merge算法的JOIN算法首先将右侧表按块进行排序并创建min-max索引。然后按照JOIN键对左侧表的部分进行排序，并在右侧表上进行JOIN操作。min-max索引用于跳过不需要的右侧表块。相较于性能，这种方法的内存占用较小。更进一步，full_sorting_merge算法允许在右侧非常大且无法适应内存且无法进行查找（例如，复杂子查询）的情况下进行JOIN操作。在这种情况下，如果右侧和左侧的表无法适应内存，则它们都在磁盘上进行排序，以允许连接大型表。

自20.3版本开始，ClickHouse支持join_algorithm设置的auto值。这会指示ClickHouse采用自适应连接方法，在内存限制被违反时首选hash连接算法，然后尝试使用partial_merge算法。最后，关于连接，我们鼓励读者了解分布式连接的行为以及如何最小化它们的内存消耗，参考这部分文档：distributed-subqueries

https://clickhouse.com/docs/en/sql-reference/operators/in#distributed-subqueries

异常查询

内存问题的其他原因是没有对用户进行限制。在这些情况下，我们会看到用户发出没有配额或查询复杂性限制的异常查询。如果将 ClickHouse 实例暴露给广泛和多样化的用户，那就很有必要的对用户进行资源限制。

ClickHouse 近期还引入了新的内存过度承诺功能。在以往，一个Clickhouse查询会受到 max_memory_usage 设置（默认为10GB）的限制，这是一个硬性的和相当粗略的限制。用户可以提高这个设置，但可能会牺牲单个查询的性能，可能影响其他用户。而内存过度承诺允许更多的内存密集型查询运行，前提是有足够的资源。当达到最大服务器内存限制时，ClickHouse 将确定哪些查询超过了内存限制并尝试终止查询。这可能不是触发此条件的查询。如果不是，查询将等待一段时间，以允许高内存查询被杀死，然后继续运行。这使得低内存查询总是能够运行，而更加强大的查询可以在服务器空闲时运行，并且有足够的资源。这种行为可以在服务器和用户级别进行调整。

使用物化视图 

物化视图是ClickHouse的一个强大功能，通过在插入时允许重新定向和转换数据，用户可以为特定查询进行优化。当需要超过单个主索引时，我们经常看到用户使用这种技术。物化视图存在许多常见问题，以下是最常见的问题总结：

我们经常看到用户误解物化视图的工作原理。物化视图不了解源表数据，实际上只是插入触发器，能够在插入的data blocks上运行，它们无法看到合并、分区删除或突变。如果用户更改源表，则必须同时更新任何已连接的物化视图，没有功能可以使这些保持同步。

单个表添加太多的物化视图会引起性能问题，一个表超过50个物化视图通常达到性能瓶颈，会导致插入变慢。除了计算开销外，每个物化视图还会从它运行的data block中创建一个新的数据块——可能会导致前面讨论过的“数据块过多”问题。请注意，通过设置parallel_view_processing，可以通过并行运行视图来改善性能。

状态函数（State functions）是ClickHouse的一个引人注目的功能，允许使用聚合函数对数据进行汇总，以便进行后续查询。拥有许多这些状态函数的物化视图，特别是计算分位状态的视图，可能会占用CPU资源，导致插入变慢。

我们经常看到用户将目标聚合/求和合并树的列与物化视图的列不匹配。目标表的ORDER BY子句必须与物化视图中SELECT子句的GROUP BY一致。如果这些不一致，则会发现不一致的列的数据将被破坏。下面是正确的示例：

CREATE MATERIALIZED VIEW test.basicENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)AS SELECT   CounterID,   StartDate,   sumState(Sign) AS Visits,   uniqState(UserID) AS UsersFROM test.visitsGROUP BY CounterID, StartDate;

CREATE MATERIALIZED VIEW test.summing_basicENGINE = SummingMergeTreePARTITION BY toYYYYMM(d)ORDER BY (CounterID, StartDate)AS SELECT CounterID, StartDate, count() AS cntFROM sourceGROUP BY CounterID, StartDate

与上文类似，物化视图的 SELECT 语句中的列名必须与目标表的列名匹配，列的顺序可以不一样。可以使用别名来确保它们匹配。请注意，目标表可以具有默认值，因此视图的列可以是目标表的子集。以下是一个正确的示例 - 请注意需要将 count() 重命名为 counter：

CREATE MATERIALIZED VIEWtest.mv1 (timestamp Date, id Int64, counter Int64)ENGINE = SummingMergeTreeORDER BY (timestamp, id)ASSELECT timestamp, id, count() as counterFROM sourceGROUP BY timestamp, id;

10.实验性功能

在ClickHouse中，我们定期发布新功能。在某些情况下，新功能被标记为“实验性的”（experimental），这意味着它们需要在实际使用和社区反馈的一段时间内受益。最终，这些功能会发展到被认为“准备就绪”（production ready）或如果它们并不普遍有用或者有其他方法可以实现原始目标，则会被弃用。虽然我们鼓励用户尝试实验性功能，但是不建议在生产环境环境上使用。

我们在文档中将所有功能标记为实验性的，任何使用都需要用户设置启用特定的实验性功能，例如SET allow_experimental_lightweight_delete = true。

总结

本文介绍了如何在生产环境中管理ClickHouse集群，并避免常见问题。管理拥有PB级数据的ClickHouse集群肯定会带来挑战，即使是经验丰富的操作员也会面临这些挑战。为了避免这些挑战并仍然体验ClickHouse的速度和强大功能，请结合本文与官方文档进行使用。

- THE END -