蓝队云 - 领先的云服务器、服务器租用托管、域名注册提供商

欢迎来到蓝队云技术小课堂，每天分享一个技术小知识。

EXPLAIN 是 MySQL 提供的一种强大的工具，用于分析 SQL 查询的执行计划。它能够帮助我们了解查询是如何执行的，从而发现潜在的性能瓶颈，并采取优化措施。通过 EXPLAIN 返回的信息，我们可以判断索引是否被正确使用，查询是否存在不必要的全表扫描，是否存在其他影响性能的操作。下面是 EXPLAIN 的详细使用方法和如何根据返回的结果优化查询。

1. 基本用法

要使用 EXPLAIN，只需在你想分析的 SELECT 语句前加上 EXPLAIN 关键字。例如：

EXPLAIN SELECT * FROM your_table WHERE your_column = 'your_value';

执行后，MySQL 会返回查询的执行计划，帮助你分析查询的性能。

2. EXPLAIN 返回值的含义

EXPLAIN 命令返回的结果集包含以下列，它们提供了查询执行的详细信息：

列名	说明
id	查询中的每个 SELECT 子句的标识符，简单查询通常是 1，子查询的 id 从 2 开始。
select_type	查询类型，常见的有：SIMPLE（简单查询）、PRIMARY（最外层查询）、UNION（UNION 查询）、SUBQUERY（子查询）、DERIVED（派生表）。
table	当前操作涉及的表。
type	连接类型，常见的有：ALL（全表扫描）、range（范围扫描）、ref（通过索引查找匹配行）、eq_ref（每行匹配唯一行）。
possible_keys	查询中可能使用的索引。
key	实际使用的索引。如果没有使用索引，显示 NULL。
key_len	使用的索引的长度（字节数）。
ref	显示使用哪个列或常量与 key 一起从表中选择行。
rows	MySQL 估计查询需要扫描的行数。
Extra	额外的信息，常见的有：Using where（使用 WHERE 筛选行）、Using index（只使用索引，无需扫描表）、Using temporary（使用临时表）、Using filesort（使用文件排序）。

3. 常见的 select_type 类型

· SIMPLE：简单查询，不包含子查询或 UNION。

· PRIMARY：最外层的查询。

· UNION：UNION 查询的第二个及后续查询。

· SUBQUERY：子查询中的第一个 SELECT 查询。

· DERIVED：派生表的查询。

4. 常见的 type 类型

· const：只扫描一行，常见于主键或唯一索引查询。

· eq_ref：每个来自前一表的行，联合查询时每次读取一行。

· ref：通过索引查找匹配的行，适用于非唯一索引。

· range：扫描索引的某个范围，效率较高。

· index：全索引扫描，按索引顺序读取。

· ALL：全表扫描，通常是效率最差的访问方式。

5. 示例与解释

假设我们有以下查询：

EXPLAIN SELECT u.user_id, u.name, o.order_idFROM users uJOIN orders o ON u.user_id = o.user_idWHERE u.status = 'active' AND o.order_date > '2024-01-01';

执行计划可能返回如下结果：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	u	ref	PRIMARY	PRIMARY	4	const	5	Using where
1	SIMPLE	o	ref	user_id	user_id	4	database.u.user_id	10	Using where

解释：

· id：查询中只有一个 SELECT 语句，因此 id 为 1。

· select_type：查询类型为 SIMPLE，表示没有子查询。

· table：查询的是 users（u）和 orders（o）表。

· type：users 表使用了 ref 类型的连接方式，orders 表也使用了 ref 类型，意味着都通过索引查找匹配的行。

· possible_keys：users 表的可能索引是 PRIMARY（假设 user_id 是主键），orders 表的可能索引是 user_id。

· key：users 表实际使用了 PRIMARY 索引，orders 表使用了 user_id 索引。

· rows：users 表预计需要读取 5 行，orders 表预计需要读取 10 行。

· Extra：显示 Using where，表示查询会根据 WHERE 子句进行筛选。

6. 日常用法与优化建议

检查索引是否被使用

通过 key 列可以确认查询是否使用了索引。如果 key 为 NULL，则表示没有使用索引，这时需要考虑为查询的字段添加索引，或者优化查询逻辑。

分析查询效率

type 列显示了查询的连接方式，ALL 类型表示全表扫描，这是最不理想的情况。为了提高性能，应该尽量避免 ALL，可以通过创建合适的索引来避免全表扫描。

优化查询

rows 列显示了 MySQL 估计的扫描行数，如果行数过多，查询的性能可能较差。此时可以考虑优化查询条件、增加索引或者重写查询语句。

避免临时表和文件排序

Extra 列如果显示 Using temporary 或 Using filesort，表示查询需要创建临时表或进行额外的排序操作，这可能会严重影响查询性能。尽量避免这些操作，可以通过优化查询、调整索引或重新设计查询来减少临时表的使用。

7. 示例优化

假设我们有一个查询没有使用索引：

EXPLAIN SELECT * FROM users WHERE name = 'John Doe';

返回结果：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	users	ALL	NULL	NULL	NULL	NULL	100000	Using where

· type 是 ALL，表示全表扫描。

· key 为 NULL，表示没有使用索引。

优化方案：

CREATE INDEX idx_name ON users(name);

执行优化后的查询：

EXPLAIN SELECT * FROM users WHERE name = 'John Doe';

返回结果：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	users	ref	idx_name	idx_name	102	const	1	Using where

· type 变为 ref，表示索引查询。

· key 变为 idx_name，表示查询使用了新建的索引。

· 通过这种优化，查询性能显著提高。

8. 总结

EXPLAIN 是优化 MySQL 查询的一个重要工具，它能够帮助我们深入了解查询的执行过程，识别潜在的性能问题。通过 EXPLAIN 返回的各列信息，我们可以判断查询是否高效，是否合理使用了索引，是否有不必要的全表扫描，是否存在文件排序或临时表的使用等。通过合理地使用索引、优化查询条件和连接方式，可以有效提升查询性能。

蓝队云官网上拥有完善的技术支持库可供参考，大家可自行查阅，更多技术问题，可以直接咨询。同时，蓝队云整理了运维必备的工具包免费分享给大家使用，需要的朋友可以直接咨询。

更多技术知识，蓝队云期待与你一起探索。

MySQL Explain使用教程