SQL 优化与执行计划面试指南

❓ 面试官：如果线上发现一条慢查询，你的排查和优化思路是什么？

频率：🔥🔥🔥🔥🔥

💡 一句话总结（先抛结论）： 我会分四步走：1. 开启慢查询日志找到慢 SQL；2. 用 EXPLAIN 分析执行计划，看是否走索引；3. 检查 SQL 语句本身是否有优化空间（如避免 SELECT *、避免函数等）；4. 如果 SQL 没问题，考虑是否是表数据量过大，需要归档、分库分表或引入缓存。

📝 详细原理解析：

定位慢 SQL：
- 临时排查：通过 show processlist 查看当前正在执行的慢语句。
- 系统排查：开启 slow_query_log，设置 long_query_time（通常设置为 1-2 秒），结合 mysqldumpslow 工具分析日志。
EXPLAIN 分析（核心）：
- 查看 type 字段：确保访问类型至少达到 range（范围扫描），最好是 ref（非唯一索引）或 eq_ref（主键/唯一索引），绝不能是 ALL（全表扫描）。
- 查看 key 字段：确认是否使用了预期的索引。
- 查看 rows 字段：预估扫描的行数，越小越好。
- 查看 Extra 字段：警惕 Using filesort（文件排序，需优化）和 Using temporary（临时表，严重影响性能），尽量达到 Using index（覆盖索引）。
SQL 级优化：
- 只查询需要的列，杜绝 SELECT *（减少网络带宽、IO 以及方便触发覆盖索引）。
- 避免在 WHERE 条件中对字段进行函数操作、数学运算或隐式类型转换，否则索引失效。
- 避免前导模糊查询 LIKE '%xxx'。
- IN 的元素不要太多，如果是连续的数字可以用 BETWEEN 代替。
表结构与架构优化：
- 数据量极大（单表超千万）：考虑分库分表。
- 聚合查询慢：考虑将结果提前计算并存入 Redis，或引入 ES、ClickHouse 解决复杂查询。

🌟 面试加分项（实战经验）： 有时候我们会发现：SQL 明明有索引，测试环境跑得飞快，生产环境却偶尔极慢。这通常不是 SQL 的问题，而是偶发的系统资源争抢或锁等待。比如：

后台正在执行大批量的 UPDATE，引发了大量的锁等待或死锁。
缓冲池（Buffer Pool）命中率下降，或者脏页刷盘（Flush）导致 IO 飙高。这时候光靠 EXPLAIN 是没用的，还需要结合服务器 IO 监控和 show engine innodb status 综合排查。

❓ 面试官：深分页问题（LIMIT 1000000, 10）为什么慢？怎么优化？

频率：🔥🔥🔥🔥

💡 一句话总结（先抛结论）： 深分页慢的原因是 MySQL 会先把前 1000010 条记录都查出来（并回表），然后丢弃前 1000000 条，只返回最后 10 条。这期间产生了海量的无用回表操作。优化方法主要有延迟关联（子查询法）和游标法（基于上一次的最大 ID）。

📝 详细原理解析： 深分页的典型 SQL：

sql

SELECT * FROM orders ORDER BY create_time LIMIT 1000000, 10;

优化方案 1：延迟关联（覆盖索引 + JOIN / 子查询） 思路：先利用覆盖索引把满足条件的 ID 查出来，由于不查完整数据，这一步极快；然后再用这些 ID 去跟原表 JOIN，获取完整数据。

sql

-- 优化后：
SELECT o.* FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY create_time LIMIT 1000000, 10
) temp ON o.id = temp.id;

优化方案 2：游标法（基于主键/时间戳游标） 思路：如果主键是连续递增的，且不需要跳页访问（只能上一页、下一页），可以直接记住上一页的最大 ID，下一次查询直接从该 ID 往后取。

sql

-- 优化后（假设上一页最大 ID 是 1000000）：
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

🌟 面试加分项（实战经验）： 在真实的 C 端业务中（比如淘宝订单列表、朋友圈），通常不允许用户直接跳到第 10000 页。所以我们在产品设计上就会规避深分页，比如只能"滑动加载更多"（使用游标法），或者限制最多只能看前 100 页。如果非要支持海量数据的跳页，我们通常会将数据同步到 Elasticsearch，由 ES 来负责复杂分页。

