MySQL 执行计划 EXPLAIN


这一章我们就来详细分析执行计划中各个显示项的内容。EXPLAIN 命令执行结果如下图所示:


EXPLAIN SELECT * FROM users 
WHERE id IN (SELECT userID FROMuser_address WHERE address = "湖南长沙麓谷") ;


image.png


执行计划的 id


select 查询的序列号,标识执行的顺序



执行计划的 select_type


查询的类型,主要是用于区分普通查询、联合查询、子查询等。



关于 MATERIALIZED 的详细解释参考 MySQL 官方文档:https://dev.mysql.com/doc/refman/8.0/en/subquery-materialization.html


对于 UNION 和 UNION RESULT 可以通过下面的例子展现:


EXPLAIN
SELECT * FROM users WHERE id IN(1, 2)
UNION
SELECT * FROM users WHERE id IN(3, 4);

image.png

mysql> explain select * from lineitem where l_shipdate <= '1995-12-31' union select * from lineitem where l_shipdate >= '1997-01-01';
+----+--------------+------------+------------+------+---------------+------+---------+------+---------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+---------+----------+-----------------+
|  1 | PRIMARY      | lineitem   | NULL       | ALL  | i_l_shipdate  | NULL | NULL    | NULL | 5606187 |    50.00 | Using where     |
|  2 | UNION        | lineitem   | NULL       | ALL  | i_l_shipdate  | NULL | NULL    | NULL | 5606187 |    50.00 | Using where     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+---------+----------+-----------------+
mysql> explain select emp_no, dept_no, (select count(1) from dept_emp t2 where t1.emp_no <= t2.emp_no) as row_num from dept_emp t1;
+----+--------------------+-------+------------+-------+----------------+--------+---------+------+--------+----------+------------------------------------------------+
| id | select_type        | table | partitions | type  | possible_keys  | key    | key_len | ref  | rows   | filtered | Extra                                          |
+----+--------------------+-------+------------+-------+----------------+--------+---------+------+--------+----------+------------------------------------------------+
|  1 | PRIMARY            | t1    | NULL       | index | NULL           | emp_no | 4       | NULL | 331143 |   100.00 | Using index                                    |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | ALL   | PRIMARY,emp_no | NULL   | NULL    | NULL | 331143 |    33.33 | Range checked for each record (index map: 0x3) |
+----+--------------------+-------+------------+-------+----------------+--------+---------+------+--------+----------+------------------------------------------------+
2 rows in set, 2 warnings (0.01 sec)
mysql> explain select * from orders where o_orderdate in (select max(l_shipdate) from lineitem group by (date_format(l_shipdate, '%Y%M')));
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+---------+----------+----------------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key          | key_len | ref  | rows    | filtered | Extra                                        |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+---------+----------+----------------------------------------------+
|  1 | PRIMARY     | orders   | NULL       | ALL   | NULL          | NULL         | NULL    | NULL | 1489118 |   100.00 | Using where                                  |
|  2 | SUBQUERY    | lineitem | NULL       | index | i_l_shipdate  | i_l_shipdate | 4       | NULL | 5606187 |   100.00 | Using index; Using temporary; Using filesort |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+---------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.01 sec)
mysql> explain select * from part where p_partkey in (select l_partkey from lineitem where l_shipdate between '1997-01-01' and '1997-02-01') limit 10;
+----+--------------+-------------+------------+--------+----------------------------------------------+--------------+---------+------------------------+--------+----------+----------------------------------+
| id | select_type  | table       | partitions | type   | possible_keys                                | key          | key_len | ref                    | rows   | filtered | Extra                            |
+----+--------------+-------------+------------+--------+----------------------------------------------+--------------+---------+------------------------+--------+----------+----------------------------------+
|  1 | SIMPLE       | part        | NULL       | ALL    | PRIMARY                                      | NULL         | NULL    | NULL                   | 198005 |   100.00 | Using where                      |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>                                   | <auto_key>   | 5       | dbt3_s1.part.p_partkey |      1 |   100.00 | NULL                             |
|  2 | MATERIALIZED | lineitem    | NULL       | range  | i_l_shipdate,i_l_suppkey_partkey,i_l_partkey | i_l_shipdate | 4       | NULL                   | 140752 |   100.00 | Using index condition; Using MRR |
+----+--------------+-------------+------------+--------+----------------------------------------------+--------------+---------+------------------------+--------+----------+----------------------------------+

