溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本文以MySQL 5.7 Reference Manual為主軸(翻譯&取其精華)并結合網文百家之長整理而成，因為筆者水平有限，文中如有不準確之處請包涵，如轉載請注明原文出處guocun09-Oraman的日記
基本概念：
EXPLAIN 提供SQL語句是怎么樣執行的信息，為select,delete,insert,replace,update語句工作。
EXPLAIN為查詢語句中使用到的每個table返回一行信息。
MySQL中所有的join方式都是使用nested-loop join
一．詳細說明
EXPLAIN Output Columns
|
列名 |
解釋 |
說明 |
|
id |
select標識符 |
Query Optimizer選定執行計劃中查詢的序列號。表示查詢中執行select子句或操作表的順序，id值越大優先級越高，越先被執行。id 相同，執行順序由上至下 |
|
select_type |
select類型 |
沒有子查詢或union時都是simple，否則會有primary和union之類的，這里要注意帶有uncacheable的類型，表示無法緩存，外層行切換會導致重新計算該select |
|
table |
輸出行所屬的表 |
表名或<unionM,N>，<derivedN>，<subqueryN> |
|
partitions |
匹配的分區 |
涉及到表的分區，沒有使用分區則是NULL |
|
type |
join類型 |
下面有詳細說明 |
|
possible_keys |
可能被選擇的索引 |
MySQL能在該表中使用哪些index助于查詢，如果為空，說明沒有可用index |
|
key |
實際被選擇的索引 |
實際決定選擇的index,如果沒有選擇index,值為NULL |
|
key_len |
被選擇的鍵的長度 |
MySQL在多部分索引中使用的部分的長度，可能有多個值 |
|
ref |
需要與索引比較(連接)的列 |
列名或者const（常數，where id = 1的時候就是const了） |
|
rows |
估計要被檢驗的行數 |
InnoDB中不一定精確，只是一個估計值 |
|
filtered |
被表的條件所過濾的行的百分比 |
估計值 |
|
extra |
額外信息 |
附加信息 |
1.select_type
select_type類型
說明
SIMPLE
簡單的select查詢，不使用 union 及子查詢
PRIMARY
最外層的select查詢
UNION
UNION 中的第二個或隨后的select查詢，不依賴于外部查詢的結果集
DEPENDENT UNION
UNION 中的第二個或隨后的select查詢，依賴于外部查詢的結果集
SUBQUERY
子查詢中的第一個select查詢，不依賴于外部查詢的結果集
DEPENDENT SUBQUERY
子查詢中的第一個select查詢，依賴于外部查詢的結果集
DERIVED
用于from子句里有子查詢的情況。 MySQL會遞歸執行這些子查詢，把結果放在臨時表里
UNCACHEABLE SUBQUERY
結果集不能被緩存的子查詢，必須重新為外層查詢的每一行進行評估
UNCACHEABLE UNION
UNION 中的第二個或隨后的select查詢，屬于不可緩存的子查詢
2.Join類型(type欄位)
Join類型
(按最優到最差排序)
說明
system
表只有一行(=system表)
const
表最多只有一行匹配，通常用到：PK或Unique index
eq_ref
每次與之前的表合并行都只在該表讀取一行，這是除了system，const之外最好的一種，
特點是使用=，而且索引的所有部分都參與join且索引是主鍵或非空唯一鍵的索引
ref
使用=或<=>，可以是最左前綴索引或非主鍵或非唯一鍵，如果每次只匹配少數行，那會是比較好的
fulltext
全文索引搜索
ref_or_null
與ref類似，但包括NULL
例：SELECT * FROM ref_table WHERE key_column=expr OR
key_column IS NULL;
index_merge
索引合并，比如一個table中有多個index column在where條件中
例：SELECT * FROM ref_table WHERE key_column1=expr1and key_column2=expr2;
unique_subquery
僅僅只是索引查找，取代子查詢完全獲得更好的效率
例：value IN (SELECT primary_key
FROM single_table WHERE some_expr)
index_subquery
同上，但替換子查詢中的”select
non_unique_key_column“
range
index范圍檢索，key 欄位顯示使用了哪個索引
通常用到：=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN,
or IN()
index
index全掃描，兩種情形：
1.僅僅掃描整個index tree，這時Extra欄位為Using
index
2.按照index 順序全表掃描，這時Extra欄位不會出現Using index
all
全表掃描
3.Extra信息(常用附加信息)
|
Extra信息 |
說明 |
|
const row not found |
Table was empty |
|
distinct |
查詢唯一值，發現到一個匹配的就停止當前搜索 |
|
FirstMatch(tbl_name) |
The semi-join FirstMatch join shortcutting strategy is used for tbl_name. |
|
No tables used |
查詢沒有from子句，或有from dual 子句 |
|
No exists |
優化了left join，一旦找到了配置left join的行就不再檢索，例如： SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL; |
|
Range checked for each record (index map: N) |
沒找到理想的index,從前面一個表中找一個行的組合，mysql檢查那個index 能否range或者index merge方式從表中返回數據。它不是很快，但比沒有index要好 |
|
Using fliesort |
使用排序檢索，出現時性能可能不高 |
|
Using index |
Index scan,不需要回表 |
|
Using index condition |
|
|
Using join buffer |
Block Nested Loop, Tables from earlier joins are read in portions into the join buffer, and then their rows are used from the buffer to perform the join with the current table. |
|
Using temporary |
Query過程中構造一張臨時表，常見order by,group by中。出現時性能可能不高 |
|
Using where |
有where子句 |
二. 實驗
環境準備
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';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 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;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| 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);
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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的區別請自己領悟了。。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
