前言

在前面六篇文章中，我们从B+树索引到底层原理，从事务隔离级别到锁机制，再到Redo Log和Binlog的崩溃恢复——这些都是MySQL的底层知识。但面试中，面试官往往不会只问原理，还会追着问：

“你说你做过慢查询优化，具体是怎么做的？从发现到优化的完整流程是什么？”

这就是本文要解决的问题。这篇文章会将前六篇的索引知识落地到真实的SQL优化场景中，完整复盘一条慢SQL从发现、分析到优化的全过程。同时，这篇文章会直接呼应你秒杀系统实战中的慢SQL治理案例——读完本文，你就能完整解释"从1.2秒降到80毫秒"到底做了什么、为什么这样做。

本文核心问题：

1. 怎么发现慢SQL？慢查询日志怎么配置和分析？
2. Explain输出的每个字段怎么解读？ALL、filesort、temporary分别怎么优化？
3. 分页查询越翻越慢怎么办？深分页优化的三种方案
4. JOIN查询怎么优化？驱动表的选择有什么讲究？
5. IN和EXISTS有什么区别？什么时候该用哪个？
6. 如何验证优化效果？只看执行时间够吗？

读完本文，你将对慢SQL优化拥有从发现到验证的完整方法论，面试时能完整讲清楚简历上的慢查询优化案例。

---

一、如何发现慢SQL？

疑问：生产环境怎么知道哪些SQL是慢的？

回答：三道防线——慢查询日志发现、监控平台聚合展示、应用侧链路追踪定位来源。

1.1 开启慢查询日志

-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.2;  -- 超过200毫秒就记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

阈值的艺术：0.2秒在OLTP系统中是一个常用起点。接口整体RT要求在50ms以内时，数据库查询占0.2秒已经需要排查。

1.2 慢查询日志示例

# Time: 2024-01-15T10:23:45.123456Z
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 1.189000  Lock_time: 0.000000  Rows_sent: 20  Rows_examined: 85632

SELECT o.id, o.order_no, o.status, o.pay_amount, o.create_time
FROM tb_order o  
WHERE o.user_id = 1001 
  AND o.status IN (1,2,3)
ORDER BY o.create_time DESC
LIMIT 0, 20;

关键信息：Rows_sent=20 只返回了20行，Rows_examined=85632 却扫描了8.5万行。扫描行数与返回行数的比值越高，索引越差或根本没有命中。

1.3 监控平台

生产环境中慢查询日志需要配合以下工具，形成可视化：

工具	作用
pt-query-digest	Percona出品的慢日志离线分析工具，排行最慢的SQL和统计执行频率
Prometheus + MySQL Exporter	实时采集慢查询数量和平均执行时间
Grafana	可视化慢查询趋势面板，设置告警阈值

---

二、慢查询分析神器——Explain

疑问：拿到一条慢SQL，从哪里开始分析？

回答：Explain永远是第一步。它告诉你MySQL优化器选择了什么执行计划，有没有走索引、扫描了多少行、有没有额外排序。

2.1 Explain完整输出解读

EXPLAIN SELECT * FROM tb_order WHERE user_id = 1001 ORDER BY create_time DESC LIMIT 20;

输出项	当前值	含义	危险信号
id	1	查询的执行顺序	多表时出现不同id说明有子查询，执行顺序从大到小
select_type	SIMPLE	查询类型	出现DEPENDENT SUBQUERY时子查询依赖外层，性能通常很差
table	tb_order	访问的表	—
type	ALL	访问类型	ALL=全表扫描，必须优化
possible_keys	idx_user_id	可能使用的索引	NULL说明没有可用的索引
key	NULL	实际使用的索引	NULL=没走索引
key_len	NULL	使用的索引字节数	NULL说明没有实际使用索引
rows	85632	预估扫描的行数	与实际返回行数的比值越高，索引效率越低
Extra	Using filesort	额外操作	filesort=额外排序，temporary=使用了临时表

2.2 危险信号速查表

信号	严重程度	含义	优化方向
type=ALL	🔴 严重	全表扫描	必须建索引
key=NULL	🔴 严重	没有走索引	检查索引命中条件，排查索引失效原因
rows >> 实际返回行数	🟡 警惕	扫描了大量无用行	索引区分度不够或索引设计不合理
Extra: Using filesort	🟡 警惕	额外排序	把ORDER BY列加入联合索引
Extra: Using temporary	🔴 需要关注	使用了临时表	DISTINCT/GROUP BY列加索引，减少临时表依赖

2.3 实战案例：订单分页查询

