EXPLAIN深度剖析

一、EXPLAIN介绍

（一）EXPLAIN 是什么

EXPLAIN 是 MySQL 提供的一个强大工具，用于分析 SQL 语句的执行计划。简单来说，它就像是一个 SQL 语句的 “翻译器”，将我们编写的 SQL 语句转换为 MySQL 实际执行的步骤和策略，让我们能够清晰地了解到 MySQL 是如何执行查询的。通过使用 EXPLAIN，我们可以获取到很多关于查询执行的详细信息，这些信息对于优化 SQL 语句至关重要。

（二）为什么要用 EXPLAIN

在实际的开发中，我们编写的 SQL 语句可能在数据量较小的时候表现良好，但当数据量逐渐增大，或者在高并发的场景下，就可能出现性能问题。使用 EXPLAIN，我们可以在不实际执行 SQL 语句的情况下，了解到查询的执行计划，从而找出潜在的性能瓶颈。比如，我们可以通过 EXPLAIN 判断查询是否使用了合适的索引，如果没有使用索引，或者使用了错误的索引，就可能导致全表扫描，从而使查询效率低下。通过分析 EXPLAIN 的结果，我们可以针对性地优化 SQL 语句，添加或修改索引，调整查询条件等，以提高查询的性能，进而提升整个系统的性能和响应速度，为用户提供更好的体验。

（三）EXPLAIN 的使用方法

EXPLAIN 的使用非常简单，基本语法格式为：EXPLAIN + SQL语句。例如，我们有一个简单的查询语句，从名为users的表中查询所有用户信息：

SELECT * FROM users;

使用 EXPLAIN 时，只需在该语句前加上EXPLAIN关键字即可：

EXPLAIN SELECT * FROM users;

执行上述语句后，MySQL 会返回一个结果集，展示该查询语句的执行计划相关信息，包含多个字段，每个字段都有其特定的含义，这些字段信息对于我们分析和优化查询语句非常关键。后续我们会详细介绍这些字段的含义以及如何通过它们来优化 SQL 查询。

二、EXPLAIN 输出字段深度解读

（一）id 字段

id 是查询中每个 SELECT 语句的唯一标识符，在 MySQL 的查询执行过程中，id 起着至关重要的作用，它决定了查询的执行顺序。

当查询中只有一个 SELECT 语句时，id 的值通常为 1 。例如：

EXPLAIN SELECT * FROM users;

执行上述语句，得到的结果中 id 字段的值为 1，表示这是一个简单的单查询，执行顺序就是直接从users表中查询所有数据。

在子查询中，id 的作用就更加明显了。子查询是嵌套在主查询中的查询，id 会为每个子查询分配一个唯一的序号，并且 id 值越大，优先级越高，越先被执行。例如：

EXPLAIN SELECT score.* FROM student_score as score WHERE subject_id = (SELECT id FROM subject WHERE teacher_id = (SELECT id FROM teacher WHERE id = 2));

在这个例子中，最内层的子查询(SELECT id FROM teacher WHERE id = 2)的 id 值最大，它会最先被执行，因为它要先确定教师的 id；接着执行中间层的子查询(SELECT id FROM subject WHERE teacher_id = (SELECT id FROM teacher WHERE id = 2))，根据教师 id 来确定学科 id；最后执行主查询SELECT score.* FROM student_score as score WHERE subject_id = (SELECT id FROM subject WHERE teacher_id = (SELECT id FROM teacher WHERE id = 2))，根据学科 id 来查询学生成绩。

当查询中包含联合查询（UNION）时，id 的情况会稍微复杂一些。如果 id 相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行。例如：

EXPLAIN SELECT subject.* FROM subject LEFT JOIN teacher ON subject.teacher_id = teacher.id
UNION
SELECT subject.* FROM subject RIGHT JOIN teacher ON subject.teacher_id = teacher.id;

