MySQL慢查询优化：从执行计划到复合索引设计实战-平芜编程栈

1. 一张订单查询耗尽数据库IO：慢问题是这么浮出水面的

事情发生得很突然。某业务系统的订单列表页在下午三点左右开始出现明显的卡顿，页面接口从平均200毫秒涨到了接近4秒，数据库服务器的IO等待指标一路飘红。监控平台上慢查询日志刷得飞快，几乎每秒钟都有几条超过2秒的SQL被记录。我接手这套系统时，第一反应不是改代码，而是先搞清楚一个最基本的问题：这些慢SQL到底是在什么样的数据量级、什么样的索引结构下跑出来的。

查了一圈之后发现，最大的那张订单表已经积累了接近5000万行数据，却不包含任何一个针对查询条件组合设计的索引，只有主键索引和两个单列索引。其中订单状态这个字段的区分度极低，只有几个固定取值，用它单独做索引，选择性太差，优化器大概率会直接放弃走索引而选择全表扫描。

这个场景在真实项目里太常见了。很多团队上线初期数据量小，查询随便写都可以秒回，于是索引设计就变成了“等出问题再补”的事情。等到表膨胀到千万级甚至亿级以后，一条原本毫秒级的查询突然就变成了灾难。

在做任何调优之前，第一步永远是把问题量化。我在这个项目里做的事情顺序是：先打开慢查询日志和全局日志开关，把耗时超过500毫秒的SQL全部捞出来，按执行次数和平均耗时的乘积排序，找出最值得优化的前十个。这一步非常关键，因为数据库的瓶颈往往不是单一SQL造成的，而是大量中等耗时SQL叠加成的IO压力。只盯着最慢的那一条去优化，可能永远无法缓解整体问题。

提示：排查慢SQL时，不要只看单次最慢的语句，要按“次数 x 耗时”来排序。一个执行了一万次、平均300毫秒的查询，远比一个执行了一次、耗时10秒的查询更值得优先优化。

捞出来的SQL里，有一个查询特别典型，它同时关联了订单表、用户表和商品表，并且对订单状态、下单时间范围、用户ID做了三个条件的过滤。原文写法大致如下：

SELECT o.order_id, u.nickname, p.product_name FROM orders o LEFT JOIN users u ON o.user_id = u.user_id LEFT JOIN products p ON o.product_id = p.product_id WHERE o.status = 1 AND o.created_at >= '2024-01-01' AND o.created_at < '2024-02-01' AND u.user_type = 2 ORDER BY o.created_at DESC LIMIT 20;

这条SQL单次执行在那个数据量下需要2.8秒，而接口高峰期每分钟会被调用上百次。三条关联表都没有合适的索引支撑，其中订单表在WHERE条件上毫无索引可用，users表也只在主键上有索引，关联时的user_type过滤没法下推，回表量巨大。

拿到问题SQL之后，我没有急着写CREATE INDEX，而是先做了一件老DBA都会做的事：用EXPLAIN看执行计划。执行计划是数据库告诉你的“我打算怎么跑这条SQL”的说明书，不读说明书就乱加索引，跟不看地图乱开车没有区别。

2. 读懂执行计划里的潜台词：type、key、rows和Extra的联动关系

EXPLAIN输出里，最核心的指标是type、key、rows和Extra。这四个字段组合在一起，能直接告诉你当前SQL是不是在裸奔。

type字段反映了访问类型，最优到最差依次是system、const、eq_ref、ref、range、index、ALL。其中ALL就是全表扫描，是性能最差的情况；index意味着全索引扫描，比全表好一点，但也不算高效；range代表索引范围扫描，这通常是范围查询可以接受的下限；ref和eq_ref则是等值关联时的理想状态。我见过不少开发同学一看到语句里用了索引就以为高枕无忧，实际上如果type是index或者Extra里有Using filesort，性能依然堪忧。

rows字段是优化器估算的需要扫描的行数。这个数字不是精确值，但能反映数量级。key字段告诉你实际使用到的索引名。Extra字段更是戏多：Using where说明存储引擎层没过滤完，需要服务层再过滤；Using temporary代表用了临时表，通常意味着GROUP BY或DISTINCT没能利用索引；Using filesort代表文件排序，说明ORDER BY没能走索引顺序；Using index代表覆盖索引，不回表，这是最理想的状态。

