一篇掌握mysql explain-白红宇

一篇掌握mysql explain

阅读量：4303 次

发布时间：2019-05-27

本文共 12816 字，大约阅读时间需要 42 分钟。

本文主要介绍下explain。

我们可以通过explian可以查看sql的执行计划，分析sql语句和表结构的性能瓶颈。

explain的使用十分简单，通过在查询语句前面加一个explain关键字即可。

更详细资料请查看

1.explain字段

explain查看执行计划，共有以下信息返回

id: 序号，表示执行顺序

select_type: 查询类型

table: 访问的表

partitions: 分区

type: 访问的类型

possible_keys: 可能使用到的索引，实际不一定使用

key: 实际使用的索引，为null表示没有使用索引

key_len: 索引使用的字节数

ref: 列与索引的比较

rows: 根据统计信息，估算出可能需要读取的行数

filtered: 查询的表行数/表的百分比

extra: 一些重要的额外信息

查询sql根据复杂程度分以下几个类别

简单

复杂
- 简单子查询 (select、where中有子查询)
- 派生表 (from中有子查询)
- union查询

2.id

SELECT识别符，每个select语句都会自动分配的一个唯一标识符。SQL执行的顺序的标识。遵循以下原则

id从大到小的执行

id相同时，执行顺序由上至下

id列为null表示为结果集，不需要使用这个语句来查询

2-1.id相同

查询演员id为1的电影信息

explain select * from film f where f.film_id in (select film_Id from film_actor fa where fa.actor_id=1)- 或者explain select f.* from film f, film_actor fa where fa.film_id = f.film_id and fa.actor_id = 1

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	fa		ref	PRIMARY,idx_fk_film_id	PRIMARY	2	const	19	100.0	Using index
1	SIMPLE	f		eq_ref	PRIMARY	PRIMARY	2	sakila.fa.film_id	1	100.0

id相同，由上至下，先查fa(film_actor)，再查f(film)表

2-2.id不同

查询演员id为1和2的电影信息

explain select * from film f where f.film_id in (select film_Id from film_actor fa where fa.actor_id in (1,2));

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	`<subquery2>`	ALL						100.0
1	SIMPLE	f	eq_ref	PRIMARY	PRIMARY	2	`<subquery2>`.film_Id	1	100.0
2	MATERIALIZED	fa	range	PRIMARY,idx_fk_film_id	PRIMARY	2		44	100.0	Using where; Using index

函数

explain select f.title ,(select ceil(rand()*10) from dual)  as randnum from film f;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	f		index		idx_title	514		1000	100.0	Using index
2	UNCACHEABLE SUBQUERY										No tables used

2-3.null

explain select film_id, title, release_year from film where rental_rate = 4.99 union select film_id, title, release_year from film where rental_rate = 3.99

id	select_type	table	type	rows	filtered	Extra
1	PRIMARY	film	ALL	1000	10.0	Using where
2	UNION	film	ALL	1000	10.0	Using where
	UNION RESULT	`<union1,2>`	ALL			Using temporary

<union1,2>表示依赖id为1和2的

3.select_type

simple: 简单查询。查询不包含子查询和union

primary：复杂查询(子查询、union查询)中最外层的select

subquery: 除了from子句中包含的子查询外，其它地方出现的子查询都可能是subquery

derived：包含在from子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

union：在union中的第二个和随后的select

union result：从union临时表检索结果的select

dependent subquery: 子查询的结果受到外层的影响

dependent union: UNION中的第二个或后面的SELECT语句，取决于外面的查询

materialized: 物化子查询

uncacheable subquery: 子查询，结果无法缓存，必须针对外部查询的每一行重新评估

uncacheable union: 属于UNCACHEABLE SUBQUERY的第二个或后面的查询

select_type细节比较多，所以单独一节来介绍，具体请查看

4.table

table: 访问的表，可能出现的值

表名: 这不用了多讲解，从一个表中查询

<unionM,N>: 有union操作的时候，UNION RESULT合并的时候，M和N代码依赖的执行计划id序号

<subqueryN>: 当简单子查询的时候，会出现。表示临时表，N表示执行计划中id

derived: from子查询时候，会出现

空: 一些函数，不需要通过表获取数据

示例1

explain select * from film where film_id in (select film_Id from film_actor where actor_id in (1,2));

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	`<subquery2>`	ALL						100.0
1	SIMPLE	film	eq_ref	PRIMARY	PRIMARY	2	`<subquery2>`.film_Id	1	100.0
2	MATERIALIZED	film_actor	range	PRIMARY,idx_fk_film_id	PRIMARY	2		44	100.0	Using where; Using index

示例2

explain select film_id, title, release_year from film where rental_rate = 4.99 union select film_id, title, release_year from film where rental_rate = 3.99

id	select_type	table	type	rows	filtered	Extra
1	PRIMARY	film	ALL	1000	10.0	Using where
2	UNION	film	ALL	1000	10.0	Using where
	UNION RESULT	`<union1,2>`	ALL			Using temporary

