跳数索引与查询优化

主键索引（稀疏索引）              跳数索引
每 8192 行记一个键值             每 GRANULARITY × 8192 行记一份统计
用于二分定位起始位置              用于判断「这块里可能有目标值吗」
 
granule:  [0-8191] [8192-16383] [16384-24575] [24576-32767]
                                                              
minmax 索引 (GRANULARITY 4)：
  记录 [0-32767] 这 4 个 granule 里 user_id 的 min=1, max=48213
 
查询 WHERE user_id = 99999
  → 99999 > max(48213)，整块 4 个 granule 全部跳过

ALTER TABLE events
    ADD INDEX idx_duration duration_ms TYPE minmax GRANULARITY 4;
 
ALTER TABLE events MATERIALIZE INDEX idx_duration;   -- 对历史数据生效

好的场景：event_id 随时间递增，主键是 event_time
  block 0: event_id ∈ [1, 8192]
  block 1: event_id ∈ [8193, 16384]     ← 区间不重叠，跳过效果极好
  查 event_id = 20000 → 只读 block 2
 
坏的场景：user_id 完全随机
  block 0: user_id ∈ [1, 49998]
  block 1: user_id ∈ [3, 49999]         ← 区间几乎完全重叠
  查 user_id = 10086 → 每块都"可能有"，一块都跳不掉

ALTER TABLE events
    ADD INDEX idx_page_set page TYPE set(1000) GRANULARITY 4;

-- 检查块内基数
SELECT
    intDiv(rowNumberInAllBlocks(), 32768) AS block,
    uniqExact(page)                       AS distinct_in_block
FROM events
GROUP BY block
LIMIT 5;

ALTER TABLE events
    ADD INDEX idx_user_bf user_id TYPE bloom_filter(0.01) GRANULARITY 4;

-- 加索引前
SELECT count() FROM events WHERE user_id = 10086;
-- Elapsed: 0.184 sec. Processed 20.00 million rows
 
-- 加索引后
ALTER TABLE events ADD INDEX idx_user_bf user_id TYPE bloom_filter(0.01) GRANULARITY 4;
ALTER TABLE events MATERIALIZE INDEX idx_user_bf;
 
SELECT count() FROM events WHERE user_id = 10086;
-- Elapsed: 0.012 sec. Processed 425.98 thousand rows

-- tokenbf：按非字母数字字符切词，适合 URL、日志
ALTER TABLE events
    ADD INDEX idx_page_token page TYPE tokenbf_v1(8192, 3, 0) GRANULARITY 4;
 
-- ngrambf：按 N-gram 切分，适合中文、子串匹配
ALTER TABLE events
    ADD INDEX idx_page_ngram page TYPE ngrambf_v1(4, 8192, 3, 0) GRANULARITY 4;

-- tokenbf 支持
WHERE hasToken(page, 'checkout')
WHERE page LIKE '%checkout%'
WHERE page = '/checkout'
WHERE multiSearchAny(page, ['checkout', 'cart'])
 
-- ngrambf 额外支持任意子串
WHERE page LIKE '%eckou%'      -- tokenbf 无效，ngrambf 有效

GRANULARITY 1：每 8192 行一份统计
  索引最精细，但索引本身大，且构建/加载慢
 
GRANULARITY 4（推荐）：每 32768 行一份
  平衡点
 
GRANULARITY 16：每 131072 行一份
  索引小，但跳过粒度粗

EXPLAIN indexes = 1
SELECT count() FROM events WHERE user_id = 10086;

Expression ((Projection + Before ORDER BY))
  Aggregating
    Expression (Before GROUP BY)
      ReadFromMergeTree (demo.events)
      Indexes:
        MinMax
          Condition: true
          Parts: 4/4
          Granules: 2442/2442
        Partition
          Condition: true
          Parts: 4/4
          Granules: 2442/2442
        PrimaryKey
          Condition: true
          Parts: 4/4
          Granules: 2442/2442
        Skip
          Name: idx_user_bf
          Description: bloom_filter GRANULARITY 4
          Parts: 3/4                    ← 排除了 1 个 part
          Granules: 52/2442             ← 2442 个 granule 只读 52 个

