MySQL（五）：SQL 优化与执行计划

星辰逐梦2026/6/8大约 17 分钟

MySQL（五）：SQL 优化与执行计划

导语：这一篇是"实战工具箱"。从慢查询定位、EXPLAIN 解读，到 count / WHERE / HAVING / JOIN 算法 / ORDER BY / DELETE 等一组高频语法辨析题，都是面试中容易被连着追问的细节。共 13 题。

一、慢 SQL 定位与执行计划

1. 如何定位慢 SQL？慢查询日志如何开启？

答： 定位手段（按优先级）：

手段	用途
慢查询日志	记录执行时间超过阈值的 SQL，是最主要的手段
`EXPLAIN` / `EXPLAIN ANALYZE`	分析单条 SQL 的执行计划与真实耗时（8.0.18+ 的 `ANALYZE` 会实际执行并给出各步骤耗时）
`SHOW PROCESSLIST` / `information_schema.processlist`	查看当前正在执行的语句与状态
`performance_schema`	统计 SQL 的耗时、扫描行数、锁等待等（8.0 推荐）
第三方工具	`pt-query-digest`（慢日志聚合分析）、Arthas、监控平台

开启慢查询日志：

SET GLOBAL slow_query_log = ON;                          -- 开启（默认关闭）
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';
SET GLOBAL long_query_time = 1;                          -- 超过 1 秒记录（默认 10 秒）
SET GLOBAL log_queries_not_using_indexes = ON;           -- 未走索引的也记录（慎用，日志会暴涨）
SET GLOBAL log_slow_admin_statements = ON;               -- 记录慢 DDL
SET SESSION min_examined_row_limit = 100;                -- 扫描行数少的不记录

生产建议：long_query_time 设 0.5~1 秒；慎开 log_queries_not_using_indexes（小表全扫很常见，日志会被灌满）；日志按天切割并定期归档。持久化配置应写进 my.cnf，SET GLOBAL 重启会失效。

2. 如何看执行计划（`EXPLAIN`）？各字段含义？

答：在 SQL 前加 EXPLAIN 查看优化器选择的执行方案。关键字段：

字段	含义	关注点
`id`	查询序列号	值越大越先执行；相同 id 从上往下执行
`select_type`	查询类型	`SIMPLE`、`PRIMARY`、`SUBQUERY`、`DERIVED`、`UNION`
`type`	访问类型	核心指标，见下一题
`possible_keys`	可能用到的索引	只是候选，不代表会用
`key`	实际使用的索引	`NULL` 表示没走索引
`key_len`	使用到的索引字节长度	可推断联合索引用到了几列（越短说明用得越少）
`rows`	预估扫描行数	越小越好；估算值可能失真
`filtered`	按条件过滤后剩余行的百分比	越低说明过滤条件越不理想
`Extra`	附加信息	最重要的排查依据，见下

Extra 的高频取值：

取值	含义	好坏
`Using index`	覆盖索引，无需回表	✅ 好
`Using index condition`	索引条件下推（ICP）	✅ 好
`Using where`	在 Server 层做了过滤	⚠️ 中性（可能没充分利用索引）
`Using filesort`	需要额外排序（未用索引排序）	❌ 需优化
`Using temporary`	使用了临时表（常见于 `GROUP BY`、`DISTINCT`、`UNION`）	❌ 需优化
`Using join buffer`	使用了 Block Nested Loop 等连接缓冲（被驱动表无索引）	❌ 需优化

实战口诀：key 为 NULL、type 为 ALL、Extra 里出现 filesort / temporary / join buffer，这四项出现任意一个就应该着手优化。

3. `EXPLAIN` 的 `type` 访问类型从好到坏的顺序？

答：常见的 type 由好到差排序：

system > const > eq_ref > ref > range > index > ALL

type	含义	示例
`system`	表只有一行（系统表），是 `const` 的特例	—
`const`	通过主键或唯一索引等值查询，最多一行，在优化阶段就确定结果	`WHERE id = 1`
`eq_ref`	连接查询中，被驱动表用主键/唯一索引匹配，每行只匹配一条	两表按主键 JOIN
`ref`	用非唯一索引等值查询，可能匹配多行	`WHERE idx_col = 'x'`
`range`	索引范围扫描	`BETWEEN`、`>`、`<`、`IN`
`index`	全索引扫描（遍历整棵索引树）	比 `ALL` 好，因为索引通常比表小
`ALL`	全表扫描	最差，必须优化

