之前的文章《??MySQL性能調(diào)優(yōu)，這個(gè)工具最有用（上）??》詳細(xì)敘述了explain結(jié)果中最重要的type字段（連接類(lèi)型）的含義。

其實(shí)，explain結(jié)果中還有一個(gè)Extra字段，對(duì)分析與優(yōu)化SQL有很大的幫助，今天花1分鐘簡(jiǎn)單和大家聊一聊。

數(shù)據(jù)準(zhǔn)備：

create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;

insert into user values(1, 'shenjian','no');
insert into user values(2, 'zhangsan','no');
insert into user values(3, 'lisi', 'yes');
insert into user values(4, 'lisi', 'no');

數(shù)據(jù)說(shuō)明：

• 用戶(hù)表：id主鍵索引，name普通索引（非唯一），sex無(wú)索引；
• 四行記錄：其中name普通索引存在重復(fù)記錄lisi；

實(shí)驗(yàn)?zāi)康模?/h3>

通過(guò)構(gòu)造各類(lèi)SQL語(yǔ)句，對(duì)explain的Extra字段進(jìn)行說(shuō)明，啟發(fā)式定位待優(yōu)化低性能SQL語(yǔ)句。

一、【Using where】

實(shí)驗(yàn)語(yǔ)句：

explain select * from user where sex='no';

結(jié)果說(shuō)明：

Extra為Using where說(shuō)明，SQL使用了where條件過(guò)濾數(shù)據(jù)。

需要注意的是：

• 返回所有記錄的SQL，不使用where條件過(guò)濾數(shù)據(jù)，大概率不符合預(yù)期，對(duì)于這類(lèi)SQL往往需要進(jìn)行優(yōu)化；
• 使用了where條件的SQL，并不代表不需要優(yōu)化，往往需要配合explain結(jié)果中的type（連接類(lèi)型）來(lái)綜合判斷；

畫(huà)外音：join type在《上》中有詳細(xì)敘述，本文不再展開(kāi)。

本例雖然Extra字段說(shuō)明使用了where條件過(guò)濾，但type屬性是ALL，表示需要掃描全部數(shù)據(jù)，仍有優(yōu)化空間。

常見(jiàn)的優(yōu)化方法為，在where過(guò)濾屬性上添加索引。

畫(huà)外音：本例中，sex字段區(qū)分度不高，添加索引對(duì)性能提升有限。

二、【Using index】

實(shí)驗(yàn)語(yǔ)句：

explain select id,name from user where name='shenjian';

結(jié)果說(shuō)明：

Extra為Using index說(shuō)明，SQL所需要返回的所有列數(shù)據(jù)均在一棵索引樹(shù)上，而無(wú)需訪問(wèn)實(shí)際的行記錄。

畫(huà)外音：The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row.

這類(lèi)SQL語(yǔ)句往往性能較好。

問(wèn)題來(lái)了，什么樣的列數(shù)據(jù)，會(huì)包含在索引樹(shù)上呢？

三、【Using index condition】

實(shí)驗(yàn)語(yǔ)句：

explain select id,name,sex from user 
where name='shenjian';

畫(huà)外音：該SQL語(yǔ)句與上一個(gè)SQL語(yǔ)句不同的地方在于，被查詢(xún)的列，多了一個(gè)sex字段。

結(jié)果說(shuō)明：

Extra為Using index condition說(shuō)明，確實(shí)命中了索引，但不是所有的列數(shù)據(jù)都在索引樹(shù)上，還需要訪問(wèn)實(shí)際的行記錄。

畫(huà)外音：聚集索引，普通索引的底層實(shí)現(xiàn)差異，之前撰文過(guò)。

這類(lèi)SQL語(yǔ)句性能也較高，但不如Using index。

問(wèn)題來(lái)了，如何優(yōu)化為Using index呢？

四、【Using filesort】

實(shí)驗(yàn)語(yǔ)句：

explain select * from user order by sex;

結(jié)果說(shuō)明：

Extra為Using filesort說(shuō)明，得到所需結(jié)果集，需要對(duì)所有記錄進(jìn)行文件排序。

這類(lèi)SQL語(yǔ)句性能極差，需要進(jìn)行優(yōu)化。

典型的，在一個(gè)沒(méi)有建立索引的列上進(jìn)行了order by，就會(huì)觸發(fā)filesort，常見(jiàn)的優(yōu)化方案是，在order by的列上添加索引，避免每次查詢(xún)都全量排序。

五、【Using temporary】

實(shí)驗(yàn)語(yǔ)句：

explain select * from user group by name order by sex;

結(jié)果說(shuō)明：

Extra是Using temporary說(shuō)明，需要建立臨時(shí)表(temporary table)來(lái)暫存中間結(jié)果。

這類(lèi)SQL語(yǔ)句性能較低，往往也需要進(jìn)行優(yōu)化。

典型的，group by和order by同時(shí)存在，且作用于不同的字段時(shí)，就會(huì)建立臨時(shí)表，以便計(jì)算出最終的結(jié)果集。

六、【Using join buffer (Block Nested Loop)】

實(shí)驗(yàn)語(yǔ)句：

explain select * from user where id in(select id from user where sex='no');

結(jié)果說(shuō)明：

Extra為Using join buffer (Block Nested Loop)說(shuō)明，需要進(jìn)行嵌套循環(huán)計(jì)算。

畫(huà)外音：內(nèi)層和外層的type均為ALL，rows均為4，需要循環(huán)進(jìn)行4*4次計(jì)算。

這類(lèi)SQL語(yǔ)句性能往往也較低，需要進(jìn)行優(yōu)化。

典型的，兩個(gè)關(guān)聯(lián)表join，關(guān)聯(lián)字段均未建立索引，就會(huì)出現(xiàn)這種情況。常見(jiàn)的優(yōu)化方案是，在關(guān)聯(lián)字段上添加索引，避免每次嵌套循環(huán)計(jì)算。

結(jié)尾：

explain是SQL優(yōu)化中最常用的工具，搞定type和Extra，explain也就基本搞定了。?