性能调优：分页SQL语句的优化

我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。

此案例来自朋友分享，是一个非常经典的分页SQL语句和视图展开的案例。
分页查询是业务系统中最常见的SQL场景之一，但如果SQL写得不合理，随着数据量的增加，性能问题会非常突出。本文通过一个真实案例，带你一步步分析和优化Oracle分页SQL，帮助你掌握通用的分页优化套路。

1. 问题SQL与执行计划

业务场景：页面查询停车场进出记录，按时间分页展示。原始SQL如下：

 *
FROM
 (
  SELECT
   page.*, ROWNUM AS rn
  FROM
   (
    SELECT
     A .PARKIN_INFO_ID AS parkininfoid,
     A .CAR_ID AS carid,
     A .PLACE_ID AS placeid,
     P .PLACE_NAME AS placename,
     A .PARKIN_CAR_NO AS parkincarno,
     A .PARKIN_CARD_NO AS parkincardno,
     A .PARKIN_CAR_TYPE AS parkincartype,
     A .PARKIN_ENTRY_NO AS parkinentryno,
     A .PARKIN_TIME AS parkintime,
     A .PARKIN_PIC AS parkinpic,
     b.PARKOUT_EXIT_NO AS parkoutexitno,
     b.PARKOUT_TIME AS parkouttime,
     b.PARKOUT_PIC AS parkoutpic,
     A .PARKIN_ENTRY_NO AS poindoorno,
     b.PARKOUT_EXIT_NO AS pooutdoorno,
     A .createtime AS createtime,
     A .createby AS createby,
     A .updatetime AS updatetime,
     A .updateby AS updateby
    FROM
     p_park_in A
    LEFT JOIN p_park_out b ON A .PARKIN_INFO_ID = b.PARKOUT_IN_ID
    LEFT JOIN p_place P ON A .PLACE_ID = P .place_id
    LEFT JOIN p_place_door d_in ON (
     A .PLACE_ID = d_in.PD_PLACE_ID
     AND A .PARKIN_ENTRY_NO = d_in.pd_no
     AND d_in.PD_TYPE = 0
    )
    LEFT JOIN p_place_door d_out ON (
     A .PLACE_ID = d_out.PD_PLACE_ID
     AND b.PARKOUT_EXIT_NO = d_out.pd_no
     AND d_out.PD_TYPE = 1
    )
    WHERE
     1 = 1
    AND A .PARKIN_TIME >= '1682672708000'
    AND A .PARKIN_TIME <= '1682759108000'
    AND P .PLACE_STATUS = '1'
    ORDER BY
     PARKIN_TIME DESC,
     PARKOUT_TIME DESC,
     createtime DESC
   ) page
  WHERE
   ROWNUM <= 10
 )
WHERE
 rn >= 1

执行计划和性能统计：

| infromation  from v$sqlstats             |
+------------------------------------------------------------------------+
 
 
        CPU(MS)  ELA(MS)     DISK           GET      ROWS        ROWS APPLI(MS) CONCUR(MS) CLUSTER(MS) USER_IO(MS)     PLSQL       JAVA
EXEC       PRE EXEC PRE EXEC PRE EXEC      PRE EXEC    PRE EXEC PRE FETCH  PER EXEC   PER EXEC     PER EXEC    PER EXEC PER EXEC PER EXEC SQL_PROFILE
---------- -------- -------- -------- ------------ ----------- --------- --------- ---------- ----------- ----------- -------- -------- ---------------
9          3,073    5,405        0       937,358        10           5     0        0        2,573        0         0          0
 
 
+------------------------------------------------------------------------+
| information from v$sql         |
+------------------------------------------------------------------------+
 
 
            PLAN  CHI USER      CPU(MS)  ELA(MS)     DISK         GET    ROWS      ROWS APPLI(MS) CONCUR(MS) CLUSTER(MS) USER_IO(MS) FIRST_LOAD_TIME
