MySql學習筆記（八）：explain之extra

extra主要有是那種情況：Using index、Using filesort、Using temporary、Using where

Using where無需多說，就是使用了where篩選條件。

數據準備：

CREATE?TABLE?`t_blog`?(
??`id`?int(11)?NOT?NULL?auto_increment,
??`title`?varchar(50)?default?NULL,
??`typeId`?int(11)?default?NULL,
??`a`?int(11)?default?'0',
??PRIMARY?KEY??(`id`),
??KEY?`index_1`?(`title`,`typeId`)
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8

1、Using index

表示在查詢中使用了覆蓋索引，避免了掃描表的數據行。

mysql>?EXPLAIN?select?title?from?t_blog;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|?id?|?select_type?|?table??|?type??|?possible_keys?|?key?????|?key_len?|?ref??|?rows?|?Extra???????|
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|??1?|?SIMPLE??????|?t_blog?|?index?|?NULL??????????|?index_1?|?158?????|?NULL?|????7?|?Using?index?|
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1?row?in?set

已知title字段是index_1索引的一部分，上條sql只查詢title字段，只會掃描索引文件而不會掃描表的所有數據行，在extra列中，出現了Using index。

mysql>?EXPLAIN?select?*?from?t_blog;
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
|?id?|?select_type?|?table??|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?Extra?|
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
|??1?|?SIMPLE??????|?t_blog?|?ALL??|?NULL??????????|?NULL?|?NULL????|?NULL?|????7?|???????|
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
1?row?in?set

上條語句中，除了查詢已經加了索引的字段，還查詢了沒有加索引的字段【a】，導致掃描了表的數據行，因此，extra列中沒有出現Using index。

當只出現Using index，沒出現Using where時，表示索引用于讀取數據，以第一條sql為例。

當Using index 和 Using where同時出現時，表示索引用于查找動作，例如：

mysql>?EXPLAIN?select?title?from?t_blog?where?title?=?'java';
+----+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+
|?id?|?select_type?|?table??|?type?|?possible_keys?|?key?????|?key_len?|?ref???|?rows?|?Extra????????????????????|
+----+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+
|??1?|?SIMPLE??????|?t_blog?|?ref??|?index_1???????|?index_1?|?153?????|?const?|????1?|?Using?where;?Using?index?|
+----+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+
1?row?in?set

2、Using filesort

Using filesort通常出現在order by，當試圖對一個不是索引的字段進行排序時，mysql就會自動對該字段進行排序，這個過程就稱為“文件排序”

mysql>?EXPLAIN?select?*?from?t_blog?order?by?title;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
|?id?|?select_type?|?table??|?type??|?possible_keys?|?key?????|?key_len?|?ref??|?rows?|?Extra?|
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
|??1?|?SIMPLE??????|?t_blog?|?index?|?NULL??????????|?index_1?|?158?????|?NULL?|????7?|???????|
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
1?row?in?set

已知title是index_1索引中的第一列索引，所以單獨使用時索引生效，在排序時根據索引排序，不會產生文件排序。

mysql>?EXPLAIN?select?*?from?t_blog?order?by?typeId;
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
|?id?|?select_type?|?table??|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?Extra??????????|
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
|??1?|?SIMPLE??????|?t_blog?|?ALL??|?NULL??????????|?NULL?|?NULL????|?NULL?|????7?|?Using?filesort?|
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
1?row?in?set

雖然typeId是index_1索引的第二列，但由于缺失第一列，所以索引失效。在排序時無法根據索引排序，故mysql會自動進行排序，產生文件排序。

mysql>?EXPLAIN?select?*?from?t_blog?order?by?a;
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
|?id?|?select_type?|?table??|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?Extra??????????|
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
|??1?|?SIMPLE??????|?t_blog?|?ALL??|?NULL??????????|?NULL?|?NULL????|?NULL?|????7?|?Using?filesort?|
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
1?row?in?set

字段a上沒有任何索引，所以在排序時無法根據索引排序，因此產生文件排序。

Using filesort出現的情況：排序時無法根據索引進行排序，mysql優化器只能自己進行排序，這種情況會大大降低性能，不可取。

3、Using temporary

表示在查詢過程中產生了臨時表用于保存中間結果。mysql在對查詢結果進行排序時會使用臨時表，常見于group by。

group by的實質是先排序后分組，同order by一樣，group by和索引息息相關。

試圖對一個沒有索引的字段進行分組，會產生臨時表：

mysql>?EXPLAIN?select?title?from?t_blog?group?by?typeId;
+----+-------------+--------+-------+---------------+---------+---------+------+------+----------------------------------------------+
|?id?|?select_type?|?table??|?type??|?possible_keys?|?key?????|?key_len?|?ref??|?rows?|?Extra????????????????????????????????????????|
+----+-------------+--------+-------+---------------+---------+---------+------+------+----------------------------------------------+
|??1?|?SIMPLE??????|?t_blog?|?index?|?NULL??????????|?index_1?|?158?????|?NULL?|????7?|?Using?index;?Using?temporary;?Using?filesort?|
+----+-------------+--------+-------+---------------+---------+---------+------+------+----------------------------------------------+
1?row?in?set

對一個有索引的字段進行分組就不會產生臨時表：

mysql>?EXPLAIN?select?title?from?t_blog?group?by?title,typeId;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|?id?|?select_type?|?table??|?type??|?possible_keys?|?key?????|?key_len?|?ref??|?rows?|?Extra???????|
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|??1?|?SIMPLE??????|?t_blog?|?index?|?NULL??????????|?index_1?|?158?????|?NULL?|????7?|?Using?index?|
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1?row?in?set

當order by子句和group by子句的字段相同時不會產生臨時表：

mysql>?explain?select?*?from?t_blog?b?left?join?t_type?t?on?b.typeId?=?t.id?group?by?b.id?order?by?b.id;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
|?id?|?select_type?|?table?|?type???|?possible_keys?|?key?????|?key_len?|?ref???????????|?rows?|?Extra?|
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
|??1?|?SIMPLE??????|?b?????|?index??|?NULL??????????|?PRIMARY?|?4???????|?NULL??????????|????7?|???????|
|??1?|?SIMPLE??????|?t?????|?eq_ref?|?PRIMARY???????|?PRIMARY?|?4???????|?blog.b.typeId?|????1?|???????|
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
2?rows?in?set

當order by子句和group by子句的字段不同時就會產生臨時表：

mysql>?explain?select?*?from?t_blog?b?left?join?t_type?t?on?b.typeId?=?t.id?group?by?b.id?order?by?b.title;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-----------------+
|?id?|?select_type?|?table?|?type???|?possible_keys?|?key?????|?key_len?|?ref???????????|?rows?|?Extra???????????|
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-----------------+
|??1?|?SIMPLE??????|?b?????|?index??|?NULL??????????|?index_1?|?158?????|?NULL??????????|????7?|?Using?temporary?|
|??1?|?SIMPLE??????|?t?????|?eq_ref?|?PRIMARY???????|?PRIMARY?|?4???????|?blog.b.typeId?|????1?|?????????????????|
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-----------------+
2?rows?in?set

當時用left join時，若order by子句和group by子句都來自于從表時會產生臨時表：

mysql>?explain?select?*?from?t_blog?b?left?join?t_type?t?on?b.typeId?=?t.id?group?by?t.id?order?by?t.id;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+---------------------------------+
|?id?|?select_type?|?table?|?type???|?possible_keys?|?key?????|?key_len?|?ref???????????|?rows?|?Extra???????????????????????????|
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+---------------------------------+
|??1?|?SIMPLE??????|?b?????|?ALL????|?NULL??????????|?NULL????|?NULL????|?NULL??????????|????7?|?Using?temporary;?Using?filesort?|
|??1?|?SIMPLE??????|?t?????|?eq_ref?|?PRIMARY???????|?PRIMARY?|?4???????|?blog.b.typeId?|????1?|?????????????????????????????????|
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+---------------------------------+
2?rows?in?set

mysql>?explain?select?*?from?t_blog?b?left?join?t_type?t?on?b.typeId?=?t.id?group?by?t.id?order?by?t.name;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+---------------------------------+
|?id?|?select_type?|?table?|?type???|?possible_keys?|?key?????|?key_len?|?ref???????????|?rows?|?Extra???????????????????????????|
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+---------------------------------+
|??1?|?SIMPLE??????|?b?????|?ALL????|?NULL??????????|?NULL????|?NULL????|?NULL??????????|????7?|?Using?temporary;?Using?filesort?|
|??1?|?SIMPLE??????|?t?????|?eq_ref?|?PRIMARY???????|?PRIMARY?|?4???????|?blog.b.typeId?|????1?|?????????????????????????????????|
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+---------------------------------+
2?rows?in?set

出現Using temporary意味著產生了臨時表存儲中間結果并且最后刪掉了該臨時表，這個過程很消耗性能。