MySQL 5.7Explain執(zhí)行計劃

小編給大家分享一下MySQL 5.7Explain執(zhí)行計劃，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

目錄

• 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，其他版本略有不同，后期會抽時間單獨說明。

• 只介紹常見的場景，其他少見的場景暫不研究，如有需要可以去官方文檔中查找。

• 非入門，需要對 MySQL 的底層數(shù)據(jù)結(jié)構(gòu) B+ 樹有一定的了解。

文檔參考：
MySQL 官方 Explain 文檔

1. 介紹

使用 EXPLAIN 關(guān)鍵字可以模擬優(yōu)化器執(zhí)行 SQL 語句，并分析查詢語句的性能瓶頸。

2. Explain 結(jié)果列詳解

2.1 id

• id 列的編號是 select 的序列號，一般有幾個 select 就有幾個 id（聯(lián)表查詢會有重復(fù)的 id），并且 id 的順序是按 select 出現(xiàn)的順序增長的。

• id 越大則表示執(zhí)行的優(yōu)先級越高，id 相同（一般出現(xiàn)在聯(lián)表查詢）則從上往下執(zhí)行，id 為 NULL 最后執(zhí)行。

2.2 select_type

select_type 表示對應(yīng)行是簡單的還是復(fù)雜的查詢。常見的值有：

• simple：簡單查詢，查詢不包含子查詢和union。

• primary：復(fù)雜查詢中最外層的 select 。

• subquery：包含在 select 中的子查詢（不在 from 子句中）

• derived：包含在 form 子句中的子查詢，MySQL 會將結(jié)果放在一個臨時表中，也稱為派生表。

• union：在 union 中的第二個或之后的 select。

【注】在 MySQL 5.7 中，會對衍生表進行合并優(yōu)化，如果要直觀的查看 select_type 的值，需要臨時關(guān)閉該功能（默認(rèn)是打開的），下面的介紹中凡是涉及到衍生表的都需要該操作。

# 關(guān)閉衍生表的合并優(yōu)化（只對該會話有效）set session optimizer_switch='derived_merge=off'; # 打開衍生表的合并優(yōu)化（只對該會話有效）set session optimizer_switch='derived_merge=on';

2.3 table

對應(yīng)行查詢的表。

【注】

• 當(dāng) from 子句中有子查詢時，table 列為是 <derivenN> 的格式，表示這一行的執(zhí)行的是 id = N 行的查詢。

• 當(dāng)有 union 時，table 的數(shù)據(jù)為 <union M,N> 的格式，M 和 N 表示參與 union 的 select 行id。

2.4 partitions

未完待續(xù)。。。

2.5 type（非常重要）

• type 表示這行查詢的關(guān)聯(lián)類型（訪問類型，或查詢類型），通過該值可以了解該行查詢數(shù)據(jù)記錄的大概范圍。

• 常見的值依次從最優(yōu)到最差分別為：system > const > eq_ref > ref > range > index > ALL；一般我們要保證效率的話，要優(yōu)化我們的語句至少使其達到 range 級別，如果可能的話做好優(yōu)化到 ref；range 一般用于范圍查找，所以換句話說除了范圍查找，其他的查詢語句我們最好是優(yōu)化到 ref 級別。

常見值說明：

• NULL : 表示 MySQL 能夠在優(yōu)化階段分解查詢語句，在執(zhí)行階段不用訪問表和索引。

• system / const： MySQL 能對某個查詢部分進行優(yōu)化并將其轉(zhuǎn)化成一個常量（可以通過 show warnings 查看優(yōu)化的結(jié)果），主要是查詢主鍵（Primary Key）或唯一鍵索引（Unique Key）對應(yīng)的記錄，因為不存在重復(fù)，所以最多只能查詢出一條記錄，所以速度比較快。system 是 const 的特例，當(dāng)臨時表里只有一條記錄時為 system 。

  # 表里有一個主鍵id為1的記錄 - constexplain select * from student where id = 1# 派生表里面只有一條記錄 - systemexplain select * from (select * from student where id = 1) tmp# 注： 如果查詢的列中有 text 類型，那么在這里 type 會變?yōu)?nbsp;ALL ，# 因為無法使用內(nèi)存臨時表，只能在磁盤上創(chuàng)建臨時表，所以性能上會有所損耗，效果等同于全表查詢 ALL。

