第七篇：慢查询分析与SQL优化实战

前言

在前面六篇文章中，我们从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 开启慢查询日志

-- 查看当前配置SHOWVARIABLESLIKE'slow_query%';SHOWVARIABLESLIKE'long_query_time';-- 开启慢查询日志SETGLOBALslow_query_log=ON;SETGLOBALlong_query_time=0.2;-- 超过200毫秒就记录SETGLOBALslow_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: 85632SELECTo.id,o.order_no,o.status,o.pay_amount,o.create_timeFROMtb_order oWHEREo.user_id=1001ANDo.statusIN(1,2,3)ORDERBYo.create_timeDESCLIMIT0,20;

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

1.3 监控平台

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

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

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

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

2.1 Explain完整输出解读

EXPLAINSELECT*FROMtb_orderWHEREuser_id=1001ORDERBYcreate_timeDESCLIMIT20;

2.2 危险信号速查表

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

-- 原SQLEXPLAINSELECT*FROMtb_orderWHEREuser_id=1001ANDstatusIN(1,2,3)ORDERBYcreate_timeDESCLIMIT0,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 索引优化——最直接的方案

-- 建立联合索引CREATEINDEXidx_user_status_timeONtb_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*FROMtb_orderWHEREuser_id=1001ANDstatusBETWEEN1AND3-- 替换 IN(1,2,3)ORDERBYcreate_timeDESCLIMIT0,20;-- 如果status值不连续（如1,5,9），无法用BETWEEN-- 此时filesort在1200行上影响不大，不需要继续优化

3.3 覆盖索引——终极优化

-- 不让SELECT * 回表，改为只查索引覆盖的字段SELECTid,user_id,status,create_timeFROMtb_orderWHEREuser_id=1001ANDstatusIN(1,2,3)ORDERBYcreate_timeDESCLIMIT0,20;-- Extra显示Using index —— 覆盖索引，不回表

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

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

-- 第5000页，每页20条SELECT*FROMtb_orderWHEREuser_id=1001ORDERBYcreate_timeDESCLIMIT100000,20;-- RT：800ms（扫描100000行非覆盖数据，每行回表再丢弃）-- 方案一：子查询取IDSELECT*FROMtb_order oINNERJOIN(SELECTidFROMtb_orderWHEREuser_id=1001ORDERBYcreate_timeDESCLIMIT100000,20)AStONo.id=t.id;-- RT：200ms（子查询只取id，覆盖索引不涉及回表；外层回表只回20次）-- 方案二：游标分页（最优，但前端只能上一页/下一页）SELECT*FROMtb_orderWHEREuser_id=1001ANDcreate_time<'2024-01-01 10:30:00'ORDERBYcreate_timeDESCLIMIT20;-- RT：<10ms（直接定位，不需要跳过10万行）

四、JOIN优化

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

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

4.1 驱动表的选择

SELECT*FROMtb_order oJOINtb_course cONo.course_id=c.idWHEREo.user_id=1001;

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

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

4.2 关联字段必须有索引

-- ❌ course_id没有索引SELECT*FROMtb_order oJOINtb_course cONo.course_id=c.id;-- 对order的每一行，都要在course表上全表扫描找course_id匹配的行 → O(n*m)-- ✅ course_id有索引CREATEINDEXidx_course_idONtb_order(course_id);-- 对order的每一行，通过order.course_id在course表的主键索引O(1)定位 → O(n)

4.3 JOIN vs 子查询

-- JOIN：适合需要两张表字段、关联字段有索引的场景SELECTo.*,c.course_nameFROMtb_order oJOINtb_course cONo.course_id=c.id;-- 子查询：适合只需子表部分数据、或逻辑更清晰时SELECT*FROMtb_orderWHEREcourse_idIN(SELECTidFROMtb_courseWHEREstatus=1);

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

五、IN vs EXISTS

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

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

-- IN：外层驱动SELECT*FROMtb_orderWHEREcourse_idIN(SELECTidFROMtb_courseWHEREstatus=1);执行顺序：先执行外层主查询 → 拿course_id去内层子查询中匹配 适用：外层结果集小，子查询结果集大时-- EXISTS：内层驱动SELECT*FROMtb_order oWHEREEXISTS(SELECT1FROMtb_course cWHEREc.id=o.course_idANDc.status=1);执行顺序：先执行内层子查询 → 拿到所有符合条件的course.id → 再用这些id去外层匹配 适用：子查询结果集小，外层大时

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

六、验证优化效果

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

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

6.1 四维度验证

6.2 执行计划对比模板

优化后重新Explain确认：

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三种格式的差异，以及主从延迟的监控和处理。