MySQL Explain 详解：索引优化与 JOIN 执行计划实战

MySQL 查询优化器工作原理与 EXPLAIN 核心字段

MySQL 查询优化器的主要目标是尽可能使用索引，并利用最严格的索引来消除尽可能多的数据行。其最终目标是提交 SELECT 语句查找数据行。EXPLAIN 用于描述 MySQL 如何执行查询操作，以及成功返回结果集需要执行的行数。以下是对关键输出字段的解析：

第一部分：查询序列与类型 (id, select_type)

• id: 执行顺序标识。id 值越大优先级越高，越先被执行；若 id 相同，则按从上至下的顺序执行。
• select_type: 查询类型。
  • SIMPLE: 简单查询，不使用 UNION 及子查询。
  • PRIMARY: 最外层的 SELECT 查询。
  • UNION/DEPENDENT UNION: UNION 中的第二个或随后的查询，分别指不依赖/依赖于外部查询结果集。
  • SUBQUERY/DEPENDENT SUBQUERY: 子查询中的第一个 SELECT，分别指不依赖/依赖于外部查询结果集。
  • DERIVED: FROM 子句中的子查询，MySQL 会递归执行并将结果放入临时表。

第二部分：连接类型与索引使用 (table, type, key)

type 是最重要的字段之一，显示连接使用的类型，按最优到最差排序。常见的类型包括：

• system: 表仅有一行（系统表），是 const 的特例。
• const: 用于用常数值比较 PRIMARY KEY 或 UNIQUE 索引的情况。
• eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录匹配（多表连接中常见）。
• ref: 非唯一索引扫描，返回匹配某个单独值的所有行（可能找到多行）。
• range: 检索给定范围的行，使用一个索引来选择行。常见于 =, <>, >, >=, <, <=, BETWEEN, IN 等操作符。
• index: 全索引扫描，比 ALL 好，避免了排序，但开销仍较大。
• ALL: 全表扫描，性能最差的情况。

key 表示 MySQL 实际使用的索引。如果为 NULL，则没有使用索引。虽然很少情况下 MySQL 会选择优化不足的索引，但你可以通过 USE INDEX (indexname) 强制使用索引，或用 IGNORE INDEX(indexname) 忽略索引。

第三部分：其他重要指标 (rows, extra)

• rows: MySQL 认为必须检查的用来返回请求数据的行数。
• possible_keys: 指出 MySQL 能在此表中使用的索引。如果为空，说明没有可用索引。
• key_len: 使用的索引长度。在不损失精度的情况下，越短越好，表明索引利用率高。
• ref: 显示索引的哪一列或被常量使用了。

extra 中出现特定项通常意味着性能问题：

• Using filesort: 表示 MySQL 会对结果使用外部索引排序（文件或内存），无法利用索引完成排序，需重点优化。
• Using temporary: 使用临时表保存中间结果，常见于 GROUP BY 和 ORDER BY，应尽量避免。

单表查询优化实战：联合索引的使用陷阱

通过以下案例演示如何通过 EXPLAIN 发现索引失效问题并进行修正。

第1步：创建测试表并插入数据

CREATE TABLE IF NOT EXISTS `article` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `author_id` int(10) unsigned NOT NULL,
  `category_id` int(10) unsigned NOT NULL,
  `views` int(10) unsigned NOT NULL,
  `comments` int(10) unsigned NOT NULL,
  `title` varbinary(255) NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `article`(
  `author_id`, `category_id`, `views`, `comments`, `title`, `content`
) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3');

第2步：分析初始查询执行计划

需求：查询 category_id 为 1 且 comments 大于 1 的情况下，views 最多的 article_id。

EXPLAIN SELECT author_id
FROM `article`
WHERE category_id = 1 AND comments > 1
ORDER BY views DESC
LIMIT 1\G

观察结果：type: ALL（全表扫描），Extra: Using where; Using filesort。这是性能最差的情况，必须进行优化。

第3步：尝试建立联合索引及分析失效原因

直觉上，我们在查询条件字段上加联合索引：ALTER TABLE article ADD INDEX x (category_id, comments, views);

再次运行 EXPLAIN，结果如下：

... ... ...
type: range
key: x
Extra: Using where; Using filesort

虽然 type 变为了 range（有进步），但 Using filesort 依然存在。
原因分析：B-Tree 索引的工作原理是先排序 category_id，再排序 comments，最后排序 views。当 comments > 1 是一个范围查询（Range）时，MySQL 无法利用该索引之后的字段（即 views）进行排序，导致后面的索引失效，仍需进行文件排序。

第4步：修正索引结构

为解决此问题，我们需要移除范围查询中间的字段，将有序字段放在后面。删除旧索引并新建：

DROP INDEX x ON article;
ALTER TABLE `article` ADD INDEX y (category_id, views);

再次查询，结果理想：type: ref，Extra: Using where。Filesort 消失，优化成功。

多表连接 (JOIN) 与索引建立策略

在多表查询中，理解驱动表与被驱动表的关系至关重要。我们通过三个表 class、book、phone 进行演示，均含有字段 card。

第1步：LEFT JOIN 优化策略

LEFT JOIN 的特性是左表（驱动表）一定都有数据，右表（被驱动表）可能有 null。因此，关键在于右表的索引。

explain select * from class left join book on class.card = book.card\G

初始状态两表均为 ALL。在右表 book 建立索引后：

ALTER TABLE `book` ADD INDEX y (card);

查看 EXPLAIN，第二行（book表）type 变为 ref，rows 大幅减少。结论：LEFT JOIN 应在右表建立索引。

第2步：RIGHT JOIN 优化策略

RIGHT JOIN 刚好相反，右表是驱动表（一定有数据），左表是被驱动表。

explain select * from class right join book on class.card = book.card;

此时若在左表 class 建立索引（而非右表），可使左表的查询类型由 ALL 变为 ref，从而优化整体性能。结论：RIGHT JOIN 应在左表建立索引。

第3步：INNER JOIN 优化策略

INNER JOIN 双方都是可选的，MySQL 优化器通常会选择数据量较小的表作为驱动表，但无论哪种情况，本质上都需要对连接字段（Join Key）建立索引以提高查找速度。在实际操作中，若发现某侧仍是全表扫描，通常需要在该侧字段添加索引。

第4步：三表连接示例

explain select * from class 
left join book on class.card=book.card 
left join phone on book.card = phone.card;

若正确建立了 book(card) 和 phone(card) 索引，EXPLAIN 结果将显示后两行均为 ref，且总扫描行数显著降低，证明索引链式生效。

总结

EXPLAIN 是 SQL 优化的利器。掌握各字段含义，特别是 type 的性能等级和 extra 中的警告信息，能帮助开发者快速定位瓶颈。记住两个核心原则：一是联合索引要注意范围查询导致的后续索引失效；二是 JOIN 操作中，始终为被驱动表（Right of Left Join, Left of Right Join）建立连接字段的索引，以实现最高效的查询。

以上就是 MySQL Explain 深入解析与索引优化实战的详细内容，更多关于 MySQL 查询优化的资料请关注本站其它相关文章！