小編給大家分享一下MySQL 5.7Explain執(zhí)行計(jì)劃，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
創(chuàng)新互聯(lián)建站專注于網(wǎng)站建設(shè)|成都網(wǎng)站改版|優(yōu)化|托管以及網(wǎng)絡(luò)推廣，積累了大量的網(wǎng)站設(shè)計(jì)與制作經(jīng)驗(yàn)，為許多企業(yè)提供了網(wǎng)站定制設(shè)計(jì)服務(wù)，案例作品覆蓋工商代辦等行業(yè)。能根據(jù)企業(yè)所處的行業(yè)與銷(xiāo)售的產(chǎn)品，結(jié)合品牌形象的塑造，量身開(kāi)發(fā)品質(zhì)網(wǎng)站。
目錄
1. 介紹
2. Explain 結(jié)果列詳解
2.1 id
2.2 select_type
2.3 table
2.4 partitions
2.5 type（非常重要）
2.6 possible_keys
2.7 key
2.8 key_len
2.9 ref
3.10 rows
2.11 filtered
2.12 Extra
【注】
當(dāng)前系統(tǒng)環(huán)境： MySQL 5.7，其他版本略有不同，后期會(huì)抽時(shí)間單獨(dú)說(shuō)明。
只介紹常見(jiàn)的場(chǎng)景，其他少見(jiàn)的場(chǎng)景暫不研究，如有需要可以去官方文檔中查找。
非入門(mén)，需要對(duì) MySQL 的底層數(shù)據(jù)結(jié)構(gòu) B+ 樹(shù)有一定的了解。
文檔參考：
MySQL 官方 Explain 文檔
1. 介紹
使用 EXPLAIN 關(guān)鍵字可以模擬優(yōu)化器執(zhí)行 SQL 語(yǔ)句，并分析查詢語(yǔ)句的性能瓶頸。
2. Explain 結(jié)果列詳解
2.1 id
id 列的編號(hào)是 select 的序列號(hào)，一般有幾個(gè) select 就有幾個(gè) id（聯(lián)表查詢會(huì)有重復(fù)的 id），并且 id 的順序是按 select 出現(xiàn)的順序增長(zhǎng)的。
id 越大則表示執(zhí)行的優(yōu)先級(jí)越高，id 相同（一般出現(xiàn)在聯(lián)表查詢）則從上往下執(zhí)行，id 為 NULL 最后執(zhí)行。
2.2 select_type
select_type 表示對(duì)應(yīng)行是簡(jiǎn)單的還是復(fù)雜的查詢。常見(jiàn)的值有：
simple：簡(jiǎn)單查詢，查詢不包含子查詢和union。
primary：復(fù)雜查詢中最外層的 select 。
subquery：包含在 select 中的子查詢（不在 from 子句中）
derived：包含在 form 子句中的子查詢，MySQL 會(huì)將結(jié)果放在一個(gè)臨時(shí)表中，也稱為派生表。
union：在 union 中的第二個(gè)或之后的 select。
【注】在 MySQL 5.7 中，會(huì)對(duì)衍生表進(jìn)行合并優(yōu)化，如果要直觀的查看 select_type 的值，需要臨時(shí)關(guān)閉該功能（默認(rèn)是打開(kāi)的），下面的介紹中凡是涉及到衍生表的都需要該操作。
# 關(guān)閉衍生表的合并優(yōu)化（只對(duì)該會(huì)話有效）set session optimizer_switch='derived_merge=off'; # 打開(kāi)衍生表的合并優(yōu)化（只對(duì)該會(huì)話有效）set session optimizer_switch='derived_merge=on';
2.3 table
對(duì)應(yīng)行查詢的表。
【注】
當(dāng) from 子句中有子查詢時(shí)，table 列為是 id = N 行的查詢。
當(dāng)有 union 時(shí)，table 的數(shù)據(jù)為 M 和 N 表示參與 union 的 select 行id。
2.4 partitions
未完待續(xù)。。。
2.5 type（非常重要）
type 表示這行查詢的關(guān)聯(lián)類(lèi)型（訪問(wèn)類(lèi)型，或查詢類(lèi)型），通過(guò)該值可以了解該行查詢數(shù)據(jù)記錄的大概范圍。
常見(jiàn)的值依次從最優(yōu)到最差分別為：system > const > eq_ref > ref > range > index > ALL；一般我們要保證效率的話，要優(yōu)化我們的語(yǔ)句至少使其達(dá)到 range 級(jí)別，如果可能的話做好優(yōu)化到 ref；range 一般用于范圍查找，所以換句話說(shuō)除了范圍查找，其他的查詢語(yǔ)句我們最好是優(yōu)化到 ref 級(jí)別。
常見(jiàn)值說(shuō)明：
NULL : 表示 MySQL 能夠在優(yōu)化階段分解查詢語(yǔ)句，在執(zhí)行階段不用訪問(wèn)表和索引。
system / const： MySQL 能對(duì)某個(gè)查詢部分進(jìn)行優(yōu)化并將其轉(zhuǎn)化成一個(gè)常量（可以通過(guò) show warnings 查看優(yōu)化的結(jié)果），主要是查詢主鍵（Primary Key）或唯一鍵索引（Unique Key）對(duì)應(yīng)的記錄，因?yàn)椴淮嬖谥貜?fù)，所以最多只能查詢出一條記錄，所以速度比較快。system 是 const 的特例，當(dāng)臨時(shí)表里只有一條記錄時(shí)為 system 。
# 表里有一個(gè)主鍵id為1的記錄 - constexplain select * from student where id = 1# 派生表里面只有一條記錄 - systemexplain select * from (select * from student where id = 1) tmp# 注： 如果查詢的列中有 text 類(lèi)型，那么在這里 type 會(huì)變?yōu)?nbsp;ALL ，# 因?yàn)闊o(wú)法使用內(nèi)存臨時(shí)表，只能在磁盤(pán)上創(chuàng)建臨時(shí)表，所以性能上會(huì)有所損耗，效果等同于全表查詢 ALL。
req_ref：當(dāng)主鍵或唯一鍵索引的相關(guān)列并聯(lián)接使用時(shí)（聯(lián)表查詢），最多匹配一條符合條件的記錄。這是除了 const之外的最好的聯(lián)接類(lèi)型，簡(jiǎn)單的 select 查詢不會(huì)出現(xiàn) req_ref，更多出現(xiàn)在聯(lián)表查詢。
# 雖然返回結(jié)果中有多條記錄，但是在查詢中一個(gè)學(xué)生id只對(duì)應(yīng)一個(gè)班級(jí)，所以查詢班級(jí)的時(shí)候?yàn)?nbsp;req_ref，# 但是查詢 student 的時(shí)候是 ALL，全表查詢explain select * from student left join banji on student.id = banji.student_id
【注】在查詢的過(guò)程中的返回結(jié)果如下：
當(dāng)聯(lián)接表查詢時(shí)候會(huì)看作是一條查詢 SQL，所以它們對(duì)應(yīng)的 id 是一樣的，當(dāng) id 都是一樣的時(shí)候，按照從上到下的順序依次執(zhí)行，這里是先查詢班級(jí)所有的學(xué)生（全表查詢 ALL），然后根據(jù)學(xué)生id查找出學(xué)生對(duì)應(yīng)的班級(jí)信息（req_ref）。
ref：當(dāng)使用普通索引（Normal）或者是聯(lián)合索引的部分前綴時(shí)，索引要和某個(gè)值進(jìn)行比較，可能會(huì)找到多個(gè)符合條件的記錄行，從輔助索引的根節(jié)點(diǎn)開(kāi)始對(duì)比并找到相應(yīng)的記錄。
# 簡(jiǎn)單的 select 查詢，name 是普通索引（Normal Index）explain select * from student where name = '張三';# 簡(jiǎn)單 select 查詢，banji_id (第一個(gè)) 和 student_id (第二個(gè)) 的聯(lián)合索引EXPLAIN SELECT * FROM banji_student WHERE banji_student.banji_id = 3# 關(guān)聯(lián)表查詢# 包含 banji 表，banji_student 是班級(jí)與學(xué)生的關(guān)系表# 關(guān)系表中有 banji_id (第一個(gè)) 和 student_id (第二個(gè)) 的聯(lián)合索引 idx_banji_stu_id 索引，# 以下查詢只用到了聯(lián)合索引的 banji_id (第一個(gè))explain select * from banji_id from banji left join banji_student on banji.id = banji_student.banji_id
range：范圍掃描，通常出現(xiàn)在 in，between，>，<，>= 等操作中，使用一個(gè)索引來(lái)檢索給定范圍的行。
# 查詢 id 大于 1 的學(xué)生信息explain select * from student where id > 2;
index：
# student 表只有id主鍵，name 普通索引select * from student;# 這個(gè)時(shí)候會(huì)走 name 索引# 因?yàn)?nbsp;name 是普通索引，所以如果加 where 的話可以達(dá)到 ref 級(jí)別select * from student where name = 'Ana'
覆蓋索引定義：覆蓋索引一般針對(duì)于輔助索引，并不是真正的索引，只是索引查找的一種方式。如果 select 查詢的字段都在輔助索引樹(shù)中全部拿到，這種情況一般是使用了覆蓋索引，不需要通過(guò)輔助索引樹(shù)找到主鍵，再通過(guò)主鍵去主鍵索引樹(shù)里獲取其它字段值。
掃描全索引就能拿到結(jié)果，一般是掃描某個(gè)二級(jí)索引（輔助索引，除了主鍵之外的索引）。這種索引不會(huì)從主鍵索引樹(shù)根節(jié)點(diǎn)開(kāi)始查找，而是直接對(duì)二級(jí)索引的葉子節(jié)點(diǎn)遍歷和掃描，從而查找出相應(yīng)的記錄行，速度比較慢；
這種查詢方式一般為使用覆蓋索引，查詢所需的所有結(jié)果集在二級(jí)索引與主鍵索引中都有的情況下，由于二級(jí)索引一般比較?。ㄒ?yàn)?code>二級(jí)索引是非聚集的，其葉子節(jié)點(diǎn)是存放的主鍵索引相應(yīng)的地址，而主鍵索引是聚集的，其葉子節(jié)點(diǎn)存放的是完整的數(shù)據(jù)集），所以優(yōu)先走二級(jí)索引，這種情況通常比 ALL 快一些。
在某些情況下，如果表的列數(shù)特別多，這個(gè)時(shí)候通過(guò)輔助索引查詢的性能就不如直接使用主鍵索引效率高（如果查詢了輔助索引的話，還會(huì)返回到主鍵索引中進(jìn)行查找更多的字段，也就是回表查詢，當(dāng)然在某些情況下使用回表查詢的性能也會(huì)比只使用主鍵索引的性能高），這個(gè)時(shí)候會(huì)走主鍵索引，這種情況也比 ALL 快。
ALL：全表掃描，掃描主鍵（聚簇、聚集）索引樹(shù)的所有葉子節(jié)點(diǎn)，通常這種情況下要根據(jù)業(yè)務(wù)場(chǎng)景來(lái)增加其他索引進(jìn)行優(yōu)化。
# id 為主鍵的 student 表，沒(méi)有其他索引，該查詢?yōu)?nbsp;ALL.select * from student
2.6 possible_keys
possible_keys 主要顯示查詢可能用到哪些索引來(lái)查找，只是可能會(huì)使用，并不代表一定會(huì)使用。
常見(jiàn)值說(shuō)明：
NULL: 沒(méi)有相關(guān)索引，如果是 NULL 的話，可以考慮在 where 子句中創(chuàng)建一個(gè)適當(dāng)?shù)乃饕齺?lái)提高查詢性能，然后繼續(xù)用 explain 查看其效果；也有可能出現(xiàn) possible_keys 為 NULL，但是 key 有值，實(shí)際走了索引。
有列值：如果顯示表中的某列，則表示可能會(huì)走這一列對(duì)應(yīng)列值的索引；如果 possible_keys 有值，但是 key 顯示 NULL，這種情況一般存在于表中數(shù)據(jù)量不大的情況，因?yàn)?MySQL 語(yǔ)句優(yōu)化器認(rèn)為索引對(duì)此查詢的幫助不大，從而選擇了全表查詢。
2.7 key
key 表示 MySQL 實(shí)際采用哪個(gè)索引來(lái)優(yōu)化對(duì)該表的查詢。
如果沒(méi)有使用索引，則該列為 NULL，如果想強(qiáng)制 MySQL 使用或忽略 possible_keys 列中的索引，可以在查詢中使用 force index 或 ignore index.
2.8 key_len
顯示了 MySQL 索引所使用的字節(jié)數(shù)，通過(guò)這個(gè)數(shù)值可以計(jì)算具體使用了索引中的哪些列（主要用于聯(lián)合索引的優(yōu)化）。
【注】索引最大長(zhǎng)度是 768 字節(jié)，當(dāng)字符串過(guò)長(zhǎng)時(shí)，MySQL 會(huì)做一個(gè)類(lèi)似左前綴索引的處理，將前半部分的字符提取出來(lái)做索引。
示例：一個(gè)學(xué)生與班級(jí)的關(guān)系表：
banji_student，存在使用banji_id與student_id兩個(gè)列組合的聯(lián)合索引，并且每個(gè)索引int都是4字節(jié)，通過(guò)key_len值為4可以知道只使用了聯(lián)合索引的第一列：banji_id來(lái)執(zhí)行索引查找。
# 只使用了聯(lián)合索引的第一列select * from banji_student where banji_id = 2
key_len 的計(jì)算規(guī)則如下：
字符串：常見(jiàn)的是 char(n) 和 varchar(n)，從 MySQL 5.0.3 之后，n 均表示字符數(shù)，而不是字節(jié)數(shù)，如果是 UTF-8，一個(gè)數(shù)字或字母占1個(gè)字節(jié)，一個(gè)漢字占3個(gè)字節(jié)。
| 描述 | |
|---|---|
char(n) | 非漢字長(zhǎng)度為 n，如果存放漢字長(zhǎng)度為 3n 字節(jié) |
varchar(n) | 非漢字長(zhǎng)度為 n+2，如果存放漢字長(zhǎng)度為 3n+2 字節(jié)；因?yàn)?varchar 是可變長(zhǎng)字符串，需要 2 字節(jié)來(lái)存儲(chǔ)字符串長(zhǎng)度 |
數(shù)值類(lèi)型：
| 描述 | |
|---|---|
tinyint | 長(zhǎng)度為 1 字節(jié) |
smallint | 長(zhǎng)度為 2 字節(jié) |
int | 長(zhǎng)度為 4 字節(jié) |
bigint | 長(zhǎng)度為 8 字節(jié) |
時(shí)間類(lèi)型：
| 描述 | |
|---|---|
date | 長(zhǎng)度為 3 字節(jié) |
timestamp | 長(zhǎng)度為 4 字節(jié) |
datetime | 長(zhǎng)度為 8 字節(jié) |
NULL
如果字段允許設(shè)置為 NULL，則需要 1 字節(jié)來(lái)記錄是否為 NULL； Not NULL 的列則不需要。
2.9 ref
顯示了在使用 key 列中實(shí)際的索引時(shí)，表查找時(shí)所用到的列名和常量；常見(jiàn)的為 const 常量或索引關(guān)聯(lián)查詢的字段（列）名。
# 使用了常量 2，所以在查詢的時(shí)候 ref 為 constselect * from student where id = 2# 關(guān)聯(lián)表查詢# 包含 banji 表，banji_student 是班級(jí)與學(xué)生的關(guān)系表# 關(guān)系表中有 banji_id (第一個(gè)) 和 student_id (第二個(gè)) 的聯(lián)合索引 idx_banji_stu_id 索引# 這里的 ref 為 test.id ，也就是指的是 banji.idexplain select * from banji_id from banji left join banji_student on banji.id = banji_student.banji_id
3.10 rows
顯示預(yù)計(jì)查詢的結(jié)果數(shù)，并不是真正的結(jié)果集中的記錄（行）數(shù)，僅供參考。
2.11 filtered
未完待續(xù)。。。
2.12 Extra
這一列展示的是額外的信息，存在很多值，且在不同的場(chǎng)景下以及不同版本的 MySQL 所表示的意思也不同，只能是表示大概的意思并且僅做優(yōu)化參考，這里只介紹常見(jiàn)的值。
Using index：使用覆蓋索引，在 type 相同的情況下， Extra 的值為 Using index 要比為 NULL 性能高。
比如 banji 表，存在 id，name，create_time 列，存在 id 主鍵與 name 普通索引。
# 覆蓋索引，直接查詢 name 對(duì)應(yīng)的索引樹(shù)就可以滿足 select 后面的查詢列select id,name from banji# 非覆蓋索引，雖然也走了索引，但是進(jìn)行了回表查詢，以查詢出 create_time 字段。select * from banji where name = '二年級(jí)'
Using where：使用 where 關(guān)鍵字來(lái)查詢，并且對(duì)應(yīng)的列沒(méi)有設(shè)置索引，對(duì)應(yīng)的 key 為 NULL。
這種情況一般要對(duì)查詢的列添加相對(duì)應(yīng)的索引來(lái)進(jìn)行優(yōu)化。
Using index condition：非覆蓋索引查詢并進(jìn)行了回表，并且輔助索引使用了條件查詢語(yǔ)句（where 或其他）。
比如 banji_student 關(guān)系表，存在 id，banji_id，student_id，create_time 列，存在 id 主鍵 和 banji_id 與 student_id 的組合（聯(lián)合）索引。
# 進(jìn)行了回表查詢，以查詢出 create_time 列，并且組合索引進(jìn)行了范圍查找select * from banji_student where banji_id > 3
Using temporary：MySQL 需要?jiǎng)?chuàng)建創(chuàng)建一個(gè)臨時(shí)表來(lái)處理查詢，出現(xiàn)這種情況一般要添加索引進(jìn)行優(yōu)化處理。
# 如果 name 沒(méi)有添加普通索引的話，則需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)進(jìn)行去重，Extra 值為 Using temporary# 如果添加了索引，則會(huì)走 name 對(duì)應(yīng)的索引樹(shù)，并且是覆蓋索引，Extra 值為 Using indexexplain select distinct name from student
Using filesort：使用外部排序而不是索引排序，當(dāng)數(shù)據(jù)較小的時(shí)候采用的是內(nèi)存排序，當(dāng)數(shù)據(jù)量較大的時(shí)候會(huì)頻繁的訪問(wèn)磁盤(pán)，并將排序后的數(shù)據(jù)寫(xiě)入磁盤(pán)。
# 如果 name 沒(méi)有添加普通索引的話，則需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)進(jìn)行去重，Extra 值為 Using filesort# 如果添加了索引，則會(huì)走 name 對(duì)應(yīng)的索引樹(shù)，并且是覆蓋索引，Extra 值為 Using indexexplain select name from student order by name
Select tables optimized away：使用聚合函數(shù)（例如 max、min等）來(lái)訪問(wèn)存在索引的字段時(shí)，只訪問(wèn)索引樹(shù)中已排好序的葉子，節(jié)點(diǎn)性能很高。
# 比如使用聚合函數(shù) min 查詢最小的學(xué)生 id(主鍵)explain select min(id) from student
以上是“MySQL 5.7Explain執(zhí)行計(jì)劃”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！
重慶分公司
重慶分公司
