播面

EXPLAIN命令用于分析SQL查询执行计划，通过解读type、Extra等关键字段，诊断性能瓶颈，是SQL优化的必备工具。

我们来详细解释一下 EXPLAIN 命令以及如何解读它的结果。这对于任何需要进行 SQL 性能优化的开发者或 DBA 来说，都是一项至关重要的技能。

EXPLAIN 命令是用来做什么的？

简单来说，EXPLAIN 命令是你的 SQL 查询导航仪。

在你实际执行一个 SELECT 查询之前，你可以先用 EXPLAIN “预览”一下 MySQL 将会如何执行它。它不会真正去运行查询，而是告诉你 MySQL 的查询优化器（Query Optimizer）为这条 SQL 语句制定出的 执行计划（Execution Plan）。

通过这个执行计划，你可以了解到：

1. 表的读取顺序：MySQL 先访问哪张表，后访问哪张表。
2. 数据读取操作类型：是对表进行全表扫描（Full Table Scan），还是通过索引查找（Index Scan/Seek）。
3. 哪些索引被实际使用：MySQL 决定使用哪个索引来最高效地查找数据。
4. 哪些索引可以被使用：MySQL 认为有哪些可用的索引。
5. 每张表被查询的行数估算：MySQL 预估需要检查多少行才能返回结果。
6. 是否存在性能瓶颈：例如是否使用了临时表（Temporary Table）或文件排序（Filesort）。

核心目的：诊断查询性能问题，找到优化点，确保查询能够高效地利用索引，避免不必要的数据扫描。

如何使用？
非常简单，在你的 SELECT 语句前加上 EXPLAIN 关键字即可。

EXPLAIN SELECT u.id, u.name, o.order_no
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 25;

如何解读其结果中的关键字段？

EXPLAIN 的输出结果是一个表格，包含多个列（字段）。下面我们来逐一解读最重要的几个字段，并按照它们的重要性进行排序。

1. type (访问类型) - 最最重要的字段

这个字段描述了 MySQL 如何查找表中的行，它直接反映了查询的效率。性能从最优到最差的顺序如下：

• system > const: 最高效。当查询的主键或唯一索引与一个常量值进行比较时出现。数据库直接将查询优化为读取一个常量，速度快如闪电。
  sql

  -- 假设 id 是主键
  EXPLAIN SELECT * FROM users WHERE id = 1;

• eq_ref: 效率极高。通常出现在多表连接（JOIN）中，连接条件是主键或唯一索引。对于前一张表的每一行，后一张表只有唯一一行与之匹配。
  sql

  -- 假设 users.id 是主键, orders.user_id 是唯一索引
  EXPLAIN SELECT * FROM users JOIN orders ON users.id = orders.user_id;

• ref: 效率很高。使用非唯一性索引进行查找，或者只使用了唯一索引的前缀部分。它可能会返回多行数据。
  sql

  -- 假设 name 上有普通索引
  EXPLAIN SELECT * FROM users WHERE name = 'John';

• range: 效率较好。对索引进行范围扫描，常见于 BETWEEN, >, <, IN() 等操作。比全表扫描要好得多，因为它只扫描索引的一部分。
  sql

  
  -- 假设 age 上有索引
  EXPLAIN SELECT * FROM users WHERE age > 25;

• index: 效率较低。它会扫描整个索引树。虽然比 ALL 快（因为索引通常比表数据小），但仍然需要扫描大量数据。通常出现在查询的字段恰好被索引完全覆盖（即“覆盖索引”）但没有 WHERE 条件时。
• ALL: 性能最差。这是 全表扫描（Full Table Scan）。MySQL 将会遍历表中的每一行来寻找匹配的记录。在数据量大的表中，这是必须避免的性能灾难。如果你的查询出现了 ALL，通常意味着你需要添加索引了。

优化目标：尽可能让 type 的级别往前靠，至少要达到 range 级别，最好是 ref 或 eq_ref。坚决避免 ALL。

2. Extra (额外信息) - 第二重要的字段

这个字段包含了许多非常有价值的补充信息，能帮你精确判断查询的瓶颈。

• Using index: 极好的信号。表示查询所需的数据只通过读取索引就能全部获得，无需回表查询（即访问实际的数据行）。这被称为“覆盖索引”（Covering Index），是性能优化的一个重要目标。
• Using where: 表示在存储引擎层返回数据后，MySQL 服务器层需要对这些数据再次进行 WHERE 条件过滤。这是很常见的。
• Using index condition: 索引条件下推（Index Condition Pushdown, ICP）。这是一个优化，MySQL 会在存储引擎层使用 WHERE 条件对索引进行过滤，减少不必要的行数据读取。是好事。
• Using temporary: 坏信号。表示 MySQL 需要创建一个临时表来处理查询。通常出现在 GROUP BY 或 ORDER BY 的列与 WHERE 中的列不同时。临时表可能在内存中，也可能在磁盘上，对性能影响很大。
• Using filesort: 坏信号。表示 MySQL 无法利用索引来完成排序操作，必须在内存或磁盘中进行额外的排序（文件排序）。这通常发生在 ORDER BY 的字段没有索引，或者索引不适用时。这是一个主要的性能瓶瓶颈。

优化目标：追求 Using index，避免 Using temporary 和 Using filesort。

3. key 和 possible_keys

• possible_keys: 指出 MySQL 可以 在这张表上使用的索引。如果这里是 NULL，通常意味着没有可用的索引，需要检查 WHERE 子句中的字段是否建立了索引。
• key: 指出 MySQL 实际决定使用 的索引。如果这里是 NULL 而 possible_keys 有值，可能是因为优化器认为全表扫描比使用索引更快（例如表数据量很小）。

优化目标：确保 key 字段显示了你期望使用的那个最高效的索引。

4. rows (估算的行数)

这个字段是 MySQL 优化器估算的，为了找到目标数据，需要读取的行数。这只是一个估算值，不一定精确，但非常有参考价值。

优化目标：这个数字越小越好。 如果一个查询的 rows 值非常大，同时 type 是 ALL，那么性能问题就非常明显了。

5. key_len (索引长度)

表示本次查询中使用的索引的字节长度。这个值可以帮助你判断是否充分利用了联合索引。

• 对于一个联合索引 (col1, col2, col3)，如果 key_len 的长度只等于 col1 的长度，说明只用到了索引的第一个部分。
• 如果 key_len 等于 col1 和 col2 的长度之和，说明用到了前两个部分。

优化目标：对于联合索引，key_len 的值越大，表示利用得越充分，查询的精确度也越高。

总结与实战建议

1. 先看 type：是不是 ALL？如果是，立刻想办法加索引。目标是 ref 或 range。
2. 再看 Extra：有没有 Using temporary 或 Using filesort？如果有，尝试通过创建合适的索引来消除它们。例如，为 ORDER BY 和 GROUP BY 的列创建索引。
3. 接着看 key：实际使用的索引是不是你预期的那个？如果不是，可能需要强制使用索引（FORCE INDEX，但不推荐常规使用）或分析为何优化器做了不同的选择。
4. 最后看 rows：估算的行数是不是过大？结合 type 字段，可以判断查询的开销。

通过对这几个关键字段的分析，你就能像一位侦探一样，一步步定位到 SQL 语句的性能瓶颈，并采取针对性的优化措施（通常是创建或修改索引）。