我实际执行EXPLAIN时，这条主要SQL的结果是这样的：

表	type	key	rows	Extra
orders	ALL	NULL	约4000万	Using where; Using filesort
users	ref	PRIMARY	1（每行）	Using where
products	ref	PRIMARY	1（每行）	NULL

看到orders表是ALL，且rows估算逼近整表行数，Extra里还有Using filesort，基本可以判定：这个查询的每一步都在做最笨的事情。全表扫描已经够糟，还要对4000万行的中间结果排序取前20条，代价可想而知。

那么问题来了：为什么orders表会走到全表扫描？其实不只是没有索引这么简单。WHERE条件里status、created_at、user_id三个字段，即便其中user_id上有单列索引（原表里确实有），查询里却没有直接限定user_id的等值条件，而是在关联后的users表上加了user_type条件。这样一来，orders表的user_id没法提前变成一个固定值，优化器认为单独走user_id索引没有什么好处。

注意：EXPLAIN里出现key为NULL且type为ALL时，不只是“没建索引”这么简单，还要检查现有索引是否真的能被这条SQL用上。WHERE条件的写法、关联条件的顺序、字段上的函数包裹，都会让索引失效。

从这条SQL的执行过程来看，核心问题可以拆成三个部分：第一，订单表缺少一个能帮WHERE条件快速定位行的复合索引；第二，排序字段created_at没有和过滤条件组合进同一个索引；第三，关联时的users表过滤条件回表查询比例偏高。接下来要做的，就是针对这三个问题逐一给出索引策略，同时优化掉低效的排序方式。

3. 复合索引的设计逻辑：列序选择、覆盖索引与排序的三角权衡

很多人一听到“建索引”，第一反应是“给WHERE条件里每个字段各建一个单列索引”。这个思路在数据量小的时候没毛病，数据量大了以后，优化器每次只能选择其中一个索引最有选择性，然后对其他条件逐个回表过滤，效率并没有本质提升。

真正有效的做法是建复合索引，把多个过滤条件组合在一棵B+树里。但复合索引的列顺序至关重要，因为它遵循最左前缀原则：查询条件必须从索引最左列开始匹配，跳列会导致后续列失效。

针对订单表，我最终设计的索引是这样一个：

ALTER TABLE orders ADD INDEX idx_status_time_user (status, created_at, user_id);

列顺序的选择逻辑是这样的：status筛选后能快速缩小范围，放在最左边；created_at是一个范围条件，放在其次；user_id作为进一步过滤条件放在最后。这样设计之后，WHERE status = 1 AND created_at >= '...' AND created_at < '...' 就可以通过索引直接定位到status=1的索引段，再在段内用created_at做范围扫描，把扫描范围从4000万行压缩到几万行。

但这个设计还差一步。原查询里ORDER BY created_at DESC，如果created_at列已经在复合索引里，并且是等值匹配之后的范围检索，Order By是否能用索引排序？答案是：在这个索引结构下，排序依然不能用。原因是ORDER BY字段前面有status = 1的等值条件，这样status等于1的所有行的created_at已经是按索引顺序排列的，直接用索引顺序取出并反向遍历就行。等等，这里有个细节。如果WHERE里只用了status一个等值条件，那么索引的第二个字段created_at天然就是有序的，ORDER BY created_at就能直接用索引顺序，不需要额外排序。但我们的WHERE里created_at本身也是一个范围条件，这时created_at已经是范围扫描的边界，第三个字段user_id才是范围扫描过程中顺序不定的一列。让我重新整理一下逻辑：

以idx_status_time_user（status, created_at, user_id）为例，当查询条件为 status = 1 AND created_at >= 'x' AND created_at < 'y' 时，范围扫描落在索引的中间片段上。此时扫描到的created_at本身是按索引顺序递增的，所以如果按ORDER BY created_at DESC取数，方向与扫描顺序相反，优化器可以直接反向读取索引块来避免filesort。但MySQL 8.0之前的版本对索引反向扫描支持并不好，很多时候还是会出现Using filesort。我实测的情况是：在MySQL 8.0下，反序索引扫描已经被良好支持，Extra里没有再出现Using filesort，类型的排名从ALL变成了range，rows估算从4000万降到了18万。

