分页查询的各种实现、优化

分页查询的各种实现、优化limit 不是在 innodb 引擎层执行的而是在 server 执行的简单 order by、limit延迟回表create_time id 游标查询其他的异构方案假设有表 user需求是按照 create_time 倒序显示列表。createtableuser(idBIGINTUNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT主键ID,# 其他字段create_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT创建时间,PRIMARYKEY(id),KEYidx_create_time(create_time))limit 不是在 innodb 引擎层执行的而是在 server 执行的虽然没有在官方文档中说明limit是在 server 执行的不是在 innodb 引擎层。但可以从侧面推测出来我们知道 innodb 只能处理与索引相关的where条件本例中只能处理与id和create_time相关的where条件有时还需要依靠 index condition pushdown 才能尽可能的减少回表如果where条件不完全在索引中比如where create_time x and state 1中的state 1需要先根据索引idx_create_time查询到id根据id回表查询出完整记录将其从 innodb 引擎层发送到 server 层server 层再进行剩余条件state 1的判断。limit肯定是要在完全符合where条件的记录集合里挑选 N 条作为结果但 innodb 引擎层无法完整的判断记录是否符合where条件所以limit不是在 innodb 引擎层执行的而是在 server 执行的。使用Limit Offset下推提升分页查询性能-云原生数据库 PolarDB-阿里云 中提到社区MySQL的Limit操作完全由SQL层完成数据从引擎层读取后需要交给SQL层处理然后过滤掉Offset。当查询二级索引需要访问主表列的时候引擎层还会先回表获取所有需要的列信息。而对于SQL层无条件过滤的场景包含SQL层谓词完全下推到引擎层Limit操作处理OffSet的数据不需要经过任何计算就会被过滤掉引擎层与SQL层的交互和回表的代价会导致分页查询随着分页数增加而越来越慢。PolarDB MySQL版会把Limit Offset下推到引擎层这些数据直接在引擎中扫描过滤且选择二级索引时这些数据不需要进行回表。简单 order by、limit最简单、常见的实现方式是select * from user order by create_time desc limit 10。但是会有深度分页的问题select * from user order by create_time desc limit 1000000, 10。随着翻页的页数越来越大SQL 执行会越来越慢。深度分页慢的原因有可能不走idx_create_time索引而是全表扫描 filesort这样肯定很慢。走idx_create_time索引的情况。已知limit是在 server 层执行。上述 SQL 的执行顺序是扫描idx_create_time索引得到 1000010 个create_time、id的元组根据id回表查询出 1000010 条完整的记录有可能利用 MRR 优化将 1000010 条记录发送到 server 层这些记录已经按create_time排好序了不需要额外的filesort然后 server 层丢弃前 1000000 条将最后 10 条发送给客户端。其中 1000000 条的回表操作和 1000000 条完整记录发送到 server 层的操作是完全无效的而且消耗大量的 IO 和时间慢的原因就是这些无效操作。缺点如果有人删除了本页之前的数据在翻到下一页时会导致有几条数据被跳过显示。如果有人插入了新的数据在翻到上一页时会导致有数据重复显示。延迟回表想要加速深度分页的速度就要减少回表的次数和发送给 server 层的数据量。下面的 SQL 会报错[42000][1235] This version of MySQL doesnt yet support LIMIT IN/ALL/ANY/SOME subquery因为 MySQL 不允许在子查询中使用limit我们先忽略这个错误只看思路。select*fromuserasuwhereu.idin(selectidfromuserorderbycreate_timedesclimit1000000,10)orderbyu.create_timedesc;先执行子查询扫描idx_create_time索引只查询id列不回表查询完整记录可以利用索引覆盖use index的优化 。将 1000010 个id发送到 server 层这些id已经按create_time排好序了不需要额外的filesortserver 层丢弃前 1000000 条只留最后 10 个id。使用 10 个id执行select * from user as u where u.id in (10 个 id) order by u.create_time desc只取 10 条数据速度很快。最后返回客户端。优化的效果减少了回表的次数优化前回表 1000010 次优化后回表 10 次。发送给 server 层的数据量变少了优化前发送 1000010 条完整记录优化后发送 1000010 个id 10 条完整记录。使用join代替子查询解决报错SQL 的执行结果是一样的select*fromuserasujoin(selectidfromuserorderbycreate_timedesclimit1000000,10)astonu.idt.idorderbyu.create_timedesc;缺点与简单 order by、limit 相同的缺点。发送到 server 层的id个数还是随着页号 x 线性增长的f(x) nx只是n变小了变慢的速度减缓了如果遇到超大页号还是会很慢。create_time id 游标查询id 倒序如果需求是按照id倒序展示列表可以以本页的最后一条、第一条记录的id作为游标来查询下一页、上一页下一页 SQL 可以是select * from user where id lastId order by id desc limit 10其中lastId是本页最后一条的id这里排序是desc。上一页 SQL 可以是select * from user where id firstId order by id asc limit 10其中firstId是本页第一条的id这里排序是asc然后执行Collections.reverse(res)。create 倒序问题根据id倒序实现较为简单是因为id没有重复值但是create_time可能存在重复值在翻页时可能导致部分相同create_time的记录被跳过显示。排序字段有重复值可能导致相同create_time的记录每次的显示顺序不一样。为了解决这些问题可以在倒序排序时将id放在最后使用order by create_time desc, id desc这样的组合排序就不会存在重复值了。排序逻辑就是如果create_time不相等就取create_time大的如果create_time相等则再比较id取id大的。在上下翻页时需要同时传入create_time、id两个条件。下一页SQL 如下其中lastCreateTime是本页最后一条的create_timelastId是本页最后一条的id两个排序都是desc。select*fromuserwherecreate_timelastCreateTimeor(create_timelastCreateTimeandidlastId)orderbycreate_timedesc,iddesclimit10等价的写法select*fromuserwhere(create_time,id)(lastCreateTime,lastId)orderbycreate_timedesc,iddesclimit10上一页SQL 如下其中firstCreateTime是本页第一条的create_timefirstId是本页第一条的id两个排序都是asc然后执行Collections.reverse(res)。select*fromuserwherecreate_timefirstCreateTimeor(create_timefirstCreateTimeandidfirstId)orderbycreate_timeasc,idasclimit10等价的写法select*fromuserwhere(create_time,id)(firstCreateTime,firstId)orderbycreate_timeasc,idasclimit10第一页select*fromuserorderbycreate_timedesc,iddesclimit10最后一页SQL 结果需执行Collections.reverse(res)。select*fromuserorderbycreate_timeasc,idasclimit10优点执行时间几乎是常数与页号无关无论打开哪一页的执行速度都是一样的。拿下一页举例倒序扫描idx_create_time索引找到create_time lastCreateTime or (create_time lastCreateTime and id lastId)的第一条记录这个操作的执行时间只与 B tree 的高度有关与lastCreateTime、lastId的取值无关。然后取 10 个id发送到 server 层id是按照create_time desc, id desc排好序的无效额外的filesort。从 innode 中获取这 10 个id对应的完整记录返回客户端。缺点只能进行上下翻页、第一页、最后一页无法直接跳到中间的第 N 页。其他的异构方案searchAfter 是 elasticsearch 中的游标查询方案