《MySQL》用explain分析SQL语句的执行计划

概述

通过对查询语句的分析，可以了解查询语句的执行情况找出查询语句的瓶颈，从而优化查询语句。

MySQL 提供了 EXPLAIN 和 DESCRIBE(可以简写为DESC)来分析查询语句，两个关键词的用法是等效的，分析结果完全一样

查询序列号 id

SELECT识别符。这是SELECT的查询序列号

• id相同表示加载表的顺序是从上到下。

• id不同id值越大，优先级越高，越先被执行。

• id有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

语句的类型 select_type

它可以是以下几种取值：

1. SIMPLE 简单查询

2. PRIMARY 主查询

3. SUBQUERY 子查询、合并查询（合并查询的结果集作为子查询时）的第一个 SELECT

4. DEPENDENT SUBQUERY 子查询、合并查询（合并查询的结果集作为子查询时）的第一个 SELECT，依赖外部查询

5. UNION 合并查询的第二个 SELECT

6. DEPENDENT UNION 合并查询的第二个 SELECT，依赖外部查询

7. UNION RESULT 合并的结果

   它并不是一个真正的查询，只是一次对多个查询结果的合并，所以分析结果的其他列大部分为 null

8. DERIVED 导出表的 SELECT（FROM 子句的子查询）

通过以下场景深入了解各个值：

1. 既没有 UNION ，字段列表中也没有子查询的，语句类型为 SIMPLE

2. 子查询出现在 FROM 子句或者 WHERE 子句中，外部查询的类型也可能是 SIMPLE

3. 字段列表中有子查询时，最外层的查询为主查询，语句类型为 PRIMARY，子查询的语句类型可能为 SUBQUERY 或 DEPENDENT SUBQUERY，取决于是否依赖主查询

4. UNION 合并的结果集作为最终的结果返回的，第一个 SELECT 的语句类型为 PRIMARY ，其他的语句类型为 UNION

5. UNION 合并的结果集作为字段列表中的子查询或 WHERE 子句的子查询时，第一个 SELECT 的语句类型可能为 SUBQUERY 或 DEPENDENT SUBQUERY，其他的语句类型可能为 UNION 或 DEPENDENT UNION，取决于是否依赖主查询

6. UNION 合并的结果集作为临时表时，第一个 SELECT 的语句类型为 DERIVED ，其他的语句类型为 UNION

查询的表 table

显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是别名

表的连接类型 type

下面按照从最差类型到最佳类型的顺序给出各种连接类型：

1. ALL

   全表扫描，MYSQL扫描全表来找到匹配的行

2. index

   索引全扫描，MYSQL遍历整个索引来查找匹配的行

3. range

   索引范围扫描，如：

   • 使用 <、<=、>、>=、between 等在索引列进行范围查找

4. ref

   在非唯一性索引或者联合唯一索引的前缀中查找，如：

   • 使用 = 或者 <=> 操作符精确匹配非唯一性索引列

   • 通过非唯一性索引列作为连接条件进行连表查询

5. eq_ref

   与 ref 的唯一区别就在使用的索引是唯一索引（或主键）

6. const

   数据表最多有一个匹配行，它将在查询开始时被读取，并在余下的查询中作为常量对待。使用常值比较 PRIMARY KEY 列或 UNION INDEX 列时，连接类型为 const

7. system

   数据表是仅有一行的系统表（亲测，自建的表只有一行，连接类型仍是 const，暂未找到连接类型是system的场景）

8. NULL

   MySQL 不用访问数据表或索引就可以直接获取结果，如：

   • explain select 1,now();

可能的键 possible_keys

指出 MySQL 可能使用的索引。

如果没有任何索引显示 null

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。

实际使用的键 key

显示 MySQL 实际决定使用的键（索引），必然包含在 possible_keys 中

如果没有选择索引，键是 NULL。

要想强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX。

