MySQL 中 order by .. limit .. 查询结果中相同数据在不同页中重复出现

MySQL 版本

MySQL 5.7.28 和 8.0.20 这两个版本都出现了这个问题。

相关的表和数据

建表语句：

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入数据：

insert  into `test`(`id`,`name`,`score`) values (1,'Aaron',78),(2,'Bill',76),(3,'Cindy',76),(4,'Damon',76),(5,'Ella',76),(6,'Frado',76),(7,'Gill',99),(8,'Hellen',76),(9,'Ivan',93),(10,'Jay',76);

表中的数据：

问题描述

只使用order by, 不使用limit时候，查询的语句：

SELECT * FROM test
ORDER BY score ASC

查询结果：

下面我们按照 score 升序，每页3条数据，来获取数据。
下面的sql依次为第1页、第2页、第3页、第4页、第5页的数据，如下：

查询第1页的语句：

SELECT * FROM test
ORDER BY score ASC
LIMIT 0,3

第1页结果：

查询第2页的语句：

SELECT * FROM test
ORDER BY score ASC
LIMIT 3,3

第2页结果：

查询第3页的语句：

SELECT * FROM test
ORDER BY score ASC
LIMIT 6,3

第3页结果：

查询第4页的语句：

SELECT * FROM test
ORDER BY score ASC
LIMIT 9,3

第4页结果：

可以发现，第1页和第4页都出现了id为10的这条数据，而id为8的这条数据不见了。这跟我们的预期是不一致的。

为什么会出现这个问题呢？

解决方法

MySQL 出于效率考虑，order by col1, col2 语句用于排序时，并不负责关心 col3, col4 … 的值。也就是同样的 col1, col2 的行数据，在limit row_count 的结果集中不负责 col3, col4 … 的排序（且返回值不确定），这是一个很自然的事情。侧面也反映了MySQL 会使用不稳定的排序算法。

在上述场景中，score字段存在大量相同的值。当排序过程中存在相同的值且没有其他排序规则时，mysql 返回的结果是不确定的，可能每次返回的结果的顺序都不一样（如果发现每次执行都返回一样的数据，可能是被mysql查询缓存了）。

解决方法：排序中存在相同的值时，需要再指定一个排序规则，通过这种排序规则不存在二义性，比如上面可以再加上id升序。

SELECT * FROM test
ORDER BY score,id ASC
LIMIT 0,3

再加上一个没有二义性的排序规则后，就不会出现上面这种问题了。

原理分析

本节内容来源于官网文档 limit 优化 的翻译。

原文：

If you combine LIMIT row_count with ORDER BY, MySQL stops sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set.

One manifestation of this behavior is that an ORDER BY query with and without LIMIT may return rows in different order, as described later in this section.

如果将 LIMIT row_count 与 ORDER BY 结合使用，MySQL 会在找到排序结果的前 row_count 行后立即停止排序，而不是对整个结果进行排序。如果使用索引进行排序，速度会非常快。如果必须进行文件排序，则在找到第一个 row_count 之前，将选择所有与不带 LIMIT 子句的查询匹配的行，并对其中的大部分或全部进行排序。找到初始行后，MySQL 不会对结果集中的任何剩余部分进行排序。

这种行为的一种表现是带有和不带有 LIMIT 的 ORDER BY 查询可能会以不同的顺序返回行，如本节后面所述。

原文：

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.

One factor that affects the execution plan is LIMIT, so an ORDER BY query with and without LIMIT may return rows in different orders. Consider this query, which is sorted by the category column but nondeterministic with respect to the id and rating columns:

如果多个行在 ORDER BY 列中具有相同的值，服务器可以自由地以任何顺序返回这些行，并且可能会根据整体执行计划以不同的方式返回。换句话说，这些行的排序顺序相对于未排序的列是不确定的。

影响执行计划的一个因素是 LIMIT，因此带有和不带有 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 可能会影响每个类别值中的行顺序。例如，这是一个有效的查询结果：

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 |
+----+----------+--------+

在每种情况下，行都按 ORDER BY 列排序，这是 SQL 标准所要求的全部。

如果使用和不使用 LIMIT 确保相同的行顺序很重要，请在 ORDER BY 子句中包含其他列以使顺序具有确定性。例如，如果 id 值是唯一的，您可以通过如下排序使给定类别值的行按 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 |
+----+----------+--------+

参考文章

Mysql排序和分页(order by&limit)及存在的坑_Mysql_脚本之家

【MySQL】官网学习 order by limit 中的乱序问题_Ch.yang的博客-CSDN博客

MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.19 LIMIT Query Optimization

MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.16 ORDER BY Optimization