MySQL 索引优化（Explain执行计划）详细讲解

🤟致敬读者

🟩感谢阅读🟦笑口常开🟪生日快乐⬛早点睡觉

📘博主相关

🟧博主信息🟨博客首页🟫专栏推荐🟥活动信息

文章目录

MySQL 索引优化（Explain执行计划）详细讲解
- 一、为什么需要 EXPLAIN？
- 二、如何使用 EXPLAIN？
- 三、EXPLAIN 输出字段详解（核心！面试重点！）
- 四、索引优化核心原则（结合 EXPLAIN）
- 五、面试常见问题及回答思路
- 六、实战分析示例（模拟面试）
- 七、总结 & 面试准备要点

📃文章前言

🔷文章均为学习工作中整理的笔记。
🔶如有错误请指正，共同学习进步。

MySQL 索引优化（Explain执行计划）详细讲解

在这里插入图片描述

MySQL 的 EXPLAIN 执行计划是理解和优化 SQL 查询性能的核心工具，也是面试中数据库相关岗位（尤其是中高级）几乎必考的重点。掌握它不仅能让你在面试中游刃有余，更能提升实际工作中的数据库优化能力。

下面我将从基础概念、EXPLAIN 输出字段详解、关键性能指标解读、常见优化场景及面试要点等方面进行详细讲解：

一、为什么需要 EXPLAIN？

理解查询行为： 数据库是如何执行你的 SQL 语句的？它决定使用哪个索引（或者不用）？它如何连接表？它预估需要扫描多少行？
定位性能瓶颈： 查询慢在哪里？是全表扫描？是文件排序？是临时表过大？EXPLAIN 能提供关键线索。
验证索引效果： 你创建的索引真的被用到了吗？用到了哪个部分？是否是高效的使用方式？
优化查询和索引： 基于 EXPLAIN 的分析结果，调整 SQL 写法（如 WHERE 条件顺序、JOIN 顺序）、添加或修改索引、优化表结构等。

二、如何使用 EXPLAIN？

非常简单，在你的 SELECT 语句前加上 EXPLAIN 或 EXPLAIN FORMAT=JSON (获取更详细的结构化信息) 即可：

EXPLAIN SELECT * FROM users WHERE name = '张三' AND age > 30 ORDER BY created_at DESC;

或者在 MySQL Workbench 等图形化工具中，直接点击“解释”按钮。

三、EXPLAIN 输出字段详解（核心！面试重点！）

EXPLAIN 的输出是一个表格（或多行，每行代表查询计划中的一个操作），包含以下重要列：

id (查询标识符)
- 含义： 标识 SELECT 子查询的执行顺序或属于哪个大查询。
- 解读：
  - id 相同：执行顺序从上到下（如普通的 JOIN）。
  - id 不同：如果是子查询，id 值越大，优先级越高，越先执行（如 SELECT ... (SELECT ...)，内层 SELECT 的 id 更大）。
  - id 为 NULL：表示这是一个“结果行”，例如 UNION 的结果。
select_type (查询类型)
- 含义： 说明查询的类型。
- 常见类型及面试要点：
  - SIMPLE：最简单的 SELECT，不包含子查询或 UNION。
  - PRIMARY：查询中最外层的 SELECT（包含子查询时）。
  - SUBQUERY：在 SELECT 或 WHERE 列表中包含的子查询。
  - DERIVED：在 FROM 列表中包含的子查询（派生表）。面试点： DERIVED 通常意味着 MySQL 需要将子查询的结果物化成一个临时表，可能影响性能。优化方向：尝试用 JOIN 重写。
  - UNION： UNION 中的第二个或后续 SELECT。
  - UNION RESULT： UNION 操作的结果集。
  - DEPENDENT SUBQUERY / UNCACHEABLE SUBQUERY：依赖于外部查询的子查询（性能通常较差）。
table (访问的表)
- 含义： 显示这一步访问的是哪个表。
- 解读：
  - 表名：直接的表名。
  - <derivedN>：指向 id 为 N 的派生表结果。
  - <unionM,N>：指向 id 为 M 和 N 的 UNION 结果。
