Oracle SQL性能优化案例报告

AI摘要

本文为数据库性能优化案例分享，对比了Oracle数据库中CTE+ROWNUM与FETCH FIRST两种分页查询的性能差异。通过执行计划分析，指出CTE结构导致全排序是性能瓶颈，而FETCH FIRST语法支持提前停止排序。文章提供了三种优化方案（改写SQL、创建复合索引、修改框架逻辑）及分阶段实施步骤，旨在解决生产环境中的分页查询性能问题。

问题概述

背景

生产环境中发现一个分页查询性能问题。原SQL使用CTE+ROWNUM方式实现分页，即使添加了索引后性能依然不佳。而使用FETCH FIRST语法的SQL性能良好。

影响

查询响应时间从毫秒级变为秒级
用户体验下降
系统资源消耗增加

问题SQL对比

原SQL（性能差）

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
),
PAGINATION AS (
    SELECT USER_SQL.*, rownum as rowNumId 
    FROM USER_SQL
)
SELECT *
FROM PAGINATION
WHERE rownum <= 2;

优化SQL（性能好）

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

执行计划分析

添加索引后的执行计划对比

1. FETCH FIRST版本（性能好）

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 |     6 |   198 |     5   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | IDX_ID_NO |   6 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

关键特征：

使用索引：INDEX RANGE SCAN on IDX_ID_NO
排序方式：WINDOW SORT PUSHED RANK（可提前停止）
访问方式：TABLE ACCESS BY INDEX ROWID BATCHED

2. ROWNUM版本（性能差）

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 |     6 |   198 |     5   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN                  | IDX_ID_NO |   6 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

关键特征：

使用索引：INDEX RANGE SCAN on IDX_ID_NO
排序方式：SORT ORDER BY（必须全排序）
多层视图嵌套（3层VIEW）
外层COUNT STOPKEY

根本原因分析

1. 排序机制差异

特性	ROWNUM版本	FETCH FIRST版本
排序方式	SORT ORDER BY	WINDOW SORT PUSHED RANK
停止机制	先排序后限制	边排序边限制
内存使用	需要所有行排序	找到足够行即停止

2. 执行顺序差异

ROWNUM版本执行流程：

通过索引找到所有匹配行（6行）
回表获取所有数据
在内存中对所有6行进行排序
应用ROWNUM限制取前2行

FETCH FIRST版本执行流程：

通过索引找到匹配行
使用窗口函数边排序边计数
找到第2行后即可停止

3. CTE结构问题

原SQL使用CTE（Common Table Expression）结构：

WITH USER_SQL AS (
    -- 这里包含ORDER BY
    ORDER BY apply_risk_id DESC
)
SELECT * FROM USER_SQL WHERE rownum <= 2;

问题： Oracle必须完全执行CTE中的查询（包括排序），然后才能应用外层的ROWNUM限制。

性能影响分析

数据量放大效应

假设实际生产环境数据：

表总行数：1,000,000
匹配id_no的行数：10,000
需要返回的行数：2

指标	ROWNUM版本	FETCH FIRST版本
索引扫描行数	10,000行	~2行
回表次数	10,000次	~2次
排序数据量	10,000行	边排序边停止
内存使用	高	低
I/O操作	多	少

解决方案

方案1：修改SQL写法（推荐）

-- 优化后的ROWNUM写法
SELECT contact
FROM (
    SELECT contact
    FROM t_apply_risk_extend_params
    WHERE id_no = :id_no
      AND contact IS NOT NULL
    ORDER BY apply_risk_id DESC
)
WHERE ROWNUM <= 2;

-- 或使用FETCH FIRST语法
SELECT contact
FROM t_apply_risk_extend_params
WHERE id_no = :id_no
  AND contact IS NOT NULL
ORDER BY apply_risk_id DESC
FETCH FIRST 2 ROWS ONLY;

方案2：创建复合索引

-- 创建覆盖索引，避免回表
CREATE INDEX idx_apply_risk_optimal ON t_apply_risk_extend_params 
(id_no, apply_risk_id DESC, contact);

-- 或使用INCLUDE子句（Oracle 12c+）
CREATE INDEX idx_apply_risk_include ON t_apply_risk_extend_params 
(id_no, apply_risk_id DESC) 
INCLUDE (contact);

方案3：优化框架生成SQL

修改YII框架的分页查询生成逻辑，避免使用CTE+外层ROWNUM的结构。

实施步骤

阶段一：紧急修复

立即修改问题SQL

