74.MySQL 分页原理与优化（下）

前言

上一篇文章介绍了分页原理与优化：73.MySQL 分页原理与优化（上）

但分页还有一个“坑”需要注意，本文细细道来，可能很多朋友都踩过这个坑还不自知，所以希望本文能对大家有所帮助。

引入

之前踩到一个比较无语的生产 BUG，严格来说其实也不能算是 BUG，只能说开发同事对于产品的需求理解没有到位。

这个 BUG 其实和分页没有任何关系，但是当我去排查问题的时候，我看了一眼 SQL ，大概是这样的：

select * from table order by priority limit 1;

priority，就是优先级的意思。

按照优先级 order by 然后 limit 取优先级最高（数字越小，优先级越高）的第一条 ，结合业务背景和数据库里面的数据，我立马就意识到了问题所在。

想起了我当年在写分页逻辑的时候，虽然场景和这个完全不一样，但是踩过到底层原理一模一样的坑，这玩意印象深刻，所以立马就识别出来了。

借着这个问题，也盘点一下遇到过的三个关于分页查询有意思的坑。

一、一次分页查询的演进

职业生涯的第一个生产 BUG 就是一个小小的分页查询。

当时还在做支付系统，接手的一个需求也很简单就是做一个定时任务，定时把数据库里面状态为初始化的订单查询出来，调用另一个服务提供的接口查询订单的状态并更新。

由于流程上有数据强校验，不用考虑数据不存在的情况。所以该接口可能返回的状态只有三种：成功，失败，处理中。

很简单，很常规的一个需求对吧，分分钟就能写出伪代码(注意：这里为了演示简便，忽略了错误处理)

//获取订单状态为初始化的数据(0:初始化 1:处理中 2:成功 3:失败)
//select * from order where order_status=0;
initOrderInfoList := queryInitOrderInfoList()
//循环处理这批数据
for _, orderInfo := range initOrderInfoList {
   //发起rpc调用
    orderStatus := queryOrderStatus(orderInfo.getOrderId)
    //更新订单状态
    updateOrderInfo(orderInfo.getOrderId,orderStatus)  
}

来，你说上面这个程序有什么问题？

其实在绝大部分情况下都没啥大问题，数据量不多的情况下程序跑起来没有任何毛病。

但是，如果数据量多起来了，一次性把所有初始化状态的订单都拿出来，是不是有点不合理了，万一把内存给你撑爆了怎么办？

所以，在我已知数据量会很大的情况下，我采取了分批次获取数据的模式，假设一次性取 100 条数据出来玩。

那么 SQL 就是这样的：

select * from order where order_status=0 order by create_time limit 100;

所以上面的伪代码会变成这样：

for {
    //获取订单状态为初始化的数据(0:初始化 1:处理中 2:成功 3:失败)
    //select * from order where order_status=0 order by create_time limit 100;
    initOrderInfoList := queryInitOrderInfoList()
    
  	if len(initOrderInfoList) == 0 {
  		break
  	}
  	
   //循环处理这批数据
	for _, orderInfo := range initOrderInfoList {
	   //发起rpc调用
	    orderStatus := queryOrderStatus(orderInfo.getOrderId)
	    //更新订单状态
	    updateOrderInfo(orderInfo.getOrderId,orderStatus)  
	}
}

来，你又来告诉我上面这一段逻辑有什么问题？

作为程序员，我们看到for{}这样的写法立马就要警报拉满，看看有没有死循环的风险。

那你说上面这段代码在什么时候退不出来？

尽管一直是取前100条状态为初始化的，一般情况下一定会取完所有数据，然后退出循环，但是当有任何一条数据的状态没有从初始化变成成功、失败或者处理中的时候，就会导致一直循环。

而虽然发起 RPC 调用的地方，服务提供方能确保返回的状态一定是成功、失败、处理中这三者之中的一个，但是这个有一个前提是接口调用正常的情况下。

如果接口调用一旦异常，那么按照上面的写法，在RPC调用出错，如超时后，状态并未发生变化，还会是停留在“初始化”，从而导致死循环。

