Mysql之Using filesort优化

一、前言在使用 explain 命令优化SQL语句的时候常常会在Extra列的描述中发现 Using filesort 选项，其实这个名字很容易造成误解，一开始我以为是“文件排序”的意思，进一步说可能就是使用了磁盘空间来进行排序，但是这个理解是错误的，Using filesort 真正含义其实只有 sort 这一个单词，和 file 没有什么关系，仅仅表示没有使用索引的排序。filesort使用的

Jason&Zhou

7595人浏览 · 2021-10-29 10:14:55

Jason&Zhou · 2021-10-29 10:14:55 发布

一、前言

在使用 explain 命令优化SQL语句的时候常常会在Extra列的描述中发现 Using filesort 选项，其实这个名字很容易造成误解，一开始我以为是“文件排序”的意思，进一步说可能就是使用了磁盘空间来进行排序，但是这个理解是错误的，Using filesort 真正含义其实只有 sort 这一个单词，和 file 没有什么关系，仅仅表示没有使用索引的排序。filesort使用的算法是QuickSort，即对需要排序的记录生成元数据进行分块排序，然后再使用mergesort方法合并块。其中filesort可以使用的内存空间大小为参数sort_buffer_size的值，默认为2M。当排序记录太多sort_buffer_size不够用时，mysql会使用临时文件来存放各个分块，然后各个分块排序后再多次合并分块最终全局完成排序。

二、正文

Using filesort 的含义很简单，就是使用了排序操作，出现这个选项的常见情况就是 Where 条件和 order by 子句作用在了不同的列上，这种情况还有优化的余地，有些场景由于数据量太小或者语句的简单性可能都不需要优化，既然说Using filesort是使用了排序的意思，那么是不是包含了 order by 子句的查询语句都会有这个选项呢？其实这个排序操作有时是可以避免的。

如果你想把一个表中的所有数据按照指定顺序输出，那么整个排序几乎是不可避免的，比如这个语句

explain select * from student ORDER BY age

结果如下：

在这里插入图片描述

可以看到虽然age字段上有索引，但是没有做索引，并且用到了Using filesort这个基于内存的排序。

注：不管在age上建没建立索引，都会用到Using filesort这个基于内存的排序。

不过个别时候这个排序还是可以省略的，比如id是该表的主键，并且是自增长的，数据本身就是有序的，那么直接返回数据就行了，相当于 order by id 这一部分被忽略了，比如这个语句：

explain select * from student ORDER BY id

结果如下：

在这里插入图片描述

上面提到的常见情况，SQL语句通常写成这样（在name和age上分别建立了索引）：

explain select * from student where name = 'Mitsunari' ORDER by age

这类语句一般会产生 Using filesort 这个选项，即使你在 name和 age 上分别添加了索引。结果如下：

在这里插入图片描述

我们想一下它的工作过程，先根据name的索引从所有数据信息中挑选出满足 name = ‘Mitsunari’ 条件的，然后根据age列的索引信息对挑选的数据进行排序，所以产生了Using filesort选项，想想怎样可以把后面排序的这个步骤省略掉？联合索引可以解决这个问题。

可以在 nage, age 两列上建立一个联合索引，索引类型一般是 BTREE，根据Mysql索引的最左原则，可以知道一共建立了IDX_Name、ID和IDX_Name_AGE两条索引，由于有了 IDX_Name_AGE这个联合索引，后面的排序步骤就可以省略了，在按照name = 'Mitsunari’条件挑选数据时，挂在name = ‘Mitsunari’ 节点下的数据，其实按照age列的值也是有顺序的，我们只需要在挑选数据的同时，按照age从小到大的顺序挑选即可，最后得到的数据就是有序的，直接返回就行了，从这一点可以看出，“排序”操作并不是不存在了，只是隐含在了前面必要的步骤中，不需要单独操作了而已，下面举个简单例子：

explain select * from student where name = 'Mitsunari' ORDER by age

在name和age字段上建立联合索引 IDX_Name_AGE，然后执行explain，结果如下：

在这里插入图片描述

可以看到已经使用了联合索引" IDX_Name_AGE"，并且已经没有使用”Using filesort“了。

三、总结

1、当Where 条件和 order by 子句作用在不同的列上，建立联合索引可以避免Using filesort的产生。
2、针对当前的例子实际上删除掉name列和age列上的单独索引，只保留联合索引也是可以达到相同效果的。
3、通过比较时间发现去掉了Using filesort情况，耗时少了一点点，实际操作中是不稳定的，但是平均时间可能会有一点提升。

最后引用我很佩服的一个人经常说的话：你知道的越多，你不知道的越多！

文章参考：

https://blog.csdn.net/albertsh/article/details/90296520

https://blog.csdn.net/canot/article/details/104920558