性能暴增100倍！MySQL执行计划全方位深度解析

什么是执行计划

MySQL执行计划是分析和优化查询性能的重要工具之一，这里将详细解读如何利用EXPLAIN语句来了解查询执行的步骤，并分析各个字段的含义。

Explain语法 基本语法：

EXPLAIN [或者 DESC] SELECT ……
作用：用于生成SQL查询的执行计划，帮助分析SQL语句的性能。
变体语法：
EXPLAIN EXTENDED SELECT ……

通过运行SHOW WARNINGS可以查看经过MySQL优化器优化后的查询语句，它会将执行计划反编译成最终的SELECT语句。

EXPLAIN PARTITIONS SELECT ……

适用于分区表，EXPLAIN语句会显示与分区表相关的执行计划。

执行计划包含的信息

1. id

id列包含一组数字，表示执行SELECT子句或操作表的顺序。

id相同：表示这些语句属于同一组查询（同一个select语句），执行顺序从上到下。

id递增：优先级递增，id值越大，越早被执行。通常，id值大的子查询会先执行。如上图，先执行第三个select，再执行第二个select，最后执行第一个select。

如果有子查询，id会递增，id值越大的部分会先执行，之后依次执行其他SELECT。

2. select_type

select_type列表示每个SELECT子句的类型，反映查询的复杂性。常见类型有：

• a. SIMPLE：表示查询中不包含任何子查询或UNION。
• b. PRIMARY：查询中包含子查询，最外层的SELECT被标记为PRIMARY。
• c. SUBQUERY：表示SELECT或WHERE列表中包含的子查询。
• d. DERIVED：表示FROM子句中的子查询，结果作为派生表。
• e. UNION：如果SELECT出现在UNION之后，该SELECT被标记为UNION。
• f. UNION RESULT：从UNION操作中获取结果的SELECT，被标记为UNION RESULT。

示例：

• 第一行: id列为1，表示第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中3代表该查询衍生自第三个select查询，即id为3的select。
• 第二行:id为3，表示该查询的执行次序为2(4—3），是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。
• 第三行: select列表中的子查询，select_type为subquery，为整个查询中的第二个select。
• 第四行: select_type为union，说明第四个select是union里的第二个select，最先执行。
• 第五行:代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个select和第四个select的结果进行union操作。

3. type

type列显示MySQL在表中查找所需行的方式，也称为“访问类型”。访问类型由差到好按以下顺序排列：

• a. ALL：全表扫描，性能最差。
• b. index：全索引扫描，类似于全表扫描，但只遍历索引。
• c. range：范围扫描，适用于范围查询（如BETWEEN、<、>等）。
• d. ref：使用非唯一索引扫描，返回匹配某个值的所有行。
• e. eq_ref：唯一性索引扫描，针对每个索引键，表中只有一条记录匹配。
• f. const/system：MySQL将部分查询优化成常量。system为const的特例，用于仅包含一行的表。
• g. NULL：MySQL在执行过程中不需要访问表或索引。

a.ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

b.index：Full Index Scan，index与ALL区别为index类型只遍历索引树

c.range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询

提示：range访问类型的不同形式的索引访问性能差异

d.ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引 或者  唯一索引的非唯一前缀进行的查找

e.eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

f.const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量

提示：system是const类型的特例，当查询的表只有一行的情况下， 使用system

g.NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

提示：通过运行SHOW WARNINGS可以查看经过MySQL优化器优化后的查询语句，它会将执行计划反编译成最终的SELECT语句。

4. possible_keys

表示MySQL能够使用哪些索引来查找行。列出可能的索引，但不一定会使用。

5. key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL提示：查询中若使用了覆盖索引，则该索引仅出现在key列表中

6. key_len

表示查询中使用索引的长度（以字节为单位）。它不是实际使用的长度，而是索引定义中的最大可能长度。

7. ref

显示MySQL在连接操作中使用的列或常量，用于查找索引值。

上图中，由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即 ’ac’

8. rows

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

9. Extra

包含额外信息，解释查询的更多细节：

• a. Using index：表示使用了覆盖索引，MySQL通过索引返回了所需的字段，无需再读取主表数据。

注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引过大，查询性能下降

• b. Using where：表示MySQL在存储引擎查找到记录后 “后过滤”（Post-filter），仍需要进行WHERE条件过滤。

• c. Using temporary：表示查询需要使用临时表来存储结果，常见于ORDER BY或GROUP BY查询。

• d. Using filesort：当MySQL无法使用索引完成排序时，使用文件排序来进行排序操作。

MySQL执行计划的局限性

• EXPLAIN不考虑缓存和其他性能优化手段。
• 部分统计信息是估算值，而非精确数据。
• EXPLAIN仅用于SELECT语句，其他查询需要转换成SELECT来分析执行计划。
  
  

总结

MySQL的EXPLAIN语句是非常强大的查询优化工具，它可以帮助我们了解查询执行的详细过程，从而找到优化的方向。然而，理解和优化MySQL查询不仅仅依赖于EXPLAIN，还需要结合实际的数据库结构和索引设计。