MySQL 中的 EXPLAIN 语句是一个强大的工具，用于分析和优化 SQL 查询。通过 EXPLAIN，你可以了解 MySQL 查询优化器是如何执行你的查询的，以及是否有可以改进的地方。本文将详细讲解 EXPLAIN 输出的各项指标，并说明如何利用这些指标来优化索引结构和 SQL 语句。

一、EXPLAIN 的基本使用

EXPLAIN 语句用于分析 SELECT 语句的执行计划。当你在 SQL 语句前加上 EXPLAIN 关键字时，MySQL 会返回一张表格，显示查询的执行计划。执行计划揭示了 MySQL 如何访问表、如何使用索引，以及执行查询的顺序。ini

 代码解读
复制代码
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

二、EXPLAIN 输出的关键指标

EXPLAIN 输出通常包含以下几个重要的列，每一列都代表一个关键的指标，用于解读查询的执行方式。

2.1 id

id 列表示查询中每个子查询或表的执行顺序。通常，id 值越大，执行的优先级越高。如果两个 id 相同，则意味着它们可以并行执行。

• id 相同：表示可以并行执行的部分查询。
• id 不同：表示执行顺序，id 越大，优先级越高。

2.2 select_type

select_type 列表示查询的类型，它描述了查询中每个 SELECT 子句的性质。常见的 select_type 值包括：

• SIMPLE：简单查询，不包含子查询或联合查询。
• PRIMARY：主查询，是最外层的查询。
• SUBQUERY：子查询，出现在 SELECT 或 WHERE 子句中。
• UNION：联合查询的一部分。
• DEPENDENT SUBQUERY：依赖于外部查询的子查询。
• DERIVED：派生表，即子查询中的临时表。

2.3 table

table 列表示查询中访问的表的名称或别名。如果查询涉及多个表，EXPLAIN 会显示它们的连接顺序。

2.4 type

type 列表示查询中表的访问方式，也称为访问类型。访问类型的性能从好到坏依次为：

• system：表只有一行数据（系统表）。
• const：查询某一特定行，性能最好，通常用于主键或唯一索引的查询。
• eq_ref：对每个前驱记录，查询引擎从后继表中读取最多一条记录，通常用于主键或唯一索引连接。
• ref：对前驱表的每个记录，查询引擎可能会找到多条匹配的行，通常用于非唯一索引连接。
• range：通过索引范围扫描查找一部分行。
• index：全索引扫描，即按索引顺序遍历整个索引，但不扫描表。
• ALL：全表扫描，性能最差。

优化建议：目标是尽量避免 ALL 和 index 类型的访问方式，尽量使用 const、eq_ref、ref 或 range 类型的访问方式。

2.5 possible_keys

possible_keys 列显示查询中可能使用到的索引。这里列出的索引并不一定在查询中实际使用，但它们都是 MySQL 优化器可能考虑的索引。

优化建议：确保查询涉及的列上有合适的索引。如果 possible_keys 为空，表示没有可用的索引，这可能会导致全表扫描。

2.6 key

key 列表示查询实际使用的索引。如果该列为空，表示查询未使用索引，而是进行了全表扫描。

优化建议：确保关键查询使用了适当的索引。可以通过修改查询语句或调整索引结构来改善这一点。

2.7 key_len

key_len 列表示 MySQL 使用的索引长度。该值是由索引的定义和查询条件决定的。

优化建议：key_len 越小，查询越高效。在组合索引中，确保最常用的查询条件出现在索引的前缀部分，以减少 key_len 的长度。

2.8 ref

ref 列显示索引的哪些列被用于查询条件中。它描述了索引列与查询条件的关系。

优化建议：确保 ref 列的值与查询条件中的字段一致，特别是在多表连接中，以确保索引被正确使用。

2.9 rows

rows 列表示 MySQL 估算需要读取多少行才能找到查询的结果。这个值越小越好，表示查询更加高效。

优化建议：如果 rows 数量过大，说明可能需要重新设计查询或优化索引。

2.10 filtered

filtered 列表示在索引过滤之后，返回的记录占扫描到的总记录数的百分比。filtered 值越高，说明筛选条件越严格，数据过滤越充分。

优化建议：尽量提高 filtered 的比例，减少返回的无效数据。

2.11 Extra

Extra 列包含查询优化器的额外信息。常见的值有：

• Using where：表示查询使用了 WHERE 过滤条件。
• Using index：表示查询只使用了索引，不需要回表查询数据。
• Using filesort：表示查询需要额外的排序操作，这是一个性能瓶颈。
• Using temporary：表示查询使用了临时表，这是一个性能瓶颈。

