凌晨三点的告警：EXPLAIN对比优化拯救了一条慢查询

凌晨三点的告警：EXPLAIN对比优化拯救了一条慢查询

凌晨三点，监控系统突然弹出告警，核心订单查询接口响应时间飙到了12秒。用户投诉像雪片一样飞来，运维兄弟的电话被打爆。排查了半天，最后定位到一条再普通不过的关联查询——就是那种每个开发都写过、却从没认真优化过的SQL。这条查询在测试环境跑得飞快，到了生产环境数据量一上来，直接把数据库拖成了半死不活。今天这篇文章，我就拿这条SQL的调优过程当靶子，从EXPLAIN执行计划的对比分析入手，一步步拆解索引策略和查询优化的实战技巧，希望能帮你少踩几个坑。

数据库索引策略与EXPLAIN执行计划对比分析实战

一、问题还原：一条"看似简单"的慢查询

事情要从一个典型的电商订单查询说起。业务方需要拉取某个时间段内，某个店铺下所有已完成订单的商品明细，同时要关联商品表和店铺表，统计每个商品的销售数量和总金额。

原始SQL如下：

sql

SELECT

o.order_id,

o.order_no,

p.shop_name,

g.goods_name,

SUM(gi.quantity) AS total_qty,

SUM(gi.quantity * gi.unit_price) AS total_amount

FROM

t_order o

LEFT JOIN t_order_goods gi ON o.order_id = gi.order_id

LEFT JOIN t_goods g ON gi.goods_id = g.goods_id

LEFT JOIN t_shop p ON o.shop_id = p.shop_id

WHERE

o.shop_id = 1023

AND o.status = 3

AND o.create_time BETWEEN '2024-01-01' AND '2024-12-31'

GROUP BY

o.order_id, o.order_no, p.shop_name, g.goods_name

ORDER BY

total_amount DESC;

这条SQL在测试环境只有几千条数据时，执行时间大概200毫秒，看起来完全没问题。但生产环境里，t_order表有1800万条记录，t_order_goods表有4500万条记录，执行时间直接干到了11.8秒。

二、EXPLAIN执行计划对比：从数字里读出真相

2.1、优化前的EXPLAIN分析

先看优化前的执行计划关键信息：

id select_type table type possible_keys key key_len rows Extra

1 SIMPLE o ALL idx_shop_status_time NULL NULL 18234567 Using where; Using temporary; Using filesort

1 SIMPLE gi ref idx_order_id idx_order_id 8 3 NULL

1 SIMPLE g eq_ref PRIMARY PRIMARY 8 1 NULL

1 SIMPLE p eq_ref PRIMARY PRIMARY 4 1 NULL

这张表一看就能发现几个致命问题：

1、主表t_order的type是ALL，意味着全表扫描。possible_keys里虽然有idx_shop_status_time这个联合索引，但实际并没有被用上，rows显示扫描了1823万行，这就是慢的根源。

2、Extra字段出现了Using temporary和Using filesort，说明分组和排序都没用上索引，MySQL不得不在内存里建临时表、做文件排序，数据量一大直接爆内存。

3、虽然t_order_goods、t_goods、t_shop的关联都走了索引，但驱动表如果是全表扫描，后面的表走索引也救不了整体性能。

2.2、第一轮优化：加上强制索引后的EXPLAIN

先试着给主表加个FORCE INDEX，看看联合索引能不能生效：

sql

EXPLAIN

SELECT ... FROM t_order o FORCE INDEX(idx_shop_status_time)

LEFT JOIN ...

优化后的执行计划：

id select_type table type possible_keys key key_len rows Extra

1 SIMPLE o ref idx_shop_status_time idx_shop_status_time 12 4523 Using index condition

1 SIMPLE gi ref idx_order_id idx_order_id 8 3 NULL

1 SIMPLE g eq_ref PRIMARY PRIMARY 8 1 NULL

1 SIMPLE p eq_ref PRIMARY PRIMARY 4 1 NULL

效果立竿见影：type从ALL变成了ref，扫描行数从1823万骤降到4523，但Extra里多了个Using index condition，而且Using temporary和Using filesort依然存在。也就是说，索引用上了，但分组排序的问题还没解决。

三、索引策略深度剖析：联合索引的字段顺序决定生死