EXEC          HASH VALUE  NUM NAME     PRE EXEC PRE EXEC PRE EXEC    PRE EXEC    PRE EXEC PRE FETCH    PER EXEC   PER EXEC    PER EXEC    PER EXEC LAST_LOAD_TIME
---------- ------------- ---- ---------- -------- -------- -------- ------------ ----------- --------- --------- ---------- ----------- ----------- ----------------------
9           981367741    0 LZPARKING     3,073    5,405      0     937,358      10         5           0      0      2,573       0 04-30/19:4.04-30/19:4
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time       |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |       |       |       |   210K(100)|       |
|*  1 |  VIEW                     |               |    10 |  6340 |       |   210K  (1)| 00:42:04 |
|*  2 |   COUNT STOPKEY              |               |       |       |       |        |       |
|   3 |    VIEW                  |               | 25578 |    15M|       |   210K  (1)| 00:42:04 |
|*  4 |     SORT ORDER BY STOPKEY         |               | 25578 |    15M|    16M|   210K  (1)| 00:42:04 |
|*  5 |      HASH JOIN RIGHT OUTER         |               | 25578 |    15M|       |   206K  (1)| 00:41:24 |
|*  6 |       INDEX FAST FULL SCAN         | IDX_PD_TYPE_ID       |   810 | 30780 |       |    10   (0)| 00:00:01 |
|   7 |       VIEW                 |               | 25578 |    14M|       |   206K  (1)| 00:41:24 |
|   8 |        NESTED LOOPS OUTER         |               | 25578 |  9042K|       |   206K  (1)| 00:41:24 |
|*  9 |     HASH JOIN RIGHT OUTER         |               | 25268 |  7032K|       |   111K  (1)| 00:22:15 |
|* 10 |      INDEX FAST FULL SCAN         | IDX_PD_TYPE_ID       |   816 | 31008 |       |    10   (0)| 00:00:01 |
|* 11 |      HASH JOIN             |               | 25268 |  6094K|       |   111K  (1)| 00:22:15 |
|  12 |       TABLE ACCESS BY INDEX ROWID| P_PLACE           |  1683 | 99297 |       |   786   (0)| 00:00:10 |
|* 13 |        INDEX RANGE SCAN         | IDX_P_PLACE       |  1683 |       |       |     9   (0)| 00:00:01 |
|  14 |       TABLE ACCESS BY INDEX ROWID| P_PARK_IN       | 34646 |  6360K|       |   110K  (1)| 00:22:06 |
|* 15 |        INDEX RANGE SCAN         | IDX_PPI_PARKIN_TIME |   173K|       |       |   434   (1)| 00:00:06 |
|  16 |     TABLE ACCESS BY INDEX ROWID  | P_PARK_OUT       |     1 |    77 |       |     4   (0)| 00:00:01 |
|* 17 |      INDEX RANGE SCAN         | IDX_P_PARK_OUT       |     1 |       |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("RN">=1)
   2 - filter(ROWNUM<=10)
   4 - filter(ROWNUM<=10)
   5 - access("B"."PARKOUT_EXIT_NO"="D_OUT"."PD_NO" AND "A"."PLACE_ID"="D_OUT"."PD_PLACE_ID")
   6 - filter(TO_NUMBER("D_OUT"."PD_TYPE")=1)
   9 - access("A"."PARKIN_ENTRY_NO"="D_IN"."PD_NO" AND "A"."PLACE_ID"="D_IN"."PD_PLACE_ID")
  10 - filter(TO_NUMBER("D_IN"."PD_TYPE")=0)
  11 - access("A"."PLACE_ID"="P"."PLACE_ID")
  13 - access("P"."PLACE_STATUS"=1)
  15 - access("A"."SYS_NC00027$">=HEXTORAW('38FDBAE2C7EDFF')  AND
          "A"."SYS_NC00027$"<=HEXTORAW('38FDBAE3BAB1FF') )
       filter((SYS_OP_UNDESCEND("A"."SYS_NC00027$")>=1682768770000 AND
          SYS_OP_UNDESCEND("A"."SYS_NC00027$")<=1682855170000))
  17 - access("A"."PARKIN_INFO_ID"="B"."PARKOUT_IN_ID")

