浅谈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 现象摹拟
建表语句
id int(11) not null,
name varchar(32) DEFAULT NULL,
score int(11) DEFAULT NULL,
PRIMARY key (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
现在,有一个需求需要把所有的同学都找出来,然后,依照成绩从高到低进行排序;很明显,sql语句可以这么写:
然后,我们对这个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是主键,这样,这三个字段都在普通索引中能够查询到,就解决了文件排序,也使用了覆盖索引;
explain分析:
type = index 表示基于索引列的扫描;Extra = Using index 表示覆盖索引 Extra = Backward index scan;表示基于索引从后往前找;效果较佳;经过测试,使用索引,以空间换时间,把数据提交依照score排好序,符合我们的需求;
3.Using temporary
背景Mysql 履行查询语句时,对order by 可能会致使filesort或temporary。
原则:filesort只能利用于单个表上,如果,有多个表的数据需要进行排序,那末,Mysql会先创建一张临时表来保存数据,然后,再在临时表上使用filesort进行排序,最后输出结果。
3.1 场景再现
建表语句:
id int, col1 int, col2 varchar(10),
key(id, col1));
id int, col1 int, col2 varchar(10),
key(col1));
情况一:
这边,即没有使用temporary 也没有使用filesort。
那末,它是怎样履行的呢?
它实际上是先定位t1.id;然后,定位t1.col1;这两个都是通过索引来进行;然后,在履行和t2表的联结(where)所以,没有文件排序和临时表;
情况二:
这里是产生了filesort;履行进程以下:
- 根据先定位t1.id;
- 把id=1的都取出来,把所有的字段也都取出来;存到sort_buffer中。
- 存入sort_buffer以后,需要根据t1的字段col2进行排序;
- t1表排序好了后,根据排序好的结果集去联结t2表中的数据;(所以,没有产生临时文件)
情况三:
这里,产生了temporary 和 filesort;履行进程以下:
- 根据id索引先定位t1.id = 1所在行;
- 由于order by的字段是在t2表上;所以,这个时候,不能排好序后再去联结t2表了;
- 需要把t1 和 t2表先联结起来,保存到temporary表上,然后,再根据t2.col1字段进行filesort;所以效力是很慢的;
3.2 解决方案
通过,对temporary的分析,如果,要排序的字段在主表上,是不会产生temporary的;所以,如果可以的话,我们尽可能修改sql语句 把要排序字段放在主表中;或使用straight_join
(强迫把左侧的表设置为驱动表); 针对情况三sql重写:
explain分析以下:
4.总结
本文主要是根据个人写了filesort、temporary 产生缘由,处理方式;在处理方案上,可能斟酌不足,如果,有一些其他的产生缘由或处理方案。欢迎交换;
到此这篇关于浅谈Using filesort和Using temporary 为何这么慢的文章就介绍到这了,更多相关Using filesort Using temporary内容请搜索之前的文章或继续浏览下面的相关文章希望大家以后多多支持!
文章来源:丸子建站
文章标题:浅谈Using filesort和Using temporary 为何这么慢
https://www.wanzijz.com/view/58986.html