📚 课程目录第4章交易SQL语句优化基础

4. 交易SQL语句优化基础

交易型SQL优化的第一步不是改SQL，而是先看清数据库“打算怎么执行这条SQL”。Oracle把这一决策结果保存在执行计划中，它决定了访问路径、连接方式、连接顺序以及谓词在什么阶段被应用。理解执行计划，等于拿到了优化器决策的“底稿”。

一、执行计划的获取方式

获取执行计划有几种常用手段，适用场景不同。最轻量的是 EXPLAIN PLAN FOR，它把计划写入 PLAN_TABLE，不真正执行SQL，适合快速预判：

EXPLAIN PLAN FOR
SELECT o.order_id, o.amount, c.cust_name
  FROM orders o, customers c
 WHERE o.cust_id = c.cust_id
   AND o.status = 'PAID'
   AND o.create_time >= DATE '2024-01-01';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

但 EXPLAIN PLAN 不绑定绑定变量，也不反映运行时统计。要拿到真实计划，应使用 DBMS_XPLAN.DISPLAY_CURSOR，它读取游标缓存中的实际执行计划，配合 ALLSTATS LAST 还能看到每步的 A-Rows（预估行数）与 E-Rows（实际行数）：

SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(sql_id => '8k2m1p9xq7v3t',
                            format  => 'ALLSTATS LAST'));

当SQL执行很慢、需要抓取正在运行的执行计划时，可用 V$SQL_PLAN 或 V$SQL_PLAN_STATISTICS_ALL。判断计划好坏的关键，是看预估行数与实际行数是否严重偏离——偏差越大，优化器越可能选错访问路径和连接方式。

二、访问路径的选择

访问路径决定数据从表中“怎么取出来”。常见路径包括：全表扫描（TABLE ACCESS FULL）、索引唯一扫描（INDEX UNIQUE SCAN）、索引范围扫描（INDEX RANGE SCAN）、索引全扫描（INDEX FULL SCAN）以及索引快速全扫描（INDEX FAST FULL SCAN）。

选择逻辑的核心是选择率。若谓词过滤后返回的行占表比例很低（通常低于约5%），索引范围扫描更优；若返回大量行，全表扫描反而更经济，因为索引扫描需要额外的回表（TABLE ACCESS BY INDEX ROWID）开销。例如 status = 'PAID' 若命中90%的行，走索引再回表可能比全表扫描更慢。

另一个关键点是索引前导列。复合索引 (status, create_time) 只有在 status 被等值过滤时，create_time 的范围条件才能有效利用。若SQL只写 create_time >= ... 而跳过 status，该索引基本无法用于范围扫描。

三、连接方式与连接顺序

多表连接时，优化器要在三种主要连接方式中选择：嵌套循环（NESTED LOOPS）、哈希连接（HASH JOIN）和排序合并连接（SORT MERGE JOIN）。

嵌套循环：驱动表返回少量行、被驱动表连接列有索引时最优，适合交易场景中“主表小结果集 + 明细表索引探测”。
哈希连接：两表数据量都较大、等值连接且无合适索引时更优，适合批量对账类查询。
排序合并连接：适用于非等值连接或两表已按连接键有序的场景，交易SQL中相对少见。

连接顺序同样重要。嵌套循环中，返回行数少的表应作为驱动表；哈希连接中，小表通常作为构建表（Build Table），大表作为探测表（Probe Table）。执行计划中缩进层级反映了连接顺序，越靠上的步骤越先执行。

四、谓词传递与谓词推入

谓词传递（Predicate Pushdown / Transitive Predicate）是优化器的重要能力。当 o.cust_id = c.cust_id 且 c.cust_id = 1001 时，优化器会自动推导出 o.cust_id = 1001，从而让 orders 表也能用上 cust_id 上的索引。这一推导在计划中表现为 access("O"."CUST_ID"=1001)。

谓词还分为访问谓词（Access Predicate，用于定位数据）和过滤谓词（Filter Predicate，用于取出后再筛）。访问谓词能减少I/O，过滤谓词不能。优化时应力争把过滤谓词转化为访问谓词，例如通过增加合适索引或改写条件。

查看执行计划时，优先关注三件事：预估行数与实际行数是否偏差过大、大表是否走了低效的全表扫描、连接顺序是否让驱动表返回了过多行。

五、从计划到优化动作

拿到执行计划后，优化动作应围绕“偏差”和“代价”展开。若发现 E-Rows=1 而 A-Rows=50000，说明统计信息过期或绑定变量导致估算失真，应先收集统计信息或考虑 DBMS_STATS 的直方图。若发现大表走了全表扫描但实际只返回几十行，应检查谓词列是否有可用索引，或索引是否因函数、隐式类型转换而失效。

对于连接顺序问题，可通过 LEADING 和 USE_NL 等提示（Hint）验证假设，但提示应作为诊断手段而非长期方案。真正的优化目标是让优化器在准确统计信息下自主选择正确计划。交易SQL尤其要注意绑定变量与执行计划稳定性的平衡，避免因绑定变量窥探导致计划在数据分布变化时突然劣化。

不要仅凭 EXPLAIN PLAN 的输出就断定计划好坏。它不反映绑定变量、不反映实际行数，也不反映运行时自适应游标共享的结果。生产环境诊断应优先使用 DISPLAY_CURSOR + ALLSTATS LAST。

总结来看，交易SQL优化基础可以归纳为一条判断链：先获取真实执行计划，再看访问路径是否匹配选择率，接着看连接方式与连接顺序是否合理，最后检查谓词是否被有效传递和推入。这四步构成了后续所有SQL调优工作的共同起点。