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

mysql执行计划是优化sql性能的关键工具，使用explain命令可查看其详细信息。1. id字段表示查询顺序，相同则从上到下执行，不同则值越大越先执行；2. select_type说明查询类型，如simple为简单查询，subquery为子查询，建议改写为join；3. table字段显示访问的表名；4. partitions显示分区表的命中情况；5. type为访问类型，all和index应避免，优先提升至eq_ref或ref；6. possible_keys列出可能使用的索引；7. key显示实际使用的索引，若为null需检查索引有效性；8. key_len用于判断是否使用组合索引全部列；9. ref显示索引匹配的具体列；10. rows表示预估扫描行数，越少越好；11. filtered表示过滤比例，越高越优；12. extra提供额外信息，如using index为覆盖索引，using filesort和using temporary应尽量避免。此外，索引失效常见于where中使用函数、类型不匹配、like以%开头、or条件未全用索引、组合索引未使用最左前缀等场景，可通过改写sql、添加索引等方式应对。结合慢查询日志分析可进一步优化数据库性能。

MySQL执行计划，简单来说，就是MySQL优化器对于SQL语句执行过程的预估。它能告诉你MySQL将如何使用索引、连接表，以及整个查询的执行顺序。理解执行计划是优化SQL性能的关键一步。

EXPLAIN命令是查看执行计划的利器。在SELECT语句前加上EXPLAIN，就能得到MySQL对该查询的执行计划报告。

EXPLAIN结果深度解析

EXPLAIN 语句会返回多行数据，每一行代表查询中的一个操作。以下是EXPLAIN结果中各个字段的详细解释以及如何利用它们来优化SQL语句：

1. id：查询的标识符

• 含义：表示SELECT查询的序列号，用于标识查询中操作的执行顺序。
• 值：
  • 如果id相同，则执行顺序从上到下。
  • 如果id不同，值越大优先级越高，越先被执行。
  • 如果id为NULL，则表示这是一个UNION查询的结果。
• 优化思路：关注id的顺序，确保连接顺序合理，避免不必要的全表扫描。

2. select_type：查询的类型

• 含义：描述查询的类型，例如简单查询、子查询或UNION查询。
• 常见值：
  • SIMPLE：简单查询，不包含子查询或UNION。
  • PRIMARY：最外层的SELECT查询。
  • SUBQUERY：SELECT或WHERE列表中包含的子查询。
  • DERIVED：在FROM子句中出现的子查询。MySQL会将结果存放在一个临时表中，也称为派生表。
  • UNION：UNION语句中的第二个或后面的SELECT查询。
  • UNION RESULT：从UNION的临时表中检索结果。
• 优化思路：尽量避免SUBQUERY和DERIVED，因为它们通常会导致性能问题。可以尝试将子查询改写成JOIN。

3. table：查询访问的表

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

4. partitions：表分区

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

5. type：访问类型

• 含义：描述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。

6. possible_keys：可能使用的索引

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

7. key：实际使用的索引

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

8. key_len：索引的长度

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

9. ref：索引的哪一列被使用了

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

10. rows：估计需要检查的行数

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

11. filtered：过滤比例

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

12. Extra：额外信息

• 含义：包含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子句中使用函数或表达式：
  • 例如：WHERE DATE(order_date) = '2023-10-26'。
  • 应对：尽量避免在WHERE子句中使用函数或表达式，可以将函数或表达式移到等号的另一边。例如：WHERE order_date = STR_TO_DATE('2023-10-26', '%Y-%m-%d')。
• 类型不匹配：
  • 例如：索引列是字符串类型，但WHERE子句中使用数字类型进行比较。
  • 应对：确保WHERE子句中使用的数据类型与索引列的数据类型一致。
• LIKE语句以%开头：
  • 例如：WHERE column LIKE '%abc'。
  • 应对：尽量避免使用以%开头的LIKE语句，如果必须使用，可以考虑使用全文索引。
• OR条件：
  • 如果OR连接的多个条件中，只有一个条件使用了索引，则MySQL可能会放弃使用索引。
  • 应对：尽量使用UNION ALL代替OR，或者确保OR连接的所有条件都使用了索引。
• 组合索引未使用最左前缀：
  • 例如：组合索引是(a, b, c)，但WHERE子句中只使用了b和c。
  • 应对：确保WHERE子句中使用了组合索引的最左前缀列。
• MySQL认为全表扫描更快：
  • 当MySQL估计全表扫描比使用索引更快时，它可能会放弃使用索引。
  • 应对：可以通过ANALYZE TABLE命令更新表的统计信息，或者强制使用索引（FORCE INDEX）。

慢查询日志分析

MySQL慢查询日志可以记录执行时间超过指定阈值的SQL语句。通过分析慢查询日志，可以找到需要优化的SQL语句。

1. 开启慢查询日志：

   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL long_query_time = 1; -- 设置阈值为1秒

   登录后复制

2. 查看慢查询日志文件位置：

   SHOW VARIABLES LIKE 'slow_query_log_file';

   登录后复制

3. 分析慢查询日志：

   可以使用mysqldumpslow工具或者其他日志分析工具来分析慢查询日志。

总结

理解MySQL执行计划是SQL优化的基础。通过EXPLAIN命令，我们可以了解MySQL如何执行查询，并根据执行计划中的信息来优化SQL语句，例如添加索引、改写SQL语句等。同时，结合慢查询日志，可以找到需要优化的SQL语句，从而提升数据库的整体性能。

以上就是MySQL如何查看执行计划 EXPLAIN结果深度解析的详细内容，更多请关注php中文网其它相关文章！