MySQL如何查看执行计划 EXPLAIN结果深度解析

MySQL执行计划是优化SQL性能的关键工具，通过EXPLAIN命令可以查看SQL语句的执行计划，了解MySQL如何使用索引、连接表以及查询的执行顺序。以下是EXPLAIN结果的深度解析：

EXPLAIN命令使用方法

在SELECT语句前加上EXPLAIN，即可得到MySQL对该查询的执行计划报告。例如：

EXPLAIN结果字段解析

EXPLAIN语句会返回多行数据，每一行代表查询中的一个操作。以下是各个字段的详细解释：

• 含义：表示SELECT查询的序列号，用于标识查询中操作的执行顺序。
• 值：

  如果id相同，则执行顺序从上到下。

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

  如果id为NULL，则表示这是一个UNION查询的结果。

• 优化思路：关注id的顺序，确保连接顺序合理，避免不必要的全表扫描。

• 含义：描述查询的类型，例如简单查询、子查询或UNION查询。
• 常见值：

  SIMPLE：简单查询，不包含子查询或UNION。

  PRIMARY：最外层的SELECT查询。

  SUBQUERY：SELECT或WHERE列表中包含的子查询。

  DERIVED：在FROM子句中出现的子查询。MySQL会将结果存放在一个临时表中，也称为派生表。

  UNION：UNION语句中的第二个或后面的SELECT查询。

  UNION RESULT：从UNION的临时表中检索结果。

• 优化思路：尽量避免SUBQUERY和DERIVED，因为它们通常会导致性能问题。可以尝试将子查询改写成JOIN。

• 含义：表示查询访问的表名或别名。
• 值：直接显示表名或者表的别名。
• 优化思路：确认是否访问了正确的表，是否存在不必要的表连接。

• 含义：如果表是分区表，则显示查询访问的分区。
• 值：显示命中的分区。
• 优化思路：如果查询没有用到分区索引，可能会导致全部分区扫描，需要检查SQL语句和分区策略。

• 含义：描述MySQL如何查找表中的行，是性能优化的关键指标。
• 常见值（从最佳到最差）：

  system：表只有一行记录，是const类型的特殊情况。

  const：通过主键或唯一索引一次就能找到。

  eq_ref：使用唯一索引查找，常见于主键或唯一索引的关联查询。

  ref：使用非唯一索引查找。

  fulltext：使用全文索引。

  ref_or_null：类似于ref，但是MySQL会对包含NULL值的列进行额外的搜索。

  index_merge：使用了索引合并优化策略。

  unique_subquery：用于替换IN子查询的一种形式，返回不重复值字段。

  index_subquery：类似于unique_subquery，但返回的是非唯一值字段。

  range：使用索引范围扫描。

  index：全索引扫描。

  ALL：全表扫描。

• 优化思路：尽量避免ALL和index，尽可能提升到ref或eq_ref。

• 含义：MySQL在查询中可能使用的索引。
• 值：列出可能用到的索引。
• 优化思路：即使possible_keys中有索引，MySQL也可能不使用。需要结合key字段来判断。

• 含义：MySQL实际使用的索引。
• 值：显示实际使用的索引名。
• 优化思路：如果key为NULL，但possible_keys不为NULL，说明MySQL认为没有合适的索引可用。需要检查索引是否有效，或者考虑创建新的索引。

• 含义：使用的索引的长度。在不损失精确性的情况下，长度越短越好。
• 值：计算得到索引长度。
• 优化思路：可以通过计算key_len来判断是否使用了组合索引的所有列。

• 含义：显示索引的哪一列被使用了，常用于关联查询。
• 值：显示具体的列名或const。
• 优化思路：检查是否使用了正确的列进行索引匹配。

• 含义：MySQL估计为了找到所需的行而需要读取的行数。
• 值：估计的行数。
• 优化思路：rows越小越好，说明MySQL需要扫描的行数越少。

• 含义：表示经过搜索条件过滤后剩余记录的百分比。
• 值：百分比。
• 优化思路：filtered越高越好，说明搜索条件过滤性越好。

• 含义：包含MySQL解决查询的额外信息。
• 常见值：

  Using index：使用了覆盖索引，避免了回表查询。

  Using where：使用了WHERE子句过滤结果。

  Using temporary：MySQL需要创建临时表来存储结果，常见于ORDER BY和GROUP BY。

  Using filesort：MySQL需要使用文件排序，而不是索引排序。

  Using join buffer (Block Nested Loop)：使用了连接缓存。

  Impossible WHERE noticed after reading const tables：WHERE子句总是false，导致没有符合条件的行。

  Select tables optimized away：使用了某些优化策略，例如直接从索引中获取数据，而不需要访问表。

  Distinct：优化DISTINCT操作，当找到第一匹配的元组后停止搜索。

• 优化思路：

  Using temporary和Using filesort通常是性能瓶颈，应该尽量避免。可以通过添加索引来优化排序。

  Using index是好的，说明使用了覆盖索引。

索引失效的常见情况与应对

索引失效是导致查询性能下降的常见原因。以下是一些常见的索引失效情况以及相应的应对策略：

• 例如：WHERE DATE(order_date) = '2023-10-26'
• 应对：尽量避免在WHERE子句中使用函数或表达式，可以将函数或表达式移到等号的另一边。例如：WHERE order_date = STR_TO_DATE('2023-10-26', '%Y-%m-%d')

• 例如：索引列是字符串类型，但WHERE子句中使用数字类型进行比较。
• 应对：确保WHERE子句中使用的数据类型与索引列的数据类型一致。

• 例如：WHERE column LIKE '%abc'
• 应对：尽量避免使用以%开头的LIKE语句，如果必须使用，可以考虑使用全文索引。

• 例如：如果OR连接的多个条件中，只有一个条件使用了索引，则MySQL可能会放弃使用索引。
• 应对：尽量使用UNION ALL代替OR，或者确保