• req_ref：當(dāng)主鍵或唯一鍵索引的相關(guān)列并聯(lián)接使用時（聯(lián)表查詢），最多匹配一條符合條件的記錄。這是除了 const之外的最好的聯(lián)接類型，簡單的 select 查詢不會出現(xiàn) req_ref，更多出現(xiàn)在聯(lián)表查詢。

  # 雖然返回結(jié)果中有多條記錄，但是在查詢中一個學(xué)生id只對應(yīng)一個班級，所以查詢班級的時候為 req_ref，# 但是查詢 student 的時候是 ALL，全表查詢explain select * from student left join banji on student.id = banji.student_id

【注】在查詢的過程中的返回結(jié)果如下：

當(dāng)聯(lián)接表查詢時候會看作是一條查詢 SQL，所以它們對應(yīng)的 id 是一樣的，當(dāng) id 都是一樣的時候，按照從上到下的順序依次執(zhí)行，這里是先查詢班級所有的學(xué)生（全表查詢 ALL），然后根據(jù)學(xué)生id查找出學(xué)生對應(yīng)的班級信息（req_ref）。

• ref：當(dāng)使用普通索引（Normal）或者是聯(lián)合索引的部分前綴時，索引要和某個值進行比較，可能會找到多個符合條件的記錄行，從輔助索引的根節(jié)點開始對比并找到相應(yīng)的記錄。

  # 簡單的 select 查詢，name 是普通索引（Normal Index）explain select * from student where name = '張三';# 簡單 select 查詢，banji_id (第一個) 和 student_id (第二個) 的聯(lián)合索引EXPLAIN SELECT * FROM banji_student WHERE banji_student.banji_id = 3# 關(guān)聯(lián)表查詢# 包含 banji 表，banji_student 是班級與學(xué)生的關(guān)系表# 關(guān)系表中有 banji_id (第一個) 和 student_id (第二個) 的聯(lián)合索引 idx_banji_stu_id 索引，# 以下查詢只用到了聯(lián)合索引的 banji_id (第一個)explain select * from banji_id from banji left join banji_student 
  	on banji.id = banji_student.banji_id

• range：范圍掃描，通常出現(xiàn)在 in，between，>，<，>= 等操作中，使用一個索引來檢索給定范圍的行。

  # 查詢 id 大于 1 的學(xué)生信息explain select * from student where id > 2;

• index：

  # student 表只有id主鍵，name 普通索引select * from student;# 這個時候會走 name 索引# 因為 name 是普通索引，所以如果加 where 的話可以達到 ref 級別select * from student where name = 'Ana'

  覆蓋索引定義：覆蓋索引一般針對于輔助索引，并不是真正的索引，只是索引查找的一種方式。如果 select 查詢的字段都在輔助索引樹中全部拿到，這種情況一般是使用了覆蓋索引，不需要通過輔助索引樹找到主鍵，再通過主鍵去主鍵索引樹里獲取其它字段值。

• 掃描全索引就能拿到結(jié)果，一般是掃描某個二級索引（輔助索引，除了主鍵之外的索引）。這種索引不會從主鍵索引樹根節(jié)點開始查找，而是直接對二級索引的葉子節(jié)點遍歷和掃描，從而查找出相應(yīng)的記錄行，速度比較慢；

