先说结论
ClickHouse 优化不能只盯某个参数。先确认业务规模和慢在哪里，再从建模、写入、查询、后台任务和集群资源这五层往下找。讲清楚一次优化，至少要交代：
- 原始业务与数据规模。
- 观察到的瓶颈和量化指标。
- 使用什么证据定位根因。
- 实施了哪些改动，为什么有效。
- 优化后的数据以及副作用。
可以按“建模、写入、查询、后台任务、集群治理”五层展开。
一、建表与数据模型优化
1. 优化分区键
原先按天分区，在多年历史数据和多表场景下形成大量分区。若业务主要按月清理和查询，可调整为按月分区：
PARTITION BY toYYYYMM(event_date)
分区首先用于生命周期管理和粗粒度裁剪。不能使用用户 ID 等高基数字段制造海量分区，也不能把分区当作传统二级索引。
2. 优化排序键
假设查询几乎都携带租户和时间范围：
ORDER BY (tenant_id, event_date, event_type, user_id)
把稳定的高频过滤条件放在前部，使同租户、相近日期的数据聚集，减少读取 Granule 数量。排序键不是字段越多越好，过长会增加索引与排序成本。
优化前后通过 EXPLAIN indexes = 1、read_rows 和 read_bytes 验证裁剪效果，而不是只比较一次耗时。
3. 选择紧凑数据类型
- 能用
UInt32不使用String保存数值 ID。 - 日期使用
Date/DateTime64，不保存格式化字符串。 - 低基数重复字符串使用
LowCardinality。 - 根据数值变化选择 Delta、DoubleDelta、Gorilla 等 Codec。
- 不需要可空语义时避免滥用
Nullable。
类型优化能同时减少磁盘、网络、解压和聚合内存，但必须先验证取值范围，避免未来溢出。
4. 面向查询适度宽表化
原本每次报表都关联事实表和多个小维表，延迟和内存不稳定。可以在数据写入阶段补齐稳定维度字段，或用 Dictionary 加速 ID 到属性映射。
宽表适合历史快照语义；Dictionary 适合需要读取较新维度值的场景。二者对数据一致性的语义不同，必须与产品确认。
二、写入优化
1. 从单条写改为批量写
单条 INSERT 会不断创建小 Part，后台 Merge 跟不上时出现 Too many parts。生产者应在可接受延迟内按条数或字节聚合：
每批 5,000～50,000 行，或达到目标字节数后写入
具体批量不能照抄，应结合单行大小、写入延迟、网络和服务端指标压测。
2. 控制分区跨度
一次写入跨越很多分区会为每个分区创建 Part。消息消费端可以按日期或分区键聚合批次，减少单批触及的分区数量。
3. 使用异步插入
客户端难以批量时，可以评估异步插入，由服务端缓冲小写入并合并。但要明确确认语义、内存限制、失败可见性和客户端是否等待刷入。
4. 处理重复写入
消息重试会导致数据重复。可以携带业务唯一 ID 与版本，使用 ReplacingMergeTree，或在聚合口径中按唯一键去重。
ReplacingMergeTree 去重依赖后台 Merge，不能保证查询瞬间唯一。使用 FINAL 会增加查询成本，应把幂等设计前移并评估业务容忍度。
三、查询优化
1. 禁止 SELECT *
列式数据库的优势来自只读需要的列。列表和报表应明确选择字段，大字符串和 JSON 列尤其不能无条件读取。
2. 增加有效过滤
强制或引导查询携带时间、租户等排序键前缀，限制最大时间跨度。开放式分析平台可以在网关层设置扫描量、执行时间和结果行数限制。
3. 优化高基数聚合
对用户 ID、设备 ID 做精确去重可能消耗大量内存。允许误差的报表可使用近似去重函数，并在业务上明确误差范围。精确财务数据不能直接替换为近似结果。
4. 物化视图预聚合
分钟、小时、天级指标被频繁重复查询时，在写入阶段预聚合：
原始明细表 → 物化视图 → 小时聚合表 → 报表查询
这能把扫描亿级明细变为扫描少量聚合状态。需要处理历史回填、迟到数据、重复事件和聚合口径版本。
5. 避免滥用 FINAL
FINAL 在查询时完成额外合并或去重，可能显著增加 CPU 和内存。可通过上游幂等、版本过滤、预聚合或定期优化减少依赖。
6. 优化 JOIN
- 大表 JOIN 前先过滤和预聚合。
- 小维表可放右侧或使用 Dictionary。
- 选择合适 JOIN 算法，并控制内存与溢出策略。
- 高频固定关联考虑宽表化。
不要通过增加内存上限掩盖无边界大表 JOIN。
四、Merge 与存储优化
1. 监控 Part 数量
关注每表、每分区的 Active Part 数、平均 Part 大小和 Merge 队列。Part 持续增加说明写入粒度、分区设计或后台能力不匹配。
2. 避免频繁 OPTIMIZE FINAL
手动强制合并会消耗大量 IO 和 CPU，并可能与正常查询争抢资源。它不是日常清理命令，只应在明确场景、受控窗口和充分评估后执行。
3. TTL 与冷热分层
近期数据放高性能磁盘，历史数据迁移到低成本卷；到期数据通过 TTL 删除。TTL 执行依赖后台 Merge，需要预留额外磁盘和任务能力。
4. 控制 Mutation
批量 UPDATE/DELETE 会重写数据片段。项目中可以改为追加新版本、定期汇总，或把可变状态保留在 OLTP 数据库。必须执行 Mutation 时，应控制范围并监控队列。
五、集群与资源治理
1. 合理分片
分片键应让数据与负载均匀，并让高频查询尽量只访问少量分片。只按租户分片可能遇到超级租户热点，应评估组合哈希或单独隔离。
2. 副本分流
副本既用于容灾，也可承担读流量。要确保路由策略不会让所有重查询集中到一个节点，同时监控副本延迟和队列。
3. 限制并发与资源
为在线报表、内部分析和离线任务配置不同用户或 Profile，限制：
- 最大执行时间。
- 最大读取行数或字节数。
- 单查询内存。
- 查询并发。
- 线程数。
让在线小查询与离线大查询分池，避免一个临时分析拖垮整个集群。
4. 查询缓存与结果复用
固定报表可以在应用层或数据库能力范围内缓存结果。缓存 Key 必须包含租户、权限、时间范围和口径版本，避免错误共享数据。
六、如何定位优化方向
通过 system.query_log 聚合 SQL 指纹，重点看：
- 查询次数与 P95/P99。
read_rows、read_bytes与结果行数。- 峰值内存和线程数。
- ProfileEvents 中的 IO、网络与 CPU 指标。
- 异常、取消和超时次数。
通过 system.parts 查看 Part 数量和大小，通过 Merge 与 Mutation 系统表观察后台积压。性能问题必须区分查询读放大、写入小 Part、后台合并还是节点资源瓶颈。
看一个优化案例
项目每天写入约 20 亿条埋点，原查询按租户和近 7 天过滤，但排序键只有事件时间，单次报表读取数百 GB。我们重建表，将租户和日期放到排序键前部，写入由逐条改为每批约 2 万行，并建立小时级物化聚合表。通过 query_log 验证，核心报表读取字节降低约 95%，P99 从十几秒下降到 2 秒以内。副作用是写入链路和回填流程更复杂，因此增加了聚合校验与双写切换。
案例里的数字只是说明分析方法。实际判断时要用自己系统的真实数据，重点看定位和验证过程，不要追求夸张的提升比例。
常见问题
追问 1：排序键字段是不是越多越好？
不是。前部字段才决定主要数据裁剪，过长排序键增加索引、排序与存储成本。应围绕最稳定的高频过滤组合设计。
追问 2：物化视图为什么可能数据不一致？
重复写入、迟到事件、历史回填顺序、口径变更和目标表异常都可能造成偏差。需要幂等键、校验任务和可重建流程。
追问 3：为什么不直接调大 max_memory_usage？
它只推迟 OOM，还会让单查询吞噬更多集群资源。应先减少扫描、聚合基数和 JOIN 数据量，再设置与并发容量匹配的上限。
追问 4：如何判断排序键是否有效？
使用 EXPLAIN 查看裁剪范围，并比较 query_log 中读取行数、字节数与总行数。仅凭 SQL 包含排序键字段不能证明裁剪有效。
追问 5：ClickHouse 写入越大批越好吗？
不是。批次过小产生 Part，过大会增加客户端内存、失败重试成本和写入延迟。应按行大小、延迟目标和服务端负载压测平衡。