MySQL执行计划EXPLAIN

数据库接收到SQL后的处理流程

#

概述

MYSQL官方的解释：当EXPLAIN与可解释的语句一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。就是说，MySQL解释了它将如何处理该语句，包括有关如何连接表以及以何种顺序连接表的信息。

EXPLAIN如何使用？怎样查看执行计划？

mysql> explain select * from t_user;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.06 sec)

Explain 执行计划包含信息如下：分别是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra 12个字段。

柱	JSON名称	含义
id	select_id	该`SELECT`标识符
select_type	没有	该`SELECT`类型
table	table_name	输出行表
partitions	partitions	匹配的分区
type	access_type	联接类型
possible_keys	possible_keys	可能的索引选择
key	key	实际选择的索引
key_len	key_length	所选键的长度
ref	ref	与索引比较的列
rows	rows	估计要检查的行
filtered	filtered	按表条件过滤的行百分比
Extra	没有	附加信息

准备工作，创建几张简单的表

CREATE TABLE `t_user` (
  `USER_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `USER_NAME` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户名',
  `MOBILE` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手机号',
  `EMAIL` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '邮箱',
  `NICK_NAME` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '昵称',
  `DETAIL_ID` int(11) DEFAULT NULL COMMENT '详情ID',
  PRIMARY KEY (`USER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-------------------------------------------------------------------------------------------------------
CREATE TABLE `t_user_detail` (
  `DETAIL_ID` int(11) NOT NULL COMMENT '详情ID',
  `USER_ID` int(11) DEFAULT NULL COMMENT '用户ID',
  `ADDRESS` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '地址',
  `PROVINCE` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '省',
  `CITY` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '市',
  `AREA` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '区',
  `MEMBER_ID` int(11) DEFAULT NULL COMMENT '会员ID',
  PRIMARY KEY (`DETAIL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-------------------------------------------------------------------------------------------------------
CREATE TABLE `t_member` (
  `MEMBER_ID` int(11) NOT NULL COMMENT '会员ID',
  `MEMBER_NAME` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '会员名称',
  `MEMBER_CODE` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '会员编码',
  `MEMBER_TYPE` smallint(8) DEFAULT NULL COMMENT '会员类型',
  PRIMARY KEY (`MEMBER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

详解

id

id： ：表示查询中执行select子句或者操作表的顺序，id的值越大，代表优先级越高，越先执行。

mysql>  explain select * from t_user u ,t_user_detail d where u.detail_id=d.detail_id;  
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | u     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                                               |
|  1 | SIMPLE      | d     | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set (0.15 sec)

id值相同，代表具有同样的优先级，执行顺序由上而下，具体顺序由优化器决定。

mysql> explain select * from t_user u where u.detail_id = (select detail_id from t_user_detail);
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | u             | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    2 |    50.00 | Using where |
|  2 | SUBQUERY    | t_user_detail | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |   100.00 | Using index |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

如果我们的 SQL 中存在子查询，那么 id的序号会递增， id值越大优先级越高，越先被执行。当三个表依次嵌套，发现最里层的子查询 id最大，最先执行

select_type

表示 select 查询的类型，主要是用于区分各种复杂的查询，例如：普通查询、联合查询、子查询等

SIMPLE

表示最简单的 select 查询语句，也就是在查询中不包含子查询或者 union交并差集等操作。

mysql> explain select * from t_user;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+

PRIMARY

当查询语句中包含任何复杂的子部分，最外层查询则被标记为PRIMARY。

explain select * from t_user where  detail_id = (select detail_id from t_user_detail d join t_member m on d.member_id=m.member_id where m.member_type=1 );
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | PRIMARY     | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where                                        |
|  2 | SUBQUERY    | m      | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 |    50.00 | Using where                                        |
|  2 | SUBQUERY    | d      | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

SUBQUERY

当 select 或 where 列表中包含了子查询，该子查询被标记为：SUBQUERY。

DERIVED

派生表：表示包含在from子句中的子查询的select，在我们的 from 列表中包含的子查询会被标记为derived

UNION

如果union后边又出现的select 语句，则会被标记为union；若 union 包含在 from 子句的子查询中，外层 select 将被标记为 derived。

UNION RESULT

代表从union的临时表中读取数据，而table列的<union1,2>表示用第一个和第二个select的结果进行union操作

mysql> explain select * from t_user where user_id=1 union select * from t_user where user_id=2;
+------+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id   | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+------+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
|    1 | PRIMARY      | t_user     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
|    2 | UNION        | t_user     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | NULL | NULL     | Using temporary |
+------+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

table

查询的表名，并不一定是真实存在的表，有别名显示别名，也可能为临时表，例如上边的DERIVED、 <union1,4>等。

partitions

查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表命中的分区情况。

type

查询使用了何种类型，它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system

当表仅有一行记录时(系统表)，数据量很少，往往不需要进行磁盘IO，速度非常快。

const

表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量(const)值。

eq_ref

查询时命中主键primary key 或者 unique key索引， type 就是 eq_ref。

区别于eq_ref ，ref表示使用非唯一性索引，会找到很多个符合条件的行。

ref_or_null

这种连接类型类似于 ref，区别在于 MySQL会额外搜索包含NULL值的行。

index_merge

使用了索引合并优化方法，查询使用了两个以上的索引。

unique_subquery

子查询返回不重复的集合。

index_subquery

区别于unique_subquery，用于非唯一索引，可以返回重复值。

range

使用索引选择行，仅检索给定范围内的行。简单点说就是针对一个有索引的字段，给定范围检索数据。在where语句中使用 bettween...and、<、>、<=、in 等条件查询 type 都是 range。

index

Index 与ALL 其实都是读全表，区别在于index是遍历索引树读取，而ALL是从硬盘中读取。

将遍历全表以找到匹配的行，性能最差。

possible_keys

该possible_keys列指示MySQL可以选择从中查找表中各行的索引。请注意，此列完全独立于的输出中显示的表顺序 EXPLAIN。这意味着，某些键possible_keys实际上可能无法用于生成的表顺序。

key

区别于possible_keys，key是查询中实际使用到的索引，若没有使用索引，显示为NULL。

key_len

表示查询用到的索引长度（字节数），原则上长度越短越好。

单列索引，那么需要将整个索引长度算进去；
多列索引，不是所有列都能用到，需要计算查询中实际用到的列。

注意：key_len只计算where条件中用到的索引长度，而排序和分组即便是用到了索引，也不会计算到key_len中。

ref

常见的有：const，func，null，字段名。

当使用常量等值查询，显示 const，
当关联查询时，会显示相应关联表的 关联字段
如果查询条件使用了 表达式、 函数，或者条件列发生内部隐式转换，可能显示为 func
其他情况 null

rows

以表的统计信息和索引使用情况，估算要找到我们所需的记录，需要读取的行数。这个值越少越好。

filtered

百分比值。表里符合条件的记录数的百分比。

Extra

不适合在其他列显式的值都出现在这里。

Using index

使用了覆盖索引。

Using where

查询时未找到可用的索引，进而通过where条件过滤获取所需数据。

Using temporary

表示查询后结果需要使用临时表来存储，一般在排序或者分组查询时用到。

mysql> explain select * from t_user where user_id=1 union select * from t_user where user_id=2;
+------+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id   | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+------+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
|    1 | PRIMARY      | t_user     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
|    2 | UNION        | t_user     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | NULL | NULL     | Using temporary |
+------+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

Using filesort

mysql> explain select * from t_user order by user_name;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+

Using join buffer

在我们联表查询的时候，如果表的连接条件没有用到索引，需要有一个连接缓冲区来存储中间结果。

mysql> explain select * from t_user u join t_user_detail d on u.detail_id=d.detail_id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | u     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                                               |
|  1 | SIMPLE      | d     | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

No tables used

mysql> explain select MD5(UUID());
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

总结

执行计划是我们进行SQL查询优化的重要工具，需要明白各列的含义才能在开发中运用自如。

本文使用 tech.souyunku.com 排版

文章永久链接：https://tech.souyunku.com/37447

MySQL执行计划EXPLAIN

MySQL执行计划EXPLAIN

#

概述

详解

id

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

总结

相关推荐

JetBrains 全家桶，激活、破解、教程