wordpress企业 破解主题下载地址,百度关键词优化费用,html网站设计模板,建造网站过程文章目录 前言一、一次分页查询的演进二、分页数据在不同页反复出现的坑 前言
上一篇文章介绍了分页原理与优化#xff1a;73.MySQL 分页原理与优化#xff08;上#xff09;
但分页还有一个“坑”需要注意#xff0c;本文细细道来#xff0c;可能很多朋友都踩过这个坑还… 文章目录 前言一、一次分页查询的演进二、分页数据在不同页反复出现的坑 前言
上一篇文章介绍了分页原理与优化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_status0;
initOrderInfoList : queryInitOrderInfoList()
//循环处理这批数据
for _, orderInfo : range initOrderInfoList {//发起rpc调用orderStatus : queryOrderStatus(orderInfo.getOrderId)//更新订单状态updateOrderInfo(orderInfo.getOrderId,orderStatus)
}来你说上面这个程序有什么问题
其实在绝大部分情况下都没啥大问题数据量不多的情况下程序跑起来没有任何毛病。
但是如果数据量多起来了一次性把所有初始化状态的订单都拿出来是不是有点不合理了万一把内存给你撑爆了怎么办
所以在我已知数据量会很大的情况下我采取了分批次获取数据的模式假设一次性取 100 条数据出来玩。
那么 SQL 就是这样的
select * from order where order_status0 order by create_time limit 100;所以上面的伪代码会变成这样
for {//获取订单状态为初始化的数据(0:初始化 1:处理中 2:成功 3:失败)//select * from order where order_status0 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{pageNumpageNum1;//获取订单状态为初始化的数据(0:初始化 1:处理中 2:成功 3:失败)//select * from order where order_status0 order by create_time limit pageNum*100,100;// 注意这里queryInitOrderInfoList方法中是会根据传入的pageNum,100pageNumsize进行分页的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_status0 的数据。那么第一页取出了 100 条数据
SELECT * from order_info WHERE order_status0 LIMIT 0,100;这 100 条处理完成之后第二页还有数据吗
第二页对应的 sql 为
SELECT * from order_info WHERE order_status0 LIMIT 100,100;但是这个时候状态为 0 的数据只有 20 条了而分页要从第 100 条开始是不是获取不到数据导致遗漏数据了
确实一定会翻到最后一页解决了死循环的问题但又有大量的数据遗漏怎么办呢
当时我苦思冥想想到一个办法导致数据遗漏的原因是因为我在翻页的时候数据状态在变化导致总体数据在变化。
那么如果我每次都从后往前取数据每次都固定取最后一页能取到数据就代表还有数据要处理循环结束条件修改为“当前页即是第一页也是最后一页时”就结束这样不就不会遗漏数据了
我再给你分析一下。
假设我们有 120 条order_status0的数据从后往前取了 100 条出来进行处理有 90 条处理成功10 条的状态还是停留在“处理中”。
第二次再取的时候会把剩下的 20 条和这次“处理中”的 10 条共计 30 条再次取出来进行处理。确保没有数据遗漏。
后来测试环节验收通过了这个方案上线之后也确实没有遗漏过数据了。
直到后来又一天提供 queryOrderStatus 接口的服务异常了我发过去的请求超时了。
导致我取出来的数据每一条都会报错都不会更新状态。从而导致我每次从后往前取数据都取到的是同一批数据。
从程序上的表现上看日志疯狂的打印但是其实一直在处理同一批就是死循环了。
最后随着业务的发展这块逻辑也完全发生了变化逻辑由我们主动去调用 RPC 接口查询状态变成了下游状态变化后进行 MQ 主动通知所以我这一坨骚代码也就随之光荣下岗。
我现在想了一下其实这个场景用分页的思想去取数据真的不好做。
还不如用最开始的分批次的思想只不过在会变化的“状态”之外再加上另外一个不会改变的限定条件比如常见的创建时间
select * from order where order_status0 and create_timexxx 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 字段就稳了
