慢 SQL 语句优化学习与改写
慢 SQL 优化学习与改写
一、慢 SQL 的诊断框架
1.1 先区分估算计划与实际执行
EXPLAIN 生成执行计划，但不真正执行目标 SQL；DIsql 的 AUTOTRACE TRACE/TRACEONLY 会实际执行并显示计划及监控信息。
| 证据 | 能回答什么 | 不能单独证明什么 |
|---|---|---|
| EXPLAIN 估算计划 | 优化器准备采用的路径、估算代价和行数 | 真实耗时、真实 I/O、实际返回行数 |
| AUTOTRACE 实际统计 | 实际执行时间、逻辑读、物理读、处理行数等 | 业务结果是否与改写前完全等价 |
| SQL 结果对比 | 两版 SQL 的集合或明细是否一致 | 性能收益能否在并发生产环境保持 |
1.2 快速定位
| 风险信号 | 可能的根因 | 下一步检查 |
|---|---|---|
CSCN2 扫描大量行，最终返回很少 |
缺少合适索引、条件不可索引、选择性估算错误 | 谓词写法、复合索引、统计信息、绑定值分布 |
SSEK2 + BLKUP2 返回大量行 |
索引命中范围太宽，大量随机回表 | 回表次数、查询列、是否扫描反而更便宜 |
| NEST LOOP 外侧实际行数很大 | 内侧被重复访问，驱动表或估算可能不合理 | 外侧行数、内侧单次成本、连接列索引 |
JOIN、HAGR2、SORT3、SPL2 前后行数突增 |
连接重复、聚合粒度错误、去重或相关计算成本高 | 连接唯一性、分组键、相关子查询、DISTINCT 来源 |
从叶子向上看数据怎样流动，再从父节点向下看谁会反复调用谁。 NEST LOOP 的两棵子树不是简单地各执行一次；外侧每多一行，内侧就可能多一次访问。
二、案例：相关标量子查询为什么会慢
2.1 问题 SQL
下面的 SQL 为每条 age < 10 的明细，统计相同 age 的行数：
SELECT
a.age,
a.name,
(
SELECT COUNT(*)
FROM t4 b
WHERE b.age = a.age
AND b.age < 10
) AS cnt
FROM t4 a
WHERE a.age < 10;
问题在于子查询引用了外层 a.age，计算容易随外层行数放大。案例记录中，相关范围约 82,827 行，原 SQL 只加载前 100 行就耗时 143 秒。
2.2 从计划中找到放大点
优化前计划摘录如下：
1 #NSET2: [290, 82827, 64]
2 #PIPE2: [290, 82827, 64]
3 #PRJT2: [11, 82827, 64]
4 #SSEK2: [11, 82827, 64] T4 as A
5 #SPL2: [278, 82827, 80]
6 #PRJT2: [278, 82827, 80]
7 #HAGR2: [278, 82827, 80] keys(A.ROWID)
8 #HASH LEFT JOIN2: [68, 2858463, 80] KEY(A.AGE=B.AGE)
9 #SSEK2: [11, 8282 7, 64] T4 as A
10 #SSEK2: [10, 82827, 16] T4 as B
计划给出三个关键信号：
- 外层 A 和内层 B 都约为 82,827 行，并不是“小外表探测小结果”。
- HASH LEFT JOIN 节点估算约 2,858,463 行，出现明显的中间结果膨胀。
HAGR2按A.ROWID聚合，计算粒度仍接近外层明细行，而业务真正需要的是“每个 AGE 统计一次”。
2.3 解决方案：先聚合，再连接
SELECT
a.age,
a.name,
COALESCE(b.cnt, 0) AS cnt
FROM t4 a
LEFT JOIN (
SELECT
age,
COUNT(*) AS cnt
FROM t4
WHERE age < 10
GROUP BY age
) b
ON b.age = a.age
WHERE a.age < 10;
优化后，内层先把约 8.28 万行收缩为约 120 个 age 分组，再与 A 连接：
1 #NSET2: [34, 82827, 64]
2 #PRJT2: [34, 82827, 64]
3 #HASH RIGHT JOIN2: [34, 82827, 64]
KEY(B.AGE=A.AGE)
4 #PRJT2: [16, 120, 8]
5 #SAGR2: [16, 120, 8] keys(B.AGE)
6 #SSEK2: [10, 82827, 8] T4 as B
7 #SSEK2: [11, 82827, 56] T4 as A
| 对比项 | 优化前 | 优化后 |
|---|---|---|
| 计算方式 | 围绕外层行处理相关聚合 | 按业务键预聚合一次 |
| 聚合粒度 | A.ROWID，接近明细行 |
B.AGE，约 120 组 |
| 主要连接输出估算 | 约 2,858,463 行 | 约 82,827 行 |
| 顶层估算代价 | 290 | 34 |
| 源案例耗时 | 143 秒，仅加载前 100 行 | 4 毫秒，原记录未注明相同取数口径 |
这里必须保留 LEFT JOIN，并对 COUNT 使用 COALESCE(b.cnt, 0)。原标量子查询中的 COUNT(*) 在没有匹配行时返回 0，而左连接未命中时 b.cnt 为 NULL；省略补零会改变通用场景的结果语义。
三、SQL 改写模板一：让过滤条件保持可索引
3.1 不要在索引列上做无必要的函数计算
问题写法：
SELECT order_id, create_time, status
FROM orders
WHERE TO_CHAR(create_time, 'YYYY-MM-DD') = :day_text;
可改为由应用传入边界值：
SELECT order_id, create_time, status
FROM orders
WHERE create_time >= :day_start
AND create_time < :next_day_start;
第一种写法需要对列做函数计算，也让普通的 create_time 索引更难直接提供范围定位；第二种写法把函数处理移到绑定值一侧，形成左闭右开的时间范围，并正确覆盖带时分秒的数据。
同类问题还包括：
-- 问题：列侧类型转换
WHERE TO_CHAR(customer_id) = :customer_id_text
-- 建议：绑定变量使用与列一致的类型
WHERE customer_id = :customer_id_num
-- 问题：用 NVL 包裹过滤列
WHERE NVL(status, 'N') = :status
-- 可能的等价展开，必须按绑定值和 NULL 业务规则分别验证
WHERE status = :status
OR (status IS NULL AND :status = 'N')
NVL 展开并不保证一定更快；当 OR 两个分支都很宽时，扫描可能仍是合理计划。重点是让优化器能看见原始列条件，并结合数据分布与索引选择访问路径。
3.2 SUBSTR 改写必须先证明数据格式不变
导师案例库中存在这类条件：
WHERE SUBSTR(t.business_code, 6) = :business_id;
如果业务规则能证明前五位是固定前缀，而且完整编码与 business_id 一一对应，可改成完整值等值查询：
WHERE t.business_code = :fixed_prefix || :business_id;
并评估 business_code 上的索引。该改写的前提不是“函数慢”，而是固定前缀规则真实、完整且长期稳定。如果前缀不固定、存在补零规则或一对多编码，直接改写会漏数据。案例库中类似改写曾把 9 秒降至约 0.03 秒，但该数字只代表特定数据和索引环境。
四、SQL 改写模板二：一次聚合替代多个相关子查询
业务报表常在同一行中写多个标量子查询：
SELECT
c.customer_id,
(
SELECT COUNT(*)
FROM orders o
WHERE o.customer_id = c.customer_id
) AS order_cnt,
(
SELECT SUM(o.amount)
FROM orders o
WHERE o.customer_id = c.customer_id
) AS total_amount,
(
SELECT COUNT(*)
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.status = 'DONE'
) AS done_cnt
FROM customer c;
如果三个指标使用相同明细范围，可以一次扫描、一次分组：
SELECT
c.customer_id,
COALESCE(x.order_cnt, 0) AS order_cnt,
x.total_amount,
COALESCE(x.done_cnt, 0) AS done_cnt
FROM customer c
LEFT JOIN (
SELECT
o.customer_id,
COUNT(*) AS order_cnt,
SUM(o.amount) AS total_amount,
SUM(CASE WHEN o.status = 'DONE' THEN 1 ELSE 0 END) AS done_cnt
FROM orders o
GROUP BY o.customer_id
) x
ON x.customer_id = c.customer_id;
这类改写通常同时减少扫描次数、相关探测次数和聚合对象数量。但要注意：
COUNT(*)空集返回 0，所以外连接后通常需要补零。SUM(amount)空集返回 NULL；若原 SQL 保留 NULL，就不要擅自补成 0。- 各标量子查询过滤范围不同且无法用条件聚合表达时，不应强行合并。
- 外层只返回极少行且内层连接键有高选择性索引时，少量相关探测可能比全量预聚合更便宜。
五、SQL 改写模板三：NOT IN、NOT EXISTS 与反连接
问题写法：
SELECT a.id, a.name
FROM account a
WHERE a.id NOT IN (
SELECT b.account_id
FROM blacklist b
);
当目标是“黑名单中不存在相同账号”时，可评估：
SELECT a.id, a.name
FROM account a
WHERE NOT EXISTS (
SELECT 1
FROM blacklist b
WHERE b.account_id = a.id
);
案例库中有 NOT IN 反复扫描的案例，改写为 NOT EXISTS 并配合合适访问路径后，执行时间下降很多。但如果 NOT IN 子查询可能返回 NULL，它与 NOT EXISTS 并不等价。
六、SQL 改写模板四：用 EXISTS 表达“只判断存在”
下面的写法先做一对多连接，再用 DISTINCT 消除重复：
SELECT DISTINCT
c.customer_id,
c.customer_name
FROM customer c
JOIN orders o
ON o.customer_id = c.customer_id
WHERE o.status = 'UNPAID';
如果只关心是否存在未支付订单，可以直接表达为半连接语义：
SELECT
c.customer_id,
c.customer_name
FROM customer c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.status = 'UNPAID'
);
不先制造一对多重复行，也不再依赖 DISTINCT 排序或哈希去重。适合配合 orders(customer_id, status) 或按真实过滤模式设计的复合索引。
这一改写仅在 DISTINCT 的目的确实是消除明细连接造成的重复时成立。如果 SELECT 中包含订单明细列，或者 DISTINCT 还在消除表自身投影重复，EXISTS 版本就不是等价替换。
七、SQL 改写模板五：正确处理 OR 条件
7.1 同一列的多个等值条件优先写成 IN
-- 原写法
WHERE status = 'NEW'
OR status = 'WAIT'
OR status = 'RETRY'
-- 更清晰的写法
WHERE status IN ('NEW', 'WAIT', 'RETRY')
这主要改善可读性并便于优化器识别同列范围。
7.2 不同列的 OR 可评估互斥的 UNION ALL
-- 原写法
SELECT task_id, status, owner_id
FROM task
WHERE status = :status
OR owner_id = :owner_id;
当两个分支各自都有高选择性索引，而 OR 导致大范围扫描时，可以评估：
SELECT task_id, status, owner_id
FROM task
WHERE status = :status
UNION ALL
SELECT task_id, status, owner_id
FROM task
WHERE owner_id = :owner_id
AND (status <> :status OR status IS NULL);
第二个分支显式排除了第一个分支已经返回的行，因此保留原 OR 的“每个物理行最多返回一次”语义。不能简单地把 OR 拆成两个 UNION ALL 而不处理交集，否则同时满足两个条件的行会重复；改用 UNION 虽能去重，却可能引入额外排序或哈希成本。
八、SQL 改写模板六：先缩小数据，再连接和聚合
问题写法的典型特征是：先把大表连接起来，最后才过滤或聚合。
SELECT
c.region_id,
COUNT(*) AS order_cnt
FROM customer c
JOIN orders o
ON o.customer_id = c.customer_id
WHERE o.create_time >= :begin_time
AND o.create_time < :end_time
AND o.status = 'DONE'
GROUP BY c.region_id;
如果时间和状态能把订单显著收缩，可以先把订单按连接所需的最小粒度整理出来：
WITH filtered_orders AS (
SELECT customer_id
FROM orders
WHERE create_time >= :begin_time
AND create_time < :end_time
AND status = 'DONE'
)
SELECT
c.region_id,
COUNT(*) AS order_cnt
FROM customer c
JOIN filtered_orders o
ON o.customer_id = c.customer_id
GROUP BY c.region_id;
这段写法更清楚地表达了条件下推意图，但不能仅凭出现 WITH 就断定物化或性能一定提升。优化器可能合并查询块，也可能生成临时结果；最终仍要检查实际计划。
九、分页与 Top-N
慢分页常见两个问题：先对大结果集排序，再丢弃绝大多数行；页码越深，扫描和排序越重。
对于按 (create_time DESC, order_id DESC) 稳定排序的列表，可以评估“基于上一页最后一条记录”的续页条件：
SELECT order_id, create_time, status
FROM orders
WHERE status = :status
AND (
create_time < :last_create_time
OR (create_time = :last_create_time AND order_id < :last_order_id)
)
ORDER BY create_time DESC, order_id DESC;
再配合取前 N 行语法，以及与过滤、排序顺序相匹配的复合索引。这种方式避免每一页都从头跳过大量数据，但只能向前翻页，不适合必须随机跳转到任意页的场景。
必须使用页码分页时，确保排序字段稳定且包含唯一键，避免翻页过程中出现重复或漏行；同时验证 SORT3 的输入行数、内存排序和磁盘排序指标。
十、索引优化
缺失索引、索引列顺序不合理以及高频回表是最常见主题之一。
10.1 复合索引的设计顺序
一般从真实查询模式出发考虑：
- 高频等值过滤列和连接列；
- 选择性与数据倾斜；
- 范围过滤列；
- 是否能兼顾排序或分组；
- 是否值得用额外列减少回表。
例如查询长期固定为：
WHERE tenant_id = :tenant_id AND status = :status AND create_time >= :begin_time AND create_time < :end_time ORDER BY create_time DESC
可以评估以等值条件开头、时间范围随后排列的复合索引，但最终顺序要结合租户大小、状态分布、返回比例和其他核心 SQL 一起测试。
10.2 回表该如何做
当计划出现 SSEK2 → BLKUP2 时，检查：
- 索引定位后实际回表多少次？
- 查询是否读取了大量不必要的列？
- 命中比例很高时，全表或聚集索引扫描是否反而更便宜？
覆盖索引能减少回表，但会增加存储、写放大和维护成本。只为一条低频 SQL 把大量展示列塞入索引，通常得不偿失。
十一、连接算法
| 连接方式 | 通常适合 | 常见风险 |
|---|---|---|
| NEST LOOP / NEST LOOP INDEX | 外侧很小，内侧连接键可高选择性定位 | 外侧估算偏小但实际很大，内侧被调用成千上万次 |
| HASH JOIN | 两侧数据较大、等值连接、一次性批量处理 | 构造侧过大、内存不足、过滤不充分导致中间结果膨胀 |
| MERGE JOIN | 两侧已有合适顺序或排序成本可接受 | 为连接额外排序大量数据 |
判断连接是否合理，应同时看四个量：外侧实际行数、内侧单次访问成本、连接输出行数、最终需要的行数。
作者
文章
阅读量
获赞
联系客服
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1