ClickHouse:排序键如何选择,如何设置索引以加快查询速度?
简单选取排序键方式:
- 列出该表常用的 SELECT 语句。
- 对 WHERE 条件后的列,按使用比重选取出 [1,5] 个作为备选。
- 根据备选列的基数从小到大排序,得出最终排序键的顺序。
- 排序键数量保持在 [1,5] 个。不设置和超出数量都不好。
多个排序字段的顺序,可以遵循两个原则:
- WHERE 子句中出现频次高的字段放到频率低字段的前面,增加查询命中索引的概率;
- 维度基数大的字段放到维度基数小字段的后面,降低查询扫描范围。
如何判断排序键是否合适?
决定排序键字段顺序的两个因素依次是:使用频率,字段基数。
什么是字段基数?
字段去重统计的值,即 SELECT COUNT(DISTINCT col)
的结果。
以性别为例,gender
有两种情况,即 gender=0
或 gender=1
,那么 gender
的基数为 2。
Case1:
某表常用查询语句为:
SELECT id, name, age, gender, weight
WHERE gender=1, age=20
gender
与 age
可设置为排序键(ORDER BY)。(排序键的选取对象,通常为查询语句 WHERE 条件的子集)
假设 age
的基数在 100 左右,当 gender
与 age
的使用频率相同时,表排序键应设置为 (gender, age)
。(频率相同,以字段基数为主)
Case2:
问题描述:表常用筛选项有 2 个:订单 ID(粒度细)、计划 ID。一个计划可包含多个订单,如何设置排序键?
最佳方案:排序键设置为:(计划ID, 订单ID)
,查询订单时,带上计划过滤条件。
降级方案:查询订单时,无法带上计划过滤条件,那么按两个字段的使用频率设置;频率不清楚时,按基数来;两个都是高基数时,一般认为更高基数的放前面,过滤效果会更好一些。
补充:
- ClickHouse 建表时指定的 ORDER BY 作用于顺序存储数据,即数据写入时根据 ORDER BY 排序写入。
- 查询时被 WHERE 条件字段触发,正确设置可大幅减少数据扫描量,大大提高查询速度。
- 与查询语句中的 ORDER BY 子句无关。