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

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

數(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ù)說明：

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

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

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

一、【Using where】

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

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

結(jié)果說明：

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

需要注意的是：

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

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

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

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

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

二、【Using index】

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

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

結(jié)果說明：

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

畫外音：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.

這類SQL語句往往性能較好。

問題來了，什么樣的列數(shù)據(jù)，會包含在索引樹上呢？

三、【Using index condition】

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

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

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

結(jié)果說明：

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

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

這類SQL語句性能也較高，但不如Using index。

問題來了，如何優(yōu)化為Using index呢？

四、【Using filesort】

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

explain select * from user order by sex;

結(jié)果說明：

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

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

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

五、【Using temporary】

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

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

結(jié)果說明：

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

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

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

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

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

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

結(jié)果說明：

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

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

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

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

結(jié)尾：

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