2. 分析与优化思路

2.1 排序阻塞

据业务人员反映，该sql是一个没有绑定变量的sql，PARKIN_TIME是页面的输入条件，当选取的时间范围越大，该sql性能消耗越大，查询时间越长。每一天的数据量大概在35w，当时间范围超过1天时，基本要等很久页面才会返回数据。

关于分页查询的优化，还是有套路的，而且个人认为所有数据库的分页查询优化思路都是一样的。一个完美的分页查询sql的执行计划，应该是一套非阻塞操作，其手段就是利用排序字段列的表对应的索引(免排序)作为驱动+NL连接(合适的索引实现精确匹配)+STOPKEY。基本无论传入什么输入条件，结果都应该是秒出的。很明显本案例的执行计划不是一个优秀的分页执行计划。

首先提一下执行计划的非阻塞操作。非阻塞操作其实就是当查询一个块的数据，不用等待其操作完成就可以按执行计划继续往下走或者返回客户端。比如全表扫描、索引范围扫描、NL连接都是非阻塞操作。常见的阻塞操作比如排序、HASH连接、VIEW关键字等等。对于分页查询的优化思路其实就是避免阻塞操作，并且尽量保证每一条记录的驱动都能最终返回(少做无用功)。

回到本案例，分析分页查询的第一个阻塞操作点order by部分：

     PARKIN_TIME DESC,
     PARKOUT_TIME DESC,
     createtime DESC

PARKIN_TIME和createtime来自表p_park_in，PARKOUT_TIME来自p_park_out，当排序字段来自多个表时，是没法通过索引去消除order by的阻塞操作的。发现第一个问题之后，立刻与开发沟通。

给出的建议有两种方法：

是否能去掉PARKOUT_TIME的order by，改为
ORDER BY PARKIN_TIME DESC, createtime DESC
如果不能去掉，可以改写sql分页部分，order by ORDER BY PARKIN_TIME DESC, createtime DESC之后，rownum<=10改成rownum<=10000，再根据这10000条记录做ORDER BY PARKIN_TIME DESC, PARKOUT_TIME DESC, createtime DESC，相当于排序了两次，应该没有人去翻页1000次。

     PARKIN_TIME DESC,
     createtime DESC
   ) page
  WHERE
   ROWNUM <= 10000
 )
 ORDER BY
     PARKIN_TIME DESC,
     PARKOUT_TIME DESC,
     createtime DESC)
  WHERE
   ROWNUM <= 10
 )
WHERE
 rn >= 1

最终开发认为PARKOUT_TIME的排序是可以去掉的，那么第一个阻塞点就消除了。结合谓词信息：

     1 = 1
    AND A .PARKIN_TIME >= '1682672708000'
    AND A .PARKIN_TIME <= '1682759108000'
    AND P .PLACE_STATUS = '1'

第一个优化建议：

创建索引PARKIN_TIME、CREATE_TIME组合索引。由该索引开始驱动，同时避免了排序阻塞操作(SORT ORDER BY STOPKEY)。

2.2 视图阻塞（VIEW）

仔细分析执行计划，第二个阻塞操作点

