ClickHouse MergeTree 引擎：排序键设计决定查询天花板

ClickHouse MergeTree 引擎排序键设计决定查询天花板公司上了 ClickHouse 之后一张 20 亿行的用户行为表同样的数据量有人查出结果只要 0.2 秒有人等了 30 秒没出来。差距全在排序键上。今天聊聊排序键这个沉默的性能杀手。一、MergeTree 的存储哲学先排好再用MergeTree 最核心的设计理念跟菜场卖菜一个道理把顾客常买的东西摆在最显眼的位置。你去菜场买葱姜蒜它们一定在最近的地方 —- 因为卖菜大爷早就观察出来了十个顾客九个要买葱。MergeTree 也是这么干的。写入数据时它按排序键ORDER BY把数据物理排序后写入磁盘。查询时主键PRIMARY KEY就是索引进场券 —- ClickHouse 用主键快速定位到数据块granule然后在这个块内按排序键做二分查找。graph TB subgraph 写入流程 A[INSERT 数据行] -- B[按排序键物理排序] B -- C[切分成 granule 粒块] C -- D[写入 .bin 数据文件] D -- E[更新稀疏索引 .mrk] end subgraph 查询流程 F[WHERE 条件] -- G[主键索引定位目标 granule] G -- H[二分查找定位具体行] H -- I[读取目标行返回结果] end style B fill:#f96,stroke:#333 style G fill:#69f,stroke:#333关键区别来了主键和排序键不是一回事。建表时这样写CREATE TABLE user_events ( event_date Date, event_time DateTime, user_id UInt64, event String, page_url String ) ENGINE MergeTree() PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, user_id, event_time) -- 排序键物理排序依据 PRIMARY KEY (event_date, user_id) -- 主键索引依据必须是排序键的前缀排序键(event_date, user_id, event_time)决定了数据在磁盘上的物理排布顺序。主键(event_date, user_id)只取前两列做索引。能享受索引加速的查询WHERE 条件必须覆盖主键的前缀列。二、排序键设计三铁律铁律一高频过滤字段放最前面。想想菜场的例子 —- 葱在最前面因为所有人买葱。同理如果你的查询 90% 都带日期范围那就把event_date放在排序键第一位。这样索引能直接跳过 90% 不相关日期的 granule。-- 按 (event_date, user_id) 排序时 -- 查询 event_date2026-07-01 AND user_id1001 -- 索引先按 event_date 过滤 granule大部分跳过 -- 在剩余 granule 中按 user_id 二分 SELECT count() FROM user_events WHERE event_date 2026-07-01 AND user_id 1001;如果把顺序反过来ORDER BY (user_id, event_date)那查某一天的某个用户仍然快因为两个字段都能走索引但查某一天的全部用户就惨了 —- 数据按 user_id 排序而非日期同一天的数据散落在磁盘各处ClickHouse 被迫扫描大量 granule。铁律二基数从低到高排列。排序键不是主键可以放超过 3 列。列的排列顺序原则是低基数列在前高基数列在后。比如(event_date, event, user_id)比(user_id, event, event_date)压缩率更高。因为低基数列相邻行的值相同ClickHouse 的列式压缩算法LZ4/ZSTD能大幅受益。铁律三1 granule 覆盖 8192 行勿动摇。默认index_granularity8192意味着索引每 8192 行才记录一个标记mark。排序键设计不好一个 granule 内数据跨度太大二分查找的效力就大打折扣。-- 检查某个日期范围内有多少 granule 被扫描 -- 被扫描越多说明排序键对这类查询越不利 EXPLAIN indexes 1 SELECT count() FROM user_events WHERE event_date BETWEEN 2026-07-01 AND 2026-07-07;三、实战推演三种排序键方案对比拿一张订单表来练练字段order_date、user_id、product_category、amount每天 500 万行。常见查询有两类查询A按用户查所有订单BI 报表查询B按日期品类查汇总运营看板graph LR subgraph 方案1 A1[ORDER BY order_date,br/user_id, product_category] end subgraph 方案2 A2[ORDER BY user_id,br/order_date, product_category] end subgraph 方案3 A3[ORDER BY order_date,br/product_category, user_id] end style A1 fill:#9f9,stroke:#333 style A2 fill:#ff9,stroke:#333 style A3 fill:#f99,stroke:#333方案排序键查询A耗时查询B耗时压缩率方案1(order_date, user_id)~2s~0.05s高方案2(user_id, order_date)~0.01s~5s中方案3(order_date, product_category)~10s~0.01s高方案1 是典型的高频字段优先 —- 大多数运营看板查询都带日期所以order_date排第一。方案2 在按用户查时最快但运营看板的按日期范围查询就慢了因为同一天的订单分布在磁盘各处。方案3 则是为特定品类分析的极端优化通用性最差。没有完美方案只有最常见的查询决定了排序键的设计。四、排序键改不动时怎么办线上表建好才发现排序键不合理想ALTER TABLE MODIFY ORDER BY不行 —- MergeTree 不支持在线修改排序键。数据需要重排只能通过重建表或物化视图来迂回-- 方案1用物化视图换排序键原始表不变 CREATE MATERIALIZED VIEW user_events_by_date ENGINE MergeTree() ORDER BY (event_date, user_id) POPULATE -- 将已有数据回填 AS SELECT * FROM user_events;物化视图的本质是一张新表用不同的排序键存储同一份数据。写入原始表时ClickHouse 自动同步到物化视图。这相当于给数据做了两种排布不同查询走不同的表 —- 代价是占用双倍存储。-- 方案2创建投影Projection更轻量的方案 -- 语法在 ALTER TABLE 时添加 ALTER TABLE user_events ADD PROJECTION proj_by_date (SELECT * ORDER BY event_date, user_id);Projection 是 ClickHouse 21.6 引入的特性比物化视图更轻量。它在数据写入时自动维护一份按不同排序键的数据影子查询优化器自动选择最优的投影。不需要手动在查询里切换表对应用层完全透明。记住一句排序键是建表时就要慎重选择的设计决策。设计错了最多用物化视图兜底但双份存储不是谁都能承受的。五、总结MergeTree 的排序键不是简单把常用字段丢给 ORDER BY 就完事了。它决定了数据的物理排布、索引扫描效率、列式压缩率甚至会影响到分布式场景下分片数据的局部性。落地三句话最高频的过滤字段放第一位排序键字段不要超过 3~4 个多了写入会慢建表后改不了想好再动手。如果业务场景已经确定花半个小时跑EXPLAIN indexes1验证排序键效果比上线后改排布划算得多。