MySQL執(zhí)行計(jì)劃EXPLAIN詳解

2024-07-24 12:32:10

字體：大中小

供稿：網(wǎng)友

本文以MySQL 5.7 Reference Manual為主軸(翻譯&取其精華)并結(jié)合網(wǎng)文百家之長整理而成，因?yàn)楣P者水平有限，文中如有不準(zhǔn)確之處請包涵，如轉(zhuǎn)載請注明原文出處guocun09-Oraman的日記

基本概念：

EXPLAIN 提供SQL語句是怎么樣執(zhí)行的信息，為select,delete,insert,replace,update語句工作。

EXPLAIN為查詢語句中使用到的每個(gè)table返回一行信息。

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

一．詳細(xì)說明

EXPLAIN Output Columns

列名

解釋

說明

id

select標(biāo)識符

Query Optimizer選定執(zhí)行計(jì)劃中查詢的序列號。表示查詢中執(zhí)行select子句或操作表的順序，id值越大優(yōu)先級越高，越先被執(zhí)行。id 相同，執(zhí)行順序由上至下

select_type

select類型

沒有子查詢或union時(shí)都是simple，否則會有primary和union之類的，這里要注意帶有uncacheable的類型，表示無法緩存，外層行切換會導(dǎo)致重新計(jì)算該select

table

輸出行所屬的表

表名或<unionM,N>，<derivedN>，<subqueryN>

partitions

匹配的分區(qū)

涉及到表的分區(qū)，沒有使用分區(qū)則是NULL

type

join類型

下面有詳細(xì)說明

possible_keys

可能被選擇的索引

MySQL能在該表中使用哪些index助于查詢，如果為空，說明沒有可用index

key

實(shí)際被選擇的索引

實(shí)際決定選擇的index,如果沒有選擇index,值為NULL

key_len

被選擇的鍵的長度

MySQL在多部分索引中使用的部分的長度，可能有多個(gè)值

ref

需要與索引比較(連接)的列

列名或者const（常數(shù)，where id = 1的時(shí)候就是const了）

rows

估計(jì)要被檢驗(yàn)的行數(shù)

InnoDB中不一定精確，只是一個(gè)估計(jì)值

filtered

被表的條件所過濾的行的百分比

估計(jì)值

extra

額外信息

附加信息

1.select_type

select_type類型

說明

SIMPLE

簡單的select查詢，不使用 union 及子查詢

PRIMARY

最外層的select查詢

UNION

UNION 中的第二個(gè)或隨后的select查詢，不依賴于外部查詢的結(jié)果集

DEPENDENT UNION

UNION 中的第二個(gè)或隨后的select查詢，依賴于外部查詢的結(jié)果集

SUBQUERY

子查詢中的第一個(gè)select查詢，不依賴于外部查詢的結(jié)果集

DEPENDENT SUBQUERY

子查詢中的第一個(gè)select查詢，依賴于外部查詢的結(jié)果集

DERIVED

用于from子句里有子查詢的情況。 MySQL會遞歸執(zhí)行這些子查詢，把結(jié)果放在臨時(shí)表里

UNCACHEABLE SUBQUERY

結(jié)果集不能被緩存的子查詢，必須重新為外層查詢的每一行進(jìn)行評估

UNCACHEABLE UNION

UNION 中的第二個(gè)或隨后的select查詢，屬于不可緩存的子查詢

2.Join類型(type欄位)

Join類型

(按最優(yōu)到最差排序)

說明

system

表只有一行(=system表)

const

表最多只有一行匹配，通常用到：PK或Unique index

eq_ref

每次與之前的表合并行都只在該表讀取一行，這是除了system，const之外最好的一種，

特點(diǎn)是使用=，而且索引的所有部分都參與join且索引是主鍵或非空唯一鍵的索引

ref

使用=或<=>，可以是最左前綴索引或非主鍵或非唯一鍵，如果每次只匹配少數(shù)行，那會是比較好的

fulltext

全文索引搜索

ref_or_null

與ref類似，但包括NULL

例：SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

index_merge

索引合并，比如一個(gè)table中有多個(gè)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 欄位顯示使用了哪個(gè)索引

通常用到：=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, or IN()

index

index全掃描，兩種情形：

1.僅僅掃描整個(gè)index tree，這時(shí)Extra欄位為Using index

2.按照index 順序全表掃描，這時(shí)Extra欄位不會出現(xiàn)Using index

all

全表掃描

3.Extra信息(常用附加信息)

Extra信息

說明

const row not found

Table was empty

distinct

查詢唯一值，發(fā)現(xiàn)到一個(gè)匹配的就停止當(dāng)前搜索

FirstMatch(tbl_name)

The semi-join FirstMatch join shortcutting strategy is used for tbl_name.

No tables used

查詢沒有from子句，或有from dual 子句

No exists

優(yōu)化了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,從前面一個(gè)表中找一個(gè)行的組合，mysql檢查那個(gè)index 能否range或者index merge方式從表中返回?cái)?shù)據(jù)。它不是很快，但比沒有index要好

Using fliesort

使用排序檢索，出現(xiàn)時(shí)性能可能不高

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過程中構(gòu)造一張臨時(shí)表，常見order by,group by中。出現(xiàn)時(shí)性能可能不高

Using where

有where子句

二. 實(shí)驗(yàn)

環(huán)境準(zhǔn)備

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:實(shí)際使用到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，以第一行做為驅(qū)動(dòng)表先執(zhí)行。

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的做為驅(qū)動(dòng)表第2行先執(zhí)行，select_type：DEPENDENT SUBQUERY 子查詢并依賴外部查詢結(jié)果集。第1行select_type：PRIMARY 最外層的select

以上幾個(gè)基本的EXPLAIN例子看懂了嗎？是不是很簡單，和Oracle的區(qū)別請自己領(lǐng)悟了。

（編輯：武林網(wǎng)）

上一篇：MySQL主從同步報(bào)error 1236

下一篇：mysql grant 用戶權(quán)限總結(jié)