但这还没完。要注意一个重要的隐藏成本：回表。查询需要SELECT出order_id、created_at，还有关联字段user_id、product_id。在19万行范围内，回表19万次，每行一次随机IO，这个代价在高峰期依然不可忽略。

于是接下来一步，是考虑让索引覆盖查询。覆盖索引的意思是：查询需要的所有列都包含在索引结构中，这样读取索引块后不需要回表，直接返回数据。对于这个场景，可以在复合索引里把需要的列加进去，把索引变成这样：

ALTER TABLE orders ADD INDEX idx_status_time_cover (status, created_at, user_id, order_id);

order_id是主键，InnoDB的二级索引会自动携带主键值，不需要额外加。真正的覆盖列是order_id？不对，二级索引默认包含主键，order_id可以不用单独加进去。需要覆盖的核心是要避免回表，但在实际业务里，很多时候SELECT的列很多，想把所有列都塞进索引不现实。所以更务实的策略是：把WHERE和ORDER BY完全用索引解决，SELECT的多个字段允许回表，但要确保回表发生在已经缩小的数据集上。

经验：覆盖索引不是必须追求的目标。真正要追求的是“回表面”足够小。把数据从4000万缩到2万后，回表2万次的成本完全可以接受，但如果回表发生在4000万的扫描结果上，则必然拖垮数据库。

这个订单查询在加了复合索引之后，执行计划发生了明显变化：

表	type	key	rows	Extra
orders	range	idx_status_time_user	约18万	Using index condition
users	ref	PRIMARY	1（每行）	Using where
products	ref	PRIMARY	1（每行）	NULL

这里还出现了Using index condition，代表触发了MySQL的索引条件下推。这个特性允许存储引擎层在使用索引扫描时直接把某些查询条件下推到索引层判断，减少回表次数。整体来看，这条SQL单次执行已经从2.8秒降到了280毫秒左右，基本解决了主要问题。

4. 查询重写是第二战场：函数包裹、隐式转换与关联顺序的陷阱

索引建完之后，并不意味着万事大吉。我常说一句话：索引只能帮你快速找到数据，但如果SQL本身写法有问题，索引再完美也发挥不出来。这个项目的后续优化，就是在剩余的一批中慢SQL里不断踩坑和重写。

第一个坑是函数包裹索引列。某条统计SQL写了WHERE DATE(created_at) = '2024-10-01'，看起来没什么问题，但对created_at列施加DATE函数后，索引就失效了，性能直接跌回全表扫描。正确写法是改成范围查询：

WHERE created_at >= '2024-10-01 00:00:00' AND created_at < '2024-10-02 00:00:00'

这里面的底层原因是：B+树索引按列值排序存储，一旦对列值做了函数变换，索引里的顺序就无法直接匹配变换后的值，优化器只能放弃索引。类似的还有在索引列上做算术运算、字符串拼接等。

第二个坑是隐式类型转换。某表的mobile字段是VARCHAR类型，查询却传入了整数条件WHERE mobile = 13800138000，MySQL会把VARCHAR列隐式转换为数字再比较，导致索引失效。实际排查时EXPLAIN结果里type为ALL，而写成WHERE mobile = '13800138000'之后就变成了ref。这类问题在代码里往往非常隐蔽，因为应用层的参数类型从没刻意指定，SQL拼出来就是数字字面量。

第三个坑是关联顺序和驱动表选择。在优化器能正确判断时，它会自动选择小表驱动大表，但一旦SQL里用了LEFT JOIN且位置颠倒，或者关联表的统计信息不准确，优化器可能选错驱动表。我在某条报表SQL里看到，orders表作为被驱动表时，本应按照索引去逐行匹配，但驱动表选取出现问题后，它变成了全表扫描。解决办法是改写关联顺序，把选择性强的过滤条件所在表作为驱动表，并在被驱动表的关联列上确保索引存在。

提示：排查隐式转换和函数包裹问题时，最直接的方法是查看EXPLAIN结果里key是否为NULL，以及rows是否忽然变成全表行数。一旦出现这种情况，优先检查索引列有没有被函数、算术或类型转换“污染”。

