刚开始接触ClickHouse？12个最容易犯的错误以及避坑方法

本文字数：11600；估计阅读时间：28 分钟

审校：庄晓东（魏庄）

在ClickHouse，我们始终在思考我们的入门体验以及如何帮助用户在尽可能短的时间内从我们的产品中获得价值。虽然大多数用户都有一个流畅的上手经验，但我们意识到ClickHouse是一个复杂的软件，并且引入了很多新的概念。加上大规模管理ClickHouse的挑战，这也是我们开发serverless ClickHouse解决方案的原因之一，它能自动处理许多常见的入门问题和后续扩展方面的挑战。

然而，有些问题仅仅是由于配置错误或更常见的是对ClickHouse行为和功能的误解。在这篇文章中，我们突出了新手用户遇到的最常见的12个问题，这些问题是由于在使用ClickHouse的过程中，不遵循最佳实践，甚至反最佳实践而导致的。对于每一个问题，我们都推荐了一个解决方案或正确的使用方法。

这是一个常见的ClickHouse错误，通常是不正确的使用方式和不遵循最佳实践导致。当插入数据时，用户经常会遇到这个错误，并且这个错误会出现在ClickHouse的日志中或作为一个INSERT请求的响应返回给客户端。为了理解这个错误，用户需要对ClickHouse中的part概念有一个基本的了解。

ClickHouse中的表，是由主键排序后的data parts组成（默认情况下，在创建表时按ORDER BY子句排序）。当数据被插入到一个表中时，会创建单独的part，每个part都按主键的字典顺序排序。例如，如果主键是 (CounterID, Date) ，则part中的数据首先按 CounterID 排序，然后在每个 CounterID 值内按 Date 排序。在后台，ClickHouse合并(merge) data part以实现更高效的存储，类似于Log-structured merge tree。每个part都有自己的primary索引，以实现高效地扫描和定位数据在parts中的位置。当parts合并时，primary索引也会合并。

随着parts的数量增加，由于需要扫描更多的索引和读取更多的文件，查询不可避免地会变慢。在part数量较多的情况下，用户也可能有启动时间较慢的体感。创建过多的parts会导致需要进行更多的内部合并，以此来保持较低数量的parts和高性能的查询。虽然合并是并发执行的，但在滥用或配置错误的情况下，parts的数量可能会超过参数配置的限制（parts_to_throw_insert和max_parts_in_total）。虽然这些限制可以进行调整，但会牺牲查询性能，这更多地指向了您的使用方式存在的问题。随着查询性能的降低，大量的pars还可能给ClickHouse Keeper带来更大的压力。

那么，为何会有太多这样的parts呢？

一个常见的原因是使用有过多基数(cardinality)的分区键。在创建表时，用户可以指定一个列作为分区键，按该键来分隔数据。每一个键值都会创建一个新的文件目录。这通常是一个数据管理技巧，允许用户在表中地逻辑分隔数据，例如按天。如DROP PARTITION这样的操作允许快速删除数据子集。然而，这个功能很容易被误用，用户将其理解为一个简单的查询优化技巧。更重要的是，属于不同分区的parts永远不会被合并。如果选择一个高基数的键，例如 date_time_ms ，作为分区键，那么分散在成千上万个文件夹中的parts永远不会是合并的候选对象 - 直到超过预配置的限制，并在后续的插入中引发"Too many inactive parts (N). Parts cleaning are processing significantly slower than inserts"错误。解决这个问题很简单：选择一个基数 < 1000 的合理分区键。

除了分区键选择不恰当以外，这个问题也可能是由于许多小的插入所造成的。每次向ClickHouse中插入数据都会导致一个插入块被转换为一个part。为了保持parts的数量在可管理的范围内，用户应该在客户端缓存数据并批量插入数据 - 理想情况下，至少1000行。如果客户端无法缓存，用户可以将这个任务推给ClickHouse通过异步插入实现。在这种情况下，ClickHouse将在本地缓存插入的数据，随后再插入到底层表中。

buffer表在这里也是一个选择，但在处理失败时的能力较差，因为它们会在内存中保持插入，直到发生刷新。相对于异步插入，buffer表有一些优势 - 主要是数据在缓冲区中时是可查询的，并且它们与物化视图的目标表的缓冲区兼容。

