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 合并压力大。
原理流程
- 建表时按 dt 分区,查询 WHERE dt = '2026-01-01' 只扫一个分区。
- 如果按 status 分区(只有 3 个值),WHERE dt = '...' 不命中分区,扫全部 3 个分区。
- 如果按 user_id 分区(高基数),每天产生百万分区,小文件爆炸。
常见误区
- 误区:分区键选高基数就好。正解:分区键要和查询条件对齐,高基数反而产生过多分区。
追问延伸
- 分区键选择原则:高基数 + 查询常用过滤维度 + 时间维度优先。
- 分区过多症状:SHOW PARTITIONS 看到几十万分区,合并慢。
05.8.2-008 排序键(ORDER BY)选错了会有什么后果?
频率 ★★★ | 难度 进阶
一句话结论
排序键决定数据在分区内的物理存储顺序和主键索引;选错会导致主键索引裁剪失效,查询无法跳过数据块,全分区扫描。
概念解释
- 排序键 = ORDER BY 列,决定 part 内数据排序和稀疏索引。
- 前缀索引:查询条件命中排序键前缀时,可用稀疏索引跳过数据块。
- 选错后果:1)前缀索引不命中,扫描更多数据;2)数据倾斜,某些 part 过大。
原理流程
- 建表 ORDER BY (dt, user_id),查询 WHERE dt='...' AND user_id=123 命中前缀索引。
- 如果 ORDER BY (user_id, dt),查询 WHERE dt='...' 不命中前缀,扫描全部 user_id 范围。
- 稀疏索引按排序键每 8192 行记录一条标记,命中时跳过整个数据块。
常见误区
- 误区:排序键和分区键一样。正解:分区键是粗粒度裁剪;排序键是细粒度索引裁剪,两者独立。
追问延伸
- 排序键选择原则:高频过滤列放前面,高基数列放前面。
- 查看索引命中:EXPLAIN 看 PrimaryKeyPrefix 还是 FullScan。
待整理清单
- [ ] Doris/StarRocks 物化视图与 ClickHouse 差异
- [ ] 谓词下推与索引跳过索引(data skipping index)
- [ ] 大表 JOIN 的 broadcast 与 shuffle 策略