罗罗编程学习记录
本页内容 展开查看

05.8.2 OLAP 查询优化

所属:全栈开发面试题库 / 05 数据库与存储 / 05.8 OLAP 与列存

覆盖范围

  • 分区裁剪、排序键过滤、数据跳过
  • 预聚合与物化视图
  • 宽表建模:减少 JOIN
  • 列存压缩与数据类型选择
  • 向量化执行与 GROUP BY 优化

高频题速览

编号 题目 难度 频率
05.8.2-001 分区裁剪和主键跳数怎么配合 进阶 ★★★★★
05.8.2-002 物化视图/预聚合怎么设计 进阶 ★★★★★
05.8.2-003 为什么 OLAP 推荐宽表少 JOIN 进阶 ★★★★
05.8.2-004 数据类型选择对压缩和性能影响 进阶 ★★★
05.8.2-005 高基数列与 LowCardinality 进阶 ★★★
05.8.2-006 大查询内存溢出怎么办 高级 ★★★

05.8.2-001 分区裁剪和主键跳数怎么配合?

频率 ★★★★★ | 难度 进阶

一句话结论

查询 WHERE 条件命中分区键时跳过整个分区,命中排序键时在分区内跳过 granule(默认 8192 行);两者叠加把扫描范围从全表缩小到极小范围,因此 ORDER BY 最左前缀列要覆盖高频过滤条件。

概念解释

  • 分区裁剪:WHERE event_date >= '2024-01-01' 跳过无关月分区。
  • 主键跳数:稀疏索引按 granule 记录排序键最值,跳过不相关 granule。
  • 最左前缀:ORDER BY (a,b,c),WHERE b=? 不能跳数,WHERE a=? 才高效。

代码 / 示例

-- ORDER BY (event_date, user_id)
SELECT count() FROM events
WHERE event_date = '2024-01-15' AND user_id = 123;
-- 先裁剪到 202401 分区,再在分区内按 event_date+user_id 跳 granule

-- 反例:WHERE user_id=123 不命中排序最左前缀,无法跳数

常见误区

  • 误区:WHERE 条件随便写都能利用索引。正解:排序键最左前缀原则与 B+ 树类似,跳数依赖前缀。
  • 误区:分区键=排序键就一定最优。正解:分区粗(月)、排序细(日期+user),两者分工。

05.8.2-002 物化视图/预聚合怎么设计?

频率 ★★★★★ | 难度 进阶

一句话结论

高频固定查询(如按天按部门统计)用物化视图预聚合:写入时触发增量聚合到另一张表,查询直接查预聚合表,避免每次扫描明细;按业务维度组合建多层物化视图。

代码 / 示例

-- 明细表
CREATE TABLE events (...) ENGINE = MergeTree ...;

-- 物化视图:按天+部门预聚合
CREATE MATERIALIZED VIEW events_daily
ENGINE = SummingMergeTree
ORDER BY (day, dept_id) AS
SELECT toDate(event_date) AS day, dept_id, count() AS cnt, sum(amount) AS amt
FROM events GROUP BY day, dept_id;

-- 查询直接查 events_daily,不用扫 events

常见误区

  • 误区:物化视图能自动匹配任意查询。正解:物化视图按固定聚合逻辑预计算,新查询不命中仍需扫明细。
  • 误区:建一堆物化视图没代价。正解:每个物化视图都在写入时增加计算和存储。

05.8.2-003 为什么 OLAP 推荐宽表少 JOIN?

频率 ★★★★ | 难度 进阶

一句话结论

列存 OLAP 的 JOIN 比扫描贵,大宽表把维度字段冗余到事实表,查询直接扫单表聚合,避免 shuffle 和 hash join;维度表在 ETL 时就冗余进宽表。

概念解释

  • 宽表建模:事实表冗余维度字段。
  • JOIN 成本:分布式 JOIN 需 shuffle 数据,网络和内存开销大。
  • 扫描成本低:列存只读涉及列,扫宽表便宜。

常见误区

  • 误区:OLAP 像 OLTP 一样范式化。正解:OLAP 反范式化,用空间换时间。

