mysql菜鸟教程

首页 >> mysql菜鸟教程

专栏导航

13.6 EXPLAIN语句：查看查询执行计划

在数据库优化中，了解 MySQL 是如何执行你的 SQL 语句至关重要。EXPLAIN 语句就是 MySQL 提供的“透视镜”，它可以展示查询的执行计划，让你看到是否使用了索引、表的连接顺序、扫描行数等信息，从而帮助你诊断慢查询并优化索引。

一、什么是 EXPLAIN？

EXPLAIN 关键字用于查看一个 SQL 语句的执行计划。执行计划是 MySQL 优化器决定如何执行查询的步骤和路径。通过 EXPLAIN，你可以了解：

表的读取顺序
数据表之间的连接类型
哪些索引被使用
每张表可能扫描的行数
是否使用了临时表或文件排序等

这些信息是优化查询的重要依据。

基本语法

EXPLAIN SELECT ...;

例如：

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

你还可以在 EXPLAIN 后加上 FORMAT=JSON 获取更详细的 JSON 格式输出：

EXPLAIN FORMAT=JSON SELECT ...;

二、EXPLAIN 输出列详解

执行 EXPLAIN 后，MySQL 会返回一行或多行结果，每一行代表查询中的一个表（可能是物理表或派生表）。下面我们以一张典型的结果集为例，解释每一列的含义。

列名	说明
id	查询中每个 SELECT 子句的标识符。数字越大，越先执行；相同 id 表示是一组，从上往下执行。
select_type	查询的类型，如 SIMPLE （简单查询）、 PRIMARY （主查询）、 SUBQUERY （子查询）、 DERIVED （派生表）、 UNION 等。
table	正在访问的表名（或别名）。
partitions	匹配的分区（如果表分区）。
type	连接类型，这是衡量查询效率的重要指标，从好到差依次为： system > const > eq_ref > ref > range > index > ALL 。我们将在后面详细解释。
possible_keys	可能使用的索引列表。
key	实际使用的索引名。如果为 NULL ，则表示没有使用索引。
key_len	使用的索引字节长度。可用于判断复合索引实际使用了多少列。
ref	显示索引的哪一列或常量被用于与索引比较。
rows	MySQL 估计需要扫描的行数（不是精确值，只是一个估算）。
filtered	按表条件过滤后，剩余行数的百分比（估算）。
Extra	额外信息，如 Using index （覆盖索引）、 Using where 、 Using temporary 、 Using filesort 等，对分析性能很有帮助。

type 列详解

type 列表示 MySQL 如何查找表中的行，其值从上到下性能递减：

type	含义	说明
system	表只有一行（系统表），是 const 的特例。	极少出现。
const	使用主键或唯一索引进行等值查询，最多返回一行。	非常快。
eq_ref	在多表连接中，使用主键或唯一索引作为连接条件，每个驱动表的行只匹配一行。	常见于主键关联。
ref	使用非唯一索引进行等值查询，或使用唯一索引的前缀部分。	如 WHERE name = '张三' 且 name 有普通索引。
range	使用索引进行范围查询，如 BETWEEN 、 > 、 < 、 IN 、 LIKE 'prefix%' 。	效率也不错。
index	扫描整个索引树，但通常比全表扫描快，因为索引通常比数据小。	常见于覆盖索引或 ORDER BY 主键。
ALL	全表扫描，需要遍历全表才能找到匹配行。	通常需要优化，添加索引。

Extra 列常见值

Extra 信息	含义	是否建议优化
Using index	使用了覆盖索引，不需要回表查询数据。	好现象。
Using where	使用 WHERE 条件过滤数据。	正常。
Using index condition	使用了索引条件下推（ICP）优化，减少回表次数。	好。
Using temporary	使用了临时表（如 GROUP BY 无索引、 DISTINCT 等）。	可能需优化。
Using filesort	需要额外的排序操作（无法利用索引排序）。	尽量避免，可考虑加索引。
Using join buffer	连接时使用了连接缓冲区，可能没有索引。	应检查连接条件索引。
Impossible WHERE	WHERE 条件永远为假，不会返回结果。	检查条件。

三、实战示例

我们通过几个示例来学习如何使用 EXPLAIN 分析查询。

