mysql分页查询遇到order by发生的血案

案发现场

业务需求：mysql从一张表中查询数据进行分页展示，要求按照某字段进行排序。
做开发的同学都觉得这兼职就是小case啦，前端传入分页下标、分页大小、排序字段、排序方式。后端采用order by +limit就搞定了。
事实上我也是这么做的，但是最后却出了个出乎意料的bug，同样的请求参数，返回的数据顺序居然不一样，分页的时候，第一页的数据居然重复出现在第二页。并且bug不是必然复现，重复数据每次一样。

场景复现

1. 准备一张表,并插入几条数据：

CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `str1` varchar(16) DEFAULT NULL,
  `str2` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('1', 'str1:100010736', '0-0-1');
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('2', 'str1:100003251', '0-0-0');
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('3', 'str1:100016926', '0-0-1');
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('4', 'str1:100009643', '0-0-1');
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('5', 'str1:100005431', '0-0-1');
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('6', 'str1:100010316', '0-0-1');
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('7', 'str1:100001498', '0-0-1');
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('8', 'str1:100009610', '0-0-1');
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('9', 'str1:100013279', '0-0-1');
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('10', 'str1:100017718', '0-0-1');
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('11', 'str1:100008208', '0-0-1');
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('12', 'str1:100008364', '0-0-1');
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('13', 'str1:100012102', '0-0-0');
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('14', 'str1:100006096', '0-0-0');
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('15', 'str1:100019269', '0-0-0');
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('16', 'str1:100006232', '0-0-1');
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('17', 'str1:100005827', '0-0-1');
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('18', 'str1:100013695', '0-0-1');
INSERT INTO `t1` (`id`, `str1`, `str2`) VALUES ('19', 'str1:100006787', '0-0-0');

2, 执行分页查询

select id,str1,str2 from t1 order by str2 limit 0,10;
select id,str1,str2 from t1 order by str2 limit 10,10;

注意看：查询sql是标标准的是分页查询sql，仅仅是加了一个排序，第一页的数据重复出现在第二页。

使用测试数据未能复现随机重复和每次顺序混乱的问题，估计是测试数据中不像真实环境中那样复杂，并且数据量相差悬殊。幸运的是数据重复的问题得到了复现。

案情分析

基础背景

首先我们知道mysql存储的数据结构是B+树，数据是挂在叶子节点上的，然后叶子节点是通过双向链表连接起来的。我们平时使用mysql一般都用的InnoDB，InnoDB的主键用的是聚簇索引，其他索引用的是非聚簇索引。

案发原因猜测

讲道理，数据是挂载在主索引的叶子节点上的，并且通过双向链表连接起来的，我们去检索的时候，其顺序应该是固定的才对。难道是因为这个排序字段不是索引字段，排序的时候不在和索引顺序有关了，然后mysql采用了不稳定的排序？

检验猜测

1. 给str2添加索引
   
2. 再次执行查询
   

很显然，跟索引没关系！！！

既然跟索引没关系，那就看看mysql是怎么排序的吧。

Order By

下面我们先通过Mysql官方提供的文档来了解下Order By。原文地址：order-by-optimization.html，英文不好（比如我）的看这里：https://www.mysqlzh.com/doc/66/633.html

mysql有两种方式来支持Order By语句：使用索引来满足 ORDER BY、使用filesort来排序。

带limit和不带limit返回的数据可能不一样。 那我想应该就是order by + limit导致的上面的问题。
不过既然后到这里了，那还是先了解小order by吧。

使用索引来满足 ORDER BY

数据准备

1. 给t1加上索引（str1,str2）
2. 新增表t2、t3、t4