还有几个介于其中的类型（面试加分）：index_merge（索引合并，多个单列索引取并/交）、ref_or_null、index_subquery、unique_subquery、fulltext。

目标：日常业务 SQL 至少达到 range，理想是 ref / eq_ref / const。若出现 ALL 且表数据量大，必须加索引或改写 SQL。

二、高频语法辨析

4. 常见的 SQL 优化手段有哪些？

答：分几个层面：

1）索引层面

为高频 WHERE / JOIN ON / ORDER BY / GROUP BY 建合适的联合索引，遵循最左前缀；把列顺序设计为「等值列在前 → 范围列在后 → 排序列次之」；
用覆盖索引消除回表（SELECT 只取需要的列，禁止 SELECT *）；
避免在索引列上做函数、运算、隐式类型转换（会让索引失效）。

2）SQL 写法层面

LIMIT 限制返回行数；深分页用"延迟关联"或"游标分页"（见第 5 题）；
UNION ALL 替代 UNION（除非确实需要去重）；
减少 JOIN 的表数量；大表 JOIN 确保被驱动表的连接字段有索引；
批量插入代替循环单条插入；INSERT ... ON DUPLICATE KEY UPDATE 或 REPLACE 处理幂等写入；
用 EXISTS / JOIN 改写"大 IN 列表"。

3）表结构与运维层面

合理选择数据类型（金额用 DECIMAL、主键用自增 BIGINT、字符集用 utf8mb4）；
冷热数据分离 / 历史数据归档（ALTER TABLE ... PARTITION 或归档到历史表）；
读写分离、加缓存、最后才考虑分库分表（见分库分表篇）。

原文此处有两处需要纠正（重要）：
① "用 EXISTS 替代 IN（大表场景）"——这个经验在现代 MySQL 中已经过时了。
传统说法是"遵循小表驱动大表"，据此推导出 IN 与 EXISTS 的适用差异。但从 MySQL 5.6 起引入了半连接（semi-join）优化，MySQL 8.0.17+ 更是会把 IN 子查询自动改写成 semi-join，因此优化器会自行选择最优的连接方式与驱动顺序，IN 与 EXISTS 的性能差异已经很小。
正确的做法是：不要背口诀，直接用 EXPLAIN 对比两者的 type、rows 与实际耗时；把关注点放在"子查询字段是否有索引、能否被改写为 JOIN"上。
② "统计计数优先 COUNT(*)，InnoDB 已优化"——需要说清"优化"到什么程度。
COUNT(*) 在 InnoDB 中仍然是需要遍历的，不是 O(1)。它比 COUNT(列) 快的原因是：不判断 NULL、且优化器会挑最小的那棵二级索引来遍历以减少 IO。而 MyISAM 能 O(1) 返回是因为它在表元数据里维护了行数（但带 WHERE 时同样要扫描）。

5. 大表深分页（`LIMIT 1000000, 10`）很慢怎么优化？

答： 为什么慢：LIMIT offset, n 会先扫描并丢弃前 offset 行（还要逐行判断、回表、丢进临时结果集），只为了返回 10 行——做了 100 万次无用功。

方案一：延迟关联（覆盖索引 + 回表）

先用索引只取主键（走覆盖索引，很快），再用主键 JOIN 回表取完整行：

SELECT t.* FROM t
JOIN (SELECT id FROM t ORDER BY id LIMIT 1000000, 10) tmp USING (id);

原理：子查询在二级索引上完成"翻页丢行"，避免了 100 万次回表；只有最终 10 条才真正回表。

方案二：游标分页 / Seek Method（推荐用于无限下拉）

利用有序主键，"记住上一页最后一条的位置"，完全避免 offset：

-- 第一页
SELECT * FROM t ORDER BY id LIMIT 10;
-- 后续页：把上一页最后一条的 id 传进来
SELECT * FROM t WHERE id > 1000000 ORDER BY id LIMIT 10;

优点：无论翻到多深，都是索引范围扫描 + LIMIT 10，性能恒定；
缺点：无法跳页（只能上一页/下一页），适合"瀑布流/无限滚动"，不适合"跳到第 500 页"。

方案三：业务层面规避

限制最大可翻页数（如只允许翻到第 100 页），引导用户用筛选条件缩小范围；
把这类"复杂列表查询"交给 ES 等搜索引擎，用 search_after 做深分页（ES 的 from + size 同样有深分页问题）；
若 ORDER BY 的列是唯一的且有序，可考虑按时间范围分段查询。

