本文以MySQL 5.7 Reference Manual為主軸(翻譯&取其精華)並結合網文百家之長整理而成，因為筆者水平有限，文中如有不準確之處請包涵，如轉載請註明原文出處guocun09-Oraman的日記

基本概念：

EXPLAIN 提供SQL語句是怎麼樣執行的資訊，為select,delete,insert,replace,update語句工作。

EXPLAIN為查詢語句中使用到的每個table返回一行資訊。

MySQL中所有的join方式都是使用 nested-loop join

一．詳細說明

EXPLAIN Output Columns

1.select_type

2. type 訪問 型別

3.Extra 資訊 ( 常用附加資訊 )

執行計劃包含的資訊 id 有一組數字組成。表示一個查詢中各個子查詢的執行順序;

id相同執行順序由上至下。
id不同，id值越大優先順序越高，越先被執行。
id為null時表示一個結果集，不需要使用它查詢，常出現在包含union等查詢語句中。

select_type 每個子查詢的查詢型別，一些常見的查詢型別。

id	select_type	description
1	SIMPLE	不包含任何子查詢或union等查詢
2	PRIMARY	包含子查詢最外層查詢就顯示為 PRIMARY
3	SUBQUERY	在select或 where字句中包含的查詢
4	DERIVED	from字句中包含的查詢
5	UNION	出現在union後的查詢語句中
6	UNION RESULT	從UNION中獲取結果集，例如上文的第三個例子

type(非常重要，可以看到有沒有走索引) 訪問型別

ALL 掃描全表資料
index 遍歷索引
range 索引範圍查詢
index_subquery 在子查詢中使用 ref
unique_subquery 在子查詢中使用 eq_ref
ref_or_null 對Null進行索引的最佳化的 ref
fulltext 使用全文索引
ref 使用非唯一索引查詢資料
eq_ref 在join查詢中使用PRIMARY KEYorUNIQUE NOT NULL索引關聯。

possible_keys 可能使用的索引，注意不一定會使用。查詢涉及到的欄位上若存在索引，則該索引將被列出來。當該列為 NULL時就要考慮當前的SQL是否需要最佳化了。

key 顯示MySQL在查詢中實際使用的索引，若沒有使用索引，顯示為NULL。

TIPS:查詢中若使用了覆蓋索引(覆蓋索引：索引的資料覆蓋了需要查詢的所有資料)，則該索引僅出現在key列表中

key_length 索引長度

ref 表示上述表的連線匹配條件，即哪些列或常量被用於查詢索引列上的值

rows 返回估算的結果集數目，並不是一個準確的值。

extra 的資訊非常豐富，常見的有：

Using index 使用覆蓋索引
Using where 使用了用where子句來過濾結果集
Using filesort 使用檔案排序，使用非索引列進行排序時出現，非常消耗效能，儘量最佳化。
Using temporary 使用了臨時表 sql最佳化的目標可以參考阿里開發手冊

【推薦】SQL效能最佳化的目標：至少要達到 range 級別，要求是ref級別，如果可以是consts最好。
說明：
1） consts 單表中最多隻有一個匹配行（主鍵或者唯一索引），在最佳化階段即可讀取到資料。
2） ref 指的是使用普通的索引（normal index）。
3） range 對索引進行範圍檢索。
反例：explain表的結果，type=index，索引物理檔案全掃描，速度非常慢，這個index級別比較range還低，與全表掃描是小巫見大巫。

二 . 實驗

環境準備

CREATE DATABASE `gc` /*!40100 DEFAULT CHARACTER SET utf8 */;

use gc;

CREATE TABLE `emp` (

`emp_no` varchar(20) NOT NULL,

`emp_name` varchar(30) NOT NULL,

`age` int(11) DEFAULT NULL,

`dept` varchar(45) DEFAULT NULL,

PRIMARY KEY (`emp_no`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into emp values ('MW00001','Oraman',30,'1');

insert into emp values ('MW00002','GC',25,'2');

insert into emp values ('MW00003','Tom Kyte',50,'1');

insert into emp values ('MW00004','Jack Ma',40,'3');

insert into emp values ('MW00005','James',33,'4');

CREATE TABLE `dept` (

`dept_no` varchar(45) NOT NULL,

`dept_name` varchar(30) NOT NULL,

`dept_header` varchar(20) DEFAULT NULL,

PRIMARY KEY (`dept_no`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into dept values ('1','DBA','MW00003');

insert into dept values ('2','DEV','MW00002');

insert into dept values ('3','BOD','MW00004');

insert into dept values ('4','Business','MW00005');

例 1.

mysql> explain select * from emp where dept='1';

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

解釋：Simple 簡單的單表查詢，type:all 全表掃描，Extra:Using where 使用where子句

例 2.

mysql> explain select * from emp where emp_no='MW00001';

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

| 1 | SIMPLE | emp | NULL | const | PRIMARY | PRIMARY | 62 | const | 1 | 100.00 | NULL |

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

解釋：Simple 簡單的單表查詢，type: const 使用到PK，possible_keys:可能使用到index為PRIMARY，key:實際使用到index為PRIMARY

例 3.

mysql> explain select * from emp a,dept b where a.emp_name='Oraman' and a.dept=b.dept_no;

+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+

+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+

| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |

| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 137 | gc.a.dept | 1 | 100.00 | NULL |

+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+

解釋：兩行id相同都是1，以第一行做為驅動表先執行。

Simple 簡單的單表查詢，第一行type:all 全表掃描，第二行type: eq_ref a表與b表連線使用到= 且只有一行，ref:gc.a.dept 透過a表dept欄位連線b表

例 4.

mysql> explain select * from dept b where exists (select * from emp a where age>30 and a.dept=b.dept_no);

+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | PRIMARY | b | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where |

| 2 | DEPENDENT SUBQUERY | a | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |

+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

解釋：id為2的做為驅動表第2行先執行，select_type：DEPENDENT SUBQUERY 子查詢並依賴外部查詢結果集。第1行select_type：PRIMARY 最外層的select

以上幾個基本的EXPLAIN例子看懂了嗎？是不是很簡單，和Oracle的區別請自己領悟了。。

MySQL執行計劃EXPLAIN詳解

相關文章