EXPLAIN执行计划中要重点关注哪些要素？

EXPLAIN介绍及执行计划中的重点关注要素

一、EXPLAIN介绍

EXPLAIN是MySQL中用于查看查询执行计划的关键字。执行计划是数据库查询优化器生成的一种指导性信息，它详细描述了MySQL执行查询时所采用的具体执行策略。通过执行EXPLAIN，用户可以获取查询的执行计划信息，从而了解查询的执行过程、访问方式、是否使用了索引、是否存在性能瓶颈等，进而优化查询语句和索引设计，提高查询性能。

二、执行计划中的重点关注要素

在执行计划中，有几个关键要素需要重点关注，它们对于理解查询性能和优化查询至关重要。以下是对这些要素的详细介绍，并结合实例进行说明。

1. select_type

• 含义：表示查询的类型，主要用于区分简单查询、联合查询、子查询等复杂查询。
• 重点关注点：了解查询的复杂程度，判断是否需要优化查询结构。

select_type	描述
SIMPLE	简单的SELECT查询，不包含子查询或UNION
PRIMARY	主查询（外层查询）
SUBQUERY	子查询中的第一个SELECT
DERIVED	派生表查询，表示从FROM子句中派生出的临时表
UNION	UNION中的第二个或后续SELECT语句
UNION RESULT	UNION的结果

2. type

• 含义：表示MySQL如何找到所需的行，即连接类型。它反映了查询的效率。
• 重点关注点：评估查询的效率，判断是否需要优化索引或查询条件。

type类型及其说明

Type	说明
system	表中只有一条记录（或空表，但这种情况较少见）。这是const类型的特例，通常可以忽略不计。
const	表示通过索引一次就找到了，通常用于PRIMARY KEY或UNIQUE索引。因为只匹配一行数据，所以非常快。
eq_ref	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键和唯一索引扫描。
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。虽然比全表扫描要好，但可能会找到多个符合条件的行。
range	只检索给定范围的行，使用一个索引来选择行。常见于BETWEEN、<、>、IN等查询中。
index	索引全表扫描，与ALL类型相似，但index是遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。
ALL	全表扫描，将遍历全表以找到匹配的行，性能最差。

表示例

假设我们有一个名为employees的表，其结构包括id（主键）、name（普通索引）、department_id（外键）等字段。以下是一些使用EXPLAIN的示例及其type类型：

SQL查询	type	说明
SELECT * FROM employees WHERE id = 1;	const	使用主键索引，直接定位到唯一一行。
SELECT * FROM employees WHERE name = 'John Doe';	ref	使用普通索引name，可能返回多行。
SELECT * FROM employees WHERE department_id BETWEEN 10 AND 20;	range	使用索引扫描部门ID在10到20之间的所有行。
SELECT department_id FROM employees ORDER BY name;	index	遍历索引树name以排序department_id，但不访问数据行。
SELECT * FROM employees WHERE salary > 100000;	ALL	如果没有合适的索引，将进行全表扫描以查找薪水大于100000的所有员工。

优化建议

• 尽可能使用PRIMARY KEY或UNIQUE索引，以获得const类型的查询性能。
• 对于非唯一性索引，确保查询条件能够有效利用索引，避免ALL类型的全表扫描。
• 对于范围查询（如BETWEEN、<、>等），确保查询列上有索引。
• 定期审查和优化索引策略，以确保查询性能。

通过上述说明和表格，可以更清晰地理解EXPLAIN命令中type列的含义及其在实际查询优化中的重要性。

3. possible_keys 和 key

• possible_keys：表示可能应用在这张表上的索引。
• key：表示实际使用的索引。
• 重点关注点：检查查询是否使用了预期的索引，如果没有使用或使用了不合适的索引，则需要考虑优化索引或查询条件。

4. rows

• 含义：MySQL认为必须检查的行数来找到所需的记录。这是一个估算值，但可以作为评估查询性能的重要指标。
• 重点关注点：行数越少，表示查询效率越高。如果rows值很大，说明查询可能需要检查大量数据，这会影响查询性能。

5. Extra

• 含义：包含不适合在其他列中显示但十分重要的额外信息。
• 重点关注点：关注是否出现“Using filesort”、“Using temporary”等表示性能问题的警告信息，以及是否使用了覆盖索引（Using index）。

EXTRA 值	说明
Using index	表示MySQL仅使用了索引来完成查询，而无需回表读取实际的数据行。这通常发生在索引覆盖了查询所需的所有列时（即索引覆盖扫描）。
Using where	表示MySQL在存储引擎检索行后，再对这些行进行过滤。这通常发生在索引不能完全覆盖查询条件时。
Using filesort	表示MySQL需要对结果集进行排序，且排序操作无法利用索引完成，因此需要进行额外的排序步骤。这可能会影响查询性能。
Using temporary	表示MySQL在执行查询时需要使用临时表来存储部分或全部结果集。这通常发生在复杂的查询操作中，如GROUP BY、DISTINCT等，且这些操作无法仅通过索引来完成。
Using index condition	这是MySQL 5.6及以上版本引入的索引条件推送（Index Condition Pushdown, ICP）功能的体现。它表示MySQL将部分WHERE条件推送到存储引擎的索引扫描阶段，以减少需要回表读取的数据量。
Impossible WHERE	表示WHERE子句的条件永远不可能为真，因此MySQL不会执行该查询的其余部分。
No tables used	表示查询中没有引用任何表，如只包含常量或表达式的SELECT语句。
Distinct	表示MySQL正在使用唯一化操作来确保查询结果中的行是唯一的。这通常发生在SELECT语句中使用了DISTINCT关键字时。

