1.簡介

我們都知道使用explain 分析sql語句的時(shí)候，如果，在Extra這一列發(fā)現(xiàn)Using index說明使用了覆蓋索引，沒有回表操作性能還不錯；那么，如果發(fā)現(xiàn)是Using filesort、Using temporary 出現(xiàn)了文件排序，臨時(shí)表， 這個時(shí)候，我們就需要進(jìn)行索引優(yōu)化了；那么，問題來了，我們需要怎么優(yōu)化呢？為什么出現(xiàn)這兩個的時(shí)候，Mysql是怎么執(zhí)行的呢？效率怎么就低下呢？所以，如果不知道它的執(zhí)行原理的話，其實(shí)，我們也無法進(jìn)行針對性的優(yōu)化；所以，本文主要就是探討一下Mysql 出現(xiàn)Using filesort、Using temporary時(shí)的場景和優(yōu)化方法；

2.Using filesort

Using filesort: 文件排序；我們首先來模擬一下文件排序，然后再分析一下為什么文件排序效率較低；最后，在來說一下解決方案；

2.1 Using filesort 現(xiàn)象模擬

建表語句

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;

現(xiàn)在，有一個需求需要把所有的同學(xué)都找出來，然后，按照成績從高到低進(jìn)行排序；很顯然，sql語句可以這么寫：

select id, name, score FROM study order by score DESC;

然后，我們對這個sql語句使用explain進(jìn)行分析：

分析結(jié)果如下：使用的是全表掃描，在掃描的過程中，出現(xiàn)了Using filesort文件排序；很顯然，對于文件排序我們需要進(jìn)行優(yōu)化；

2.2 Using filesort 之Mysql的執(zhí)行過程

Using filesort 文件排序，其實(shí)文件排序的話，會有很多種情況，比如說：根據(jù)要排序的內(nèi)容大小，就有內(nèi)部排序和外部排序；如果，排序的內(nèi)容比較小，那么，在內(nèi)存中就可以搞定，這就是內(nèi)部排序（使用快排）；如果，要排序的內(nèi)容太大，那么，就得需要通過磁盤的幫助了，這個就是外部排序（使用歸并）。
還有，就是根據(jù)一行的大小來進(jìn)行區(qū)分，如果，一行的內(nèi)容不是很大，那么，就整個字段讀取出來進(jìn)行排序，稱為全字段排序；如果，整個字段內(nèi)容很大，那么，就采用rowid排序，讀取rowid和該字段先進(jìn)行排序，然后，再回表查找其他的內(nèi)容； 下面，我將分別解釋在全字段排序和rowid排序的時(shí)候，這個sql的執(zhí)行過程；

2.2.1 全字段排序

sql執(zhí)行過程

• 初始化sort_buffer,確定要放入的是id,name,score這三個字段
• 全表掃描，取出id, name, score這三個字段的值，存入到sort_buffer中；
• 對sort_buffer中的數(shù)據(jù)按照字段score做快速排序（在這里產(chǎn)生了filesort）；
• 將排序完的結(jié)果進(jìn)行返回即可

注意：第三步可能是在內(nèi)存中完成，但是，如果內(nèi)存中排序數(shù)據(jù)量太大，內(nèi)存放不下，則不得不利用磁盤臨時(shí)文件輔助排序，如果，內(nèi)存放不下的話，使用的就是外部排序，（將快排的結(jié)果寫入到臨時(shí)文件中）外部排序使用的是歸并排序;(兩個有序文件使用歸并排序，時(shí)間復(fù)雜度為N效率較高)
總結(jié)：針對，Using filesort的全字段排序，使用快排時(shí)間復(fù)雜度為NlogN。所以，效率較低；應(yīng)該避免。

2.2.2 rowid排序

rowid：一張表中如果沒有主鍵或者非空唯一索引時(shí)，就會創(chuàng)建一個rowid大小為6字節(jié)；
產(chǎn)生背景： 在我們排序的過程中，因?yàn)椋瑂ort_buffer是有限的，如果，我們要查詢的字段很多的話，那么,sort_buffer里面要放的字段數(shù)太多，這樣內(nèi)存能夠同時(shí)放下的行數(shù)就會很少，就需要分成多個臨時(shí)文件，再進(jìn)行歸并，排序的性能會很差；
為了解決這個問題，Mysql會進(jìn)行優(yōu)化，如果，一行數(shù)據(jù)大于一個閾值的話，讀入到內(nèi)存的時(shí)候，就是讀取rowid + 要排序的字段；然后，再通過rowid回表去查詢剩余的字段；我們通過SHOW VARIABLES LIKE '%max_length_for_sort_data%';這個指令可以查看這個閾值；
使用rowid的sql執(zhí)行過程

