持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第20天，点击查看活动详情

FileSort两种排序方式

 select * from test_index where name > '北' order by addr;

单路排序

将我们通过where筛序出的结果集，全部放入到sort buffer中，按照order by 的条件进行排序，最终直接返回数据集合。

缺点：所有字段全部放入sort buffer中占用空间

-- mysql 8.0.27 版本
            
            "filesort_summary": { -- 文件排序信息
              "memory_available": 262144, 
              "key_size": 61,
              "row_size": 230, 
              "max_rows_per_buffer": 106,
              "num_rows_estimate": 106,
              "num_rows_found": 93,
              "num_initial_chunks_spilled_to_disk": 0,
              "peak_memory_used": 33792,
              "sort_algorithm": "std::sort",
              "sort_mode": "<fixed_sort_key, packed_additional_fields>"
            } /* filesort_summary */
 
 
-- mysql 5.6 版本
            "filesort_summary": {
              "rows": 93,   -- 预计扫描行数
              "examined_rows": 106, -- 参与排序的行
              "number_of_tmp_files": 0,-- 使用临时文件的个数，这个值如果为0代表全部使用的sort_buffer内存排序，否则使用的磁盘文件排序
              "sort_buffer_size": 234112,-- 排序缓存的大小，单位Byte
              "sort_mode": "<sort_key, additional_fields>" -- 排序方式，这里用的单路排序
            } /* filesort_summary */

双路排序

将我们通过where筛序出的结果集当中的主键id（或许是伪列rowid）以及order by的字段放入sort buffer当中，按照order by 的条件进行排序，然后按照排序后的主键id（或许是伪列rowid）进行回表查询出满足所有条件的记录，最终直接返回数据集合。

优点：当sort buffer空间有限时适合使用，因为放入的字段少，占用空间小。

-- mysql 8.0.27 版本 效果等同同mysql版本的单路排序（已失效）
 
     "filesort_summary": {
              "memory_available": 262144,
              "key_size": 61,
              "row_size": 230,
              "max_rows_per_buffer": 106,
              "num_rows_estimate": 106,
              "num_rows_found": 93,
              "num_initial_chunks_spilled_to_disk": 0,
              "peak_memory_used": 33792,
              "sort_algorithm": "std::sort",
              "sort_mode": "<fixed_sort_key, packed_additional_fields>"
            }
 
-- mysql 5.6版本
 
           "filesort_summary": {
              "rows": 93,
              "examined_rows": 106,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 72416,
              "sort_mode": "<sort_key, rowid>" -- 双路排序
            } /* filesort_summary */

选择依据

MySQL 通过比较系统变量 max_length_for_sort_data(mysql8.0默认4096字节,mysql5.6默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

1.如果字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式。 2.如果字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模式。

结束语

记得最后关闭trace工具哟！

set session optimizer_trace="enabled=off",end_markers_in_json=OFF;