MySQL执行计划EXPLAIN详解

2024-07-24 12:32:10

字体：大中小

来源：转载

供稿：网友

本文以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的区别请自己领悟了。

（编辑：武林网）

上一篇：MySQL主从同步报error 1236

下一篇：mysql grant 用户权限总结