对比总结：延迟关联适合"必须用 offset"的场景（改动最小）；游标分页适合"能接受不能跳页"的场景（性能最优）。面试中把两种都说出来，并指出各自的限制，是最好的答法。

6. `COUNT(*)`、`COUNT(1)`、`COUNT(列)` 有什么区别？

答：

写法	统计内容	性能
`COUNT(*)`	统计行数，不判断任何列是否为 NULL	最优：优化器会选最小的二级索引遍历
`COUNT(1)`	同上（每行返回常量 1，同样不判断 NULL）	与 `COUNT()` 完全等价*，性能无差别
`COUNT(主键)`	主键非空，等价于统计行数	优化器会把它优化为 `COUNT(*)`
`COUNT(普通列)`	只统计该列非 NULL 的行数	更慢：需要取出该列的值逐行判断是否为 NULL；且如果该列没有索引，还要读聚簇索引
`COUNT(DISTINCT 列)`	统计该列去重后的非 NULL 值个数	最慢，需要去重（可能用临时表）

结论：

COUNT(*) 与 COUNT(1) 没有任何性能差异——这是最常见的误区，二者语义与执行方式一致；
COUNT(列) 语义不同（跳过 NULL），能不用就不用；
InnoDB 的 COUNT(*) 仍然要遍历索引——因为 MVCC 下有多版本，无法维护一个全局准确行数（MyISAM 可以，但带 WHERE 时同样要扫）。

如何优化大表计数：

方案	说明
维护计数表	用 `INSERT ... ON DUPLICATE KEY UPDATE cnt = cnt + 1` 自行维护，读取 O(1)
Redis 计数器	`INCR`/`DECR`，注意与数据库的一致性（可用 binlog 补偿）
近似值	用 `EXPLAIN` 的 `rows` 或 `SHOW TABLE STATUS` 的 `Rows` 做估算（误差可能很大）
只查"是否有"	若只是判断存在性，用 `SELECT 1 ... LIMIT 1` 而非 `COUNT(*) > 0`

7. `WHERE` 与 `HAVING` 有什么区别？

答：

维度	`WHERE`	`HAVING`
执行时机	在 `GROUP BY` 分组之前，过滤原始行	在 `GROUP BY` 分组之后，过滤分组结果
能否用聚合函数	不能（如 `WHERE COUNT(*) > 1` 报错）	可以（`HAVING COUNT(*) > 1`）
能否用 SELECT 别名	一般不能（标准 SQL 顺序在别名之前）	可以使用（MySQL 支持）
能否用索引	可以，是主要的索引利用点	一般不能用索引（作用于分组后的结果）
性能	更早过滤，减少参与分组的数据量 → 更高效	分组后再过滤，开销更大

-- ✅ 尽量把条件写在 WHERE
SELECT dept, AVG(salary) FROM emp
WHERE status = 1                     -- 先过滤原始行（可用索引）
GROUP BY dept
HAVING AVG(salary) > 10000;          -- 再过滤分组结果

最佳实践：能用 WHERE 的条件就别放 HAVING。把条件尽量前移到 WHERE，可以显著减少进入分组的数据量。典型反例是把 WHERE 能做的过滤（如 status = 1）写到 HAVING 里，导致全表参与分组。

8. `JOIN` 中 `ON` 与 `WHERE` 有什么区别？

答：关键在于连接类型：

1）INNER JOIN：ON 与 WHERE 基本等价

内连接只保留"两边都匹配"的行，优化器会把 ON 与 WHERE 的条件合并处理，执行结果相同。

2）LEFT JOIN / RIGHT JOIN：语义完全不同（高频考点）

子句	作用
`ON`	连接（匹配）条件——不满足时，仍保留左表（或右表）的行，另一侧补 `NULL`
`WHERE`	结果过滤条件——不满足时，该行从结果中删掉

关键结论：在 LEFT JOIN 中，对「右表」的过滤条件写在 WHERE 里，会把 LEFT JOIN 退化成 INNER JOIN（因为补 NULL 的行不满足条件，会被过滤掉）。

-- ① 过滤右表条件放 ON：仍保留所有用户，无订单的用户 order_no 为 NULL
SELECT u.id, o.order_no FROM user u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 1;

-- ② 过滤右表条件放 WHERE：等于 INNER JOIN，只返回有有效订单的用户
SELECT u.id, o.order_no FROM user u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 1;

实战原则：左表的过滤条件放 WHERE，右表的过滤条件放 ON（当你想保留左表全部数据时）。这是 LEFT JOIN 最容易出错的地方，也是"名单类查询漏数据"的常见 bug 来源。

