1.簡介

我們都知道使用explain 分析sql語句的時候，如果，在Extra這一列發(fā)現(xiàn)Using index說明使用了覆蓋索引，沒有回表操作性能還不錯；那么，如果發(fā)現(xiàn)是Using filesort、Using temporary 出現(xiàn)了文件排序，臨時表， 這個時候，我們就需要進行索引優(yōu)化了；那么，問題來了，我們需要怎么優(yōu)化呢？為什么出現(xiàn)這兩個的時候，Mysql是怎么執(zhí)行的呢？效率怎么就低下呢？所以，如果不知道它的執(zhí)行原理的話，其實，我們也無法進行針對性的優(yōu)化；所以，本文主要就是探討一下Mysql 出現(xiàn)Using filesort、Using temporary時的場景和優(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)在，有一個需求需要把所有的同學都找出來，然后，按照成績從高到低進行排序；很顯然，sql語句可以這么寫：

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

然后，我們對這個sql語句使用explain進行分析：

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

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

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

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

2.2.2 rowid排序

rowid：一張表中如果沒有主鍵或者非空唯一索引時，就會創(chuàng)建一個rowid大小為6字節(jié)；
產(chǎn)生背景： 在我們排序的過程中，因為，sort_buffer是有限的，如果，我們要查詢的字段很多的話，那么,sort_buffer里面要放的字段數(shù)太多，這樣內(nèi)存能夠同時放下的行數(shù)就會很少，就需要分成多個臨時文件，再進行歸并，排序的性能會很差；
為了解決這個問題，Mysql會進行優(yōu)化，如果，一行數(shù)據(jù)大于一個閾值的話，讀入到內(nèi)存的時候，就是讀取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ù)太多，可能會導致外部文件排序）；
• 所有數(shù)據(jù)排序好以后，根據(jù)id回表查詢name字段的內(nèi)容；
• 把所有的數(shù)據(jù)返回給客戶端

2.3 解決方案

針對，這個題目來說，解決方案也是比較簡單的；要查詢的字段是id , name, score；然后，需要對score進行排序。可以對score + 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ù)據(jù)提交按照score排好序，符合我們的需求；

3.Using temporary

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

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

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進行排序；
• t1表排序好了后，根據(jù)排序好的結果集去聯(lián)結t2表中的數(shù)據(jù)；（所以，沒有產(chǎn)生臨時文件）

情況三：

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表上；所以，這個時候，不能排好序后再去聯(lián)結t2表了；
• 需要把t1 和 t2表先聯(lián)結起來，保存到temporary表上，然后，再根據(jù)t2.col1字段進行filesort；所以效率是很慢的；

3.2 解決方案

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

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

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

最新評論