ClickHouse 索引与查询优化 是提升分析性能的核心。ClickHouse 的“快”并非魔法，而是建立在对 稀疏索引、数据排序、分区裁剪、执行引擎 的深刻理解和合理使用之上。

本篇将系统、深入地讲解 ClickHouse 的索引机制与查询优化策略，帮助你从“能用”走向“用得好”。

---

🔍 一、深入理解稀疏索引（Sparse Index）

✅ 什么是稀疏索引？

与传统数据库（如 MySQL 的 B+ 树）不同，ClickHouse 使用的是 稀疏索引（Sparse Index） —— 它不是每行建索引，而是每隔一定数量的行（默认 8192 行）记录一个“标记”（mark），存储该位置的主键值。

📌 关键点：

• 稀疏：索引条目远少于数据行数 → 占用内存小
• 有序：数据按 ORDER BY 物理排序，索引基于此构建
• 用于跳过数据块：查询时快速定位相关数据块，跳过无关部分

---

✅ 稀疏索引如何工作？

假设表结构：

CREATE TABLE logs (
    event_date Date,
    city String,
    user_id UInt32
) ENGINE = MergeTree()
ORDER BY (event_date, city, user_id);

数据按 (event_date, city, user_id) 排序存储：

event_date	city	user_id
2024-04-01	Beijing	1001
2024-04-01	Beijing	1002
…	…	…
2024-04-01	Shanghai	2001
2024-04-02	Beijing	1003

稀疏索引每隔 8192 行记录一个主键值：

Mark 0: (2024-04-01, Beijing, 1001)
Mark 1: (2024-04-01, Shanghai, 2001)
Mark 2: (2024-04-02, Beijing, 1003)

查询示例：

SELECT * FROM logs WHERE event_date = '2024-04-01' AND city = 'Beijing'

1. 稀疏索引定位到 event_date='2024-04-01' 且 city='Beijing' 的数据块范围；
2. 只扫描这些数据块，跳过 Shanghai 和 2024-04-02 的数据；
3. 结合列式存储，只读取相关列。

✅ 效果：大幅减少 I/O 和计算量。

---

✅ 为什么 ORDER BY 键的选择至关重要？

💥 在 ClickHouse 中，ORDER BY 就是主键（Primary Key）！

• ORDER BY 决定了数据的物理存储顺序
• 主键（Primary Key）默认等于 ORDER BY 的前缀
• 稀疏索引基于主键构建
• 查询条件中使用 ORDER BY 字段才能有效利用索引

📌 选择原则：

1. 最常用于过滤的字段放前面
   如 WHERE date = ? AND city = ? → ORDER BY (date, city)
2. 高基数字段优先（如 user_id）有助于更细粒度跳过
3. 避免将低选择性字段放前面（如 status=‘active’）
4. 考虑聚合字段：常 GROUP BY city → 把 city 放 ORDER BY 中

❌ 错误示例：

ORDER BY user_id
-- 查询 WHERE event_date = '2024-04-01' → 无法利用索引，全表扫描！

✅ 正确示例：

ORDER BY (event_date, user_id)
-- WHERE event_date 过滤 → 快速跳过无关数据块

---

🔎 二、使用 EXPLAIN 查看执行计划

EXPLAIN 是分析查询性能的第一步。

1. EXPLAIN —— 查看逻辑执行树

EXPLAIN SELECT * FROM logs WHERE event_date = '2024-04-01';

输出：

Filter (WHERE)
  ReadFromStorage (logs)

• 如果没有 IndexCondition，说明未走索引

2. EXPLAIN ESTIMATE —— 估算扫描量

EXPLAIN ESTIMATE SELECT * FROM logs WHERE event_date = '2024-04-01';

输出：

rows:  1000000
bytes: 209715200
parts: 3

• parts: 3 → 只扫描 3 个数据片段（好）
• 如果 parts 很大 → 分区或索引设计不佳

3. EXPLAIN PIPELINE —— 查看并行度

EXPLAIN PIPELINE SELECT COUNT(*) FROM logs GROUP BY city;

• 观察 Aggregating 阶段是否并行
• ReadFromStorage 是否多线程读取