9. `UNION` 与 `UNION ALL` 有什么区别？

答：

维度	`UNION`	`UNION ALL`
是否去重	会去重（隐式 `DISTINCT`）	不去重，直接拼接
额外开销	需要排序或临时表来去重	无
性能	较慢（数据量大时尤其明显）	快
使用场景	确实需要去重时	确定无重复、或允许重复时（绝大多数情况）

原则：能用 UNION ALL 就用 UNION ALL。很多开发者习惯性写 UNION，在大结果集下会带来不必要的排序/临时表开销（EXPLAIN 里能看到 Using temporary）。
补充：UNION 的结果列名以第一个 SELECT 为准，且要求各 SELECT 的列数与类型兼容（MySQL 会做隐式转换）；若两个子查询的数据来源本身互斥（如不同状态的数据），根本无需去重。

三、JOIN 与排序的内部实现

10. MySQL 的 JOIN 算法有哪些？为什么说"小表驱动大表"？

答： MySQL 的 JOIN 底层主要有这几种算法：

算法	触发条件	原理	复杂度
Simple Nested-Loop Join（SNLJ）	早期实现，被驱动表无索引	驱动表每行都全表扫描被驱动表	最差（`n × m`），实际很少用
Index Nested-Loop Join（INLJ）	被驱动表的连接字段有索引	驱动表每行通过索引去被驱动表查找	优秀，是理想情况
Block Nested-Loop Join（BNL）	被驱动表无索引，`join_buffer_size` 足够	把驱动表数据放入 join buffer，批量与被驱动表比对，减少被驱动表扫描次数	一般，`Extra` 显示 `Using join buffer`
Hash Join	MySQL 8.0.18+，等值连接且无索引可用	用小表建哈希表，扫描大表时直接哈希探测	大幅优于 BNL（8.0.20+ 支持全场景）

"小表驱动大表"的原理：

在外层循环遍历的表叫驱动表，内层被查找的叫被驱动表；
驱动表的行数决定了外层循环的次数——驱动表越小，循环次数越少，总体代价越低；
尤其在 BNL 算法下，驱动表要放进 join buffer，驱动表越大越可能放不下（要分段多次扫描被驱动表，代价倍增）。

两个实践要点：

确保被驱动表的连接字段有索引（让优化器能选 INLJ）——这是优化 JOIN 的第一要务；
多表 JOIN 时让结果集最小的表做驱动表，但不要手动写死顺序——优化器通常会自行估算选择，应以 EXPLAIN 的 id / rows 为准（EXPLAIN FORMAT=JSON 或 EXPLAIN ANALYZE 能看到实际的连接顺序）。

注意：在外连接（LEFT JOIN）中，驱动表通常是固定的（左表必须做驱动表，否则语义会被破坏），所以优化手段主要落在"给被驱动表建索引"上。

11. `ORDER BY` 是如何实现的？如何避免 `filesort`？

答： ORDER BY 有两种实现路径：

1）索引排序（最优）：如果 ORDER BY 的列顺序与某个索引的顺序一致，直接顺序扫描索引即可，天然有序，无需额外排序（Extra 中不出现 filesort）。

2）filesort（需要额外排序）：无法利用索引顺序时，MySQL 会在内存/磁盘中排序。它有两种模式：

模式	做法	说明
全字段排序	把 `SELECT` 需要的所有字段都放进 `sort_buffer`，排完直接返回	内存占用大，但不需要回表
rowid 排序	`sort_buffer` 只放排序列 + 主键，排完再按主键回表取其他列	内存占用小，但需要回表

由 max_length_for_sort_data 决定用哪种（单行长度超过该值就用 rowid 排序）。

性能断崖：若排序数据量超过 sort_buffer_size，MySQL 会使用磁盘临时文件做归并排序——性能骤降（SHOW STATUS LIKE 'Sort_merge_passes' 增大就是信号）。

避免 filesort 的手段：

手段	说明
让索引覆盖排序顺序	联合索引设计成「等值列在前、排序列在后」，如 `INDEX (status, create_time)` 支持 `WHERE status=1 ORDER BY create_time`
减少 `SELECT` 的列	列少 → 更可能用全字段排序（避免回表），也更可能放得下 `sort_buffer_size`
增大 `sort_buffer_size`	治标手段，但注意是每连接分配，不能无脑调大
避免范围查询破坏有序性	`WHERE status > 1 ORDER BY create_time` 无法用上面的联合索引排序（范围之后的列无序）

