如何利用工具，迅猛定位低效SQL？ | 1分鐘系列
兩個案例分析，展現(xiàn)了MySQL性能分析工具explain的強大。
詳細敘述了explain結果中最重要的type字段(連接類型)的含義。
其實，explain結果中還有一個Extra字段，對分析與優(yōu)化SQL有很大的幫助，今天花1分鐘簡單和大家聊一聊。
數(shù)據(jù)準備：
- 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普通索引存在重復記錄lisi;
實驗目的：
通過構造各類SQL語句，對explain的Extra字段進行說明，啟發(fā)式定位待優(yōu)化低性能SQL語句。
一、【Using where】
實驗語句：
- explain select * from user where sex='no';
結果說明：
Extra為Using where說明，SQL使用了where條件過濾數(shù)據(jù)。
需要注意的是：
- 返回所有記錄的SQL，不使用where條件過濾數(shù)據(jù)，大概率不符合預期，對于這類SQL往往需要進行優(yōu)化;
- 使用了where條件的SQL，并不代表不需要優(yōu)化，往往需要配合explain結果中的type(連接類型)來綜合判斷;
畫外音：join type在《同一個SQL語句，為啥性能差異咋就這么大呢?》一文中有詳細敘述，本文不再展開。
本例雖然Extra字段說明使用了where條件過濾，但type屬性是ALL，表示需要掃描全部數(shù)據(jù)，仍有優(yōu)化空間。
常見的優(yōu)化方法為，在where過濾屬性上添加索引。
畫外音：本例中，sex字段區(qū)分度不高，添加索引對性能提升有限。
二、【Using index】
實驗語句：
- explain select id,name from user where name='shenjian';
結果說明：
Extra為Using index說明，SQL所需要返回的所有列數(shù)據(jù)均在一棵索引樹上，而無需訪問實際的行記錄。
畫外音：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】
實驗語句：
- explain select id,name,sex from user
- where name='shenjian';
畫外音：該SQL語句與上一個SQL語句不同的地方在于，被查詢的列，多了一個sex字段。
結果說明：
Extra為Using index condition說明，確實命中了索引，但不是所有的列數(shù)據(jù)都在索引樹上，還需要訪問實際的行記錄。
畫外音：聚集索引，普通索引的底層實現(xiàn)差異，詳見《1分鐘了解MyISAM與InnoDB的索引差異》。
這類SQL語句性能也較高，但不如Using index。
問題來了，如何優(yōu)化為Using index呢?
四、【Using filesort】
實驗語句：
- explain select * from user order by sex;
結果說明：
Extra為Using filesort說明，得到所需結果集，需要對所有記錄進行文件排序。
這類SQL語句性能極差，需要進行優(yōu)化。
典型的，在一個沒有建立索引的列上進行了order by，就會觸發(fā)filesort，常見的優(yōu)化方案是，在order by的列上添加索引，避免每次查詢都全量排序。
五、【Using temporary】
實驗語句：
- explain select * from user group by name order by sex;
結果說明：
Extra為Using temporary說明，需要建立臨時表(temporary table)來暫存中間結果。
這類SQL語句性能較低，往往也需要進行優(yōu)化。
典型的，group by和order by同時存在，且作用于不同的字段時，就會建立臨時表，以便計算出最終的結果集。
六、【Using join buffer (Block Nested Loop)】
實驗語句：
- explain select * from user where id in(select id from user where sex='no');
結果說明：
Extra為Using join buffer (Block Nested Loop)說明，需要進行嵌套循環(huán)計算。
畫外音：內(nèi)層和外層的type均為ALL，rows均為4，需要循環(huán)進行4*4次計算。
這類SQL語句性能往往也較低，需要進行優(yōu)化。
典型的，兩個關聯(lián)表join，關聯(lián)字段均未建立索引，就會出現(xiàn)這種情況。常見的優(yōu)化方案是，在關聯(lián)字段上添加索引，避免每次嵌套循環(huán)計算。
結尾：
explain是SQL優(yōu)化中最常用的工具，搞定type和Extra，explain也就基本搞定了。
- 《MySQL explain，type分析》進行了常見type分析
- 本文進行了常見Extra分析
- 《兩個工具分析SQL死鎖》和《SQL空值帶來的大坑》是兩篇典型案例分析
- 《MyISAM與InnoDB的索引差異》是InnoDB和MyISAM索引差異分析
- 《數(shù)據(jù)庫索引，到底是什么做的?》是索引底層實現(xiàn)分析
以上幾篇文章，強烈建議大家讀透。
【本文為51CTO專欄作者“58沈劍”原創(chuàng)稿件，轉載請聯(lián)系原作者】
戳這里，看該作者更多好文