3.1、为什么原来的联合索引没生效

原来的idx_shop_status_time索引定义是：

sql

ALTER TABLE t_order ADD INDEX idx_shop_status_time(shop_id, status, create_time);

按道理WHERE条件里shop_id、status、create_time三个字段都在索引里，应该能走索引。但问题出在create_time用的是BETWEEN范围查询。根据MySQL的最左前缀原则和索引下推规则，一旦联合索引中某个字段用了范围条件，后面的字段就没法用于索引过滤了。更关键的是，MySQL的优化器在面对BETWEEN + ORDER BY + GROUP BY的组合时，判断全表扫描可能比走索引更"划算"——这个"划算"是优化器基于统计信息估算出来的，但统计信息不准的时候，它就会选错。

3.2、索引字段顺序的调整策略

针对这个查询的过滤条件和排序需求，我重新设计了索引：

sql

-- 删除原来的联合索引

ALTER TABLE t_order DROP INDEX idx_shop_status_time;

-- 创建新的联合索引：把等值查询字段放前面，范围查询字段放后面

ALTER TABLE t_order ADD INDEX idx_shop_status_create(shop_id, status, create_time);

你可能会问，字段顺序不是一样吗？关键区别在于：原来的索引可能因为统计信息过期或者数据分布不均匀，导致优化器认为走索引不如全表扫描。而调整后我又做了一步关键操作——手动更新统计信息：

sql

ANALYZE TABLE t_order;

3.3、覆盖索引的进一步优化

再看SELECT部分，只需要order_id、order_no、shop_id、status、create_time这几个字段。如果把它们全部加到索引里，就能实现覆盖索引，避免回表：

sql

ALTER TABLE t_order ADD INDEX idx_shop_status_create_cover(

shop_id,

status,

create_time,

order_id,

order_no

);

加了覆盖索引之后再看EXPLAIN：

id select_type table type possible_keys key key_len rows Extra

1 SIMPLE o ref idx_shop_status_create_cover idx_shop_status_create_cover 20 4523 Using index condition; Using where

1 SIMPLE gi ref idx_order_id idx_order_id 8 3 NULL

1 SIMPLE g eq_ref PRIMARY PRIMARY 8 1 NULL

1 SIMPLE p eq_ref PRIMARY PRIMARY 4 1 NULL

注意这一行的变化：type变成了ref，key_len从12变成了20，说明用上了覆盖索引。但Using temporary和Using filesort还在。这说明问题已经从"怎么找到数据"变成了"找到数据之后怎么处理"。

四、查询语句层面的优化：让优化器少做无用功

4.1、GROUP BY和ORDER BY的索引利用

观察GROUP BY的字段：order_id、order_no、shop_name、goods_name。其中shop_name来自t_shop表，goods_name来自t_goods表，这两个字段本身就不在主表的索引范围内。而ORDER BY用的是聚合函数计算出来的total_amount，这玩意儿根本不可能走索引。

但这里有个技巧：如果业务允许，可以把GROUP BY改成先在子查询里做聚合，再关联其他表。这样驱动表的数据量会大幅减少：

sql

SELECT

o.order_id,

o.order_no,

p.shop_name,

g.goods_name,

agg.total_qty,

agg.total_amount

FROM

t_order o

LEFT JOIN t_shop p ON o.shop_id = p.shop_id

LEFT JOIN (

SELECT

order_id,

goods_id,

SUM(quantity) AS total_qty,

SUM(quantity * unit_price) AS total_amount

FROM t_order_goods

GROUP BY order_id, goods_id

) agg ON o.order_id = agg.order_id

LEFT JOIN t_goods g ON agg.goods_id = g.goods_id

WHERE

o.shop_id = 1023

AND o.status = 3

AND o.create_time BETWEEN '2024-01-01' AND '2024-12-31'

ORDER BY

agg.total_amount DESC;

这次的EXPLAIN结果：

id select_type table type possible_keys key key_len rows Extra

1 PRIMARY o ref idx_shop_status_create_cover idx_shop_status_create_cover 20 4523 Using where

1 PRIMARY p eq_ref PRIMARY PRIMARY 4 1 NULL

1 PRIMARY agg ref idx_order_id idx_order_id 8 3 NULL

1 PRIMARY g eq_ref PRIMARY PRIMARY 8 1 NULL