❓ 面试官：如何优化 JOIN 操作？什么是小表驱动大表？

频率：🔥🔥🔥

💡 一句话总结（先抛结论）： 优化 JOIN 的核心原则是小表驱动大表，并且被驱动表的关联字段一定要有索引。这是为了最小化嵌套循环的次数。

📝 详细原理解析： MySQL 执行 JOIN 主要使用嵌套循环算法（Nested-Loop Join，NLJ）：

先从驱动表（外层表）中取出一行数据。
拿着这一行数据的关联字段，去被驱动表（内层表）中查找匹配的行。
循环往复。

假设表 A 有 100 条数据，表 B 有 10000 条数据。

如果 A 驱动 B：外层循环 100 次，每次去 B 中找。如果 B 的关联字段有索引，B 的查找是 O(logN)。总代价相对较小。
如果 B 驱动 A：外层循环 10000 次，开销剧增。

如何确定驱动表：

LEFT JOIN：左表是驱动表，右表是被驱动表。
RIGHT JOIN：右表是驱动表，左表是被驱动表。
INNER JOIN：MySQL 优化器会自动选择数据量较小（或经过 WHERE 过滤后结果集较小）的表作为驱动表。

🌟 面试加分项（实战经验）： 在微服务架构下，由于数据库被拆分（分库分表），我们现在极少在数据库层面直接使用 JOIN，尤其是 3 张表以上的 JOIN 是被规约严令禁止的。通常的做法是在 Java 代码层的内存里做 JOIN：先查 A 表的集合，提取出关联 ID 的 List，再用 WHERE id IN (...) 去查 B 表，最后在内存里通过 Map 或 Stream 将数据拼装起来。这虽然增加了一次网络请求，但大幅减轻了数据库 CPU 的压力，且极利于后续的横向扩展。

❓ 面试官：能看懂 EXPLAIN 的结果吗？重点看哪几个字段？

频率：🔥🔥🔥🔥

💡 一句话总结（先抛结论）：EXPLAIN 是 SQL 调优最重要的工具。我主要看四个核心字段：type（有没有走全表扫描）、key（实际用了哪个索引）、rows（预估扫描行数）和 Extra（是否触发了文件排序或覆盖索引）。

📝 详细原理解析：

type（访问类型，性能从好到坏）：
- system / const：系统表或只有一条数据（极好）。
- eq_ref：使用了主键或唯一索引扫描（好）。
- ref：使用了非唯一索引扫描。
- range：使用了索引进行范围查询（如 >、<、between、in）。
- index：全索引扫描（遍历了整棵索引树，比全表稍好，但仍需优化）。
- ALL：全表扫描（最差，必须优化）。
key：
- 实际使用的索引名称。如果为 NULL，说明没走索引。
rows：
- 优化器预估需要读取的行数。越小越好。
Extra（额外信息）：
- Using index：完美！触发了覆盖索引，不需要回表。
- Using index condition：触发了索引下推（ICP）。
- Using where：在 Server 层进行了条件过滤。
- Using filesort：差！无法利用索引完成排序，在内存或磁盘中进行了额外的排序操作。必须通过建联合索引来优化。
- Using temporary：差！使用了临时表保存中间结果（常出现在 GROUP BY 时），开销极大。

SQL 优化与执行计划面试指南 ​

❓ 面试官：如果线上发现一条慢查询，你的排查和优化思路是什么？ ​

❓ 面试官：深分页问题（LIMIT 1000000, 10）为什么慢？怎么优化？ ​

❓ 面试官：如何优化 JOIN 操作？什么是小表驱动大表？ ​

❓ 面试官：能看懂 EXPLAIN 的结果吗？重点看哪几个字段？ ​

SQL 优化与执行计划面试指南

❓ 面试官：如果线上发现一条慢查询，你的排查和优化思路是什么？

❓ 面试官：深分页问题（LIMIT 1000000, 10）为什么慢？怎么优化？

❓ 面试官：如何优化 JOIN 操作？什么是小表驱动大表？

❓ 面试官：能看懂 EXPLAIN 的结果吗？重点看哪几个字段？