05.8.2-004 数据类型选择对压缩和性能有什么影响?

频率 ★★★ | 难度 进阶

一句话结论

小数据类型(UInt32 代替 Int64、Date 代替 DateTime、LowCardinality 枚举字符串)压缩率更高、扫描更快;避免用大 varchar 装短枚举,合理用 LowCardinality 编码字典。

代码 / 示例

-- 高频枚举字符串用 LowCardinality
event_type LowCardinality(String)

-- 时间用 Date 而非 DateTime(只到天)
event_date Date

-- 数值用最小合适类型
user_id UInt64, age UInt8, status UInt8

常见误区

  • 误区:字符串一律用 String。正解:低基数字典字符串压缩比高 10 倍以上。
  • 误区:用 Nullable 无所谓。正解:Nullable 有额外开销,能 NOT NULL 尽量 NOT NULL。

05.8.2-005 LowCardinality 适合什么列?

频率 ★★★ | 难度 进阶

一句话结论

LowCardinality 把字符串按字典编码成整数索引,适合低基数列(城市、状态、类型,取值 < 1 万);高基数列(user_id、URL)不适合,字典本身变成负担。

常见误区

  • 误区:所有字符串都加 LowCardinality。正解:高基数字典大,反而慢。

05.8.2-006 大查询内存溢出(OOM)怎么办?

频率 ★★★ | 难度 高级

一句话结论

OLAP 聚合/排序消耗大量内存,优化手段:加分区过滤减少扫描量、用预聚合代替全量扫描、group by 用临时磁盘(group_by_overflow_mode)、限制单查询内存上限、分布式下推到分片本地聚合。

代码 / 示例

-- ClickHouse 限制内存
SET max_memory_usage = 10000000000;  -- 10GB

-- 用 max_bytes_before_external_group_by 落盘聚合
SET max_bytes_before_external_group_by = 2000000000;

常见误区

  • 误区:OOM 就加内存。正解:先优化查询减少扫描和聚合基数,加内存只是治标。

05.8.2-007 谓词下推在列存里怎么体现?

频率 ★★ | 难度 进阶

一句话结论

列存扫描时先按 WHERE 列的 min/max 跳行组,再按过滤列筛选命中行,最后才读其他大列(延迟物化);避免读不命中行的大列。


05.8.2-008 大表 JOIN 怎么优化?

频率 ★★ | 难度 进阶

一句话结论

小表广播(broadcast join)到大表节点;大表按 JOIN 键 shuffle;避免超大表之间 JOIN,用宽表预聚合。


05.8.2-009 物化视图和 projection 区别?

频率 ★★ | 难度 高级

一句话结论

物化视图是独立表,需显式查询;projection 是表内的额外排序/聚合结构,查询自动匹配,无需改 SQL;CK 22+ 支持 projection。

典型场景与代码示例

物化视图示例

-- 明细表
CREATE TABLE events (
  event_date Date,
  dept_id UInt32,
  amount Float64
) ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, dept_id);

-- 物化视图:按天+部门预聚合
CREATE MATERIALIZED VIEW events_daily
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, dept_id)
AS SELECT
  event_date,
  dept_id,
  count() AS cnt,
  sum(amount) AS total_amount
FROM events
GROUP BY event_date, dept_id;

分区裁剪示例

表:PARTITION BY toYYYYMM(event_date)

查询:WHERE event_date = '2024-01-15'
  -> 只扫 202401 分区,跳过 202402~202412

查询:WHERE dept_id = 10
  -> 不命中分区键,扫全部分区

OOM 治理

问题:GROUP BY 大表聚合内存溢出
方案:
1. 加分区过滤:WHERE event_date >= '2024-01-01'
2. 用预聚合表:查 events_daily 而不是 events
3. 设置落盘:SET max_bytes_before_external_group_by = 2000000000
4. 分布式下推:分片本地聚合,再全局聚合

面试高频追问

问:物化视图和普通视图有什么区别?

答:普通视图是查询别名,不存数据;物化视图预聚合存数据,写入时触发,查询直接查结果。

问:为什么 OLAP 推荐宽表少 JOIN?