其他可能原因是过多的物化视图。物化视图实际上是当一个数据块被插入到一个表中时运行的触发器。它们通过例如GROUP BY来转换数据，然后将结果插入到另一个表中。这种技术经常被用来通过在INSERT时，预计算聚合值来加速某些查询。用户可以创建这些物化视图，但可能导致过多的parts。一般来说，我们建议用户在创建视图时要注意开销，并尽可能整合它们。

此外，mutation（如下面所讨论的）也可能引起合并的压力和parts的积累。最后，我们应该注意，这个错误虽然是最常见的，但只是上述误配置的一个表现。例如，由于分区键选择不当，用户可能会遇到其他问题。这些包括但不限于“no free inodes on the filesystem”、备份时间过长、以及在复制上的延迟等（以及ClickHouse Keeper上的高负载）。

我们经常收到有自建的新用户向我们咨询关于编排和如何扩展到几十个、甚至上百个节点的建议。虽然像Kubernetes这样的技术使得部署多个无状态应用的实例变得相对简单，但在几乎所有情况下，ClickHouse都不应该需要这种模式。与其他可能由于固有限制（例如，JVM堆大小）而受到机器大小限制的数据库不同，ClickHouse从一开始就被设计成利用机器的全部资源。在拥有数百核、数TB的RAM和数PB的磁盘空间的服务器上成功部署了ClickHouse的案例经常可以看到。大多数分析查询都有排序、过滤和聚合阶段。这些都可以独立地并发进行，并且默认情况下，会使用与核数相同的线程数，从而为查询利用整台机器的资源。

首先垂直扩展有很多好处，主要是成本效率、更低的操作成本，以及最小化网络数据的传输开销，从而有更好的查询性能。当然，用户需要额外做冗余，在大多数场景中，两台机器应该足够了。

总之，先垂直再水平！

虽然在OLAP用例中很少见，但有时修改数据是不可避免的。为了满足这一需求，ClickHouse提供了mutation功能，允许用户通过ALTER修改已插入的数据。ClickHouse在不需要做数据变更的场景下表现最佳，任何需要在插入后更新数据的场景都值得讨论。

在内部，mutation是通过重写整个data parts来完成的。这个过程跟合并公用相同的线程池。还要注意，默认情况下，mutation需要应用在所有副本上。因此，mutation既是CPU密集型的，也是IO密集型的，应该谨慎地执行，并限制给管理员运行。由于mutation导致的资源压力会以多种方式表现出来。典型的是，正常计划的合并会堆积，进而导致我们前面提到的“Too many parts”问题。此外，用户可能会经历复制延迟。system.mutations表提供给管理员目前正在执行的的mutation的一些指示。注意，mutation可以被取消，但不能回滚，使用KILL MUTATION取消正在执行的mutation。

我们经常看到用户因为有重复的数据而需要执行合并。我们通常建议用户在插入到ClickHouse之前的上游解决这个问题。如果这样做不可行，用户有几个选择：在查询时去重或使用ReplacingMergeTree。

在查询时去重可以通过对唯一字段的行进行group by，并使用argMax函数和日期字段来确定其他字段的最新值。ReplacingMergeTree允许具有相同排序键（ORDER BY键）的行在合并时去重。注意这只是“尽力”去完成：因为执行合并的线程不确定什么时间去执行，从而导致parts不会被合并。因此，它不能保证没有重复。用户还可以使用FINAL修饰符在 SELECT 时强制进行去重（再次谨慎使用，因为它是资源密集型的，尽管近期有所改进，但可能会很慢），或者通过OPTIMIZE FINAL在磁盘上强制合并。

在需要从ClickHouse中删除数据的情况下，用户也可以使用lightweight deletes代替mutation。这采用DELETE语句的形式，该语句接受WHERE子句来过滤行。这只是将行标记为已删除。这些标记将在查询时用于过滤行，并在parts合并时被删除。

注意：此功能处于实验阶段，需要设置 SET allow_experimental_lightweight_delete = true; 。在大多数情况下，它比使用mutation更有效率，除非你正在进行大规模的批量删除。

除了支持常用的原始类型外，ClickHouse还为Nested、Tuple、Map甚至JSON等复杂类型提供了丰富的支持。有时，这是格式化数据的唯一方式，所以有充分的理由支持这些类型，但我们建议尽可能使用原始类型，因为它们提供了最佳的插入和查询性能。

