Clickhouse 二级索引（二）

1. 添加二级索引的完整语法

ALTER TABLE [db_name.]table_name 
ADD INDEX index_name expression 
TYPE index_type([parameters]) 
GRANULARITY granularity_value
[AFTER existing_index_name]  -- 可选：指定索引位置

---

2. 操作步骤详解（附示例）

2.1 步骤 1：添加索引定义

-- 示例：为 user_actions 表添加 minmax 和 bloom_filter 索引
ALTER TABLE user_actions
ADD INDEX idx_event_time (event_time) TYPE minmax GRANULARITY 3,
ADD INDEX idx_user_id (user_id) TYPE bloom_filter(1024, 3) GRANULARITY 1;

2.2 步骤 2：关键操作 - 物化索引到现有数据

-- 物化所有新索引
ALTER TABLE user_actions MATERIALIZE INDEX idx_event_time;
ALTER TABLE user_actions MATERIALIZE INDEX idx_user_id;

-- 或批量物化（ClickHouse 22.8+）
ALTER TABLE user_actions MATERIALIZE ALL INDEXES;

重要：

• ADD INDEX 只修改元数据，不会处理历史数据

• MATERIALIZE INDEX 会重写数据部分（data parts），使索引对现有数据生效

---

3. 操作原理图解

---

4. 关键参数详解

参数	作用	示例值	注意事项
index_name	索引标识名	idx_user_id	全局唯一，建议小写下划线命名
expression	索引表达式	user_id toDate(event_time)	支持列名/函数表达式
TYPE	索引类型	minmax, bloom_filter	必须大写
parameters	类型专用参数	(1024, 3) (2048, 5, '-')	布隆过滤器需指定大小/哈希数
GRANULARITY	索引粒度	1, 3, 10	核心参数：值越小索引越密
AFTER	索引位置	AFTER idx_existing	优化查询计划器选择顺序

---

5 不同类型索引添加示例

5.1. MinMax 索引（范围查询）

 

ALTER TABLE logs
ADD INDEX idx_timestamp (timestamp) TYPE minmax GRANULARITY 4;

ALTER TABLE logs MATERIALIZE INDEX idx_timestamp;

适用查询：WHERE timestamp > '2023-01-01'

5.2. Bloom Filter 索引（高基数精确匹配）

ALTER TABLE users
ADD INDEX idx_email (email) TYPE bloom_filter(2048, 4) GRANULARITY 2;

ALTER TABLE users MATERIALIZE INDEX idx_email;

适用查询：WHERE email = 'user@example.com'

5.3. TokenBF 索引（关键词搜索）

ALTER TABLE products
ADD INDEX idx_description (description) 
TYPE tokenbf_v1(4096, 5) GRANULARITY 3;

ALTER TABLE products MATERIALIZE INDEX idx_description;

适用查询：WHERE description LIKE '%wireless%'

---

6. 注意事项与最佳实践

6.1. 数据重写规则

操作	影响范围	是否锁表
ADD INDEX	仅元数据	瞬时完成
MATERIALIZE INDEX	重写相关数据块	异步后台执行
新数据写入	自动构建索引	无额外开销

6.2. 生产环境建议

-- 低峰期执行数据重写
ALTER TABLE sales MATERIALIZE INDEX idx_product_id 
SETTINGS mutations_sync = 2;  -- 同步等待完成

-- 查看进度
SELECT * FROM system.mutations 
WHERE table = 'sales' AND command LIKE '%MATERIALIZE%';

6.3. 索引管理命令

-- 删除索引
ALTER TABLE user_actions DROP INDEX idx_old_index;

-- 临时禁用索引
ALTER TABLE user_actions DISABLE INDEX idx_user_id;

-- 启用索引
ALTER TABLE user_actions ENABLE INDEX idx_user_id;

---

7. 验证索引效果

EXPLAIN indexes=1
SELECT count() FROM user_actions 
WHERE user_id = 12345;

-- 输出关键信息
"Index `idx_user_id` has dropped 1523/1820 parts"

---

8. 常见问题解决

1. 索引未生效？

   • 确认执行了 MATERIALIZE INDEX

   • 检查 system.mutations 无失败任务

2. 重写数据太慢？

   ALTER TABLE ... MATERIALIZE INDEX ...
   SETTINGS max_part_loading_threads=16;  -- 增加线程数

3. 参数选择困难？
   使用自适应布隆过滤器（ClickHouse 22.8+）：

    

   TYPE adaptive_bf_v1(0.025)  -- 指定期望误报率

通过合理添加二级索引，可提升查询性能 10-100 倍，尤其对大表范围查询和高基数点查效果显著。