示例3

explain select tn.randnum  from (select rand()*10  as randnum from dual) as tn;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	`<derived2>`		system					1	100.0
2	DERIVED										No tables used

<derived2>表示依赖于id为1的，而且是派生表

5.partitions

paritions对于分区表才会有效，未分区表则未空。

分区按照维度可以分为水平和垂直

水平分区的模式
- Range（范围）: 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980’s）的数据，90年代（1990’s）的数据以及任何在2000年（包括2000年）后的数据。
- Hash（哈希）:这种模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如DBA可以建立一个对表主键进行分区的表。
- Key（键值）: Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。
- List（预定义列表）: 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。
- Composite（复合模式）: 以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。

垂直分区（按列分）：

举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。

如果对分区表有兴趣的，可以阅读下这篇文章，这就不多介绍了。

分区建表语句

CREATE TABLE p_student ( id int NOT NULL AUTO_INCREMENT COMMENT 'id', name varchar(30) default '' COMMENT '姓名', grade int(2) default 1 COMMENT '年级', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ciPARTITION BY range (id) (PARTITION p0 VALUES LESS THAN (10000),PARTITION p1 VALUES LESS THAN (20000),PARTITION p2 VALUES LESS THAN (30000) ,PARTITION p3 VALUES LESS THAN (40000) ,PARTITION p5 VALUES LESS THAN (50000),PARTITION p6 VALUES LESS THAN (60000),PARTITION p7 VALUES LESS THAN MAXVALUE );

sql查询

explain select * from p_student where id < 30000

执行计划结果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	p_student	p0,p1,p2	range	PRIMARY	PRIMARY	4		1	100.0	Using where

id小于30000的数据在p0,p1,p2分区中

6.type

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

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

一般来说，得保证查询至少达到range级别，最好能达到ref

system: 表只有一条记录(等于系统表),这是const类型的特例，平时业务中不会出现，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index

const: 通过索引一次查到数据，该类型主要用于比较primary key 或者unique 索引，因为只匹配一行数据，所以很快;如果将主键置于WHERE语句后面，Mysql就能将该查询转换为一个常量。

eq_ref: 多表关联查询时，被驱动表是主键或唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配，且为非空not null。

ref: 不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。

fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引

ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。

unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值

index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

range：只检索给定范围的行，使用一个索引来选着行。key列显示使用了哪个索引。一般在你的WHERE 语句中出现between 、< 、> 、in 等查询，这种给定范围扫描比全表扫描要好。因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

index：扫描遍历索引树(扫描全表的索引，从索引中获取数据)。常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

ALL: 全表扫描,然后再在server层进行过滤返回符合要求的记录。从磁盘中获取数据百万级别的数据ALL类型的数据尽量优化。

type细节比较多，所以单独一节来介绍，具体请查看

7.possible_keys

possible_keys: 可能使用到的索引，实际不一定使用

key: 实际使用的索引，为null表示没有使用索引

key_len: 索引使用的字节数

这三个我们一起来演示

possible_keys有值，key才可能有值；possible_keys为空，key必为空。

key有值，key_len有值。key为空，key_len必为空。

7-1.possible_keys

使用索引的示例

explainselect language_id from film where language_id = 1

有where条件，language_id是普通索引，不是全表扫描。会有多条数据返回，所以是ref，而不是const。

执行计划结果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	film		ref	idx_fk_language_id	idx_fk_language_id	1	const	1000	100.0	Using index

没有使用索引的示例

explainselect * from film where language_id = 1

有where条件，，language_id是普通索引，不是全表扫描，查询字段是所有字段，language_id记录很多。
mysql认为全表all扫描处理的效率比通过language_id找到对于的主键索引后，再通过主键索引找到字段的效率高。所以使用了all

执行计划结果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	film		ALL	idx_fk_language_id				1000	100.0	Using where

即使possible_keys有值，但是key还时为空，即实际也不一定使用到。

7-2.key值多条

一般key都为一个值，当type为index_merge时，会出现多个值。

查询sql

explainselect * from film where film_id =10 or title ='ACADEMY DINOSAUR' or original_language_id =2

由于结果比较长，所以使用列方式打印出来了。

执行计划结果

Name	Value
id	1
select_type	SIMPLE
table	film
partitions
type	index_merge
possible_keys	PRIMARY,idx_title,idx_fk_original_language_id
key	PRIMARY,idx_title,idx_fk_original_language_id
key_len	2,514,2
ref
rows	3
filtered	100.0
Extra	Using union(PRIMARY,idx_title,idx_fk_original_language_id); Using where

7-3.key_len计算

计算公式

列类型	KEY_LEN	备注
int	`key_len = 4+1`	int为4bytes,允许为NULL,加1byte
bigint not null	`key_len=8`	bigint为8bytes
char(30) utf8	`key_len=30*3+1`	utf8每个字符为3bytes,允许为NULL,加1byte
varchar(30) not null utf8	`key_len=30*3+2`	utf8每个字符为3bytes,变长数据类型,加2bytes
varchar(30) utf8	`key_len=30*3+2+1`	utf8每个字符为3bytes,允许为NULL,加1byte,变长数据类型,加2bytes
text(10) utf8	`key_len=30*3+2+1`	TEXT截取部分,被视为动态列类型。

key_len只指示了where中用于条件过滤时被选中的索引列，是不包含order by/group by这一部分被选中的索引列的。

可以为null的长度需要+1。

如果不记得类型长度的可以查看下第一节的

我们以film来说明，我们先看下其索引的结构

CREATE TABLE film (  film_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,  title VARCHAR(128) NOT NULL,  description TEXT DEFAULT NULL,  release_year YEAR DEFAULT NULL,  language_id TINYINT UNSIGNED NOT NULL,  original_language_id TINYINT UNSIGNED DEFAULT NULL,  rental_duration TINYINT UNSIGNED NOT NULL DEFAULT 3,  rental_rate DECIMAL(4,2) NOT NULL DEFAULT 4.99,  length SMALLINT UNSIGNED DEFAULT NULL,  replacement_cost DECIMAL(5,2) NOT NULL DEFAULT 19.99,  rating ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G',  special_features SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY  (film_id),  KEY idx_title (title),  KEY idx_fk_language_id (language_id),  KEY idx_fk_original_language_id (original_language_id),  CONSTRAINT fk_film_language FOREIGN KEY (language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE,  CONSTRAINT fk_film_language_original FOREIGN KEY (original_language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

film里每个索引计算长度结果为

索引名	字段	类型	长度(字节)	备注
PRIMARY KEY	film_id	SMALLINT	2 bytes	SMALLINT为 2 bytes,不允许为NULL,不加1byte
idx_title	title	VARCHAR(128)	`128*4+2=514` bytes	utf8mb4每个字符为4bytes,变长数据类型,加2bytes,不为NULL,不加1byte
idx_fk_language_id	language_id	TINYINT	1 byte	TINYINT为 1 bytes,不允许为NULL,不加1byte
idx_fk_original_language_id	original_language_id	TINYINT	1 byte	TINYINT为 1 bytes,不允许为NULL,不加1byte

我们使用的编码时utf8mb4，而不是utf8。所以一个字符是4个字节。

下面我们以不同的索引作为条件来查询，查看执行计划结果。

以lfilm_id查询

explainselect film_id from film where film_id = 1

key_len的长度为2

以language_id查询

explainselect film_id from film where language_id = 1

key_len的长度为2

以title查询

explainselect title from film where title = 'ACADEMY DINOSAUR'

key_len的长度为514

128*4+2=514 utf8mb4每个字符为4bytes,变长数据类型,加2bytes,不为NULL,不加1byte

8.ref

ref 会显示以下几种值

如果是使用的常数等值查询，这里会显示const

关联查询，被驱动表的ref列，显示驱动表的关联字段

如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

::: tip

条件列上要有索引，如果不是索引的话，那么是all全表，ref为空

:::

8-3.常量

explainselect * from film where title = 'AFRICAN EGG'

执行计划结果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	film		ref	idx_title	idx_title	514	const	1	100.0

title = 'AFRICAN EGG' 其中AFRICAN EGG为常量，且title为索引列，所以ref为const

8-2.关联查询

explainselect film_actor.actor_id , film.title from film_actor left join film on film.film_id = film_actor.film_id

执行计划结果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	film_actor		index		idx_fk_film_id	2		5462	100.0	Using index
1	SIMPLE	film		eq_ref	PRIMARY	PRIMARY	2	sakila.film_actor.film_id	1	100.0

关联查询，被驱动表film的ref列，显示驱动表film_actor的关联字段sakila.film_actor.film_id

9.rows

rows：这里是执行计划中估算的扫描行数，不是精确值

filtered: server过滤后数据给客户端数量/引擎层返回给server数据量百分比。

这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。

explainselect film_actor.actor_id , film.title from film_actor left join film on film.film_id = film_actor.film_idwhere film_actor.actor_id  = 1

执行计划结果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	film_actor		ref	PRIMARY	PRIMARY	2	const	19	100.0	Using index
1	SIMPLE	film		eq_ref	PRIMARY	PRIMARY	2	sakila.film_actor.film_id	1	100.0

通常来说rows越小越好，io消耗越少。

filtered是百分比，越大越好，说明引擎层给server层的数据都是有用的数据。

10.extra

extra: 一些重要的额外信息。这个列可以显示的信息非常多，有几十种，常用的有

distinct：在select部分使用了distinc关键字

no tables used：不带from字句的查询或者From dual查询

使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接。即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。

using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中

using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。

using join buffer（block nested loop），using join buffer（batched key accss）：5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。

using sort_union，using_union，using intersect，using sort_intersection
- using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集
- using union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集
- using sort_union和using sort_intersection：与前面两个对应的类似，只是他们是出现在用and和or查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。
- using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。

using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition