从根上理解order by limit分页数据重复问题

作者：业余草

来源：https://www.xttblog.com/?p=4631

这个问题相信不少人都遇到过，但很少有人追根溯源。 今天我抽出一点时间，来讨论讨论 order by + limit 在什么情况下会出现分页数据重复。

昨天在“CTO”群提问了之后，不少大神都回答的相当正确。 我这里结合一下他们的回答，并补充一些内容分享给大家！

排序离不开算法，在关系型数据库中，往往会存在多种排序算法。 通过 MySQL 的源码和官方文档介绍可以得知，它的排序规律可以总结如下：

1. 当 order by 不能使用索引进行排序时，将使用排序算法进行排序；

2. 若排序内容能全部放入内存，则仅在内存中使用快速排序；

3. 若排序内容不能全部放入内存，则分批次将排好序的内容放入文件，然后将多个文件进行归并排序；

4. 若排序中包含 limit 语句，则使用堆排序优化排序过程。

其他如： PG，MariaDB，AliSQL，SQL Server 等排序算法方面差别不大。

根据上面的总结，当你的 order by limit 分页出现数据重复。 比如，一个用户表，当使用 limit 5 后出现一个张三。 再使用 limit 5,10 的时候，张三又出现了。 注意，这两个张三是同一个人，id 是相同的。 在这种情况下，你的 order by 肯定是没有使用索引的。 因为使用了索引，就会进行索引排序。

根据官方文档显示，以及我上面的总结，可以得出。 上面的 SQL 使用了堆排序。 因为，category 没索引，所以没走索引排序； 其二我们使用了 limit，所以最终使用了堆排序。 而了解算法的朋友都知道，堆排序是不稳定的。

比如，我们现在有下面一列数。

为了，看清两个 2 的区别，我分别给它们多标了一个数字。

那么使用堆排序的结果，可能会存在下面的结果。

这种不稳定性，指的就是多次排序后，各个数的相对位置发生了变化。

除了堆排序，不稳定的排序还有下面这些排序算法。

那么如何解决 order by limit 分页数据重复问题呢？ 方法有多种，我这里列举最常用的两种方法。

第一种就是，在排序中加上唯一值，比如主键 id，这样由于 id 是唯一的，就能确保参与排序的 key 值不相同。

第二种就是避免使用堆排序，让 order by 根据索引来排序。 说白了，就是 order by 后面的字段要有索引。

以上，知道的越多，不知道的就越多，业余的就像一棵小草一样！

想学习更多的 MySQL 底层知识，建议阅读《高性能MySQL》一书。

参考资料：

• https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

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

• https://en.wikipedia.org/wiki/Sorting_algorithm

扫码求关注

给我好看

您看此文用

·

秒，转发只需1秒呦~

好看你就

点点

我