在实际应用中，如果EXTRA列中出现了Using filesort或Using temporary，这通常意味着查询可能需要优化。例如，你可以尝试通过调整查询逻辑、添加或修改索引、改变查询的JOIN顺序等方式来减少排序和临时表的使用，从而提高查询性能。

三、实例分析

以下是一些使用EXPLAIN分析查询执行计划的实例，通过这些实例可以更好地理解上述要素在实际查询中的应用。

实例1：简单的等值查询

sql

EXPLAIN SELECT id, name, age FROM users WHERE id = 1;

执行计划分析：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	users	const	PRIMARY	PRIMARY	4	const	1

• select_type：SIMPLE，表示这是一个简单的SELECT查询。
• type：const，表示通过常量条件进行检索，性能最好。
• key：PRIMARY，表示使用了主键索引。
• rows：1，表示MySQL只需扫描1行数据。
• Extra：无额外信息，表示查询效率高。

实例2：使用索引的范围查询

sql

EXPLAIN SELECT id, name, age FROM users WHERE age > 30;

执行计划分析：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	users	range	idx_age	idx_age	4	NULL	100	Using where

• type：range，表示索引范围扫描，性能较好。
• key：idx_age，表示实际使用了age列的索引。
• rows：100，表示MySQL预计需要扫描100行数据来找到满足条件的记录（这是一个估算值）。
• Extra：Using where，表示在索引扫描后，还需要通过WHERE子句进行过滤。

优化建议：

• 确保age列上的索引是有效的，并且是最优的。
• 如果查询条件经常变化，考虑使用复合索引或调整索引策略。

实例3：多表连接查询

sql

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

执行计划分析（假设users表的id列和orders表的user_id列都有索引）：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	u	range	PRIMARY,idx_age	idx_age	4	NULL	100	Using where
1	SIMPLE	o	ref	idx_user_id	idx_user_id	4	db.u.id	10

• type：对于users表是range，对于orders表是ref，表示orders表通过users表的索引进行了高效的连接。
• key：users表使用了age列的索引idx_age，orders表使用了user_id列的索引idx_user_id。
• rows：users表预计扫描100行，orders表预计每行users表的数据对应10行orders表的数据。
• ref：orders表的ref列显示了它是如何与users表连接的，即使用users表的id列作为引用。

优化建议：

• 确保连接条件上的索引是有效的，并且是最优的。
• 如果查询中经常需要连接这两个表，并且连接条件不变，考虑创建复合索引（如果尚未创建）。

实例4：子查询

sql

EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id 
FROM orders WHERE order_status = 'shipped');

执行计划分析（假设子查询和主查询都使用了索引）：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	users	ALL	PRIMARY	NULL	NULL	NULL	1000	Using where
2	SUBQUERY	orders	ref	idx_order_status	idx_order_status	767	const	100	Using index

• select_type：主查询为PRIMARY，子查询为SUBQUERY。
• type：主查询为ALL（全表扫描，性能较差，但可能是因为子查询的结果集较大），子查询为ref（索引引用，性能较好）。
• key：子查询使用了order_status列的索引idx_order_status。
• Extra：子查询的Extra列显示Using index，表示直接通过索引获取了需要的数据，没有回表查询。

优化建议：

进一步优化建议：

最后，优化查询时应该根据实际的查询性能和数据量来做出决策。有时候，简单的查询调整就可以带来显著的性能提升，而有时候则需要更复杂的策略。始终使用EXPLAIN来分析和验证你的查询优化效果。

• 如果子查询的结果集较小，考虑将子查询优化为JOIN操作，因为JOIN操作在大多数情况下能够更有效地利用索引和减少数据扫描量。

  优化后的查询：

  sql

• EXPLAIN SELECT u.*   
  FROM users u  
  JOIN orders o ON u.id = o.user_id  
  WHERE o.order_status = 'shipped';

• 优化后的执行计划分析（假设users.id、orders.user_id和orders.order_status都有索引）：

  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
  1	SIMPLE	o	ref	idx_user_id,idx_order_status	idx_order_status	767	const	100	Using index
  1	SIMPLE	u	eq_ref	PRIMARY	PRIMARY	4	db.o.user_id	1

• type：orders表使用了ref类型，因为它通过order_status索引找到了满足条件的行。users表使用了eq_ref类型，表示它通过主键索引与orders表进行了高效的连接。
• key：orders表使用了order_status索引idx_order_status，users表则直接使用了主键索引PRIMARY。
• rows：orders表预计扫描100行，每行对应users表中的1行数据（因为是通过主键连接）。
• ref：users表的ref列显示了它是如何通过orders表的user_id列进行连接的。
• 确保所有参与连接的列上都有索引，并且索引是有效的。
• 如果order_status的值经常作为查询条件，考虑在该列上创建索引（如已创建则无需重复）。
• 如果users表和orders表的数据量非常大，并且查询性能仍然不足，可以考虑对连接条件进行分区处理或使用更高级的优化技术，如查询缓存、物化视图等（但请注意，这些技术并非总是适用或有效）。