示例表结构

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT,
    created_at DATETIME,
    INDEX idx_email (email),
    INDEX idx_age (age)
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10,2),
    order_date DATE,
    INDEX idx_user_id (user_id),
    INDEX idx_order_date (order_date)
);

1. 简单查询，使用索引

EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

可能输出：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	users	ref	idx_email	idx_email	402	const	1	Using index condition

解释：

type = ref：使用了非唯一索引等值查询。
key = idx_email：实际使用的索引。
rows = 1：估计只扫描一行。
Extra = Using index condition：使用了索引条件下推（ICP），但这里不是覆盖索引，因为 SELECT * 需要回表。

2. 无索引的列查询

EXPLAIN SELECT * FROM users WHERE username = 'john';

如果 username 没有索引：

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	users	ALL	NULL	NULL	1000	Using where

解释：

type = ALL：全表扫描。
rows = 1000：估计扫描所有行。
建议为 username 添加索引。

3. 复合索引与最左前缀

假设我们为 orders 表创建一个复合索引：

CREATE INDEX idx_user_date ON orders(user_id, order_date);

查询：

EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND order_date = '2024-01-01';

输出：

type	possible_keys	key	key_len	ref	rows	Extra
ref	idx_user_date	idx_user_date	8	const,const	1	Using index condition

如果只查询 order_date：

EXPLAIN SELECT * FROM orders WHERE order_date = '2024-01-01';

可能 type = ALL 或 index，因为无法使用复合索引的左前缀，除非有单独的索引。

4. 排序与文件排序

EXPLAIN SELECT * FROM users ORDER BY age;

如果 age 有索引：

type	key	Extra
index	idx_age	Using index

如果没有索引：

type	key	Extra
ALL	NULL	Using filesort

Using filesort 表示需要额外的排序操作，当数据量大时可能很慢，考虑为排序列创建索引。

5. 连接查询

EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE u.age > 30;

输出通常有两行，分别对应 users 和 orders。注意 type 和 Extra，观察是否使用了索引。

四、如何利用 EXPLAIN 优化查询？

检查 type 列：尽量让 type 达到 ref 或 range 级别，避免 ALL。
观察 key 列：确保使用了预期的索引。如果 possible_keys 有候选但 key 为 NULL，说明优化器认为全表扫描更快（可能因为数据量小或索引选择性差）。可以尝试用 FORCE INDEX 或调整查询。
关注 rows 列：估算扫描的行数，与表的总行数对比，如果差距太大，考虑优化索引。
分析 Extra 列：

出现 Using temporary 或 Using filesort 时，考虑添加合适的索引或改写查询。
出现 Using index 很好，说明使用了覆盖索引。
出现 Using where 正常，但如果加上 Using index 可能更好。

复合索引的顺序：根据查询中最常出现的列顺序调整索引列顺序，尽量利用左前缀。
关注 key_len：可以判断复合索引实际使用了多少列，如果比预期短，说明没有完全用上。

五、注意事项

EXPLAIN 只是估算，并不实际执行查询，因此 rows 和 filtered 是估计值，可能与实际执行有偏差。
对于复杂的查询，EXPLAIN 可能会产生多行，需要仔细阅读。
在 MySQL 5.6 及以后，可以使用 EXPLAIN FORMAT=JSON 获得更详细的信息，包括成本估算。
有些 Extra 信息如 Using index condition（ICP）是优化手段，不必过度担心。
对于 UPDATE 或 DELETE 语句，也可以使用 EXPLAIN，但需要先改成 SELECT 以分析执行计划（例如 EXPLAIN SELECT ... FROM 表 WHERE ...）。

六、实际优化案例

假设有一个慢查询：

SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31' ORDER BY amount DESC LIMIT 10;

执行 EXPLAIN 发现 type = ALL 且 Extra = Using filesort。解决方案：

为 order_date 创建索引以加速范围查询。
如果仍然需要文件排序，考虑创建复合索引 (order_date, amount)，使排序也能利用索引。

修改后再次执行 EXPLAIN，应看到 type = range 且 Extra = Using index condition 或 Using where; Using index（如果覆盖索引）。

七、小结

EXPLAIN 是分析查询执行计划的利器，能帮助你理解查询的性能瓶颈。
重点关注的列：type、key、rows、Extra。
优化目标：避免全表扫描，减少扫描行数，避免文件排序和临时表。
结合索引设计，持续使用 EXPLAIN 验证优化效果。