MySQL SQL优化实战：索引失效、执行计划与慢查询排查指南

接手过不少线上数据库的锅，十次里有八次最后都落在一条SQL头上。页面卡、接口超时、凌晨的告警短信，追根溯源大概率是一条没走索引的大查询，或者一个排序排到磁盘上的order by。MySQL的SQL优化，说白了就是跟引擎商量着来：让它少翻数据、少做无谓计算、把该走的索引走起来。这篇是我自己整理的学习笔记第4篇，专门聊SQL优化这块。适合刚接触索引概念、写SQL但没深究过执行计划的同学，也适合被慢查询困扰、想系统梳理一遍优化思路的人。我会从思路到实操，把索引设计、执行计划、写法细节这些硬骨头一个个啃下来。

1. 优化前的思路梳理

1.1 先搞清楚慢在哪一步

很多人一上来就急着改SQL，看到慢查询日志里有条查询耗时2秒，立刻给它加个索引。结果加完发现一点用没有，为什么？因为你根本没搞清楚这2秒花在哪。MySQL执行一条查询，时间主要消耗在几个环节：网络传输、语法解析、查询优化器生成执行计划、存储引擎扫描数据、排序分组、返回结果集。大多数情况下瓶颈都在“扫描数据”和“排序”这两步。

所以我的习惯是，先看执行计划，再决定改哪里。用EXPLAIN把SQL过一遍，看它走的什么索引、扫了多少行、有没有filesort、有没有回表。这些信息都摆在明面上，比瞎猜靠谱得多。还有一种情况是线上环境不方便直接EXPLAIN，那就开慢查询日志，把超过阈值（比如1秒）的SQL抓出来，再逐条分析。慢查询日志默认是关的，需要手动打开，具体参数我后面会提到。

还有一个很容易被忽略的点：应用层的慢。有时候SQL本身一秒都不到，但接口响应花了3秒，问题出在连接池不够、网络往返太多、或者反复查询同一份数据。这种情况你再怎么优化SQL也白搭。所以定位慢，要先分清是“SQL慢”还是“整个调用链慢”，别一股脑把锅扣给数据库。

1.2 优化原则：先定位、再分析、后改动

我给自己定了个三条铁律，这几年靠它们少踩了很多坑。第一，只优化真正慢的SQL。别逮着一条执行只要5毫秒的查询使劲折腾，收益为零，还容易引入新问题。优化的前提是有明确的性能痛点，最好有慢查询日志或者监控数据作支撑。第二，用数据说话。优化前记录耗时、扫描行数，优化后再对比一次，让效果看得见。我习惯把EXPLAIN的结果和实际执行时间一起截图存档，方便后来复盘。第三，小步快跑，一次只改一个变量。加了索引就别同时改SQL写法，否则出了问题你根本不知道是哪一步导致的。

在这三条基础上，大部分SQL优化都可以按一个固定套路走：先用慢查询日志定位目标SQL，再用EXPLAIN看执行计划，接着判断是索引问题、写法问题还是表结构问题，然后针对性调整，最后回归测试。这套流程说起来简单，但每一步都有细节，后面几个章节我会逐个展开。记住，SQL优化不是玄学，它是一套有依据、可验证的方法论。

2. 索引：SQL优化的一等功臣

2.1 主键索引和唯一索引到底啥区别

面试的时候这个问题高频到不行，实际工作中也经常有人搞混。先明确一点：主键索引和唯一索引都是“唯一性约束+索引”的结合体，但两者有本质区别。

主键索引是聚集索引，也就是说InnoDB表的数据行本身就是按主键顺序物理存储的。一张表只能有一个主键，主键列不允许为NULL。你建了主键，整张表的数据就按这个键的物理顺序排布，查询用主键找数据是最快的路径，直接定位到对应数据页，不需要额外跳转。

唯一索引则是非聚集索引，它只是保证列值不重复，允许有一个或多个NULL值（MySQL里唯一索引对NULL的处理是“多个NULL是允许的”，因为NULL != NULL）。唯一索引对应的数据是独立的索引结构，叶子节点存的是主键值，查询时先查索引树，再通过主键回表去拿完整行数据，多一步回表开销。

简单总结一张表：

实操中要注意：如果业务上确实需要一个唯一键，比如用户表的手机号，我建议“主键用自增id，手机号建唯一索引”。这种设计的好处是主键短小、索引树紧凑，写入性能好；手机号作为唯一索引在查询时虽然多一次回表，但业务上有唯一性校验需求，这个代价是值得的。反过来，如果用手机号直接做主键，数据页物理排序会被随机字符串打乱，插入时频繁页分裂，写性能会明显下降。

