MySql學(xué)習(xí)筆記（八）：explain之extra

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

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

數(shù)據(jù)準(zhǔn)備：

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

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

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字段，只會(huì)掃描索引文件而不會(huì)掃描表的所有數(shù)據(jù)行，在extra列中，出現(xiàn)了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

上條語句中，除了查詢已經(jīng)加了索引的字段，還查詢了沒有加索引的字段【a】，導(dǎo)致掃描了表的數(shù)據(jù)行，因此，extra列中沒有出現(xiàn)Using index。

當(dāng)只出現(xiàn)Using index，沒出現(xiàn)Using where時(shí)，表示索引用于讀取數(shù)據(jù)，以第一條sql為例。

當(dāng)Using index 和 Using where同時(shí)出現(xiàn)時(shí)，表示索引用于查找動(dòng)作，例如：

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通常出現(xiàn)在order by，當(dāng)試圖對(duì)一個(gè)不是索引的字段進(jìn)行排序時(shí)，mysql就會(huì)自動(dòng)對(duì)該字段進(jìn)行排序，這個(gè)過程就稱為“文件排序”

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索引中的第一列索引，所以單獨(dú)使用時(shí)索引生效，在排序時(shí)根據(jù)索引排序，不會(huì)產(chǎn)生文件排序。

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索引的第二列，但由于缺失第一列，所以索引失效。在排序時(shí)無法根據(jù)索引排序，故mysql會(huì)自動(dòng)進(jìn)行排序，產(chǎn)生文件排序。

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上沒有任何索引，所以在排序時(shí)無法根據(jù)索引排序，因此產(chǎn)生文件排序。

Using filesort出現(xiàn)的情況：排序時(shí)無法根據(jù)索引進(jìn)行排序，mysql優(yōu)化器只能自己進(jìn)行排序，這種情況會(huì)大大降低性能，不可取。

3、Using temporary

表示在查詢過程中產(chǎn)生了臨時(shí)表用于保存中間結(jié)果。mysql在對(duì)查詢結(jié)果進(jìn)行排序時(shí)會(huì)使用臨時(shí)表，常見于group by。

group by的實(shí)質(zhì)是先排序后分組，同order by一樣，group by和索引息息相關(guān)。

試圖對(duì)一個(gè)沒有索引的字段進(jìn)行分組，會(huì)產(chǎn)生臨時(shí)表：

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

對(duì)一個(gè)有索引的字段進(jìn)行分組就不會(huì)產(chǎn)生臨時(shí)表：

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

當(dāng)order by子句和group by子句的字段相同時(shí)不會(huì)產(chǎn)生臨時(shí)表：

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

當(dāng)order by子句和group by子句的字段不同時(shí)就會(huì)產(chǎn)生臨時(shí)表：

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

當(dāng)時(shí)用left join時(shí)，若order by子句和group by子句都來自于從表時(shí)會(huì)產(chǎn)生臨時(shí)表：

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

出現(xiàn)Using temporary意味著產(chǎn)生了臨時(shí)表存儲(chǔ)中間結(jié)果并且最后刪掉了該臨時(shí)表，這個(gè)過程很消耗性能。