MySQL 大表分页 (附独门秘技)

问题背景

MySQL(InnoDB)中的订单表需要按时间顺序分页查询, 且主键不是时间维度递增, 订单表在百万以上规模, 此时如何高效地实现该需求?

注: 本文并非主要讲解如何建立索引, 以下的分析均建立在有合适的索引的前提下

初步方案 1

众所周知, MySQL 中, 有一个 limit offset, pageSize 的用法, 可以实现分页查询

select * from order where user_id = xxx and [其它业务条件] order by created_time, id limit offset, pageSize

因为 created_time 可能重复, 所以 order by 时应加上 id, 保证顺序的确定性

点评

该方案在表规模较小的时候, 不会暴露出问题, 当 order 表增长到十万级, 并且查询后面几页的时候, 执行速度明显变慢, 可能降到 100ms 的量级, 如果数据量增长到百万级, 则耗时达到秒级, 如果增长到千万级, 那耗时就变得完全不可接受了(曾排查过这样的线上慢 SQL)

深入分析

方案 1 为啥在大表中表现这么差呢? 我们可以来揣测一下 MySQL 是怎么执行这个查询的

假设我们在 user_id,created_time, 以及[其它业务条件] 建立了联合索引, 当我要查找第 100000 条到 100049 条的记录时, 因为 MySQL 的索引是 b+ tree 结构, 不像数组可以随机定位到第 N 条记录, 它需要花不小的成本去找到 N 的位置, N 越大, 成本越大

抛开 b+ tree 的细节不讲, 我们还可以借助统计表记录总数的 SQL 来理解

select count(1) from order

如果能非常高效地定位第 N 条记录, 那么上述统计也能非常高效的执行, 但实际上, 在大表中统计记录总条数, 也是非常慢的(本文是在 InnoDB 的场景下)

方案 1 低效的根本原因在于: 定位到 offset 的成本过高, 未能充分利用索引的有序性

方案 2

索引 (b+ tree) 的特点在于, 数据是有序的, 虽然找到第 N 条记录的效率比较低, 但找到某一条数据在索引中的位置, 其效率是很高的(索引本来就是解决这个问题的)

我们换一种思路, 每次取 50 条记录, 第一次取的时候, 指定从上次结束的位置继续往后取 50 条, 这样, 我们便可以利用上索引的有序性了

我们先看一个以 id 为序, 进行分页查询的例子

select * from order where id> 'pre max id' order by id limit 50

第一次查询不用带条件, 后续查询则传入前一次查询的最大 id, 简单分析可知, MySQL 在执行时, 先定位到 pre max id 的位置(id 是有序的, 定位非常快), 然后从这往后取 50 条记录即可, 整个过程非常高效

我们回到最开始的问题,"按时间顺序分页查询, 且主键不是时间维度递增", 此时我们不能用 id 作为分页的条件, 因为按它去分页, 便不是按时间顺序了, 但也不能直接把 id 换成时间, 因为时间可能会重复, 我们来分析一下

假如前一次分页的最后一条记录为 id=ddd 的这条(created_time 为 2019-02-03), 下一次查询使用 created_time>2019-02-03 作为条件时, 则会把 id=yyy 的这条记录漏掉, 如果换成 created_time>=2019-02-03 也不行, id=ddd 的这条记录就又被查出来了

对于这个数据遗漏或重复的问题, 我看到一种解决方案是这样的:

分三种情况进行查询

首次查询, created_time>='xxxx-xx-xx', 如果不要求以某时间开始, 则无条件

select * from order where user_id = xxx and [其它业务条件] and created_time>= 'xxxx-xx-xx' order by created_time, id limit pageSize

如果上次查询的记录条数等于 pageSize, 则用 created_time 和 id 的组合条件来查询, 为了防止 created_time 在边界位置发生重复时漏掉数据

select * from order where user_id = xxx and [其它业务条件] and created_time = 'created_time of latest recored' and id> 'id of latest recored' order by created_time, id limit pageSize

如果上次查询的记录数小于 pageSize, 并且上次查询是第二种查询, 则仅用 created_time 来查询,

select * from order where user_id = xxx and [其它业务条件] and created_time> 'created_time of latest recored' order by created_time, id limit pageSize

注意:

created_time 不能为 null, 否 = 和>会返回 null, 导致对应结果查不出来, 如果存在为 null 的情况, 则需要对部分查询把 = 和>分别改为 is null 和 is not null 来查询

点评

上述方法确实可以解决漏掉数据或重复的问题, 并且也有着不错的性能, 但缺点也比较明显, 查询过于复杂, 得分情况执行不同的 SQL, 并且分页不稳定, 中间查询出来的记录数可能小于 pageSize(如果没有重复项, 那会多出一倍的结果为空的查询), 实际上后面还有数据

进一步深入分析

我尝试在网上找过资料, 只找到了以 id 为分页顺序, 然后用 id>'pre max id'这种方式来查, 而我们要以可重复的 created_time 为分页顺序, 如何写出简洁高效的 SQL 呢?

如果要成为一个优秀的程序员, 我觉得分析 & 解决新问题的能力, 是必不可少的, 即使在网上能找到解决方案, 优秀的分析能力也有助于借鉴并结合自己的场景, 优化出更好的个性化方案.

我们在 (user_id,created_time) 建立了索引, 并且我们知道 InnoDB 的辅助索引是包含了主键的, 且主键一定不会重复, 这意味着在索引上, 每条记录的顺序是完全确定的, 不存在重复的情况

