explain 可以分析 select 語句的執行,即 MySQL 的“執行計劃。
一、type 列
MySQL 在表裡找到所需行的方式。包括(由左至右,由最差到最好): | All | index | range | ref | eq_ref | const,system | null |
ALL(所有) 全表掃描,MySQL 從頭到尾掃描整張表查找行。 mysql> explain select * from a\G ...
type: ALL
如果加上 limit 如 select * from a limit 100 MySQL 會掃描 100 行,但掃描方式不會變,還是從頭到尾掃描。
index(索引) 根據索引來讀取資料,如果索引已包含了查詢資料,隻需掃描索引樹,否則執行全表掃描和All類似;
create table a(a_id int not null, key(a_id)); insert into a value(1),(2); mysql> explain select a_id from a\G
...
type: index
range(範圍) 以範圍的形式掃描索引 建表: create table a(a_id int not null, key(a_id)); insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10); mysql> explain select * from a where a_id > 1\G
...
type: range ...
IN 比較符也會用 range 表示: mysql> explain select * from a where a_id in (1,3,4)\G
...
type: range
...
` ref(引用) 非唯一性索引通路 建表: create table a(a_id int not null, key(a_id)); insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10); mysql> explain select * from a where a_id=1\G
...
type: ref ...
eq_ref(等值引用) 使用有唯一性索引查找(主鍵或唯一性索引) 建表及插入資料: create table a(id int primary key); create table a_info(id int primary key, title char(1)); insert into a value(1),(2); insert into a_info value(1, 'a'),(2, 'b'); mysql> explain select * from a join a_info using(id);
...+--------+--------+...
...| table | type |...
...+--------+--------+...
...| a | index |...
...| a_info | eq_ref |...
...+--------+--------+... 此時 a_info 每條記錄與 a 一一對應,通過主鍵 id 關聯起來,是以 a_info 的 type 為 eq_ref。 删除 a_info 的主鍵:ALTER TABLE `a_info` DROP PRIMARY KEY; 現在 a_info 已經沒有索引了: mysql> explain select * from a join a_info using(id);
+----+...+--------+--------+... | id |...| table | type |... +----+...+--------+--------+... | 1 |...| a_info | ALL |... | 1 |...| a | eq_ref |... +----+...+--------+--------+... 這次 MySQL 調整了執行順序,先全表掃描 a_info 表,再對表 a 進行 eq_ref 查找,因為 a 表 id 還是主鍵。 删除 a 的主鍵:alter table a drop primary key; 現在 a 也沒有索引了: mysql> explain select * from a join a_info using(id);
...+--------+------+...
...| table | type |...
...+--------+------+...
...| a | ALL |...
...| a_info | ALL |...
...+--------+------+... 現在兩個表都使用全表掃描了。
建表及插入資料: create table a(id int primary key); create table a_info(id int, title char(1), key(id)); insert into a value(1),(2); insert into a_info value(1, 'a'),(2, 'b'); 現在 a_info 表 id 列變為普通索引(非唯一性索引): mysql> explain select * from a join a_info using(id) where a.id=1;
...+--------+-------+...
...| table | type |...
...+--------+-------+...
...| a | const |...
...| a_info | ref |...
...+--------+-------+... a_info 表 type 變為 ref 類型了。 是以,唯一性索引才會出現 eq_ref (非唯一性索引會出現 ref ),因為唯一,是以最多隻傳回一條記錄,找到後無需繼續查找,是以比 ref 更快。
const(常量連接配接) 被稱為“常量”,這個詞不好了解,不過出現 const 的話就表示發生下面兩種情況: 在整個查詢過程中這個表最多隻會有一條比對的行,比如主鍵 id=1 就肯定隻有一行,隻需讀取一次表資料便能取得所需的結果,且表資料在分解執行計劃時讀取。 傳回值直接放在 select 語句中,類似 select 1 AS f 。可以通過 extended 選擇檢視内部過程:
建表及插入資料: create table a(id int primary key, c1 char(20) not null, c2 text not null, c3 text not null); insert into a values(1, 'asdfasdf', 'asdfasdf', 'asdfasdf'), (2, 'asdfasdf', 'asdfasdf', 'asdfasdf'); mysql> explain extended select * from a where id=1\G
...
type: const
possible_keys: PRIMARY
key: PRIMARY
... 用 show warnings 檢視 MySQL 是如何優化的: mysql> show warnings\G
...
Message: select '1' AS `id`,'asdfasdf' AS `c1`,'asdfasdf' AS `c2`,'asdfasdf' AS
`c3` from `test`.`a` where 1 查詢傳回的結果為: mysql> select * from a where id=1;
+----+----------+----------+----------+
| id | c1 | c2 | c3 |
+----+----------+----------+----------+
| 1 | asdfasdf | asdfasdf | asdfasdf |
+----+----------+----------+----------+ 可以看出,傳回結果中的字段值都以“值 AS 字段名”的形式直接出現在優化後的 select 語句中。 修改一下查詢: mysql> explain select * from a where id in(1,2)\G
...
type: range ... 當傳回結果超過 1 條時, type 便不再為 const 了。 重建立表及插入資料: create table a (id int not null); insert into a value(1),(2),(3); mysql> explain select * from a where id=1\G
...
type: ALL 目前表中隻有一條 id=1 的記錄,但 type 已為 ALL ,因為隻有唯一性索引才能保證表中最多隻有一條記錄,隻有這樣 type 才有可能為 const 。 為 id 加普通索引後, type 變為 ref ,改為加唯一或主鍵索引後, type 便變為 const 了。
二、Extra 列
Extra表示附加資訊,常見的有如下幾種(也按查詢效率從高到低排列):
- Using index:表示使用索引,如果隻有 Using index,說明他沒有查詢到資料表,隻用索引表就完成了這個查詢,這個叫覆寫索引。如果同時出現Using where,代表使用索引來查找讀取記錄, 也是可以用到索引的,但是需要查詢到資料表。
- Using where:表示條件查詢,如果不讀取表的所有資料,或不是僅僅通過索引就可以擷取所有需要的資料,則會出現 Using where。如果type列是ALL或index,而沒有出現該資訊,則你有可能在執行錯誤的查詢:傳回所有資料。
- Using filesort:不是“使用檔案索引”的含義!filesort是MySQL所實作的一種排序政策,通常在使用到排序語句ORDER BY的時候,會出現該資訊。
- Using temporary:表示為了得到結果,使用了臨時表,這通常是出現在多表聯合查詢,結果排序的場合。
如果EXPLAIN出現後面兩個資訊(Using filesort,Using temporary),而rows又比較大,通常意味着你需要調整查詢語句,或者需要添加索引,總之需要盡量消除這兩個資訊。