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

站在用戶的角度思考問(wèn)題，與客戶深入溝通，找到船山網(wǎng)站設(shè)計(jì)與船山網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個(gè)性化、用戶體驗(yàn)好的作品，建站類型包括：網(wǎng)站設(shè)計(jì)制作、做網(wǎng)站、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、國(guó)際域名空間、網(wǎng)絡(luò)空間、企業(yè)郵箱。業(yè)務(wù)覆蓋船山地區(qū)。

Using where無(wú)需多說(shuō)，就是使用了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

上條語(yǔ)句中，除了查詢已經(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è)過(guò)程就稱為“文件排序”

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í)無(wú)法根據(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í)無(wú)法根據(jù)索引排序，因此產(chǎn)生文件排序。

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

3、Using temporary

表示在查詢過(guò)程中產(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子句都來(lái)自于從表時(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?setmysql>?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è)過(guò)程很消耗性能。

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無(wú)理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場(chǎng)景需求。

網(wǎng)頁(yè)名稱：MySql學(xué)習(xí)筆記（八）：explain之extra-創(chuàng)新互聯(lián)
分享路徑：http://weahome.cn/article/dpjsds.html