MySQL 的 EXPLAIN 语句可以帮助我们了解查询的执行计划，从而优化 SQL 语句的性能。

1. 基本使用方法

在我们的查询前面加上 EXPLAIN 关键字。例如：

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

执行后，MySQL 会返回一个结果集，其中包含了查询优化器对这条 SQL 的执行计划的评估结果。

2. 输出字段解析

常见的输出字段包括：

• id：表示查询中每个 SELECT 语句的标识符。如果有子查询，则每个子查询会有自己的 id。id 较大的行先执行。简单查询的 id 通常为1，复杂查询（如包含子查询或 UNION ）的 id 会有多个。
• select_type：表示 SELECT 的类型，如 SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
• table：显示当前行所引用的表。
• type：连接类型，表示查找方式。常见值从好到差依次为：system、const、eq_ref、ref、range、index、ALL。一般来说，ALL 表示全表扫描，性能较差。
• possible_keys：可能用于查询的索引列表。
• key：实际选择使用的索引。如果为 NULL，表示未使用索引。
• key_len：表示 MySQL 选择的索引的长度，用于估算读取的行数。
• ref：显示哪个列或常量与 key 结合被使用。
• rows：MySQL 估算需要读取的行数。行数越多，通常说明查询效率可能较低。
• Extra：提供额外的信息，如 “Using where” 表示使用了 WHERE 条件过滤；“Using index” 表示使用了覆盖索引；“Using temporary” 表示查询过程中使用了临时表等。

3. 进阶用法

• EXPLAIN EXTENDED
  在一些情况下，我们可以使用 EXPLAIN EXTENDED，它会给出更多的诊断信息。在执行后，可以使用 SHOW WARNINGS; 查看 MySQL 对查询的优化建议。
• FORMAT=JSON
  对于 MySQL 5.6 及以上版本，我们还可以使用 EXPLAIN FORMAT=JSON 来获得 JSON 格式的执行计划，这对于自动化工具或更复杂的分析会更加直观。例如：

  EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 123;

4. 分析执行计划的步骤

1. 检查索引使用情况
   首先看 possible_keys 和 key 字段，判断查询是否能够利用索引。如果 key 为 NULL 而 possible_keys 不为空，可能需要检查查询条件或索引设计。
2. 查看连接类型
   type 字段是判断查询性能的重要指标，理想情况下希望看到 const、eq_ref 或 ref。如果看到 ALL，则说明查询使用了全表扫描，通常需要优化。
3. 估算扫描行数
   rows 字段告诉你 MySQL 预估需要扫描的行数。行数越少越好，行数多说明可能需要优化查询条件或增加适当的索引。
4. 注意额外信息
   Extra 字段中出现 “Using temporary” 或 “Using filesort” 往往表明查询存在潜在性能瓶颈，可以考虑通过优化 SQL 或调整索引来改善。

5. 实际案例

假设我们有以下两个表：

customers 表

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

orders 表

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

索引

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_order_date ON orders(order_date);

查询：

我们想要查询所有订单金额大于 1000 的客户的订单信息，并且按照订单日期排序：

SELECT o.order_id, o.order_date, o.amount, c.name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.amount > 1000
ORDER BY o.order_date;

步骤 1: 使用 EXPLAIN 分析查询

在查询前加上 EXPLAIN，查看执行计划：

EXPLAIN SELECT o.order_id, o.order_date, o.amount, c.name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.amount > 1000
ORDER BY o.order_date;

输出结果（假设）

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	o	ref	idx_orders_customer_id, idx_orders_order_date	idx_orders_customer_id	4	const	500	Using where; Using index; Using temporary; Using filesort
1	SIMPLE	c	eq_ref	PRIMARY	PRIMARY	4	o.customer_id	1

步骤 2: 分析输出

