部署运维

面向 AI Agent 的 ClickHouse 集群调优实战:从病根定位到"近乎白捡"

ladydd

17 Jun 2026 — 15 min read

我们最近把一套面向 AI agent 的分析型数据服务,从单机迁到了 ClickHouse 集群(1 分片 × 3 副本 + HAProxy 入口),并做了一轮系统的性能调优。

这篇不止于"我们改了哪些参数",更想讲清楚背后的思路:怎么用数据定位病根、怎么在"空间/复杂度/收益"之间取舍、怎么做到改完能验证、出事能秒回滚、上线不断服务。如果你也在为高重复、只读、模板化的负载(AI agent、看板、报表 API)调 ClickHouse,这套方法可以直接借鉴。

一条主线贯穿全文:先吃透流量特征,再分层优化,每一步都可验证、可回滚。

一、起点:先吃透你的流量长什么样

任何优化的第一步不是动手,是看清流量。AI agent 作为查询方,有几个非常鲜明的特征:

模板化:查询是固定 SQL 模板 + 少量参数,不是人手写的千奇百怪 SQL;
只读:全是 SELECT,数据由后台批量更新;
高重复:同一个"探索首页""热门实体分析"会被不同会话反复打;
有界:LIMIT 封顶、超时保护,尾部延迟可控。

这几个特征直接决定了优化的优先级:

高重复 + 只读 → 查询结果缓存(query cache)收益极高,几乎是白捡;
模板化 → 可以针对固定的几条热查询做深度的查询形态优化;
有界 → 可以用并发上限当保险丝,尾部行为可预测。

没有"通用最优"的 ClickHouse 调优,只有"匹配你流量特征"的调优。 先看清流量,后面每一步才有的放矢。

二、方法论:怎么定位病根(别靠猜)

调优最常见的浪费,是凭直觉在错误的地方使劲。我们坚持用数据说话,几个手段:

1. query_log 按查询模式聚合,找真正的大头

SELECT
  normalizeQuery(query) AS pattern,
  count()                AS n,
  round(avg(query_duration_ms)) AS avg_ms,
  round(avg(read_rows))  AS avg_rows
FROM clusterAllReplicas('your_cluster', system.query_log)
WHERE event_time > now() - INTERVAL 3 HOUR
  AND type = 'QueryFinish' AND is_initial_query
GROUP BY pattern
ORDER BY avg(query_duration_ms) DESC
LIMIT 10;

normalizeQuery 把字面量替换成占位符,于是"同一类查询"被聚到一起,一眼能看出哪类查询慢、平均扫多少行、跑了多少次。

一个真实的纠偏:我们最初看到 max(query_duration_ms) 高达 60 秒,差点当成"某个线上工具特别慢"去优化它。一查 query_log 才发现——那条 60 秒是后台构建任务的 INSERT(扫 2.6 亿行做物化),根本不在 agent 的查询路径上。线上读最慢的其实只有 4 秒。如果不查清就动手,就会在一个根本不影响用户的地方白费力气。

2. EXPLAIN 看 granule,确认走没走主键

EXPLAIN indexes = 1
SELECT ... ;   -- 看 "Granules: X/Y",X 越接近 Y 说明越接近全表扫

Granules: 9/5918 是好的(主键裁剪到位),Granules: 1333/1333 是坏的(整表扫)。这是判断"JOIN/过滤有没有下推到主键"最直接的证据。

3. 分段 profiling:把端到端拆开计时

一个工具调用往往是"搜索召回 → JOIN 明细 → 聚合 → 排序分页"好几段串起来。不要只看总耗时,要把每一段单独计时,才知道大头在哪。我们就是这样发现:某个工具的"搜索段"优化后读行降了 90%,但端到端只快了 6–15%——因为大头根本在后半段的 JOIN。

4. 学会证伪,排除"嫌疑犯"

我们一度怀疑 count() OVER()(分页用的总数统计)是性能病根。分段一测:它只占 50–150ms。果断排除,没有去动它(动它还会牵连分页契约)。

调优思路里很重要的一条:先证伪、再动手。把"看起来像病根"的嫌疑犯逐个测掉,剩下的才是真凶。

