MySQL索引優(yōu)化Explain詳解

在日常工作中，我們會(huì)有時(shí)會(huì)開(kāi)慢查詢?nèi)ビ涗浺恍﹫?zhí)行時(shí)間比較久的SQL語(yǔ)句，找出這些SQL語(yǔ)句并不意味著完事了，些時(shí)我們常常用到explain這個(gè)命令來(lái)查看一個(gè)這些SQL語(yǔ)句的執(zhí)行計(jì)劃，查看該SQL語(yǔ)句有沒(méi)有使用上了索引，有沒(méi)有做全表掃描，這都可以通過(guò)explain命令來(lái)查看。所以我們深入了解MySQL的基于開(kāi)銷的優(yōu)化器，還可以獲得很多可能被優(yōu)化器考慮到的訪問(wèn)策略的細(xì)節(jié)，以及當(dāng)運(yùn)行SQL語(yǔ)句時(shí)哪種策略預(yù)計(jì)會(huì)被優(yōu)化器采用。

-- 實(shí)際SQL，查找用戶名為Jefabc的員工
select * from emp where name = 'Jefabc';
-- 查看SQL是否使用索引，前面加上explain即可
explain select * from emp where name = 'Jefabc';

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ō)明

下面對(duì)這些字段出現(xiàn)的可能進(jìn)行解釋：

一、id

SELECT識(shí)別符。這是SELECT的查詢序列號(hào)

我的理解是SQL執(zhí)行的順序的標(biāo)識(shí)，SQL從大到小的執(zhí)行

1. id相同時(shí)，執(zhí)行順序由上至下

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

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

-- 查看在研發(fā)部并且名字以Jef開(kāi)頭的員工，經(jīng)典查詢
explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name = '研發(fā)部';

二、select_type

示查詢中每個(gè)select子句的類型

(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)于哪張表的），有時(shí)不是真實(shí)的表名字，可能是簡(jiǎn)稱，例如上面的e，d，也可能是第幾步執(zhí)行的結(jié)果的簡(jiǎn)稱

四、type

對(duì)表訪問(wèn)方式，表示MySQL在表中找到所需行的方式，又稱“訪問(wèn)類型”。

常用的類型有：ALL、index、range、 ref、eq_ref、const、system、NULL（從左到右，性能從差到好）

ALL：Full Table Scan， MySQL將遍歷全表以找到匹配的行

index: Full Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹(shù)

range:只檢索給定范圍的行，使用一個(gè)索引來(lái)選擇行

ref: 表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值

eq_ref: 類似ref，區(qū)別就在使用的索引是唯一索引，對(duì)于每個(gè)索引鍵值，表中只有一條記錄匹配，簡(jiǎn)單來(lái)說(shuō)，就是多表連接中使用primary key或者 unique key作為關(guān)聯(lián)條件

const、system: 當(dāng)MySQL對(duì)查詢某部分進(jìn)行優(yōu)化，并轉(zhuǎn)換為一個(gè)常量時(shí)，使用這些類型訪問(wèn)。如將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個(gè)常量，system是const類型的特例，當(dāng)查詢的表只有一行的情況下，使用system

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

五、possible_keys

指出MySQL能使用哪個(gè)索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用（該查詢可以利用的索引，如果沒(méi)有任何索引顯示 null）

該列完全獨(dú)立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實(shí)際上不能按生成的表次序使用。
如果該列是NULL，則沒(méi)有相關(guān)的索引。在這種情況下，可以通過(guò)檢查WHERE子句看是否它引用某些列或適合索引的列來(lái)提高你的查詢性能。如果是這樣，創(chuàng)造一個(gè)適當(dāng)?shù)乃饕⑶以俅斡肊XPLAIN檢查查詢

六、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)檢索出的）

不損失精確性的情況下，長(zhǎng)度越短越好

八、ref

列與索引的比較，表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值

九、rows

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

十、Extra

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

Using where:不用讀取表中所有信息，僅通過(guò)索引就可以獲取所需數(shù)據(jù)，這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引的部分的時(shí)候，表示mysql服務(wù)器將在存儲(chǔ)引擎檢索行后再進(jìn)行過(guò)濾

Using temporary：表示MySQL需要使用臨時(shí)表來(lái)存儲(chǔ)結(jié)果集，常見(jiàn)于排序和分組查詢，常見(jiàn) group by ; order by

Using filesort：當(dāng)Query中包含 order by 操作，而且無(wú)法利用索引完成的排序操作稱為“文件排序”

-- 測(cè)試Extra的filesort
explain select * from emp order by name;

Using join buffer：改值強(qiáng)調(diào)了在獲取連接條件時(shí)沒(méi)有使用索引，并且需要連接緩沖區(qū)來(lái)存儲(chǔ)中間結(jié)果。如果出現(xiàn)了這個(gè)值，那應(yīng)該注意，根據(jù)查詢的具體情況可能需要添加索引來(lái)改進(jìn)能。

Impossible where：這個(gè)值強(qiáng)調(diào)了where語(yǔ)句會(huì)導(dǎo)致沒(méi)有符合條件的行（通過(guò)收集統(tǒng)計(jì)信息不可能存在結(jié)果）。

Select tables optimized away：這個(gè)值意味著僅通過(guò)使用索引，優(yōu)化器可能僅從聚合函數(shù)結(jié)果中返回一行

No tables used：Query語(yǔ)句中使用from dual 或不含任何from子句

-- explain select now() from dual;

總結(jié)：

• EXPLAIN不會(huì)告訴你關(guān)于觸發(fā)器、存儲(chǔ)過(guò)程的信息或用戶自定義函數(shù)對(duì)查詢的影響情況
• EXPLAIN不考慮各種Cache
• EXPLAIN不能顯示MySQL在執(zhí)行查詢時(shí)所作的優(yōu)化工作
• 部分統(tǒng)計(jì)信息是估算的，并非精確值
• EXPALIN只能解釋SELECT操作，其他操作要重寫(xiě)為SELECT后查看執(zhí)行計(jì)劃。

通過(guò)收集統(tǒng)計(jì)信息不可能存在結(jié)果

到此這篇關(guān)于MySQL索引優(yōu)化Explain詳解的文章就介紹到這了,更多相關(guān)MySQL索引優(yōu)化Explain內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！