---

📊 三、分析系统表监控查询与分区

1. system.query_log —— 慢查询分析

SELECT
    query,
    query_duration_ms,
    read_rows,
    read_bytes,
    result_rows,
    event_time
FROM system.query_log
WHERE event_date >= today() - 1
  AND query LIKE '%logs%'
ORDER BY query_duration_ms DESC
LIMIT 10;

✅ 用于：

• 定位慢查询
• 分析扫描数据量（read_rows）
• 评估性能瓶颈

---

2. system.parts —— 分析数据片段（Part）

SELECT
    name,
    rows,
    bytes_on_disk,
    part_type,
    modification_time,
    partition
FROM system.parts
WHERE table = 'logs'
  AND database = 'default'
ORDER BY modification_time DESC;

✅ 关注：

• rows：是否生成大量小 Parts（写入频繁）
• bytes_on_disk：压缩效果
• partition：分区是否合理
• part_type：Compact（小数据）、Wide（大数据）

优化建议：

• 大量小 Parts → 合并慢 → 优化写入批次
• Compact Parts 多 → 影响查询性能 → 增加写入批量

---

🚀 四、常见查询优化技巧

1. ❌ 避免 SELECT *

-- 错误
SELECT * FROM logs;

-- 正确
SELECT event_date, city, COUNT(*) FROM logs GROUP BY event_date, city;

✅ 原因：列式存储，只读所需列 → 减少 I/O

---

2. ✅ 使用预聚合（Aggregation）

• 对高频聚合查询，使用物化视图预计算
• 使用 SummingMergeTree、AggregatingMergeTree

-- 预聚合表
CREATE MATERIALIZED VIEW daily_city_stats
TO city_daily
AS SELECT
    event_date,
    city,
    sum(1) AS pv,
    uniqState(user_id) AS uv
FROM logs
GROUP BY event_date, city;

---

3. ✅ 利用分区裁剪（Partition Pruning）

• 查询时带上分区键，自动跳过无关分区

-- 好：走分区裁剪
SELECT * FROM logs WHERE event_date = '2024-04-01';

-- 坏：无法裁剪
SELECT * FROM logs WHERE toYYYYMM(event_date) = 202404;

✅ 分区键建议：toYYYYMM(event_date)、city 等粗粒度字段

---

4. ✅ 注意函数下推（Function Pushdown）

• 尽量让过滤条件在存储层执行
• 避免在 WHERE 中对列使用函数

-- ❌ 函数在列上，无法下推
SELECT * FROM logs WHERE substring(city, 1, 1) = 'B';

-- ✅ 改为前缀匹配（可下推）
SELECT * FROM logs WHERE city LIKE 'B%';

✅ 推荐使用：IN, =, >, <, LIKE 'prefix%'

---

5. ✅ 合理设置索引粒度

SETTINGS index_granularity = 8192,
         index_granularity_bytes = 1048576;  -- 启用自适应粒度

• 默认已启用，无需修改
• 小数据表可减小粒度提高精度

---

6. ✅ 批量写入，避免小写

• 每次 INSERT 至少 1000 行
• 使用 INSERT INTO ... SELECT 或文件导入

---

✅ 五、优化 checklist

项目	是否优化
ORDER BY 是否包含常用 WHERE 字段？	✅ / ❌
是否避免 SELECT *？	✅ / ❌
是否使用分区裁剪？	✅ / ❌
查询是否走了稀疏索引？（EXPLAIN）	✅ / ❌
是否有大量小 Parts？（system.parts）	✅ / ❌
慢查询是否扫描过多数据？（query_log）	✅ / ❌
是否使用预聚合？	✅ / ❌
写入是否批量？	✅ / ❌

---

🎯 六、总结：ClickHouse 查询优化核心思想

“快”的本质是：少读、少算、多并行”

优化手段	目标
ORDER BY + 稀疏索引	减少扫描数据块
分区裁剪	跳过无关分区
列式存储	只读所需列
预聚合	提前计算，查询秒级响应
批量写入	减少 Parts，提升查询效率
EXPLAIN + system 表	可视化性能瓶颈