• 這種查詢方式一般為使用覆蓋索引，查詢所需的所有結(jié)果集在二級索引與主鍵索引中都有的情況下，由于二級索引一般比較小（因為二級索引是非聚集的，其葉子節(jié)點是存放的主鍵索引相應(yīng)的地址，而主鍵索引是聚集的，其葉子節(jié)點存放的是完整的數(shù)據(jù)集），所以優(yōu)先走二級索引，這種情況通常比 ALL 快一些。

• 在某些情況下，如果表的列數(shù)特別多，這個時候通過輔助索引查詢的性能就不如直接使用主鍵索引效率高（如果查詢了輔助索引的話，還會返回到主鍵索引中進行查找更多的字段，也就是回表查詢，當(dāng)然在某些情況下使用回表查詢的性能也會比只使用主鍵索引的性能高），這個時候會走主鍵索引，這種情況也比 ALL 快。

• ALL：全表掃描，掃描主鍵（聚簇、聚集）索引樹的所有葉子節(jié)點，通常這種情況下要根據(jù)業(yè)務(wù)場景來增加其他索引進行優(yōu)化。

  # id 為主鍵的 student 表，沒有其他索引，該查詢?yōu)?nbsp;ALL.select * from student

2.6 possible_keys

possible_keys 主要顯示查詢可能用到哪些索引來查找，只是可能會使用，并不代表一定會使用。

常見值說明：

• NULL: 沒有相關(guān)索引，如果是 NULL 的話，可以考慮在 where 子句中創(chuàng)建一個適當(dāng)?shù)乃饕齺硖岣卟樵冃阅?，然后繼續(xù)用 explain 查看其效果；也有可能出現(xiàn) possible_keys 為 NULL，但是 key 有值，實際走了索引。

• 有列值：如果顯示表中的某列，則表示可能會走這一列對應(yīng)列值的索引；如果 possible_keys 有值，但是 key 顯示 NULL，這種情況一般存在于表中數(shù)據(jù)量不大的情況，因為 MySQL 語句優(yōu)化器認(rèn)為索引對此查詢的幫助不大，從而選擇了全表查詢。

2.7 key

• key 表示 MySQL 實際采用哪個索引來優(yōu)化對該表的查詢。

• 如果沒有使用索引，則該列為 NULL，如果想強制 MySQL 使用或忽略 possible_keys 列中的索引，可以在查詢中使用 force index 或 ignore index.

2.8 key_len

顯示了 MySQL 索引所使用的字節(jié)數(shù)，通過這個數(shù)值可以計算具體使用了索引中的哪些列（主要用于聯(lián)合索引的優(yōu)化）。

【注】索引最大長度是 768 字節(jié)，當(dāng)字符串過長時，MySQL 會做一個類似左前綴索引的處理，將前半部分的字符提取出來做索引。

示例：一個學(xué)生與班級的關(guān)系表：banji_student，存在使用 banji_id 與 student_id 兩個列組合的聯(lián)合索引，并且每個索引 int 都是 4 字節(jié)，通過 key_len 值為 4 可以知道只使用了聯(lián)合索引的第一列：banji_id 來執(zhí)行索引查找。

# 只使用了聯(lián)合索引的第一列select * from banji_student where banji_id = 2

key_len 的計算規(guī)則如下：

• 字符串：常見的是 char(n) 和 varchar(n)，從 MySQL 5.0.3 之后，n 均表示字符數(shù)，而不是字節(jié)數(shù)，如果是 UTF-8，一個數(shù)字或字母占1個字節(jié)，一個漢字占3個字節(jié)。

  	描述
  char(n)	非漢字長度為 n，如果存放漢字長度為 3n 字節(jié)
  varchar(n)	非漢字長度為 n+2，如果存放漢字長度為 3n+2 字節(jié)；因為 varchar 是可變長字符串，需要 2 字節(jié)來存儲字符串長度

• 數(shù)值類型：

  	描述
  tinyint	長度為 1 字節(jié)
  smallint	長度為 2 字節(jié)
  int	長度為 4 字節(jié)
  bigint	長度為 8 字節(jié)

