OLAP 查询调优

本章目标：能从存储模型、分区、排序键、预聚合和 Join 策略五个方向定位慢查询，并给出可验证的优化方案。 前置知识：ClickHouse、Doris 与 StarRocks 预计用时：100 分钟

同一条 SQL，在 OLAP 引擎里可能 50 毫秒返回，也可能跑 5 分钟。差别通常不在 SQL 写法，而在表怎么建、数据怎么排、查询扫了多少行。

1. 为什么 OLAP 查询会慢

分析查询慢，先看四件事：

1. 扫了多少数据：分区裁剪没生效、SELECT *、没有过滤条件。
2. 数据怎么排：排序键和查询过滤条件不匹配，索引用不上。
3. 算了多少行：高基数 GROUP BY、大表 JOIN 大表、重复计算。
4. 资源够不够：并发高、内存不足、磁盘 IO 打满。

调优的顺序是：先减少扫描量，再减少计算量，最后才加资源。反过来先加机器，往往只是把慢查询变得更贵。

2. 存储模型与索引

2.1 行存与列存

OLAP 引擎基本都是列存。列存的好处是：查 3 列就只读 3 列，压缩率也更高。坏处是更新单行代价大，所以有了各种主键模型和批量写入机制。

2.2 分区、分桶与排序键

• 分区（Partition）：按日期或业务维度把数据切成目录，查询时跳过无关分区。
• 分桶（Bucket）：把数据按哈希分散到不同文件或节点，影响并行度和 JOIN。
• 排序键（ORDER BY / Sort Key）：决定数据在文件内的顺序，配合稀疏索引、前缀索引、Zone Map 跳过数据块。

一个常见组合：

PARTITION BY toYYYYMM(order_date)
ORDER BY (city, order_date, order_id)

查询 WHERE city = '北京' AND order_date >= '2024-01-01' 时，分区先裁掉其他月份，排序键再定位到北京的数据块。如果排序键写成 (order_id, city)，按城市过滤就用不上前缀索引，只能全扫。

2.3 常见数据模型

以 Doris/StarRocks 为例：

• 明细模型（Duplicate Key）：保留所有行，适合日志、明细。
• 聚合模型（Aggregate Key）：相同 key 自动预聚合，适合固定维度的指标。
• 主键模型（Unique Key）：按主键更新，适合订单状态这类需要更新的表。

选错模型会出现“数据对不上”或“更新不生效”。例如用明细模型做订单状态表，同一订单会有多行，查询时必须自己去重。

3. 动手：用 DuckDB 看执行计划

DuckDB 不是分布式 OLAP，但可以用 EXPLAIN ANALYZE 观察扫描、过滤和聚合的关系。

3.1 准备数据

CREATE OR REPLACE VIEW orders AS
SELECT * FROM read_csv_auto('data/sample/orders_demo.csv', header = true);

EXPLAIN ANALYZE
SELECT city, SUM(amount) AS gmv
FROM orders
WHERE status = 'paid'
GROUP BY city
ORDER BY gmv DESC;

执行计划里重点看：

• SCAN 读了多少行。
• FILTER 是否把无效订单提前过滤。
• HASH_GROUP_BY 的输入行数。
• 总耗时和峰值内存。

把过滤条件放到子查询里，通常能减少聚合输入：

EXPLAIN ANALYZE
SELECT city, SUM(amount) AS gmv
FROM (
    SELECT city, amount
    FROM orders
    WHERE status = 'paid'
) t
GROUP BY city
ORDER BY gmv DESC;

3.2 预聚合

如果某个看板每天都问“各城市 GMV”，没必要每次扫描明细表：

CREATE OR REPLACE TABLE ads_city_gmv AS
SELECT
    city,
    SUM(amount) AS gmv,
    COUNT(*) AS order_cnt
FROM orders
WHERE status = 'paid'
GROUP BY city;

SELECT * FROM ads_city_gmv ORDER BY gmv DESC;

预聚合把计算从查询时挪到了写入时。代价是维度组合固定：一旦要看“按城市 + 品类”，这张表就不够用，需要新的聚合表或明细表。

3.3 ClickHouse 建表示例

