执行计划EXPLAIN详解

什么是EXPLAIN？

EXPLAIN是MySQL提供的一条语句，用于详细展示MySQL如何执行一条SELECT语句。通过使用EXPLAIN，开发者可以看到MySQL如何处理查询及连接表，帮助我们诊断性能问题。

使用方法非常直接，只需在SELECT查询前加上EXPLAIN关键字。

EXPLAIN SELECT * FROM your_table;

EXPLAIN 列

执行EXPLAIN后，MySQL将返回一张表格，其中包含以下列：

1. id

唯一标识SELECT子句或操作表的序号，id相同表示它们是一部分，id越大优先级越高。

2. select_type

查询的类型，常见的值有以下几个。

• SIMPLE：简单SELECT（不使用UNION或子查询）。
• PRIMARY：查询中最外层的SELECT。
• UNION：UNION中的第二个或后续的SELECT语句。
• SUBQUERY：子查询中的第一个SELECT。
• DERIVED：来自于派生表的SELECT（子查询在FROM列表中）。

3. table

显示这一行的数据是关于哪个表的。

4. partitions

显示查询是否使用了分区表。

5. type

type列显示了MySQL解决查询的联接类型，通常从最好到最差如下：

• system：表只有一行，等同于系统表。
• const：表示通过索引一次就找到了，比如通过主键或者唯一索引查询。
• eq_ref：唯一索引扫描，用于等值比较。
• ref：非唯一索引扫描，返回匹配某个单独值的所有行。
• fulltext：全文索引。
• index_merge：表示使用了索引合并优化。
• unique_subquery：在IN查询中使用了唯一索引。
• index_subquery：IN查询，查询了索引的一部分。
• range：索引范围扫描。
• index：全索引扫描。
• ALL：全表扫描。

一般type为index、all就可能需要进行优化。

6. possible_keys

显示可能应用在这张表上的索引。

7. key

实际使用的索引。如果没有选择索引，值是NULL。

8. key_len

表示MySQL在索引里使用的字节数，越小越好。

9. ref

显示列或常数与key一起被使用。

10. rows

MySQL估计为了找到所需的行而必须检查的行数。

11. filtered

表示返回结果的行占开始查找时行的百分比。

12. Extra

包含MySQL解决查询的详细信息。

• Using index：使用了覆盖索引

• Using where：包括额外条件判断，server层对 where 条件过滤

• NULL：查询条件(where)使用了索引，查询列(select)需要回表（使用了索引但需要回表）

• Using index condition：使用了索引下推

• Using temporary：创建一张临时表来处理查询。（未使用索引，且需要创建临时表）

• Using filesort：表示MySQL将使用一个外部索引排序来重新排序结果。（需要全表扫描排序）

如何利用EXPLAIN进行SQL调优？

1. 查看type列

尽量避免ALL（全表扫描）和index（全索引扫描），将它们改进为range，ref。

2. 检查possible_keys和key

确认是否有可能的索引未被使用。如果key列为空，或者没有利用到理想的索引，可能需要添加或修改索引。

3. 分析key_len

key_len反映了索引的长度，确认是否可以通过缩短索引长度来减少扫描的数据量。

4. 查看rows和filtered

如果rows数目特别大，可能需要优化查询或者增加索引来减少需要扫描的行数。filtered的百分比越高越好。

5. 解读Extra列

"Using where"说明此时条件查询可能没有用到索引，需要建立索引结构
"Using temporary"表示使用了临时表，遇到这种情况可能需要优化查询结构。
"Using filesort"意味着MySQL将使用外部排序而不是索引顺序，通常需要优化排序操作，可以考虑联合索引。

上面列需要结合起来看，通过不断迭代和比对EXPLAIN的结果，您可以逐步优化查询，直到达到满意的性能。

​