partitions (匹配的分区)
- 含义： 如果表是分区表，显示查询将访问哪些分区。非分区表为 NULL。
type (访问/连接类型 - 极其重要！性能关键指标！ 面试必问！)
- 含义： 表示 MySQL 决定如何查找表中的行。从最好到最差（性能降序）大致如下：
  - system：表只有一行（系统表）。是 const 的特例。
  - const：通过主键（Primary Key）或唯一索引（Unique Index）进行等值查询，最多只返回一行。速度极快。面试点： 这是最理想的情况之一。
  - eq_ref：在连接查询中，对于来自前表的每一行，使用主键或唯一索引在当前表中进行等值匹配（只找到一行）。常见于 PRIMARY KEY 或 UNIQUE NOT NULL 索引的等值连接。性能非常好。
  - ref：使用非唯一索引进行等值查询，可能返回多行。比 eq_ref 稍差，但仍然是高效的（如果返回行不多）。面试点： 最常用的高效访问方式之一。
  - ref_or_null：类似 ref，但额外包含了对 NULL 值的搜索。
  - range： 范围扫描。使用索引检索给定范围（BETWEEN, <, >, IN, LIKE 'prefix%' 等）的行。比全表扫描好，但需要关注扫描的行数（rows）。
  - index： 全索引扫描。扫描整个索引树（比全表扫描 ALL 快，因为索引通常比表数据小）。面试点：
    - 当查询所需列全部包含在索引中（覆盖索引），且不需要回表时，性能可以接受（Extra 列会显示 Using index）。
    - 否则，效率可能不高（需要排序或 Using filesort 时可能更糟）。
  - ALL： 全表扫描。没有使用索引，或者索引失效。性能最差！ 面试点： 看到 ALL 必须警惕！通常意味着需要优化（加索引、修改查询条件等）。在大表上尤其致命。
- 面试要点： 务必熟悉这个顺序！能清晰解释每种类型的含义和性能差异。面试官常问：“type 出现 ALL 意味着什么？如何优化？”，“ref 和 eq_ref 的区别是什么？”。
possible_keys (可能用到的索引)
- 含义： 显示查询可能使用哪些索引来查找行。基于 WHERE 子句、JOIN 条件等分析得出。
- 解读： 这里列出的索引不一定真正被使用。实际使用的索引在 key 列中。如果为 NULL，表示没有找到适用的索引。
key (实际使用的索引 - 重要！)
- 含义： 显示 MySQL 实际决定使用的索引。如果为 NULL，表示没有使用索引。
- 面试要点： 对比 possible_keys 和 key：
  - 如果 key 在 possible_keys 中：正常。
  - 如果 key 不在 possible_keys 中：可能使用了覆盖索引（即使没在 WHERE 中直接用到，但索引包含了 SELECT 需要的所有列），或者优化器基于成本选择了不同的索引。
  - 如果 possible_keys 有值而 key 为 NULL：意味着虽然有可用索引，但优化器基于成本（如小表扫描更快）或索引选择性差等原因决定不使用索引。需要分析原因！
key_len (使用的索引长度)
- 含义： 表示 MySQL 在索引中实际使用的字节数。可用于判断索引是否被充分利用（是否使用了索引的全部列或部分列）。
- 解读：
  - 计算规则：所有被使用的索引字段的长度之和。对于变长字段（如 VARCHAR），需要考虑字符集和是否允许 NULL（多1字节）。
  - 面试点： 如果 key_len 小于索引定义的长度，说明只使用了索引的最左前缀（复合索引的部分列）。这是理解最左前缀原则的关键指标！例如，索引 (a, b, c)，WHERE 条件只有 a=1 AND b=2，那么 key_len 就是 a 和 b 的长度，表明 c 没有被用到。
ref (与索引比较的列或常量)
- 含义： 显示使用 key 列指定的索引时，查找值所用到的列或常量。
- 常见值：
  - const：常量值（如 WHERE id = 10）。
  - func：使用函数的结果。
  - database_name.table_name.column_name：来自另一个表的列（常见于 JOIN）。
  - NULL：例如 index 扫描（全索引扫描）。
