EXPLAIN:查看SQL語句的執(zhí)行計劃
EXPLAIN命令可以幫助我們深入了解MySQL基于開銷的優(yōu)化器,還可以獲得很多可能被優(yōu)化器考慮到的訪問策略的細節(jié),以及當運行SQL語句時哪種策略預計會被優(yōu)化器采用,在優(yōu)化慢查詢時非常有用。
執(zhí)行explain之后結果集包含如下信息
下面將對每一個值進行解釋
1、id
id用來標識整個查詢中SELELCT語句的順序,在嵌套查詢中id越大的語句越先執(zhí)行,該值可能為NULL
id如果相同,從上往下依次執(zhí)行。id不同,id值越大,執(zhí)行優(yōu)先級越高,如果行引用其他行的并集結果,則該值可以為NULL
2、select_type
select_type表示查詢使用的類型,有下面幾種:
simple: 簡單的select查詢,沒有union或者子查詢
primary: 最外層的select查詢
union: union中的第二個或隨后的select查詢,不依賴于外部查詢的結果集
dependent union: union中的第二個或隨后的select查詢,依賴于外部查詢的結果集
subquery: 子查詢中的第一個select查詢,不依賴與外部查詢的結果集
dependent subquery: 子查詢中的第一個select查詢,依賴于外部查詢的結果集
derived: 用于from子句中有子查詢的情況,mysql會遞歸執(zhí)行這些子查詢,此結果集放在臨時表中
3、table
table用來表示輸出行所引用的表名
4、type(重要)
type表示訪問類型,下面依次解釋各種類型,類型順序從最好到最差排列
system: 表僅有一行,是const類型的一個特例
因為子查詢只有一行數(shù)據(jù),模擬了單表只有一行數(shù)據(jù),此時type為system
const: 確定只有一行匹配的時候,mysql優(yōu)化器會在查詢前讀取它并且只讀取一次,速度非???/p>
eq_ref: 對于每個來自于前面的表的行組合,從該表中讀取一行,常用在一個索引是unique key或者primary key
ref: 對于來自前面的表的行組合,所有有匹配索引值的行都從這張表中讀取,如果聯(lián)接只使用鍵的最左邊的前綴,或如果鍵不是UNIQUE或PRIMARY KEY(換句話說,如果聯(lián)接不能基于關鍵字選擇單個行的話),則使用ref
ref可以用于使用=或<=>操作符的帶索引的列
test表bnet_id不是索引,test2表aid為索引列
ref_or_null: 類似ref,但是添加了可以專門搜索null值的行
前提為bnet_id列為索引,且bnet_id列有null值
index_merge: 該訪問類型使用了索引合并優(yōu)化方法,key列包含了使用的索引的清單,key_len包含了使用的索引的最長的關鍵元素
前提條件為id列和bnet_id列都有單列索引。如果出現(xiàn)index_merge,并且這類SQL后期使用較頻繁,可以考慮把單列索引換為組合索引,這樣效率更高
range: 只檢索給定范圍的行,使用一個索引來選擇行。key列顯示使用了哪個索引。key_len包含所使用索引的最長關鍵元素。在該類型中ref列為NULL
當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比較關鍵字列時,可以使用range
前提條件為bnet_id列有索引
index: 在進行統(tǒng)計時非常常見,此聯(lián)接類型實際上會掃描索引樹
all: 對于每個來自于先前的表的行組合,進行完整的表掃描,通常可以增加更多的索引而不要使用ALL,使得行能基于前面的表中的常數(shù)值或列值被檢索出
5、possible_keys
possible_keys是指在這個SQL中,mysql可以使用這個索引去輔助查找記錄,當查詢涉及到的字段,都會被列出,但不一定被查詢使用.若為空則表示沒有可以使用的索引,此時可以通過檢查where語句看是否可以引用某些列或者新建索引來提高性能。
6、key(重要)
key列顯示的是當前表實際使用的索引,如果沒有選擇索引,則此列為null,要想強制MySQL使用或忽視possible_keys列中的索引,在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX
7、key_len
key_len列顯示MySQL決定使用的鍵長度。如果KEY鍵是NULL,則長度為NULL。在不損失精確性的情況下,長度越短越好
key len的長度還和字符集有關,latin1一個字符占用1個字節(jié),gbk一個字符占用2個字節(jié),utf8一個字符占用3個字節(jié)。key_len的計算法方法:
列類型 | 長度 | 備注 |
---|---|---|
id int | 4+1 | int為4bytes,允許為NULL,加1byte |
id bigint not null | 8 | bigint為8bytes |
user char(30) utf8 | 30*3+1 | utf8每個字符為3bytes,允許為NULL,加1byte |
user varchar(30) not null utf8 | 30*3+2 | utf8每個字符為3bytes,變長數(shù)據(jù)類型,加2bytes |
user varchar(30) utf8 | 30*3+2+1 | utf8每個字符為3bytes,允許為NULL,加1byte,變長數(shù)據(jù)類型,加2bytes |
detail text(10) utf8 | 30*3+2+1 | TEXT截取部分,被視為動態(tài)列類型。 |
key_len只指示了where中用于條件過濾時被選中的索引列,是不包含order by或group by這一部分被選中的索引列
8、ref
ref列用來顯示使用哪個列或常數(shù)與key一起從表中選擇相應的行。它顯示的列的名字(或const),此列多數(shù)時候為null
9、rows
rows列顯示的是mysql解析器認為執(zhí)行此SQL時必須掃描的行數(shù)。此數(shù)值為一個預估值,不是具體值,通常比實際值小
10、filtered
此參數(shù)為mysql 5.7 新加參數(shù),指的是返回結果的行數(shù)所占需要讀到的行(rows的值)的比例
對于使用join時,前一個表的結果集大小直接影響了循環(huán)的行數(shù)
11、extra(重要)
extra表示不在其他列并且也很重要的額外信息
using index: 該值表示這個SQL語句使用了覆蓋索引(覆蓋索引是指可以直接在索引列中得到想要的結果,而不用去回表),此時效率最高
這個例子中id字段為主鍵,但是key那里顯示走的并不是主鍵索引,這個是因為mysql的所有二級索引中都會包含所有的主鍵信息,而mysql沒有單獨的存儲主鍵索引,所以掃描二級索引的開銷比全表掃描更快
using where: 表示存儲引擎搜到記錄后進行了后過濾(POST-FILTER),如果查詢未能使用索引,using where的作用只是提醒我們mysql要用where條件過濾結果集
using temporary 表示mysql需要使用臨時表來存儲結果集,常見于排序和分組查詢
using filesort: 是指mysql無法利用索引直接完成排序(排序的字段不是索引字段),此時會用到緩沖空間來進行排序
using join buffer: 強調在獲取連接條件時沒有用到索引,并且需要連接緩沖區(qū)來存儲中間結果。(性能可以通過添加索引或者修改連接字段改進)
Block Nested Loop是指Block Nested-Loop Join算法:將外層循環(huán)的行/結果集存入join buffer, 內層循環(huán)的每一行與整個buffer中的記錄做比較,從而減少內層循環(huán)的次數(shù).
impossible where: 表示where條件導致沒有返回的行
using index condition: 是mysql 5.6 之后新加的特性,結合mysql的ICP(Index Condition Pushdown)特性使用。主要是優(yōu)化了可以在索引(僅限二級索引)上進行 like 查找
如果extra中出現(xiàn)多個上面結果,則表示順序使用上面的方法進行解析查詢
【責任編輯:龐桂玉 TEL:(010)68476606】