我們都知道用explain xxx分析sql語(yǔ)句的性能，但是具體從explain的結(jié)果怎么分析性能以及每個(gè)字段的含義你清楚嗎？這里我做下總結(jié)記錄，也是供自己以后參考。

• 首先需要注意：MYSQL 5.6.3以前只能EXPLAIN SELECT; MYSQL5.6.3以后就可以EXPLAIN SELECT,UPDATE,DELETE

explain結(jié)果示例：

mysql> explain select * from staff;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | staff | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

先上一個(gè)官方文檔表格的中文版：

這是explain結(jié)果的各個(gè)字段，分別解釋下含義：

1. id

SQL查詢中的序列號(hào)。

id列數(shù)字越大越先執(zhí)行，如果說數(shù)字一樣大，那么就從上往下依次執(zhí)行。

2. select_type

查詢的類型，可以是下表的任何一種類型：

DEPENDENT 意味著使用了關(guān)聯(lián)子查詢。

3. table

查詢的表名。不一定是實(shí)際存在的表名。
可以為如下的值：

• <unionM,N>: 引用id為M和N UNION后的結(jié)果。
• <derivedN>: 引用id為N的結(jié)果派生出的表。派生表可以是一個(gè)結(jié)果集，例如派生自FROM中子查詢的結(jié)果。
• <subqueryN>: 引用id為N的子查詢結(jié)果物化得到的表。即生成一個(gè)臨時(shí)表保存子查詢的結(jié)果。

4. type（重要）

這是最重要的字段之一，顯示查詢使用了何種類型。從最好到最差的連接類型依次為：

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

除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一個(gè)索引。

• 1、system

表中只有一行數(shù)據(jù)或者是空表，這是const類型的一個(gè)特例。且只能用于myisam和memory表。如果是Innodb引擎表，type列在這個(gè)情況通常都是all或者index

• 2、const

最多只有一行記錄匹配。當(dāng)聯(lián)合主鍵或唯一索引的所有字段跟常量值比較時(shí)，join類型為const。其他數(shù)據(jù)庫(kù)也叫做唯一索引掃描

• 3、eq_ref

多表join時(shí)，對(duì)于來自前面表的每一行，在當(dāng)前表中只能找到一行。這可能是除了system和const之外最好的類型。當(dāng)主鍵或唯一非NULL索引的所有字段都被用作join聯(lián)接時(shí)會(huì)使用此類型。

eq_ref可用于使用'='操作符作比較的索引列。比較的值可以是常量，也可以是使用在此表之前讀取的表的列的表達(dá)式。

相對(duì)于下面的ref區(qū)別就是它使用的唯一索引，即主鍵或唯一索引，而ref使用的是非唯一索引或者普通索引。
eq_ref只能找到一行，而ref能找到多行。

• 4、ref

對(duì)于來自前面表的每一行，在此表的索引中可以匹配到多行。若聯(lián)接只用到索引的最左前綴或索引不是主鍵或唯一索引時(shí)，使用ref類型（也就是說，此聯(lián)接能夠匹配多行記錄）。

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

• 5、 fulltext

使用全文索引的時(shí)候是這個(gè)類型。要注意，全文索引的優(yōu)先級(jí)很高，若全文索引和普通索引同時(shí)存在時(shí)，mysql不管代價(jià)，優(yōu)先選擇使用全文索引

• 6、ref_or_null

跟ref類型類似，只是增加了null值的比較。實(shí)際用的不多。

eg.
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

• 7、index_merge

表示查詢使用了兩個(gè)以上的索引，最后取交集或者并集，常見and ，or的條件使用了不同的索引，官方排序這個(gè)在ref_or_null之后，但是實(shí)際上由于要讀取多個(gè)索引，性能可能大部分時(shí)間都不如range

• 8、unique_subquery

用于where中的in形式子查詢，子查詢返回不重復(fù)值唯一值，可以完全替換子查詢，效率更高。
該類型替換了下面形式的IN子查詢的ref：
value IN (SELECT primary_key FROM single_table WHERE some_expr)

• 9、index_subquery

該聯(lián)接類型類似于unique_subquery。適用于非唯一索引，可以返回重復(fù)值。

• 10、range

索引范圍查詢，常見于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等運(yùn)算符的查詢中。

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

• 11、index

索引全表掃描，把索引從頭到尾掃一遍。這里包含兩種情況：
一種是查詢使用了覆蓋索引，那么它只需要掃描索引就可以獲得數(shù)據(jù)，這個(gè)效率要比全表掃描要快，因?yàn)樗饕ǔ１葦?shù)據(jù)表小，而且還能避免二次查詢。在extra中顯示Using index，反之，如果在索引上進(jìn)行全表掃描，沒有Using index的提示。