1. id: 查询只有一个 SELECT 语句，所以 id 为 1。通常，如果查询有子查询，子查询会有不同的 id。
2. select_type: 查询类型为 SIMPLE，表示这是一个简单查询，没有嵌套查询。
3. table: 第一行显示的是 orders 表（别名 o），第二行显示的是 customers 表（别名 c）。表示我们正在联接这两个表。
4. type: orders 表使用的是 ref 类型，表示它使用了索引来连接 customers 表。customers 表使用的是 eq_ref 类型，表示 MySQL 使用主键来查找单一的行。
5. possible_keys: 对于 orders 表，possible_keys 显示了两个可能的索引：idx_orders_customer_id 和 idx_orders_order_date，分别是针对 customer_id 和 order_date 字段的索引。对于 customers 表，只有 PRIMARY 索引可用。
6. key: 实际使用的索引是 idx_orders_customer_id，这是因为查询中的 WHERE 子句包含了 amount > 1000 这一条件，而索引 idx_orders_customer_id 被用来高效地匹配客户 ID。idx_orders_order_date 没有被使用，因为查询的过滤条件并没有针对 order_date 列。
7. key_len: key_len 是 4，表示 MySQL 使用了 4 字节的索引长度。这是因为 customer_id 是一个整数类型，所以它的索引长度是 4 字节。
8. ref: 在 orders 表的行中，ref 显示的是 const，表示查询使用了常量值（具体来说，就是 customer_id）来查找符合条件的记录。customers 表的行使用了 o.customer_id 来查找匹配的记录。
9. rows: 对于 orders 表，rows 显示 500，表示 MySQL 预计需要扫描 500 行记录才能找到满足 amount > 1000 条件的订单。对于 customers 表，rows 显示为 1，表示在 customers 表中使用 customer_id 查找时只会匹配到一行。
10. Extra:
    • Using where: 表示查询中使用了 WHERE 子句来过滤数据。
    • Using index: 表示查询使用了覆盖索引，也就是说，orders 表的索引不仅包含了 customer_id，还包含了其他字段（如 amount），所以查询可以仅通过索引来获取所需的数据，不需要回表。
    • Using temporary: 表示查询在处理中创建了临时表。这通常发生在有 ORDER BY 的查询中。
    • Using filesort: 表示 MySQL 使用了外部排序机制来对查询结果进行排序。这通常是因为 ORDER BY 没有使用索引进行排序，所以 MySQL 需要额外的步骤来完成排序。

步骤 3: 优化建议

从 EXPLAIN 输出中，我们可以看到：

• orders 表使用了索引，但是 MySQL 仍然需要创建临时表并进行文件排序。这个可能是因为 ORDER BY 没有利用索引，导致查询性能下降。
• 如果查询频繁，可能需要考虑将 amount 和 order_date 这两个字段一起建立复合索引，优化排序操作。
• 另外，如果数据量很大，可以进一步检查 amount > 1000 的条件，确保该条件使用了合适的索引。

扩展：type 值及含义

1. system

• 含义：当表中只有一行（通常是系统表）时使用。
• 特点：这是最理想的情况，几乎不需要查找操作。

2. const

• 含义：常量查找。通常用于主键或唯一索引的等值匹配。
• 特点：查询返回最多一行数据，MySQL 在解析阶段就可以确定结果，非常高效。

3. eq_ref

• 含义：在连接查询中，当对于前面的表中每一行，在关联表中都能找到一条匹配记录时使用。
• 特点：一般出现在使用主键或唯一索引的连接中，每个连接都只返回一条记录，是非常高效的连接方式。

4. ref

• 含义：非唯一索引查找。用于非唯一索引的等值匹配，也可能用于常量与索引列的匹配。
• 特点：可能返回多条记录，但性能仍然较好，因为 MySQL 能通过索引快速定位数据行。

5. range

• 含义：范围扫描。查询条件使用了索引范围，例如 BETWEEN、>, <, >=, <= 或 IN() 中部分匹配。
• 特点：MySQL 会根据索引范围查找符合条件的记录，扫描的行数通常比全表扫描少，但会比 eq_ref 和 ref 慢一些。

6. index

• 含义：全索引扫描。与全表扫描类似，但数据是从索引中读取的（通常是覆盖索引）。
• 特点：虽然仍然需要扫描整个索引，但由于索引一般比表数据小，性能比全表扫描略好。适用于需要读取大部分数据时。

7. ALL

• 含义：全表扫描。没有使用任何索引，直接扫描整个表。
• 特点：性能最差，适用于小表或当查询条件不适合索引时。数据量大的表全表扫描会显著影响性能。

总结

• 性能排序：system > const > eq_ref > ref > range > index > ALL
• 优化建议：
  • 尽量设计合理的索引，避免全表扫描（ALL）。
  • 使用唯一索引（const 或 eq_ref）能大大提高查询效率。
  • 对于范围查询，确保索引的选择性足够高，减少扫描的行数。
  • 如果查询返回大量数据且不需要全表扫描，可以考虑覆盖索引（index）。