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

2022-01-19 10:30:53

能力有限有误请指出。

本文使用源码版本：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节会进行原因解释和问题重现。

临时文件如下：

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

下面是这些案例信息：

  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



  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;


  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)

  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)

  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)
上一篇：智联万物，车联未来：湖南湘江新区智能汽车产业再升级


下一篇：[微信小程序]通过计算其他view的高度，动态给定scroll-view的高度