本文主要介绍下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 = 1id | 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 | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
1 | SIMPLE |
| ALL | 100.0 | |||||||
1 | SIMPLE | f | eq_ref | PRIMARY | PRIMARY | 2 |
| 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.99id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
1 | PRIMARY | film | ALL | 1000 | 10.0 | Using where | |||||
2 | UNION | film | ALL | 1000 | 10.0 | Using where | |||||
UNION RESULT |
| 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细节比较多，所以单独一节来介绍，具体请查看 explain之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 | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
1 | SIMPLE |
| ALL | 100.0 | |||||||
1 | SIMPLE | film | eq_ref | PRIMARY | PRIMARY | 2 |
| 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.99id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
1 | PRIMARY | film | ALL | 1000 | 10.0 | Using where | |||||
2 | UNION | film | ALL | 1000 | 10.0 | Using where | |||||
UNION RESULT |
| 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 |
| 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了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。
如果对分区表有兴趣的，可以阅读下这篇文章深入解析MySQL分区Partition功能，这就不多介绍了。
分区建表语句
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_ci
PARTITION 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类型的数据尽量优化。
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
使用索引的示例
explain
select 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 |
没有使用索引的示例
explain
select * 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
explain
select * 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 |
| int为4bytes,允许为NULL,加1byte |
bigint not null |
| bigint为8bytes |
char(30) utf8 |
| utf8每个字符为3bytes,允许为NULL,加1byte |
varchar(30) not null utf8 |
| utf8每个字符为3bytes,变长数据类型,加2bytes |
varchar(30) utf8 |
| utf8每个字符为3bytes,允许为NULL,加1byte,变长数据类型,加2bytes |
text(10) utf8 |
| 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) |
| 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查询
explain
select film_id from film
where film_id = 1
key_len的长度为2
以language_id查询
explain
select film_id from film
where language_id = 1
key_len的长度为2
以title查询
explain
select title from film
where title = 'ACADEMY DINOSAUR'
key_len的长度为514
128*4+2=514utf8mb4每个字符为4bytes,变长数据类型,加2bytes,不为NULL,不加1byte
8.ref
ref 会显示以下几种值
- 如果是使用的常数等值查询，这里会显示const
- 关联查询，被驱动表的ref列，显示驱动表的关联字段
- 如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func
::: tip
条件列上要有索引，如果不是索引的话，那么是all全表，ref为空
:::
8-3.常量
explain
select * 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.关联查询
explain
select 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层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。
explain
select film_actor.actor_id , film.title
from film_actor
left join film
on film.film_id = film_actor.film_id
where 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
- firstmatch(tb_name)：5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个
- loosescan(m…n)：5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个
