浅谈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内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！