CREATE TABLE fact_order_item
(
    order_id String,
    order_date Date,
    city LowCardinality(String),
    product_id String,
    category LowCardinality(String),
    item_amount Decimal(12, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(order_date)
ORDER BY (city, order_date, order_id);

LowCardinality 适合城市、品类这类重复值多的字符串，能降低存储和扫描成本。分区不要太细：按天分区一年 365 个分区，小表会产生大量小文件；按月分区通常更稳。

3.4 物化视图

ClickHouse 的物化视图可以在写入时自动聚合：

CREATE MATERIALIZED VIEW mv_city_gmv
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_date, city)
AS
SELECT
    order_date,
    city,
    SUM(item_amount) AS gmv,
    COUNT() AS order_cnt
FROM fact_order_item
GROUP BY order_date, city;

SummingMergeTree 会在后台合并相同 key 的行，查询时再 SUM 一次。注意：物化视图只对创建之后写入的数据生效，历史数据要单独回填。

3.5 Join 策略

OLAP 里常见三种 Join：

• Broadcast Join：小表广播到每个节点，避免大表 shuffle。适合维度表。
• Shuffle Join：两张大表按 key 重分布，网络开销大。
• Colocate Join：两表按相同分桶键分布，直接在本地 Join。建表时要设计好分桶。

如果维表只有几万行，优先广播；如果两张大表频繁 JOIN，考虑 Colocate 或预聚合。

4. 慢查询排查流程

1. 拿到 SQL、执行时间、扫描行数、峰值内存
2. 看分区裁剪是否生效
3. 看排序键/索引是否匹配过滤条件
4. 看 GROUP BY 基数、JOIN 输入行数
5. 看是否有数据倾斜（某个 key 占大头）
6. 决定：改 SQL、改表结构、加预聚合、加资源

每一步都要有对比数据。只说“加了索引变快了”不够，要说清扫描行数从多少降到多少、耗时从多少降到多少。

4.1 数据倾斜

某个城市或商品占了 80% 的数据，所有任务都压在一个节点上。处理方式：

• 给热点 key 加随机后缀，先局部聚合再合并。
• 把热点 key 单独拆出来处理。
• 检查分桶键是否选错，避免高基数或严重不均的字段。

4.2 高基数 GROUP BY

按用户 ID、订单 ID 分组，结果集可能上亿行。先问业务是否真的需要这么细；如果只是看 TopN，先聚合再排序，或者用近似去重函数。

5. 常见坑

5.1 分区过多

按天分区、按小时分区、再按城市分区，分区数量爆炸，元数据和小文件成为瓶颈。分区字段要选查询必带、基数可控的维度。

5.2 排序键顺序写反

ORDER BY (order_id, city) 对按城市过滤几乎没有帮助。前缀索引只对最左列有效，排序列要把最常用的过滤字段放前面。

5.3 SELECT *

列存引擎只读需要的列才快。SELECT * 会把所有列拉出来，宽表上代价很高。查询和导出都要显式列名。

5.4 物化视图建了但没命中

查询的过滤条件、聚合粒度、排序键和物化视图不匹配，优化器就不会用它。建之前先确认典型查询长什么样。

5.5 只看单次耗时

缓存命中、并发高低、数据量变化都会影响耗时。压测要固定数据集和并发，记录 P50/P95，而不是只看一次最快的结果。

6. 在线练习

自测：

• 能写出分区裁剪会生效的过滤条件。
• 能解释排序键前缀为什么影响索引命中。

7. 面试延伸

面试题：OLAP 查询慢，你从哪些方向优化？

是什么：减少扫描量（分区、列裁剪）、减少计算量（预聚合、TopN）、优化 Join、处理倾斜、最后加资源。

解决什么问题：慢查询的原因不同，盲目加资源可能无效。

我的实操边界：本章用 DuckDB 执行计划和 ClickHouse/Doris DDL 演示，未在生产集群做过大规模压测。

面试题：分区和分桶有什么区别？

是什么：分区是目录级裁剪，分桶是数据分布和并行单位。

解决什么问题：分区减少扫描，分桶优化 JOIN 和并行度；两者解决的不是同一个问题。

我的实操边界：本章给出建表形态，未做分桶键的线上调优。

面试题：物化视图和预聚合表怎么选？

是什么：物化视图由引擎自动维护，预聚合表由调度任务写入。

解决什么问题：都能减少查询计算，但物化视图对历史回填和复杂依赖的支持有限。

我的实操边界：了解 ClickHouse 物化视图机制，未在生产做过历史回填。

面试题：数据倾斜怎么处理？

是什么：给热点 key 加盐、拆分热点、改分桶键，或先局部聚合再全局聚合。

解决什么问题：单个节点成为瓶颈，其他节点空闲。

我的实操边界：本章说明思路，未在分布式集群上实测。

8. 本章验收

• 能用 EXPLAIN ANALYZE 看出扫描行数、过滤和聚合的位置。
• 能写出一个按城市和日期过滤会命中前缀索引的排序键。
• 能解释明细、聚合、主键三种模型的适用场景。
• 能说出物化视图只对新数据生效，并说明历史数据怎么补。
• 能列出慢查询排查的至少五个步骤。
• 能给出两种数据倾斜的处理方案。