淺談Using filesort和Using temporary 為什麼這麼慢

2022-02-18 13:04:59

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內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！