浅谈Using filesort和Using temporary 为什么这么慢

1.简介

我们都知道使用explain 分析sql语句的时候，如果，在extra这一列发现using index说明使用了覆盖索引，没有回表操作性能还不错；那么，如果发现是using filesort、using temporary 出现了文件排序，临时表， 这个时候，我们就需要进行索引优化了；那么，问题来了，我们需要怎么优化呢？为什么出现这两个的时候，mysql是怎么执行的呢？效率怎么就低下呢？所以，如果不知道它的执行原理的话，其实，我们也无法进行针对性的优化；所以，本文主要就是探讨一下mysql 出现using filesort、using temporary时的场景和优化方法；

2.using filesort

using filesort: 文件排序；我们首先来模拟一下文件排序，然后再分析一下为什么文件排序效率较低；最后，在来说一下解决方案；

2.1 using filesort 现象模拟

建表语句

create table study (
	id int(11) not null,
	name varchar(32) default null,
	score int(11) default null,
	primary key (id)
) engine=innodb default charset=utf8;

现在，有一个需求需要把所有的同学都找出来，然后，按照成绩从高到低进行排序；很显然，sql语句可以这么写：

select id, name, score from study order by score desc;

然后，我们对这个sql语句使用explain进行分析：

分析结果如下：使用的是全表扫描，在扫描的过程中，出现了using filesort文件排序；很显然，对于文件排序我们需要进行优化；

2.2 using filesort 之mysql的执行过程

using filesort 文件排序，其实文件排序的话，会有很多种情况，比如说：根据要排序的内容大小，就有内部排序和外部排序；如果，排序的内容比较小，那么，在内存中就可以搞定，这就是内部排序（使用快排）；如果，要排序的内容太大，那么，就得需要通过磁盘的帮助了，这个就是外部排序（使用归并）。
还有，就是根据一行的大小来进行区分，如果，一行的内容不是很大，那么，就整个字段读取出来进行排序，称为全字段排序；如果，整个字段内容很大，那么，就采用rowid排序，读取rowid和该字段先进行排序，然后，再回表查找其他的内容； 下面，我将分别解释在全字段排序和rowid排序的时候，这个sql的执行过程；

2.2.1 全字段排序

sql执行过程

• 初始化sort_buffer,确定要放入的是id,name,score这三个字段
• 全表扫描，取出id, name, score这三个字段的值，存入到sort_buffer中；
• 对sort_buffer中的数据按照字段score做快速排序（在这里产生了filesort）；
• 将排序完的结果进行返回即可

注意：第三步可能是在内存中完成，但是，如果内存中排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序，如果，内存放不下的话，使用的就是外部排序，（将快排的结果写入到临时文件中）外部排序使用的是归并排序;(两个有序文件使用归并排序，时间复杂度为n效率较高)
总结：针对，using filesort的全字段排序，使用快排时间复杂度为nlogn。所以，效率较低；应该避免。

2.2.2 rowid排序

rowid：一张表中如果没有主键或者非空唯一索引时，就会创建一个rowid大小为6字节；
产生背景： 在我们排序的过程中，因为，sort_buffer是有限的，如果，我们要查询的字段很多的话，那么,sort_buffer里面要放的字段数太多，这样内存能够同时放下的行数就会很少，就需要分成多个临时文件，再进行归并，排序的性能会很差；
为了解决这个问题，mysql会进行优化，如果，一行数据大于一个阈值的话，读入到内存的时候，就是读取rowid + 要排序的字段；然后，再通过rowid回表去查询剩余的字段；我们通过show variables like '%max_length_for_sort_data%';这个指令可以查看这个阈值；
使用rowid的sql执行过程

• 初始化sort_buffer，确定要放入的字段为 id, 和 score;
• 全表扫描，取出id, score这两个字段的值，存入到sort_buffer中；
• 对sort_buffer中的数据按照字段score做快速排序（如果，数据太多，可能会导致外部文件排序）；
• 所有数据排序好以后，根据id回表查询name字段的内容；
• 把所有的数据返回给客户端

2.3 解决方案

针对，这个题目来说，解决方案也是比较简单的；要查询的字段是id , name, score；然后，需要对score进行排序。可以对score + name 建立联合索引，id是主键，这样，这三个字段都在普通索引中能够查询到，就解决了文件排序，也使用了覆盖索引；

create index index_score_name on study(score, name);

explain分析：

type = index 表示基于索引列的扫描；extra = using index 表示覆盖索引 extra = backward index scan；表示基于索引从后往前找；效果较佳；经过测试，使用索引，以空间换时间，把数据提交按照score排好序，符合我们的需求；

3.using temporary

背景mysql 执行查询语句时，对于order by 可能会导致filesort或者temporary。
原则：filesort只能应用于单个表上，如果，有多个表的数据需要进行排序，那么，mysql会先创建一张临时表来保存数据，然后，再在临时表上使用filesort进行排序，最后输出结果。

3.1 场景再现

建表语句：

create table t1(    
id int, col1 int, col2 varchar(10),
key(id, col1));

create table t2(
id int, col1 int, col2 varchar(10),
key(col1));

情况一：

explain select * from t1 force index(id), t2 where t1.id=1 and t1.col1 = t2.col2 order by t1.col1;

这边，即没有使用temporary 也没有使用filesort。
那么，它是怎么执行的呢？
它其实是先定位t1.id;然后，定位t1.col1；这两个都是通过索引来进行；然后，在执行和t2表的联结（where）所以，没有文件排序和临时表；
情况二：

explain select * from t1 force index(id), t2 where t1.id=1 and t1.col1 = t2.col2 order by t1.col2;

这里是产生了filesort；执行过程如下：

• 根据先定位t1.id；
• 把id=1的都取出来，把所有的字段也都取出来；存到sort_buffer中。
• 存入sort_buffer之后，需要根据t1的字段col2进行排序；
• t1表排序好了后，根据排序好的结果集去联结t2表中的数据；（所以，没有产生临时文件）

情况三：

explain select * from t1 force index(id), t2 where t1.id=1 and t1.col1 = t2.col2 order by t2.col1 ;

这里，产生了temporary 和 filesort；执行过程如下：

• 根据id索引先定位t1.id = 1所在行；
• 由于order by的字段是在t2表上；所以，这个时候，不能排好序后再去联结t2表了；
• 需要把t1 和 t2表先联结起来，保存到temporary表上，然后，再根据t2.col1字段进行filesort；所以效率是很慢的；

3.2 解决方案

通过，对temporary的分析，如果，要排序的字段在主表上，是不会产生temporary的；所以，如果可以的话，我们尽量修改sql语句 把要排序字段放在主表中；或者使用straight_join(强制把左边的表设置为驱动表)； 针对情况三sql重写：

select * from t2 force index(col1)  straight_join t1  on t1.id=1 and t1.col1 = t2.col2 order by t2.col1 ;

explain分析如下：

4.总结

本文主要是根据个人写了filesort、temporary 产生原因，处理方式；在处理方案上，可能考虑不足，如果，有一些其他的产生原因或者处理方案。欢迎交流；

到此这篇关于浅谈using filesort和using temporary 为什么这么慢的文章就介绍到这了,更多相关using filesort using temporary内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！