Mysql 性能優(yōu)化Explain詳解

explain

功能
我們?cè)谌粘Ｊ褂弥?，使用慢查詢找到?zhí)行時(shí)間比較久的查詢，然后使用SHOW STATUS、SHOW PROFILE、和explain做單條語(yǔ)句的分析。
使用explain關(guān)鍵字可以模擬優(yōu)化器執(zhí)行sql查詢語(yǔ)句，從而知道Mysql是如何處理你的sql語(yǔ)句的。分析你的查詢語(yǔ)句或者表結(jié)構(gòu)的性能瓶頸。


具體可以分析哪些

* 表的讀取順序
* 數(shù)據(jù)讀取操作的操作類型
* 哪些索引可以使用
* 哪些索引被實(shí)際使用
* 表之間的引用
* 每張表有多少行被優(yōu)化器查詢

使用語(yǔ)法
explain sql語(yǔ)句



?

?

得到了什么結(jié)果

expain出來(lái)的信息有10列，分別是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra




字段概要解釋：

* id:選擇標(biāo)識(shí)符
* select_type:表示查詢的類型。
* table:輸出結(jié)果集的表
* partitions:匹配的分區(qū)
* type:表示表的連接類型
* possible_keys:表示查詢時(shí)，可能使用的索引
* key:表示實(shí)際使用的索引
* key_len:索引字段的長(zhǎng)度
* ref:列與索引的比較
* rows:掃描出的行數(shù)(估算的行數(shù))
* filtered:按表?xiàng)l件過(guò)濾的行百分比
* Extra:執(zhí)行情況的描述和說(shuō)明
?

字段詳細(xì)解釋

一、 id
SQL執(zhí)行查詢的順序的標(biāo)識(shí)
1. id相同時(shí)，執(zhí)行順序由上至下



2. id不同時(shí)，如果是子查詢，id的序號(hào)會(huì)遞增，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行



3. id相同和不同同時(shí)存在，id如果相同，可以認(rèn)為是一組，從上往下順序執(zhí)行；在所有組中，id值越大，優(yōu)先級(jí)越高，越先執(zhí)行



?

?

二、select_type
查詢中每個(gè)select子句的類型，主要用于區(qū)別普通查詢、聯(lián)合查詢、子查詢等復(fù)雜查詢
(1) SIMPLE(簡(jiǎn)單SELECT，不使用UNION或子查詢等)
(2) PRIMARY(子查詢中最外層查詢，查詢中若包含任何復(fù)雜的子部分，最外層的select被標(biāo)記為PRIMARY)
(3) UNION(UNION中的第二個(gè)或后面的SELECT語(yǔ)句)
(4) DEPENDENT UNION(UNION中的第二個(gè)或后面的SELECT語(yǔ)句，取決于外面的查詢)
(5) UNION RESULT(UNION的結(jié)果，union語(yǔ)句中第二個(gè)select開(kāi)始后面所有select)
(6) SUBQUERY(子查詢中的第一個(gè)SELECT，結(jié)果不依賴于外部查詢)
(7) DEPENDENT SUBQUERY(子查詢中的第一個(gè)SELECT，依賴于外部查詢)
(8) DERIVED(派生表的SELECT, FROM子句的子查詢)
(9) UNCACHEABLE SUBQUERY(一個(gè)子查詢的結(jié)果不能被緩存，必須重新評(píng)估外鏈接的第一行)


三、table
顯示這一步所訪問(wèn)數(shù)據(jù)庫(kù)中表名稱（顯示這一行的數(shù)據(jù)是關(guān)于哪張表的）


四、type

表示MySQL在表中找到所需行的方式，又稱“訪問(wèn)類型”，常見(jiàn)類型如下:

?ALL, index,? range, ref, eq_ref, const, system, NULL

從左到右，性能從最差到最好



?

?一般來(lái)說(shuō)。得保證查詢至少達(dá)到range級(jí)別，最好能達(dá)到ref。

所有的訪問(wèn)類型排序：



system：表只有一行記錄（等于系統(tǒng)表），這是const類型的特別，平時(shí)不會(huì)出現(xiàn)，這個(gè)可以忽略不計(jì)。

const:表示通過(guò)索引一次就找到了，const用于比較primary key或者unique索引，因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以很快
如將主鍵置于where列表中，Mysql就能將該查詢轉(zhuǎn)換為一個(gè)常量

