ClickHouse MergeTree表适配员工聚合查询的最优主键设计

你当前查询慢的根源，和(hotel, date)的排序键设计与查询逻辑的匹配度问题直接相关。

• 现有排序键下，同一个酒店的所有数据会严格按照时间顺序连续存储，同个时间点下不同员工的记录是混杂排布的。你执行的查询虽然能通过稀疏索引快速定位到指定酒店、指定时间范围的所有数据块，但这些数据块里没有任何按员工维度的有序性：你需要把范围内所有行全部读出来，在内存里通过哈希分组的方式，逐个把task_number累加到对应员工的聚合桶里，整个过程没办法利用数据的物理连续特性减少扫盘量，也没办法降低聚合阶段的内存开销，自然执行速度上不去。

这个调整方向完全贴合你的核心查询逻辑，调整之后的收益非常明确：

• 物理存储上会变成：同一个酒店下，先按员工标识把所有记录归拢到连续的存储区段，同一个员工的所有记录再按时间顺序连续排布。
• 对应你的查询执行路径会大幅简化：
  1. 通过稀疏索引快速定位到目标酒店的存储区段，直接跳过其他酒店的无关数据
  2. 每个员工的记录是连续存放的，聚合阶段不需要在零散的存储位置跳着找同一个员工的记录，甚至可以做到边扫边累加，不需要单独开辟大体积的哈希表存每个员工的临时聚合值，内存占用能降一个量级
  3. 因为员工的记录内部是按时间排序的，每个员工的区段里都能通过索引快速定位到你查询的时间范围的起止点，跳过每个员工不在查询区间的历史/未来数据，整体扫盘量比之前的混排模式小很多
• 这里要避开一个常见误区：排序键的顺序不需要严格匹配WHERE子句的顺序，要遵循等值过滤列在前、用于归拢分组的列居中、带范围查询的列靠后的原则，你选的hotel -> staff_member -> date的顺序，完全符合这个原则，没有设计上的硬伤。

• ClickHouse的排序键直接决定全表的物理存储顺序，调整排序键不是简单改个配置，需要对全表数据做重写，提前评估好存量数据的迁移耗时，选择业务低峰期操作
• 要同步排查同一张表上跑的其他高频查询：如果存在大量按酒店维度、跨员工统计整体时间粒度的报表类查询（比如某天全酒店的总任务量、按天的任务趋势），把staff_member放到date列前面，会让这类查询的数据连续性变差——同个时间点的不同员工数据会分散在各个员工的存储区段里，扫盘的连续性会比原有的(hotel, date)设计差一点，需要提前对比两类查询的QPS，做优先级取舍
• 排序键的列不要贪多，通常控制在3-4列以内最优，列数过多会导致稀疏索引体积膨胀，反而拖慢索引的查找效率，你目前选的3列长度是非常合适的，不需要再加额外列

• 针对date列增加minmax类型的跳数索引，就算不调整排序键，也能帮执行器快速跳过不在查询时间范围内的数据块，减少无效扫盘
• 搭建粒度为(hotel, staff_member, 日期)的预聚合物化视图，提前把每天每个员工的任务总量计算好，业务查询直接查预聚合表，扫的数据量会从明细行的量级降到每天每员工1行的量级，提效效果比单纯调整排序键更明显
• 确认task_number字段的精度需求，如果不需要Double级别的精度，换成Float32类型能让单值的存储体积减半，进一步降低扫盘的IO开销

内容的提问来源于stack exchange，提问作者anon