一、问题现象
业务线上ClickHouse列表查询出现严重的冷热查询性能割裂问题，极大影响前端页面渲染体验：
-
首次冷查询：耗时 8000ms – 10000ms，前端页面渲染超时、卡顿严重
-
二次热查询：同SQL、同参数，耗时骤降为 500-800ms（含TCP链接开销）
常规认知中，该现象大概率是ClickHouse缓存未命中、OS PageCache未预热导致，但经过全链路排查，本次问题和缓存、SQL、网络、连接池完全无关，是典型的底层磁盘IO劣化+系统表无限膨胀引发的后台任务IO饿死。
二、前置误区排查（全网90%博主都会踩的坑）
先逐一排除常规慢查询诱因，精准缩小问题范围，避免无效优化。
1. 排除网络与TCP连接问题
通过极简基准SQL测试单机往返延迟，剥离业务SQL复杂度影响：
SELECT 1
测试结果：中位耗时 19ms，证明9000端口TCP握手、网络链路、客户端驱动、连接池均无瓶颈，排除连接耗时干扰。
2. 排除ClickHouse内置缓存不足问题
大量教程认为首次慢是mark_cache、解压缓存不足，本次实测缓存资源极度充裕，完全不是瓶颈：
-
mark_cache 配置上限：5.00GiB，实际仅占用：6.33MiB，使用率不足0.2%
-
uncompressed_cache 配置上限：8.00GiB，实际占用：0B，完全空闲
同时核对缓存命中日志：热查询mark_cache命中10/0失效，索引缓存完全正常，缓存无瓶颈、无需调优任何缓存参数。
3. 排除业务SQL与表结构问题
本次慢查询为常规列表分页查询，仅读取数百KB数据，无大排序、无聚合、无联表、无全表扫描，SQL写法合规，表ORDER BY排序键与查询过滤条件完全匹配，无索引失效、分区裁剪失效问题。
三、核心数据佐证：定位真实根因（关键实测数据）
通过ClickHouse监控指标，抓取冷热查询核心IO指标，彻底锁定问题根源，数据对比差距极其夸张：
| 冷查询1 | 288KiB | 2484ms | 1243ms | 2590ms |
| 冷查询2 | 1.08MiB | 10899ms | 9133ms | 15730ms |
| 热查询 | 576KiB | 29ms | 0.7ms | 0ms |
数据解读核心结论：
极小数据量、极致耗时：仅读取288KB微量数据，磁盘读取耗时1.2s，IO等待超2.5s，折算磁盘有效吞吐仅0.2MB/s，远低于正常SSD毫秒级响应标准
冷热差异本质：冷查询走物理磁盘IO，热查询命中OS PageCache、无磁盘交互，耗时直接断崖式下跌
全局IO劣化：同机器APM其他查询读取27MiB数据，耗时高达15s，证明整台CH节点磁盘IO被打满，并非单条SQL问题
四、深层根因：后台Merge任务IO饿死
磁盘性能差只是基础问题，真正导致前台查询卡死的核心诱因是：系统表无限膨胀，引发大量后台Merge任务抢占IO。
1. 异常Merge任务现状
节点常驻6个后台合并任务，长期霸占磁盘IO，无法释放：
-
metric_log merge：运行357.5s，进度0%，完全卡死
-
part_log merge：运行257.8s，进度20%（532MiB）
-
index_v3 merge：运行188.2s，进度22%
2. Merge任务原理通俗解释
ClickHouse MergeTree引擎每次写入都会生成小数据分片（part），后台Merge线程会自动合并小分片为大分片，目的是优化查询效率。但Merge过程需要大量读写磁盘，极度消耗IO资源。
正常SSD磁盘：后台Merge无感，不影响前台查询；劣质超卖云盘/机械盘下，Merge会直接占满IO队列，前台查询IO排队饿死。
3. 为什么会有无限Merge？
ClickHouse默认情况下，system.metric_log、system.part_log 两张系统日志表无任何TTL限制：
-
metric_log：每秒采集CH运行指标，持续生成大量小part分片
-
part_log：记录所有分片变更、合并事件，持续增量写入
两张系统表永久保留数据，24小时持续膨胀，源源不断触发后台Merge任务，在劣质磁盘上形成「无限合并→IO打满→前台查询超时」的死循环。
五、关键认知纠正（全网高频误区）
1. TTL ≠ 触发Merge，TTL是数据生命周期管控
很多开发者误以为TTL是用来控制Merge次数，实际完全相反：
-
Merge：自动合并小分片，优化查询，消耗IO
-
TTL：过期数据清理/迁移，不主动触发Merge，只会从源头抑制数据膨胀，减少无效Merge
2. 系统表TTL绝对不影响业务数据
给 system.metric_log、system.part_log 设置TTL，仅清理ClickHouse自身运行监控、分片日志，零侵入业务数据表，不会删除、迁移、修改任何业务历史数据，完全适配业务禁止删数的场景。
3. 冷热分层无法根治本次问题
业务侧ES冷热节点、CH冷热卷存储策略，仅用于区分新旧业务数据存储介质，无法解决系统表Merge抢占IO的核心故障，只能做长期成本优化，不能解决当前查询超时问题。
六、落地解决方案（应急+根治，可直接上线）
1. 根治方案：系统表配置标准TTL（生产通用最佳实践）
针对IO压力大的生产环境，采用通用保守TTL规则，控制系统表数据量，彻底杜绝无限Merge：
-
metric_log（高频指标日志）：保留7天
-
part_log（低频分片日志）：保留7天
上线最优SQL（附带防IO抖动优化）：
— 设置系统表TTL，仅清理CH内部日志，不影响业务数据
ALTER TABLE system.metric_log MODIFY TTL event_time + INTERVAL 7 DAY;
ALTER TABLE system.part_log MODIFY TTL event_time + INTERVAL 7 DAY;
— 核心优化：仅整分片过期才删除，避免局部删数触发额外Merge，大幅降低IO开销
ALTER TABLE system.metric_log MODIFY SETTING ttl_only_drop_parts = 1;
ALTER TABLE system.part_log MODIFY SETTING ttl_only_drop_parts = 1;
2. 临时应急方案：限流后台Merge并发
磁盘IO劣化严重时，临时降低后台合并并发，优先保障前台查询IO资源，临时缓解超时问题：
修改config.xml配置，重启生效：
<merge_tree>
<max_background_merges>2</max_background_merges>
<max_background_fetches>1</max_background_fetches>
</merge_tree>
3. 业务侧临时缓解（不治本）
短期无法修复磁盘、配置TTL时，可缩小查询时间窗口（24h→2h），减少扫描分片数量，降低冷查询IO开销，代价是超期无上报数据会显示为空。
七、运维底层根治建议
核查磁盘介质：当前磁盘为超卖云盘/机械盘，随机IO性能极差，小文件读取吞吐仅0.2MB/s，建议更换标准SSD高性能云盘
核查云盘配额：检查磁盘IOPS、突发限流、队列深度，解除云厂商性能限制
常态化管控系统表：新增query_log、asynchronous_metric_log等系统表TTL规范，避免后续再次出现膨胀问题
八、最终总结与排查流程图
1. 问题根因一句话
本次ClickHouse首次查询慢非缓存、非SQL、非网络问题，是劣质磁盘IO + 系统表无TTL无限膨胀 + 后台Merge任务IO抢占，导致前台冷查询磁盘IO排队饿死，冷热性能差异被极致放大。
2. 标准排查优先级（可复用所有CH慢查询）
排查网络/连接池基准延迟，排除链路问题
核查CH内置缓存使用率，排除缓存瓶颈
通过query_log抓取read_bytes、磁盘耗时、IO等待指标
检查后台Merge任务运行状态，确认IO抢占情况
核查系统表是否无TTL、无限膨胀
底层核查磁盘IOPS、await、%util性能指标
九、拓展：业务冷热数据分层（不删数方案）
业务禁止删除历史数据，需要保留3-6个月热数据、历史数据归档冷存储，可使用CH TTL冷热迁移语法（只迁移不删除，对标ES冷热节点ILM）：
— 业务表3个月热数据存SSD，超期自动迁移至冷存储，数据永久保留
ALTER TABLE business_table MODIFY TTL create_time + INTERVAL 3 MONTH TO VOLUME 'cold';
该方案仅用于业务数据分层，无法解决本次IO卡死问题，仅作为长期性能优化手段。
