MySQL Order BY 排序过程
MySQL 在进行 Order By 操作排序时，通常有两种排序方式：
- 全字段排序
- Row_id 排序
MySQL 中每个线程在执行排序时，都会被分配一块区域 - sort buffer，它的大小通过 sort_buffer_size 控制。
全字段排序指的是，将要查询的字段，全都存入 sort buffer 中，然后对 sort buffer 进行排序，然后将结果返回给客户端。
Row_id 排序：将被排序的字段和对应主键索引的 ID 放入，sort buffer 中，然后对 sort buffer 进行排序，最后额外进行一次回表操作查找额外的信息，然后将结果返回给客户端。
全字段排序和 Row_id 排序的主要区别在：
- sort buffer 存入的内容不同
- 回表查询的次数不一致。
对于 InnoDB 表来说，在内存足够的情况下，会优先选择全字段排序的方式。在内存不足的情况下，可能会借用外部文件进行排序。
但如果单行内容较大时，会导致拆分的外部文件过多，进行归并排序时，效率变低。此时会采用 Row_id 的排序方式。
对于 Memory 表来说，会优先选择 Row_id 的排序方式。
接下来会对全字段排序和 Row_id 排序进行验证，最后并给出一些调优的技巧。
环境准备
假设存在如下表结构，表里有 5万的数据行, 其中 type 为二级索引。
# MySQL5.7.28, RR
CREATE TABLE `test_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
`type` varchar(16) NOT NULL,
`phone` int(11) NOT NULL,
`addr` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `type` (`type`)
) ENGINE=InnoDB;
向表中插入数据：
import random
import MySQLdb
def prepare_data():
result = []
type = ["a", "b", "c", "d", "e"]
for i in range(50000):
index = random.randint(0, 4)
result.append((str(i), str(type[index]), str(i + 10), str(i)))
return result
def insert_data():
db = MySQLdb.connect(host='10.124.207.xxx',
user='xxxx',
passwd='xxxxx',
db='usecase',
charset='utf8')
sql = 'INSERT INTO test_table ( name, type, phone, addr) VALUES ( %s, %s, %s, %s);'
cur = db.cursor()
cur.executemany(sql, prepare_data())
db.commit()
db.close()
if __name__ == '__main__':
try:
insert_data()
except Exception as e:
下面会进行查询操作：
``select name,type,phone from test_table where type='a' order by name limit 1000 ;`
并对排序的过程进行分析。
全字段排序
这里对该查询语句进行了 EXPLAIN 操作，可以看到在 Extra 列 ：
- 用到了索引
- 进行了排序操作 - filesort （无法利用索引默认有序的情况）
针对本次 SELECT 来说，经历如下的过程：
- 使用 type = a 的二级索引，找到满足的第一个值。
- 根据该值，找到主键 ID。回表去找 name 和 phone 的值。
- 然后将
type,name, phone存入 sort_buffer。 - 然后重复 1 - 4 过程，查询所有 type =a 的内容，然后将 name, phone, type 存入 sort_buffer。
- 然后对分配内存里的信息进行排序。
- 然后选择前 1000 条，返回给客户端。
打开 optimizer_trace ，查看执行的流程：
可以看到，对应 "sort_mode": 中的内容为 sort_key 和其他字段，这就表示用的是全字段排序。
chosen: true 表示使用的是优先队列的排序算法。
但在实际情况下，往往会出现待排序的内容大于分配用于排序的空间，此时就会用到外部的文件排序，而这种外部排序一般都使用归并排序。
将 sort_buffer 调小，重新执行：
将默认大小临时改小：
# 将 size 调小, 并重新登录
set sort_buffer_size= 24 * 1024;
/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on';
/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 执行语句 */
select type,name,phone from test_table where type='a' order by name limit 1000 ;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 计算Innodb_rows_read差值 */
select @b-@a;
在 OPTIMIZER_TRACE 中查看：
number_of_tmp_files 表示使用的临时文件数为 20，上面的 chosen 表示由于空间不足，无法使用堆排序。
可以发现：
-
当
sort_buffer_size大于要待排序的内容，则使用内存排序。 -
如果小于，则使用外部临时文件辅助排序。
但还有一种情况，就是待排序的内容数据量太大或者单行查询的字段太多（如 SELECT *）这种情况，会导致生成的临时文件数量太多，效率不高。所以就出现了另一种 Row_id 的排序方式。
Row_id 排序
在单行查询字段很多时，在 sort_buffer 中仅仅保存必要的字段，最后额外再进行一次统一回表的操作，查询必要的信息。
这里可以将 SET max_length_for_sort_data = 16; 改小，模拟这种情况。
这里 sort_mode: sort_key, rowid 指的就是用的 Row_id 这种处理方式。
对应经历的过程就会变成：
- 使用 type = a 的二级索引，找到满足的第一个值。
- 根据该值，找到主键 ID。回表去找 row_id 的值。（发生变化）
- 然后将
type, row_id存入 sort_buffer。（发生变化） - 然后重复 1 - 4 过程，查询所有 type = a 的内容，然后将 type, row_id 存入 sort_buffer。（发生变化）
- 然后对分配内存里的信息进行排序。
- 然后选择前 1000 条，并取到需要的 row_id 集合。
- 根据 row_id，回表查询所需要的信息，然后返回给客户端。（发生变化）
这里 2, 3, 4, 7 和之前全字段排序相比，发生了变化。
但需要注意的是，在内存足够的情况下，InooDB 会优先选择全排序的方式。但对于 Memory 方式的表结构，则会有不同的选择。
内存临时表的排序选择
在如使用 Union 或者 Group By 等查询的情况下，会创建临时表，采用 Memory 作为存储的引擎。
而对于内存临时表，会优先采用 row_id 排序。
因为内存临时表，本身会在原表基础上，新建一张临时表保存需要的信息。因为临时表本身就在内存中，所以最后一次回表的操作，不会进行额外的磁盘 IO。所以 MySQL 会优先选择 row_id 的排序方式。
优化方法
场景1：利用索引有序，让 Order by 不排序
之前仅仅有 type 类型的索引，可以将其改成 type, name 的联合索引：
alter table test_table add index type_name(type, name);
在创建联合索引时，因为本身有序。在查询 typem,name,phone 时，会将 sort buffer 中的排序过程省略，也就是全排序过程中的第五步。
可以看到，在 Extra 中 filesort 的过程，已经被省略了。
场景2：利用覆盖索引，简化回表流程
alter table test_table add index type_name(type, name, phone);
这里由于想要查询的字段，都已经在二级索引上了。所以不需要进行回表，而且本身也是有序的。
并且可以发现，Extra 变成了 Using index. 表示直接使用了索引。
场景3：IN 导致 Order By 需要排序
假设存在 type_name(type, name); 联合索引。
将之前的 type = 换成了 IN，由于是对 a 和 b 两个类型同时排序，所以就需要 filesort 操作。
如果不想让 MySQL 进行排序的操作，可以将 IN 拆分成多个 = 执行，然后在调用端，自己进行合并。
总结
在 MySQL 中，使用 Order BY 时，通常有全字段排序和 Row_id 两种排序方式。
对于 InooDB 来说，在内存足够的情况下，会优先选择全字段排序。在内存不足，并所需排序内容不多时，会采用外部归并排序的方式。
但如果所在单行内容太大，导致拆分文件过多的情况下，会选择 Row_id 的排序方式。
对于 Memory 表来说，由于本身就在内存中，所以会优先选择 Row_id 的排序方式。
使用 Order By 操作时，不一定真的意味着真的去做排序，可以利用索引本身有序，或者覆盖索引，拆分 SQL 的方式，减少 MySQL 的排序过程。
参考
题外话：最近在系统的学习 MySQL，推荐一个比较好的学习材料就是<<丁奇老师的 MySQL 45 讲>>，链接已经附在文章末尾。
文章中很多知识点就是从中学来，加入自己的理解并整理的。
大家在购买后，强烈推荐读一读评论区的内容，价值非常高，不少同学问出了自己在思考时的一些困惑。