例如，我们最近看到用户热衷于利用在ClickHouse 22.4中添加的JSON功能。这个强大的功能允许表结构从数据动态推断出来，避免了用户手动指定字段类型。但请谨慎使用这一功能，不要用它来替代显示指定列类型。因为这个功能有用户该知道的一些限制：

其中的最后两个无疑导致了更差的压缩和查询/插入性能。与其让他指定所有列的类型，不如为选择性地使用这种特定类型，例如Kubernetes标签，因为数据可能会发生变化。总之，如果你知道你的表结构...请显式地指定它！

注意：JSON对象类型处于实验阶段，并正在进行改进。关于此功能仍在发展，并可能在后续版本中有所改变。

我们还经常看到用户使用Nullable类型。这能将Null值与类型的默认值区分开来。这可能很有用，但需要一个额外的Uint8列来确定哪些值为空。这会导致每个值需要额外一个字节的存储（尽管压缩性上较好），并增加查询时间开销。只有在真正需要它的时候才使用Nullable！

新用户经常对ClickHouse的去重策略感到惊讶。当出现相同的插入似乎没有任何效果时，这种情况通常会发生。例如，考虑以下内容：

CREATE TABLE temp(   `timestamp` DateTime,   `value` UInt64)ENGINE = MergeTreeORDER BY tuple() INSERT INTO temp VALUES ('2022-10-21', 10), ('2022-10-22', 20), ('2022-10-23', 15), ('2022-10-24', 18)INSERT INTO temp VALUES ('2022-10-21', 10), ('2022-10-22', 20), ('2022-10-23', 15), ('2022-10-24', 18) clickhouse-cloud :) SELECT * FROM temp SELECT *FROM temp ┌───────────timestamp─┬─value─┐│ 2022-10-21 00:00:00 │    10 ││ 2022-10-22 00:00:00 │    20 ││ 2022-10-23 00:00:00 │    15 ││ 2022-10-24 00:00:00 │    18 │└─────────────────────┴───────┘

新用户可能会对这里的结果感到惊讶，尤其是如果他们之前的经验是在ClickHouse的单个本地实例上。这种行为是由 replicated_deduplication_window 设置造成的。

当数据插入ClickHouse时，它会创建一个或多个块（parts）。在复制的环境中，ClickHouse Keeper中也会写入一个哈希值。随后插入的块会与这些哈希值进行比较，并在发现匹配时被忽略。这很有用，因为它允许客户端在没有从ClickHouse获得确认的情况下安全地重试插入，例如，由于网络中断。这要求块是完全相同的，即大小相同、顺序相同的行。这些哈希值仅存储最近的100个块，尽管这可以修改。注意，设置更高的值会由于需要跟更多的哈希值比较而减慢插入。

对于非复制实例，可以通过 non_replicated_deduplication_window 设置启用此相同行为。在这种情况下，哈希值存储在本地磁盘上。

初次使用ClickHouse的用户经常很难完全理解其独特的主键概念。与基于B(+)Tree的OLTP数据库不同，后者针对快速定位特定行进行了优化，ClickHouse使用了一个稀疏索引，该索引针对每秒插入的数百万行和PB级数据集进行了设计。与OLTP数据库相反，该索引依赖于磁盘上排序过的数据，以快速识别可能匹配查询的一组行 - 这是分析查询中的常见要求。索引实际上允许part文件在流入处理引擎之前快速的识别和匹配。

这种方法的有效性，对于查询性能和压缩而言，依赖于用户在创建表时通过ORDER BY子句选择好的主键列。通常，用户应选择他们经常用于过滤的列，一般不超过3列。这些列的顺序至关重要，可能会影响到压缩以及对第一列以外列的过滤性能。为了在查询中有效过滤次要列和高效的列文件压缩比，最佳的方法是按照它们的基数以升序排列主键中的列。

当需要加速查询时，主键通常是用户首先考虑的手段。然而，表只限于一个主键，查询的方式可能会使其失效，即对于多种场景，无法有效利用主键的查询。在这些情况下，ClickHouse可能会被迫对每个列执行全表扫描，当应用WHERE子句条件时。通常这仍然足够快，但在某些情况下，用户会使用跳数索（data skipping index），希望轻松地加速这些查询。

