MySQL?EXPLAIN執(zhí)行計(jì)劃解析
前言
調(diào)用EXPLAIN可以獲取關(guān)于查詢(xún)執(zhí)行計(jì)劃的信息,以及如何解釋輸出。EXPLAIN命令是查看查詢(xún)優(yōu)化器如何決定執(zhí)行查詢(xún)的主要方法,但該動(dòng)能也有局限性,它的選擇并不總是最優(yōu)的,展示的也并不一定是真相。
1 調(diào)用EXPLAIN
要使用EXPLAIN,只需要在SELECT 關(guān)鍵字之前增加 EXPLAIN這個(gè)詞。MySQL會(huì)在查詢(xún)上設(shè)置一個(gè)標(biāo)記。當(dāng)執(zhí)行查詢(xún)時(shí),這個(gè)標(biāo)記會(huì)使其返回關(guān)于在執(zhí)行計(jì)劃中每一步的信息,而不是真正完全的執(zhí)行該語(yǔ)句。
它會(huì)返回一行或多行信息,顯示出執(zhí)行計(jì)劃中的每一部分和執(zhí)行的次序。在查詢(xún)中,每個(gè)表的輸出只有一行,若多表關(guān)聯(lián),則輸出多行。別名表單算為一個(gè)表,因此如果把表和自己連接,輸出中也會(huì)有兩行。這里的表的定義非常的廣:可以是一個(gè)子查詢(xún),一個(gè) UNION 結(jié)果。
EXPLAIN有兩個(gè)變種:
EXPLAIN EXTENDED
:看起來(lái)和正常的EXPLAIN行為一樣,但他會(huì)告訴服務(wù)器“逆向編譯”執(zhí)行計(jì)劃為一個(gè) SELECT 語(yǔ)句(SHOW WARNINGS 后能看到),該命令在MySQL5.0之后可用,MySQL5.1開(kāi)始還額外增加一個(gè) filtered 列。EXPLAIN PARTITIONS
:如果查詢(xún)基于分區(qū)表的話(huà),將顯示查詢(xún)將訪(fǎng)問(wèn)的分區(qū)。MySQL5.1以及更新的版本支持。
增加了EXPLAIN之后,MySQL可能仍然會(huì)執(zhí)行部分查詢(xún),如果查詢(xún)中FROM字句中包括子查詢(xún),那么MySQL實(shí)際會(huì)執(zhí)行子查詢(xún)的,并將其結(jié)果放在一個(gè)臨時(shí)表中,然后完成外層查詢(xún)優(yōu)化。
EXPLAIN 返回的只是個(gè)近似結(jié)果,并且還有相關(guān)是的限制:
- 不會(huì)告訴你知道觸發(fā)器、存儲(chǔ)過(guò)程或 UDF 如何影響查詢(xún)。
- 不支持存儲(chǔ)過(guò)程,盡管可以單獨(dú)抽取查詢(xún)進(jìn)行 EXPLAIN。
- 不會(huì)告訴你查詢(xún)執(zhí)行中所做的特定優(yōu)化。
- 不會(huì)顯示關(guān)于查詢(xún)的執(zhí)行計(jì)劃的所有信息。
- 無(wú)法區(qū)分具有相同名字的事物,例如,它對(duì)內(nèi)存排序和臨時(shí)文件排序都使用“filesort”,并且對(duì)磁盤(pán)上和內(nèi)存中的臨時(shí)表都顯示“Using temporary”。
- 可能會(huì)誤導(dǎo),例如:可能會(huì)對(duì)一個(gè)很小的limit查詢(xún)顯示全索引掃描。
- 只能解釋select查詢(xún)(5.6以后允許解釋非select語(yǔ)句),不會(huì)對(duì)存儲(chǔ)過(guò)程調(diào)用和INSERT、DELETE、UPDATE或其他語(yǔ)句做解釋?zhuān)赏ㄟ^(guò)重寫(xiě)某些非 SELECT 查詢(xún)以利用 EXPLAIN。
2 EXPLAIN中的列
2.1 id
一個(gè)編號(hào),表示select所屬的行。如果查詢(xún)中沒(méi)有子查詢(xún)或關(guān)聯(lián)查詢(xún),那么只會(huì)有唯一的SELECT,每一行的該列中都將顯示一個(gè)1,否則,內(nèi)層的SELECT語(yǔ)句一般會(huì)順序編號(hào),對(duì)應(yīng)于其在原始語(yǔ)句中的位置。id越大執(zhí)行優(yōu)先級(jí)越高,id相同則認(rèn)為是一組,從上往下執(zhí)行,id為NULL最后執(zhí)行。
例如UNION查詢(xún)中最后對(duì)于臨時(shí)表的查詢(xún),它的id就為null,因?yàn)榕R時(shí)表并不在原sql中出現(xiàn)。
EXPLAIN select * from contacts where contact_id <1000 UNION select * from contacts where contact_id >99000
2.2 select_type
表示對(duì)應(yīng)行是簡(jiǎn)單還是復(fù)雜的查詢(xún)。
SIMPLE
,簡(jiǎn)單SELECT,查詢(xún)不包括UNION和子查詢(xún)。PRIMARY
,查詢(xún)中若查詢(xún)包含任何復(fù)雜的子部分,最外層的select被標(biāo)記為PRIMARY。
其他部分標(biāo)記如下:
SUBQUERY
,包含在SELECT子句(不在from子句中)中的子查詢(xún)的SELECT,結(jié)果不依賴(lài)于外部查詢(xún)。DERIVED
,包含在from子句中的子查詢(xún)中的SELECT。MySQL會(huì)遞歸執(zhí)行并將結(jié)果存放在一個(gè)臨時(shí)表中,也稱(chēng)為派生表,因?yàn)樵撆R時(shí)表是從子查詢(xún)中派生來(lái)的。UNION
,UNION中的第二個(gè)或后面的SELECT。第一個(gè)SELECT被標(biāo)記就好像它以部分外查詢(xún)來(lái)執(zhí)行,因此第一個(gè)SELECT可能顯示為PRIMARY。如果UNION被FROM字句中的子查詢(xún)包含,那么它的第一個(gè)SELECT被標(biāo)記為DERIVED。UNION RESULT
,用來(lái)從UNION的匿名臨時(shí)表中檢索結(jié)果的SELECT。
除了上面這些,SUBQUERY和UNION
還可以被標(biāo)記為DEPENDENT和UNCACHEABLE
,DEPENDENT意味著SELECT 依賴(lài)與外層查詢(xún)中發(fā)現(xiàn)的數(shù)據(jù);UNCACHEABLE意味著SELECT 中的某些特性阻止結(jié)果被緩存于一個(gè) Item_cache 中。
EXPLAIN select * from contacts where contact_id =99000
2.3 table
顯示了EXPLAIN對(duì)應(yīng)行正在訪(fǎng)問(wèn)哪個(gè)表。通常情況下,它相當(dāng)表明了:那就是那個(gè)表,或者該表的別名。
可以通過(guò)該列從上到下觀(guān)察MySQL的關(guān)聯(lián)優(yōu)化器為查詢(xún)選擇的關(guān)聯(lián)順序。
當(dāng)from
字句中有子查詢(xún)的時(shí)候,table列是<derivedN>
的形式,N指向子查詢(xún)id,這里N總是指向EXPLAIN輸出結(jié)果中的后面的一行。
當(dāng)有UNION
時(shí),UNION RESULT的table列包含一個(gè)參與UNION的id列表,UNION RESULT總是出現(xiàn)在UNION中所有參與行之后,例如<union 1,2>
。
2.4 type
關(guān)聯(lián)類(lèi)型,或者說(shuō)訪(fǎng)問(wèn)類(lèi)型,該字段表明MySQL決定如何查找表中的行。
常用的訪(fǎng)問(wèn)類(lèi)型如下(性能依次從最差到最優(yōu)):
ALL
:全表掃描,從頭到尾的查找所需要的行。但仍然存在例外,例如使用了LIMIT
,或者Extra
列中顯示 “Using distinct/not exists
”。index
:跟全表掃描一樣,只是MySQL掃描表時(shí)按照索引次序進(jìn)行而不是行,主要優(yōu)點(diǎn)是避免了排序;缺點(diǎn)是要承擔(dān)按索引次序讀取整個(gè)表的開(kāi)銷(xiāo)。這通常意味著如實(shí)按照隨機(jī)次序訪(fǎng)問(wèn)行,開(kāi)銷(xiāo)較大。如果Extra 列中顯示 “using index”,說(shuō)明MySQL正在使用覆蓋索引,這樣就不需要按索引次序訪(fǎng)問(wèn)每一行數(shù)據(jù),開(kāi)小會(huì)少很多。range
:范圍掃描,就是一個(gè)有限制的索引掃描,使用一個(gè)索引來(lái)檢索給定范圍的行,不需要遍歷全部索引。范圍掃描通常出現(xiàn)在between,>,<,>=
等操作中。in()和OR
也會(huì)顯示范圍掃描,但這兩者其實(shí)是不同的訪(fǎng)問(wèn)類(lèi)型,性能上也有差異。此類(lèi)查找的開(kāi)銷(xiāo)根ref
索引訪(fǎng)問(wèn)的開(kāi)銷(xiāo)相當(dāng)。ref
:索引訪(fǎng)問(wèn),也叫索引查找。返回所有匹配某個(gè)單個(gè)值的行,然而它可能會(huì)找到符合條件的多個(gè)行。此類(lèi)索引訪(fǎng)問(wèn)只有當(dāng)使用非唯一性索引或者唯一索引的非唯一性前綴時(shí)才會(huì)發(fā)生。把他叫ref是因?yàn)樗湍硞€(gè)參考值相比較。這個(gè)參考值或者是一個(gè)常數(shù),或者來(lái)自多表查詢(xún)前一個(gè)表里的結(jié)果值。eq_ref
:使用這種索引查找,MySQL清楚的知道最多只返回一條符合條件的記錄,使用主鍵或者唯一值索引查找時(shí)能看到這種方法。MySQL對(duì)于這種訪(fǎng)問(wèn)類(lèi)型的優(yōu)化做得非常好,因?yàn)樗赖綗o(wú)需估計(jì)匹配行的范文或者在找到匹配行后再繼續(xù)查找(因?yàn)橹挡粫?huì)重復(fù))。const,system
:當(dāng)MySQL能對(duì)查詢(xún)的某部分進(jìn)行優(yōu)化并將其轉(zhuǎn)換成一個(gè)常量時(shí),它就會(huì)使用這些訪(fǎng)問(wèn)類(lèi)型。比如通過(guò)將某一行的主鍵訪(fǎng)問(wèn)WHERE字句的方式來(lái)查詢(xún)主鍵:SELECT id from t where id = 1。此時(shí)MySQL就能把這個(gè)查詢(xún)轉(zhuǎn)換為一個(gè)常量。NULL
:這種訪(fǎng)問(wèn)方式意味著MySQ能在優(yōu)化階段分解查詢(xún)語(yǔ)句,在執(zhí)行階段甚至不需要再訪(fǎng)問(wèn)表或者索引。例如,從一個(gè)索引列里選取最小值可以通過(guò)單獨(dú)查詢(xún)索引來(lái)完成,不需要在執(zhí)行時(shí)訪(fǎng)問(wèn)表。index_merge
:索引合并(index merge)。MySQL5.0之前,一個(gè)表一次只能使用一個(gè)索引,無(wú)法同時(shí)使用多個(gè)索引分別進(jìn)行條件掃描。但是從5.1開(kāi)始,引入了索引合并優(yōu)化技術(shù),對(duì)同一個(gè)表可以使用多個(gè)索引分別進(jìn)行條件掃描,然后將它們各自的結(jié)果進(jìn)行合并(intersect/union)。index merge使得我們可以使用到多個(gè)索引同時(shí)進(jìn)行掃描,然后將結(jié)果進(jìn)行合并。聽(tīng)起來(lái)好像是很好的功能,但是如果出現(xiàn)了 index intersect merge,那么一般同時(shí)也意味著我們的索引建立得不太合理,因?yàn)?index intersect merge 是可以通過(guò)建立復(fù)合索引進(jìn)行更一步優(yōu)化的。
2.5 possible_keys
顯示查詢(xún)可以使用哪些索引,這是基于查詢(xún)?cè)L問(wèn)的列和使用的比較操作符來(lái)判斷的。該列表是在優(yōu)化過(guò)程的早期創(chuàng)建的,因此列出來(lái)的索引對(duì)于后續(xù)實(shí)際優(yōu)化過(guò)程可能是沒(méi)有用的。
2.6 key
顯示mysql決定采用哪一個(gè)索引來(lái)優(yōu)化對(duì)該表的訪(fǎng)問(wèn),如果該索引沒(méi)有出現(xiàn)在possible_keys列中,那么MySQL選用它是出于另外的原因——例如,它可能選擇了一個(gè)覆蓋索引,哪怕它沒(méi)有WHERE字句。
possible_keys表示哪些索引有助于高效查找,而key表示該索引可以最小化查詢(xún)成本。
如果沒(méi)有選擇索引,鍵是NULL
。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引,在查詢(xún)中使用FORCE INDEX、USE INDEX或者IGNORE INDEX
。
2.7 key_len
MySQL在索引中使用的字節(jié)數(shù),通過(guò)這個(gè)值可以算出具體使用了索引中的哪些列,計(jì)算時(shí)需要考慮字符集,如果字段允許為 NULL,需要1字節(jié)記錄是否為 NULL。
key_len
顯示的值為索引字段的最大可能長(zhǎng)度,并非實(shí)際使用長(zhǎng)度,即key_len是根據(jù)表定義計(jì)算而得,不是通過(guò)表內(nèi)檢索出的。
2.8 ref
這一列顯示了在key列記錄的索引中,表查找值所用到的列或常量,即哪些列或常量被用于查找索引列上的值。常見(jiàn)的有:const(常量),func,NULL,字段名(例:film.id)
2.9 rows
這一列是mysql估計(jì)要讀取并檢測(cè)的行數(shù),注意這個(gè)不是結(jié)果集里的行數(shù),而是MySQL為了找到符合查詢(xún)的每一個(gè)標(biāo)準(zhǔn)的那些行而必須讀取的行的平均數(shù)。
有時(shí)候該估值可能很不精確,該數(shù)字也反映不了LIMIT字句的真正檢查行數(shù)。
2.10 Extra
這一列展示的是額外信息。常見(jiàn)的重要值如下:
Using index
:表示MySQL將使用覆蓋索引,這發(fā)生在對(duì)表的請(qǐng)求列都是同一索引的部分的時(shí)候,返回的列數(shù)據(jù)只使用了索引中的信息,而沒(méi)有再去訪(fǎng)問(wèn)表中的行記錄。是性能高的表現(xiàn)。Using index condition
:在5.6版本后加入的新特性索引下推(Index Condition Pushdown,ICP),在索引遍歷過(guò)程中,對(duì)索引中包含的字段先做判斷(即使該字段沒(méi)有使用到索引),直接過(guò)濾掉不滿(mǎn)足條件的記錄,減少回表次數(shù)。Using where
:意味著MySQL服務(wù)器將在存儲(chǔ)引擎檢索行后再進(jìn)行過(guò)濾。就是先通過(guò)索引讀取整行數(shù)據(jù),再按 WHRER條件進(jìn)行檢查,符合就留下,不符合就丟棄。查詢(xún)的列未被索引覆蓋。Using temporary
:MySQL需要?jiǎng)?chuàng)建一張臨時(shí)表來(lái)中間結(jié)果并進(jìn)一步處理,比如union、group by、distinct等,出現(xiàn)這種情況一般是要進(jìn)行優(yōu)化的,首先是想到用索引來(lái)優(yōu)化。Using filesort
:MySQL會(huì)對(duì)結(jié)果使用一個(gè)外部索引排序,而不是按索引次序從表里讀取行,即filesort(文件排序)。此時(shí)mysql會(huì)根據(jù)聯(lián)接類(lèi)型瀏覽所有符合條件的記錄,并保存排序關(guān)鍵字和行指針,然后排序關(guān)鍵字并按順序檢索行信息。這種情況下一般也是要考慮使用索引來(lái)優(yōu)化的。filesort有兩種,一種是內(nèi)存排序,一種是磁盤(pán)排序,無(wú)法得知。Distinct
: 一旦MySQL找到了與行相聯(lián)合匹配的行,就不再搜索了,常見(jiàn)于關(guān)聯(lián)查詢(xún)。No tables used
:Query語(yǔ)句中使用from dual 或不含任何from子句。Using join buffer
:使用了連接緩存,join語(yǔ)句用到了緩沖區(qū)。
到此這篇關(guān)于MySQL EXPLAIN執(zhí)行計(jì)劃解析的文章就介紹到這了,更多相關(guān)MySQL EXPLAIN 內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL數(shù)據(jù)庫(kù)事務(wù)隔離級(jí)別介紹(Transaction Isolation Level)
這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)事務(wù)隔離級(jí)別(Transaction Isolation Level) ,需要的朋友可以參考下2014-05-05Mysql?optimize?table?時(shí)報(bào)錯(cuò):Temporary?file?write?fail的解決
這篇文章主要介紹了Mysql?optimize?table?時(shí)報(bào)錯(cuò):Temporary?file?write?fail的解決方案,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2022-09-09MySQL數(shù)據(jù)庫(kù)入門(mén)之多實(shí)例配置方法詳解
這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)入門(mén)之多實(shí)例配置方法,結(jié)合實(shí)例形式分析了MySQL數(shù)據(jù)庫(kù)多實(shí)例配置相關(guān)概念、原理、操作方法與注意事項(xiàng),需要的朋友可以參考下2020-05-05MYSQL 創(chuàng)建函數(shù)出錯(cuò)的解決方案
在程序開(kāi)發(fā)過(guò)程中,大家有沒(méi)有遇到過(guò)mysql函數(shù)不能創(chuàng)建,我是遇到過(guò),是一個(gè)很麻煩的問(wèn)題,上網(wǎng)搜了些相關(guān)資料,整理在一起了,供大家參考,幫助那些需要幫助的朋友2015-08-08MySQL的MaxIdleConns不合理,會(huì)變成短連接的原因
這篇文章主要介紹了MySQL的MaxIdleConns不合理,會(huì)變成短連接的原因,幫助大家更好的理解和使用MySQL數(shù)據(jù)庫(kù),感興趣的朋友可以了解下2021-01-01解決“無(wú)法啟動(dòng)mysql服務(wù) 錯(cuò)誤1069”的方法
本文給大家分享的是小編解決自己網(wǎng)站無(wú)法連接數(shù)據(jù)庫(kù)的時(shí)候遇到的“無(wú)法啟動(dòng)mysql服務(wù) 錯(cuò)誤1069”的方案,有相同需求的小伙伴可以參考下2017-08-08Mysql8.4.3LTS中離線(xiàn)部署的實(shí)現(xiàn)示例
本文詳細(xì)介紹了在Ubuntu 24.04系統(tǒng)上離線(xiàn)部署MySQL 8.4.3 LTS,包括環(huán)境配置、安裝步驟等步驟,對(duì)小白有一定的幫助,感興趣的可以了解一下2024-11-11mysql數(shù)據(jù)庫(kù)limit的四種用法小結(jié)
mysql數(shù)據(jù)庫(kù)中l(wèi)imit子句可以被用于強(qiáng)制select語(yǔ)句返回指定的記錄數(shù),本文主要介紹了mysql數(shù)據(jù)庫(kù)limit的四種用法小結(jié),感興趣的可以了解一下2023-10-10