ClickHouse没想象中好用！除非避开了这12个坑……
ClickHouse中国
2023-09-06 11:15:00
这是一个常见的ClickHouse错误，通常是不正确的使用方式和不遵循最佳实践导致。当插入
在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带来更大的压力。
一个常见的原因是使用有过多基数(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功能。这个强大的功能允许表结构从数据动态推断出来，避免了用户手动指定字段类型。但请谨慎使用这一功能，不要用它来替代显示指定列类型。因为这个功能有用户该知道的一些限制：
-
-
没有使用最优的类型，比如不必要的使用Nullable
-
其中的最后两个无疑导致了更差的压缩和查询/插入性能。与其让他指定所有列的类型，不如为选择性地使用这种特定类型，例如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的极限。超出内存的查询可能是由多种原因造成的。我们最常见的是在高基数字段上执行大型连接或聚合。如果性能至关重要，而这些查询是必需的，我们经常建议用户进行扩展，云上实例可以毫不费力完成。然而，我们理解，在自建的场景下，这有时并不简单，也许甚至不需要最佳性能。在这种情况下，用户有一些选择。
对于内存密集型的聚合或排序场景，用户可以使用设置max_bytes_before_external_group_by 和 max_bytes_before_external_sort 。简而言之，这确保任何聚合在超出内存阈值时都可以“溢出”到磁盘。这无疑会影响查询性能，但将有助于确保查询不会OOM。后者的排序参数帮助解决与内存密集型排序相似的问题。在分布式环境中，这尤其重要，因为协调节点从其他分片接收排序后的响应。在这种情况下，可以要求协调服务器对一个大于其可用内存的数据集进行排序。有了 max_bytes_before_external_sort ，排序可以溢出到磁盘。这种设置对于用户在 GROUP BY 后有一个 ORDER BY ，并带有 LIMIT 的情况也有用，特别是在查询是分布式的情况下。
对于Join，用户可以选择不同的Join算法，这可以帮助减少所需的内存。默认情况下会使用Hash Join，这提供最完整的功能，并且经常能提供最佳性能。此算法将Join的右表加载到内存中的哈希表中，然后对左表进行评估。为了最小化内存，用户因此应将较小的表放在右侧。然而，这种方法在内存有限的情况下仍然有限制。在这些情况下，可以通过 join_algorithm 设置启用 partial_merge 连接。这是sort-merge算法的变种，首先将右表排序到块里，并为它们创建一个min-max索引。然后，它按连接键对左表的parts进行排序。使用min-max索引来跳过不需要的右表块。这在损失一定性能的情况下减少了内存使用。进一步延伸这个概念， full_sorting_merge 算法允许在右表非常大，不适合在内存查找的情况下执行Join，例如复杂的子查询。在这种情况下，如果两侧都不适合内存，那么它们都会先在磁盘上进行排序，从而允许大表Join。
自20.3起，ClickHouse支持 join_algorithm 设置为auto。这指示ClickHouse自适应选择Join方法，其中优先使用Hash-Join算法，直到内存限制为止，再尝试partial_merge算法。最后，关于Join，我们鼓励读者关注分布式Join的行为以及如何最大限度地减少它们的内存消耗。
其他导致内存问题的原因是没有对用户做限制。在这些情况下，我们看到用户在没有quotas或查询复杂性限制(各种_overflow_mode参数)的情况下发起流氓查询。如果ClickHouse实例的用户比较多，并且水平参差不齐，这些控制手段对于提供一个稳健的服务是必不可少的。
ClickHouse还最近引入了新的内存overcommit功能。之前，查询受到 max_memory_usage 设置（默认10GB）的限制，这提供了一个简单粗暴的限制。用户可以在单个查询上提高内存，但可能会影响其他用户使用。内存overcommit允许运行更多内存密集型的查询，只要存在足够的资源。当达到最大服务器内存限制(max_server_memory_usage)时，ClickHouse将确定哪些查询最超配，并尝试杀死这些查询-并且可能不是触发内存超限的查询。如果不是，查询将等待一段时间，直到高内存查询被杀死，然后继续运行。这使得低内存消耗的查询始终可以运行，而高内存消耗的查询可以在服务空闲并且资源可用时运行。此行为可以在server和user级别进行调整。
物化视图是ClickHouse的一个强大特性。通过允许在插入时重新定向和转换数据，用户可以优化特定查询。当需要多于一个主索引时，我们经常看到用户使用这种技巧。与物化视图相关的常见问题很多，可能足以写一篇单独的博客文章。简单归纳最常见的问题如下：
-
我们经常看到用户对物化视图的工作方式存在误解。物化视图对源表数据一无所知，实际上只是在插入时的触发器 - 只能在插入的数据块上运行。它们无法看到合并、分区删除或mutation。如果用户更改了源表，他们因此也必须更新关联的物化视图 - 目前没有办法能保持它们同步。
-
用户在单个表上添加了太多的物化视图。这些视图并不是没有消耗的，并且必须在每次插入时运行。一个表上超过50个物化视图通常是过多的，会降低插入速度。除了计算开销外，每个物化视图都会从其运行的块创建一个新的part - 可能引发前面讨论的“Too many Parts”问题。请注意，通过设置 parallel_view_processing 来并行运行视图，可以提高性能。
-
状态函数是ClickHouse的一个引人注目的功能，允许数据在后续的查询中使用聚合函数进行总结。具有许多这些函数的物化视图，尤其是计算分位数状态函数的那些物化视图，可能对CPU开销很大并导致插入变慢。
我们经常看到物化视图的列和目标Aggregating/SummingMergeTree表上的列不匹配。目标表的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;
在ClickHouse，我们定期发布新功能。在某些情况下，新功能被标记为"experimental"，这意味着它们将从现实场景的使用和社区的反馈中受益。最终，这些功能会发展到被视为“production ready”的程度，或者如果发现它们通常不是很有用或有其他方法来实现最初的目标，则会被抛弃。虽然我们鼓励用户尝试实验性功能，但我们建议不要在生产环境中严重依赖它们。
我们在文档中将所有实验性功能做了标记，并且任何使用都需要用户设置一个参数来启用特定的实验性功能，例如：SET allow_experimental_lightweight_delete = true 。
如果你读到这里，你应该已经准备好在生产环境中管理ClickHouse集群 - 或者至少能够避免许多常见的问题！管理拥有数PB级数据的ClickHouse集群无疑是具有挑战性的，即使对于最有经验的使用者也是如此。为了避免这些挑战并仍然体验ClickHouse的速度和功能，尝试在云上使用ClickHouse。
来源丨公众号：ClickHouseInc（ID：ClickHouseInc）
dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn
如果字段的最大可能长度超过255字节，那么长度值可能…
只能说作者太用心了，优秀
感谢详解
一般干个7-8年（即30岁左右），能做到年入40w-50w；有…
230721