ClickHouse优化秘籍:别让AI帮你“挖坑”!Schema设计的关键考量

Meetup活动

ClickHouse 上海第3届 Meetup 讲师招募中，欢迎讲师在文末扫码报名！

向任何 LLM（大型语言模型）询问如何为实时事件分析设计 ClickHouse 表，你经常会得到类似这样的结果：

乍一看，这还算合理。它语法正确，使用了 ClickHouse 特有功能。你确实可以直接将其投入生产环境。

然而，这个表结构 (schema) 中的许多选择可能并不适合你：

这些选择本身并无对错，你最终可能在某个阶段用到它们。但更有可能的是，你根本不需要它们。而这正是 LLM 可能引导你走错方向的地方。它们很智能，但有时会试图过于求全。

正确的方法几乎总是恰恰相反的：从一个基础表、一个合理的 ORDER BY 语句、默认压缩、不分区、不使用投影开始。运行你的实际工作负载，进行测量。再根据数据反馈，按需增加复杂度。ClickHouse Agent Skills 可以在需要时帮助 LLM 做出正确的选择。

本文将深入探讨我们所观察到的 AI 生成 ClickHouse 表结构时常见的陷阱，这些经验结合了我们解决方案架构团队的实际交流以及数十个客户项目中的常见模式。

常见错误

场景： 用户向某个 LLM 提问：“如何让这个查询更快？”，LLM 随即建议通过某个常用作过滤条件的列进行分区。

除特殊小众场景或极大规模应用外，这种做法通常是错误的。

ClickHouse 中的分区旨在作为一项数据管理功能。如果分区得非常谨慎，它们确实可以加速查询，但这并非其主要目的，而且这种性能提升并非没有代价。

在少数情况下，基于维度字段进行分区对性能确有益处，ClickHouse 的解决方案架构师（Solutions Architect）偶尔也会向用户推荐这种做法。然而，每当他们给出此建议时，都会详细阐述其所有权衡和潜在影响：

我最近协助一位客户将终端用户查询延迟降低了 200 毫秒。他们的查询复杂且规模庞大。我们已经对 ORDER BY 键、查询语句和数据类型进行了优化，分区是最后的手段。我不得不向他们解释：“这会彻底改变你管理表的方式。但如果你确实需要那额外的 200 毫秒低延迟，那么这就是必须付出的代价。”我只有在穷尽所有其他优化手段后，才会给出这样的建议。—— Jack Borthwick，解决方案架构师（Solutions Architect）

推荐做法： 首先优化你的 ORDER BY 键。ClickHouse 中大部分查询性能都源于此。分区应由数据生命周期管理需求（例如删除旧数据、管理数据保留期）驱动，而非追求查询速度。

场景： 用户采用 ReplacingMergeTree 表引擎来实现数据去重。然而，在插入数据后，他们发现查询结果中仍出现重复数据。这符合预期，因为 ClickHouse 的去重操作是在后台合并（merge）时进行的，而非在数据插入时。因此，他们会向其大型语言模型（LLM）提问：“我如何在 ClickHouse 中强制去重？”

LLM 回答道：运行 OPTIMIZE TABLE ... FINAL。

这条命令会强制 ClickHouse 尽可能彻底地合并分区中的所有数据分片。它会绕过常规的数据分片大小限制，包括你的服务或集群上设置的限制。这在开源版（OSS）和云版（Cloud）中均适用。

这样做的结果是，你最终可能导致数据分片体积过大，甚至可能达到数 TB 级别。这是不可逆转的操作，你无法撤销这些分片的合并。

其后续影响可能比原问题更糟：它可能导致未来的数据变更操作失败。如果你后续尝试添加列或索引，ClickHouse 需要重写这些过大的数据分片，而这些操作可能会因此中断。

应对方法： 如果你需要在合并操作完成前进行去重查询，请在 SELECT 查询中使用 FINAL 关键字。请注意，物化去重在设计上是最终一致 (eventually consistent) 的，ClickHouse 会在后台自动完成合并和去重操作。

场景： 某个用户有五种慢查询类型。他们请求大型语言模型 (LLM) 针对每种类型进行优化。LLM 为每种慢查询创建了一个增量物化视图 (Incremental Materialized View)。于是，用户现在有了五个 MV。一个月后，MV 的数量增加到十五个。最终，数据摄取 (ingestion) 性能因此下降，变得过于缓慢。

