先讲一个我印象特别深的线上事故。某个订单系统的列表接口某天下午开始P99从80ms直接飙到12秒数据库CPU跑到92%连接数被打满紧接着应用服务大面积超时用户点什么都转圈。当时值班的同事一边把接口降级保核心链路一边翻慢查询日志最后揪出一条看起来很普通的SQL查订单列表where条件里对下单时间字段做了函数包裹索引彻底失效加上分页翻到了几千行之后回表成本直接按几十万行算。等我们把函数条件改写成区间查询、把深分页改成延迟关联接口才从12秒回到70ms。那次事故之后我一直在想MySQL SQL语句性能优化这件事最值钱的地方不是背几个索引失效口诀而是能把为什么慢、怎么定位、怎么写才快、面试怎么答这条线完整串起来。这篇文章我就按这个链路来讲适合后端开发、准DBA、以及正在准备数据库面试的同学。内容会尽量贴合实际生产场景每一步都有可复现的依据。1. 一条慢SQL引发的线上事故从发现到定责1.1 那晚的告警与排障动作先说故障表现。监控大屏上最先报警的是数据库CPU使用率从平时的20%以内直接打到90%以上紧接着活跃连接数暴涨。MySQL的连接数是有上限的默认max_connections通常只有几百当大量查询都卡住不出结果时新来的请求根本拿不到连接应用层就会跟着出问题表现就是接口超时和雪崩。当时我们第一步做的事情不是急着改代码而是先进数据库看清楚到底在执行什么。SHOW FULL PROCESSLIST;这条命令非常关键。它能列出当前所有数据库连接正在运行的SQL、执行状态和执行时间。我看到的现象是大量会话的State是Sending dataTime都在几十秒以上Info里是同一个模式的查询语句。这个状态说明SQL确实在执行而不是在等锁那基本可以锁定是查询性能问题。接下来就是去慢查询日志里找那条罪魁祸首。MySQL的慢日志可以直接告诉我们三类信息执行时间、扫描行数、返回行数。当时那条SQL大概是这样的脱敏简化后SELECT o.id, o.order_no, o.amount, c.name FROM user_order o LEFT JOIN user_customer c ON o.customer_id c.id WHERE DATE(o.create_time) 2024-05-01 ORDER BY o.create_time DESC LIMIT 200000, 20;这个SQL看起来也不复杂但它同时踩了好几个坑对create_time用了DATE()函数、左连接、深分页、还有排序。随手用EXPLAIN一看type是ALLrows是几十万行Extra里明显出现了Using filesort和Using temporary。数据库要把几十万行数据放进临时表排序再扔掉前20万行取20行不慢才怪。1.2 根因复盘一行代码怎么拖垮一个系统先拆第一个坑WHERE DATE(o.create_time) 2024-05-01。这是一个非常经典的索引失效写法。如果o表在create_time上有索引MySQL执行查询时本来可以借助B树索引快速定位到指定时间范围内的一批数据。但一旦把列用函数包起来等于是把原来的值变换成了另一个值索引里存的是原始时间值MySQL无法拿变换后的结果去搜索原始索引条目只能退化成全表扫描。这个原理我们后面会详细说。第二个坑是深分页。LIMIT 200000, 20对MySQL来说意味着查出前200020行然后扔掉前200000行只返回最后20行。如果查询本身是全表扫描那这些数据都要进内存处理如果查询走了索引那就意味着有20万次回表动作。分页越深这个代价越大。第三个坑是排序。ORDER BY o.create_time DESC在没有合适的索引支撑时MySQL会把结果集装进排序缓冲区如果放不下就会在磁盘上建临时文件做归并排序也就是Extra里看到的Using filesort。这个动作对IO和CPU都是实打实的消耗。复盘会上我们定责时说得很直接这不是某个人的粗心而是整个流程缺了一道SQL评审关卡。一条慢SQL进到线上说明开发、测试、上线前的review都没有人真正去看过执行计划。也就是从那天起我们把新SQL上线前必须看EXPLAIN写进了团队规范。2. 慢SQL的底层原因索引、回表、临时文件与锁等待要真正理解SQL优化得先搞清楚一个问题一条SQL是怎么变慢的我比较喜欢用查电话本来打比方。全表扫描就像是把一本厚厚的电话本从头翻到尾只为了找一个姓张的人哪怕你明明知道电话本第一页就印着姓氏索引。索引的作用就是让你不用翻完一整本直接按字母区间跳着翻。2.1 为什么是全表扫描而不是等待数据库的慢很多时候不是卡住了而是工作量太大。InnoDB的数据是存在磁盘上的读写按页进行一页默认16KB。全表扫描意味着要把一个表涉及的所有数据页都读进Buffer Pool处理几十万行数据在磁盘上可能是几万个数据页。磁盘IO的耗时单位是毫秒内存操作是微秒甚至纳秒差了三个数量级。所以一条SQL扫描的行数越多消耗的IO资源就越多数据库整体的吞吐量就被拖垮了。索引的作用本质上就是减少扫描的数据页数量。InnoDB的索引基于B树叶子节点之间用链表串起来。范围查询只要找到起点叶子节点然后顺着链表一路往后读就行不需要把整棵树都走一遍这是B树相比二叉树和哈希表的核心优势也是面试里特别爱考的点。2.2 回表、覆盖索引和二级索引InnoDB里主键索引的叶子节点直接存放完整行数据这种索引也叫聚簇索引。而其他索引二级索引的叶子节点存放的是主键值。也就是说如果你在name字段上建了索引执行SELECT * FROM user WHERE name张三时MySQL先从name索引找到对应的主键id再用主键id去主键索引里找完整行这一步就叫回表。回表是不是一定很慢不一定但会额外增加随机IO。因为二级索引的叶子节点和主键索引的数据页在磁盘上往往不是挨着的每回表一次就可能触发一次磁盘读。数据量小的时候感觉不明显数据量一大回表次数就是扫描行数IO压力一下子就上去了。所以在设计索引和写SQL时要考虑覆盖索引让查询需要的所有列都包含在同一个二级索引里这样连回表都可以省掉。比如SELECT id, name FROM user WHERE name 张三;如果只建了idx_name(name)MySQL查到name为张三的记录后还需要回表拿id。但如果建的是联合索引idx_name_id(name, id)这时候索引里既有name又有idMySQL直接遍历二级索引就足够返回结果Extra里会显示Using index连数据页都不用回。这个细节在面试里展开讲会显得你是真的懂InnoDB而不是背概念。2.3 索引失效的七宗罪我把平时最常见的索引失效场景整理成了一张表排查慢SQL时按这张表逐项对照效率会高很多。场景为什么失效改写建议WHERE DATE(col) ?函数改变了列值索引无法按原值定位改写为col ? and col ?的区间查询WHERE col like %abc前导通配符导致无法从索引起点开始扫描改为col like abc%WHERE mobile 13800138000列是varchar传入数值发生隐式类型转换应用层或SQL里显式传字符串WHERE col 1 2索引列参与运算破坏原值比对改为WHERE col 1WHERE a 1 OR b 2OR中只要有一列没有索引就可能全表扫描拆成两个SQL用UNION ALL合并WHERE a 1 AND b 2但索引是(b, a)不满足最左前缀原则调整索引顺序或调整查询条件NOT IN (2, 3)优化器评估后可能放弃索引按业务场景改写为LEFT JOIN排除两表JOIN的关联字段字符集不同隐式字符集转换导致索引失效统一为相同字符集和排序规则2.4 长事务、锁等待和MySQL的隐性慢还有一种慢不是查询本身慢而是查询在等待锁。这就涉及到事务隔离级别和锁机制。一个事务如果迟迟不提交它持有的行锁就不会释放其他事务更新同一批行时就会一直在锁等待队列里排队。你看到的状态可能是Waiting for lock但业务层感知到的就是接口一直在转圈。更隐蔽的是长事务会把undo日志区拖得很大历史版本不能及时清理导致数据页里的旧版本过多普通查询在MVCC判断上也要消耗更多资源。所以SQL性能优化不只是看SELECT还要看事务边界有没有合理提交大批量更新有没有拆批。这些点面试官常挂在嘴上因为实际生产环境里锁等待引发的慢一点都不比全表扫描少。3. 可落地的SQL优化策略从执行计划到改写了解原理之后接下来是真正动手的部分。我一般把优化策略分成四层按优先级来先看执行计划找问题再改写SQL再调整索引最后审视事务与表结构。顺序不能反因为有些SQL问题不是加索引能解决的得先改写。3.1 EXPLAIN读出执行计划优化任何一条SQL第一步永远是EXPLAIN。它告诉你MySQL这个查询计划里会从哪张表开始、用哪个索引、预估扫描多少行、有没有排序和临时表。EXPLAIN SELECT o.id, o.order_no, o.amount, c.name FROM user_order o LEFT JOIN user_customer c ON o.customer_id c.id WHERE o.create_time 2024-05-01 00:00:00 AND o.create_time 2024-05-02 00:00:00 ORDER BY o.create_time DESC LIMIT 20;我重点看这几个字段type访问类型。从好到差大概是system const eq_ref ref range index ALL。只要见到ALL基本可以判定这条SQL需要优化。key实际用到的索引。如果显示NULL说明没走索引。rows优化器预估需要扫描的行数。这个数字结合真实数据量能判断SQL量级。Extra这里信息量最大。出现Using filesort说明排序没走索引出现Using temporary说明要建临时表出现Using index说明是覆盖索引扫描是好事。EXPLAIN的结果是优化器基于统计信息估算出来的不一定等于实际执行情况但它足够帮我们定位问题方向。3.2 SQL改写把复杂度从数据库挪走这里讲几个我反复用的改写套路。第一函数包裹列的改写。像DATE(create_time)这种正确写法是转成区间条件WHERE create_time 2024-05-01 00:00:00 AND create_time 2024-05-02 00:00:00注意右边是小于下一天零点这样才能把5月1日全天数据完整包括进来而且create_time上的索引可以走range。第二OR条件的改写。如果OR的多个条件只有一部分能走索引优化器可能直接全表扫。处理办法是拆开再合并SELECT ... WHERE user_id 1 UNION ALL SELECT ... WHERE order_status 2;前提是两个子查询各自都能用好索引再考虑这样改。第三深分页用延迟关联。分页越深直接LIMIT的代价越大。延迟关联的思路是先只查主键id再回原表取完整数据SELECT o.id, o.order_no, o.amount FROM user_order o INNER JOIN ( SELECT id FROM user_order WHERE create_time 2024-05-01 00:00:00 AND create_time 2024-05-02 00:00:00 ORDER BY create_time DESC LIMIT 200000, 20 ) tmp ON o.id tmp.id ORDER BY o.create_time DESC;这样子查询里只查主键MySQL可以用索引覆盖完成扫描和排序扫描行数和回表压力都小很多。第四IN和EXISTS的选择。很多人以为IN一定比EXISTS快其实不完全对。核心原则是让结果集小的一方作为外部驱动。比如外层表小、内层表大用IN可以让优化器选择驱动表外层表大、内层表小用EXISTS或者JOIN可能更合适。但现在的优化器已经做了很多改写具体情况还是要以EXPLAIN为准。3.3 索引设计不是越多越好而是越准越好索引是双刃剑查询快但写入要同步维护索引树索引太多会拖慢INSERT和UPDATE。所以索引设计要考虑两个问题建在哪建几个。一个很实用的经验是先评估列的选择性SELECT COUNT(DISTINCT col) / COUNT(*) FROM table;这个值越高说明这一列区分度越好越适合做索引。比如性别列算出来大概是2/N几乎没区分度做索引对查询帮助有限订单号这种唯一性强的列选择性接近1建索引收益最大。联合索引的字段顺序也很有讲究。经验法则是等值条件的字段放前面范围条件的字段放后面。因为联合索引最左前缀的原则范围条件之后的字段就没法继续用来匹配索引了这个顺序一旦建错SQL再怎么调都白搭。还要小心冗余索引。比如你已经建了联合索引(user_id, status)又单独建了(user_id)索引。联合索引最左前缀本身就能覆盖user_id的查询场景单列索引就成了纯浪费。我见过不少表光索引文件比数据文件还大就是冗余索引叠出来的。3.4 事务与锁的优化前面提过长事务的危害这里给出可操作的建议大批量更新/删除一定要分批比如一次UPDATE ... WHERE status0 LIMIT 1000循环提交避免事务长时间持锁。使用SELECT ... FOR UPDATE时只锁需要处理的行不要为了一时的方便把条件写宽。另外如果某条SQL经常处在Waiting for lock状态先排查是否有慢SQL把事务拖住了而不是急着调这条等待SQL本身。优化顺序应该是先消灭锁的源头再优化排队者。4. 面试官真正想听到的答案高频面试题与答题框架带过不少候选人之后我发现很多人对SQL优化的回答停留在加索引避免select *这种表面话。面试官最怕的也是这种答案。真正有区分度的回答是能讲清楚为什么慢并且带着一个完整的排查和验证闭环。4.1 为什么B树适合做索引这个问题几乎是MySQL面试的必考题。建议从为什么不是哈希、为什么不是二叉树来答。哈希索引只能做等值匹配做不了范围查询也做不了排序普通二叉树在数据量大的时候高度太高最坏情况退化成链表B树虽然矮但内部节点既存索引又存数据存储空间有限范围查询时中序遍历也不是很顺畅。B树把数据都放到叶子节点内部节点只存键值所以同一个16KB页能容纳更多索引条目树更矮查询路径上磁盘IO次数更少。而且叶子节点之间用链表串联对范围查询和排序非常友好。回答到这里面试官一般会认可你的理解深度。4.2 联合索引最左前缀到底是怎么来的这个问题的核心不是背有最左前缀原则这一句而是要讲清楚联合索引的存储结构。联合索引(a, b)在B树里先按a排序a相等时再按b排序。这就决定了查询条件里如果没有a索引的排序结构对b的定位没有任何帮助也就没法使用这个索引。延伸回答可以加一条尽量把查询里最常出现的等值条件放在联合索引左边这样索引利用率最高。能讲到这一层说明你真的理解索引的排列机制。4.3 count(*)、order by、group by为什么慢count(*)在InnoDB里慢是因为InnoDB必须扫描实际行数做统计MVCC导致它无法像MyISAM那样直接保存一个行数变量。优化方案有几种业务精确计数用一个独立的计数表不要求精确时用SHOW TABLE STATUS的估算值或者单独维护一张汇总表。order by出现Using filesort时优先看排序字段能不能被现有索引覆盖。group by出现Using temporary时同样要检查分组字段是否满足最左前缀或者考虑拆分成多条聚合SQL再合并结果。4.4 一道综合面试题这个SQL怎么优化面试里最高频的题型是给你一张表结构、一段SQL让你说优化思路。这里列一个典型题目场景订单表user_order有3个索引主键id、单列索引user_id、联合索引(user_id, create_time)。SQL如下SELECT * FROM user_order WHERE user_id 123 ORDER BY create_time DESC LIMIT 200000, 20;正常回答思路是先EXPLAIN确认执行计划是否完全用到(user_id, create_time)这个联合索引。这个联合索引刚好满足user_id等值查询同时create_time已经是索引前缀的一部分排序可以走索引避免filesort。然后指出真正的瓶颈是深分页建议改成延迟关联。最后补充一句光改SQL不行我还要看这张表的行数和真实数据分布再用优化前后EXPLAIN对比确认。这样的回答就非常加分因为它展示了完整的实战思维。5. 生产环境优化流程闭环定位、验证、归档很多人在本地改SQL很快一上生产就露馅原因是缺少一套可验证的优化流程。优化SQL的产出是什么不是我认为快了而是有监控数据支撑的确实快了。5.1 用慢日志定位目标MySQL的慢查询日志是排障第一工具。配置建议是在my.cnf里开启慢日志并设置阈值slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 1long_query_time别设成0否则所有SQL都进日志数据量太大反而无法辨别。线上一般设置1秒业务比较敏感的场景可以再调小。慢日志分析有几个手段。我比较常用的是把慢日志导出后按执行次数、总耗时排序找出Top N。也可以用现成的日志分析工具但核心指标都是一样的平均执行时间、最大执行时间、扫描行数、返回行数。5.2 EXPLAIN、Profiling与性能对比优化之前先保留基线数据执行时间、扫描行数、Extra里的关键标志。优化完再跑一次EXPLAIN看type是否从ALL变成range或者refrows是否大幅下降Extra里的filesort和temporary是否消失。MySQL 8.0以上版本我更喜欢用EXPLAIN ANALYZE它能给出实际执行时间和实际行数比单纯EXPLAIN的预估值更接近真实情况EXPLAIN ANALYZE SELECT ... ;输出里会有每个步骤的实际耗时和行数定位具体慢在哪一步非常方便。5.3 上线监控与回滚SQL改写一般可以直接走代码发布流程风险不大但索引变更要格外小心。大表上加索引不能直接执行普通的ADD INDEX否则可能把表锁住影响线上写入。要用在线DDL工具比如改名替换的方式在低峰期执行执行完观察主从延迟。灰度验证也是必要的。优化上线的第一个小时重点看数据库CPU、慢查询数量、接口P99这三个指标。如果指标不降反升说明优化器选择或者数据分布和预期不符要有快速回滚方案。5.4 把SQL评审变成习惯我现在的团队已经形成了一套固定动作新功能开发涉及SQL必须把EXPLAIN结果贴进代码评审记录里出现typeALL、Using filesort、Using temporary这三个标志之一必须说明优化方案。这套动作看起来繁琐但能从源头拦住大部分事故。6. 踩坑实录三个让我印象深刻的案例最后分享几个我真实碰到过的坑都是那种当时觉得没问题生产环境给了一巴掌的类型。6.1 字符集不一致导致JOIN索引失效之前做过一个统计需求订单表关联用户表两边id列都是bigint表里数据量都不大但查询就是慢。EXPLAIN看订单表走的是全表扫描用户表用的关联也走了全表。折腾了半天才发现两张表的字符集不一样一个是utf8mb4一个是utf8mb3。MySQL做JOIN时为了比较两个不同字符集的列会对其中一个列做隐式转换索引也就失效了。统一字符集后查询秒回。所以字符集一致性这件事建表的时候一定要定死。6.2 加了索引反而更慢还有一次一个查询原本走某条索引我为了优化它加了一个新索引结果执行时间反而更长了。原因是优化器根据统计信息判断新索引的选择性不如原来的选来选去选了个不好的执行计划。最终解决方式是删掉那个新索引问题就恢复了。这让我学到一个教训索引不是越多越好上线前一定要用EXPLAIN确认优化器真的会选它不要想当然认为加了索引就会走。6.3 深分页的连环效应某管理后台的导出功能每次点到后面几页就越来越慢。一开始只调了LIMIT以为减少返回行数就够了。后来发现导出的SQL是LIMIT 500000, 100MySQL为了这100行先要扫描排序50万行。改成延迟关联后同样的分页位置查询从3秒降到100毫秒左右。从那以后我只要看到分页一深第一反应就是重写成子查询先取id。做MySQL优化这几年我最大的体会是性能优化不是炫技是常识加上证据。每次有人问我最快的学习路径我都说把你们生产环境的慢日志拉出来挑三条最慢的SQL用EXPLAIN拆一遍再按这里说的方法改写一遍最后用监控数据验证一遍。做完这三条你对SQL性能优化的理解会超过大多数只背面试题的人。面试被问到的时候讲一段自己真实的定位和优化经历远比背十个答案更有说服力。 
长河君 · 2026/10/18 5:34:52 · 阅读 1.2k MySQL慢SQL优化：从索引失效到深分页的完整排查指南
先讲一个我印象特别深的线上事故。某个订单系统的列表接口某天下午开始P99从80ms直接飙到12秒数据库CPU跑到92%连接数被打满紧接着应用服务大面积超时用户点什么都转圈。当时值班的同事一边把接口降级保核心链路一边翻慢查询日志最后揪出一条看起来很普通的SQL查订单列表where条件里对下单时间字段做了函数包裹索引彻底失效加上分页翻到了几千行之后回表成本直接按几十万行算。等我们把函数条件改写成区间查询、把深分页改成延迟关联接口才从12秒回到70ms。那次事故之后我一直在想MySQL SQL语句性能优化这件事最值钱的地方不是背几个索引失效口诀而是能把为什么慢、怎么定位、怎么写才快、面试怎么答这条线完整串起来。这篇文章我就按这个链路来讲适合后端开发、准DBA、以及正在准备数据库面试的同学。内容会尽量贴合实际生产场景每一步都有可复现的依据。1. 一条慢SQL引发的线上事故从发现到定责1.1 那晚的告警与排障动作先说故障表现。监控大屏上最先报警的是数据库CPU使用率从平时的20%以内直接打到90%以上紧接着活跃连接数暴涨。MySQL的连接数是有上限的默认max_connections通常只有几百当大量查询都卡住不出结果时新来的请求根本拿不到连接应用层就会跟着出问题表现就是接口超时和雪崩。当时我们第一步做的事情不是急着改代码而是先进数据库看清楚到底在执行什么。SHOW FULL PROCESSLIST;这条命令非常关键。它能列出当前所有数据库连接正在运行的SQL、执行状态和执行时间。我看到的现象是大量会话的State是Sending dataTime都在几十秒以上Info里是同一个模式的查询语句。这个状态说明SQL确实在执行而不是在等锁那基本可以锁定是查询性能问题。接下来就是去慢查询日志里找那条罪魁祸首。MySQL的慢日志可以直接告诉我们三类信息执行时间、扫描行数、返回行数。当时那条SQL大概是这样的脱敏简化后SELECT o.id, o.order_no, o.amount, c.name FROM user_order o LEFT JOIN user_customer c ON o.customer_id c.id WHERE DATE(o.create_time) 2024-05-01 ORDER BY o.create_time DESC LIMIT 200000, 20;这个SQL看起来也不复杂但它同时踩了好几个坑对create_time用了DATE()函数、左连接、深分页、还有排序。随手用EXPLAIN一看type是ALLrows是几十万行Extra里明显出现了Using filesort和Using temporary。数据库要把几十万行数据放进临时表排序再扔掉前20万行取20行不慢才怪。1.2 根因复盘一行代码怎么拖垮一个系统先拆第一个坑WHERE DATE(o.create_time) 2024-05-01。这是一个非常经典的索引失效写法。如果o表在create_time上有索引MySQL执行查询时本来可以借助B树索引快速定位到指定时间范围内的一批数据。但一旦把列用函数包起来等于是把原来的值变换成了另一个值索引里存的是原始时间值MySQL无法拿变换后的结果去搜索原始索引条目只能退化成全表扫描。这个原理我们后面会详细说。第二个坑是深分页。LIMIT 200000, 20对MySQL来说意味着查出前200020行然后扔掉前200000行只返回最后20行。如果查询本身是全表扫描那这些数据都要进内存处理如果查询走了索引那就意味着有20万次回表动作。分页越深这个代价越大。第三个坑是排序。ORDER BY o.create_time DESC在没有合适的索引支撑时MySQL会把结果集装进排序缓冲区如果放不下就会在磁盘上建临时文件做归并排序也就是Extra里看到的Using filesort。这个动作对IO和CPU都是实打实的消耗。复盘会上我们定责时说得很直接这不是某个人的粗心而是整个流程缺了一道SQL评审关卡。一条慢SQL进到线上说明开发、测试、上线前的review都没有人真正去看过执行计划。也就是从那天起我们把新SQL上线前必须看EXPLAIN写进了团队规范。2. 慢SQL的底层原因索引、回表、临时文件与锁等待要真正理解SQL优化得先搞清楚一个问题一条SQL是怎么变慢的我比较喜欢用查电话本来打比方。全表扫描就像是把一本厚厚的电话本从头翻到尾只为了找一个姓张的人哪怕你明明知道电话本第一页就印着姓氏索引。索引的作用就是让你不用翻完一整本直接按字母区间跳着翻。2.1 为什么是全表扫描而不是等待数据库的慢很多时候不是卡住了而是工作量太大。InnoDB的数据是存在磁盘上的读写按页进行一页默认16KB。全表扫描意味着要把一个表涉及的所有数据页都读进Buffer Pool处理几十万行数据在磁盘上可能是几万个数据页。磁盘IO的耗时单位是毫秒内存操作是微秒甚至纳秒差了三个数量级。所以一条SQL扫描的行数越多消耗的IO资源就越多数据库整体的吞吐量就被拖垮了。索引的作用本质上就是减少扫描的数据页数量。InnoDB的索引基于B树叶子节点之间用链表串起来。范围查询只要找到起点叶子节点然后顺着链表一路往后读就行不需要把整棵树都走一遍这是B树相比二叉树和哈希表的核心优势也是面试里特别爱考的点。2.2 回表、覆盖索引和二级索引InnoDB里主键索引的叶子节点直接存放完整行数据这种索引也叫聚簇索引。而其他索引二级索引的叶子节点存放的是主键值。也就是说如果你在name字段上建了索引执行SELECT * FROM user WHERE name张三时MySQL先从name索引找到对应的主键id再用主键id去主键索引里找完整行这一步就叫回表。回表是不是一定很慢不一定但会额外增加随机IO。因为二级索引的叶子节点和主键索引的数据页在磁盘上往往不是挨着的每回表一次就可能触发一次磁盘读。数据量小的时候感觉不明显数据量一大回表次数就是扫描行数IO压力一下子就上去了。所以在设计索引和写SQL时要考虑覆盖索引让查询需要的所有列都包含在同一个二级索引里这样连回表都可以省掉。比如SELECT id, name FROM user WHERE name 张三;如果只建了idx_name(name)MySQL查到name为张三的记录后还需要回表拿id。但如果建的是联合索引idx_name_id(name, id)这时候索引里既有name又有idMySQL直接遍历二级索引就足够返回结果Extra里会显示Using index连数据页都不用回。这个细节在面试里展开讲会显得你是真的懂InnoDB而不是背概念。2.3 索引失效的七宗罪我把平时最常见的索引失效场景整理成了一张表排查慢SQL时按这张表逐项对照效率会高很多。场景为什么失效改写建议WHERE DATE(col) ?函数改变了列值索引无法按原值定位改写为col ? and col ?的区间查询WHERE col like %abc前导通配符导致无法从索引起点开始扫描改为col like abc%WHERE mobile 13800138000列是varchar传入数值发生隐式类型转换应用层或SQL里显式传字符串WHERE col 1 2索引列参与运算破坏原值比对改为WHERE col 1WHERE a 1 OR b 2OR中只要有一列没有索引就可能全表扫描拆成两个SQL用UNION ALL合并WHERE a 1 AND b 2但索引是(b, a)不满足最左前缀原则调整索引顺序或调整查询条件NOT IN (2, 3)优化器评估后可能放弃索引按业务场景改写为LEFT JOIN排除两表JOIN的关联字段字符集不同隐式字符集转换导致索引失效统一为相同字符集和排序规则2.4 长事务、锁等待和MySQL的隐性慢还有一种慢不是查询本身慢而是查询在等待锁。这就涉及到事务隔离级别和锁机制。一个事务如果迟迟不提交它持有的行锁就不会释放其他事务更新同一批行时就会一直在锁等待队列里排队。你看到的状态可能是Waiting for lock但业务层感知到的就是接口一直在转圈。更隐蔽的是长事务会把undo日志区拖得很大历史版本不能及时清理导致数据页里的旧版本过多普通查询在MVCC判断上也要消耗更多资源。所以SQL性能优化不只是看SELECT还要看事务边界有没有合理提交大批量更新有没有拆批。这些点面试官常挂在嘴上因为实际生产环境里锁等待引发的慢一点都不比全表扫描少。3. 可落地的SQL优化策略从执行计划到改写了解原理之后接下来是真正动手的部分。我一般把优化策略分成四层按优先级来先看执行计划找问题再改写SQL再调整索引最后审视事务与表结构。顺序不能反因为有些SQL问题不是加索引能解决的得先改写。3.1 EXPLAIN读出执行计划优化任何一条SQL第一步永远是EXPLAIN。它告诉你MySQL这个查询计划里会从哪张表开始、用哪个索引、预估扫描多少行、有没有排序和临时表。EXPLAIN SELECT o.id, o.order_no, o.amount, c.name FROM user_order o LEFT JOIN user_customer c ON o.customer_id c.id WHERE o.create_time 2024-05-01 00:00:00 AND o.create_time 2024-05-02 00:00:00 ORDER BY o.create_time DESC LIMIT 20;我重点看这几个字段type访问类型。从好到差大概是system const eq_ref ref range index ALL。只要见到ALL基本可以判定这条SQL需要优化。key实际用到的索引。如果显示NULL说明没走索引。rows优化器预估需要扫描的行数。这个数字结合真实数据量能判断SQL量级。Extra这里信息量最大。出现Using filesort说明排序没走索引出现Using temporary说明要建临时表出现Using index说明是覆盖索引扫描是好事。EXPLAIN的结果是优化器基于统计信息估算出来的不一定等于实际执行情况但它足够帮我们定位问题方向。3.2 SQL改写把复杂度从数据库挪走这里讲几个我反复用的改写套路。第一函数包裹列的改写。像DATE(create_time)这种正确写法是转成区间条件WHERE create_time 2024-05-01 00:00:00 AND create_time 2024-05-02 00:00:00注意右边是小于下一天零点这样才能把5月1日全天数据完整包括进来而且create_time上的索引可以走range。第二OR条件的改写。如果OR的多个条件只有一部分能走索引优化器可能直接全表扫。处理办法是拆开再合并SELECT ... WHERE user_id 1 UNION ALL SELECT ... WHERE order_status 2;前提是两个子查询各自都能用好索引再考虑这样改。第三深分页用延迟关联。分页越深直接LIMIT的代价越大。延迟关联的思路是先只查主键id再回原表取完整数据SELECT o.id, o.order_no, o.amount FROM user_order o INNER JOIN ( SELECT id FROM user_order WHERE create_time 2024-05-01 00:00:00 AND create_time 2024-05-02 00:00:00 ORDER BY create_time DESC LIMIT 200000, 20 ) tmp ON o.id tmp.id ORDER BY o.create_time DESC;这样子查询里只查主键MySQL可以用索引覆盖完成扫描和排序扫描行数和回表压力都小很多。第四IN和EXISTS的选择。很多人以为IN一定比EXISTS快其实不完全对。核心原则是让结果集小的一方作为外部驱动。比如外层表小、内层表大用IN可以让优化器选择驱动表外层表大、内层表小用EXISTS或者JOIN可能更合适。但现在的优化器已经做了很多改写具体情况还是要以EXPLAIN为准。3.3 索引设计不是越多越好而是越准越好索引是双刃剑查询快但写入要同步维护索引树索引太多会拖慢INSERT和UPDATE。所以索引设计要考虑两个问题建在哪建几个。一个很实用的经验是先评估列的选择性SELECT COUNT(DISTINCT col) / COUNT(*) FROM table;这个值越高说明这一列区分度越好越适合做索引。比如性别列算出来大概是2/N几乎没区分度做索引对查询帮助有限订单号这种唯一性强的列选择性接近1建索引收益最大。联合索引的字段顺序也很有讲究。经验法则是等值条件的字段放前面范围条件的字段放后面。因为联合索引最左前缀的原则范围条件之后的字段就没法继续用来匹配索引了这个顺序一旦建错SQL再怎么调都白搭。还要小心冗余索引。比如你已经建了联合索引(user_id, status)又单独建了(user_id)索引。联合索引最左前缀本身就能覆盖user_id的查询场景单列索引就成了纯浪费。我见过不少表光索引文件比数据文件还大就是冗余索引叠出来的。3.4 事务与锁的优化前面提过长事务的危害这里给出可操作的建议大批量更新/删除一定要分批比如一次UPDATE ... WHERE status0 LIMIT 1000循环提交避免事务长时间持锁。使用SELECT ... FOR UPDATE时只锁需要处理的行不要为了一时的方便把条件写宽。另外如果某条SQL经常处在Waiting for lock状态先排查是否有慢SQL把事务拖住了而不是急着调这条等待SQL本身。优化顺序应该是先消灭锁的源头再优化排队者。4. 面试官真正想听到的答案高频面试题与答题框架带过不少候选人之后我发现很多人对SQL优化的回答停留在加索引避免select *这种表面话。面试官最怕的也是这种答案。真正有区分度的回答是能讲清楚为什么慢并且带着一个完整的排查和验证闭环。4.1 为什么B树适合做索引这个问题几乎是MySQL面试的必考题。建议从为什么不是哈希、为什么不是二叉树来答。哈希索引只能做等值匹配做不了范围查询也做不了排序普通二叉树在数据量大的时候高度太高最坏情况退化成链表B树虽然矮但内部节点既存索引又存数据存储空间有限范围查询时中序遍历也不是很顺畅。B树把数据都放到叶子节点内部节点只存键值所以同一个16KB页能容纳更多索引条目树更矮查询路径上磁盘IO次数更少。而且叶子节点之间用链表串联对范围查询和排序非常友好。回答到这里面试官一般会认可你的理解深度。4.2 联合索引最左前缀到底是怎么来的这个问题的核心不是背有最左前缀原则这一句而是要讲清楚联合索引的存储结构。联合索引(a, b)在B树里先按a排序a相等时再按b排序。这就决定了查询条件里如果没有a索引的排序结构对b的定位没有任何帮助也就没法使用这个索引。延伸回答可以加一条尽量把查询里最常出现的等值条件放在联合索引左边这样索引利用率最高。能讲到这一层说明你真的理解索引的排列机制。4.3 count(*)、order by、group by为什么慢count(*)在InnoDB里慢是因为InnoDB必须扫描实际行数做统计MVCC导致它无法像MyISAM那样直接保存一个行数变量。优化方案有几种业务精确计数用一个独立的计数表不要求精确时用SHOW TABLE STATUS的估算值或者单独维护一张汇总表。order by出现Using filesort时优先看排序字段能不能被现有索引覆盖。group by出现Using temporary时同样要检查分组字段是否满足最左前缀或者考虑拆分成多条聚合SQL再合并结果。4.4 一道综合面试题这个SQL怎么优化面试里最高频的题型是给你一张表结构、一段SQL让你说优化思路。这里列一个典型题目场景订单表user_order有3个索引主键id、单列索引user_id、联合索引(user_id, create_time)。SQL如下SELECT * FROM user_order WHERE user_id 123 ORDER BY create_time DESC LIMIT 200000, 20;正常回答思路是先EXPLAIN确认执行计划是否完全用到(user_id, create_time)这个联合索引。这个联合索引刚好满足user_id等值查询同时create_time已经是索引前缀的一部分排序可以走索引避免filesort。然后指出真正的瓶颈是深分页建议改成延迟关联。最后补充一句光改SQL不行我还要看这张表的行数和真实数据分布再用优化前后EXPLAIN对比确认。这样的回答就非常加分因为它展示了完整的实战思维。5. 生产环境优化流程闭环定位、验证、归档很多人在本地改SQL很快一上生产就露馅原因是缺少一套可验证的优化流程。优化SQL的产出是什么不是我认为快了而是有监控数据支撑的确实快了。5.1 用慢日志定位目标MySQL的慢查询日志是排障第一工具。配置建议是在my.cnf里开启慢日志并设置阈值slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 1long_query_time别设成0否则所有SQL都进日志数据量太大反而无法辨别。线上一般设置1秒业务比较敏感的场景可以再调小。慢日志分析有几个手段。我比较常用的是把慢日志导出后按执行次数、总耗时排序找出Top N。也可以用现成的日志分析工具但核心指标都是一样的平均执行时间、最大执行时间、扫描行数、返回行数。5.2 EXPLAIN、Profiling与性能对比优化之前先保留基线数据执行时间、扫描行数、Extra里的关键标志。优化完再跑一次EXPLAIN看type是否从ALL变成range或者refrows是否大幅下降Extra里的filesort和temporary是否消失。MySQL 8.0以上版本我更喜欢用EXPLAIN ANALYZE它能给出实际执行时间和实际行数比单纯EXPLAIN的预估值更接近真实情况EXPLAIN ANALYZE SELECT ... ;输出里会有每个步骤的实际耗时和行数定位具体慢在哪一步非常方便。5.3 上线监控与回滚SQL改写一般可以直接走代码发布流程风险不大但索引变更要格外小心。大表上加索引不能直接执行普通的ADD INDEX否则可能把表锁住影响线上写入。要用在线DDL工具比如改名替换的方式在低峰期执行执行完观察主从延迟。灰度验证也是必要的。优化上线的第一个小时重点看数据库CPU、慢查询数量、接口P99这三个指标。如果指标不降反升说明优化器选择或者数据分布和预期不符要有快速回滚方案。5.4 把SQL评审变成习惯我现在的团队已经形成了一套固定动作新功能开发涉及SQL必须把EXPLAIN结果贴进代码评审记录里出现typeALL、Using filesort、Using temporary这三个标志之一必须说明优化方案。这套动作看起来繁琐但能从源头拦住大部分事故。6. 踩坑实录三个让我印象深刻的案例最后分享几个我真实碰到过的坑都是那种当时觉得没问题生产环境给了一巴掌的类型。6.1 字符集不一致导致JOIN索引失效之前做过一个统计需求订单表关联用户表两边id列都是bigint表里数据量都不大但查询就是慢。EXPLAIN看订单表走的是全表扫描用户表用的关联也走了全表。折腾了半天才发现两张表的字符集不一样一个是utf8mb4一个是utf8mb3。MySQL做JOIN时为了比较两个不同字符集的列会对其中一个列做隐式转换索引也就失效了。统一字符集后查询秒回。所以字符集一致性这件事建表的时候一定要定死。6.2 加了索引反而更慢还有一次一个查询原本走某条索引我为了优化它加了一个新索引结果执行时间反而更长了。原因是优化器根据统计信息判断新索引的选择性不如原来的选来选去选了个不好的执行计划。最终解决方式是删掉那个新索引问题就恢复了。这让我学到一个教训索引不是越多越好上线前一定要用EXPLAIN确认优化器真的会选它不要想当然认为加了索引就会走。6.3 深分页的连环效应某管理后台的导出功能每次点到后面几页就越来越慢。一开始只调了LIMIT以为减少返回行数就够了。后来发现导出的SQL是LIMIT 500000, 100MySQL为了这100行先要扫描排序50万行。改成延迟关联后同样的分页位置查询从3秒降到100毫秒左右。从那以后我只要看到分页一深第一反应就是重写成子查询先取id。做MySQL优化这几年我最大的体会是性能优化不是炫技是常识加上证据。每次有人问我最快的学习路径我都说把你们生产环境的慢日志拉出来挑三条最慢的SQL用EXPLAIN拆一遍再按这里说的方法改写一遍最后用监控数据验证一遍。做完这三条你对SQL性能优化的理解会超过大多数只背面试题的人。面试被问到的时候讲一段自己真实的定位和优化经历远比背十个答案更有说服力。