| Id  | Operation                 | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time       |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |       |       |       |   210K(100)|       |
|*  1 |  VIEW                     |               |    10 |  6340 |       |   210K  (1)| 00:42:04 |
|*  2 |   COUNT STOPKEY              |               |       |       |       |        |       |
|   3 |    VIEW                  |               | 25578 |    15M|       |   210K  (1)| 00:42:04 |
|*  4 |     SORT ORDER BY STOPKEY         |               | 25578 |    15M|    16M|   210K  (1)| 00:42:04 |
|*  5 |      HASH JOIN RIGHT OUTER         |               | 25578 |    15M|       |   206K  (1)| 00:41:24 |
|*  6 |       INDEX FAST FULL SCAN         | IDX_PD_TYPE_ID       |   810 | 30780 |       |    10   (0)| 00:00:01 |
|   7 |       VIEW                 |               | 25578 |    14M|       |   206K  (1)| 00:41:24 |
|   8 |        NESTED LOOPS OUTER         |               | 25578 |  9042K|       |   206K  (1)| 00:41:24 |
|*  9 |     HASH JOIN RIGHT OUTER         |               | 25268 |  7032K|       |   111K  (1)| 00:22:15 |
|* 10 |      INDEX FAST FULL SCAN         | IDX_PD_TYPE_ID       |   816 | 31008 |       |    10   (0)| 00:00:01 |
|* 11 |      HASH JOIN             |               | 25268 |  6094K|       |   111K  (1)| 00:22:15 |
|  12 |       TABLE ACCESS BY INDEX ROWID| P_PLACE           |  1683 | 99297 |       |   786   (0)| 00:00:10 |
|* 13 |        INDEX RANGE SCAN         | IDX_P_PLACE       |  1683 |       |       |     9   (0)| 00:00:01 |
|  14 |       TABLE ACCESS BY INDEX ROWID| P_PARK_IN       | 34646 |  6360K|       |   110K  (1)| 00:22:06 |
|* 15 |        INDEX RANGE SCAN         | IDX_PPI_PARKIN_TIME |   173K|       |       |   434   (1)| 00:00:06 |
|  16 |     TABLE ACCESS BY INDEX ROWID  | P_PARK_OUT       |     1 |    77 |       |     4   (0)| 00:00:01 |
|* 17 |      INDEX RANGE SCAN         | IDX_P_PARK_OUT       |     1 |       |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

ID=7的view关键字，说明VIEW下的连接需要关联查询完成当作一个整体才能与ID=6做HASH外连接。

为何会出现VIEW呢？为什么oracle不能做view merge的查询转换呢？view merge相关概念参考之前的一篇文章。

仔细分析sql文本发现：

     A .PLACE_ID = d_out.PD_PLACE_ID
     AND b.PARKOUT_EXIT_NO = d_out.pd_no
     AND d_out.PD_TYPE = 1
    )

与表p_place_door关联时，关联字段有表P_PARK_IN的PLACE_ID，还有表P_PARK_OUT的PARKOUT_EXIT_NO。如果oracle做了view merge，那么会导致d表同时与a和b都外连接，这在oracle中是不允许的，会报出ORA-01417错误。所以CBO不会对这样的view做merge转换。

  2  from test.t a,test.t2 b,TEST.T1 c
  3  where a.object_id=b.object_id and a.data_object_id=c.data_object_id(+) and b.object_type=c.object_type(+);
where a.object_id=b.object_id and a.data_object_id=c.data_object_id(+) and b.object_type=c.object_type(+)
                                                  *
ERROR at line 3:
ORA-01417: a table may be outer joined to at most one other table

那么要消除VIEW必须要CBO去把这个view merge掉，解决办法只有d表要么都与P_PARK_IN的字段关联，要么都与P_PARK_OUT的字段关联。

跟开发沟通之后P_PARK_OUT表的PARKOUT_PLACEID可以替代P_PARK_IN的PLACE_ID字段。

3. 优化后SQL

最终与开发沟通后，SQL优化如下：

 *