物化视图 (Materialized View) 是优化查询的强大工具，但它们并非没有成本。每个增量物化视图都会在源表每次数据插入时被触发，这意味着物化视图的数量越多，ClickHouse 在数据插入时需要完成的工作量就越大。

我们经常看到，用户最终陷入数量庞大、相互交织的表结构蔓延之中，这些表包含不同引擎、物化视图、可刷新物化视图 (Refreshable Materialized View) 以及下游存在不兼容特性。出现这种情况的原因是 LLM 试图孤立地解决每个问题，未能理解整体架构的全貌。

通常，这种混乱程度常常让用户也一头雾水。第一步始终是相同的：梳理出所有的表、视图和依赖关系，并明确每个组件的实际作用。我们经常发现，整个数据管道中存在已损坏或冗余的分支。

应对方法： 从零个物化视图着手。直接针对基础表执行查询，并进行性能剖析 (profile)。当你发现某个查询模式确实无法在基础表上以可接受的延迟得到满足时，再添加一个物化视图 (MV)，并衡量其对数据摄取性能的影响。请将每个 MV 都视为每次数据插入操作所付出的成本。

场景： 用户拥有的数据是完全非结构化数据 (unstructured data)，且带有动态键 (dynamic keys)。LLM 建议使用 ClickHouse 的 JSON 数据类型。然而，用户插入的数据具有无限制的可变性 (unbounded variability)，从而产生了数千个独一无二的键路径。

ClickHouse 的 JSON 类型功能强大，即使你的数据包含数千个键路径 (key paths)，也能帮助你针对半结构化数据构建高性能分析。然而，它需要根据你的数据特性进行恰当的使用。

在内部实现上，ClickHouse 会为你的键路径创建物理子列 (physical sub-columns)，最大数量受限于 max_dynamic_paths 参数（默认值为 1000）。当这些键路径很常见，并且你很可能在分析中查询这些列时，使用物理子列是明智的选择。

然而，如果你的数据是高度动态的，包含用户自定义属性、可变模式或不可预知的字段，这可能导致创建数百个几乎为空且从不被查询的持久列 (persistent columns)。更糟糕的是，为了适应这种情况，你可能需要将 max_dynamic_paths 上限提高到数万。

在这两种情况下，JSON 类型都能有效利用，但其应用方式必须有所区别。我们发现 LLMs (大型语言模型) 很少能为这两种场景提供恰当的配置建议。

应对策略： 如果你的数据是高度动态的，且键路径不可预测，请将 max_dynamic_paths 设置为 0。这会提供一个哈希桶映射类型 (bucketed map type)，不创建持久子列，并且它非常适用于那些不需要对单个键进行列式访问 (columnar access) 的数据。切勿将 max_dynamic_paths 提高到数千。

如果你的数据是半结构化的，并且拥有数量可控的常用键路径，那么请使用 JSON 类型并明确哪些特定字段应存储为物理子列。如果你的数据是半结构化的但确实存在数千个常用键路径，可以研究 JSON 类型中的高级序列化格式 (advanced serialisation format)。请注意，不同的序列化格式各有优缺点，尤其是在数据摄取 (ingest) 性能方面。

典型场景： 用户需要满足具有不同排序需求的查询。LLM 建议添加 Projections。在开发阶段，数据量可能只有数百 GB，此时这种做法表现良好。但当表增长到 5-10+ TB 时，查询延迟会显著飙升。

在查询时，ClickHouse 会评估所有 projection（预聚合/物化视图）的标记计数（mark counts），以选择最适合当前查询的 projection。然而，随着数据量的增长，这种评估本身会变得非常耗时。一个在小规模下能为你节省时间的 projection，在大规模时，却可能导致每个查询增加一到两秒的延迟，即便该 projection 最终并未被实际使用。

替代方案： Projection 在恰当的规模和用例下是一个出色的特性。如果你的数据量在数 TB 级别以下，并具有定义明确的替代查询模式，那么 projection 是一个不错的选择。但如果你的数据量正增长到或已超过 5-10TB，那么在决定采用 projection 之前，务必使用生产规模的数据明确测试其性能。

优化需精妙考量