-- 原SQL
EXPLAIN SELECT * FROM tb_order 
WHERE user_id = 1001 AND status IN (1,2,3)
ORDER BY create_time DESC 
LIMIT 0, 20;

-- 输出：type=ALL, rows=85632, Extra=Using where; Using filesort

分析：

1. type=ALL：全表扫描，没有索引可用
2. rows=85632：预估扫描8.5万行，取20行，效率极低
3. Using filesort：额外排序——8.5万行数据排序，消耗CPU和内存

根因：user_id、status、create_time三个字段组合查询，没有任何联合索引能同时覆盖。user_id有索引但status不在索引中，MySQL优化器发现过滤完user_id后仍需扫描大量行逐行比对status——它判断全表扫描比走索引更省。

---

三、优化策略实战

3.1 索引优化——最直接的方案

-- 建立联合索引
CREATE INDEX idx_user_status_time ON tb_order(user_id, status, create_time);

-- 优化后Explain：
-- type=range, key=idx_user_status_time, rows=1200, 
-- Extra=Using index condition; Using filesort

效果分析：

• type=ALL → range：从全表扫描变成范围索引扫描
• rows=85632 → 1200：只需扫描该用户的1200条订单，不是全表8.5万行
• Extra中filesort还在——status IN (1,2,3)破坏索引的有序性，create_time在同一个status内有序，但跨status全局无序

3.2 消除filesort——让排序也走索引

-- 如果status只有少数几个值，可以将IN改写为范围
-- 前提：status值连续（如1,2,3是连续的）
SELECT * FROM tb_order 
WHERE user_id = 1001 
  AND status BETWEEN 1 AND 3    -- 替换 IN(1,2,3)
ORDER BY create_time DESC 
LIMIT 0, 20;

-- 如果status值不连续（如1,5,9），无法用BETWEEN
-- 此时filesort在1200行上影响不大，不需要继续优化

3.3 覆盖索引——终极优化

-- 不让SELECT * 回表，改为只查索引覆盖的字段
SELECT id, user_id, status, create_time FROM tb_order 
WHERE user_id = 1001 AND status IN (1,2,3)
ORDER BY create_time DESC 
LIMIT 0, 20;

-- Extra显示Using index —— 覆盖索引，不回表

覆盖索引 + 索引条件覆盖的字段列表必须和索引完全一致，SELECT * 直接葬送覆盖索引优化。

3.4 深分页优化——越翻越慢的解决方案

-- 第5000页，每页20条
SELECT * FROM tb_order 
WHERE user_id = 1001 ORDER BY create_time DESC 
LIMIT 100000, 20;
-- RT：800ms（扫描100000行非覆盖数据，每行回表再丢弃）

-- 方案一：子查询取ID
SELECT * FROM tb_order o
INNER JOIN (
    SELECT id FROM tb_order 
    WHERE user_id = 1001 ORDER BY create_time DESC 
    LIMIT 100000, 20
) AS t ON o.id = t.id;
-- RT：200ms（子查询只取id，覆盖索引不涉及回表；外层回表只回20次）

-- 方案二：游标分页（最优，但前端只能上一页/下一页）
SELECT * FROM tb_order 
WHERE user_id = 1001 
  AND create_time < '2024-01-01 10:30:00'
ORDER BY create_time DESC 
LIMIT 20;
-- RT：<10ms（直接定位，不需要跳过10万行）

---

四、JOIN优化

疑问：多表JOIN查询慢，怎么优化？

回答：JOIN优化的核心是驱动表的选择和关联字段的索引。用小表驱动大表，关联字段必须有索引。

4.1 驱动表的选择

SELECT * FROM tb_order o
JOIN tb_course c ON o.course_id = c.id
WHERE o.user_id = 1001;

MySQL优化器会自动选择驱动表：

• 有WHERE条件过滤后行数少的表优先做驱动表
• 关联字段有索引的表优先做被驱动表
• 以上SQL中，o经过user_id过滤后可能只有几十行 → 用小表o驱动大表c → 对o的每一行在c上用主键id=o.course_id快速定位

4.2 关联字段必须有索引

-- ❌ course_id没有索引
SELECT * FROM tb_order o
JOIN tb_course c ON o.course_id = c.id;

-- 对order的每一行，都要在course表上全表扫描找course_id匹配的行 → O(n*m)

-- ✅ course_id有索引
CREATE INDEX idx_course_id ON tb_order(course_id);
-- 对order的每一行，通过order.course_id在course表的主键索引O(1)定位 → O(n)

4.3 JOIN vs 子查询

-- JOIN：适合需要两张表字段、关联字段有索引的场景
SELECT o.*, c.course_name 
FROM tb_order o JOIN tb_course c ON o.course_id = c.id;

