Clickhouse 二级索引（一）

ClickHouse 二级索引详解

ClickHouse 的二级索引（Skipping Indexes）是稀疏索引，用于快速跳过不满足查询条件的数据块（Granule），大幅减少 I/O 扫描量。与行级索引不同，它不定位具体行，而是跳过整块数据。以下是详细解析：

---

1. 索引核心机制

1. 数据块（Granule）

   • 每个索引项对应一个数据块（默认 8,192 行）

   • 索引存储该数据块的统计信息（如 min/max、哈希值等）

2. 稀疏性

   • 索引不覆盖所有行，按 GRANULARITY 参数间隔创建

3. 查询流程

   • 查询时先检查索引 → 跳过不匹配的数据块 → 仅扫描剩余块

---

2. 二级索引类型及适用场景

2.1. minmax 索引

• 原理：存储每个数据块的列值最小/最大值

• 适用查询：

  WHERE date_col BETWEEN '2023-01-01' AND '2023-01-31'  -- 日期范围
  WHERE price > 100 AND price < 200                    -- 数值区间

• 参数：无额外参数

• 例子：

  INDEX idx_price price TYPE minmax GRANULARITY 4

  • 每 4 个数据块（32,768 行）存储一个 min/max 值

2.2. set 索引

• 原理：存储每个数据块的唯一值集合（最大 max_rows 个）

• 适用查询：

  WHERE status IN (1, 3, 5)          -- 低基数列的离散值
  WHERE country_code = 'US'          -- 枚举型精确匹配

• 参数：max_rows（集合最大元素数）

• 例子：

  INDEX idx_status status TYPE set(100) GRANULARITY 2

  • 每 2 个数据块记录最多 100 个唯一 status 值

2.3. ngrambf_v1 索引

• 原理：使用 N-gram 布隆过滤器存储子串特征

• 适用查询：

  WHERE log_message LIKE '%error%'     -- 模糊子串匹配
  WHERE url LIKE '/api/v1/user/%'      -- 前缀/后缀匹配

• 参数：

  • n：N-gram 长度（默认 3）

  • bloom_size：布隆过滤器大小（字节）

  • hashes：哈希函数数量

  • seed：哈希种子（可选）

• 例子：

  INDEX idx_log log TYPE ngrambf_v1(4, 1024, 3) GRANULARITY 4

  • 使用 4-gram、1KB 布隆过滤器、3 个哈希函数

2.4. tokenbf_v1 索引

• 原理：按分隔符拆分文本为 Token，构建布隆过滤器

• 适用查询：

  WHERE text_col LIKE '%password%'       -- 单词级搜索
  WHERE tags CONTAINS 'urgent'           -- 标签包含查询

• 参数：

  • bloom_size：布隆过滤器大小（字节）

  • hashes：哈希函数数量

  • token_separators：分隔符（默认 ' \t\n'）

• 例子：

  INDEX idx_tags tags TYPE tokenbf_v1(2048, 5, '-') GRANULARITY 2

  • 用 - 分割标签，2KB 布隆过滤器，5 个哈希函数

2.5. bloom_filter 索引

• 原理：标准布隆过滤器存储列值存在性

• 适用查询：

  WHERE user_id = 12345                   -- 高基数列精确匹配
  WHERE order_id IN (1001, 1002, 1003)    -- 离散值过滤

• 参数：

  • bloom_size：布隆过滤器大小（字节）

  • hashes：哈希函数数量

  • false_positive：误报率（默认 0.025）

• 例子：

  INDEX idx_user user_id TYPE bloom_filter(1024, 3) GRANULARITY 1

  • 每 1 个数据块使用 1KB 布隆过滤器

---

3. 索引创建语法

CREATE TABLE logs (
    event_time DateTime,
    user_id UInt64,
    log String,
    INDEX idx_time (toDate(event_time)) TYPE minmax GRANULARITY 2,
    INDEX idx_user (user_id) TYPE bloom_filter GRANULARITY 1,
    INDEX idx_log (log) TYPE tokenbf_v1(2048, 3) GRANULARITY 4
) ENGINE = MergeTree()
ORDER BY (event_time);

• GRANULARITY N：每 N 个数据块创建一个索引项（N=1 表示每个块都有索引）

---

4. 索引选择策略

索引类型	最佳场景	避坑指南
minmax	数值/日期范围查询	高基数列效果差（如 UUID）
set	低基数列（<1000 唯一值）	max_rows 过小导致漏过滤
ngrambf_v1	子串搜索（如 URL、日志）	短文本无效（< N-gram 长度）
tokenbf_v1	关键词搜索（单词、标签）	需匹配分隔符定义
bloom_filter	高基数列精确查询（ID、IP）	布隆过滤器越大，误报率越低

---

5. 性能优化建议

1. 粒度选择

   • 小粒度（GRANULARITY 1）：过滤精度高，但索引存储增大

   • 大粒度（GRANULARITY 10）：节省存储，但跳过效率降低

2. 布隆过滤器参数

   • 理想大小：bloom_size = -n * ln(false_positive) / (ln2)^2
     （n=块内唯一值数）

3. 组合索引

    

   INDEX idx_combo (col1, col2) TYPE minmax GRANULARITY 4

   • 对多列条件联合过滤有效

4. 监控索引效果

   SET send_logs_level = 'trace';
   SELECT ... WHERE indexed_col = ...; -- 查看日志中的 "Index `idx_name` dropped X/Y parts"

---

6. 实战案例

6.1 场景：电商订单查询优化

CREATE TABLE orders (
    order_time DateTime,
    order_id String,          -- 高基数
    user_id UInt64,           -- 高基数
    amount Float32,
    tags Array(String),       -- 标签数组
    INDEX idx_time (toDate(order_time)) TYPE minmax GRANULARITY 3,
    INDEX idx_order (order_id) TYPE bloom_filter(2048, 3) GRANULARITY 1,
    INDEX idx_tags (tags) TYPE tokenbf_v1(4096, 4) GRANULARITY 2
) ENGINE = MergeTree()
ORDER BY (order_time);

• 查询 1：按订单号精准查询

  SELECT * FROM orders WHERE order_id = 'ORD-2023-7890' -- 用 bloom_filter 跳过 99% 数据块

• 查询 2：按标签和日期范围过滤

  SELECT * FROM orders 
  WHERE has(tags, 'discounted')    -- tokenbf_v1 过滤标签
    AND order_time > '2023-06-01'  -- minmax 跳过早期数据

---

7. 总结

• minmax：范围查询首选，尤其时间/数值列

• bloom_filter：高基数精确匹配（替代 WHERE id IN (...)）

• tokenbf_v1：文本关键词搜索（优于 LIKE '%word%'）

• ngrambf_v1：结构化文本子串匹配（如日志、路径）

• set：低基数枚举列（状态码、类型码）

关键点：二级索引的核心是 减少磁盘扫描范围，需根据数据分布、查询模式、基数大小综合设计参数。