[Mysql]Explain

Explain执行计划分析

什么是执行计划？#

执行计划 是指一条 SQL 语句在经过 MySQL 查询优化器 的优化后，具体的执行方式。
执行计划通常用于 SQL 性能分析、优化等场景。通过 EXPLAIN 的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。

如何获取执行计划？#

MySQL 为我们提供了 EXPLAIN 命令，来获取执行计划的相关信息。
需要注意的是，EXPLAIN 语句并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。
EXPLAIN 执行计划支持SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 语句。我们一般多用于分析 SELECT 查询语句，使用起来非常简单，语法如下：
EXPLAIN + SELECT 查询语句；
我们简单来看下一条查询语句的执行计划：

mysql> explain SELECT * FROM dept_emp WHERE emp_no IN (SELECT emp_no FROM dept_emp GROUP BY emp_no HAVING COUNT(emp_no)>1);
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys   | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | dept_emp | NULL       | ALL   | NULL            | NULL    | NULL    | NULL | 331143 |   100.00 | Using where |
|  2 | SUBQUERY    | dept_emp | NULL       | index | PRIMARY,dept_no | PRIMARY | 16      | NULL | 331143 |   100.00 | Using index |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+

可以看到，执行计划结果中共有 12 列，各列代表的含义总结如下表：

列名	含义
id	SELECT 查询的序列标识符
select_type	SELECT 关键字对应的查询类型
table	用到的表名
partitions	匹配的分区，对于未分区的表，值为 NULL
type	表的访问方法
possible_keys	可能用到的索引
key	实际用到的索引
key_len	所选索引的长度
ref	当使用索引等值查询时，与索引作比较的列或常量
rows	预计要读取的行数
filtered	按表条件过滤后，留存的记录数的百分比
Extra	附加信息

分析 EXPLAIN 结果#

为了分析 EXPLAIN 语句的执行结果，我们需要搞懂执行计划中的重要字段。

id#

SELECT 标识符，是查询中 SELECT 的序号，用来标识整个查询中 SELECT 语句的顺序。(尤其是有子查询的时候)
id 如果相同，从上往下依次执行。id 不同，id 值越大，执行优先级越高，如果行引用其他行的并集结果，则该值可以为 NULL。

select_type#

查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询，常见的值有：

SIMPLE	简单查询，不包含 UNION 或者子查询。
PRIMARY	查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY。
SUBQUERY	子查询中的第一个 SELECT。
UNION	在 UNION 语句中，UNION 之后出现的 SELECT。
DERIVED	在 FROM 中出现的子查询将被标记为 DERIVED。
UNION RESULT	UNION 查询的结果。

union#

UNION 是 MySQL 中用于合并两个或多个 SELECT 语句结果集的关键字。它用于将多个查询的结果合并为一个结果集，且结果集中不包含重复的行。UNION 操作符执行垂直合并，即按行合并结果。

基本语法如下：

SELECT column1, column2, ...
FROM table1
WHERE condition1

UNION [DISTINCT | ALL]

SELECT column1, column2, ...
FROM table2
WHERE condition2;

• column1, column2, ...: 要选择的列。
• table1, table2: 要查询的表。
• condition1, condition2: 查询的条件。
• DISTINCT: 可选关键字，用于去除合并结果中的重复行。
• ALL: 可选关键字，用于保留合并结果中的所有行，包括重复行。

要注意的是，UNION 操作符默认会去除结果中的重复行，如果想要保留重复行，可以使用 UNION ALL。

例如，假设有两个表 employees1 和 employees2，它们有相同的列结构，可以执行以下查询：

SELECT employee_id, first_name, last_name
FROM employees1

UNION

SELECT employee_id, first_name, last_name
FROM employees2;

这个查询将合并 employees1 和 employees2 中符合条件的行，并去除重复的行。如果想要包含重复行，可以使用 UNION ALL 替代 UNION。

SELECT employee_id, first_name, last_name
FROM employees1

UNION ALL

SELECT employee_id, first_name, last_name
FROM employees2;

UNION 可以用于合并任意数量的查询，只要它们具有相同的列结构。需要确保各个查询的列顺序和数据类型匹配。

总的来说，UNION 是一个方便的工具，可以用于合并多个查询的结果集，但要谨慎使用，确保你的查询逻辑正确且效率高。

table#

查询用到的表名，每行都有对应的表名，表名除了正常的表之外，也可能是以下列出的值：

<union M,N> : 本行引用了 id 为 M 和 N 的行的 UNION 结果；(这里的M,N不是指数据库表中的M,N而是指执行计划中的M,N行)
: 本行引用了 id 为 N 的表所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询。
: 本行引用了 id 为 N 的表所产生的的物化子查询结果。

type（重要）#

查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

常见的几种类型具体含义如下：

system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。

const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。

eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。

ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。

index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。

range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。

index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。

ALL：全表扫描。

possible_keys#

possible_keys 列表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ，则表示没有可能用到的索引；这种情况下，需要检查 WHERE 语句中所使用的的列，看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。

key（重要）#

key 列表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。

key_len#

key_len 列表示 MySQL 实际使用的索引的最大长度；当使用到联合索引时，有可能是多个列的长度和。在满足需求的前提下越短越好。如果 key 列显示 NULL ，则 key_len 列也显示 NULL 。

rows#

rows 列表示根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好。

Extra（重要）#

这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
Using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。
这里提醒下，当 Extra 列包含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，需要尽可能避免。

在MySQL的EXPLAIN输出中，Using filesort是一个常见的术语，它指的是MySQL需要进行额外的排序操作来满足查询中的ORDER BY语句，而这个排序操作不能完全通过索引来完成。尽管名称中包含"file"，但这并不意味着排序操作一定会在磁盘上进行；排序可以在内存中完成，也可能因为内存不足而退化到磁盘上。

为什么会出现Using filesort？#

当查询包含ORDER BY语句，且以下情况之一成立时，MySQL可能会使用文件排序：

• 索引无法满足排序需求：查询所使用的索引不能直接用来满足ORDER BY中指定的排序顺序。这可能是因为ORDER BY的列不是索引的一部分，或者即使是索引的一部分，但因为查询条件的限制，索引无法直接用于排序。
• 复合索引顺序与ORDER BY顺序不匹配：即使ORDER BY的列在一个复合索引中，但如果排序的顺序（升序/降序）或列的顺序与索引中的不一致，MySQL也可能需要进行外部排序。

文件排序是怎样进行的？

当MySQL决定使用文件排序来执行ORDER BY操作时，它会按以下步骤进行：

1. 扫描表：首先，MySQL扫描表或索引，获取必要的行。
2. 排序：然后，根据ORDER BY子句的要求，MySQL在内存中对这些行进行排序。如果排序的数据量超过了系统变量sort_buffer_size的设置值，MySQL可能会使用磁盘临时文件来辅助排序。
3. 返回结果：最后，MySQL按照排序后的顺序返回结果集。

如何避免Using filesort？

• 优化索引：确保你的查询能够更好地利用索引进行排序。为ORDER BY中的列创建适当的索引，尤其是当查询也包含WHERE子句时，考虑创建复合索引。
• 调整查询：有时候，通过调整查询的结构，可以消除对文件排序的需求。比如，通过改变ORDER BY中列的顺序，或者移除不必要的排序操作。
• 增加内存：增加sort_buffer_size参数的大小可以提高内存中排序的能力，减少磁盘IO的需要。然而，这应该谨慎进行，因为过大的排序缓冲区可能会对系统性能产生负面影响。

理解Using filesort的含义和背后的机制可以帮助你更好地优化你的SQL查询，减少排序操作的开销，从而提高查询性能。