在这个联合查询中，两个 SELECT 语句的 id 可能相同也可能不同，假设它们的 id 分别为 1 和 2，那么 id 为 2 的查询会先执行，然后是 id 为 1 的查询，最后将两个查询的结果合并起来。通过 id 字段，我们能够清晰地了解查询中各个部分的执行顺序，这对于优化复杂查询非常重要。如果发现某个子查询的执行顺序不合理，导致性能低下，我们就可以通过调整查询结构或者添加合适的索引来优化查询。

（二）select_type 字段：查询类型

select_type 字段用于标识查询的类型，它就像是一个标签，告诉我们这个查询是简单查询、子查询还是其他复杂类型的查询。不同的查询类型有着不同的执行特点和性能表现，了解 select_type 字段对于优化 SQL 查询至关重要。

常见的 select_type 类型有以下几种：

SIMPLE：简单查询，不包含子查询或 UNION 查询。这种类型的查询没有过多的复杂逻辑，执行起来相对简单高效。例如：

EXPLAIN SELECT * FROM users WHERE age > 20;

这个查询就是一个简单查询，select_type 字段的值为 SIMPLE，它直接从users表中查询年龄大于 20 的用户数据。

PRIMARY：如果查询中包含任何复杂的子部分，最外层的查询则被标记为主查询（PRIMARY）。它就像是一场大型交响乐的总指挥，统筹着整个查询的执行。例如：

EXPLAIN SELECT score.* FROM student_score as score WHERE subject_id = (SELECT id FROM subject WHERE teacher_id = (SELECT id FROM teacher WHERE id = 2));

在这个例子中，最外层的查询SELECT score.* FROM student_score as score WHERE subject_id = (SELECT id FROM subject WHERE teacher_id = (SELECT id FROM teacher WHERE id = 2))的 select_type 字段为 PRIMARY，它依赖于内层子查询的结果来完成整个查询。

SUBQUERY：在 SELECT 或 WHERE 中包含子查询时，子查询中的第一个 SELECT 查询会被标记为 SUBQUERY。它就像是交响乐中的一段精彩独奏，虽然是整个演出的一部分，但有着自己独特的旋律。例如：

EXPLAIN SELECT score.* FROM student_score as score WHERE subject_id = (SELECT id FROM subject WHERE teacher_id = (SELECT id FROM teacher WHERE id = 2));

这里的子查询(SELECT id FROM subject WHERE teacher_id = (SELECT id FROM teacher WHERE id = 2))中的第一个 SELECT 查询的 select_type 字段为 SUBQUERY 。

DERIVED：在 FROM 列表中包含的子查询被标记为 DERIVED（衍生），MySQL 会递归执行这些子查询，把结果放在临时表中。例如：

EXPLAIN SELECT * FROM (SELECT id, name FROM users WHERE age > 20) AS derived_table;

在这个例子中，子查询(SELECT id, name FROM users WHERE age > 20)被标记为 DERIVED，MySQL 会先执行这个子查询，将结果存储在临时表derived_table中，然后再从临时表中查询数据。

UNION：若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION。它就像是交响乐中的不同乐章，各自独立又相互呼应。例如：

EXPLAIN SELECT subject.* FROM subject LEFT JOIN teacher ON subject.teacher_id = teacher.id
UNION
SELECT subject.* FROM subject RIGHT JOIN teacher ON subject.teacher_id = teacher.id;

在这个联合查询中，第二个 SELECT 语句的 select_type 字段为 UNION 。

UNION RESULT：从 UNION 表获取结果的 SELECT。它就像是交响乐的最终合奏，将各个乐章的精华融合在一起。例如：

EXPLAIN SELECT subject.* FROM subject LEFT JOIN teacher ON subject.teacher_id = teacher.id
UNION
SELECT subject.* FROM subject RIGHT JOIN teacher ON subject.teacher_id = teacher.id;

在这个例子中，用于获取 UNION 结果的 SELECT 的 select_type 字段为 UNION RESULT 。