三、查询层优化:本质都是"少扫行"

定位清楚后,我们做了两轮查询层优化(代号"第一刀""第二刀")。抽象出来,几乎所有查询优化都在干一件事:减少要扫描的行数 / granule。手段无非三类——冗余换速度、裁剪换速度、预计算换速度,都是用空间或预处理换运行时。

病根 A:命中后"回表扫大表"

我们的搜索召回是"倒排索引命中 token → 回 JOIN 一张明细表取展示/排序字段"。问题在于:明细表的排序键和回表的连接键不匹配,导致这一步要扫 1700 万行。

解法:冗余物化一张"命中索引表"。 把展示、排序、token 等字段直接冗余进倒排索引,命中即拿、不再回表。

代价:多占几 GB 磁盘(payload 冗余到几千万行);
收益:搜索段读行降 90%,EXPLAIN 从扫 426 granule 降到 9。

这就是典型的**"冗余换速度"**:用存储空间换掉一次昂贵的回表。

病根 B:JOIN 没下推 + 热路径上挂着"现场算的视图"

第二轮发现两个后半段的真凶:

B1:小集合 JOIN 大表,但没走主键。 一个工具拿"搜索命中的少量 key"去 JOIN 一张千万行的聚合服务表,本该用主键只读相关 granule,实际却整表扫(1333/1333)。

-- 改写前:JOIN 形态让优化器整表扫聚合表
FROM hits h JOIN big_service_table t ON t.key = h.key

-- 改写后:先用命中 key 显式裁剪,逼优化器走主键
WITH h AS ( ... )
SELECT ...
FROM big_service_table t
JOIN h ON t.key = h.key
WHERE t.key IN (SELECT key FROM h)   -- 关键:把小集合下推成主键过滤

原型实测:某查询 1.2s → 0.3–0.7s。这是**"裁剪换速度"**——不改数据,只把过滤条件下推到主键。

B2:热路径上有个旧视图在"现场算"。 一个高频工具每次都现场 JOIN 两张大表去算一个评分视图。

解法:把视图输出物化成一张小服务表(我们这例是 23 万行),查询直接 join 这张小表,不再现场算。这是**"预计算换速度"**。

一个容易被忽略的正确性细节:物化前我们核对发现,这个视图的输出和"现成的近似字段"有约 3 万行对不上。为了零差异,我们老老实实把视图原样物化,而不是图省事用近似字段替代。优化不能以改变结果为代价。

四、一个诚实的中间结果:第一刀"不及预期"

值得单独说:第一刀(命中索引)把回表病根切掉了、搜索段读行降 90%,但端到端工具只快了 6–15%,远没有达到当初预期的"4 秒进 1 秒"。

我们没有粉饰这个结果,而是回头看 query_log + 分段数据,得到结论:搜索段在整个工具里只占一小段,真正的大头在后半段的 JOIN(也就是上面的病根 B)。

这反而给了一个有价值的工程经验:

先做最容易定位、最干净的那一刀,它会把"前半段"清理干净,让后半段的瓶颈暴露得更清楚;
别指望一刀解决全部,诚实地测、承认不及预期、再针对性补第二刀——第二刀直击后半段,market_space 这类工具才真正从 ~2.6s 降到 ~1s。

迭代式优化 + 诚实评估,比"一把梭哈 + 自我感觉良好"靠谱得多。

五、Server 层:Query Cache —— 近乎白捡的一项

查询层啃的是"硬骨头",而 server 层有一项性价比高到近乎白捡:query cache。

原理:ClickHouse 把一条 SELECT 的最终结果缓存起来,下次遇到完全相同的查询,直接返回结果、跳过整个计算(不扫表、不 JOIN、不聚合)。

<!-- users.xml: 给查询用户的 profile -->
<use_query_cache>1</use_query_cache>
<query_cache_ttl>300</query_cache_ttl>
<query_cache_min_query_duration>100</query_cache_min_query_duration>  <!-- 只缓存算起来>100ms 的 -->