使用的键的字节数 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。（显示的值为索引字段的最大可能长度，是根据表定义计算而得，并非实际使用长度）

不损失精确性的情况下，长度越短越好

和前方表连接的字段 ref

和前方表连接的字段, const 代表是常量值连接

检查的行数 rows

表示 MySQL 根据表统计信息及索引选用情况，估算的找到所需的记录所需要检查的行数

额外的一些描述 Extra

该列包含 MySQL 查询时的详细信息,以下列举几种常见情况：

• Using index

  仅使用索引树中的信息就能获取要查询的信息，而不必去读取实际的行记录（回表）

• Using where

  表示MySQL服务器在存储引擎收到记录后进行“后过滤”，和是否读取数据文件或索引文件没有关系。

• Using index condition

  先根据索引列过滤，筛选出符合条件的主键再回表。如果 WHERE 子句中还有其他条件，再根据其他条件进一步过滤数据

• Using temporary

  要解决查询，MySQL 需要创建一个临时表来保存结果，因为临时结果集太大了。 比如对没有索引的字段使用 GROUP BY

• Using filesort

  当查询中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL 不得不选择相应的排序算法来实现。数据较少时从内存排序，否则从磁盘排序。

通过以下场景深入理解：

# 表结构
CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `username` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `gender` enum('F','M','') COLLATE utf8mb4_general_ci NOT NULL,
  `address` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `nation` varchar(20) COLLATE utf8mb4_general_ci DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_info` (`username`,`address`),
  KEY `user_info_user_id` (`user_id`),
  CONSTRAINT `user_info_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4624816 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

1. Using index

   #仅从主键索引中即可获取到数据，不必回表。精确匹配，利用索引定位到 id=1,而不是使用过滤
   explain select id from user_info where id=1;

   # idx_info 索引的叶节点中存储的有主键数据，因此也不必回表
   explain select id from user_info where username='test';

2. Null

   # 查询所有字段，仅依靠索引无法完成，必须回表。精确匹配，利用索引定位到 id=1,而不是使用过滤
   explain select * from user_info where id=1;

3. Using where; Using index

   #仅从主键索引中即可获取到数据，不必回表。范围取值，收到记录后过滤
   explain select id from user_info where id<2;

4. Using where

   #查询所有字段，必须回表。收到记录后过滤
   explain select * from demo_user where id<2;

5. Using index condition

必须打开ICP

SHOW VARIABLES LIKE 'optimizer_switch';
SET optimizer_switch = 'index_condition_pushdown=on';

# 先根据索引列过滤，筛选出符合条件的主键再回表（回表查其他数据）
explain select * from user_info where username like 'f';
# 先根据索引列过滤，筛选出符合条件的主键再回表（回表进一步过滤）
explain select id from user_info where username like 'f' and gender = 'M';

疑问：以下两个查询，同样是查询所有字段，同样是索引列，前者使用 Using where，后者使用 Using index condition
目前总结出来的规律是：前者的 id 字段的数据类型为整数，后者 username 字段的数据类型为字符串

explain select * from demo_user where id<2;
explain select * from user_info where username like 'f';

总结：

1. 有索引参与的精确匹配不使用过滤

   1. 不需要回表，则 Extra 为 Using index

   2. 需要回表，则 Extra 为 Null

2. 范围查询或者模糊匹配使用过滤

   1. 过滤时没有索引参加，则 Extra 为 Using where；

   2. 有索引参加，不需要回表，则 Extra 为 Using where; Using index

   3. 有索引参加但最终需要回表（回表查其他数据或者回表进一步过滤）

      1. 索引列的数据类型为整数，则先回表后过滤，Extra 为 Using where

      2. 索引列的数据类型为字符串，则先通过索引过滤再回表， Extra 为 Using index condition

      3. 索引列的数据类型为字符串且还有其他非索引的过滤条件，则先通过索引过滤再回表再根据其他条件过滤， Extra 为 Using where; Using index condition

其他

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。