rows (预估需要扫描的行数 - 重要！性能指标！)
- 含义： MySQL 优化器预估执行该查询操作需要扫描的行数（不是精确值，基于统计信息）。对于 InnoDB，这是一个估计值。
- 解读： 这个值非常重要！它直接反映了查询的成本。值越小越好。结合 type 看：
  - type=ALL 且 rows 很大：性能灾难！
  - type=index 且 rows 很大：全索引扫描，也可能很慢，要看数据量和 Extra。
  - type=const/eq_ref/ref 且 rows=1：非常高效。
- 面试点： 理解 rows 是预估的，实际执行可能不同。关注其数量级（几十、几万、几百万？）。
filtered (按条件过滤后剩余行的百分比 - MySQL 5.7+ 重要！)
- 含义： 表示存储引擎返回的数据在服务层经过 WHERE 条件或其他过滤后，预估剩余行数的百分比。值在 0 到 100 之间。100 表示没有过滤。
- 解读： 对于单表查询，意义不大（rows * filtered / 100 可以预估最终返回的行数）。对于 JOIN 查询极其重要：
  - 它表示前一个表返回的行，在连接当前表后，预估能有多少比例的行满足 JOIN 条件或单表 WHERE 条件。
  - 例如，前表 rows=1000, filtered=10%，则预估需要连接当前表 1000 * 10% = 100 次。filtered 越低，意味着连接次数越少，JOIN 效率可能越高。
- 面试点： 理解 filtered 在 JOIN 优化中的意义，特别是评估 JOIN 顺序是否合理时。
Extra (额外信息 - 极其重要！包含关键性能线索！ 面试必问！)
- 含义： 包含 MySQL 解决查询的额外细节信息。这里常常藏着性能问题的答案。
- 常见值及优化方向（面试高频考点）：
  - Using index： 覆盖索引（Covering Index）。查询的列全部包含在使用的索引中，无需回表查询数据行。这是非常理想的情况！ 面试点： 解释什么是覆盖索引及其优势（减少I/O，提高速度）。
  - Using where：在存储引擎返回行之后，服务层还需要应用 WHERE 条件进行过滤。说明索引可能没有完全覆盖 WHERE 条件，或者索引被部分使用。不一定坏，但结合 type 和 rows 看。
  - Using temporary： 使用了临时表来保存中间结果。常见于 GROUP BY 或 ORDER BY 的列不属于驱动表且没有合适索引，或者 DISTINCT、UNION。通常需要优化！ 面试点： 为什么临时表不好？如何避免（优化 GROUP BY/ORDER BY 的索引）？
  - Using filesort： 使用了文件排序。无法利用索引完成排序（ORDER BY, GROUP BY），需要在内存或磁盘进行额外排序。通常需要优化！ 面试点： filesort 为什么慢？如何避免（为 ORDER BY/GROUP BY 创建合适的索引）？注意：有时优化器会选择 filesort 如果它认为更快（比如行数很少）。
  - Using join buffer (Block Nested Loop) / Using join buffer (Batched Key Access)：使用了连接缓冲区。常见于被驱动表（第二个表）没有有效索引可用时，需要将驱动表的一部分行读入 Join Buffer，然后批量与被驱动表匹配。效率较低！ 优化方向： 为被驱动表的连接字段添加索引。
  - Impossible WHERE： WHERE 子句永远为 false，查不到任何行（如 WHERE 1=0）。
  - Select tables optimized away：使用某些聚合函数（如 MIN()/MAX()）访问仅包含这些值的索引时，优化器可以直接从索引中取值，无需访问表。
  - Distinct / Not exists：优化了 DISTINCT 或 NOT EXISTS 操作。
  - Range checked for each record (index map: N)： MySQL 没有找到好的索引可用，但发现在前面的表返回行后，可能可以根据该行的值来使用某个索引进行范围扫描（N 是位图标识哪个索引可能被用）。效率不高。

四、索引优化核心原则（结合 EXPLAIN）

