记一次ORACLE分页优化

一、场景描述：springmvc项目，mybatis，oracle数据库，目标表中有大概三百万条数据。需求要能够分页展示。

二、问题描述：使用pageHelper分页插件，对查出来的list进行分页。结果是一直pending的状态，最后超时报错。

三、知识储备：逻辑分页和逻辑分页

        逻辑分页只需要访问一次数据库，是把DB中数据一次性查出来，然后通过自己在代码中利用分页插件或其他方法实现分页。这种情况在数据量小的情况下还好，但是数据量大的话，会占用很多内存，并且查询比较耗时。

        物理分页是每次查询都要连接数据库，查询的条数就是你要查的那一页的内容。所以数据量大也不会有太大影响。

四、优化思路：

        1、加好各种限制条件，尽量让查询范围缩小。

        2、采用物理分页。
        

五、代码示例：

SELECT
   *
FROM
   (
      SELECT
         ROWNUM RN,
         A.*
      FROM
         (SELECT
            UDISPLAY userName,
            UCARDNO cardNo,
            USTATUS cardStatus,
            UTYPE cardType,
            STARTDATE startDate,
            count(*) over () totalCount
         FROM
            TABLE_NAME
         WHERE AREA = 'shanghai'
            <if test="userBO.userName != null and userBO.userName != ''">
               AND  UDISPLAY = #{userBO.userName}
            </if>
            <if test="userBO.cardNo != null and userBO.cardNo != ''"  >
               AND  UCARDNO = #{userBO.cardNo}
            </if>
            <if test="userBO.cardStatus != null and userBO.cardStatus != ''">
               AND  USTATUS = #{userBO.cardStatus}
            </if>
            <if test="userBO.cardType != null and userBO.cardType != ''">
               AND  UTYPE = #{userBO.cardType}
            </if>
         ) A
      WHERE
         ROWNUM &lt;= #{userBO.endRowNum}
   )
WHERE
RN &gt;= #{userBO.startRowNum}

说明：上面的startRowNum和endRowNum是计算出来的：

        startRowNum = (currentPage - 1) * pageSize + 1

        endRowNum = currentPage * pageSize

其中currentPage代表当前页数，pageSize代表每页行数。

六、代码解析：

        a) 最内层的where条件就是加上各种限制条件，使得查询范围尽量缩小。

        b) 利用 count(*) over () 查询出总条数。

七、结果对比：

        优化前：接口一直pending状态，sql查询大概要花费十几秒。

        优化后：接口平均响应时间大概0.5秒左右。

以上仅为我本次优化中实际操作总结，如果有些地方过于主观或片面，欢迎指导，一起进步，加油！