• 時間類型：

  	描述
  date	長度為 3 字節(jié)
  timestamp	長度為 4 字節(jié)
  datetime	長度為 8 字節(jié)

• NULL

  如果字段允許設(shè)置為 NULL，則需要 1 字節(jié)來記錄是否為 NULL； Not NULL 的列則不需要。

2.9 ref

顯示了在使用 key 列中實際的索引時，表查找時所用到的列名和常量；常見的為 const 常量或索引關(guān)聯(lián)查詢的字段（列）名。

# 使用了常量 2，所以在查詢的時候 ref 為 constselect * from student where id = 2# 關(guān)聯(lián)表查詢# 包含 banji 表，banji_student 是班級與學(xué)生的關(guān)系表# 關(guān)系表中有 banji_id (第一個) 和 student_id (第二個) 的聯(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ù)計查詢的結(jié)果數(shù)，并不是真正的結(jié)果集中的記錄（行）數(shù)，僅供參考。

2.11 filtered

未完待續(xù)。。。

2.12 Extra

這一列展示的是額外的信息，存在很多值，且在不同的場景下以及不同版本的 MySQL 所表示的意思也不同，只能是表示大概的意思并且僅做優(yōu)化參考，這里只介紹常見的值。

• Using index：使用覆蓋索引，在 type 相同的情況下， Extra 的值為 Using index 要比為 NULL 性能高。

  比如 banji 表，存在 id，name，create_time 列，存在 id 主鍵與 name 普通索引。

  # 覆蓋索引，直接查詢 name 對應(yīng)的索引樹就可以滿足 select 后面的查詢列select id,name from banji# 非覆蓋索引，雖然也走了索引，但是進行了回表查詢，以查詢出 create_time 字段。select * from banji where name = '二年級'

• Using where：使用 where 關(guān)鍵字來查詢，并且對應(yīng)的列沒有設(shè)置索引，對應(yīng)的 key 為 NULL。

  這種情況一般要對查詢的列添加相對應(yīng)的索引來進行優(yōu)化。

• Using index condition：非覆蓋索引查詢并進行了回表，并且輔助索引使用了條件查詢語句（where 或其他）。

  比如 banji_student 關(guān)系表，存在 id，banji_id，student_id，create_time 列，存在 id 主鍵 和 banji_id 與 student_id 的組合（聯(lián)合）索引。

  # 進行了回表查詢，以查詢出 create_time 列，并且組合索引進行了范圍查找select * from banji_student where banji_id > 3

• Using temporary：MySQL 需要創(chuàng)建創(chuàng)建一個臨時表來處理查詢，出現(xiàn)這種情況一般要添加索引進行優(yōu)化處理。

  # 如果 name 沒有添加普通索引的話，則需要創(chuàng)建一個臨時表來進行去重，Extra 值為 Using temporary# 如果添加了索引，則會走 name 對應(yīng)的索引樹，并且是覆蓋索引，Extra 值為 Using indexexplain select distinct name from student

• Using filesort：使用外部排序而不是索引排序，當(dāng)數(shù)據(jù)較小的時候采用的是內(nèi)存排序，當(dāng)數(shù)據(jù)量較大的時候會頻繁的訪問磁盤，并將排序后的數(shù)據(jù)寫入磁盤。

  # 如果 name 沒有添加普通索引的話，則需要創(chuàng)建一個臨時表來進行去重，Extra 值為 Using filesort# 如果添加了索引，則會走 name 對應(yīng)的索引樹，并且是覆蓋索引，Extra 值為 Using indexexplain select name from student order by name

• Select tables optimized away：使用聚合函數(shù)（例如 max、min等）來訪問存在索引的字段時，只訪問索引樹中已排好序的葉子，節(jié)點性能很高。

  # 比如使用聚合函數(shù) min 查詢最小的學(xué)生 id(主鍵)explain select min(id) from student

以上是“MySQL 5.7Explain執(zhí)行計劃”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注億速云行業(yè)資訊頻道！