CREATE TABLE `t2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `str1` varchar(16) DEFAULT NULL,
  `str2` varchar(16) DEFAULT NULL,
  `str3` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_str1_str2` (`str1`,`str2`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t3` (
  `id` int NOT NULL AUTO_INCREMENT,
  `str1` varchar(16) DEFAULT NULL,
  `str2` varchar(16) DEFAULT NULL,
  `str3` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_str1_str2` (`str1`,`str2`) USING BTREE,
  KEY `idx_str3` (`str3`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t4` (
  `id` int NOT NULL AUTO_INCREMENT,
  `str1` varchar(16) DEFAULT NULL,
  `str2` varchar(16) DEFAULT NULL,
  `str3` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `str4` varchar(16) DEFAULT NULL,
  `str5` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_str2` (`str2`) USING BTREE,
  KEY `idx_str1_str2` (`str1`,`str2`) USING BTREE,
  KEY `idx_str3` (`str3`),
  KEY `idx_str4` (`str4`),
  KEY `idx_str5` (`str5`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

下面会使用explain来分析sql。关于explain可以自行百度。

开始实践

排序字段命中索引

当排序字段能命中索引时，mysql的优化器可能会直接使用索引完成排序：

select * from t1 order by str1;

为什么是可能呢？接着看就知道了：

t1和t2的差距就是t2.* 多了一列不是索引(str1,str2)中。
所以当查询*包含非order by索引列的时候，优化器将不会通过索引来完成排序。

如果排序字段中包含两个索引呢？

这里有Using index又有Using filesort，我很是疑惑，究竟是什么意思，不能索引排序就不能呗，为啥先index再sortfile啊，直到：


我想这涉及到select的知识了，应该是select不带where的情况下结果列只有一个索引的情况下（可以再加上主键索引），select会直接走索引，否则就会全表扫描。
所以这里的index只是说select走了index，但是order by是文件排序。

很明显，两个索引也不行不会用索引完成排序，及时有一个是主键索引也不行。
因为优化器认为查询多个索引然后还要排序的话还不如全表扫描来得快。

排序字段为索引右部分，其左部分为常量条件

我们知道，根据联合索引的命中规则中有最左匹配原则,那么下面这个情况也不会用索引排序：

当str1为常量时也会走索引排序：

联合索引排序方式

当联合索引显示指定排序方式且方式一致时，优化器会用索引来排序：

但是，当联合索引排序方向不一致时，优化器将不会使用索引来排序：

其实这一点，结合mysql的数据存储结构来想很容易理解。

使用函数

知道索引命中规则的应该都可以猜到order by中只用函数，肯定是不能用索引来排序的：

但是如果函数使用在结果集上，并不会影响索引排序的：

联合查询

排序是否使用索引，个人感觉和查询是否索引再加上上诉的情况就能得出结论，我这里就不累赘了，列举几个看看就好了：

小结

要想用索引就能完成排序，排序字段需要是命中索引，且只能有一个索引。并且输出结果也需要是这索引中的列（可以包含主键索引），在结果中可以使用函数。

filesort

当索引不满足排序是，mysql则将采用文件排序的方式进行排序。优化器会分配内存缓冲区给filesort；

8.0.12之前,mysql会预先分配固定sort_buffer_size字节的空间，8.0.12之后则是增量的分配，直到达到sort_buffer_size。这样我们就可以通过设置更大的sort_buffer_size来加快排序速度。

如果结果集太大，无法放入内存，那么filesort会在必要的时候使用临时磁盘文件来完成排序。而有些场景则可以避免这一问题：

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

各位可以通过修改sort_buffer_size去测试下；

优化Order by

1. 8.0.20版本前的mysql，对于没有使用filesort的满查询，可以通过降低 max_length_for_sort_data （该值过高会导致磁盘使用高而cpu使用低）的值 。
2. 尝试使用索引排序。
3. 增加 sort_buffer_size的值，该值应该大到足以将整个结果集放入排序缓冲区，从而避免写入磁盘和合并道。
4. 增加read_rnd_buffer_size变量值，以便一次读取更多行。
5. 将tmpdir系统变量改为指向具有大量空闲空间的专用文件系统。可以配置多个目录，Unix用:分隔，Windows用;分隔。

limit

• 如果你用LIMIT只选择一些行，当MySQL选择做完整的表扫描时，它将在一些情况下使用索引。
  
  

• 如果你使用LIMIT row_count与ORDER BY，MySQL一旦找到了排序结果的第一个row_count行，将结束排序而不是排序整个表。如果使用索引，将很快。如果必须进行文件排序（filesort），必须选择所有匹配查询没有LIMIT子句的行，并且在确定已经找到第1个row_count行前，必须对它们的大部分进行排序。在任何一种情况下，一旦找到了行，则不需要再排序结果的其它部分，并且MySQL不再进行排序

• 结合LIMIT row_count和DISTINCT时，MySQL一旦找到row_count个唯一的行，它将停止。

• 在一些情况下，GROUP BY能通过顺序读取键(或在键上做排序)来解决，然后计算摘要直到关键字的值改变。在这种情况下，LIMIT row_count将不计算任何不必要的GROUP BY值。

• 只要MySQL已经发送了需要的行数到客户，它将放弃查询，除非你正使用SQL_CALC_FOUND_ROWS。

• IMIT 0将总是快速返回一个空集合。这对检查查询的有效性是有用的。当使用MySQL API时，它也可以用来得到结果列的列类型。(该技巧在MySQL Monitor中不工作，只显示Empty set；应使用SHOW COLUMNS或DESCRIBE）。

• 当服务器使用临时表来进行查询时，使用LIMIT row_count子句来计算需要多少空间。

结果很不幸

很不幸的是，到目前为止并未得知为什么mysql出来的数据顺序是乱的。通过百度得到的一句话就是：

SQL中ORDER BY相同值结果乱序的具体原因
  查阅了Google和相关资料，大概总结了这种情况的原因。其实发生这种现象是“故意”设计的。
  如果没有指定ORDER BY语句，则SQL Server（或任何RDBMS）不保证以特定顺序返回结果。 有些人认为，如果没有指定order by子句，行总是以聚簇索引顺序或物理磁盘顺序返回。 然而，这是不正确的，因为在查询处理期间可以改变行顺序的许多因素，例如并行的HASH连接是更改行顺序的操作符的一个很好的例子。
  如果指定ORDER BY语句，SQL Server将对行进行排序，并按请求的顺序返回。 但是，如果该顺序不是确定性的，即可能有重复的值，则在每个具有相同值的组中，由于与上述相同的原因，该顺序是“随机的”。
  确保确定性顺序的唯一方法是在ORDER BY子句中包含保证的唯一列或列组（例如主键）。

结合个人的经验，猜测应该是数据库读取数据过来排序的时候用了并发操作，然后将数据放到了无序的集合里面，类似HashMap这种，然后通过对key进行排序后输出排序结果，所以导致了多次排序顺序不一致。