答:列存扫描便宜,JOIN 需 shuffle 数据,网络和内存开销大;宽表预聚合避免 JOIN。

问:OOM 为什么先优化查询而不是加内存?

答:加内存是治标;优化查询减少扫描量和聚合基数才是治本。

问:LowCardinality 适合什么列?

答:低基数字符串(枚举、城市、状态),取值少于 1 万;高基数字典大反而慢。

核心要点速记

优化手段 原理
分区裁剪 跳过无关分区
排序键跳数 稀疏索引跳过 granule
物化视图 写入时预聚合
宽表 免 JOIN
LowCardinality 低基数字典编码

OOM 治理:先优化查询减少扫描量,再加内存。

一句话记忆:OLAP 优化 = 减少扫描范围 + 预聚合 + 宽表免 JOIN。

相关概念辨析

分区键 vs 排序键

维度 分区键 排序键(ORDER BY)
作用 数据按分区拆分 分区内按排序键物理排序
查询裁剪 支持分区裁剪 支持主键索引裁剪
唯一性 不唯一 唯一
选择 高基数、按时间 常用过滤和排序字段

物化视图 vs 普通视图

  • 普通视图:SQL 别名,查询时执行,不存储数据。
  • 物化视图:预计算结果,存储数据,查询时直接读。
  • 选择:固定聚合逻辑用物化视图;灵活查询用普通视图。

05.8.2-007 分区键选错了会有什么后果?

频率 ★★★ | 难度 进阶

一句话结论

分区键选错(低基数或不在查询条件中)会导致分区裁剪失效,每次查询扫描全部分区,数据量翻倍以上,且产生大量小 part 合并压力。

概念解释

  • 分区裁剪:查询条件命中分区键时,只扫对应分区。
  • 分区键选错后果:1)裁剪失效,全表扫描;2)单分区数据过大,查询变慢;3)分区过多,小 part 合并压力大。

原理流程

  1. 建表时按 dt 分区,查询 WHERE dt = '2026-01-01' 只扫一个分区。
  2. 如果按 status 分区(只有 3 个值),WHERE dt = '...' 不命中分区,扫全部 3 个分区。
  3. 如果按 user_id 分区(高基数),每天产生百万分区,小文件爆炸。

常见误区

  • 误区:分区键选高基数就好。正解:分区键要和查询条件对齐,高基数反而产生过多分区。

追问延伸

  • 分区键选择原则:高基数 + 查询常用过滤维度 + 时间维度优先。
  • 分区过多症状:SHOW PARTITIONS 看到几十万分区,合并慢。

05.8.2-008 排序键(ORDER BY)选错了会有什么后果?

频率 ★★★ | 难度 进阶

一句话结论

排序键决定数据在分区内的物理存储顺序和主键索引;选错会导致主键索引裁剪失效,查询无法跳过数据块,全分区扫描。

概念解释

  • 排序键 = ORDER BY 列,决定 part 内数据排序和稀疏索引。
  • 前缀索引:查询条件命中排序键前缀时,可用稀疏索引跳过数据块。
  • 选错后果:1)前缀索引不命中,扫描更多数据;2)数据倾斜,某些 part 过大。

原理流程

  1. 建表 ORDER BY (dt, user_id),查询 WHERE dt='...' AND user_id=123 命中前缀索引。
  2. 如果 ORDER BY (user_id, dt),查询 WHERE dt='...' 不命中前缀,扫描全部 user_id 范围。
  3. 稀疏索引按排序键每 8192 行记录一条标记,命中时跳过整个数据块。

常见误区

  • 误区:排序键和分区键一样。正解:分区键是粗粒度裁剪;排序键是细粒度索引裁剪,两者独立。

追问延伸

  • 排序键选择原则:高频过滤列放前面,高基数列放前面。
  • 查看索引命中:EXPLAIN 看 PrimaryKeyPrefix 还是 FullScan。

待整理清单

  • [ ] Doris/StarRocks 物化视图与 ClickHouse 差异
  • [ ] 谓词下推与索引跳过索引(data skipping index)
  • [ ] 大表 JOIN 的 broadcast 与 shuffle 策略