引言
一条跑 5 分钟的查询和一条跑 5 秒的查询，往往不是"机器差多少"，而是执行计划差了量级。SQL 优化不是玄学：每个数据库都会给你一张"执行计划"——它把 SQL 翻译成具体的执行步骤，步骤的选择直接决定性能。会读执行计划的人，能准确说出"慢在哪、怎么改"。
SQL 优化的本质是"帮优化器做更好的决定"：给足信息（统计、索引、分区），减少废话（全表扫、大 Join、重复聚合）。
本文从执行计划读法讲起，覆盖索引、分区、Join、聚合与倾斜五大优化面，最后给出一套"慢查询定位→优化→验证"的工程方法。示例以 PostgreSQL/Doris/ClickHouse 为主（SQL 语义通用）。
一、读懂执行计划
1.1 EXPLAIN 与 EXPLAIN ANALYZE
-- PostgreSQL
EXPLAIN ANALYZE
SELECT region, SUM(amount)
FROM orders
WHERE created_at >= '2026-09-01'
GROUP BY region;
执行计划从下往上读，每一行是一个算子：
Aggregate (actual time=120.5..121.0 rows=8 loops=1) -- 聚合
-> Seq Scan on orders (actual time=0.1..95.2 rows=1.2M ...)
Filter: (created_at >= '2026-09-01')
Rows Removed by Filter: 800000 -- 扫了全表
读计划三件事：
- rows：算子实际处理的行数，判断预估是否离谱（统计信息过时 → 计划差）。
- time：耗时分布，找"大头算子"。
- 操作类型：Seq Scan（全表扫）/ Index Scan / Hash Join / Nested Loop——操作类型决定量级。
1.2 代价模型与统计信息
优化器靠表统计（行数、分布、直方图）做选择。统计信息过旧，优化器就会"误判"：
# 统计信息缺失/过旧的症状
# 明明有索引却走 Seq Scan（误判全表更便宜）
# Join 顺序离谱（把小表当驱动表）
# 修法: ANALYZE / 手动更新统计
# 数仓场景: 大表定期 ANALYZE，分区表按分区更新统计
二、索引优化
2.1 索引不是越多越好
| 索引类型 | 加速场景 | 代价 |
|---|---|---|
| B+ 树索引 | 等值 + 范围 + 排序 | 写放大、占用空间 |
| 覆盖索引 | 查询列全在索引内（避免回表） | 索引变宽 |
| 联合索引 | 多列组合过滤/排序 | 列顺序敏感 |
| 位图索引 | 低基数列（数仓常用） | 写不友好 |
2.2 索引选择铁律
# 1) 高频过滤列（WHERE）优先建索引
# 2) 联合索引: 最左前缀 + 选择性高列放前
# (region, created_at) 支持 region=? AND created_at BETWEEN...
# 3) 覆盖索引: SELECT 列全部纳入索引 → 免回表
# 4) 排序/分组列: ORDER BY / GROUP BY 列入索引，避免排序
# 5) 低选择性列（is_active 0/1）别单独建索引，走分区/位图
# 6) 写多读少的表少建索引（写放大）
2.3 数仓与 OLTP 的差异
- OLTP（行存 + 索引）：小范围点查，索引是主角。
- OLAP（列存）：查询几乎全扫，索引让位给分区裁剪与列裁剪——列存本身"每列一个索引"。
三、分区与分桶
3.1 分区裁剪（Partition Pruning）
分区表让查询只扫相关分区，是数仓第一提速手段：
-- 按日期分区
CREATE TABLE events (
...
) PARTITION BY RANGE (dt);
SELECT * FROM events WHERE dt = '2026-09-27';
-- 只扫描 2026-09-27 分区，其他分区直接跳过
# 分区裁剪生效条件
# 1) WHERE 里对分区键用等值/范围过滤（不能是函数包住: dt+1=...）
# 2) 分区键在计划里出现 "Partition Pruned: 90%" 即成功
# 3) 别对分区键做隐式类型转换（varchar vs date 会失效）
3.2 分桶与聚簇
在分区内按业务键分桶（Hash/Key），让 Join/聚合局部化：
# 分桶 Join: 两表按同键分桶，桶对桶 Join，避免全网 shuffle
# 分桶聚合: 聚合先在桶内局部完成，再合并
# 聚簇表（数据按某列物理有序）: 范围查询连续 IO
3.3 分区粒度的取舍
# 粒度太细（按小时）: 分区数量爆炸，元数据开销大
# 粒度太粗（按年）: 裁剪失效，扫描大
# 经验: 按查询最小范围选，多数场景按日
四、谓词下推与投影下推
4.1 谓词下推（Predicate Pushdown）
把 WHERE 过滤"尽量往数据源头推"，让数据在读取/Join 前就变少：
# 优化器自动做的
# Join 前先各自过滤 → 少 Join 少扫描
# 分区裁剪本质也是谓词下推（推到存储层）
# 对数仓外部表/视图
# 让过滤条件"穿透"到源表（如 Doris 推给 Hive）
4.2 投影下推（Projection Pushdown）
只取需要的列，别 SELECT *：
# 列存场景
# SELECT * → 读出所有列（列存 IO 浪费）
# 只选需要的列 → 列裁剪，IO 大减
# 警惕: 隐式全列（SELECT a, b 但后面 join 带整表）
4.3 下推失效的信号
- 过滤在子查询外层（
WHERE作用在 join 结果上）。 - 对列套函数（
WHERE UPPER(name)='X'）——索引与裁剪失效。 - 视图/CTE 中过滤被"物化"后再过滤（未下推）。
五、Join 策略与数据倾斜
5.1 三大 Join 策略
| 策略 | 原理 | 适用 | 陷阱 |
|---|---|---|---|
| Nested Loop | 逐行匹配 | 小表驱动小结果 | 大表笛卡尔爆炸 |
| Hash Join | 建哈希表匹配 | 大表等值 Join | 内存不足时溢出 |
| Merge Join | 排序后归并 | 两侧已有序 | 排序开销 |
# 驱动表（小表）选择
# 优化器按统计选，但你能干预:
# 1) 先过滤缩小表再 Join（子查询/CTE）
# 2) 大表 Join 前确保过滤/裁剪生效
# 3) 数仓里 Hash Join 主导，驱动表越小越好
5.2 数据倾斜（Skew）
某些 Join/聚合键值过于集中（如"默认分组"、头部用户），导致单个节点扛全部：
# 倾斜症状
# 计划里某算子处理行数远超其他（rows 图有"长尾巴"）
# 实际: 大部分任务秒级完成，个别任务卡死
# 解法
# 1) 倾斜键加盐: key || '_' || random(n) 摊平后再聚合
# 2) 过滤大热点: 大热点单独算，再 union 回来
# 3) 广播小表: 小表广播到各节点，免 shuffle
# 4) 参数: 倾斜检测 + 自动加盐（数仓内置）
5.3 Join 的工程检查清单
# [ ] 等值 Join（Hash 友好），避免不等式 Join
# [ ] 先过滤再 Join（缩小输入）
# [ ] 小表放 Join 右侧/驱动位
# [ ] 数据量级差大的 Join 用广播
# [ ] 确认没有因谓词无法下推导致的笛卡尔积
六、聚合与窗口函数优化
6.1 聚合下推与预聚合
# 1) 聚合下推: 子查询内先聚合再 Join（减少 Join 输入）
# 2) 预聚合/物化: 高频聚合结果物化（物化视图/明细表）
# 3) 两阶段聚合: 数仓内部先局部聚合再全局（自动，但注意倾斜）
# 4) 去重优化: COUNT(DISTINCT) 极耗资源，多维度可改 approx 估算
6.2 窗口函数重排
窗口函数（ROW_NUMBER/PARTITION BY）常常是计划中的"大头"，优化点：
# 1) 分区列尽量与物理排序一致（减少 sort）
# 2) ROW_NUMBER() 取 top-N 场景: 用 limit within group 或先过滤
# 3) 避免窗口套窗口（先算一层，再套一层）
# 4) 大排序窗口: 确认排序列有索引/分区顺序支撑
6.3 去重的代价认识
# COUNT(DISTINCT user_id) 在大基数上极贵（要全局去重）
# 替代:
# 近实时看板 → HyperLogLog 近似（误差 ~1-2%）
# 精确但要快 → 预聚合去重明细
# 业务可接受近似 → 优先近似，别追求完美
七、慢查询定位与工程方法
7.1 慢查询发现
# 1) 数据库慢查询日志/网关记录: 按耗时排序找"头号杀手"
# 2) 监控: 每查询耗时、扫描行数、返回行数埋点
# 3) 大盘: 高频慢查询 TOP 榜（按耗时×频次=总成本排序）
# 4) 资源监控: CPU/IO/内存与慢查询关联
7.2 优化工作流
# 定位慢 SQL → EXPLAIN ANALYZE → 找大头算子
# → 判定原因（全表扫/坏 Join/倾斜/统计旧）
# → 对症修改（索引/分区/重写/加盐）
# → EXPLAIN 对比验证（rows/time 下降）
# → 回归: 确认结果一致（语义不变）
7.3 查询重写的常见模式
| 问题 | 重写思路 | 收益 |
|---|---|---|
| 大表自 Join 算同期对比 | 窗口函数替代 | 省一次大 Join |
| 多层嵌套子查询 | CTE + 先过滤再关联 | 减小中间集 |
| 三表大 Join 无过滤 | 先各自过滤/裁剪 | 输入骤减 |
| 逐行关联查字典 | 预 Join 字典或广播 | 免 Nested Loop |
| 深分页 ORDER BY … OFFSET 大 | 游标 / 键集分页 | 免重复排序 |
八、数仓 SQL 优化实践清单
8.1 分层优化优先级
# 第一优先（收益最大）: 分区裁剪 + 谓词下推（少扫 90%）
# 第二优先: 投影下推（少读列）+ 缩小 Join 输入
# 第三优先: 索引/排序对齐（免排序）
# 最后: 算子级微调（倾斜、资源参数）
# 教训: 先解决"扫了多少"再抠"每行多快"
8.2 性能基线与回归
- 为关键查询建立性能基线（耗时 + 扫描量），每次模型变更后跑回归。
- 数据量增长时主动复查：今天快的查询，3 个月后数据翻倍可能就慢了。
- 用查询历史复盘：哪些查询总在 TOP 榜？是口径问题还是性能问题？
8.3 常见误区
- 迷信索引：数仓 OLAP 索引经常不如分区裁剪有效，先确认裁剪再建索引。
- 忽视统计：统计信息过期，优化器做的任何"聪明决定"都可能跑偏。
- 只看总耗时：要分"扫描时间 vs 计算时间 vs 网络时间"，别把锅全甩给 SQL。
- 重写不改语义：任何"优化重写"必须回归验证结果一致，聚合去重最容易悄悄变口径。
总结
| 优化面 | 核心手段 | 量级收益 |
|---|---|---|
| 执行计划 | EXPLAIN ANALYZE 定位 | 精准对症 |
| 索引 | 覆盖/联合索引 | 点查量级提升 |
| 分区 | 裁剪 + 分桶 | 少扫 90% |
| 下推 | 谓词 + 投影下推 | 输入骤减 |
| Join | 驱动表 + 广播 + 加盐 | 消除量级爆炸 |
| 聚合 | 预聚合 + 两阶段 | 大聚合提速 |
| 工程 | 基线 + 回归 + 监控 | 可持续 |
SQL 优化是"读计划、找大头、对症改"的工程方法，不是背技巧。核心原则：先让数据库少干活（裁剪/下推/索引），再让活干得稳（倾斜/排序/统计）。 每次优化都要用执行计划验证、用回归守护语义——快不是目标，又对又快才是。
参考与延伸阅读
- PostgreSQL 官方文档：EXPLAIN 与查询优化器
- Doris/StarRocks 官方文档：分区裁剪、分桶与 Join 策略
- ClickHouse 官方文档：列存优化与聚合下推
- 高性能 MySQL / The Art of SQL —— 查询优化的经典参考
- ClickHouse 分析引擎 — 列存与向量化执行
- Doris 与 StarRocks — 分布式数仓优化器
- 数据仓库建模 — 建模影响查询性能
继续阅读
探索更多技术文章
浏览归档，发现更多关于系统设计、工具链和工程实践的内容。