另外一个比较经典的重写场景是分页深翻页。原业务有一个管理后台的列表页，用户翻到第1000页时，SQL生成了LIMIT 20000, 20，MySQL需要扫描前20020行然后丢弃前20000行。这个写法在大数据量下即使有索引，也会随着页数加深而越来越慢。

这个项目的处理方式是改成基于游标的翻页：不跳页，只做“下一页”的场景，用上一次返回的最后一条记录的ID做条件过滤：

WHERE created_at < last_seen_created_at AND status = 1 ORDER BY created_at DESC LIMIT 20;

配合复合索引，这个改写让深翻页的查询时间从1.2秒降到了20毫秒以内，而且数据量越大优化效果越明显。对于必须支持随机跳页的场景，可以考虑延迟关联：先只查出主键ID，再用主键关联回原表取完整数据。

5. 索引选择的双子策略：选择性优先还是空间优先

在给字段设计索引的时候，经常要做一个权衡：是优先选择性（字段区分度），还是优先索引体积。这个决策直接影响索引的存储成本和查询收益。

先明确一个基本概念：区分度。区分度可以用 COUNT(DISTINCT column) / COUNT(*) 来估算。区分度越高，表示每个索引值对应的行数越少，过滤效果越好。性别字段区分度只有2，状态字段通常也只有3到5个取值，这种字段单独建索引，选择性和直接全表扫没什么区别。

但现实中没有那么绝对。像status这种低区分度字段，单独建索引没用，可一旦放到复合索引的最左列，配合后续高区分度字段，反而可以快速收敛范围。这是很多调优实践的常用套路：低选择性字段当“粗筛”，高选择性字段当“精筛”。

我还见过一个特别有意思的案例：某日志场景的表，业务查询条件主要是app_id和时间范围，其中app_id的区分度不高不低，一个app_id能对应几百万行日志，但总表行数有几个亿。如果只建app_id单列索引，索引选择性仍然不高；如果只建时间索引，单日时间范围可能命中几万行，加上app_id过滤后降到几百行。最终方案是建了(app_id, create_time)复合索引，让时间的范围定位在一棵更小的子树里，查询速度直接提升了两个数量级。

挑选索引时还须考虑写入成本。每次插入、更新、删除，都要同步维护所有二级索引。索引数量越多，写放大越高。在这套订单系统里，我观察到orders表本身已经有两个单列索引，加上复合索引，索引总量变成了四个。写入性能在优化后依然稳定，是因为该表的写入量并不算极端，但如果在高并发写入场景，每增加一个索引，都要提前压测评估写入延迟。

经验：索引设计要克制。每增加一个索引，都是一次以读换写的交易。建议用“查询收益假设计算”来决策：如果一个索引能让查询耗时从2秒降到200毫秒，而相关查询每天只执行十次，那么收益有限；但如果每天执行上万次，这个索引就必须建。

6. 从单点到全局：优化结果验证、慢查询防线与回退预案

单条SQL优化完成不等于调优结束。我在这套系统上做的最后一件事，是把优化成果量化记录，并建立一套防止回潮的机制。

验证环节，我在一个只读的备库上复现了优化前的慢SQL，记录执行时间；然后加索引后同环境再跑一次，记录对比结果。同时用EXPLAIN记录优化后的type、rows、Extra，把这些信息固化到文档里，方便后续同事看到这条SQL时知道它的设计意图。这个动作看起来小，但在人员流动频繁的团队里非常值得做，能避免别人看到索引后觉得多余而误删。

慢查询防线方面，我在监控系统里把慢查询阈值从2秒降到800毫秒，并设置了告警规则：任何一条SQL单次执行超过1秒，或者执行次数超过每分钟50次且单次超过500毫秒，就触发页面端通知。这一步非常实用，因为很多性能问题不是爆发式的，而是透过大量小慢SQL累积出来的。降阈值后，几乎每天都能在问题变严重之前看到苗头。

回退预案这个东西，团队内部经常忽略。线上加索引在MySQL 8.0里通常支持在线DDL，不会锁表，但也不排除某些老版本或特殊引擎下索引构建会引发主从延迟。我在正式环境操作的时候，会先估算索引构建时间，选择业务低峰期执行，并提前写好回退SQL。回退预案的核心内容只有一条：如果需要把索引删掉，应该执行哪条语句、会不会影响当前正在运行的查询，万一影响如何降级，这些都要提前想清楚。