通过了解 select_type 字段，我们可以快速判断查询的复杂程度和类型，从而有针对性地进行优化。对于简单查询，可以直接关注查询条件和索引的使用；对于复杂的子查询或联合查询，需要考虑如何优化子查询的执行顺序，或者是否可以将子查询改写为 JOIN 操作，以提高查询性能。

（三）table 字段：锁定查询涉及的表

table 字段显示了查询涉及的表名或别名，它就像是地图上的标记，明确地告诉我们查询的路径经过了哪些 “站点” 。无论是简单查询还是复杂的多表关联查询，table 字段都能让我们一目了然地知道查询涉及到哪些表。

在单表查询中，table 字段非常直观，就是我们查询的表名。例如：

EXPLAIN SELECT * FROM users;

这里的 table 字段值为users，表示查询只涉及到users这一张表。

当查询涉及到多表关联时，table 字段会依次列出关联的表。例如：

EXPLAIN SELECT users.name, orders.order_id FROM users JOIN orders ON users.user_id = orders.user_id;

在这个查询中，table 字段会先显示users表，然后显示orders表，表明查询是从users表和orders表中获取数据，并通过user_id进行关联。

如果查询中使用了子查询，并且子查询在 FROM 子句中，那么 table 字段会显示为<derivedN>的形式，其中 N 是子查询的 id 。例如：

EXPLAIN SELECT * FROM (SELECT id, name FROM users WHERE age > 20) AS derived_table;

这里的 table 字段为<derived1>，表示查询使用了 id 为 1 的子查询生成的派生表。

在联合查询（UNION）中，如果是获取 UNION 结果的查询，table 字段会显示为<unionM,N>的形式，其中 M 和 N 是参与 UNION 的 SELECT 语句的 id 。例如：

EXPLAIN SELECT subject.* FROM subject LEFT JOIN teacher ON subject.teacher_id = teacher.id
UNION
SELECT subject.* FROM subject RIGHT JOIN teacher ON subject.teacher_id = teacher.id;

对于获取 UNION 结果的查询，table 字段可能显示为<union1,2>，表示这个查询是从 id 为 1 和 2 的两个 SELECT 语句的结果中获取数据。

通过 table 字段，我们可以清晰地看到查询涉及的表结构，这对于分析多表关联查询的性能非常有帮助。如果发现某个表的关联顺序不合理，或者某个表的数据量过大导致查询性能下降，我们就可以针对性地调整查询语句，比如调整关联顺序、添加合适的索引等，以提高查询效率。

（四）type 字段：访问类型

type 字段是 EXPLAIN 结果中非常关键的一个字段，它表示 MySQL 在查询时使用的数据检索方法，也就是访问类型。不同的访问类型有着不同的性能表现，从最优到最差依次为：system > const > eq_ref > ref > range > index > ALL 。了解 type 字段对于优化 SQL 查询性能至关重要。

ALL - 全表扫描：ALL 表示全表扫描，意味着 MySQL 需要遍历整个表的所有数据行来查找符合条件的记录。这就像是在一个巨大的图书馆里，不借助任何索引（图书分类目录），直接逐本翻阅每一本书来寻找特定的信息，效率是非常低的。例如：

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

如果name字段上没有索引，执行这个查询时，type 字段的值就会是 ALL，MySQL 会扫描users表的每一行数据，去匹配name为John Doe的记录。在数据量较小的时候，全表扫描的影响可能不太明显，但当数据量逐渐增大，比如users表中有数百万条记录时，全表扫描会导致查询速度极慢，严重影响系统性能。因此，在实际应用中，我们要尽量避免出现 ALL 类型的访问，通过添加合适的索引来优化查询。

index - 索引全扫描：index 表示索引全扫描，它类似于 ALL，也是扫描整个表，但不同的是，它是按照索引的顺序来扫描索引树，而不是直接扫描数据行。这就好比在图书馆里，虽然还是要查阅所有的图书，但我们是按照图书分类目录（索引）的顺序来查找，相比全表扫描，效率会有所提高。例如：

EXPLAIN SELECT idx_column FROM large_table;