2.2 联合索引的最左前缀原则

联合索引是SQL优化里最需要花心思的地方。很多人建索引很随性，看到一个查询条件就建一个单列索引，结果一张表上挂了六七个索引，写入慢、占用空间大，查询还不一定走。真正的做法是，分析业务查询模式，用联合索引覆盖多个查询条件。

联合索引遵循最左前缀原则：查询条件必须命中索引的最左列（或最左列的连续组合），索引才会生效。比如我建了一个(idx_a, idx_b, idx_c)的联合索引，那查询条件里用了idx_a能走索引，用了idx_a和idx_b能走，用了idx_a、idx_b和idx_c也能走，但只用idx_b或者只用idx_c，索引就用不上。

这个原则背后的逻辑其实不难理解。联合索引的B+树排序规则是：先按第一列排序，第一列相同再按第二列排，以此类推。就像查字典，先按拼音首字母排，首字母相同再按第二个字母排。你直接要查第二个字母开头的单词，字典根本无从下手，只能从头翻到尾。

所以在设计联合索引时，列的排列顺序非常重要。我常用的判断标准是：区分度高的列放前面，等值查询的列放前面，范围查询的列放后面。举个例子，一个订单表经常按“用户id + 下单时间范围”查询，那就建(user_id, order_time)联合索引，user_id等值匹配放前，order_time做范围过滤放后。这样既能利用索引快速定位到某个用户的数据，又能在索引内部完成时间范围的筛选，效率是最高的。

2.3 索引失效的典型场景

这部分基本是SQL优化里老生常谈但最容易踩的坑，我把自己踩过和帮别人排查过的场景都列一下。

第一，对索引列使用函数或者表达式。比如WHERE DATE(order_time) = '2025-01-01'，只要对索引列套了函数，MySQL就无法用它去匹配B+树，只能全表扫描。正确写法是WHERE order_time >= '2025-01-01' AND order_time < '2025-01-02'。这个坑非常隐蔽，因为DATE()这种函数看起来人畜无害，但引擎确实用不上索引。

第二，隐式类型转换。比如phone字段是varchar类型，查询写成WHERE phone = 13800138000，MySQL会把字段值转成数字去比较，这就相当于在索引列上做了函数转换，索引失效。同样，字符串和数字的比较、字符集不一致的关联，都可能触发这个问题。排查思路很简单：确认字段类型和查询参数类型一致。

第三，LIKE以通配符开头。WHERE name LIKE '%张%'走不了索引，但WHERE name LIKE '张%'可以。原因还是老一套：前缀开头才能按B+树的顺序去匹配，通配符在开头就破坏了前缀匹配能力。

第四，使用OR连接多个条件，且其中一个条件没有索引。比如WHERE user_id = 123 OR status = 1，如果status没有索引，MySQL只能把两个条件都全表扫一遍再合并，索引就废了。这种情况可以改成UNION ALL，让每个分支各自走索引。

第五，范围查询右侧的列失效。还是那个联合索引(idx_a, idx_b, idx_c)，如果WHERE idx_a = 1 AND idx_b > 100 AND idx_c = 2，那idx_c的等值条件就用不上索引了，因为idx_b的范围条件后面的列无法保持有序匹配。这也是为什么范围列要放后面的原因。

遇到这些情况，我的建议是不要死记硬背，理解B+树的匹配原理比背书管用：只要索引列的有序性在匹配过程中被破坏，索引就会失效。你可以把索引想象成一排按顺序摆好的书架，任何让你直接跳到中间某个位置去找的行为，都必须从头翻。

3. 用EXPLAIN把执行计划翻个底朝天

3.1 慢查询日志怎么配置

定位慢SQL，最直接的手段就是开慢查询日志。MySQL的慢查询日志有几个关键参数，我用8.0版本举例：

-- 查看当前设置 SHOW VARIABLES LIKE 'slow_query_log'; SHOW VARIABLES LIKE 'long_query_time'; -- 开启慢查询日志 SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; -- 超过1秒的记录，线上建议先设0.5测试 SET GLOBAL log_queries_not_using_indexes = 'ON'; -- 记录不走索引的SQL

注意两点：long_query_time的最小粒度是0.001秒，单位为秒；设置之后新开启的连接才生效，已经存在的连接不会用新配置，要重连一下。慢查询日志默认输出到数据目录下的hostname-slow.log文件，也可以用SET GLOBAL slow_query_log_file指定路径。