FROM
 (
  SELECT
   page.*, ROWNUM AS rn
  FROM
   (
    SELECT
     A .PARKIN_INFO_ID AS parkininfoid,
     A .CAR_ID AS carid,
     A .PLACE_ID AS placeid,
     P .PLACE_NAME AS placename,
     A .PARKIN_CAR_NO AS parkincarno,
     A .PARKIN_CARD_NO AS parkincardno,
     A .PARKIN_CAR_TYPE AS parkincartype,
     A .PARKIN_ENTRY_NO AS parkinentryno,
     A .PARKIN_TIME AS parkintime,
     A .PARKIN_PIC AS parkinpic,
     b.PARKOUT_EXIT_NO AS parkoutexitno,
     b.PARKOUT_TIME AS parkouttime,
     b.PARKOUT_PIC AS parkoutpic,
     A .PARKIN_ENTRY_NO AS poindoorno,
     b.PARKOUT_EXIT_NO AS pooutdoorno,
     A .createtime AS createtime,
     A .createby AS createby,
     A .updatetime AS updatetime,
     A .updateby AS updateby
    FROM
     p_park_in A
    LEFT JOIN p_park_out b ON A .PARKIN_INFO_ID = b.PARKOUT_IN_ID
    LEFT JOIN p_place P ON A .PLACE_ID = P .place_id
    LEFT JOIN p_place_door d_in ON (
     A .PLACE_ID = d_in.PD_PLACE_ID
     AND A .PARKIN_ENTRY_NO = d_in.pd_no
     AND d_in.PD_TYPE = 0
    )
    LEFT JOIN p_place_door d_out ON (
      b.PARKOUT_PLACEID = d_out.PD_PLACE_ID
     AND b.PARKOUT_EXIT_NO = d_out.pd_no
     AND d_out.PD_TYPE = 1
    )
    WHERE
     1 = 1
    AND A .PARKIN_TIME >= '1682672708000'
    AND A .PARKIN_TIME <= '1682759108000'
    AND P .PLACE_STATUS = '1'
    ORDER BY
     PARKIN_TIME DESC,
     createtime DESC
   ) page
  WHERE
   ROWNUM <= 10
 )
WHERE
 rn >= 1

消除了阻塞操作点之后，需要查看后续关联的对象上是否有对应的索引，保证NL连接能使用上，这里的索引都可以不考虑其选择性，其主要目的是保证精确匹配。

INDEX INFO
****ucptdvs "UNIQUENESS COMPRESSION PARTITIONED TEMPORARY  VISIBILITY SEGMENT_CREATED"**
****************************************************************************************
 
TABLE        TABLE                    Index                   COLUMN              Col
OWNER        NAME                    Name               UCPTDVS NAME               Pos DESC
--------------- ----------------------------------- ------------------------------ ------- ------------------------- ---- ----
LZPARKING    P_PARK_IN                IDX_INFOID               NNNNNVY PARKIN_INFO_ID        1 ASC
                                           NNNNNVY PLACE_ID            2 ASC
                                           NNNNNVY PARKIN_ENTRY_NO        3 ASC
                            IDX_PARKIN_TIME           NNNNNVY PARKIN_CAR_NO        1 ASC
                                           NNNNNVY SYS_NC00028$         2 DESC
                                           NNNNNVY SYS_NC00029$         3 DESC
                            IDX_PPI_PARKIN_TIME        NNNNNVY SYS_NC00027$         1 DESC
                            PK_P_PARK_IN           UNNNNVY PARKIN_INFO_ID        1 ASC
        P_PARK_OUT                IDX_P_PARK_OUT           NNNNNVY PARKOUT_IN_ID        1 ASC
                                           NNNNNVY PARKOUT_EXIT_NO        2 ASC
                                           NNNNNVY CREATETIME            3 ASC
                            IDX_P_PARK_OUT_IN_ID       NNNNNVY PARKOUT_IN_ID        1 ASC
                            PK_P_PARK_OUT           UNNNNVY PARKOUT_INFO_ID        1 ASC
        P_PLACE                 IDX_P_PLACE            NNNNNVY PLACE_STATUS         1 ASC
                            IDX_P_PLACE_ID           NNNNNVY PLACE_ID            1 ASC
                                           NNNNNVY PLACE_STATUS         2 ASC
                            SYS_C006913            UNNNNVY PLACE_ID            1 ASC
        P_PLACE_DOOR                IDX_PD_TYPE_ID           NNNNNVY PD_TYPE            1 ASC
                                           NNNNNVY PD_PLACE_ID            2 ASC
                                           NNNNNVY PD_NO            3 ASC
                            PK_P_PLACE_DOOR           UNNNNVY PD_ID            1 ASC