SELECT
    table, name, type_full, granularity,
    formatReadableSize(data_compressed_bytes) AS size
FROM system.data_skipping_indices
WHERE database = 'demo';

┌─table──┬─name──────────┬─type_full──────────────┬─granularity─┬─size──────┐
│ events │ idx_user_bf   │ bloom_filter(0.01)     │           4 │ 1.24 MiB  │
│ events │ idx_duration  │ minmax                 │           4 │ 12.4 KiB  │
│ events │ idx_page_token│ tokenbf_v1(8192, 3, 0) │           4 │ 4.82 MiB  │
└────────┴───────────────┴────────────────────────┴─────────────┴───────────┘

ALTER TABLE events ADD PROJECTION proj_by_user
(
    SELECT *
    ORDER BY (user_id, event_time)
);
 
ALTER TABLE events MATERIALIZE PROJECTION proj_by_user;

events/202406_1_9_2/
├── event_time.bin           ← 主数据，按 (country, event_type, event_time) 排序
├── user_id.bin
├── ...
└── proj_by_user.proj/       ← 投影，按 (user_id, event_time) 排序
    ├── event_time.bin
    ├── user_id.bin
    ├── primary.cidx
    └── ...

SELECT * FROM events WHERE user_id = 10086 ORDER BY event_time;

EXPLAIN indexes = 1 SELECT count() FROM events WHERE user_id = 10086;

      ReadFromMergeTree (demo.events)
      Projections:
        Name: proj_by_user
        Description: Projection has been analyzed and is used
        Condition: true
        Search Algorithm: projection
        Parts: 4
        Granules: 8               ← 从投影读，只需 8 个 granule

ALTER TABLE events ADD PROJECTION proj_daily_agg
(
    SELECT
        toDate(event_time) AS day,
        country,
        count(),
        sum(duration_ms),
        uniqState(user_id)
    GROUP BY day, country
);
 
ALTER TABLE events MATERIALIZE PROJECTION proj_daily_agg;

SELECT toDate(event_time) AS day, country, count()
FROM events GROUP BY day, country;
-- 自动从 proj_daily_agg 读，不扫明细

-- 1. 查询计划（默认）
EXPLAIN SELECT country, count() FROM events GROUP BY country;
 
-- 2. 索引分析（最常用）
EXPLAIN indexes = 1 SELECT count() FROM events WHERE country = 'CN';
 
-- 3. 执行管道
EXPLAIN PIPELINE SELECT country, count() FROM events GROUP BY country;
 
-- 4. 语法树
EXPLAIN AST SELECT 1;
 
-- 5. 优化后的语法树
EXPLAIN SYNTAX SELECT * FROM events WHERE 1 = 1 AND country = 'CN';
 
-- 6. 估算读取量
EXPLAIN ESTIMATE SELECT count() FROM events WHERE country = 'CN';

-- EXPLAIN ESTIMATE 输出
┌─database─┬─table──┬─parts─┬────rows─┬─marks─┐
│ demo     │ events │     4 │ 4098048 │   501 │
└──────────┴────────┴───────┴─────────┴───────┘

-- PIPELINE 能看到并行度
EXPLAIN PIPELINE SELECT country, count() FROM events GROUP BY country;

(Expression)
ExpressionTransform × 8              ← 8 个并行线程
  (Aggregating)
  Resize 8 → 8
    AggregatingTransform × 8
      StrictResize 8 → 8
        (Expression)
        ExpressionTransform × 8
          (ReadFromMergeTree)
          MergeTreeThread × 8 0 → 1

-- 最近的慢查询 Top 10
SELECT
    event_time,
    query_duration_ms,
    formatReadableQuantity(read_rows)            AS rows,
    formatReadableSize(read_bytes)               AS bytes,
    formatReadableSize(memory_usage)             AS mem,
    result_rows,
    substring(query, 1, 100)                     AS q
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_date = today()
  AND query_kind = 'Select'
ORDER BY query_duration_ms DESC
LIMIT 10;

