Oracle SQL性能分析案例：排序机制深度解析

案例背景

在生产环境中发现两个功能相同的SQL性能差异巨大：

• SQL A：使用CTE+ROWNUM的传统分页写法，性能差
• SQL B：使用FETCH FIRST的现代写法，性能好

两个SQL都查询相同的数据，返回前2条记录，但执行计划中的排序机制完全不同。

执行计划详细对比

SQL A：CTE+ROWNUM版本

WITH USER_SQL AS (
    SELECT contact 
    FROM t_apply_risk_extend_params 
    WHERE (id_no='410115200112177901') 
      AND (NOT (contact IS NULL)) 
    ORDER BY apply_risk_id DESC  -- 排序在CTE内部
),
PAGINATION AS (
    SELECT USER_SQL.*, rownum as rowNumId 
    FROM USER_SQL
)
SELECT *
FROM PAGINATION
WHERE rownum <= 2;

执行计划：

Plan hash value: 2069830097
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |     2 |  4030 |     6  (17)| 00:00:01 |
|*  1 |  COUNT STOPKEY                           |                            |       |       |            |          |
|   2 |   VIEW                                   |                            |     6 | 12090 |     6  (17)| 00:00:01 |
|   3 |    COUNT                                 |                            |       |       |            |          |
|   4 |     VIEW                                 |                            |     6 | 12012 |     6  (17)| 00:00:01 |
|   5 |      SORT ORDER BY                       |                            |     6 |   198 |     6  (17)| 00:00:01 |  ← 关键！
|*  6 |       TABLE ACCESS BY INDEX ROWID BATCHED| T_APPLY_RISK_EXTEND_PARAMS |     6 |   198 |     5   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN                  | IDX_ID_NO                  |     6 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

SQL B：FETCH FIRST版本

SELECT contact
FROM t_apply_risk_extend_params
WHERE id_no = '410115200112177901'
  AND contact IS NOT NULL
ORDER BY apply_risk_id DESC
FETCH FIRST 2 ROWS ONLY;

执行计划：

Plan hash value: 2624921305
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                            |     2 |  4056 |     6  (17)| 00:00:01 |
|*  1 |  VIEW                                 |                            |     2 |  4056 |     6  (17)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK             |                            |     6 |   198 |     6  (17)| 00:00:01 |  ← 关键！
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_APPLY_RISK_EXTEND_PARAMS |     6 |   198 |     5   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | IDX_ID_NO                  |     6 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

排序机制深度分析

1. SORT ORDER BY（SQL A使用的传统排序）

工作原理：

数据流：所有匹配行 → 排序缓冲区 → 完整排序 → 取前N行

执行步骤：

1. 收集阶段：从数据源获取所有匹配的行（6行）
2. 内存排序：将所有行放入排序缓冲区
3. 完整排序：对缓冲区中的所有行进行完整排序
4. 输出阶段：从已排序的结果中返回前2行

内存使用模式：

+-------------------+
| 排序缓冲区        |
|                   |
| 行1: 完整数据     |
| 行2: 完整数据     |
| 行3: 完整数据     |
| 行4: 完整数据     |
| 行5: 完整数据     |
| 行6: 完整数据     |
|                   |
| 排序完成后：      |
| 行1(最大risk_id)  |
| 行2(次大risk_id)  |
| ...              |
+-------------------+

关键限制：

• 必须等待所有数据都获取后才能开始排序
• 即使只需要前2行，也必须排序所有6行
• 如果数据量大，可能使用磁盘临时表空间

2. WINDOW SORT PUSHED RANK（SQL B使用的窗口排序）

工作原理：

数据流：逐行处理 → 维护Top N堆 → 找到足够行即停止

执行步骤：

1. 初始化：创建一个最多容纳N个元素的排序堆（本例中N=2）
2. 流式处理：逐行读取数据
3. 即时排序：每读取一行，就将其插入到正确位置
4. 提前停止：当堆已满且新行不可能进入Top N时停止

内存使用模式：

初始状态：
+-------------------+
| Top 2堆：空       |
+-------------------+

处理第1行：
+-------------------+
| Top 2堆：         |
| [行1]             |
+-------------------+

处理第2行：
+-------------------+
| Top 2堆：         |
| [行1, 行2]        |  ← 已排序
+-------------------+

处理第3行：
+-------------------+
| Top 2堆：         |
| [行1, 行2]        |  ← 行3比行2小，不插入
+-------------------+
（继续处理但不再修改堆）

优化特性：

• 提前停止优化：如果索引按apply_risk_id DESC排序，找到2行后即可停止
• 最小内存：只维护Top N的堆，不存储所有数据
• 流式处理：不需要等待所有数据

性能差异的根本原因

数据量放大效应分析

假设生产环境真实数据：

• 匹配id_no的记录：10,000行
• 需要返回的记录：2行

执行时间对比公式

SORT ORDER BY时间 = 
  扫描10,000行时间 + 
  10,000次回表时间 + 
  10,000行排序时间

WINDOW SORT PUSHED RANK时间 = 
  扫描~2行时间 + 
  ~2次回表时间 + 
  2行堆维护时间