后续NL连接关联索引都有。那么看看优化效果，输入了一个月的时间范围，直接秒出结果。

----------------------------------------------------------
Plan hash value: 3360519549
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                         |     2 |  1268 |    17   (0)| 00:00:01 |
|*  1 |  VIEW                              |                         |     2 |  1268 |    17   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                    |                         |       |       |            |          |
|   3 |    VIEW                            |                         |     2 |  1242 |    17   (0)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER             |                         |     2 |   806 |    17   (0)| 00:00:01 |
|   5 |      NESTED LOOPS OUTER            |                         |     2 |   730 |    16   (0)| 00:00:01 |
|   6 |       NESTED LOOPS OUTER           |                         |     2 |   542 |     8   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                |                         |     2 |   466 |     7   (0)| 00:00:01 |
|   8 |         TABLE ACCESS BY INDEX ROWID| P_PARK_IN               |     3 |   522 |     4   (0)| 00:00:01 |
|*  9 |          INDEX RANGE SCAN          | IDX_PARK_IN_CREATE_TIME |     1 |       |     3   (0)| 00:00:01 |
|* 10 |         TABLE ACCESS BY INDEX ROWID| P_PLACE                 |     1 |    59 |     1   (0)| 00:00:01 |
|* 11 |          INDEX UNIQUE SCAN         | PK_P_PLACE              |     1 |       |     0   (0)| 00:00:01 |
|* 12 |        INDEX RANGE SCAN            | IDX_P_PLACE_DOOR_1      |     1 |    38 |     1   (0)| 00:00:01 |
|  13 |       TABLE ACCESS BY INDEX ROWID  | P_PARK_OUT              |     1 |    94 |     4   (0)| 00:00:01 |
|* 14 |        INDEX RANGE SCAN            | IDX_P_PARK_OUT_IN_ID    |     1 |       |     3   (0)| 00:00:01 |
|* 15 |      INDEX RANGE SCAN              | IDX_P_PLACE_DOOR_1      |     1 |    38 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("RN">=1)
   2 - filter(ROWNUM<=10)
   9 - access(SYS_OP_DESCEND("PARKIN_TIME")>=HEXTORAW('38FDBAE3C3F4AEFF')  AND
              SYS_OP_DESCEND("PARKIN_TIME")<=HEXTORAW('38FDBAE4B6B8AEFF') )
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("PARKIN_TIME"))>=1682672708000 AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("PARKIN_TIME"))<=1682759108000)
  10 - filter("P"."PLACE_STATUS"=1)
  11 - access("A"."PLACE_ID"="P"."PLACE_ID")
  12 - access("A"."PLACE_ID"="D_IN"."PD_PLACE_ID"(+) AND "A"."PARKIN_ENTRY_NO"="D_IN"."PD_NO"(+) AND
              TO_NUMBER("PD_TYPE"(+))=0)
  14 - access("A"."PARKIN_INFO_ID"="B"."PARKOUT_IN_ID"(+))
  15 - access("B"."PARKOUT_PLACEID"="D_OUT"."PD_PLACE_ID"(+) AND
              "B"."PARKOUT_EXIT_NO"="D_OUT"."PD_NO"(+) AND TO_NUMBER("PD_TYPE"(+))=1)
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        105  consistent gets
          0  physical reads
          0  redo size
       4268  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

优化效果非常明显，无论输入多少时间范围都是秒出结果。当一个分页查询的执行计划都是只有COUNT STOPKEY+NL连接+并且谓词都是access(没有filter或者filter的过滤条件过滤性微乎其微)，就是一个性能非常好的分页查询执行计划。