MySQL explain 各字段的含義

MySQL 5.6.3 以前只能 EXPLAIN SELECT; 5.6.3 以後就可以 EXPLAIN SELECT,UPDATE,DELETE

有這樣一張user表, 300 多萬行記錄, 表結構及索引信息如下:

對於 sql:

SELECT
 * 
FROM
 `user` 
WHERE
 id > 20000 
 AND country > 1 
 AND grade IN ( 1, 4 ) 
 AND city IN ( 1, 500, 1000, 1500, 3000 ) 
ORDER BY
 update_time DESC 
 LIMIT 30;

explain 結果如下:

1.id

SQL 查詢中的序列號

id 列數字越大越先執行，如果說數字一樣大，那麼就從上往下依次執行。

2.select_type

查詢的類型, 可以是如下的任何一種類型：

3.table

查詢的表名. 並不一定是實際存在的表名.

可以爲如下的值：

<unionM,N>: 引用 id 爲 M 和 N UNION 後的結果。
<derivedN>: 引用 id 爲 N 的結果派生出的表。派生表可以是一個結果集，例如派生自 FROM 中子查詢的結果。
<subqueryN>: 引用 id 爲 N 的子查詢結果物化得到的表。即生成一個臨時表保存子查詢的結果。

4.partitions

5.7 以前，該項是explain partitions顯示的選項; 5.7 以後成爲了默認選項.

該列顯示的爲分區表命中的分區情況, 非分區表該字段爲空（NULL）.

5.type

最重要的一個指標, 顯示查詢使用了何種類型

除ALL之外, 其他 type 都可以用到索引; 除index_merge外, 其他 type 只可用到一個索引.

由左到右, 性能由差到好:

ALL,index，range，index_subquery，unique_subquery，index_merge，ref_or_null，fulltext，ref，eq_ref，const，system

從上到下, 性能由差到好:

(1) ALL

掃描全表, 性能最差.

>>>>>>

(2) index

掃描全部索引樹

或稱 "索引全表掃描", 即把索引從頭到尾掃一遍.

包含兩種情況：

查詢使用了覆蓋索引, 那麼只需要掃描索引就可以獲得數據. 這個效率要比全表掃描快, 因爲索引通常比數據表小, 且還能避免二次查詢. 這種情況在 extra 中顯示 Using index.
反之, 如果在索引上進行全表掃描, 則 extra 字段沒有 Using index.

如對於user表,telephone 字段建有索引, 如果

<1>.

explain select telephone from user;, 則 type 字段將爲 index

<2>.

explain select amount_coin from user;,

因爲 amount_coin 字段沒有建索引, 故而 type 字段將爲 ALL

<3>.

explain select * from user;,

如果包含沒有建索引的列, type 字段也將爲 ALL

>>>>>>

(3) range

掃描部分索引

索引範圍掃描, 對索引的掃描開始於某一點, 返回匹配值域的行，常見於 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等運算符的查詢中

>>>>>>

(4) index_subquery

該聯接類型類似於下面的unique_subquery

適用於非唯一索引, 可以返回重複值.

>>>>>>

(5) unique_subquery

用於where中in形式的子查詢.

子查詢返回不重複值唯一值, 可以完全替換子查詢, 效率更高.

該類型替換了下面形式的 IN 子查詢的 ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

>>>>>>

(6) index_merge

表示查詢使用了兩個以上的索引, 最後取交集或者並集, 常見and ，or的條件使用了不同的索引.

官方排序這個在下面的ref_or_null之後, 但實際上由於要讀取多個索引, 性能可能大部分時間都不如更下面的range

>>>>>>

(7) ref_or_null

跟下面的 ref 類型類似, 只是增加了 null 值的比較.

實際用的不多

>>>>>>

(8) fulltext

使用全文索引時type會是這個類型.

注意, 全文索引的優先級很高, 若全文索引和普通索引同時存在時, mysql 不管代價, 會優先選擇使用全文索引

>>>>>>

(9) ref

使用非唯一索引或非唯一索引前綴進行的查找

對於來自前表的每一行, 在當前表的索引中可以匹配到多行.

若連接只用到索引的_最左前綴_或_索引不是主鍵或唯一索引_時, 使用 ref 類型 (可以理解成可能出現 "一對多" 時)

ref 可用於使用'='或'<=>'操作符作比較的索引列

>>>>>>

(10) eq_ref

唯一性索引掃描, 對於每個索引鍵, 表中只有一條記錄與之匹配.

eq_ref 和 const 的區別:

eq_ref 出現於多表 join 時, 對於來自前表的每一行, 在當前表中只能找到一行.

這是除了下面幾種類型之外最好的類型. 當主鍵或唯一非 NULL 索引的所有字段都被用作 join 聯接時會使用此類型.

eq_ref 可用於使用'='操作符作比較的索引列, 比較的值可以是常量, 也可以是使用在此表之前讀取的表的列的表達式.

>>>>>>

(11) const

const: 單表中最多有一個匹配行, 例如根據主鍵或唯一索引查詢. (如 select * from user where id=100), 查詢起來非常迅速

>>>>>>

(12) system

system是const類型的特例, 只會出現在Myisam或Memory存儲引擎, 當查詢的表只有一行或空表的情況下,type字段將是 system.

如果是Innodb引擎表, type列在這種情況通常都是 ALL 或者 index.

這種類型可以可以忽略不計

>>>>>>

(13) NULL

不用訪問表或者索引, 直接就能得到結果, 如 explain select NOW()

6.prossible_keys

可能使用到的索引

7.key

真正使用到的索引

select_type爲index_merge時, 這裏可能出現兩個以上的索引;

其他的select_type這裏只會出現一個.

8.key_len

查詢用到的索引長度（字節數）

如果是單列索引, 那就整個索引長度算進去;