-- 通过 show warnings 命令查看实际执行的 SQL 语句,发现上面的 SQL 已经被优化成 JOIN
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `dbt3_s1`.`part`.`p_partkey` AS `p_partkey`,`dbt3_s1`.`part`.`p_name` AS `p_name`,`dbt3_s1`.`part`.`p_mfgr` AS `p_mfgr`,`dbt3_s1`.`part`.`p_brand` AS `p_brand`,`dbt3_s1`.`part`.`p_type` AS `p_type`,`dbt3_s1`.`part`.`p_size` AS `p_size`,`dbt3_s1`.`part`.`p_container` AS `p_container`,`dbt3_s1`.`part`.`p_retailprice` AS `p_retailprice`,`dbt3_s1`.`part`.`p_comment` AS `p_comment` from `dbt3_s1`.`part` semi join (`dbt3_s1`.`lineitem`) where ((`<subquery2>`.`l_partkey` = `dbt3_s1`.`part`.`p_partkey`) and (`dbt3_s1`.`lineitem`.`l_shipDATE` between '1997-01-01' and '1997-02-01')) limit 10 |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

执行计划的 table


查询涉及到的表。



执行计划的 type


访问类型,SQL 查询优化中一个很重要的指标,结果值从好到坏依次是:system > const > eq_ref > ref > range > index > ALL。



官方文档:https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types


官方文档中的 type 类型要多一些。


下面通过举例说明。


system


explain select * from mysql.time_zone;


image


上例中,从系统库 MySQL 的系统表 time_zone 里查询数据,访问类型为 system,这些数据已经加载到内存里,不需要进行磁盘 IO,这类扫描是速度最快的。


explain select * from (select * from user where id=1) tmp;


image


再举一个例子,内层嵌套(const)返回了一个临时表,外层嵌套从临时表查询,其扫描类型也是 system,也不需要走磁盘 IO,速度超快。


const


数据准备:


CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');


explain select * from user where id=1;


image


const 扫描的条件为:


  1. 命中主键primary key)或者唯一unique)索引
  2. 被连接的部分是一个常量const)值


如上例,id 是 主键索引,连接部分是常量1。


eq_ref


数据准备:


CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');

CREATE TABLE `user_ex` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);
insert into user_ex values(5,50);


EXPLAIN SELECT * FROM USER,user_ex WHERE user.id=user_ex.id;


image


eq_ref 扫描的条件为,对于前表的每一行(row,后表只有一行被扫描


再细化一点:  


  1. join 查询
  2. 命中主键primary key)或者非空唯一unique not null)索引
  3. 等值连接;


如上例,id 是主键,该 join 查询为 eq_ref 扫描。


ref


数据准备:


CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');

CREATE TABLE `user_ex` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);
insert into user_ex values(5,50);


EXPLAIN SELECT * FROM USER,user_ex WHERE user.id=user_ex.id;


image


如果把上例 eq_ref 案例中的主键索引,改为普通非唯一(non unique)索引。就由 eq_ref 降级为了 ref,此时对于前表的每一行(row),后表可能有多于一行的数据被扫描


select * from user where id=1;


image


当 id 改为普通非唯一索引后,常量的连接查询,也由 const 降级为了 ref,因为也可能有多于一行的数据被扫描。

 

ref 扫描,可能出现在 join 里,也可能出现在单表普通索引里,每一次匹配可能有多行数据返回,虽然它比 eq_ref 要慢,但它仍然是一个很快的 join 类型。


range


数据准备:


CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');
insert into user values(5,'zhaoliu');


explain select * from user where id between 1 and 4;
explain select * from user where id in(1,2,3);
explain select * from user where id > 3;


image


range 扫描就比较好理解了,它是索引上的范围查询,它会在索引上扫码特定范围内的值。


像上例中的 between,in,> 都是典型的范围(range)查询。


index


explain count (*) from user;


image


如上例,id 是主键,该 count 查询需要通过扫描索引上的全部数据来计数,它仅比全表扫描快一点。


ALL


数据准备:


CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');

CREATE TABLE `user_ex` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);
insert into user_ex values(5,50);


explain select * from user,user_ex where user.id=user_ex.id;


image


如果 id 上不建索引,对于前表的每一行(row),后表都要被全表扫描


文章中,这个相同的 join 语句出现了三次:


  1. 扫描类型为 eq_ref,此时 id 为主键
  2. 扫描类型为 ref,此时 id 为非唯一普通索引
  3. 扫描类型为 ALL,全表扫描,此时id上无索引