当年，测试同学在测试阶段直接就测出了这个问题，然后我对其进行了修改。

我改变了思路，把每次分批次查询前 100 条数据，修改为了分页查询，一页一页的往后查，这样会忽略前面页数失败的记录：

for{
    pageNum=pageNum+1;
    //获取订单状态为初始化的数据(0:初始化 1:处理中 2:成功 3:失败)
    //select * from order where order_status=0 order by create_time limit pageNum*100,100;
   // 注意这里queryInitOrderInfoList方法中，是会根据传入的pageNum,100（pageNum，size）进行分页的
    initOrderInfoList := queryInitOrderInfoList(pageNum,100)
    
  	if len(initOrderInfoList) == 0 {
  		break
  	}
  	
   //循环处理这批数据
	for _, orderInfo := range initOrderInfoList {
	   //发起rpc调用
	    orderStatus := queryOrderStatus(orderInfo.getOrderId)
	    //更新订单状态
	    updateOrderInfo(orderInfo.getOrderId,orderStatus)  
	}
}

跳出循环的条件为判断最后一次获取的条数是否为0。

由于每循环一次，当前页就加一，那么理论上讲一定会是翻到最后一页的，没有任何毛病，对不对？

我们可以分析一下上面的代码逻辑。

假设，我们有 120 条 order_status=0 的数据。那么第一页，取出了 100 条数据：

SELECT * from order_info WHERE order_status=0 LIMIT 0,100;

这 100 条处理完成之后，第二页还有数据吗？

第二页对应的 sql 为：

SELECT * from order_info WHERE order_status=0 LIMIT 100,100;

但是这个时候，状态为 0 的数据，只有 20 条了，而分页要从第 100 条开始，是不是获取不到数据，导致遗漏数据了？

确实一定会翻到最后一页，解决了死循环的问题，但又有大量的数据遗漏怎么办呢？

当时我苦思冥想，想到一个办法：导致数据遗漏的原因是因为我在翻页的时候，数据状态在变化，导致总体数据在变化。

那么如果我每次都从后往前取数据，每次都固定取最后一页，能取到数据就代表还有数据要处理，循环结束条件修改为“当前页即是第一页，也是最后一页时”就结束，这样不就不会遗漏数据了？

我再给你分析一下。

假设，我们有 120 条order_status=0的数据，从后往前取了 100 条出来进行处理，有 90 条处理成功，10 条的状态还是停留在“处理中”。

第二次再取的时候，会把剩下的 20 条和这次“处理中”的 10 条，共计 30 条再次取出来进行处理。确保没有数据遗漏。

后来测试环节验收通过了，这个方案上线之后，也确实没有遗漏过数据了。

直到后来又一天，提供 queryOrderStatus 接口的服务异常了，我发过去的请求超时了。

导致我取出来的数据，每一条都会报错，都不会更新状态。从而导致我每次从后往前取数据，都取到的是同一批数据。

从程序上的表现上看，日志疯狂的打印，但是其实一直在处理同一批，就是死循环了。

最后随着业务的发展，这块逻辑也完全发生了变化，逻辑由我们主动去调用 RPC 接口查询状态变成了，下游状态变化后进行 MQ 主动通知，所以我这一坨骚代码也就随之光荣下岗。

我现在想了一下，其实这个场景，用分页的思想去取数据真的不好做。

还不如用最开始的分批次的思想，只不过在会变化的“状态”之外，再加上另外一个不会改变的限定条件，比如常见的创建时间：

select * from order where order_status=0 and create_time>xxx order by create_time limit 100;

最好不要基于状态去做分页，如果一定要基于状态去做分页，那么要确保状态在分页逻辑里面会流转下去。

二、分页数据在不同页反复出现的坑

关于这个 BUG 可以说是印象深刻了。当年遇到这个坑的时候排查了很长时间没啥头绪，最后还是组里的大佬指了条路。

业务需求很简单，就是在管理页面上可以查询订单列表，查询结果按照订单的创建时间倒序排序。

对应的分页 SQL 很简单，很常规，没有任何问题：

select * from table order by create_time desc limit 0,10;

但是当年在页面上的表现大概是这样的：

