如何利用工具，迅猛定位低效SQL？ | 1分鐘系列
兩個(gè)案例分析，展現(xiàn)了MySQL性能分析工具explain的強(qiáng)大。
《同一個(gè)SQL語(yǔ)句，為啥性能差異咋就這么大呢?》
詳細(xì)敘述了explain結(jié)果中最重要的type字段(連接類型)的含義。
其實(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ō)明：
- 用戶表：id主鍵索引，name普通索引(非唯一)，sex無(wú)索引;
- 四行記錄：其中name普通索引存在重復(fù)記錄lisi;
實(shí)驗(yàn)?zāi)康模?/strong>
通過(guò)構(gòu)造各類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ì)于這類SQL往往需要進(jìn)行優(yōu)化;
- 使用了where條件的SQL，并不代表不需要優(yōu)化，往往需要配合explain結(jié)果中的type(連接類型)來(lái)綜合判斷;
畫(huà)外音：join type在《同一個(gè)SQL語(yǔ)句，為啥性能差異咋就這么大呢?》一文中有詳細(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.
這類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ǔ)句不同的地方在于，被查詢的列，多了一個(gè)sex字段。
結(jié)果說(shuō)明：
Extra為Using index condition說(shuō)明，確實(shí)命中了索引，但不是所有的列數(shù)據(jù)都在索引樹(shù)上，還需要訪問(wèn)實(shí)際的行記錄。
畫(huà)外音：聚集索引，普通索引的底層實(shí)現(xiàn)差異，詳見(jiàn)《1分鐘了解MyISAM與InnoDB的索引差異》。
這類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)行文件排序。
這類SQL語(yǔ)句性能極差，需要進(jìn)行優(yōu)化。
典型的，在一個(gè)沒(méi)有建立索引的列上進(jìn)行了order by，就會(huì)觸發(fā)filesort，常見(jiàn)的優(yōu)化方案是，在order by的列上添加索引，避免每次查詢都全量排序。
五、【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é)果。
這類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ì)算。
這類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也就基本搞定了。
- 《MySQL explain，type分析》進(jìn)行了常見(jiàn)type分析
- 本文進(jìn)行了常見(jiàn)Extra分析
- 《兩個(gè)工具分析SQL死鎖》和《SQL空值帶來(lái)的大坑》是兩篇典型案例分析
- 《MyISAM與InnoDB的索引差異》是InnoDB和MyISAM索引差異分析
- 《數(shù)據(jù)庫(kù)索引，到底是什么做的?》是索引底層實(shí)現(xiàn)分析
以上幾篇文章，強(qiáng)烈建議大家讀透。
【本文為51CTO專欄作者“58沈劍”原創(chuàng)稿件，轉(zhuǎn)載請(qǐng)聯(lián)系原作者】
戳這里，看該作者更多好文
