MySQL（4）EXPLAIN执行计划

Posted on 2023-07-19 Edited on 2023-07-20 In MySQL Word count in article: 1.4k Reading time ≈ 5 mins.

EXPLAIN是MySQL的性能分析神器，提供了MySQL如何执行一条SQL语句的信息。

通过使用 EXPLAIN，我们可以查看 SQL 语句的执行方式，进而排查 SQL 语句的瓶颈，然后进行优化。

首先我们先定义表的结构。

CREATE TABLE `user` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `phone` varchar(20) NOT NULL,
  `gender` int NOT NULL,
  `age` int NOT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

输出格式

先用一个简单的explain来看看输出的结果。

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

id 查询的序列标识，如果涉及到JOIN查询的时候，会有多个select
select_type 查询的类型，有下面集中类型
- SIMPLE 简单查询，没有union或者subquery
- PRIMARY 最外层的select
- UNION union查询中的第二个或者以后的select
- DEPENDENT UNION union查询中的第二个或者以后的select，依赖于外层的query
- UNION RESULT UNION的结果
- SUBQUERY subquery中的第一个select
- DEPENDENT SUBQUERY subquery中的第一个select，依赖于外层query
- DERIVED 衍生表，在from中出现的subquery会被标记成derived，mysql会递归执行这些子查询，把结果放到临时表里面
- DEPENDENT DERIVED 在from中出现的subquery，取决于外面的查询
- MATERIALIZED 物化子查询
- UNCACHEABLE SUBQUERY 不能将结果缓存的子查询，必须重新计算外部查询的每一行
- UNCACHEABLE UNION 在一个UNION中第二或之后的SELECT查询属于UNCACHEABLE SUBQUERY
table 查询的数据库表名
partitions 匹配的分区，如果数据没有分区，则为NULL
type 表之间关联的类型，扫描速度由快到慢：system > const > eq_ref > ref > range > index > ALL
- system 表里面只有一行，是const的一种特殊类型，通常是系统表，比如select * from mysql.proxies_priv;
- const 最多有一行匹配，一般在开始就匹配到了，一般用在主键或者唯一索引里面，比如select * from user where id = 1
- eq_ref 对于上个表里面的组合行的组合，只有一行数据在这个表里面被扫描，一般出现在有join语句的查询里面，这样扫描速度也非常快
- ref 非主键或者唯一索引的等值扫描，
- fulltext 针对FULLTEXT索引进行的搜索
- ref_or_null 和ref类似，MySQL还会额外进行NULL值的搜索
- index_merge
- unique_subquery 唯一索引子查询，在某些IN()查询中可以代替eq_ref，比如value IN (SELECT primary_key FROM single_table WHERE some_expr)
- index_subquery 类似于unique_subquery，只不过作用在非唯一索引上面
- range 范围查询，当在索引上使用=/</>/>=/<=/<>/IS NULL/<=>/BETWEEN/LIKE/IN()比较符时会出现
- index 和ALL类似，只不过是整个索引树都会被扫描，
- ALL 全表扫描，通常情况下都不希望发生
possible_keys 可能应用在这张表上面的索引，一个或者几个（实际上不一定被使用）
key 查询中实际使用的索引，如果是NULL，就没有使用索引
key_len 使用的key的长度，比如主键用bigint，key的长度就是8，因为bigint是8个字节；比如是varchar(255)在utfmb8字符集下（一个字符占4个字节），且不允许为NULL，key的长度是1020+2（需要额外的2个字节保存字符串的长度）
ref 哪些列或者常数和索引发生了比较
- const 和常数进行了比较，比如where id = 1，其中id是主键索引，1是常数，所以这里是常数和主键索引的比较
- 其他表的列，在嵌套查询中会出现
rows MySQL认为有多少行必须要进行检查，也就是扫描的行数，估算值，把所有的rows列值相乘，可以粗略估算整个查询检查的行数
filtered 通过查询条件获取的最终记录行数占通过type字段指明的搜索出来的记录行数的百分比，比如filtered=50,rows=100，那么最终通过筛选有50条记录符合要求，会和剩下的表进行join操作
extra 展示查询的额外信息
- Using filesort 在索引之外，还需要进行额外的排序动作，可以通过合适的索引来减少或者避免
- Using temporary 用临时表保存中间结果，通常出现在order/group by中，一般都需要优化这个。临时表是个中间过程，要创建然后销毁，过程非常慢
- Using index 表示使用了覆盖索引（即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能），效率比较高
- Using index condition MySQL会先通过二级索引搜索出一部分数据给搜索引擎，然后通过where里面的其他条件再次进行筛选（覆盖索引不会发生回表，即使发生索引条件下推，也不会出现Using index condition）
- Using where MySQL服务层应用过滤存储引擎层返回的数据，比如全表扫描的时候，或者用索引访问数据但是where中有索引包含字段之外的条件等等

参考

EXPLAIN Statement
EXPLAIN Output Format
慎用MySQL子查询，尤其是看到DEPENDENT SUBQUERY标记时
 Mysql Explain之type详解
 Explain之key_len长度计算
 MySQL 覆盖索引详解
 explain 执行计划详解2–Extra
MySQL EXPLAIN: “Using index” vs. “Using index condition”