• 初始化sort_buffer，確定要放入的字段為 id, 和 score;
• 全表掃描，取出id, score這兩個字段的值，存入到sort_buffer中；
• 對sort_buffer中的數(shù)據(jù)按照字段score做快速排序（如果，數(shù)據(jù)太多，可能會導(dǎo)致外部文件排序）；
• 所有數(shù)據(jù)排序好以后，根據(jù)id回表查詢name字段的內(nèi)容；
• 把所有的數(shù)據(jù)返回給客戶端

2.3 解決方案

針對，這個題目來說，解決方案也是比較簡單的；要查詢的字段是id , name, score；然后，需要對score進(jìn)行排序?？梢詫core + name 建立聯(lián)合索引，id是主鍵，這樣，這三個字段都在普通索引中能夠查詢到，就解決了文件排序，也使用了覆蓋索引；

create index index_score_name on study(score, name);

explain分析：

type = index 表示基于索引列的掃描；Extra = Using index 表示覆蓋索引 Extra = Backward index scan；表示基于索引從后往前找；效果較佳；經(jīng)過測試，使用索引，以空間換時(shí)間，把數(shù)據(jù)提交按照score排好序，符合我們的需求；

3.Using temporary

背景Mysql 執(zhí)行查詢語句時(shí)，對于order by 可能會導(dǎo)致filesort或者temporary。
原則：filesort只能應(yīng)用于單個表上，如果，有多個表的數(shù)據(jù)需要進(jìn)行排序，那么，Mysql會先創(chuàng)建一張臨時(shí)表來保存數(shù)據(jù)，然后，再在臨時(shí)表上使用filesort進(jìn)行排序，最后輸出結(jié)果。

3.1 場景再現(xiàn)

建表語句：

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。
那么，它是怎么執(zhí)行的呢？
它其實(shí)是先定位t1.id;然后，定位t1.col1；這兩個都是通過索引來進(jìn)行；然后，在執(zhí)行和t2表的聯(lián)結(jié)（where）所以，沒有文件排序和臨時(shí)表；
情況二：

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

這里是產(chǎn)生了filesort；執(zhí)行過程如下：

• 根據(jù)先定位t1.id；
• 把id=1的都取出來，把所有的字段也都取出來；存到sort_buffer中。
• 存入sort_buffer之后，需要根據(jù)t1的字段col2進(jìn)行排序；
• t1表排序好了后，根據(jù)排序好的結(jié)果集去聯(lián)結(jié)t2表中的數(shù)據(jù)；（所以，沒有產(chǎn)生臨時(shí)文件）

情況三：

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

這里，產(chǎn)生了temporary 和 filesort；執(zhí)行過程如下：

• 根據(jù)id索引先定位t1.id = 1所在行；
• 由于order by的字段是在t2表上；所以，這個時(shí)候，不能排好序后再去聯(lián)結(jié)t2表了；
• 需要把t1 和 t2表先聯(lián)結(jié)起來，保存到temporary表上，然后，再根據(jù)t2.col1字段進(jìn)行filesort；所以效率是很慢的；

3.2 解決方案

通過，對temporary的分析，如果，要排序的字段在主表上，是不會產(chǎn)生temporary的；所以，如果可以的話，我們盡量修改sql語句 把要排序字段放在主表中；或者使用straight_join(強(qiáng)制把左邊的表設(shè)置為驅(qū)動表)； 針對情況三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.總結(jié)

本文主要是根據(jù)個人寫了filesort、temporary 產(chǎn)生原因，處理方式；在處理方案上，可能考慮不足，如果，有一些其他的產(chǎn)生原因或者處理方案。歡迎交流；

到此這篇關(guān)于淺談Using filesort和Using temporary 為什么這么慢的文章就介紹到這了,更多相關(guān)Using filesort Using temporary內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論