当一位经验丰富的工程师面对复杂的数据问题时——例如，从 Postgres 数据库进行数据反范式化处理，同时解决去重和跨表连接的问题——他们很少直接给出“唯一的答案”。相反，他们会提供三到四个选项，每个选项都有其独特的权衡（trade-offs）。例如，选项 A 可能会带来更快的查询速度，但数据摄入（ingestion）速度会变慢；选项 B 简化了数据管道，但需要更多的硬件资源；选项 C 实现了系统解耦，却增加了运维复杂性。他们会详细阐述这些选项，并建议：选择最符合你业务需求的那个。

大型语言模型（LLM）倾向于表现出自信，而非深入考量复杂细节。它们很少会说“这取决于具体情况”，更不会主动呈现各种权衡取舍。

任何一个被广泛采用的系统都面临着同样的挑战：市面上充斥着大量涵盖基础知识的内容，包括各类教程、入门指南和最佳实践。当然，也有一些（尽管数量通常较少）内容涉及高级主题。LLM 正是基于所有这些数据进行训练，因此在理解基本概念上大多是准确的。然而，在“普遍适用的最佳实践”与“针对具体场景的专业高级指导”之间，存在一个显著的知识鸿沟。因此，LLM 只能采取它唯一能做的方式：将通用建议应用于你的具体情况，却无法判断这是否是正确的决策。

存在一个“知识边界”，一旦超越这个边界，LLM 的输出实际上就是一种猜测，而且它永远不会告诉你何时已经越界。

但这绝不意味着你应该停止在 ClickHouse 相关任务中使用 LLM。LLM 在入门学习、编写查询以及理解基本概念方面，确实能起到显著的加速作用。以下是如何在避免上述情况的同时，最大限度地发挥 LLM 优势的方法。

由简入繁，循序渐进。 你的初始 Schema (数据库模式) 应该力求简洁：采用 MergeTree (如果需要去重则选择 ReplacingMergeTree)，搭配精心选择的 ORDER BY 子句，使用默认压缩，不设分区、不使用投影、不创建 MVs (Materialized Views)。只有在你实际测量了工作负载并确定了具体瓶颈后，才逐步增加复杂性。

与你的 LLM 协作，深入理解其设计决策。 如果一个 LLM (大型语言模型) 生成的 Schema 包含你无法立即确认其正确性的具体细节，务必追问其背后的考量。这样做有两大益处：一方面能在问题进入生产环境前及时发现并解决，另一方面也能加深你对即将运维的系统的理解。将 LLM 视为你的陪练伙伴，对其 DDL (数据定义语言) 中的每一个决策进行审问，直到你完全理解为止。

何时寻求人工协助？ 如果你正在对来自 Postgres (数据库) 的数据进行反范式化处理、处理复杂的去重逻辑、在数据摄取 (Ingestion) 阶段进行表连接，或者处理多 TB 级别的数据规模——务必暂停，并寻求专家意见。互联网上可供 LLM 训练的模式数据不足，使其无法可靠解决此类问题。这并非技术本身的缺陷，而仅仅是当前训练数据的现状使然。

未来是协同共进的

我们预计，将有越来越多的工程师运用 AI (人工智能) 代理来设计和运维数据库。这是一个不可避免的趋势，整体而言，是积极正向的。LLM 的能力将持续增强，未来将有更多 ClickHouse 领域的专业内容可供其训练，它们能够可靠处理的边界也将持续拓展。

然而，就目前而言，LLM 能够自信生成的内容与实际在生产环境中大规模运行的有效方案之间，仍存在显著差距。本文所讨论的潜在陷阱，正是源于这一差距。在这一差距弥合之前，人类仍需在验证 AI 输出方面发挥关键作用，尤其是在那些复杂、高风险、且关乎产品能否稳定运行的规模化决策上。

善用 AI 提速，巧借人工判断确保方向正确。

Meetup 活动讲师招募

我们正为上海活动招募讲师，如果你有独特的技术见解、实践经验或 ClickHouse 使用故事，非常欢迎你加入我们，成为这次活动的讲师，与大家分享你的经验。

/END/

试用阿里云 ClickHouse企业版

轻松节省30%云资源成本？阿里云数据库ClickHouse 云原生架构全新升级，首次购买ClickHouse企业版计算和存储资源组合，首月消费不超过99.58元（包含最大16CCU+450G OSS用量）了解详情：https://t.aliyun.com/Kz5Z0q9G