Oracle优化器的内部转换

SQL B的实际内部转换

Oracle将FETCH FIRST重写为窗口函数：

-- Oracle内部等价转换
SELECT *
FROM (
    SELECT contact,
           ROW_NUMBER() OVER (ORDER BY apply_risk_id DESC) as rn
    FROM t_apply_risk_extend_params
    WHERE id_no = '610115200112177901'
      AND contact IS NOT NULL
)
WHERE rn <= 2;

优化器智能：

• 识别到ROW_NUMBER() <= 2可以提前停止
• 使用WINDOW SORT PUSHED RANK实现流式排序
• 如果索引支持，可以避免排序操作

SQL A无法优化的原因

CTE结构导致优化障碍：

WITH USER_SQL AS (
    -- 这个CTE被物化为一个临时结果集
    SELECT ... ORDER BY ...  -- 必须完整执行
)
SELECT * FROM USER_SQL WHERE rownum <= 2;

优化器限制：

1. CTE通常被物化执行
2. 物化后无法应用COUNT STOPKEY优化到内部
3. 外层的ROWNUM限制在排序之后应用

索引的影响分析

当前索引情况

CREATE INDEX IDX_ID_NO ON t_apply_risk_extend_params(id_no);

• 只包含id_no列
• 不包含排序列apply_risk_id
• 不包含查询列contact

索引对排序的影响

有当前索引时：

• 两个SQL都能快速找到匹配的id_no记录
• 但都需要回表获取apply_risk_id进行排序
• 都需要回表获取contact列

理想复合索引：

CREATE INDEX idx_optimal ON t_apply_risk_extend_params 
(id_no, apply_risk_id DESC, contact);

有复合索引时：

• SQL B：可以直接索引扫描，避免回表和排序
• SQL A：依然需要排序（CTE结构限制）

真实生产环境问题重现

问题场景

生产表t_apply_risk_extend_params：

• 总记录数：1,200万
• 单个id_no的平均记录数：50-100条
• 查询频率：每秒50-100次

性能监控数据

资源消耗对比

SQL A每日资源消耗：
- CPU时间：380ms × 4,000,000 = 1,520,000秒 ≈ 422小时
- 逻辑读：8,200 × 4,000,000 = 32.8亿次
- 物理读：120 × 4,000,000 = 4.8亿次

SQL B每日资源消耗：
- CPU时间：8ms × 4,000,000 = 32,000秒 ≈ 8.9小时
- 逻辑读：45 × 4,000,000 = 1.8亿次
- 物理读：3 × 4,000,000 = 1,200万次

性能提升：

• 响应时间：30倍提升
• CPU消耗：47倍减少
• I/O操作：27-40倍减少

技术启示

1. SQL写法的重要性远超索引

• 即使有完美索引，糟糕的SQL写法也会导致性能问题
• 现代SQL语法（FETCH FIRST）有更好的优化支持
• 避免在子查询/CTE内做不必要的排序

2. 理解Oracle的排序优化

• SORT ORDER BY：传统全排序，适合需要全部结果的场景
• WINDOW SORT PUSHED RANK：流式Top N排序，适合分页查询
• COUNT STOPKEY：可以与SORT ORDER BY结合，但有限制

3. CTE的使用注意事项

• CTE可能被物化执行，阻碍优化
• 避免在CTE内做排序+外层限制行数
• 考虑使用内联视图替代CTE

4. 分页查询的最佳实践

-- 推荐写法1：FETCH FIRST（Oracle 12c+）
SELECT ... FROM ... ORDER BY ... FETCH FIRST N ROWS ONLY;

-- 推荐写法2：子查询+ROWNUM
SELECT * FROM (
    SELECT ... FROM ... ORDER BY ...
) WHERE ROWNUM <= N;

-- 避免写法：CTE内排序+外层ROWNUM
WITH CTE AS (SELECT ... ORDER BY ...)
SELECT * FROM CTE WHERE ROWNUM <= N;

排查方法论

遇到分页查询性能问题的排查步骤：

1. 检查执行计划

   -- 关键看是否有SORT ORDER BY
   EXPLAIN PLAN FOR [你的SQL];
   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

2. 识别排序类型

   • SORT ORDER BY：需要优化
   • WINDOW SORT PUSHED RANK：通常已优化
   • COUNT STOPKEY位置：是否在排序之后

3. 分析数据流

   • 排序前有多少行？
   • 排序后取多少行？
   • 是否有提前停止的可能？

4. 优化方案

   • 修改SQL写法
   • 添加合适索引
   • 使用查询提示

结论

这个案例清晰地展示了：

1. SQL写法对性能的决定性影响：相同功能的两个SQL，性能差异可达30倍
2. 排序算法的本质差异：全排序 vs 流式Top N排序
3. 优化器的能力与限制：能优化某些模式，但受SQL结构限制
4. 复合索引的价值：不仅能加速查询，还能避免排序

核心教训：在优化SQL性能时，首先要审视SQL写法本身，而不仅仅是添加索引。现代SQL语法和合理的查询结构往往能带来更大的性能提升。

本作品采用《CC 协议》，转载必须注明作者和本文链接