关于mysql:不懂Mysql排序的特性加班到12点认了认了

小弟新写了一个性能，自测和测试环境测试都没问题，但在生产环境会呈现偶发问题。于是，加班到 12 点始终排查问题，终于定位了的问题起因：Mysql Limit 查问优化导致。现形象出问题模型及解决方案，剖析给大家，防止大家踩坑。

新上线一个交易记录导出性能，逻辑很简略：依据查问条件，导出对应的数据。因为数据量比拟大，在查询数据库时采纳了分页查问，每次查问 1000 条数据。

自测失常，测试环境失常，上线之后经营反馈导出的 数据有重复记录。

本来是认为业务逻辑问题，从新 Review 了一遍代码，仍旧未找到问题起因。最初只好把 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 的官网文档，形容如下：

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

上述内容概述：在应用 ORDER BY 对列进行排序时，如果对应（ORDER BY 的列）列存在多行雷同数据，（Mysql）服务器会依照任意程序返回这些行，并且可能会依据整体执行打算以不同的形式返回。

简略来说就是：ORDER BY 查问的数据，如果 ORDER BY 列存在多行雷同数据，Mysql 会随机返回。这就会导致尽管应用了排序，但也会产生乱序的情况。

针对上述问题，根本的解决思路是：防止 ORDER BY 列的值呈现反复。因而，能够退出其余维度，比方 ID 等其余排序列。

select * from tb_order order by create_time ,id desc;

这样，在 create_time 雷同时，会依据 id 进行排序，而 id 必定是不同的，就再不会呈现上述问题了。

其实，上述内容在 Mysql 的官网曾经有明确阐明，而且还举了例子。上面对官网的内容和例子做一个简略的汇总总结。

如果咱们只是查问一个后果集的一部分，那么不要查问所有数据，而后再抛弃不须要的数据，而是要通过 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 不会计算任何不必要的 group by 值。
一旦 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 row_count和 order by 联合应用个性。

在下面第二条中曾经提到，limit row_count和 order by 联合出现的个性之一就是后果返回的程序是不确定的。而影响执行打算的一个因素就是 limit，因而带有limit 与不带有 limit 执行同样的查问语句，返回后果的程序可能不同。

上面示例中，依据 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;
+----+----------+--------+
| 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 |
+----+----------+--------+

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

能够看出，当增加了 id 列的排序，即便 category 雷同，也不会呈现乱序问题。这正与咱们最后的解决方案统一。

原本通过实际中偶发的一个坑，聊到了 Mysql 对 limit 查问语句的优化，同时提供了解决方案，即满足了业务需要，又防止了业务逻辑的谬误。

很多敌人都在应用 order by 和 limit 语句进行查问，但如果不晓得 Mysql 的这些优化个性，很可能曾经入坑，只不过数据量没有触发出现而已。

如果这篇文章帮到你了，关注一波，后续更多实战干货分享。

Mysql 官网文档：https://dev.mysql.com/doc/ref…

博主简介：《SpringBoot 技术底细》技术图书作者，热爱钻研技术，写技术干货文章。

公众号：「程序新视界」，博主的公众号，欢送关注~

技术交换：请分割博主微信号：zhuan2quan

关于mysql:不懂Mysql排序的特性加班到12点认了认了

问题场景

起因剖析

解决方案

拓展常识

limit 查问优化

limit 与 order by 联合应用

小结