ClickHouse存量数据索引未生效：创建索引后查询仍全表扫描

核心原因 #

通过ALTER TABLE ADD INDEX新增的二级索引，不会自动为存量数据生成索引文件。ClickHouse的二级索引仅在数据part被合并（Merge）时才会构建：

• 创建表时同步定义索引，数据写入过程中会直接生成对应索引；
• 存量数据所在的历史part未触发合并操作，因此没有生成新添加的索引，查询时自然无法利用。

存量数据使用索引的关键注意事项 #

• 二级索引依赖MergeTree的合并机制，存量part必须经过合并才能生成索引，后台自动合并有触发条件（如part大小、时间间隔），不会立即执行；
• 索引的GRANULARITY设置需匹配数据粒度，过大的粒度可能导致索引过滤精度不足，ClickHouse optimizer可能选择不使用索引；
• 必须使用EXPLAIN indexes=1才能准确查看索引是否被命中，普通EXPLAIN无法展示索引使用细节；
• ClickHouse不支持USE INDEX语法，无法强制指定索引，只能通过优化表结构、索引类型或触发合并让optimizer自动选择。

解决方法与优化步骤 #

1. 手动触发合并生成索引 #

执行OPTIMIZE TABLE强制合并所有存量part，生成对应索引：

OPTIMIZE TABLE devices FINAL;

注意：FINAL参数会强制合并所有分区的part，大表执行会消耗大量资源，建议在业务低峰期操作。如果仅需处理特定分区，可添加分区条件：

OPTIMIZE TABLE devices PARTITION '2024-01-01' FINAL;

2. 调整自动合并策略（可选） #

修改MergeTree的合并配置，让后台更快触发存量part的合并：

• 针对单表设置（无需重启ClickHouse）：

  ALTER TABLE devices MODIFY SETTING 
      merge_tree_max_size_to_merge = 100 * 1024 * 1024,  -- 降低触发合并的part大小阈值
      merge_tree_min_rows_to_merge = 10000;             -- 降低触发合并的最小行数
  

• 全局配置：在config.xml中修改对应参数，重启后生效。

3. 验证索引生效情况 #

使用带索引细节的EXPLAIN语句确认：

EXPLAIN indexes=1
SELECT *
FROM devices
WHERE properties_device_type = 'Android';

若输出中包含Using index device_type，则说明索引已成功生效并被使用。

4. 优化索引类型（可选） #

针对枚举型字段（如设备类型），可根据数据基数选择更合适的索引：

• 低基数场景：set索引（当前使用的类型）或minmax索引；
• 中高基数场景：bloom_filter索引，过滤效果更稳定。

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