最左前缀原则 (Leftmost Prefixing):
- 对于复合索引 (col1, col2, col3)，索引可以被用于：
  - 只包含 col1 的查询 (WHERE col1=val)
  - 包含 col1 和 col2 的查询 (WHERE col1=val1 AND col2=val2)
  - 包含 col1, col2 和 col3 的查询 (WHERE col1=val1 AND col2=val2 AND col3=val3)
- 不能跳过左边列直接使用右边列 (WHERE col2=val2 AND col3=val3 无法有效使用该索引)。
- EXPLAIN 体现： key_len 会显示实际使用了索引的前几列的长度。
- 面试必考！ 必须能清晰解释并举例。
避免索引失效（导致全表扫描 ALL 或低效 index）：
- 在索引列上做操作： 函数、计算、类型转换（隐式或显式）。如 WHERE YEAR(create_date) = 2023, WHERE amount * 2 > 100, WHERE id = '123' (id 是 INT)。
- 使用 != 或 <>： 通常会导致索引失效（除非覆盖索引）。
- 使用 NOT IN, NOT LIKE： 通常会导致索引失效。
- OR 连接非索引列： 如果 OR 的条件中有一个列没有索引，整个索引可能失效。
- LIKE 以通配符 % 开头： LIKE '%keyword' 无法利用索引（LIKE 'keyword%' 可以）。
- 字符串不加单引号（隐式类型转换）： 如 WHERE name = 123 (name 是 VARCHAR)。
- 复合索引未遵循最左前缀原则。
- 优化器认为全表扫描更快： 当表很小，或者索引选择性极低时（如性别列）。
- EXPLAIN 体现： type=ALL 或 type=index + 高 rows + Using where。
覆盖索引 (Covering Index)：
- 创建包含查询所需所有列（SELECT, WHERE, ORDER BY, GROUP BY）的索引。
- 优势： 避免回表（访问数据行），极大提升速度。体现在 EXPLAIN 的 Extra: Using index。
- 权衡： 索引列越多，索引维护成本（插入/更新/删除）越高，占用空间越大。需平衡查询性能与写入性能/空间开销。
为排序和分组创建索引：
- ORDER BY 和 GROUP BY 子句的列顺序同样需要遵循最左前缀原则。
- 如果排序方向不一致 (ORDER BY col1 ASC, col2 DESC)，可能需要特殊索引或无法充分利用。
- EXPLAIN 体现： 避免 Extra: Using filesort 和 Using temporary。理想情况是 Using index 完成排序/分组。
JOIN 优化：
- 确保 ON/USING 子句中的列有索引： 通常在被驱动表（第二个及以后的表）的连接字段上建索引。体现在 type=ref/eq_ref。
- 小表驱动大表： 优化器通常会尝试选择小表作为驱动表（第一个表）。手动指定时可以用 STRAIGHT_JOIN（谨慎使用）。
- 关注 filtered 列： 高 filtered 值（接近100%）可能意味着 JOIN 条件选择性差或索引不佳。
- 减少 Using join buffer： 通过为被驱动表添加有效索引。

五、面试常见问题及回答思路

EXPLAIN 是做什么用的？

答：EXPLAIN 是 MySQL 提供的用于分析 SQL 查询执行计划的工具。它显示了 MySQL 优化器打算如何执行查询，包括是否使用索引、使用哪个索引、表的连接顺序和方式、预估扫描行数等关键信息。主要目的是帮助我们理解查询行为、定位性能瓶颈、验证索引效果和指导优化。
type 字段有哪些值？性能从好到坏排序是怎样的？

