SQL分页查询方案的性能对比
导读
本文主要介绍了基于ROWNUM、主键列/非空唯一性列、分析函数、OFFSET-FETCH NEXT机制的几种SQL分页查询方案的性能对比。
分页查询可分为逻辑分页和物理分页两种。逻辑分页是应用代码级别实现的分页，指用户通过一次查询就取出所有的数据结果集并进行缓存，然后根据当前页所需要展示的数据内容进行切分并遍历显示，若需要查询的数据量非常大，则会消耗大量的内存来缓存数据，并且在会话生命周期内重复访问数据时，可直接访问缓存的数据，不过此时有可能访问不到最新的数据。物理分页是指使用数据库自带的分页机制，比如MySQL的limit offset机制，Oracle的rownum和offset-fetch机制进行分页查询，是对数据库表数据进行分页条件查询，每一次物理分页都会直接访问数据库，可以保证数据是最新的，并且不需要在会话级别缓存过多的数据。
本文主要介绍的SQL分页，即物理分页，主要用于在数据结果集较大时控制数据在前台(比如报表，列表框，页面等)的分页显示，这样既可以降低内存消耗，提高查询效率，也可以方便数据在前台的展示。文中如有疏漏之处，望指正！
环境版本信息
Oracle 版本：19.3.0.0.0
MySQL版本：8.0.18
OS版本：CentOS 8.0
方案及性能对比
1.确认测试表emp中的数据量
2.确认表结构和索引信息
3.通过rownum实现分页查询(不使用order by排序)
SQL: select * from ( select rownum rowno,e.* from emp e where rownum<=&ROW_NUM1) t where t.rowno>=&ROW_NUM2;执行计划信息:
通过执行计划和评估开销可以看出，该方法将使用全表扫描，前段的分页查询效率会比较高，但是随着ROWNUM值的增大，在分页后期查询的速度会越来越慢，这个情况和MySQL的limit机制一样，当表中数据量较大时，随着查询范围的扩大，每次需要读取的表数据块越来越多，查询效率越来越低。如下图所示：
4.通过rownum实现分页查询(使用order by排序)
SQL: select * from ( select rownum rowno,e.* from (select * from emp order by id) e where rownum<=&2) t where t.rowno>=&1;
执行计划信息：
由执行计划信息可以看出，当使用order by对数据集进行排序后再分页时，由于索引数据在存储的时候默认已经进行了升序排序(若有需要，也可以创建降序索引，该案例是基于Oracle环境，对于MySQL数据库，从8.0开始也支持了真正意义的降序索引)，因此使用了索引全扫描(即索引遍历)来避免排序，后期需要遍历的索引块越来越多，并且由于index full scan是单块读，所以该方法会出现在分页后期查询效率越来越慢的情况。如下图所示：
5.直接使用主键代替ROWNUM进行分页查询
查出id的最大值和最小值：
SQL: select * from emp where id between &1 and &2;
执行计划信息：
从执行计划信息可以看出，该方法使用了主键索引的range scan，当表数据量较大时，不会出现随着查询范围的扩大而查询效率越来越低的情况，因为可以直接通过主键或非空唯一性索引读取到符合条件的rowid，然后直接通过rowid找到数据块读取数据，如下图所示：
说明：
该方法需要主键值是连续的，否则有可能出现分页查询时每一页的数据行数不一样的情况。
假如表上有其他的非空唯一性索引列，则同样可以基于该列做分页查询。
若在分页查询时表上有一定的DML操作，则可以考虑进行最后一页查询时将SQL中的变量2设置较大一些(也可以通过子查询直接获取max(id))。
6.使用分析函数进行分页查询
SQL: select * from ( select e.*, row_number() over (order by id) rn from emp e) where rn between &1 and &2;
执行计划信息：
从执行计划信息可以看出，该方法使用了窗口函数进行分页查询，同样使用了INDEX FULL SCAN来避免排序，该方法也会出现在分页后期查询效率越来越慢的情况，因为后期需要遍历的索引块越来越多，并且由于index full scan是单块读，因此后期的效率有可能会比使用ROWNUM的方式更为低下，如下图所示：
SQL: select * from emp order by id OFFSET &1 ROWS FETCH NEXT &2 ROWS ONLY;
执行计划信息：
从执行计划可以看出，offset-fetch机制在底层本质上还是基于分析函数实现的，同样使用了索引全扫描(即索引遍历)来避免排序，因此该方法也会出现在分页后期查询效率越来越慢的情况，因为后期需要遍历的索引块越来越多，并且由于index full scan是单块读，从而产生的物理IO和逻辑IO次数更多，因此后期的效率有可能会比使用ROWNUM的方式更为低下，如下图所示：
8.排序列的选择
当列可为NULL时，Oracle不能使用该列上的索引来避免排序，因为Oracle的索引是不记录NULL值的，如下图所示：
通过对比分析，我们可以得出如下结论：
1.当主键值或者非空唯一性列值是连续时，推荐使用主键值或者非空唯一性列进行分页，此时分页效率较高且数据量较大时分页后期性能不会越来越差。
2.当对分页后每页的数据行数没有较高要求时，同样推荐使用主键值或者非空唯一性列进行分页。
3.使用分析函数和OFFSET-FETCH实现分页，分页后期的性能衰减率可能会比通过ROWNUM的方式高，这是因为index full scan是单块读，从而产生了更多次的物理IO和逻辑IO。
4.在使用分析函数和OFFSET-FETCH机制时，需要基于主键或非空唯一性列进行order by排序，此时会通过列上的索引来避免排序操作。若选择的排序列可为NULL，则Oracle数据库只能通过全表扫描来访问数据，因为Oracle数据库的索引是不记录NULL值的，因此不能基于该列上的索引来避免排序，从而保证不会丢失数据。
5.在MySQL中，索引是会记录NULL值的，这也是为什么MySQL中IS NULL可以走索引的原因。
6.MySQL数据库的分页中，可以使用可为null的非唯一性列作为排序列，因为此时MySQL会将null值当作最小值参加排序，不会丢失数据。
作者介绍：
吴海存，10g/11g/12c OCM, Oracle Exadata/Golden Gate 专家, 曾于Amazon和Oracle公司担任全球业务资深DBA，目前供职于中国农业银行，担任资深数据库专家。
更多阅读推荐