如果是多列索引 (即聯合索引), 那麼查詢不一定都能使用到所有的列, 用多少算多少.

可以留意下這個列的值, 算一下多列索引總長度, 就可知有沒有使用到所有的列.

另: key_len 只計算 where 條件用到的索引長度, 而排序和分組就算用到了索引, 也不會計算到 key_len 中.

9.ref

如果使用常數等值查詢, 這裏會顯示 const;

如果是連接查詢, 被驅動表的執行計劃這裏會顯示驅動表的關聯字段,

如果是條件使用了表達式或者函數, 或者條件列發生了內部隱式轉換, 這裏可能顯示爲 func

10.rows

非常重要的一個字段

mysql 估算的 需要掃描的行數（不是精確值）

通過這個值, 可以非常直觀地顯示 SQL 的效率好壞.

原則上 rows 越小越好.

當存在 limit 時, 會對 rows 字段產生影響. 建議在 explain 時先去除 limit

11.filtered

這個字段表示存儲引擎返回的數據在 server 層過濾後, 剩下多少滿足查詢的記錄數量的比例;

注意是百分比, 不是具體記錄數.

12.Extra

非常重要的一個字段

explain 中的很多額外信息會在 Extra 字段顯示, 常見的有以下幾種內容:

distinct：在 select 部分使用了 distinc 關鍵字
Using filesort：當 Extra 中有 Using filesort 時, 表示 MySQL 需額外的排序操作 (優先內存, 內存空間不夠則會在磁盤排序), 不能通過索引順序達到排序效果. 一般存在 Using filesort, 都建議通過優化去掉, 因爲這樣的查詢對機器的資源消耗很大.
Using index: "覆蓋索引掃描", 表示查詢在索引樹中就可查找所需數據, 不用掃描表數據文件, 往往說明性能不錯. 即不需要進行 filesort
Using temporary: 查詢有使用臨時表, 一般出現於排序, 分組和多表 join 的情況, 查詢效率不高, 建議通過優化去掉.

另外還有

using where：在查找使用索引的情況下，需要回表去查詢所需的數據
using index condition：查找使用了索引，但是需要回表查詢數據
using index & using where：查找使用了索引，但是需要的數據都在索引列中能找到，所以不需要回表查詢數據

using index 好於 using where 好於 using index condition, 不需要回表查詢數據，效率最快

在帶有order by子句的 sql 中, 要儘可能使 extra 字段不要出現Using filesort, 而是Using index

舉例如何去掉 Using filesort:

explain 結果每個字段的含義說明 [1]

mysql 索引 type 介紹 [2]

MySQL 優化：定位慢查詢的兩種方法以及使用 explain 分析 SQL[3]

limit 會對 explain 的 type 產生巨大影響

關於 order by 的優化

具有 LIMIT 和不具有 LIMIT 的 ORDER BY 可能是不同的

file_sort 優化器會預先分配固定數量的 sort_buffer_size 字節。

MySQL 有時會優化具有 LIMIT row_count 子句而沒有 HAVING 子句的查詢：

如果您只選擇 LIMIT 的幾行，則在某些情況下，MySQL 通常會選擇使用全 table 掃描，而 MySQL 通常會使用索引。

如果將 LIMIT row_count 和 ORDER BY 結合使用，MySQL 會在找到排序結果的前 * row_count 行後立即停止排序，而不是對整個結果進行排序。如果通過使用索引進行排序，這將非常快。如果必須執行文件排序，則在找到第一個 row_count * 之前，將選擇與查詢匹配的所有行，但不帶有 LIMIT 子句，並對其中的大多數或全部進行排序。找到初始行後，MySQL 不會對結果集的其餘部分進行排序。

如果沒有爲 ORDER BY 使用索引，但是也存在 LIMIT 子句，則優化器可能能夠避免使用合併文件，並使用內存中 filesort 操作對內存中的行進行排序。

參考:

優化 Sequences[4]

Orderby 排序優化 [5]

理解 mysql 的臨時表和文件排序 [6]

【IT 老齊 338】MySQL Extra 常見信息解析 [7]

【大廠文章速讀】字節跳動 - 慢 SQL 分析與優化 [8] （extra 出現這幾項，一定要加倍注意）

參考資料

[1]

explain 結果每個字段的含義說明: https://www.jianshu.com/p/8fab76bbf448

[2]

mysql 索引 type 介紹: https://blog.csdn.net/L_ieluil/article/details/62885392

[3]

MySQL 優化：定位慢查詢的兩種方法以及使用 explain 分析 SQL: https://baijiahao.baidu.com/s?id=1644795692359019265&wfr=spider&for=pc

[4]

優化 Sequences: https://www.docs4dev.com/docs/zh/mysql/5.7/reference/order-by-optimization.html

[5]

Orderby 排序優化: https://learnku.com/articles/38925

[6]

理解 mysql 的臨時表和文件排序: https://plu.one/mysql/2019/02/24/mysql-understand-temporary-and-filesort/

[7]

【IT 老齊 338】MySQL Extra 常見信息解析: https://www.bilibili.com/video/BV1Ch41157e

[8]

【大廠文章速讀】字節跳動 - 慢 SQL 分析與優化: https://www.bilibili.com/video/BV1vg411p7uJ

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/i-GtOAVHJp7Al7_m5G5p5Q

1.id

2.select_type

3.table

4.partitions

5.type

(1) ALL

(2) index

(3) range

(4) index_subquery

(5) unique_subquery

(6) index_merge

(7) ref_or_null

(8) fulltext

(9) ref

(10) eq_ref

(11) const

(12) system

(13) NULL

6.prossible_keys

7.key

8.key_len

9.ref

10.rows

11.filtered

12.Extra

參考資料

猜你喜歡