mysql 执行计划面试：从原理到实战的深度解析

在当前的互联网技术面试中，mysql 执行计划面试已成为考察后端工程师数据库功底的核心环节。无论是初级开发还是资深架构师，如何准确解读EXPLAIN输出结果，并据此进行SQL调优，往往是决定面试成败的关键。本文旨在通过详尽的知识点梳理、实战案例演示以及高频面试题解析，帮助读者构建完整的MySQL执行计划知识体系，彻底解决面试中的相关难题。

① 为什么关注执行计划？

执行计划是SQL在MySQL内部执行的“路线图”。不看执行计划调优，如同盲人摸象。它是定位性能瓶颈的唯一真理。

② 核心指标解读

type、key、rows、Extra是四大核心字段。掌握它们，你就能一眼看穿SQL的性能优劣。

③ 索引失效场景

最左前缀法则、函数运算、隐式类型转换...这些是面试必问，也是线上故障的高发区。

EXPLAIN详解：逐字段剖析

在面试中，面试官通常会要求你在一条SQL语句前加上EXPLAIN，然后让你解释每一列的含义。以下是对关键字段的深度解读：

1. type（访问类型）

type显示了连接使用了何种类型，从最好的到最差依次是：

system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现。
const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in 之类的查询。这种范围扫描索引扫描比全表扫描好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
index：Full Index Scan，index与ALL区别为index类型只遍历所有的索引，而不遍历数据。All与Index区别为All需要遍历全表，Index遍历索引。
all：Full Table Scan，将遍历全表以找到匹配的行。

面试技巧：在mysql 执行计划面试中，如果看到type为ALL，必须指出这是性能杀手，需要优化。

2. key（实际使用的索引）

显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视key列中的索引，在查询中使用force index、ignore index。

3. rows（扫描行数）

估计为了找到所需的行而要读取的行数。这只是一个预估值，不准确。但它是一个重要的参考指标，越小越好。

4. Extra（额外信息）

Value	含义	是否良好
Using index	覆盖索引扫描，查询未读数据表中的列信息，直接从索引中获取数据	✅ 优秀
Using where	查找使用了where过滤	⚠️ 正常
Using filesort	需要额外的排序操作，不能通过索引顺序达到排序效果	❌ 较差
Using temporary	使用了临时表保存中间结果，常用于GROUP BY、ORDER BY	❌ 较差

索引优化：面试中的高频陷阱

索引是MySQL执行计划优化的核心。面试官常会给出一个看似正常的SQL，问你是否会走索引。以下是几个经典的索引失效场景：

最左前缀法则

联合索引(a,b,c)，查询条件必须包含a，否则索引失效。例如 WHERE b=1 不会走索引，但 WHERE a=1 AND b=1 会走。

函数运算

对索引列进行函数操作会导致索引失效。例如 WHERE YEAR(create_time) = 2023。应改为范围查询 WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'。

隐式类型转换

如果字段是varchar类型，查询时传入数字，会导致索引失效。例如 WHERE name = 123 (name是varchar)。必须使用 WHERE name = '123'。

模糊查询

以%开头的模糊查询 LIKE '%abc' 不走索引。而 LIKE 'abc%' 会走索引。

示例代码对比

-- ❌ 错误示例：导致全表扫描
SELECT  FROM users WHERE YEAR(birth_date) = 1990;
-- ✅ 正确示例：利用索引范围扫描
SELECT  FROM users
WHERE birth_date >= '1990-01-01'
  AND birth_date < '1991-01-01';

Join策略：Nested Loop与优化

在mysql 执行计划面试中，关于Join的考察通常涉及驱动表的选择和Join算法。MySQL默认使用Nested Loop Join（嵌套循环连接）。

1. Simple Nested-Loop Join (SNLJ)

最简单的Join方式。对于驱动表中的每一行，都在被驱动表中扫描所有行。性能最差，通常只在没有索引时使用。

2. Index Nested-Loop Join (INLJ)

如果被驱动表有索引，MySQL会使用索引来查找匹配的行。这是最常见的优化方式。

面试考点：谁做驱动表？
原则：小表驱动大表。即Join条件中，数据量小的表作为驱动表，数据量大的表作为被驱动表。因为被驱动表的索引查找次数取决于驱动表的行数。

3. Block Nested-Loop Join (BNLJ)

当被驱动表没有索引时，MySQL会使用BNLJ。它将驱动表的部分数据放入Join Buffer中，然后一次性扫描被驱动表。如果Join Buffer设置得当，可以显著减少磁盘IO。

BNLJ 执行流程

1. 从驱动表中读取一行。
2. 放入Join Buffer。
3. 重复直到Buffer满或驱动表读完。
4. 扫描被驱动表，与Buffer中的数据比对。

INLJ 执行流程

1. 从驱动表读取一行。
2. 拿着这行的Join字段，去被驱动表的索引中查找。
3. 找到后返回整行数据。
4. 重复直到驱动表读完。

mysql 执行计划面试：高频真题深度解答

以下是网友和求职者最关心的5个面试问题，我们逐一进行深度解析。

问题一：如何定位慢SQL？

解答：首先开启慢查询日志（slow_query_log），设置阈值（long_query_time）。使用mysqldumpslow分析日志，或者直接使用pt-query-digest工具。找到慢SQL后，使用EXPLAIN分析执行计划，关注type、key、rows和Extra字段。

问题二：什么是覆盖索引？

解答：覆盖索引（Covering Index）是指查询的列恰好是索引的一部分，或者查询可以通过索引直接获取所有需要的数据，而不需要回表查询主键。在EXPLAIN结果中，Extra字段显示“Using index”即表示使用了覆盖索引。这能极大提升查询性能。

问题三：ORDER BY何时会Filesort？

解答：当ORDER BY的字段没有索引，或者索引顺序与ORDER BY顺序不一致，或者进行了多表Join时，很容易产生Filesort。优化方法包括：确保ORDER BY字段符合最左前缀法则，或者使用覆盖索引避免回表。

问题四：GROUP BY如何优化？

解答：GROUP BY通常会产生临时表（Using temporary）。优化思路与ORDER BY类似，尽量利用索引进行分组。在MySQL 5.7+中，优化器可能会选择Index Sort代替Filesort，如果索引顺序恰好满足分组需求。

问题五：Count()、Count(1)和Count(字段)有什么区别？

解答：在InnoDB引擎中，Count()和Count(1)性能基本一致，都会扫描主键索引。而Count(字段)如果该字段为NULL，则不会计数，且需要扫描二级索引，性能通常较差。面试中应推荐统一使用Count()。