订单编号为 5 的这条数据，会同时出现在了第一页和第二页。

甚至有的数据在第二页出现了之后，在第五页又出现一次。

后来定位到产生这个问题的原因是因为有一批数量不小的订单数据是通过线下执行 SQL 的方式导入的。

而导入的这一批数据，写 SQL 的同学为了方便，就把 create_time 都设置为了同一个值，比如都设置为了 2024-01-21 21:13:56 这个时间。

由于create_time又是我作为 order by 的字段，当这个字段的值大量都是同一个值的时候，就会导致上面的一条数据在不同的页面上多次出现的情况。

针对这个现象，当时组里的大佬分析明白之后，扔给我一个链接：

https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

这是 MySQL 官方文档，这一章节叫做“对Limit 查询的优化”。

开篇的时候人家就是这样说的：

如果将 LIMIT row_count 和 ORDER BY 组合在一起，那么 MySQL 在找到排序结果的第一行 count 行时就停止排序，而不是对整个结果进行排序。

然后给了这一段补充说明：

如果多条记录的 ORDER BY 列中有相同的值，服务器可以自由地按任何顺序返回这些记录，并可能根据整体执行计划的不同而采取不同的方式。

换句话说，相对于未排序列，这些记录的排序顺序是 nondeterministic 的：

然后官方给了一个示例。

首先，不带 limit 的时候查询结果是这样的：

基于这个结果，如果我要取前五条数据，对应的 id 应该是 1,5,3,4,6。

但是当我们带着 limit 的时候查询结果可能是这样的：

对应的 id 实际是 1,5,4,3,6。

这就是前面说的：如果多条记录的 ORDER BY 列中有相同的值，服务器可以自由地按任何顺序返回这些记录，并可能根据整体执行计划的不同而采取不同的方式。

从程序上的表现上来看，结果就是 nondeterministic。

所以看到这里，我们大概可以知道我前面遇到的分页问题的原因是因为那一批手动插入的数据对应的 create_time 字段都是一样的，而 MySQL 这边又对 Limit 参数做了优化，运行结果出现了不确定性，从而页面上出现了重复的数据。

而回到文章最开始的这个 SQL，也就是我一眼看出问题的这个 SQL：

select * from table order by priority limit 1;

因为在我们的界面上，只是约定了数字越小优先级越高，数字必须大于 0。

所以当大家在输入优先级的时候，大部分情况下都默认自己编辑的数据对应的优先级最高，也就是设置为 1，从而导致数据库里面有大量的优先级为 1 的数据。

而程序每次处理，又只会按照优先级排序只会，取一条数据出来进行处理。

经过前面的分析我们可以知道，这样取出来的数据，不一定每次都一样。

所以由于有这段代码的存在，导致业务上的表现就很奇怪，明明是一模一样的请求参数，但是最终返回的结果可能不相同。

好，现在，我问你，你说在前面，我给出的这样的分页查询的 SQL 语句有没有毛病？

select * from table order by create_time desc limit 0,10;

看着没有任何毛病嘛，但是执行结果也没有任何毛病吗？

• 有没有给你按照 create_time 排序？摸着良心说，是有的。

• 有没有给你取出排序后的 10 条数据？也是有的。

所以，针对这种现象，官方的态度是：我没错！在我的概念里面，没有“分页”这样的玩意，你通过组合我提供的功能，搞出了“分页”这种业务场景，现在业务场景出问题了，你反过来说我底层有问题？

这不是欺负老实人吗？我没错！

但实际从我们的业务诉求看是有毛病的，多次请求出现了不一样的结果

所以，官方把这两种案例都拿出来，并且强调：在每种情况下，查询结果都是按 ORDER BY 的列进行排序的，这样的结果是符合 SQL 标准的。

虽然我没错，但是我还是可以给你指个路。

如果你非常在意执行结果的顺序，那么在 ORDER BY 子句中包含一个额外的列，以确保顺序具有确定性。

例如，如果 id 值是唯一的，你可以通过这样的排序使给定类别值的行按 id 顺序出现。

你这样去写，排序的时候加个 id 字段，就稳了：