MySQL在使用order by + limit子句时存在重复数据的陷阱

在对热门数据进行排序时，细心负责的质量控制发现了这样一个问题——排序后的分页结果中存在重复数据。我很疑惑，仔细检查了代码也没有发现什么问题，但是偏偏就出现了这个现象。通过分析这个现象并查看数据，我终于找到了事件的原因。这是一个比较常见的通病。项目之前的所有排序查询表达式都已包含在内，我们已将它们一起编辑。

1。重现问题

这里我使用RDS重现问题。经过测试，在DDB中也出现了同样的现象。

先按顺序运行查询，限制40条。结果是排好序的前40条数据，所以不用仔细看。

然后使用订单raja20 完成相同的调查。结果是前20个排序的数据，与40个民意调查的前20个项进行相比，发现不一致。注意红框中的几条信息。

最后，在20.20之前完成与订单相同的调查。结果是排序的第 21-40 个。注意，红框中的数据项是前20条数据的副本！

2。问题分析

分析上面的数据，不难看出重叠的数据点有一个属性，它们的排序值是相同的。例如，在上面的数据项中，标识符16923、16925和16872对应的排序值都是1。也就是说，根据查询，边界处的顺序仅保证不同排序的结果集值是肯定有序的，并且不保证具有相同排序值的结果的顺序。推测MySQL对顺序进行了极限优化。 limit n[, m] 不需要返回所有数据，只返回前n项或者前n+m项。如果我们自己解决这个问题，我们会怎么做？回顾排序算法，唯一适合大数据开头n的算法就是堆排序。mysql是否也使用类似的优化方法？ ？ BY，但也存在 LIMIT 子句，优化器也许能够避免使用合并文件并使用内存中文件排序功能对内存中行进行排序。更多信息，请参见内存文件排序算法。

在order ORDER 中，如果ORDER BY 在查询语句BY + LIMIT 中无法使用索引，优化器可以使用内存排序功能。有关详细信息，请参阅内存中文件排序算法。

下面是一个例子。这个例子和我们遇到的现象一模一样。另外给出了解决方案——按顺序定义一个辅助排序字段。该字段是绝对有序的，保证了整个排序结果的顺序。

4。解决方案

如上所述，在order定义的排序字段后面添加一个辅助排序字段，以保证顺序。这样问题就解决了，这里不再发表结果，而是占用空间。有兴趣的读者可以自行验证一下。

5。深度学习

采用上面的方案，问题解决了，很开心。但你可能还有疑问MySQL是如何优化这条语句的，是否使用了堆排序算法？我们看一下解释性的说法。结果如下：

我们只看到使用了文件排序，但在解释结果中看不到使用的排序算法。

继续查资料，阅读上面提到的The In-Memory filesort Algorithm的官方文档。您知道 MySQL 文件排序有三种优化算法，分别是：

• 基本文件排序
• 改进的文件排序
• 内存文件排序

本页介绍了三种算法。建议花10分钟阅读。您还可以阅读这篇博客了解MySQL排序的内部原理。

官方文档注释：排序缓冲区的大小为sort_buffer_size。如果 N 行排序元素足够小，可以放入排序缓冲区（如果指定了 M，则为 M+N 行），服务器可以避免使用合并文件，并通过将排序缓冲区视为优先级来执行内存排序。 也就是说，内存文件排序使用的是优先级队列，而优先级队列的原理是二元的。

检查实际查询中是否使用了优先级队列。你怎么认为？官方文档也给出了方法：

Optimizer Tracer 很强大，但是默认没有开启：

SHOW VARIABLES LIKE '%trace%';

我们手动开启：

SET optimizer_trace = "enabled=on";

然后运行查询。查询完成后，查看跟踪器：

SELECT * FROM information_schema.optimizer_trace;

Optimizer Tracer 使用 Blob 字段以 json 格式存储优化记录。可以看到，确实使用了优先级队列。

6。后记

解决问题很容易，但发现问题、找出问题本质却需要精力。首先，我们要感谢我们的QA同学为我们寻找bug的无私帮助。其次，开发效率与深入研究问题在时间上是相互冲突的，必须权衡。一天晚上，QA 通知我们这个问题，它出现在测试环境中。那天晚上我们想出了一个解决方案，添加一个绝对排序列，并在第二天解决了这个问题。