还有一个工具叫mysqldumpslow，用来汇总分析慢查询日志，按执行次数、耗时排序，能快速找出“最值得优化”的SQL。命令大概是：

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

意思是最耗时的前10条。这个工具对日志的汇总能力很强，能把同模板SQL聚合到一起，比如where条件不同的相同查询会被归为一类。我接手一个新系统时，第一件事就是把慢查询日志开起来，跑几天，再用mysqldumpslow拉个排行，对整个数据库的健康状况就有底了。

3.2 EXPLAIN关键字段逐个看

慢查询定位到了，就可以用EXPLAIN解析。EXPLAIN SELECT ... 不需要真的执行查询，它只输出优化器预估的执行计划。虽然MySQL 8.0还提供了EXPLAIN ANALYZE可以真正执行并输出实际耗时，但日常分析先用EXPLAIN就够了。

重点看这几个字段：

type是访问类型，从好到差大致是：system > const > eq_ref > ref > range > index > all。const和eq_ref意味着用主键或唯一索引精确定位，最快；ref是普通索引等值匹配，也不错；range是范围扫描，可以接受；index是全索引扫描，通常不太好；all是全表扫描，最差。我深更半夜排查线上问题时，最先瞄的就是这个字段，type一旦出现all，基本就是索引没走对。

key表示实际用到的索引，possible_keys是可能用到的索引。有时候possible_keys里有索引但key是NULL，说明优化器评估后认为索引不如全表扫，这时候要检查是不是数据量分布问题，或者是索引失效了。

rows是预估扫描行数，这个数字越小越好。但不是绝对，有时候rows预估有误差，最终看EXPLAIN ANALYZE的实际行数更准。Extra字段信息量巨大，看到Using filesort说明有额外排序，看到Using temporary说明用了临时表，这两项出现，SQL基本慢成定局。看到Using index（覆盖索引）是最理想的，意味着查询的数据直接从索引拿到，连表都不用回。

3.3 一个真实慢SQL的拆解

拿一个我曾经排查过的例子来说。业务反馈某报表页面加载要十几秒，从慢查询日志里捞出来一条：

SELECT order_id, user_id, amount, create_time FROM orders WHERE status = 1 AND create_time >= '2025-01-01' ORDER BY amount DESC LIMIT 20;

EXPLAIN的结果大概长这样：type是all，key是NULL，rows显示80万，Extra里面有Using where和Using filesort。问题一目了然：status和create_time都没有可用索引，80万行全表扫描，且按amount排序走了磁盘排序。

我的优化动作分两步。第一步，创建联合索引(status, create_time)，让条件能走索引，把扫描范围压缩到目标数据。第二步，排序问题，单纯靠(status, create_time)索引解决不了amount的排序，因为索引列里没有amount，排序还是在临时表做。又想了一招：如果业务能接受，排序条件改成create_time（和索引顺序一致），或者把联合索引建成(status, create_time, amount)这种形式，让amount也进索引。

最后方案是建了(status, create_time, amount)联合索引，查询条件走索引，amount直接按索引顺序取，Extra里的filesort消失，执行时间从12秒降到0.3秒。这个案例其实很典型：优化不仅仅是“加索引”，而是让索引结构同时满足过滤条件和排序条件，一步到位。这种思维方式比背几个优化口诀重要得多。

4. SQL写法优化：那些容易忽略的小地方

4.1 ORDER BY排序到底慢在哪

排序是SQL优化里特别容易翻车的一环。MySQL排序有两种实现方式：如果用到的索引天然有序，Extra里不会出现filesort；反之，引擎会把数据先捞出来，再在内存或磁盘上排序。当排序数据量超过sort_buffer_size（默认256KB，8.0设置）时，会落到磁盘上用临时文件排序，那性能会断崖式下降。

排序优化最有效的思路，就是让排序走索引。比如业务常用“按用户查最近订单并按时间倒序”，如果表上有(user_id, create_time)联合索引，那么ORDER BY create_time DESC就能直接利用索引有序性，因为同一个user_id下的create_time是天然有序的。没有这个索引，MySQL就得把用户的所有订单捞出来，再内存排序，甚至磁盘排序。

另外，SELECT的字段如果太多、太大，排序时也会受影响。因为MySQL排序时可能要把整行数据放进sort buffer，字段越多占空间越多，缓冲更容易被撑爆。优化手法是只SELECT需要的字段，或者用“先查主键，再回表取详情”的延迟关联方式。这些都算是排序优化的常见操作。

