记一次MySQL/MariaDB的limit使用陷阱

发布于 2021-09-06  24 次阅读


前言

有这么一个场景,需要对数据进行分页,然后数据使用了伪删除,所以带了个is_deleted字段,并且与原本计划的唯一字段组合成了唯一索引。(大噶就是这样,具体触发的前提条件是不是完全写到了我也不清楚,因为网上关于limit陷阱都说法不太一样,这玩意内部优化比较诡异)

相关数据

file

其中branch是一个普通业务字段,不存在任何相关索引。

预期的查询及其实际结果

select `id`, `branch` from `repo` where `is_deleted` = 0 limit 1 offset 0;
select `id`, `branch` from `repo` where `is_deleted` = 0 limit 1 offset 1;
select `id`, `branch` from `repo` where `is_deleted` = 0 limit 1 offset 2;
select `id`, `branch` from `repo` where `is_deleted` = 0 limit 1 offset 3;

预期的情况应该是出了四个不同的页面内容,但是很遗憾,查出来的数据十分奇怪。

file

file

file

file

可以看到,不仅仅是顺序并非默认按照id排序,连第二页和第四页的数据都重复了,这就出大问题了。

尝试定位原因

尝试扩大limit页面容量。

select `id`, `branch` from `repo` where `is_deleted` = 0 limit 1;
select `id`, `branch` from `repo` where `is_deleted` = 0 limit 2;
select `id`, `branch` from `repo` where `is_deleted` = 0 limit 3;
select `id`, `branch` from `repo` where `is_deleted` = 0 limit 4;

file

file

file

file

可以发现,不同页面容量的最后一条数据就是之前页面容量为1时的每页数据,且不同页面容量的查询返回的数据顺序并不一样。

根据数据内容猜测是limit 4因为已经和实际数据数量一致了,所以被优化为不做处理,直接返回了不分页的结果。

突发奇想尝试去掉branch

select `id` from `repo` where `is_deleted` = 0 limit 1;
select `id` from `repo` where `is_deleted` = 0 limit 2;
select `id` from `repo` where `is_deleted` = 0 limit 3;
select `id` from `repo` where `is_deleted` = 0 limit 4;

file

file

file

file

虽然依旧没有按id排序,但是至少有一个未知但固定的顺序了(也可能是我这边数据量不足导致的偶然性情况,所以只是猜测和select的字段也有关系)

解决办法

无论什么情况下使用limit进行分页,都应当显式地进行order by排序,而不是潜意识地认为会默认按照主键排序或者存在固定顺序。

另,根据网上其他相关资料显示,limitorder by一起使用时,若order by的字段并不唯一,则对于那些字段内容重复的记录,返回的顺序可能会有随机波动,所以limit时的order by的应当包含唯一索引或者主键进行顺序兜底,防止出现意外情况。


有文章指出,MySQL中的limit n offset x查询实际上是会被执行为limit x+n,然后返回最后的n条。所以果然是这个问题嘛...


The End


什么都会,但又什么都不会。