sql优化-分页查询效率慢的问题分析及处理

    订单查询有个分页查询的功能，在线上查询20条数据时速度较慢，耗时在9s左右。后来定位问题是因为一条sql执行过慢。

    系统使用的是mybatis，配置的sql如下：

SELECT * FROM (SELECT T1_.*, rownum ROWNUM_ FROM (
SELECT  O.* FROM TRADE_ORDER O
WHERE 1=1
 AND O.CREATE_TIME <![CDATA[ >= ]]> #{startDate}
AND O.CREATE_TIME <![CDATA[ < ]]> #{endDate}
 ORDER BY O.CREATE_TIME DESC
) T1_ WHERE ROWNUM <= (#{offset}+#{size})) WHERE ROWNUM_ >= (#{offset}+1)

    由于mybatis执行sql时使用的是PreparedStatement（绑定参数），因此oracle在解析这条sql时，使用的是软解析。这里说明下硬解析和软解析的区别。

    1、硬解析每次都会对sql进行解析，无法共享执行计划。

    2、软件系会使用一个共享池，对于相同的sql共享执行计划。只有使用了绑定参数的sql才可能会被认为是相同的sql。比如上面那条sql，在oracle执行时，会使用统一的执行计划。

    现在，我们分析该sql为什么会这么慢。

    背景：

    1、在表TRADE_ORDER上的CREATE_TIME上建立了索引。

    2、该表目前有1000w+数据。

    3、CREATE_TIME列中数据比较分散。

    分析：

    1、执行该sql花了9s的时间，一般千万数据量的表做全表扫描时差不多也就这么长时间。

    2、找到该sql的sql id，查看执行计划。

查询sql id：

select * from V$sql t where t.sql_text like 'select * from (select t1.*, rownum ROWNUM_ from ( select o.* from trade_order o%'

根据sql id查看执行计划：

select plan_table_output from table(dbms_xplan.display_cursor('9zqcz386hrnvk'));

执行计划为：

    从执行计划可以看出：

    1、create_time没有走索引，而是执行了全表扫描。

    2、2与4的筛选重复了。

    怎么避免全表扫描呢？从执行计划中可以看出，绑定参数进行了计算。

    在编写sql时，尽量避免参数在数据库端进行运算，应该将运算过程放在应用端进行。

    修改sql为：

SELECT * FROM (SELECT T1_.*, rownum ROWNUM_ FROM (
SELECT  O.* FROM TRADE_ORDER O
WHERE 1=1
 AND O.CREATE_TIME <![CDATA[ >= ]]> #{startDate}
AND O.CREATE_TIME <![CDATA[ < ]]> #{endDate}
 ORDER BY O.CREATE_TIME DESC
) T1_ WHERE ROWNUM <= #{end}) WHERE ROWNUM_ >= #{start}

    这次执行1s左右就返回了结果。

    按照上面步骤，查看执行计划：

    可以看出，已经正常的走了索引，执行速度提升很多。

    总结：在编写sql时，尽量避免参数在数据库端进行运算，应该将运算过程放在应用端进行。特别是对于绑定参数，在sql层面进行参数运算，可能会超出想象。