如果idx_column上有索引，执行这个查询时，type 字段可能为 index。这种方式适用于查询的字段都包含在索引中，也就是覆盖索引的情况，此时不需要回表查询数据行，直接从索引中就能获取所需数据，所以比 ALL 快一些。另外，当需要对数据按照索引列进行排序时，也可能会使用到索引全扫描。

range - 范围扫描：range 表示范围扫描，它是在指定的索引范围内进行查询，使用一个索引来选择行。这就像是在图书馆里，根据图书分类目录（索引）的某个范围来查找书籍，比如查找某一类图书编号在一定范围内的书籍。常见的范围查询操作符有BETWEEN、>、<、IN等。例如：

EXPLAIN SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;

在这个例子中，salary字段上有索引，执行查询时，type 字段为 range，MySQL 会在salary索引上查找在 5000 到 10000 这个范围内的记录，相比全表扫描，大大减少了扫描的数据量，提高了查询效率。

ref - 非唯一索引扫描：ref 表示非唯一索引扫描，它使用非唯一索引（普通索引、非唯一的外键索引）来查找数据，返回匹配某个单独值的所有行。这就像是在图书馆里，根据一个不太精确的分类线索（非唯一索引）来查找相关的书籍，可能会找到多本符合条件的书。例如：

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

如果department_id是一个普通索引，执行这个查询时，type 字段为 ref，MySQL 会通过department_id索引查找所有department_id为 5 的记录。在关联查询中，当使用非唯一索引进行连接时，也会出现 ref 类型的访问。

eq_ref - 唯一索引扫描：eq_ref 表示唯一索引扫描，它用于主键（PRIMARY KEY）或唯一索引（UNIQUE KEY）的等值查询，通常出现在多表 JOIN 操作中。对于每一行来自主表的数据，子表最多只会返回一条匹配记录。这就像是在图书馆里，根据一个独一无二的标识（唯一索引），能够准确无误地找到对应的那一本书。例如：

EXPLAIN SELECT e.emp_id, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.dept_id;

如果dept_id在departments表中是主键或唯一索引，执行这个查询时，type 字段为 eq_ref，MySQL 会通过dept_id唯一索引快速匹配到对应的部门记录，查询效率非常高。

const - 常量查找：const 表示常量查找，适用于唯一索引（PRIMARY KEY 或 UNIQUE）查询，并且查询条件是常量。MySQL 仅需查询一次，就能获取数据。这就像是在图书馆里，已知某本书的唯一编号（主键或唯一索引），可以直接定位到那本书，速度极快。例如：

EXPLAIN SELECT * FROM employees WHERE emp_id = 100;

如果emp_id是主键，执行这个查询时，type 字段为 const，MySQL 在编译阶段就确定了查询结果，直接通过主键找到对应的记录，查询速度非常快。

system - 系统表的特殊情况：system 是 const 的特例，当表只有一行记录（等于系统表）时出现。这就像是图书馆里只有一本书，直接拿起来看就行，不需要任何查找过程。例如，在一些系统表中，数据量非常小且固定，可能会出现 system 类型的访问。在实际业务中，system 类型很少见，一般可以忽略。

通过了解 type 字段的不同取值及其含义，我们可以判断查询的性能状况。在优化查询时，我们的目标是尽量将 type 优化到 ref 或更好级别，避免出现 ALL 和 index 类型，通过合理创建和使用索引，提高查询效率。

（五）possible_keys 和 key 字段：索引的潜在与实际应用

在 MySQL 的查询优化中，possible_keys 和 key 字段是与索引密切相关的两个重要字段，它们就像是索引使用的指示灯，能够帮助我们了解索引在查询中的潜在应用和实际使用情况。

possible_keys 字段显示了 MySQL 在查询时可能使用的索引列表。这些索引是根据查询条件和表结构来确定的，它们就像是一份候选名单，列出了所有有可能被用来优化查询的索引。例如：

EXPLAIN SELECT * FROM users WHERE age > 20 AND gender = 'Male';