有此可见,建立正确的索引,对数据库性能的提升是多么重要。


总结


  1. explain 结果中的 type 字段,表示(广义)连接类型,它描述了找到所需数据使用的扫描方式;
  2. 常见的扫描类型有:system>const>eq_ref>ref>range>index>ALL,其扫描速度由快到慢;
  3. 各类扫描类型的要点是:
    1. system 最快:不进行磁盘 IO
    2. const:PK 或者 unique 上的等值查询
    3. eq_ref:PK 或者 unique 上的 join 查询,等值匹配,对于前表的每一行,后表只有一行命中
    4. ref:非唯一索引,等值匹配,可能有多行命中
    5. range:索引上的范围扫描,例如:between、in、>
    6. index:索引上的全集扫描,例如:InnoDB 的 count
    7. ALL 最慢:全表扫描
  1. 建立正确的索引,非常重要;
  2. 使用 explain 了解并优化执行计划,非常重要;


执行计划 possible_keys


查询过程中有可能用到的索引。


执行计划 key


实际使用的索引,如果为 NULL ,则没有使用索引。


执行计划 rows


根据表统计信息或者索引选用情况,大致估算出找到所需的记录所需要读取的行数。


执行计划 filtered


表示返回结果的行数占需读取行数的百分比, filtered 的值越大越好。


执行计划 Extra


十分重要的额外信息。



下面通过举例说明。


数据准备:


CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into user values(1, 'shenjian','no');
insert into user values(2, 'zhangsan','no');
insert into user values(3, 'lisi', 'yes');
insert into user values(4, 'lisi', 'no');


数据说明:


用户表:id 主键索引,name 普通索引(非唯一),sex 无索引。

四行记录:其中 name 普通索引存在重复记录 lisi。


Using filesort


explain select * from user order by sex;


image.png


Extra 为 Using filesort 说明,得到所需结果集,需要对所有记录进行文件排序。

 

这类 SQL 语句性能极差,需要进行优化。

 

典型的,在一个没有建立索引的列上进行了 order by,就会触发 filesort,常见的优化方案是,在 order by 的列上添加索引,避免每次查询都全量排序。


Using temporary


explain select * from user group by name order by sex;


image.png


Extra 为 Using temporary 说明,需要建立临时表(temporary table)来暂存中间结果。

 

这类 SQL 语句性能较低,往往也需要进行优化。

 

典型的 group by 和 order by 同时存在,且作用于不同的字段时,就会建立临时表,以便计算出最终的结果集。


临时表存在两种引擎,一种是 Memory 引擎,一种是 MyISAM 引擎,如果返回的数据在 16M 以内(默认),且没有大字段的情况下,使用 Memory 引擎,否则使用 MyISAM 引擎。


Using index


EXPLAIN SELECT id FROM USER;


image.png


Extra 为 Using index 说明,SQL 所需要返回的所有列数据均在一棵索引树上,而无需访问实际的行记录。


这类 SQL 语句往往性能较好。


Using index condition


explain select id, name, sex from user where name='shenjian';


image.png


Extra 为 Using index condition 说明,确实命中了索引,但不是所有的列数据都在索引树上,还需要访问实际的行记录。


这类 SQL 语句性能也较高,但不如 Using index。


Using where


explain select * from user where sex='no';


image.png


Extra 为 Using where 说明,查询的结果集使用了 where 过滤条件,比如上面的 SQL 使用了 sex = 'no' 的过滤条件


Select tables optimized away


EXPLAIN SELECT MAX(id) FROM USER;


image.png


比如上面的语句查询 id 最大值,因为 id 是主键索引,根据 B+Tree 的结构,天然就是有序存放的,所以不需要等到执行阶段再进行计算,查询执行计划生成的阶段即可完成优化


Using join buffer (Block Nested Loop)


explain select * from user where id in (select id from user where sex='no');


image.png


Extra 为 Using join buffer (Block Nested Loop) 说明,需要进行嵌套循环计算。内层和外层的 type 均为 ALL,rows 均为4,需要循环进行4*4次计算。

 

这类 SQL 语句性能往往也较低,需要进行优化。

 

典型的两个关联表 join,关联字段均未建立索引,就会出现这种情况。常见的优化方案是,在关联字段上添加索引,避免每次嵌套循环计算。


参考



作者:殷建卫

链接:https://www.yuque.com/yinjianwei/vyrvkf/qeog7k

来源:殷建卫 - 架构笔记

著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。