我们要分页的顺序跟此索引的顺序是吻合的, 只需要沿着索引, 一批一批地取数据就可以了, 这是一个对索引很直接的利用, 为什么现在我没办法做到?

如果我是 MySQL 的设计人员, 针对这种很常见很直接的需求, 我怎么去提供支持? 还是说不支持?

我举一个例子, 像 java 中的基于排序的 TreeSet, 我猜它一定有 floor 和 ceiling 这样的方法(返回 Set 中, 大于或小于指定元素的第一个元素), 这是基于排序的数据结构该有的东西, 如果它没有, 那早被人喷了然后加上去了

回到索引的话题, 这种直接的需求, 它应该支持, 否则说不过去, 现在的问题变成了: 用什么语法来, 来实现在组合索引上, 基于组合 (user_id,created_time,id 的组合) 顺序的遍历?

此时脑海里便回想起以前用过的 (a,b) in ((1,2),(3,4),(7,4)) 这样的组合写法, 然后猜测它也支持大于小于这类比较, 跑去 MySQL 中验证一下:

select (3,7)>(3,7), (3,6)>(3,7), (3,8)>(3,7), (4,7)>(3,7), (4,2)>(3,7);

返回:

0 0 1 1 1

如此一来, 这问题就变得和 id>'pre max id'这种一样简单了.

这种写法在官方文档中也找到了对应的资料, 官方称这类运算为 "行比较"(row comparisons)

看到这里, 也许你跟我当时一样, 即开心又兴奋, 一个完美的方案就在眼前, 然而 MySQL 优化器没有我们想像的聪明, 在 "行比较" 面前, 就变成了二傻子, 不能很好地使用索引了

此时我又回过头去试验了一下 "行比较" 对应的等价写法

(a,b)>(x,y)

等价于

a>x or (a=x and b>y)

发现这种看似很复杂且还有 or 的写法, 竟然能很好地使用索引, 效率非常高, 即使像(a,b,c)>(x,y,z), 改成很复杂的等价写法:

a>x or (a=x and (b>y or (b=y and c>z)))

也能很好地使用索引, 此时真不知道该夸它还是骂它, 唉

关于 "行比较" 的索引选择, 在官网找到这样一份资料, 文中说索引覆盖不到时, 建议拆开成普通写法, 这样看来, 也许人家是有什么苦衷吧

方案 3

由于有了 a>x or (a=x and b>y)这种等价于组合比较的语法, 且能正确地使用索引, 所以可以写出高效且还算简洁的 SQL

select * from order

where user_id = xxx and [其它业务条件] and (created_time> 'created_time of latest recode' or (created_time = 'created_time of latest recode' and id> 'id of latest recode'))

order by created_time, id limit pageSize

此方式跟以 id 为序的分页查询是一样的, 首次查询去掉组合条件即可, 代码略显复杂, 好在可以利用上组合索引, 十分高效, 耗时稳定, 不会因为遍历到末尾而性能降低

遗憾地是, 最优雅的方式却撞见个二傻子优化器, 按理说用他们支持的特定语法 (变化范围更小, 模式更固定) 去精确地表达查询需求, 应该更容易被优化器识别出来并用最优方案去执行才说得通, 结果却不如人意

希望以后能 MySQL 更好地支持 "行比较" 吧(8.0.19 仍存在问题)

注意:

这里也不允许 created_time 为 null, 因为 null 值参与>和 = 运算, 结果一律为 null, 即条件不成立, 相应结果查不出来.

如果存在为 null 的情况, 则要作一些调整, 如果前一批数据的最后一条记录的 created_time 为 null(null 在索引中被视作极小值), 则可以这样改:

(created_time is not null or (created_time is null and id> 'id of latest recode'))

仍旧可以走索引, 实现高效分页查询

总结

方案 1 在小表的情况下, 简单方便, 只用传页码和页大小即可, 还可以随机跳到指定页, 具有一定优势

方案 2 和方案 3 在大表的情况下, 有着优异的性能, 以及稳定性, 缺点是不能随机地跳转页面, 需要传入上一页的排序字段. 这个弊端在一定程度上可以规避, 比如现在很多分页都是一页一页地往下翻, 比如微博, 朋友圈动态等, 或者是分批处理全表数据, 不需要随机跳转

细心的同学可能发现, where 条件里还有[其它业务条件] , 这样还能正常走索引吗? 是否会发生全表扫描? 这个问题其实是可以规避的, 有空再写一篇执行计划并不完全可靠的案例.

注: 执行计划有时不能正确地反映实际执行效果, 所以我没有贴执行计划; 我使用的 MySQL 版本为 5.7.23 和 8.0.19

题外话

方案 3 的写法是我自己琢磨出来的, 在网上也没找到类似的资料, 算独门秘技吧, 除此之外, 我觉得同样很有价值的是[进一步深入分析] 中的思考过程, 如果养成这种思考习惯, 有利于创新, 去解决别人没遇到过的问题, 在未知的领域, 知道该从哪个方向去寻找答案; 或者找到新的方法更好地去解决旧问题.

如果本文有帮助到你, 或者觉得有价值, 麻烦点个赞, 这样我会更有动力去更多地分享自己的经验

来源: https://www.cnblogs.com/trytocatch/p/mysql-page-query.html