MySQL：排序（filesort）详细解析（8000字长文1）

能力有限有误请指出。

本文使用源码版本：5.7.22

引擎为：Innodb

排序（filesort）作为DBA绕不开的话题，也经常有朋友讨论它，比如常见的问题如下：

• 排序的时候，用于排序的数据会不会如Innodb一样压缩空字符存储，比如varchar(30)，我只是存储了1个字符是否会压缩，还是按照30个字符计算？
• max_length_for_sort_data/max_sort_length 到底是什么含义？
• original filesort algorithm（回表排序） 和 modified filesort algorithm（不回表排序） 的根本区别是什么？
• 为什么使用到排序的时候慢查询中的Rows_examined会更大，计算方式到底是什么样的？

在MySQL通常有如下算法来完成排序：

• 内存排序（优先队列 order by limit 返回少量行常用，提高排序效率，但是注意order by limit n,m 如果n过大可能会涉及到排序算法的切换）
• 内存排序（快速排序）
• 外部排序（归并排序）

但是由于能力有限本文不解释这些算法，并且本文不考虑优先队列算法的分支逻辑，只以快速排序和归并排序作为基础进行流程剖析。我们在执行计划中如果出现filesort字样通常代表使用到了排序，但是执行计划中看不出来下面问题：

• 是否使用了临时文件。
• 是否使用了优先队列。
• 是original filesort algorithm（回表排序）还是modified filesort algorithm（不回表排序）。

如何查看将在后面进行描述。本文还会给出大量的排序接口供感兴趣的朋友使用，也给自己留下笔记。

一、从一个问题出发

这是最近一个朋友遇到的案例，大概意思就是说我的表在Innodb中只有30G左右，为什么使用如下语句进行排序操作后临时文件居然达到了200多G，当然语句很变态，我们可以先不问为什么会有这样的语句，我们只需要研究原理即可，在本文的第13节会进行原因解释和问题重现。

临时文件如下：

下面是这些案例信息：

1. show create table  t\G
   
2. *************************** 1. row ***************************
   
3. Table: t
   