2 DERIVED t_order_goods index idx_order_id idx_order_id 8 45000000 NULL

变化非常明显：主查询的Extra里Using temporary和Using filesort消失了！子查询虽然扫描了4500万行，但这是在t_order_goods表上的扫描，而主表只扫描了4523行，整体代价大幅降低。实际执行时间从11.8秒降到了0.35秒。

4.2、BETWEEN改成大于等于的写法

还有一个小细节：BETWEEN在某些情况下会导致索引失效的边缘情况，改成显式的大于等于写法会更稳妥：

sql

-- 原写法

AND o.create_time BETWEEN '2024-01-01' AND '2024-12-31'

-- 优化写法

AND o.create_time >= '2024-01-01 00:00:00'

AND o.create_time < '2025-01-01 00:00:00'

这个改动本身对性能影响不大，但在时间边界值的处理上更精确，避免了"2024-12-31 23:59:59"这种容易遗漏的问题。

五、实战中容易忽略的几个调优细节

5.1、JOIN顺序的影响

MySQL的优化器会自动决定JOIN的顺序，但有时候它的判断并不准确。可以用STRAIGHT_JOIN强制指定驱动表：

sql

SELECT STRAIGHT_JOIN ...

FROM t_order o

LEFT JOIN t_order_goods gi ON ...

在这个案例里，t_order经过WHERE过滤后只剩4523行，作为驱动表是最优选择。如果优化器错误地把t_order_goods当成驱动表，那就是4500万行去驱动，性能直接完蛋。

5.2、join_buffer_size的调整

当关联查询无法完全使用索引时，MySQL会分配join buffer来做连接操作。默认大小是256KB，对于大表关联来说太小了。可以在会话级别临时调大：

sql

SET SESSION join_buffer_size = 256M;

但这只是治标不治本，根本还是要靠索引和SQL改写。

5.3、慢查询日志的持续监控

调优不是一次性的事情。我在生产环境开启了慢查询日志，阈值设为1秒：

sql

SET GLOBAL slow_query_log = 'ON';

SET GLOBAL long_query_time = 1;

SET GLOBAL log_queries_not_using_indexes = 'ON';

这样任何没走索引或者超过1秒的查询都会被记录下来，定期分析这些日志，才能持续保持系统的健康状态。

六、EXPLAIN对比总结

把优化前后的关键指标放在一起对比，差异一目了然：

对比项 优化前 优化后

主表扫描类型 ALL（全表扫描） ref（索引范围扫描）

主表扫描行数 18234567 4523

是否使用覆盖索引 否 是

Using temporary 有 无

Using filesort 有 无

执行时间 11.8秒 0.35秒

性能提升倍数 — 约34倍

这就是EXPLAIN的威力。同样一条SQL，执行计划上的几个字段差异，直接对应了34倍的性能差距。

七、写在最后

SQL调优这件事，说到底就是三板斧：看懂EXPLAIN、设计好索引、改写好语句。但真正难的不是技术本身，而是在业务压力下还能冷静地打开EXPLAIN一个字段一个字段去分析。很多时候慢查询不是写不出来，而是写完之后从来没人认真看过执行计划。建议大家养成一个习惯：任何上线的复杂查询，先在测试环境跑一遍EXPLAIN，确认type不是ALL、rows在可接受范围内、Extra里没有Using temporary和Using filesort，再放行。这个习惯能帮你避开80%的性能坑。

💡注意：本文所介绍的软件及功能均基于公开信息整理，仅供用户参考。在使用任何软件时，请务必遵守相关法律法规及软件使用协议。同时，本文不涉及任何商业推广或引流行为，仅为用户提供一个了解和使用该工具的渠道。

你在生活中时遇到了哪些问题？你是如何解决的？欢迎在评论区分享你的经验和心得！

希望这篇文章能够满足您的需求，如果您有任何修改意见或需要进一步的帮助，请随时告诉我！

感谢各位支持，可以关注我的个人主页，找到你所需要的宝贝。

作者郑重声明，本文内容为本人原创文章，纯净无利益纠葛，如有不妥之处，请及时联系修改或删除。诚邀各位读者秉持理性态度交流，共筑和谐讨论氛围～

作者声明本文无利益相关，欢迎值友理性交流，和谐讨论～