-- 将原CTE+ROWNUM改为子查询+ROWNUM
SELECT contact
FROM (
    SELECT contact
    FROM t_apply_risk_extend_params
    WHERE id_no = :id_no
      AND contact IS NOT NULL
    ORDER BY apply_risk_id DESC
)
WHERE ROWNUM <= 2;

验证性能改进

-- 监控修改后的SQL性能
SELECT sql_id, elapsed_time/1000000 as elapsed_sec, executions
FROM v$sql
WHERE sql_text LIKE '%t_apply_risk_extend_params%'
  AND sql_text LIKE '%ROWNUM%';

阶段二：中期优化

创建复合索引

CREATE INDEX idx_apply_risk_perf ON t_apply_risk_extend_params 
(id_no, apply_risk_id DESC, contact)
TABLESPACE users
COMPRESS 1;

收集统计信息

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname => 'SCHEMA_NAME',
        tabname => 'T_APPLY_RISK_EXTEND_PARAMS',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt => 'FOR ALL COLUMNS SIZE AUTO',
        cascade => TRUE,
        degree => 8
    );
END;

阶段三：长期预防

代码审查规范
- 禁止使用CTE+外层ROWNUM的分页写法
- 推荐使用FETCH FIRST或子查询+ROWNUM
- 所有分页查询必须包含ORDER BY的复合索引

监控体系建立

-- 创建性能监控视图
CREATE VIEW vw_slow_pagination AS
SELECT sql_id, sql_text, executions, 
       elapsed_time/executions/1000 as avg_ms,
       buffer_gets/executions as avg_gets
FROM v$sql
WHERE (UPPER(sql_text) LIKE '%WITH%ROWNUM%' 
       OR UPPER(sql_text) LIKE '%CTE%ROWNUM%')
  AND elapsed_time/executions/1000 > 100; -- 超过100ms

测试验证

测试用例

-- 测试1：性能对比测试
SET TIMING ON

-- 原SQL（CTE+ROWNUM）
WITH USER_SQL AS (...)
SELECT * FROM PAGINATION WHERE rownum <= 2;

-- 优化SQL（子查询+ROWNUM）
SELECT contact FROM (...) WHERE ROWNUM <= 2;

-- 优化SQL（FETCH FIRST）
SELECT contact FROM ... FETCH FIRST 2 ROWS ONLY;

SET TIMING OFF

预期结果

SQL类型	执行时间	逻辑读	物理读
CTE+ROWNUM	慢	高	高
子查询+ROWNUM	快	低	低
FETCH FIRST	最快	最低	最低

经验教训

1. SQL写法的重要性

避免：CTE内排序 + 外层ROWNUM限制
推荐：子查询内排序 + ROWNUM限制或 FETCH FIRST
原则：尽早应用行数限制，避免不必要的排序

2. 索引设计原则

分页查询需要WHERE条件列 + ORDER BY列的复合索引
考虑索引的排序方向（DESC）
使用INCLUDE子句避免回表

3. 框架使用注意事项

了解框架生成的SQL结构
对框架生成的关键SQL进行性能测试
必要时自定义SQL改写框架行为

4. 性能测试方法

使用真实数据量测试
对比不同写法的执行计划
监控实际生产环境性能

附录：诊断工具

1. 执行计划查看

-- 基本执行计划
EXPLAIN PLAN FOR [SQL];
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 真实执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

2. 性能监控

-- 查看SQL性能
SELECT sql_id, sql_text, executions,
       elapsed_time/1000000 as elapsed_sec,
       buffer_gets, disk_reads,
       rows_processed
FROM v$sql
WHERE sql_text LIKE '%your_table%';

-- 查看等待事件
SELECT event, total_waits, time_waited
FROM v$system_event
WHERE event LIKE '%read%' OR event LIKE '%write%';

3. 索引分析

-- 查看索引使用情况
SELECT index_name, table_name, 
       leaf_blocks, distinct_keys,
       clustering_factor
FROM user_indexes
WHERE table_name = 'YOUR_TABLE';

-- 查看索引列
SELECT index_name, column_name, 
       column_position, descend
FROM user_ind_columns
WHERE table_name = 'YOUR_TABLE';

报告总结： 本案例展示了SQL写法对性能的重大影响。即使有合适的索引，不当的SQL结构（如CTE内排序+外层ROWNUM）也会导致性能问题。解决方案包括修改SQL写法、创建合适的复合索引，并建立长期的代码审查和监控机制。

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

每天一点小知识，到那都是大佬，哈哈

raybon

Laravel 8.x 译者 408 声望

php @ 远程

生活越细越好

0 人点赞

讨论数量: 0

(=￣ω￣=)··· 暂无内容！

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容，与人为善，比聪明更重要！

帮助