前言在前面六篇文章中我们从B树索引到底层原理从事务隔离级别到锁机制再到Redo Log和Binlog的崩溃恢复——这些都是MySQL的底层知识。但面试中面试官往往不会只问原理还会追着问“你说你做过慢查询优化具体是怎么做的从发现到优化的完整流程是什么”这就是本文要解决的问题。这篇文章会将前六篇的索引知识落地到真实的SQL优化场景中完整复盘一条慢SQL从发现、分析到优化的全过程。同时这篇文章会直接呼应你秒杀系统实战中的慢SQL治理案例——读完本文你就能完整解释从1.2秒降到80毫秒到底做了什么、为什么这样做。本文核心问题怎么发现慢SQL慢查询日志怎么配置和分析Explain输出的每个字段怎么解读ALL、filesort、temporary分别怎么优化分页查询越翻越慢怎么办深分页优化的三种方案JOIN查询怎么优化驱动表的选择有什么讲究IN和EXISTS有什么区别什么时候该用哪个如何验证优化效果只看执行时间够吗读完本文你将对慢SQL优化拥有从发现到验证的完整方法论面试时能完整讲清楚简历上的慢查询优化案例。一、如何发现慢SQL疑问生产环境怎么知道哪些SQL是慢的回答三道防线——慢查询日志发现、监控平台聚合展示、应用侧链路追踪定位来源。1.1 开启慢查询日志-- 查看当前配置SHOWVARIABLESLIKEslow_query%;SHOWVARIABLESLIKElong_query_time;-- 开启慢查询日志SETGLOBALslow_query_logON;SETGLOBALlong_query_time0.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# UserHost: 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_id1001ANDo.statusIN(1,2,3)ORDERBYo.create_timeDESCLIMIT0,20;关键信息Rows_sent20只返回了20行Rows_examined85632却扫描了8.5万行。扫描行数与返回行数的比值越高索引越差或根本没有命中。1.3 监控平台生产环境中慢查询日志需要配合以下工具形成可视化工具作用pt-query-digestPercona出品的慢日志离线分析工具排行最慢的SQL和统计执行频率Prometheus MySQL Exporter实时采集慢查询数量和平均执行时间Grafana可视化慢查询趋势面板设置告警阈值二、慢查询分析神器——Explain疑问拿到一条慢SQL从哪里开始分析回答Explain永远是第一步。它告诉你MySQL优化器选择了什么执行计划有没有走索引、扫描了多少行、有没有额外排序。2.1 Explain完整输出解读EXPLAINSELECT*FROMtb_orderWHEREuser_id1001ORDERBYcreate_timeDESCLIMIT20;输出项当前值含义危险信号id1查询的执行顺序多表时出现不同id说明有子查询执行顺序从大到小select_typeSIMPLE查询类型出现DEPENDENT SUBQUERY时子查询依赖外层性能通常很差tabletb_order访问的表—typeALL访问类型ALL全表扫描必须优化possible_keysidx_user_id可能使用的索引NULL说明没有可用的索引keyNULL实际使用的索引NULL没走索引key_lenNULL使用的索引字节数NULL说明没有实际使用索引rows85632预估扫描的行数与实际返回行数的比值越高索引效率越低ExtraUsing filesort额外操作filesort额外排序temporary使用了临时表2.2 危险信号速查表信号严重程度含义优化方向typeALL 严重全表扫描必须建索引keyNULL 严重没有走索引检查索引命中条件排查索引失效原因rows 实际返回行数 警惕扫描了大量无用行索引区分度不够或索引设计不合理Extra: Using filesort 警惕额外排序把ORDER BY列加入联合索引Extra: Using temporary 需要关注使用了临时表DISTINCT/GROUP BY列加索引减少临时表依赖2.3 实战案例订单分页查询-- 原SQLEXPLAINSELECT*FROMtb_orderWHEREuser_id1001ANDstatusIN(1,2,3)ORDERBYcreate_timeDESCLIMIT0,20;-- 输出typeALL, rows85632, ExtraUsing where; Using filesort分析typeALL全表扫描没有索引可用rows85632预估扫描8.5万行取20行效率极低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-- typerange, keyidx_user_status_time, rows1200,-- ExtraUsing index condition; Using filesort效果分析typeALL → range从全表扫描变成范围索引扫描rows85632 → 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_id1001ANDstatusBETWEEN1AND3-- 替换 IN(1,2,3)ORDERBYcreate_timeDESCLIMIT0,20;-- 如果status值不连续如1,5,9无法用BETWEEN-- 此时filesort在1200行上影响不大不需要继续优化3.3 覆盖索引——终极优化-- 不让SELECT * 回表改为只查索引覆盖的字段SELECTid,user_id,status,create_timeFROMtb_orderWHEREuser_id1001ANDstatusIN(1,2,3)ORDERBYcreate_timeDESCLIMIT0,20;-- Extra显示Using index —— 覆盖索引不回表覆盖索引 索引条件覆盖的字段列表必须和索引完全一致SELECT *直接葬送覆盖索引优化。3.4 深分页优化——越翻越慢的解决方案-- 第5000页每页20条SELECT*FROMtb_orderWHEREuser_id1001ORDERBYcreate_timeDESCLIMIT100000,20;-- RT800ms扫描100000行非覆盖数据每行回表再丢弃-- 方案一子查询取IDSELECT*FROMtb_order oINNERJOIN(SELECTidFROMtb_orderWHEREuser_id1001ORDERBYcreate_timeDESCLIMIT100000,20)AStONo.idt.id;-- RT200ms子查询只取id覆盖索引不涉及回表外层回表只回20次-- 方案二游标分页最优但前端只能上一页/下一页SELECT*FROMtb_orderWHEREuser_id1001ANDcreate_time2024-01-01 10:30:00ORDERBYcreate_timeDESCLIMIT20;-- RT10ms直接定位不需要跳过10万行四、JOIN优化疑问多表JOIN查询慢怎么优化回答JOIN优化的核心是驱动表的选择和关联字段的索引。用小表驱动大表关联字段必须有索引。4.1 驱动表的选择SELECT*FROMtb_order oJOINtb_course cONo.course_idc.idWHEREo.user_id1001;MySQL优化器会自动选择驱动表有WHERE条件过滤后行数少的表优先做驱动表关联字段有索引的表优先做被驱动表以上SQL中o经过user_id过滤后可能只有几十行 → 用小表o驱动大表c → 对o的每一行在c上用主键ido.course_id快速定位4.2 关联字段必须有索引-- ❌ course_id没有索引SELECT*FROMtb_order oJOINtb_course cONo.course_idc.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_idc.id;-- 子查询适合只需子表部分数据、或逻辑更清晰时SELECT*FROMtb_orderWHEREcourse_idIN(SELECTidFROMtb_courseWHEREstatus1);MySQL 5.6对子查询做了大量优化不再一定比JOIN慢。Explain后看执行计划哪个优雅用哪个不需要强制优先选择JOIN。五、IN vs EXISTS疑问IN和EXISTS有什么区别面试经常问。回答核心区别在于驱动表不同。IN是外层驱动内层EXISTS是内层驱动外层。在关联子查询的上下文中根据驱动表的行数做选择。-- IN外层驱动SELECT*FROMtb_orderWHEREcourse_idIN(SELECTidFROMtb_courseWHEREstatus1);执行顺序先执行外层主查询 → 拿course_id去内层子查询中匹配 适用外层结果集小子查询结果集大时-- EXISTS内层驱动SELECT*FROMtb_order oWHEREEXISTS(SELECT1FROMtb_course cWHEREc.ido.course_idANDc.status1);执行顺序先执行内层子查询 → 拿到所有符合条件的course.id → 再用这些id去外层匹配 适用子查询结果集小外层大时选择规则外层小用IN内层小用EXISTS。不确定时两条各执行一次Explain对比rows估算值。六、验证优化效果疑问优化完成后怎么验证效果只看执行时间够吗回答四维度验证——执行时间、Explain对比、压测环境验证、慢日志归零。6.1 四维度验证维度优化前优化后执行时间1.2s80msExplaintypeALL, rows85632, ExtraUsing filesorttyperange, rows1200, ExtraUsing index condition压测QPS 1000 → RT 5sQPS 3000 → RT 200ms慢日志每分钟记录3-5条优化后该SQL不再出现在慢日志中6.2 执行计划对比模板优化后重新Explain确认Explain字段优化前优化后typeALLrangekeyNULLidx_user_status_timerows856321200ExtraUsing where; Using filesortUsing index condition6.3 要关注的副作用索引写入开销新索引会让INSERT/UPDATE/DELETE变慢写多读少的场景需要权衡内存压力索引页缓存到Buffer Pool中热索引多占用Buffer Pool空间可能挤出其他数据页导致其他查询的缓存命中率下降锁范围变化新索引改变了查询的扫描行数行锁的加锁范围也随之改变。曾经全表扫描加大量轻量锁现在精准命中可能只有几个锁——这个改变在RR隔离级别下可能影响其他事务被阻塞的模式七、慢查询优化方法论总结1. 发现慢SQL ├── 慢查询日志long_query_time0.2s ├── 监控平台Prometheus MySQL Exporter └── 应用侧APMSkyWalking/Pinpoint 2. Explain分析 ├── typeALL → 必须加索引 ├── keyNULL → 检查索引失效原因 ├── rows 返回行数 → 索引区分度不够 └── ExtraUsing filesort/temporary → 排序或临时表需要优化 3. 选择策略 ├── 单表查询 → 联合索引 覆盖索引 ├── 多表JOIN → 关联字段索引 小表驱动大表 ├── 深分页 → 子查询取ID 游标分页 └── 子查询 → EXPLAIN对比后选IN或EXISTS 4. 验证效果 ├── 执行时间前后对比 ├── Explain前后对比 ├── 压测环境验证 └── 慢日志归零总结慢查询日志是发现问题的第一道防线——Rows_examined / Rows_sent比值越高索引越差Explain是优化的导航仪——typeALL和keyNULL是必须处理的危险信号联合索引设计要遵循最左前缀和排序顺序覆盖索引消除回表深分页优化从子查询取ID到游标分页逐步升级根据业务场景选择JOIN优化核心是小表驱动大表关联字段必有索引优化后要四维验证——执行时间、Explain、压测、慢日志不能只看时间优化不只在SQL本身——索引维护成本、内存压力、锁范围变化都是索引变更的副作用需要综合评估读写比例和业务优先级下一篇预告MySQL索引原理八——MySQL架构与主从复制高可用的基石。拆解MySQL的逻辑架构、主从复制原理、Binlog三种格式的差异以及主从延迟的监控和处理。