MySQL - EXPLAIN

MySQL - EXPLAIN 语句

EXPLAIN 语句的作用

MySQL EXPLAIN 语句是理解和优化查询性能最重要的工具。它不执行查询；相反，它提供一份详细的报告，称为执行计划，展示了 MySQL 打算如何执行 SELECT、DELETE、INSERT、REPLACE 或 UPDATE 语句。

通过分析此计划，您可以识别性能瓶颈，例如缺失的索引、低效的连接或不必要的全表扫描。这使您能够对查询和表结构进行有针对性的改进，从而显著加快执行时间。

注意：EXPLAIN <table_name> 是 DESCRIBE <table_name> 的同义词。虽然有用，但其主要目的是显示表结构，而非查询执行。本教程侧重于 EXPLAIN <query>。

语法

基本语法很简单：

EXPLAIN SELECT ... FROM ... WHERE ...;

理解 EXPLAIN 输出

EXPLAIN 的输出是一个包含多列的表。以下是您需要理解的最关键的几列：

• id：查询中每个 SELECT 的顺序标识符。
• select_type：SELECT 的类型（例如，SIMPLE、JOIN、SUBQUERY）。
• table：输出行引用的表。
• type：这一点至关重要。它描述了表是如何连接的。目标是避免 ALL，并争取达到 ref、eq_ref、range 或 index。
• possible_keys：显示 MySQL 可能 用于查找行的索引。
• key：MySQL 决定 实际使用的索引。如果此值为 NULL，则表示没有使用索引，这通常是一个危险信号。
• rows：MySQL 为执行查询必须检查的行数估计值。越低越好。
• Extra：提供额外的、关键的信息。注意 Using filesort 或 Using temporary 等警告，它们表示代价高昂的操作。

关键的 type 值（从优到劣）

• system/const：该表至多只有一行匹配，可以在开始时读取。
• eq_ref：极佳。对于之前表的每行组合，从此表中读取一行。用于主键或唯一键上的连接。
• ref：良好。读取所有具有匹配索引值的行。用于非唯一索引列上的连接。
• range：良好。使用索引，只检索给定范围内的行。
• index：扫描整个索引树。比 ALL 快，但仍不理想。
• ALL：糟糕。执行整个表的完全扫描。这是您必须努力避免在大表上出现的情况。

EXPLAIN ANALYZE：用于实际成本分析

从 MySQL 8.0.18 起可用，EXPLAIN ANALYZE 实际上会执行查询，并提供一个增强的执行计划，其中包含 实际 计时和行数，而不仅仅是估计值。这对于诊断时间花费在哪里非常有帮助。

EXPLAIN ANALYZE SELECT * FROM customers WHERE name = 'Alice Smith';

-- Example Output:
-- 示例输出：
-> Filter: (customers.name = 'Alice Smith')  (cost=7.35 rows=1) (actual time=0.040..0.082 rows=1 loops=1)
    -> Table scan on customers  (cost=7.35 rows=70) (actual time=0.033..0.071 rows=70 loops=1)

输出显示了估计的成本/行数，以及计划每个部分的 actual time（实际时间）和 actual rows（实际行数）。

输出格式：TREE 和 JSON

您可以更改输出格式，以提高可读性或进行程序化分析。

• FORMAT=TREE：提供执行计划的分层视图，对于复杂的连接通常更易于阅读。
• FORMAT=JSON：输出详细的 JSON 对象，非常适合输入到分析工具中。

-- Tree format is often the default for EXPLAIN ANALYZE
-- Tree 格式通常是 EXPLAIN ANALYZE 的默认格式
EXPLAIN FORMAT=TREE SELECT * FROM customers c JOIN orders o ON c.id = o.customer_id;

-- JSON format for detailed analysis
-- JSON 格式用于详细分析
EXPLAIN FORMAT=JSON SELECT * FROM customers WHERE id = 1\G

实用优化演练

让我们逐步优化一个慢查询。我们将使用 INNER JOIN 教程中的 customers 和 orders 表，但假设我们忘记创建了一个关键索引。

步骤 1：慢查询

假设 orders 表中的 customer_id 列是在没有索引的情况下创建的：

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATETIME NOT NULL,
    customer_id INT NOT NULL, -- No index on this column!
    -- 该列上没有索引！
    amount DECIMAL(10, 2) NOT NULL
);

步骤 2：使用 EXPLAIN 分析执行计划

我们对连接查询运行 EXPLAIN。

EXPLAIN SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE c.name = 'Alice Smith';

orders 表的简化输出如下所示：

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	o	ALL	NULL	NULL	10000	Using where

问题立即清晰可见：type 是 ALL，key 是 NULL。MySQL 正在对 orders 进行全表扫描，因为 customer_id 没有索引。

步骤 3：应用索引

我们通过在外键列上添加索引来解决这个问题。

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

步骤 4：重新分析执行计划

现在，我们再次运行相同的 EXPLAIN 命令。orders 表的输出将显著改善：

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	o	ref	idx_orders_customer_id	idx_orders_customer_id	2	Using where

成功！type 现在是 ref，key 显示我们正在使用新索引。估计要检查的 rows 数已从 10,000 降至仅 2。现在查询的速度将提高数个数量级。