<!-- config.d/*.xml: server 级缓存池 -->
<query_cache>
    <max_size_in_bytes>2147483648</max_size_in_bytes>          <!-- 2 GiB -->
    <max_entries>8192</max_entries>
    <max_entry_size_in_bytes>8388608</max_entry_size_in_bytes>  <!-- 单条 8 MiB 上限 -->
</query_cache>

实测:一条 4780 万行的聚合查询,冷查 6185ms → 热查 7–8ms;落到完整工具上,首次 3854ms → 之后 ~130ms。

为什么敢说"白捡":它不改一行 SQL、不动数据、不建表,只开个配置;而 agent 流量重复度极高,命中率天然就高。投入趋近于零,收益是数量级的。

容量:2 GiB 能存多少?会爆吗?

query cache 存的是结果不是数据。AI agent 结果普遍很小(LIMIT ≤ 100 行),我们实测平均每条约 19 KiB。于是:

2 GiB÷19 KiB≈11 万条2GiB÷19KiB≈11 万条

而热查询去重后也就几百到几千种。能存 11 万条 ≫ 几千种热查询,意味着缓存池根本装不满,所有热查询都能常驻、命中率拉满。

会不会爆?占用由池子上限(2 GiB)封顶,不由 TTL 决定——TTL 设多长都不会让它占更多。还有个反直觉点:ClickHouse query cache 的淘汰以 TTL 过期为主,并非传统 LRU 的"新顶旧";在"装不满"的场景里,淘汰实际只发生在 TTL 到期时。

六、集群特有的坑:三副本 Query Cache 不共享

这是单机不会遇到、上了集群才浮现、也最容易误解的一点:

ClickHouse 的 query cache 是节点本地的(node-local),三个副本各缓各的、互不共享。

HAProxy 轮询把请求分到三台,于是同一个热查询会在三台各"垫付"一次首查(总共算 3 次),之后才各自命中。

但务必分清两件事:

缓存内容三台不同(各自存了哪些查询);
同一查询的结果三台完全一致——底层是 ReplicatedMergeTree、三副本数据严格一致,同一查询无论落到哪台、命中与否,答案永远相同。

所以"不共享"不影响正确性,只影响预热:稳态下三台都热了,命中率一样,代价仅仅是开张时每个热查询多算两次——在高重复流量下可忽略。

要不要折腾成"统一缓存"?不值得:query cache 架构上不支持跨节点共享;硬要统一就得在应用层外挂 Redis(多一个会挂的组件),或让 HAProxy 按查询内容哈希路由(又会把热查询压到单台、破坏均衡)。接受"各缓各的"反而最省心。

唯一要记的集群纪律——清缓存必须广播三台:

SYSTEM DROP QUERY CACHE ON CLUSTER your_cluster;   -- 只清一台,另两台还吐旧结果

七、缓存新鲜度:长 TTL + 发布时清

缓存的本质矛盾是命中率 vs 数据新鲜度。破法的关键是看清"数据多久变一次"。

我们的数据是批量周期性更新的(比如每周一次),两次更新之间数据根本不变。因此最优策略是:

TTL 设得很长(按更新周期,甚至几天)→ 平时把命中率吃满;
每次发布新数据后,主动 DROP QUERY CACHE ON CLUSTER 清一次 → 更新即刷新。

长 TTL 负责"平时拉满",发布清缓存负责"更新即新鲜",两头都占。

⚠️ 一条铁律:TTL 拉得越长,"发布后清缓存"越不能忘——忘了清,旧结果会一直顶到 TTL 过期(设几天就脏几天)。所以务必把清缓存焊进发布流程自动执行,不要长期依赖人工。"发布的必经动作"交给脚本,远比靠人记可靠。

八、配套两件:Mark Cache 与并发保险丝

query cache 治"重复查询",另两项治"基础盘":

Mark Cache 调大(我们 5G → 12G):点查为主的负载,mark cache 缓的是稀疏索引标记,命中后少读磁盘、直奔目标 granule。内存够就给足。
max_concurrent_queries 当保险丝(512 → 150):16 核机器上 512 并发不是"容量"而是"事故放大器"——真到那个量级,ClickHouse 已经在排队抢内存。配合应用层限流,把它收到一个"过载时干脆拒绝、而非全体超时"的合理水位。

九、安全上线方法论(这套最值钱)

调优的另一半是怎么把改动安全地推上线。我们每一步都遵守同一套:

旁路新建,不动现有读路径:新表、新 SQL 一律加开关(环境变量),默认走旧逻辑;新东西先建好、灌好、对账,期间线上完全不受影响。
独立验证,不轻信"自报":行数、三副本一致性、路由证据(查 query_log 确认线上真的在走新表/新路径)、前后延迟对比——每一项自己复核,不只看"对方说没问题"。
开关切换 + 可秒回滚:切换就是改一个环境变量 + 重启,旧表/旧视图/旧 SQL 全程保留;出问题一行切回 legacy。数据层回滚成本接近 0。
滚动重启,不断服务:改 server 配置需要重启时,三副本一台一台滚——

零 diff 对账:新旧两套结果做 canonical 对比(排序后逐字段比),语义零差异才算对。物化是否忠实,用业务列 hash 校验:

一个实战教训:第一次比"全列 hash"发现不一致,差点以为物化错了——排查发现是新表多了一个 join 键列 + build_id/built_at 等元数据列导致的。排除元数据列后,业务列逐字段相等。 比 hash 时一定要先剔除元数据列,否则会被假阴吓到。

HAProxy drain 摘掉 ch-C(等连接归零)→ 改配置 → 重启 → 健康检查
(SELECT 1 / replication_queue=0 / replicas 正常) → 挂回 HAProxy → 下一台

全程至少 2 台在线,读流量绕开正在重启的节点,MCP/应用无感。顺序、"重启前暂停重型 ETL"、"每台确认 replication_queue=0 再下一台"都写进脚本,不靠人记。

十、专业陷阱:Query Cache 会污染你的性能评估

最后一个最容易被忽略、却最该写进团队规范的点。

开了 query cache 之后,直接测延迟会失真:命中缓存是 7–8ms(假快),没命中才是真实计算耗时。你拿这种数去评估"某个优化到底有没有效",结论是乱的。

解法:评估真实计算性能时,显式绕过缓存——

SELECT ... SETTINGS use_query_cache = 0;   -- 纯计算,不读不写缓存

按目的分两套测法:

测优化真功夫 / 前后对比 → use_query_cache = 0,得到干净可比的计算耗时;
测用户实际体感 → 开着缓存测(命中就是快,那才是真体验)。

分析 system.query_log 时也要会区分:命中缓存的查询 read_rows ≈ 0,把它们过滤掉,剩下才是真实计算画像。

总结:分层优化清单 + 三条思路

层次	优化项	手段	收益
查询层	回表病根	冗余物化"命中索引",命中不回表	搜索段读行降 90%
查询层	JOIN 没下推	命中 key `IN()` 裁剪,逼走主键	整表扫 → granule 裁剪,延迟 -60%
查询层	热路径现场算视图	物化成小服务表,不再现场 JOIN	去掉一次大表扫描
Server	Query Cache	开启 + 长 TTL + 发布时 `ON CLUSTER` 清	重复查询数量级提速,近乎白捡
Server	Mark Cache	点查为主则调大	减少磁盘读
Server	并发保险丝	`max_concurrent_queries` 收到合理水位	过载优雅拒绝而非雪崩
上线	滚动重启	摘一台→改→重启→检查→挂回,留 2 台在线	改配置不中断服务
评估	绕过缓存	`use_query_cache=0` 量真功夫	性能数据不被缓存污染

如果只记三句话:

先吃透流量,再选手段。 正因为看清"AI agent = 高重复只读",query cache 才从"可做可不做"变成"近乎白捡";
用数据定位病根,别靠猜。 query_log 聚合、EXPLAIN granule、分段 profiling、先证伪再动手——优化要打在真凶上;
每一步都旁路、对账、可回滚。 新建不动旧、零 diff 才切、滚动不断线、一行切回——让"优化"变成一件低风险、可重复的工程动作,而不是一次提心吊胆的赌博。

调优不是炫技,是在看清流量和数据之后,用最小的风险换最大的确定性收益。