┌──────────event_time─┬─query_duration_ms─┬─rows──────────┬─bytes─────┬─mem───────┬─result_rows─┬─q──────────────────────────────┐
│ 2024-07-30 12:04:12 │              4218 │ 20.00 million │ 1.24 GiB  │ 820 MiB   │           5 │ SELECT country, uniqExact(us...│
│ 2024-07-30 12:03:41 │              1842 │ 20.00 million │ 480 MiB   │ 240 MiB   │        1000 │ SELECT page, count() FROM ev...│
└─────────────────────┴───────────────────┴───────────────┴───────────┴───────────┴─────────────┴────────────────────────────────┘

-- 找出最消耗资源的查询模式（normalized_query_hash 会把参数不同的同类查询归并）
SELECT
    normalized_query_hash,
    count()                                          AS runs,
    round(avg(query_duration_ms), 1)                 AS avg_ms,
    max(query_duration_ms)                           AS max_ms,
    formatReadableQuantity(sum(read_rows))           AS total_rows,
    formatReadableSize(max(memory_usage))            AS peak_mem,
    any(substring(query, 1, 80))                     AS sample
FROM system.query_log
WHERE type = 'QueryFinish' AND event_date >= today() - 7 AND query_kind = 'Select'
GROUP BY normalized_query_hash
ORDER BY sum(query_duration_ms) DESC
LIMIT 10;

SELECT
    query_duration_ms,
    ProfileEvents['SelectedParts']              AS parts,
    ProfileEvents['SelectedMarks']              AS marks,
    ProfileEvents['SelectedRanges']             AS ranges,
    ProfileEvents['OSReadBytes']                AS disk_read,
    ProfileEvents['OSCPUVirtualTimeMicroseconds'] / 1000 AS cpu_ms,
    ProfileEvents['NetworkSendBytes']           AS net_send
FROM system.query_log
WHERE query_id = '你的-query-id' AND type = 'QueryFinish';

SELECT
    event_time, query_duration_ms,
    exception_code, exception,
    substring(query, 1, 200) AS q
FROM system.query_log
WHERE type = 'ExceptionWhileProcessing' AND event_date = today()
ORDER BY event_time DESC
LIMIT 5;

第 1 步：看扫描量
  SELECT ... 的输出末尾 "Processed X rows"
  或 EXPLAIN ESTIMATE
  ├─ 扫描量 ≈ 全表 ──▶ 索引没生效，往下走
  └─ 扫描量已经很小 ──▶ 问题在计算/内存，跳到第 5 步
 
第 2 步：分区裁剪
  EXPLAIN indexes = 1 看 MinMax / Partition 的 Parts: x/y
  ├─ 没裁剪 ──▶ WHERE 里的时间列被函数包了？加时间范围条件？
  └─ 已裁剪 ──▶ 继续
 
第 3 步：主键索引
  看 PrimaryKey 的 Granules: x/y
  ├─ 比值接近 1 ──▶ 排序键不匹配查询模式
  │                  → 考虑加 Projection 或重建表
  └─ 比值很小 ──▶ 继续
 
第 4 步：跳数索引
  按非排序键列过滤？
  ├─ 等值 + 高基数 ──▶ bloom_filter
  ├─ 范围 + 与主键相关 ──▶ minmax
  ├─ 块内低基数 ──▶ set(N)
  └─ 文本包含 ──▶ tokenbf_v1 / ngrambf_v1
 
第 5 步：计算优化
  ├─ uniqExact → uniqCombined
  ├─ count(DISTINCT) → uniq
  ├─ JOIN → 字典 / IN
  ├─ 窗口函数 → 先聚合再开窗
  ├─ SELECT * → 只选需要的列
  └─ 正则 → startsWith / position
 
第 6 步：并发与资源
  ├─ max_threads 是否够
  ├─ 内存是否触及 max_memory_usage
  └─ 是否被其他查询挤占（system.processes）

-- 正在跑的查询
SELECT
    query_id,
    elapsed,
    round(read_rows / elapsed) AS rows_per_sec,
    formatReadableSize(memory_usage) AS mem,
    user,
    substring(query, 1, 80) AS q
FROM system.processes
ORDER BY elapsed DESC;
 
-- 杀掉失控的查询
KILL QUERY WHERE query_id = 'xxx-xxx-xxx';
KILL QUERY WHERE elapsed > 300 AND user = 'analyst';