1.簡(jiǎn)介

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

2.Using filesort

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

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

建表語(yǔ)句

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)在，有一個(gè)需求需要把所有的同學(xué)都找出來(lái)，然后，按照成績(jī)從高到低進(jìn)行排序；很顯然，sql語(yǔ)句可以這么寫(xiě)：

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

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

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

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

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

2.2.1 全字段排序

sql執(zhí)行過(guò)程

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

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

2.2.2 rowid排序

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

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

2.3 解決方案

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

create index index_score_name on study(score, name);

explain分析：

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

3.Using temporary

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

3.1 場(chǎng)景再現(xiàn)

建表語(yǔ)句：

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;

這邊，即沒(méi)有使用temporary 也沒(méi)有使用filesort。
那么，它是怎么執(zhí)行的呢？
它其實(shí)是先定位t1.id;然后，定位t1.col1；這兩個(gè)都是通過(guò)索引來(lái)進(jìn)行；然后，在執(zhí)行和t2表的聯(lián)結(jié)（where）所以，沒(méi)有文件排序和臨時(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í)行過(guò)程如下：

• 根據(jù)先定位t1.id；
• 把id=1的都取出來(lái)，把所有的字段也都取出來(lái)；存到sort_buffer中。
• 存入sort_buffer之后，需要根據(jù)t1的字段col2進(jìn)行排序；
• t1表排序好了后，根據(jù)排序好的結(jié)果集去聯(lián)結(jié)t2表中的數(shù)據(jù)；（所以，沒(méi)有產(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í)行過(guò)程如下：

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

3.2 解決方案

通過(guò)，對(duì)temporary的分析，如果，要排序的字段在主表上，是不會(huì)產(chǎn)生temporary的；所以，如果可以的話，我們盡量修改sql語(yǔ)句 把要排序字段放在主表中；或者使用straight_join(強(qiáng)制把左邊的表設(shè)置為驅(qū)動(dòng)表)； 針對(duì)情況三sql重寫(xiě)：

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ù)個(gè)人寫(xiě)了filesort、temporary 產(chǎn)生原因，處理方式；在處理方案上，可能考慮不足，如果，有一些其他的產(chǎn)生原因或者處理方案。歡迎交流；

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

最新評(píng)論