# 此表見有一個(gè)name列索引。
# 因?yàn)椴樵兊牧衝ame上建有索引，所以如果這樣type走的是index
mysql> explain select name from testa;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | testa | index | NULL          | idx_name | 33      | NULL |    2 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set

# 因?yàn)椴樵兊牧衏usno沒有建索引，或者查詢的列包含沒有索引的列，這樣查詢就會(huì)走ALL掃描，如下：
mysql> explain select cusno from testa;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | testa | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

# 包含有未見索引的列
mysql> explain select * from testa;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | testa | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

• 12、all

全表掃描，性能最差。

5. partitions

版本5.7以前，該項(xiàng)是explain partitions顯示的選項(xiàng)，5.7以后成為了默認(rèn)選項(xiàng)。該列顯示的為分區(qū)表命中的分區(qū)情況。非分區(qū)表該字段為空（null）。

6. possible_keys

查詢可能使用到的索引都會(huì)在這里列出來

7. key

查詢真正使用到的索引。
select_type為index_merge時(shí)，這里可能出現(xiàn)兩個(gè)以上的索引，其他的select_type這里只會(huì)出現(xiàn)一個(gè)。

8. key_len

查詢用到的索引長(zhǎng)度（字節(jié)數(shù)）。
如果是單列索引，那就整個(gè)索引長(zhǎng)度算進(jìn)去，如果是多列索引，那么查詢不一定都能使用到所有的列，用多少算多少。留意下這個(gè)列的值，算一下你的多列索引總長(zhǎng)度就知道有沒有使用到所有的列了。

key_len只計(jì)算where條件用到的索引長(zhǎng)度，而排序和分組就算用到了索引，也不會(huì)計(jì)算到key_len中。

9. ref

如果是使用的常數(shù)等值查詢，這里會(huì)顯示const，如果是連接查詢，被驅(qū)動(dòng)表的執(zhí)行計(jì)劃這里會(huì)顯示驅(qū)動(dòng)表的關(guān)聯(lián)字段，如果是條件使用了表達(dá)式或者函數(shù)，或者條件列發(fā)生了內(nèi)部隱式轉(zhuǎn)換，這里可能顯示為func

10. rows（重要）

rows 也是一個(gè)重要的字段。 這是mysql估算的需要掃描的行數(shù)（不是精確值）。
這個(gè)值非常直觀顯示 SQL 的效率好壞, 原則上 rows 越少越好.

11. filtered

這個(gè)字段表示存儲(chǔ)引擎返回的數(shù)據(jù)在server層過濾后，剩下多少滿足查詢的記錄數(shù)量的比例，注意是百分比，不是具體記錄數(shù)。這個(gè)字段不重要

12. extra（重要）

EXplain 中的很多額外的信息會(huì)在 Extra 字段顯示, 常見的有以下幾種內(nèi)容:

• distinct：在select部分使用了distinc關(guān)鍵字
• Using filesort：當(dāng) Extra 中有 Using filesort 時(shí), 表示 MySQL 需額外的排序操作, 不能通過索引順序達(dá)到排序效果. 一般有 Using filesort, 都建議優(yōu)化去掉, 因?yàn)檫@樣的查詢 CPU 資源消耗大.

# 例如下面的例子:

mysql> EXPLAIN SELECT * FROM order_info ORDER BY product_name \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: index
possible_keys: NULL
          key: user_product_detail_index
      key_len: 253
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)
我們的索引是

KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
但是上面的查詢中根據(jù) product_name 來排序, 因此不能使用索引進(jìn)行優(yōu)化, 進(jìn)而會(huì)產(chǎn)生 Using filesort.
如果我們將排序依據(jù)改為 ORDER BY user_id, product_name, 那么就不會(huì)出現(xiàn) Using filesort 了. 例如:

mysql> EXPLAIN SELECT * FROM order_info ORDER BY user_id, product_name \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: index
possible_keys: NULL
          key: user_product_detail_index
      key_len: 253
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

• Using index
  "覆蓋索引掃描", 表示查詢?cè)谒饕龢渲芯涂刹檎宜钄?shù)據(jù), 不用掃描表數(shù)據(jù)文件, 往往說明性能不錯(cuò)

• Using temporary
  查詢有使用臨時(shí)表, 一般出現(xiàn)于排序, 分組和多表 join 的情況, 查詢效率不高, 建議優(yōu)化.

除此之外還有其他值，這里就不一一一列舉了。

通過以上這些總結(jié)，以后我們看到explain的結(jié)果，就知道該從哪些字段值去分析sql語(yǔ)句的執(zhí)行效率了。