网站建设费专用票,十年网站建设,百度seo提交,免费网站app下载作者 | 中国农业银行 吴海存责编 | 晋兆雨头图 | CSDN下载自视觉中国导读本文主要介绍了基于ROWNUM、主键列/非空唯一性列、分析函数、OFFSET-FETCH NEXT机制的几种SQL分页查询方案的性能对比。分页查询可分为逻辑分页和物理分页两种。逻辑分页是应用代码级别实现的分页#x… 作者 | 中国农业银行 吴海存责编 | 晋兆雨头图 | CSDN下载自视觉中国导读本文主要介绍了基于ROWNUM、主键列/非空唯一性列、分析函数、OFFSET-FETCH NEXT机制的几种SQL分页查询方案的性能对比。分页查询可分为逻辑分页和物理分页两种。逻辑分页是应用代码级别实现的分页指用户通过一次查询就取出所有的数据结果集并进行缓存然后根据当前页所需要展示的数据内容进行切分并遍历显示若需要查询的数据量非常大则会消耗大量的内存来缓存数据并且在会话生命周期内重复访问数据时可直接访问缓存的数据不过此时有可能访问不到最新的数据。物理分页是指使用数据库自带的分页机制比如MySQL的limit offset机制Oracle的rownum和offset-fetch机制进行分页查询是对数据库表数据进行分页条件查询每一次物理分页都会直接访问数据库可以保证数据是最新的并且不需要在会话级别缓存过多的数据。本文主要介绍的SQL分页即物理分页主要用于在数据结果集较大时控制数据在前台(比如报表列表框页面等)的分页显示这样既可以降低内存消耗提高查询效率也可以方便数据在前台的展示。文中如有疏漏之处望指正环境版本信息Oracle 版本19.3.0.0.0MySQL版本8.0.18OS版本CentOS 8.0方案及性能对比1.确认测试表emp中的数据量2.确认表结构和索引信息3.通过rownum实现分页查询(不使用order by排序)SQL: select * from ( select rownum rowno,e.* from emp e where rownumROW_NUM1) t where t.rownoROW_NUM2; 执行计划信息:通过执行计划和评估开销可以看出该方法将使用全表扫描前段的分页查询效率会比较高但是随着ROWNUM值的增大在分页后期查询的速度会越来越慢这个情况和MySQL的limit机制一样当表中数据量较大时随着查询范围的扩大每次需要读取的表数据块越来越多查询效率越来越低。如下图所示 4.通过rownum实现分页查询(使用order by排序)SQL: select * from ( select rownum rowno,e.* from (select * from emp order by id) e where rownum2) t where t.rowno1;
执行计划信息由执行计划信息可以看出当使用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目前供职于中国农业银行担任资深数据库专家。更多阅读推荐蓝色巨人IBM全力奔赴的混合云之旅能顺利吗大数据给教育带来怎样的可能对话阿里云开源与自研如何共处除了云原生2021 年还有这八大趋势值得关注算力至上四大AI芯片大对决
