Order by limit分页出现数据重复问题

问题现象

对一个表执行排序，并对排序结果进行分页，得到的结果不符合预期。

假设有一个名为商品(merchants)的表，只有一个商品id和商品种类category两个字段，表结构如下：

mysql> show create table merchants;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                            |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| merchants | CREATE TABLE `merchants` (
  `id` int NOT NULL AUTO_INCREMENT,
  `category` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

执行如下SQL，查看表中的内容。

mysql>  select * from merchants;
+----+----------+
| id | category |
+----+----------+
|  1 |        1 |
|  2 |        3 |
|  3 |        2 |
|  4 |        2 |
|  5 |        1 |
|  6 |        2 |
|  7 |        3 |
|  8 |        3 |
|  9 |        2 |
| 10 |        1 |
+----+----------+
10 rows in set (0.00 sec)

执行如下SQL，对商品按照类别(category)字段进行排序。

mysql> select * from merchants order by category;
+----+----------+
| id | category |
+----+----------+
|  1 |        1 |
|  5 |        1 |
| 10 |        1 |
|  3 |        2 |
|  4 |        2 |
|  6 |        2 |
|  9 |        2 |
|  2 |        3 |
|  7 |        3 |
|  8 |        3 |
+----+----------+
10 rows in set (0.00 sec)

执行如下SQL，将排序结果用limit分页，每页两行数据。

mysql> select * from merchants order by category limit 0,2;
+----+----------+
| id | category |
+----+----------+
|  1 |        1 |
|  5 |        1 |
+----+----------+
2 rows in set (0.00 sec)
 
mysql> select * from merchants order by category limit 2,2;
+----+----------+
| id | category |
+----+----------+
|  1 |        1 |
|  9 |        2 |
+----+----------+
2 rows in set (0.00 sec)

可以看到，第二页的数据出现了错误。按照没有分页时的排法，第二页应该显示为id为10和id为3的行，但实际结果这里是id为1和9的行。

原因分析

优化器在遇到order by limit语句的时候，做了一个优化，内部使用priority queue结构做排序，该排序方式属于不稳定排序算法，筛选出limit n的结果后就直接返回，不能保证有序性。

解决方案

方案1：可以在需要排序的字段上加上索引。如案例中，alter table ratings add index idx_category (category);
方案2：可以在排序语句的order by后面加入主键列。如案例中，select * from ratings order by category, id limit 2,2;
方案3：可以在GaussDB(for MySQL)控制台参数修改页面，开启参数“rds_force_stable_sort”。该参数开启后，将强制使用稳定排序算法，确保排序结果的稳定。
图1 设置参数rds_force_stable_sort