ClickHouse新增skip索引后无性能提升 如何排查索引生效情况

1. 低版本排查索引命中的核心方法 #

你当前使用的版本不支持EXPLAIN查看索引命中，可以通过开启trace日志获取索引执行详情：

• 临时开启当前会话的trace级日志输出：SET send_logs_level = 'trace';
• 执行你要测试的业务查询，在返回的日志内容中搜索关键词Skipped.*granules.*by index，如果存在对应new_index1/new_index2的日志条目，说明索引已成功命中，条目中会明确标注该索引跳过了多少个数据颗粒；如果未搜索到对应索引的相关日志，说明索引没有被查询触发。
• 调试完成后将日志级别恢复为默认值，避免占用过多资源：SET send_logs_level = 'info';

2. 确认存量数据索引已构建完成 #

即使执行了OPTIMIZE FINAL，也需要确认索引实际构建状态：

• 执行以下SQL查询索引构建状态：

SELECT 
    partition,
    name,
    type,
    status,
    data_compressed_bytes
FROM system.parts_columns
WHERE database = '你的库名'
  AND table = '你的表名'
  AND name IN ('new_index1', 'new_index2')
ORDER BY partition;

• 只有status列值为Complete时，对应分区的索引才生效；如果为Pending说明后台还在构建索引，可通过SELECT * FROM system.merges WHERE table = '你的表名'查看合并任务进度，所有合并完成后索引才会全量生效。

3. 确认查询符合set类型索引的触发规则 #

你创建的是set类型跳数索引，必须满足以下条件才会被触发：

• WHERE查询条件中必须包含索引对应字段的等于判断、IN判断，不能是范围判断、经过函数运算后的条件
• 过滤的字段值不能是全量高频值：如果过滤的eVar1/prop0值在每一个数据颗粒中都存在，索引无法跳过任何颗粒，自然不会有性能提升
• 你设置的GRANULARITY = 1代表每个索引颗粒对应1个默认数据颗粒（8192行），如果你的查询本身需要扫描的颗粒数量极少，也不会产生可感知的性能差异。

4. 检查索引相关配置是否正常 #

• 先查询集群的跳数索引开关配置：

SELECT name, value FROM system.settings 
WHERE name IN ('use_skip_indexes', 'force_data_skipping_indices');

• 确保use_skip_indexes值为1，没有关闭跳数索引功能
• 测试阶段可以临时强制查询使用指定索引，验证索引有效性：SET force_data_skipping_indices = 'new_index1,new_index2';，配置后再执行查询对比性能变化。

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