中国农业银行吴海存责编
晋兆雨头图
CSDN下载自视觉中国导读
本文主要介绍了基于ROWNUM、主键列/非空唯一性列、分析函数、OFFSET-FETCHNEXT机制的几种SQL分页查询方案的性能对比。
分页查询可分为逻辑分页和物理分页两种。逻辑分页是应用代码级别实现的分页，指用户通过一次查询就取出所有的数据结果集并进行缓存，然后根据当前页所需要展示的数据内容进行切分并遍历显示，若需要查询的数据量非常大，则会消耗大量的内存来缓存数据，并且在会话生命周期内重复访问数据时，可直接访问缓存的数据，不过此时有可能访问不到最新的数据。物理分页是指使用数据库自带的分页机制，比如MySQL的limitoffset机制，Oracle的rownum和offset-fetch机制进行分页查询，是对数据库表数据进行分页条件查询，每一次物理分页都会直接访问数据库，可以保证数据是最新的，并且不需要在会话级别缓存过多的数据。
本文主要介绍的SQL分页，即物理分页，主要用于在数据结果集较大时控制数据在前台(比如报表，列表框，页面等)的分页显示，这样既可以降低内存消耗，提高查询效率，也可以方便数据在前台的展示。文中如有疏漏之处，望指正！
环境版本信息Oracle版本：19.3.0.0.0
MySQL版本：8.0.18
OS版本：CentOS8.0
方案及性能对比
1.确认测试表emp中的数据量.确认表结构和索引信息3.通过rownum实现分页查询(不使用orderby排序)
SQL:select*from(selectrownumrowno,e.*fromempewhererownum=ROW_NUM1)twheret.rowno=ROW_NUM;
执行计划信息:
通过执行计划和评估开销可以看出，该方法将使用全表扫描，前段的分页查询效率会比较高，但是随着ROWNUM值的增大，在分页后期查询的速度会越来越慢，这个情况和MySQL的limit机制一样，当表中数据量较大时，随着查询范围的扩大，每次需要读取的表数据块越来越多，查询效率越来越低。如下图所示：
4.通过rownum实现分页查询(使用orderby排序)SQL:select*from(selectrownumrowno,e.*from(select*fromemporderbyid)ewhererownum=)twheret.rowno=1;
执行计划信息：
由执行计划信息可以看出，当使用orderby对数据集进行排序后再分页时，由于索引数据在存储的时候默认已经进行了升序排序(若有需要，也可以创建降序索引，该案例是基于Oracle环境，对于MySQL数据库，从8.0开始也支持了真正意义的降序索引)，因此使用了索引全扫描(即索引遍历)来避免排序，后期需要遍历的索引块越来越多，并且由于indexfullscan是单块读，所以该方法会出现在分页后期查询效率越来越慢的情况。如下图所示：
5.直接使用主键代替ROWNUM进行分页查询
查出id的最大值和最小值：
SQL:select*fromempwhereidbetween1and;
执行计划信息：
从执行计划信息可以看出，该方法使用了主键索引的rangescan，当表数据量较大时，不会出现随着查询范围的扩大而查询效率越来越低的情况，因为可以直接通过主键或非空唯一性索引读取到符合条件的rowid，然后直接通过rowid找到数据块读取数据，如下图所示：
说明：
该方法需要主键值是连续的，否则有可能出现分页查询时每一页的数据行数不一样的情况。
假如表上有其他的非空唯一性索引列，则同样可以基于该列做分页查询。
若在分页查询时表上有一定的DML操作，则可以考虑进行最后一页查询时将SQL中的变量设置较大一些(也可以通过子查询直接获取max(id))。
6.使用分析函数进行分页查询SQL:select*from(selecte.*,row_number()over(orderbyid)rnfromempe)wherernbetween1and;
执行计划信息：
从执行计划信息可以看出，该方法使用了窗口函数进行分页查询，同样使用了INDEXFULLSCAN来避免排序，该方法也会出现在分页后期查询效率越来越慢的情况，因为后期需要遍历的索引块越来越多，并且由于indexfullscan是单块读，因此后期的效率有可能会比使用ROWNUM的方式更为低下，如下图所示：
SQL:select*fromemporderbyidOFFSET1ROWSFETCHNEXTROWSONLY;
执行计划信息：
从执行计划可以看出，offset-fetch机制在底层本质上还是基于分析函数实现的，同样使用了索引全扫描(即索引遍历)来避免排序，因此该方法也会出现在分页后期查询效率越来越慢的情况，因为后期需要遍历的索引块越来越多，并且由于indexfullscan是单块读，从而产生的物理IO和逻辑IO次数更多，因此后期的效率有可能会比使用ROWNUM的方式更为低下，如下图所示：
8.排序列的选择
当列可为NULL时，Oracle不能使用该列上的索引来避免排序，因为Oracle的索引是不记录NULL值的，如下图所示：
通过对比分析，我们可以得出如下结论：
1.当主键值或者非空唯一性列值是连续时，推荐使用主键值或者非空唯一性列进行分页，此时分页效率较高且数据量较大时分页后期性能不会越来越差。
.当对分页后每页的数据行数没有较高要求时，同样推荐使用主键值或者非空唯一性列进行分页。
3.使用分析函数和OFFSET-FETCH实现分页，分页后期的性能衰减率可能会比通过ROWNUM的方式高，这是因为indexfullscan是单块读，从而产生了更多次的物理IO和逻辑IO。
4.在使用分析函数和OFFSET-FETCH机制时，需要基于主键或非空唯一性列进行orderby排序，此时会通过列上的索引来避免排序操作。若选择的排序列可为NULL，则Oracle数据库只能通过全表扫描来访问数据，因为Oracle数据库的索引是不记录NULL值的，因此不能基于该列上的索引来避免排序，从而保证不会丢失数据。
5.在MySQL中，索引是会记录NULL值的，这也是为什么MySQL中ISNULL可以走索引的原因。
6.MySQL数据库的分页中，可以使用可为null的非唯一性列作为排序列，因为此时MySQL会将null值当作最小值参加排序，不会丢失数据。
作者介绍：
吴海存，10g/11g/1cOCM,OracleExadata/GoldenGate专家,曾于Amazon和Oracle公司担任全球业务资深DBA，目前供职于中国农业银行，担任资深数据库专家。
更多阅读推荐
蓝色巨人IBM全力奔赴的混合云之旅能顺利吗？
大数据给教育带来怎样的可能？
对话阿里云：开源与自研如何共处？
除了云原生，01年还有这八大趋势值得