问题现象

在MySQL中使用limit分页查询时，有时候第一页的数据在第二页中又会出现，而有部分数据又会被遗漏。

举个例子

我们先构建一个表

1
2
3
4
5
6
7
8

CREATE TABLE IF NOT EXISTS `test` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `a` int NOT NULL,
  `b` int NOT NULL,
  `c` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

其中字段id为主键，字段b有一个普通索引。

然后插入一些数据，数据需要多一些，数据量过少时，无法复现。

表数据如下：

接着我们执行分页sql

1
2

select * from test order by c desc limit 0,10;
select * from test order by c desc limit 10,10;

可以看到第一句SQL，查询第一页的数据，查出了id为127的数据

第二句SQL，查询第二页的数据，查出了id为127的数据

同一行数据，怎么可能又在第一页，又在第二页呢？

接着我们在使用索引列b执行一下分页查询

1
2

select * from test order by b desc limit 0,10;
select * from test order by b desc limit 10,10;

同样第一页和第二页都查到了id为199的数据，可见使用索引排序并不能解决问题。

问题原因

具体原因可以查看MySQL官网对limit的详细说明及优化建议

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.

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

If it is important to ensure the same row order with and without LIMIT, include additional columns in the ORDER BY clause to make the order deterministic. For example, if id values are unique, you can make rows for a given category value appear in id order by sorting like this:

如果确保在使用和不使用LIMIT时行顺序相同很重要，那么在ORDER BY子句中包含其他列，以使顺序具有确定性。例如，如果id值是唯一的，你可以通过如下排序，使给定category值的行按id顺序显示

解决方案

根据MySQL官网说明的内容，我们只需要在排序字段中加入一个不重复的值，例如id，就可以避免这个问题

例如

1
2

select * from test order by c desc,id desc limit 0,10;
select * from test order by c desc,id desc limit 10,10;

这样就可以解决排序不稳定的问题了。