-- 子查询：适合只需子表部分数据、或逻辑更清晰时
SELECT * FROM tb_order 
WHERE course_id IN (SELECT id FROM tb_course WHERE status = 1);

MySQL 5.6+对子查询做了大量优化，不再一定比JOIN慢。Explain后看执行计划，哪个优雅用哪个，不需要强制优先选择JOIN。

---

五、IN vs EXISTS

疑问：IN和EXISTS有什么区别？面试经常问。

回答：核心区别在于驱动表不同。IN是外层驱动内层，EXISTS是内层驱动外层。在关联子查询的上下文中，根据驱动表的行数做选择。

-- IN：外层驱动
SELECT * FROM tb_order 
WHERE course_id IN (SELECT id FROM tb_course WHERE status = 1);

执行顺序：先执行外层主查询 → 拿course_id去内层子查询中匹配
适用：外层结果集小，子查询结果集大时

-- EXISTS：内层驱动
SELECT * FROM tb_order o
WHERE EXISTS (
    SELECT 1 FROM tb_course c 
    WHERE c.id = o.course_id AND c.status = 1
);

执行顺序：先执行内层子查询 → 拿到所有符合条件的course.id → 再用这些id去外层匹配
适用：子查询结果集小，外层大时

选择规则：外层小用IN，内层小用EXISTS。不确定时两条各执行一次Explain对比rows估算值。

---

六、验证优化效果

疑问：优化完成后，怎么验证效果？只看执行时间够吗？

回答：四维度验证——执行时间、Explain对比、压测环境验证、慢日志归零。

6.1 四维度验证

维度	优化前	优化后
执行时间	1.2s	80ms
Explain	type=ALL, rows=85632, Extra=Using filesort	type=range, rows=1200, Extra=Using index condition
压测	QPS 1000 → RT 5s	QPS 3000 → RT 200ms
慢日志	每分钟记录3-5条	优化后该SQL不再出现在慢日志中

6.2 执行计划对比模板

优化后重新Explain确认：

Explain字段	优化前	优化后
type	ALL	range
key	NULL	idx_user_status_time
rows	85632	1200
Extra	Using where; Using filesort	Using index condition

6.3 要关注的副作用

• 索引写入开销：新索引会让INSERT/UPDATE/DELETE变慢，写多读少的场景需要权衡
• 内存压力：索引页缓存到Buffer Pool中，热索引多占用Buffer Pool空间，可能挤出其他数据页导致其他查询的缓存命中率下降
• 锁范围变化：新索引改变了查询的扫描行数，行锁的加锁范围也随之改变。曾经全表扫描加大量轻量锁，现在精准命中可能只有几个锁——这个改变在RR隔离级别下可能影响其他事务被阻塞的模式

---

七、慢查询优化方法论总结

1. 发现慢SQL
   ├── 慢查询日志（long_query_time=0.2s）
   ├── 监控平台（Prometheus + MySQL Exporter）
   └── 应用侧APM（SkyWalking/Pinpoint）

2. Explain分析
   ├── type=ALL → 必须加索引
   ├── key=NULL → 检查索引失效原因
   ├── rows >> 返回行数 → 索引区分度不够
   └── Extra=Using filesort/temporary → 排序或临时表需要优化

3. 选择策略
   ├── 单表查询 → 联合索引 + 覆盖索引
   ├── 多表JOIN → 关联字段索引 + 小表驱动大表
   ├── 深分页 → 子查询取ID + 游标分页
   └── 子查询 → EXPLAIN对比后选IN或EXISTS

4. 验证效果
   ├── 执行时间前后对比
   ├── Explain前后对比
   ├── 压测环境验证
   └── 慢日志归零

---

总结

• 慢查询日志是发现问题的第一道防线——Rows_examined / Rows_sent比值越高，索引越差
• Explain是优化的导航仪——type=ALL和key=NULL是必须处理的危险信号
• 联合索引设计要遵循最左前缀和排序顺序，覆盖索引消除回表
• 深分页优化从子查询取ID到游标分页，逐步升级，根据业务场景选择
• JOIN优化核心是小表驱动大表+关联字段必有索引
• 优化后要四维验证——执行时间、Explain、压测、慢日志，不能只看时间
• 优化不只在SQL本身——索引维护成本、内存压力、锁范围变化都是索引变更的副作用，需要综合评估读写比例和业务优先级

下一篇预告：MySQL索引原理（八）——MySQL架构与主从复制，高可用的基石。拆解MySQL的逻辑架构、主从复制原理、Binlog三种格式的差异，以及主从延迟的监控和处理。