4.2 深分页优化：limit offset为何越翻越慢

分页查询人人都写过，但深分页的坑未必人人知道。LIMIT 100000, 20这种写法，MySQL不是只取20条，而是先把前100020条全部扫出来，然后丢掉前10万条，只保留最后20条。数据量大的时候，前几次翻页可能还没感觉，翻到几十页之后，耗时是肉眼可见地上涨。

为什么会这样？因为limit offset在索引上的定位方式是从头开始数，offset越大，扫描的行数越多。即使走了索引，这10万次回表操作也够数据库喝一壶的。

我常用的优化方案有几种。第一种是延迟关联：先通过索引查出主键，再join原表取完整行。因为主键查询时可以先不碰真实数据行，只扫索引树，等确定好20条主键后再统一回表，大大减少随机I/O。

SELECT o.order_id, o.user_id, o.amount FROM orders o INNER JOIN (SELECT order_id FROM orders ORDER BY create_time LIMIT 100000, 20) t ON o.order_id = t.order_id;

第二种是记住上一页的位置，用条件过滤代替offset。比如按order_id排序，上一页最后一条是id=99999，下一页直接查WHERE order_id > 99999 ORDER BY order_id LIMIT 20。这种方案要求排序字段连续且唯一，通常用主键最合适。深分页问题越到数据量大越明显，这两招能解燃眉之急，但要想根治，产品层面限制用户翻页深度才是关键。

4.3 写法层面的坑：习惯比优化更重要

有一类SQL问题，不是引擎不行，是写的人太随意。这里面最常见的就是SELECT *。你以为少打几个字段省事，代价是MySQL要把整行所有列都读出来，即使业务根本不需要。更要命的是，SELECT *加上ORDER BY时，很可能因为包含了超长字段而导致排序缓冲紧张。我见到SELECT *的慢SQL，第一反应就是先把它改成明确字段列表。

还有函数运算的滥用。WHERE year(create_time) = 2025这种写法，在前面提过索引会失效，其实即使没索引，对每行做函数计算也是额外开销。应该改写为范围条件。这个习惯要尽早养成，能直接写范围就别在字段上套函数。

还有一个容易忽略的点：多表JOIN时的驱动顺序。小表驱动大表是一条基本经验，MySQL优化器一般会自动选择，但在复杂查询里它也可能判断失误。遇到这种情况，可以用STRAIGHT_JOIN强制驱动顺序，或者改写查询结构，让过滤条件更明确。不过这个操作要对执行计划很有把握才建议做，否则容易适得其反。

最后说一个很有意思的场景：OR和去重的问题。有热词问“MySQL的or能去重吗”，答案是OR本身不会去重，去重要靠DISTINCT或GROUP BY，而且OR的写法在索引利用上往往也差于UNION或IN。这也是我在实践中特别注意的地方，能用IN代替OR就用IN，能改UNION就不OR。细节养成习惯，日积月累能省下很多事。

5. 优化实践中的问题排查与经验沉淀

5.1 常见问题速查表

把日常运维和开发里高频踩坑整理成一张速查表，遇到类似问题可以对照着看。

这张表只是索引，真正遇到问题还是要回到EXPLAIN和慢查询日志这两条线上去拿证据。

5.2 几条实战经验，希望你能少走弯路

做SQL优化这几年，我有个很深的体会：优化永远不要追求炫技，而要在可控和可维护之间找平衡。比如频繁更新的列不适合加太多索引，因为每次UPDATE都要同步维护索引树，写入成本很高。还有，不要为了某一条低频SQL去建冗余索引，一张表的索引数量控制在5个以内比较健康，多了就是负担。

还有一个建议是，优化完必须做回归验证。有一次我加了个索引让查询变快了，结果发现更新那个高频表的其他语句开始变慢，因为索引维护成本上去了。所以每次优化，除了对比目标SQL的耗时，还要关注周边SQL会不会受影响。线上环境改索引，我一般选低峰期执行，用gh-ost这类工具做在线变更，不影响业务读写。

我也强烈建议大家把慢查询日志作为常态监控的一部分，别等出了故障才去看。平时可以每天看一眼慢查询排行，提前发现那些“正在变慢”的SQL，在它还没变成事故之前把它处理掉。这种预防性的成本，远比事后救火要小得多。SQL优化这件事没有终点，表在长，数据在变，SQL也要持续迭代。把它当成一个持续改进的过程，你的数据库会稳很多。