答：type 表示访问表的方式，性能从最优到最差大致是：system > const > eq_ref > ref > ref_or_null > range > index > ALL。const/eq_ref/ref/range 通常表示使用了索引且高效；index 是全索引扫描，有时可接受（覆盖索引时）；ALL 是全表扫描，性能最差，必须优化。
看到 type=ALL 怎么优化？
答：ALL 表示全表扫描，是性能瓶颈的信号。优化方向包括：
- 检查 WHERE 条件涉及的列，尝试添加合适的索引（单列或复合索引，注意最左前缀）。
- 检查查询条件是否导致索引失效（如函数操作、类型转换、LIKE '%xx'、违反最左前缀）。
- 如果表很小，ALL 可能比用索引快，但需确认表大小。
- 分析是否真的需要所有列，避免 SELECT *，考虑覆盖索引。
Extra 列里 Using filesort 和 Using temporary 是什么意思？怎么优化？
答：
- Using filesort：表示 MySQL 无法利用索引完成排序（ORDER BY/GROUP BY），需要在内存或磁盘进行额外排序。优化： 为排序/分组的字段创建合适的索引（顺序遵循最左前缀）。
- Using temporary：表示 MySQL 创建了临时表来处理查询（常见于 GROUP BY, DISTINCT, UNION）。优化： 尝试优化 GROUP BY 的列使其有索引；避免不必要的 DISTINCT/排序；确保 UNION 子句有索引。临时表消耗内存/磁盘，影响性能。
什么是覆盖索引？EXPLAIN 如何体现？有什么好处？
答：
- 覆盖索引： 指一个索引包含了查询所需的所有列（SELECT, WHERE, ORDER BY, GROUP BY 涉及的列）。
- EXPLAIN 体现： Extra 列会显示 Using index。
- 好处： 最大的优势是避免回表（无需访问数据行）。直接从索引中获取数据，减少 I/O 操作，显著提高查询速度。是优化查询的高效手段之一。
什么是索引的最左前缀原则？EXPLAIN 如何验证？
答：
- 最左前缀原则： 对于复合索引 (col1, col2, col3)，查询条件必须包含索引定义中的最左边的连续列才能有效利用该索引。例如，条件 col1=val 或 col1=val1 AND col2=val2 可以利用索引；但 col2=val2 或 col2=val2 AND col3=val3 无法有效利用（除非跳过扫描，但效率不高）。
- EXPLAIN 验证： 主要看 key_len 列。key_len 的值代表了实际使用到的索引列的总字节数。如果 key_len 小于整个复合索引的长度，说明只使用了索引的一部分（最左前缀）。
possible_keys 有值，key 为 NULL 是什么原因？
答：这表示优化器分析后认为有可用的索引 (possible_keys)，但最终决定不使用任何索引 (key=NULL)。常见原因：
- 表很小： 全表扫描比走索引更快（如只有几行）。
- 索引选择性差： 索引列的值重复率极高（如状态标志位），走索引后仍需回表扫描大量行，不如直接全表扫。
- 覆盖索引不成立且需要回表： 虽然 WHERE 条件有索引，但 SELECT 的列不在索引中，需要大量回表操作，优化器计算成本后认为全表扫描更优。
- 查询使用了 FORCE INDEX 之外的提示但优化器未采纳。
  需要结合 rows 预估值和表大小具体分析。
filtered 字段有什么用？

答：filtered (MySQL 5.7+) 表示存储引擎返回的行，在服务层经过 WHERE 条件过滤后，预估剩余行的百分比。对于单表查询： 可以用来预估最终结果行数 (rows * filtered / 100)。对于 JOIN 查询（更重要）： 它表示前一个表的每一行，在连接当前表时，预估有多少比例的行能满足连接条件或单表 WHERE 条件。filtered 值越低（接近0），意味着前表行连接当前表的次数越少，JOIN 效率可能越高。优化 JOIN 顺序时，驱动表（第一个表）应尽量选择 filtered 小的。

六、实战分析示例（模拟面试）

查询：

EXPLAIN SELECT o.order_id, c.customer_name, p.product_name, oi.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-01-31'
AND c.country = 'China'
ORDER BY o.order_date DESC;

假设 EXPLAIN 结果关键部分（简化）：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	o	range	idx_date	idx_date	4	NULL	1000	100.00	Using where; Using filesort
1	SIMPLE	c	eq_ref	PRIMARY	PRIMARY	4	db.o.customer_id	1	10.00	Using where
1	SIMPLE	oi	ref	idx_order	idx_order	4	db.o.order_id	5	100.00	NULL
1	SIMPLE	p	eq_ref	PRIMARY	PRIMARY	4	db.oi.product_id	1	100.00	NULL