如果users表在age和gender字段上分别有索引，那么在执行这个查询时，possible_keys 字段可能会显示这两个索引，表明这两个索引都有可能被用于优化查询。然而，possible_keys 中列出的索引并不一定会被实际使用，它只是提供了一种可能性。

key 字段则显示了 MySQL 实际选择使用的索引。它就像是最终确定的执行者，告诉我们在众多可能的索引中，MySQL 真正使用了哪个索引来执行查询。如果 key 字段的值为 NULL，则表示没有使用索引，查询可能会进行全表扫描。例如：

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

如果name字段上没有索引，执行这个查询时，key 字段为 NULL，MySQL 会进行全表扫描来查找符合条件的记录。而如果name字段上有索引，并且 MySQL 选择使用了这个索引，那么 key 字段就会显示该索引的名称。

通过对比 possible_keys 和 key 字段，我们可以判断索引的使用情况是否合理。如果 possible_keys 中列出了多个索引，但 key 字段显示只使用了其中一个，我们可以进一步分析为什么其他索引没有被使用，是否可以通过调整查询条件或者创建更合适的索引来提高查询性能。例如，如果查询条件中同时包含了age和gender字段，但 MySQL 只使用了age字段上的索引，我们可以考虑创建一个包含age和gender字段的联合索引，以提高查询效率。

当 key 字段为 NULL，而 possible_keys 中列出了索引时，说明索引未被使用，这可能是由于查询条件的写法不当、索引的选择性不高或者数据分布不均匀等原因导致的。此时，我们需要进一步分析原因，采取相应的优化措施，比如调整查询语句、重新设计索引或者对数据进行分析和优化。

（六）key_len 字段：索引使用字节数

key_len 字段在 MySQL 的查询分析中扮演着重要的角色，它表示索引中使用的字节数，通过这个字段，我们可以了解到 MySQL 在使用索引时的一些细节，就像是一把尺子，能够衡量索引使用的 “长度” 。

key_len 的计算与索引字段的类型、字符集以及是否允许 NULL 值等因素有关。

key_len 表示MySQL 在查询中实际使用的索引字段的字节长度，用于评估复合索引是否被完整利用。

值越小：说明索引使用越 “简短”（可能仅使用部分索引字段）。
值等于索引总长度：说明索引被完整使用（最佳状态）。

三、EXPLAIN 实战优化案例

（一）单表查询优化案例

假设我们有一个电商系统的商品表products，表结构如下：

CREATE TABLE products (

   product_id INT PRIMARY KEY AUTO_INCREMENT,

   product_name VARCHAR(255),

   category_id INT,

   price DECIMAL(10, 2),

   stock INT,

   description TEXT

);

表中存储了大量的商品信息，现在我们要查询价格在 100 到 200 之间且库存大于 50 的商品，初始的 SQL 语句如下：

SELECT * FROM products WHERE price BETWEEN 100 AND 200 AND stock > 50;

使用 EXPLAIN 分析这个查询语句的执行计划：

EXPLAIN SELECT * FROM products WHERE price BETWEEN 100 AND 200 AND stock > 50;

执行结果可能如下：

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

从执行计划中可以看出，type 字段为 ALL，即进行了全表扫描，这在数据量较大时效率会非常低。分析原因，是因为price和stock字段上没有索引，导致 MySQL 无法快速定位到符合条件的数据。

针对这个问题，我们可以创建一个联合索引来优化查询：

CREATE INDEX idx_price_stock ON products (price, stock);

再次使用 EXPLAIN 分析优化后的查询语句：

EXPLAIN SELECT * FROM products WHERE price BETWEEN 100 AND 200 AND stock > 50;

执行结果可能如下：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	products	range	idx_price_stock	idx_price_stock	9	NULL	100	Using where

优化后，type 字段变为 range，即进行了范围扫描，并且使用了我们创建的索引idx_price_stock，rows 字段也大幅减少，查询效率得到了显著提升。通过实际测试，在数据量较大的情况下，优化前的查询可能需要几秒钟甚至更长时间，而优化后的查询可以在几十毫秒内完成。