4. CreateTable: CREATE TABLE `t`(
   
5. `ID` bigint(20) NOT NULL COMMENT 'ID',
   
6. `UNLOAD_TASK_NO` varchar(50) NOT NULL ,
   
7. `FORKLIFT_TICKETS_COUNT` bigint(20) DEFAULT NULL COMMENT '叉车票数',
   
8. `MANAGE_STATUS` varchar(20) DEFAULT NULL COMMENT '管理状态',
   
9. `TRAY_BINDING_TASK_NO` varchar(50) NOT NULL ,
   
10. `STATISTIC_STATUS` varchar(50) NOT NULL ,
    
11. `CREATE_NO` varchar(50) DEFAULT NULL ,
    
12. `UPDATE_NO` varchar(50) DEFAULT NULL ,
    
13. `CREATE_NAME` varchar(200) DEFAULT NULL COMMENT '创建人名称',
    
14. `UPDATE_NAME` varchar(200) DEFAULT NULL COMMENT '更新人名称',
    
15. `CREATE_ORG_CODE` varchar(200) DEFAULT NULL COMMENT '创建组织编号',
    
16. `UPDATE_ORG_CODE` varchar(200) DEFAULT NULL COMMENT '更新组织编号',
    
17. `CREATE_ORG_NAME` varchar(1000) DEFAULT NULL COMMENT '创建组织名称',
    
18. `UPDATE_ORG_NAME` varchar(1000) DEFAULT NULL COMMENT '更新组织名称',
    
19. `CREATE_TIME` datetime DEFAULT NULL COMMENT '创建时间',
    
20. `UPDATE_TIME` datetime DEFAULT NULL COMMENT '更新时间',
    
21. `DATA_STATUS` varchar(50) DEFAULT NULL COMMENT '数据状态',
    
22. `OPERATION_DEVICE` varchar(200) DEFAULT NULL COMMENT '操作设备',
    
23. `OPERATION_DEVICE_CODE` varchar(200) DEFAULT NULL COMMENT '操作设备编码',
    
24. `OPERATION_CODE` varchar(50) DEFAULT NULL COMMENT '操作码',
    
25. `OPERATION_ASSIST_CODE` varchar(50) DEFAULT NULL COMMENT '辅助操作码',
    
26. `CONTROL_STATUS` varchar(50) DEFAULT NULL COMMENT '控制状态',
    
27. `OPERATOR_NO` varchar(50) DEFAULT NULL COMMENT '操作人工号',
    
28. `OPERATOR_NAME` varchar(200) DEFAULT NULL COMMENT '操作人名称',
    
29. `OPERATION_ORG_CODE` varchar(50) DEFAULT NULL COMMENT '操作部门编号',
    
30. `OPERATION_ORG_NAME` varchar(200) DEFAULT NULL COMMENT '操作部门名称',
    
31. `OPERATION_TIME` datetime DEFAULT NULL COMMENT '操作时间',
    
32. `OPERATOR_DEPT_NO` varchar(50) NOT NULL COMMENT '操作人所属部门编号',
    
33. `OPERATOR_DEPT_NAME` varchar(200) NOT NULL COMMENT '操作人所属部门名称',
    
34. `FORKLIFT_DRIVER_NAME` varchar(200) DEFAULT NULL ,
    
35. `FORKLIFT_DRIVER_NO` varchar(50) DEFAULT NULL ,
    
36. `FORKLIFT_DRIVER_DEPT_NAME` varchar(200) DEFAULT NULL ,
    
37. `FORKLIFT_DRIVER_DEPT_NO` varchar(50) DEFAULT NULL ,
    
38. `FORKLIFT_SCAN_TIME` datetime DEFAULT NULL ,
    
39. `OUT_FIELD_CODE` varchar(200) DEFAULT NULL,
    
40.  PRIMARY KEY (`ID`),
    
41.  KEY `IDX_TRAY_BINDING_TASK_NO`(`TRAY_BINDING_TASK_NO`),
    
42.  KEY `IDX_OPERATION_ORG_CODE`(`OPERATION_ORG_CODE`),
    
43.  KEY `IDX_OPERATION_TIME`(`OPERATION_TIME`)
    
44. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
    
45. 
    
46. 
    
47. 
    
48. desc
    
49. SELECT
    
50.    ID,
    
51.    UNLOAD_TASK_NO,
    
52.    FORKLIFT_TICKETS_COUNT,
    
53.    MANAGE_STATUS,
    
54.    TRAY_BINDING_TASK_NO,
    
55.    STATISTIC_STATUS,
    
56.    CREATE_NO,
    
57.    UPDATE_NO,
    
58.    CREATE_NAME,
    
59.    UPDATE_NAME,
    
60.    CREATE_ORG_CODE,
    
61.    UPDATE_ORG_CODE,
    
62.    CREATE_ORG_NAME,
    
63.    UPDATE_ORG_NAME,
    
64.    CREATE_TIME,
    
65.    UPDATE_TIME,
    
66.    DATA_STATUS,
    
67.    OPERATION_DEVICE,
    
68.    OPERATION_DEVICE_CODE,
    
69.    OPERATION_CODE,
    
70.    OPERATION_ASSIST_CODE,
    
71.    CONTROL_STATUS,
    
72.    OPERATOR_NO,
    
73.    OPERATOR_NAME,
    
74.    OPERATION_ORG_CODE,
    
75.    OPERATION_ORG_NAME,
    
76.    OPERATION_TIME,
    
77.    OPERATOR_DEPT_NO,
    
78.    OPERATOR_DEPT_NAME,
    
79.    FORKLIFT_DRIVER_NAME,
    
80.    FORKLIFT_DRIVER_NO,
    
81.    FORKLIFT_DRIVER_DEPT_NAME,
    
82.    FORKLIFT_DRIVER_DEPT_NO,
    
83.    FORKLIFT_SCAN_TIME,
    
84.    OUT_FIELD_CODE
    
85. FROM
    
86.    t
    
87. GROUP BY id , UNLOAD_TASK_NO , FORKLIFT_TICKETS_COUNT ,
    
88. MANAGE_STATUS , TRAY_BINDING_TASK_NO , STATISTIC_STATUS ,
    
89. CREATE_NO , UPDATE_NO , CREATE_NAME , UPDATE_NAME ,
    
90. CREATE_ORG_CODE , UPDATE_ORG_CODE , CREATE_ORG_NAME ,
    
91. UPDATE_ORG_NAME , CREATE_TIME , UPDATE_TIME , DATA_STATUS ,
    
92. OPERATION_DEVICE , OPERATION_DEVICE_CODE , OPERATION_CODE ,
    
93. OPERATION_ASSIST_CODE , CONTROL_STATUS , OPERATOR_NO ,
    
94. OPERATOR_NAME , OPERATION_ORG_CODE , OPERATION_ORG_NAME ,
    
95. OPERATION_TIME , OPERATOR_DEPT_NO , OPERATOR_DEPT_NAME ,
    
96. FORKLIFT_DRIVER_NAME , FORKLIFT_DRIVER_NO ,
    
97. FORKLIFT_DRIVER_DEPT_NAME , FORKLIFT_DRIVER_DEPT_NO ,
    
98. FORKLIFT_SCAN_TIME , OUT_FIELD_CODE;
    
99. 
    
100. 
     
101. +----+-------------+-------------------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
     
102. | id | select_type | table                   | partitions | type | possible_keys | key  | key_len | ref| rows    | filtered | Extra|
     
103. +----+-------------+-------------------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
     
104. |  1| SIMPLE      | t | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 5381145|   100.00| Using filesort |
     
105. +----+-------------+-------------------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
     
106. 1 row inset, 1 warning (0.00 sec)
     

也许你会怀疑这个语句有什么用，我们先不考虑功能，我们只考虑为什么它会生成200G的临时文件这个问题。接下来我将分阶段进行排序的流程解析，注意了整个排序的流程均处于状态‘Creating sort index’下面，我们以filesort函数接口为开始进行分析。

二、测试案例为了更好的说明后面的流程我们使用2个除了字段长度不同，其他完全一样的表来说明，但是需要注意这两个表数据量很少，不会出现外部排序，如果涉及外部排序的时候我们需要假设它们数据量很大。其次这里根据original filesort algorithm和modified filesort algorithm进行划分，但是这两种方法还没讲述，不用太多理会。

• original filesort algorithm（回表排序）

1. mysql> show create table tests1 \G
   
2. *************************** 1. row ***************************
   
3. Table: tests1
   
4. CreateTable: CREATE TABLE `tests1`(
   
5. `a1` varchar(300) DEFAULT NULL,
   
6. `a2` varchar(300) DEFAULT NULL,
   
7. `a3` varchar(300) DEFAULT NULL
   
8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
   
9. 1 row inset(0.00 sec)
   
10. 
    
11. mysql> select* from tests1;
    
12. +------+------+------+
    
13. | a1   | a2   | a3   |
    
14. +------+------+------+
    
15. | a    | a    | a    |
    
16. | a    | b    | b    |
    
17. | a    | c    | c    |
    
18. | b    | d    | d    |
    
19. | b    | e    | e    |
    
20. | b    | f    | f    |
    
21. | c    | g    | g    |
    
22. | c    | h    | h    |
    
23. +------+------+------+
    
24. 8 rows inset(0.00 sec)
    
25. 
    
26. mysql> desc select* from tests1 where a1='b' order by a2,a3;
    
27. +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
    
28. | id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref| rows | filtered | Extra|
    
29. +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
    
30. |  1| SIMPLE      | tests1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8|    12.50| Usingwhere; Using filesort |
    
31. +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
    
32. 1 row inset, 1 warning (0.00 sec)