面试官：分析一下这个查询的执行计划和潜在性能问题？

回答思路：

驱动表： 第一个表是 o (orders)。优化器选择它作为驱动表，因为 WHERE o.order_date BETWEEN ... 使用了范围查询 (type=range)，预估扫描 rows=1000 行。
orders 表 (o):
- type=range：使用了索引 idx_date 进行范围扫描（好）。
- Extra=Using where; Using filesort：Using where 可能表示索引未完全覆盖 WHERE 条件（比如 order_date 在索引中，但可能还有其他条件？这里只有日期）。Using filesort 是问题！ 因为最终的 ORDER BY o.order_date DESC 无法利用 idx_date 的排序（因为是范围扫描后结果集，且可能需要与其他表连接后再排序），需要额外文件排序。优化建议： 尝试调整索引或查询，看是否能利用索引消除排序。
customers 表 (c):
- type=eq_ref：使用主键连接，对于驱动表 o 的每一行 customer_id，精确查找一行。效率很高。
- filtered=10.00%：表示连接后，只有大约 10% 的行满足 c.country='China' 条件。这个选择性还不错，意味着驱动表 o 的 1000 行，大约只需要连接 c 表 100 次 (1000 * 10%)。
order_items 表 (oi):
- type=ref：使用非唯一索引 idx_order (可能在 order_id 上) 进行连接。预估每个订单有 5 个订单项 (rows=5)。
products 表 (p):
- type=eq_ref：使用主键连接，效率很高。
主要性能风险点：
- o 表的文件排序 (Using filesort)： 如果满足日期条件的订单很多（rows=1000 是预估，实际可能更多），文件排序可能成为瓶颈，尤其是在内存不足需落盘时。
- 连接行数放大： 驱动表 o 扫描 1000 行 -> 连接 c 约 100 次 -> 连接 oi 约 100 * 5 = 500 次 -> 连接 p 500 次。最终需要处理的行数可能达到 500 行（结果集），但中间过程需要处理连接。
优化建议：
- 解决排序：
  - 如果 o.order_date 是驱动条件且需要排序，考虑能否让 idx_date 包含排序方向（但范围查询本身可能破坏顺序）。
  - 尝试创建一个包含 (order_date, customer_id) 的索引，order_date 用于范围查找和排序，customer_id 用于连接 c 表。看 EXPLAIN 是否能消除 filesort (Extra 去掉 Using filesort) 并保持高效连接。
- 覆盖索引检查： 检查 SELECT 的列是否被现有索引覆盖，减少回表（当前计划中没有 Using index，说明有回表）。
- c.country 过滤： filtered=10% 不错，但如果在 customers 表上有 (country, customer_id) 的索引，理论上可以让 eq_ref 连接更顺畅（虽然主键连接已最优），或者如果 country 过滤性更强，考虑调整 JOIN 顺序（但优化器通常已经做了最优选择，除非用 STRAIGHT_JOIN 强制）。

七、总结 & 面试准备要点

理解每个 EXPLAIN 字段的含义： 特别是 type, key, rows, filtered, Extra。
掌握性能关键指标： type=ALL/index 差，type=const/eq_ref/ref/range 好；rows 大差小好；Extra 中的 Using filesort, Using temporary, Using join buffer 通常是警告信号；Using index 是好的信号。
深刻理解索引原理： 最左前缀原则（看 key_len）、覆盖索引（看 Using index）、索引失效场景。
理解 JOIN 优化： 驱动表选择、被驱动表索引 (ref/eq_ref)、filtered 的意义。
能解读 EXPLAIN 输出： 按 id 顺序理解执行步骤，分析每个步骤的访问方式和潜在问题。
能提出优化建议： 根据 EXPLAIN 结果，说出可能的优化方向（加索引、改索引、改写查询、调整 JOIN 顺序等）。
动手实践： 在自己环境或在线 SQL 平台多写 SQL，多用 EXPLAIN 分析不同写法和索引的影响，加深理解。