eq_ref：唯一性索引掃描，對(duì)于每個(gè)索引鍵，表只只有一條記錄與之匹配。常見(jiàn)于主鍵或唯一索引掃描


ref：非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行，本質(zhì)上也是一種索引訪問(wèn)，它返回匹配某個(gè)單獨(dú)值的行，然而，它可能會(huì)找到多個(gè)符合條件的行，索引他應(yīng)該屬于查找和掃描的混合體

range：只檢索給定范圍的行，使用一個(gè)索引來(lái)選擇行。
key列顯示使用了哪個(gè)索引，一般就是在你的where語(yǔ)句中出現(xiàn)了between、<、>、in等的查詢
這種范圍掃描索引掃描比全表掃描要好，因?yàn)樗恍枰_(kāi)始于索引的某一點(diǎn)，面結(jié)束另一點(diǎn)，不用掃描全部索引。

index: Full Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹(shù)，這通常比ALL快，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小，

也就是說(shuō)雖然ALL和Index都是讀全表，但I(xiàn)ndex是從索引中讀取的，而ALL都是從磁盤中讀的。

ALL：Full Table Scan， MySQL將遍歷全表以找到匹配的行。NULL:
MySQL在優(yōu)化過(guò)程中分解語(yǔ)句，執(zhí)行時(shí)甚至不用訪問(wèn)表或索引，例如從一個(gè)索引列里選取最小值可以通過(guò)單獨(dú)索引查找完成。

?

五、possible_keys
顯示可能應(yīng)用在這張表中的索引，一個(gè)或多個(gè)。
查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實(shí)際使用。
簡(jiǎn)單來(lái)說(shuō)，mysql認(rèn)為這條查詢可能用到哪些索引。

?

六、Key

key列顯示MySQL實(shí)際決定使用的鍵（索引），必然包含在possible_keys中

如果沒(méi)有選擇索引，鍵是NULL。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE
INDEX或者IGNORE INDEX。

?

七、key_len


表示索引中使用的字節(jié)數(shù)，可通過(guò)該列計(jì)算查詢中使用的索引的長(zhǎng)度（key_len顯示的值為索引字段的最大可能長(zhǎng)度，并非實(shí)際使用長(zhǎng)度，即key_len是根據(jù)表定義計(jì)算而得，不是通過(guò)表內(nèi)檢索出的）

同樣的查詢結(jié)果夏，不損失精確性的情況下，長(zhǎng)度越短越好?。

?

八、ref

顯示索引的哪一列被使用了，如果可能的話，是一個(gè)常數(shù)，哪些列或者常量被用于查詢索引列上的值。

?

九、rows

?估算出結(jié)果集行數(shù)，表示MySQL根據(jù)表統(tǒng)計(jì)信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(shù)

?

十、Extra

該字段包含MySQL解決查詢的詳細(xì)信息,有以下幾種情況：

Using filesort：

說(shuō)明mysql會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取。

Mysql中無(wú)法利用索引完成的排序操作稱為“文件排序”，如果出現(xiàn)，要優(yōu)化此查詢。

簡(jiǎn)單來(lái)說(shuō)：Mysql并未按照你建立的索引去查找，

?

Using temporary：

使用了臨時(shí)表中保存中間結(jié)果，Mysql在對(duì)查詢結(jié)果排序時(shí)使用臨時(shí)表。常見(jiàn)于排序order by和分組查詢group by。

?

Using index：

表示相應(yīng)的select操作中使用了覆蓋索引（Covering Index）,避免訪問(wèn)了表的數(shù)據(jù)行，效率不錯(cuò)。

如果同時(shí)出現(xiàn)using where，表明索引被用于來(lái)執(zhí)行索引鍵值的查找。

如果沒(méi)有同時(shí)出現(xiàn)wing where，表明索引用來(lái)讀取數(shù)據(jù)而非執(zhí)行查找動(dòng)作。

?

Using where ：

表明使用了where過(guò)濾

?

Using join buffer：

使用了連接緩存

?

impossible where：

where子句中的值總是false，不能用來(lái)獲取任何元組

• ? 上一篇：關(guān)于青少年，編程，教育的一些感悟（寫在農(nóng)歷鼠年前）
• ? 下一篇：surging 社區(qū)版本支持.net core 3.1