对于MySQL排序的特性不了解，所以只能加班到午夜十二点了，真是认命吧！

本文转载自微信公众号「程序新视界」，作者是二师兄。请联系程序新视界公众号获取转载本文的许可。

弟弟最近开发了一个新功能，经过自测和测试环境测试都没有问题，但在生产环境会偶尔出现问题。于是，我持续加班直到午夜12点，不断排查问题，最终成功定位到问题的原因：Mysql Limit查询优化导致的。现在我们把问题抽象成一个模型，并提出解决方案，分享给大家进行分析，以免大家踩坑。一个新的交易记录导出功能在

问题场景

上线了，它的逻辑很简单：根据查询条件，导出相应的数据。为了应对大数据量，在数据库查询过程中使用了分页查询，每次查询1000条数据。

的自我检查结果正常，测试环境也正常，但上线后，经营团队反馈出现了重复的数据记录。原本以为是业务逻辑问题，我重新审查了一次代码，但仍然找不到问题的原因。最终只能将SQL查询语句单独执行，导出数据后进行对比，结果表明是由于SQL查询结果的顺序混乱所导致的。

查询语句按照create_time字段以倒序排序，然后使用limit进行分页。在正常情况下不会有问题。这是

的原因。在业务并发量较大的情况下，由于create_time存在大量相同的数值，当基于limit进行分页时，可能会出现乱序问题。当以create_time排序时，如果create_time存在相同值，通过limit分页会导致分页数据混乱。举例来说，假设要查询1000条数据，其中有一批create_time记录的数值都是"2021-10-28 12:12:12"。当这些数据有相同的创建时间，一部分出现在第一页，而另一部分出现在第二页。在查询第二页的数据时，可能会出现已经在第一页查过的数据。这意味着数据会在页面之间来回跳动，有时出现在第一页，有时出现在第二页，导致导出的数据出现部分重复，部分缺失。

在阅读了Mysql 5.7和8.0的官方文档之后，得出如下描述：

如果在ORDER BY列中存在多行具有相同的值，则服务器可以以任何顺序返回这些行，并且可能根据整体执行计划来进行不同的返回顺序。换句话说，当使用ORDER BY对列进行排序时，如果存在多行相同数据的列，那么这些行的排序顺序是不确定的，并且可能根据整体执行计划以不同的方式返回。在简单的术语中，

意味着：如果ORDER BY检索到多行具有相同值的数据，MySQL会随机返回其中的一行。简要而言，当使用ORDER BY查询数据时，若ORDER BY列存在多个相同的数据行，Mysql可能会随机返回。这可能导致即便进行了排序，结果仍可能出现无序的情况。针对上述问题，基本的解决思路是：避免出现重复的ORDER BY列的值。因此，可以考虑添加其他方面，例如ID或其他排序字段。

选择 * 从 tb_order 按照 create_time ，id 降序排列; 

这样，当create_time相同时，会根据id进行排序，而id肯定是不同的，就不会再出现上述问题了。

其实，上述信息已在Mysql官网上得到明确阐述，并且还提供了示例。请对官方网站上的内容和示例进行简洁的总结。

limit查询的优化

当我们只需要查询结果集的部分数据时，不应该先查询所有数据，然后再丢弃不需要的数据，而是应该通过limit条件进行限制。在没有使用"having"条件时，Mysql可能会对"limit"条件进行优化：如果只查询少量数据，建议使用"limit"，这样Mysql可能会利用索引，而通常情况下Mysql会进行全表扫描；如果结合使用"limit row_count"和"order by"，Mysql会在找到第一个row_count结果集后立即停止排序，而不是对整个结果集进行排序。如果现在使用索引来进行操作，会更加迅速。需要对文件进行排序时，会在找到row_count结果集之前，对部分或全部符合条件的结果进行排序。但一旦找到row_count的结果，剩余部分就不会被排序。这种特性的一种表现是，当我们进行带有limit和不带limit的查询时，返回结果的顺序可能会有所不同。如果将limit row_count和distinct结合使用，Mysql会在找到row_count结果集唯一行后立即停止。在一些情况下，可以根据索引的顺序进行读取或排序，然后计算摘要直到索引发生变化，以实现分组。在这种情况下，只有必要的group by值才会计算limit row_count。一旦MySQL将所需数量的行发送给客户端，查询就会中止，除非使用了SQL_CALC_FOUND_ROWS。可以使用 SELECT FOUND_ROWS() 来获取行数，这在这种情况下是合适的。当LIMIT设置为0时，会快速返回一个空集合，通常用于检查SQL语句的有效性。可以用于获取应用程序中结果集的类型。在MySQL客户端中，可以使用--column-type-info选项来显示结果列的类型。当使用临时表来解析查询时，Mysql会使用limit row_count来计算需要多少空间。当没有使用索引进行order by操作，并且存在limit条件时，优化器可能会避免使用合并文件的方式，而会选择在内存中对行进行排序，采用内存filesort操作。了解了limit的一些特性，接下来再回到本文的重点，讨论limit与order by结合使用的特性。在上面的第二条中已经提到，将limit和order by结合使用的一个特点是返回结果的顺序是不确定的。执行计划的一个影响因素是限制条件，因此含有限制条件和不含限制条件的查询语句执行同样的操作时，返回结果的顺序可能会有所不同。

下面的例子中，根据category列进行了排序查询。id和rating的值是不确定的：

mysql> SELECT * FROM ratings ORDER BY category; +----+--++ | id | category | rating | +----+--++ |  1 |        1 |    4.5 | |  5 |        1 |    3.2 | |  3 |        2 |    3.7 | |  4 |        2 |    3.5 | |  6 |        2 |    3.5 | |  2 |        3 |    5.0 | |  7 |        3 |    2.7 | +----+--++ 

当查询语句包含limit时，可能会影响到具有相同category值的数据：

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5; +----+--++ | id | category | rating | +----+--++ |  1 |        1 |    4.5 | |  5 |        1 |    3.2 | |  4 |        2 |    3.5 | |  3 |        2 |    3.7 | |  6 |        2 |    3.5 | +----+--++ 

其中id为3和4的结果位置发生了变化。在实际操作中，保持查询结果的顺序通常非常关键，这时就需要引入其他列来确保结果的顺序性。上面的例子加入id后，查询语句和结果如下：

mysql> SELECT * FROM ratings ORDER BY category,id | category | rating\n+----+--+\n1 | 1 | 4.5\n5 | 1 | 3.2\n3 | 2 | 3.7\n4 | 2 | 3.5\n6 | 2 | 3.5\n2 | 3 | 5.0\n7 | 3 | 2.7\nmysql> SELECT * FROM ratings ORDER BY category, 选择 id 时， 限制 5条记录； +----+--++ | id | 类别 | 评分 |表格中的数据显示了不同项目的数值。很明显，项目1的数值较高，达到了4.5。而项目2和项目6的数值都是3.5，这两个项目的数值是相同的。 即使category相同，添加了id列的排序之后也不会出现乱序问题。这与我们最初的解决方案一致。这与我们最初的解决方案完全一致。在实践中遇到了一个问题，讨论了Mysql对limit查询语句的优化，并提供了解决方案，以满足业务需求并避免业务逻辑错误。许多朋友在查询时都会使用order by和limit语句，但是如果不了解Mysql的优化特性，很可能已经掉入陷阱，只是因为数据量还不足以显现出问题。如果这篇文章对你有帮助，请关注一下，接下来会分享更多实用的经验和知识。请访问Mysql官方文档链接: https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html