优化建议：尽量避免 Using filesort 和 Using temporary，可以通过调整查询语句、增加索引或优化表结构来消除这些性能瓶颈。

三、使用EXPLAIN进行索引优化

3.1 分析查询计划

通过 EXPLAIN 输出的 type、key、rows 等列，可以分析查询的执行计划。如果发现使用了全表扫描 (ALL)，或者 rows 数量过大，说明查询可能有优化空间。

3.2 索引覆盖与优化

• 覆盖索引：在 EXPLAIN 输出中，如果 Extra 列显示 Using index，说明查询已经使用了覆盖索引，可以避免回表查询，提高查询效率。为了实现覆盖索引，可以在查询中选择包含索引的列，或者在创建索引时覆盖更多查询条件。
• 组合索引：对于涉及多个列的查询，可以创建组合索引，并确保最常用的查询条件放在组合索引的前缀部分。组合索引的顺序对查询的效率影响很大。

3.3 调整查询语句

• 简化查询：避免复杂的子查询，尽量将子查询转化为连接查询。在 EXPLAIN 中查看 select_type 是否包含 SUBQUERY 或 DEPENDENT SUBQUERY，如果有，可以考虑重写查询语句。
• 避免排序与临时表：通过索引排序或提前筛选数据，避免 Using filesort 和 Using temporary。例如，可以在需要排序的列上建立索引，或者通过限制查询范围来减少排序操作。

3.4 使用分析工具

MySQL 提供了一些工具，可以帮助分析 EXPLAIN 输出并优化查询。例如：

• ANALYZE TABLE：分析表中的索引统计信息，帮助优化器做出更好的查询计划。
• OPTIMIZE TABLE：重新整理表数据，减少碎片，提高查询效率。
• SHOW PROFILE：详细分析查询执行过程，定位性能瓶颈。

四、实战案例：EXPLAIN 优化实例

假设我们有一个电商数据库，包含以下两个表：orders 和 customers。我们要优化以下查询：sql

 代码解读
复制代码
SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31'
AND c.customer_name LIKE 'A%';

4.1 初步分析sql

 代码解读
复制代码
EXPLAIN SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31'
AND c.customer_name LIKE 'A%';

假设 EXPLAIN 输出如下：

4.2 问题分析

• 表 orders：使用了 order_date 索引，type 为 range，表示通过索引范围扫描找到匹配的订单，rows 列显示了需要扫描 500 行。这部分的执行效率相对较好。
• 表 customers：采用了全表扫描 (ALL)，并且使用了连接缓冲区 (join buffer)，表明未有效使用索引。rows 列显示了需要扫描 10000 行，这是性能瓶颈所在。

4.3 优化策略

1. 优化 customers 表的查询：

   • 针对 customer_name LIKE 'A%' 的查询条件，考虑创建 customer_name 列的索引。因为 LIKE 查询中使用的前缀是固定字符（A%），因此可以利用索引来加速查询。

   • 创建索引的 SQL 语句：scss

      代码解读
     复制代码
     CREATE INDEX idx_customer_name ON customers(customer_name);
     

2. 重新执行 EXPLAIN 并验证结果：

   • 再次使用 EXPLAIN 分析查询语句，确保 customers 表不再使用全表扫描，且 key 列显示为 idx_customer_name。

   假设优化后的 EXPLAIN 输出如下：

   id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
   1	SIMPLE	o	range	order_date	order_date	5	NULL	500	100.00	Using where
   1	SIMPLE	c	ref	idx_customer_name	idx_customer_name	62	const	100	100.00	Using where

   经过优化后，customers 表的访问类型变为 ref，并使用了新的索引 idx_customer_name，查询行数大幅减少，性能得到了明显提升。

五、总结

MySQL 的 EXPLAIN 工具提供了一个清晰的窗口，让你可以窥探查询优化器的决策过程。通过详细分析 EXPLAIN 输出的各项指标，你可以识别出性能瓶颈，并采取适当的措施优化索引结构和 SQL 语句。关键在于：

1. 理解各项指标的含义：了解 type、key、rows、filtered 等列的作用和表现。
2. 合理设计索引：根据查询模式优化索引结构，使用覆盖索引、组合索引等技术提高查询效率。
3. 避免常见的性能陷阱：如全表扫描、排序操作、临时表的使用等，通过优化查询和表结构来消除这些瓶颈。

通过有效利用 EXPLAIN，你可以显著提升 MySQL 数据库的查询性能，为应用程序提供更加流畅的用户体验

转载来源：https://juejin.cn/post/7405152755818283059