（二）多表联查优化案例

以一个电商系统中的订单查询为例，涉及三张表：orders（订单表）、users（用户表）和products（商品表）。表结构如下：

CREATE TABLE orders (

   order_id INT PRIMARY KEY AUTO_INCREMENT,

   user_id INT,

   product_id INT,

   order_date DATE,

   quantity INT,

   FOREIGN KEY (user_id) REFERENCES users(user_id),

   FOREIGN KEY (product_id) REFERENCES products(product_id)

);

CREATE TABLE users (

   user_id INT PRIMARY KEY AUTO_INCREMENT,

   user_name VARCHAR(255),

   email VARCHAR(255)

);

CREATE TABLE products (

   product_id INT PRIMARY KEY AUTO_INCREMENT,

   product_name VARCHAR(255),

   price DECIMAL(10, 2)

);

现在我们要查询每个用户的订单信息，包括用户名、商品名和订单日期，初始的 SQL 语句如下：

SELECT u.user_name, p.product_name, o.order_date
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN products p ON o.product_id = p.product_id;

使用 EXPLAIN 分析这个查询语句的执行计划：

EXPLAIN SELECT u.user_name, p.product_name, o.order_date
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN products p ON o.product_id = p.product_id;

执行结果可能如下：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	o	ALL	NULL	NULL	NULL	NULL	1000	Using temporary; Using filesort
1	SIMPLE	u	ALL	NULL	NULL	NULL	NULL	500	Using join buffer (Block Nested Loop)
1	SIMPLE	p	ALL	NULL	NULL	NULL	NULL	800	Using join buffer (Block Nested Loop)

从执行计划中可以看出，三张表的 type 字段都为 ALL，即都进行了全表扫描，并且使用了临时表和外部排序（Using temporary; Using filesort），这会导致查询性能很差。分析原因，是因为连接条件o.user_id = u.user_id和o.product_id = p.product_id上没有索引。

为了优化这个查询，我们在连接字段上创建索引：

CREATE INDEX idx_user_id ON orders(user_id);

CREATE INDEX idx_product_id ON orders(product_id);

再次使用 EXPLAIN 分析优化后的查询语句：

EXPLAIN SELECT u.user_name, p.product_name, o.order_date
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN products p ON o.product_id = p.product_id;

执行结果可能如下：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	o	index	idx_user_id,idx_product_id	idx_user_id	4	NULL	1000	Using index; Using temporary; Using filesort
1	SIMPLE	u	eq_ref	PRIMARY	PRIMARY	4	o.user_id	1
1	SIMPLE	p	eq_ref	PRIMARY	PRIMARY	4	o.product_id	1

优化后，users表和products表的 type 字段变为 eq_ref，即使用了唯一索引扫描，查询效率大大提高。虽然orders表的 type 字段还是 index，但相比之前的 ALL 已经有了很大改善。通过实际测试，优化前的查询在数据量较大时可能需要几十秒甚至更长时间，而优化后的查询可以在一秒内完成。

四、总结

在 MySQL 的世界里，EXPLAIN 是我们进行性能优化的得力助手。通过它，我们能够深入了解 SQL 查询的执行计划，洞察 MySQL 在执行查询时的每一个决策和步骤。从查询顺序的确定到访问类型的选择，从索引的潜在应用到实际使用情况，EXPLAIN 为我们提供了丰富而详细的信息，这些信息是我们优化 SQL 性能的关键依据。

在实际的 Java 开发中，SQL 查询的性能直接关系到系统的响应速度和用户体验。一个高效的 SQL 查询能够使系统快速响应用户的请求，提升用户满意度；而一个低效的查询则可能导致系统卡顿，甚至引发用户的流失。因此，我们要养成使用 EXPLAIN 分析 SQL 查询的好习惯，在开发过程中，对每一条重要的 SQL 语句都进行仔细的分析和优化，确保其能够高效地执行。