-- 索引：INDEX idx_status_time (status, create_time)
SELECT id FROM t WHERE status = 1 ORDER BY create_time;   -- ✅ 等值 + 排序，可走索引排序
SELECT id FROM t WHERE status > 1 ORDER BY create_time;   -- ❌ 范围查询，需 filesort

同理：GROUP BY 在无法利用索引有序性时，也需要临时表 + 排序（Extra 显示 Using temporary; Using filesort）。因此 GROUP BY 的优化思路与 ORDER BY 一致：让分组列也走索引。
小技巧：MySQL 8.0 会自动消除 GROUP BY 之后不必要的 ORDER BY（因为分组本身已排序），而 5.7 及之前不会——所以老版本里写 GROUP BY 时可以顺便观察是否需要显式 ORDER BY NULL。

12. `DELETE`、`TRUNCATE`、`DROP` 有什么区别？

答：

维度	`DELETE`	`TRUNCATE`	`DROP`
类型	DML（数据操作）	DDL（数据定义）	DDL
删除范围	可带 `WHERE` 删部分行	清空整表（保留表结构）	删除整张表（连结构一起）
能否回滚	可以（写了 undo log）	不可回滚（隐式提交）	不可回滚
自增值	不重置（继续往下走）	重置为初始值	—
速度	慢（逐行删除 + 写 undo/binlog）	快（直接丢弃数据页、重建表）	快
触发触发器	会触发	不会	不会
是否释放空间	不立即释放（形成碎片，需 `OPTIMIZE TABLE`）	释放（等同于重建表）	全部释放
`WHERE` 支持	支持	不支持	不支持

实践要点：

清空整表优先用 TRUNCATE（快、释放空间、重置自增），但注意它不可回滚且会隐式提交当前事务；
DELETE 删完大量数据后，磁盘空间不会自动回收——需要 OPTIMIZE TABLE t（本质是重建表 + 回收碎片，会上锁、大表慎用，推荐用 pt-online-schema-change/gh-ost）；
DELETE 大表要分批：一次删百万行会产生巨大 undo 与 binlog、长时间持锁，应分批 DELETE ... LIMIT 1000 循环执行，并在批次间短暂 sleep 降低压力；
误删恢复：DROP/TRUNCATE 无法用 binlog 直接回滚（没有行级记录），需要全量备份 + binlog 的 PITR（见《MySQL（六）》），DELETE 则可以借助 binlog2sql / MyFlash 做闪回。

13. 为什么优化器会"选错索引"？如何干预？

答： "选错索引"的本质是：优化器基于「成本估算」选择，而估算依赖的统计信息不准确。

常见原因：

原因	说明
统计信息失真	索引基数（Cardinality）是采样估算的（默认采样 20 页），大量增删后严重偏离实际
需要回表的行数过多	优化器估算"走索引 + 回表" vs "全表扫描"的成本，如果预计要回表很多行，就会选全表扫描——这个决策在某些数据分布下会错
`rows` 估算偏差大	多条件组合、范围查询、`LIKE` 等场景下，优化器的行数估算常常与实际相差一个数量级
索引区分度低	优化器对低选择性索引的信心不足
统计信息未更新	表数据剧变后没触发/没执行 `ANALYZE TABLE`

干预手段（按推荐顺序）：

手段	做法	说明
1. 重新收集统计信息	`ANALYZE TABLE t;`	最简单，往往直接解决
2. 提高采样精度	`innodb_stats_persistent_sample_pages` 调大（默认 20）	让基数估算更准
3. 改进索引设计	建覆盖索引消除回表，让"走索引"的成本估算明显更低	治本
4. 改写 SQL	把范围查询拆成等值、去掉函数、把 `SELECT *` 改成具体列	让优化器有更好的选择
5. 强制指定索引	`SELECT ... FROM t FORCE INDEX(idx) WHERE ...`	最后手段
6. 忽略某索引	`IGNORE INDEX(idx)`	用于排除干扰

关于 FORCE INDEX 的提醒：它是双刃剑——一旦数据分布变化，原本"强制"的索引可能反而更慢，而且会掩盖真正的问题（统计信息或索引设计）。优先修正统计信息与索引设计，而不是长期依赖强制索引。
排查技巧：用 EXPLAIN ANALYZE（MySQL 8.0.18+）可以看到每个步骤的真实行数与耗时，能直接对比"优化器预估"与"实际执行"的差距，是诊断这类问题最有效的手段。