跳数索引使用了特定的数据结构，允许ClickHouse跳过大量确保没有匹配值的数据块。更具体地说，它们在块颗粒上创建索引（实际上是标记），允许跳过不满足WHERE子句的块。

在某些情况下，可以加速特定的查询，但他们通常被过度使用，不是简单的创建而是需要仔细设计才能发挥最大作用。因此，我们经常看到它们使表设计复杂化，并减慢插入性能，而很少（如果有的话）提高查询性能。我们总是鼓励用户阅读概念和最佳实践。

通常，只有在没有其他选择的情况下才应考虑跳数索引 - 具体来说，只有在研究其他选择之后，才应使用这种高级功能，例如修改主键（请参阅创建额外主索引的选项），使用投影(projections)或物化视图。通常，只有在主键和目标非主列/表达式之间存在强相关性时，才考虑跳过索引。如果没有任何实际的关联，跳数索引将匹配大多数块 - 结果所有颗粒都被读入内存并评估。在这种情况下，已经有了索引开销，但没有任何好处，实际上减慢了整个表的扫描。

我们经常发现习惯于OLTP的用户在初次接触ClickHouse时，试图使用LIMIT子句通过限制返回的结果数量来优化查询。从OLTP数据库的角度来看，这种方式应该直观地优化查询：返回的数据越少 = 结果越快，对吗？答案是既对又错。

这种技巧的有效性取决于查询是否可以完全以流方式运行。例如，像 SELECT * FROM table LIMIT 10 这样的查询在查到10行结果并将结果返回给用户之前，只会扫描前几parts的几个粒度(granules)。由于 optimize_in_read_order 设置默认为1，所以当用户按主键字段对SELECT进行排序时，这也是成立的。但是，如果用户运行 SELECT a from table ORDER BY b LIMIT N ，其中表是按a而不是b排序的，ClickHouse不能避免读取整个表，即查询无法提前终止。

对于聚合，情况稍微复杂一些。除非用户按主键分组并设置 optimize_aggregation_in_order=1 ，否则也需要进行全表扫描。在这种情况下，一旦获得足够的结果，就会发送一个传播信号。只要查询的前几步能够流式传输数据，例如过滤器，这个机制就会起作用，查询会提前终止。但通常，聚合在返回和应用LIMIT作为最后阶段之前必须读取所有表数据。

举个例子，我们从UK Property Price Paid教程中创建并加载了一个包含2755万行的表。这个数据集在我们的play.clickhouse.com环境中是可用的。

对于 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的不太明确的行为所困扰，这样的环境中，一个表可以有很多分片。分片允许用户将其数据分割或复制到多个ClickHouse实例中。当一个带有LIMIT N子句的查询发送到一个分片表时，例如一个分布式表，这个子句将被传递到每个分片。每个分片都需要收集前N个结果，并返回给协调节点。当用户运行需要全表扫描的查询时，这可能会特别消耗资源。这类查询通常是“点查”，查询的目标只是定位几行数据。虽然这在ClickHouse中可以通过良好的索引设计来实现，但一个未优化的版本，再加上一个LIMIT子句，可能会非常消耗资源。

在复制环境中，当一个节点失去与ZooKeeper的连接时，这种情况就会发生。这通常总是由于ZooKeeper的问题造成的。尽管ClickHouse Keeper的发布解决了与ZooKeeper相关的许多挑战，但如果该组件资源不足，这个问题仍然可能出现。常见的原因是在生产环境中将keeper与ClickHouse部署在相同的主机上，或ZooKeeper JVM资源配置不佳。通常，通过确保此组件部署在单独的硬件上，并给予充足的资源可以解决此问题。

对于新用户来说，ClickHouse经常像魔术一样 - 每个查询都非常快速，即使是大数据集的查询。然而，实际的使用情况会测试到ClickHouse的极限。超出内存的查询可能是由多种原因造成的。我们最常见的是在高基数字段上执行大型连接或聚合。如果性能至关重要，而这些查询是必需的，我们经常建议用户进行扩展，云上实例可以毫不费力完成。然而，我们理解，在自建的场景下，这有时并不简单，也许甚至不需要最佳性能。在这种情况下，用户有一些选择。