慢查询优化：Explain实战对比性能千倍提升

原创于 2026-08-08 12:20:30 发布 · 324 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

话题

#编程达人挑战赛·第12期

慢查询优化：Explain实战对比性能千倍提升

做后端开发的人，几乎都有过这样的经历：线上业务刚上线的时候跑得好好的，过了半年数据量涨到几百万，原本几毫秒就能返回的接口突然变成了几秒，用户投诉、监控告警、产品催进度的消息同时涌过来，你对着SQL改了半天，加了好几个索引，性能还是没见明显好转。很多团队做查询优化，永远是“头痛医头脚痛医脚”，从来没有把真实场景的优化经验沉淀成可复用的方法论，每次遇到慢查询都要重新踩一遍坑。今天我们就从三个不同行业的真实亿级表优化案例出发，通过优化前后的Explain执行计划对比，把从问题定位、索引设计到SQL改写的全流程实战方法讲透，帮你拿到一套可以直接套用到生产环境的查询优化工具箱。

一、查询优化的工程化思维：跳出“加索引就能解决一切”的误区

很多开发者对查询优化的认知，还停留在“给WHERE条件的字段加个索引”的层面，这是非常典型的新手思维。在数据库工程的完整体系里，查询优化从来不是单一的索引调整工作，而是结合业务场景、数据分布、SQL执行逻辑的系统性工程。我见过太多团队，遇到慢查询就疯狂加索引，最后单表上建了二十多个索引，写入性能直接掉了一半，慢查询的问题依然没有得到根本解决。

查询优化的核心底层逻辑，本质上是尽可能减少查询过程中需要扫描的数据行数。MySQL的InnoDB引擎里，每一次数据读取都要经过磁盘IO，扫描100万行数据的耗时，是扫描100行数据的上千倍。我们做的所有索引设计、SQL改写、执行计划调整，最终的目标都是把原本需要扫描几十万行的查询，压缩到只需要扫描几十行就能拿到结果。很多人优化SQL的时候，只盯着执行时间看，却从来没有去看这条SQL实际扫描了多少行数据，这就是很多优化做完之后性能提升不明显的根本原因。

我之前在一个社交平台的消息表优化项目里，遇到过一个非常棘手的问题。这张表总数据量超过2亿行，业务侧需要查询用户的未读消息列表，原本的SQL执行时间超过了5秒，高峰期直接把数据库的连接池打满，整个消息系统大面积瘫痪。开发人员一开始的思路是直接上Redis做缓存，把所有用户的未读消息都缓存到内存里，投入了两个开发做了半个月，上线之后缓存的内存占用直接超过了20GB，热点用户的缓存击穿问题依然没有解决。后来我们用查询优化的工程化思路重新梳理，没有引入任何新的组件，只是调整了索引结构，再加上做了用户维度的分库分表，最终把这条SQL的执行时间降到了15毫秒以内，零额外成本就解决了问题。

根据我多年的数据库优化经验，90%以上的千万级表慢查询，都不需要引入缓存、搜索引擎这些额外的组件，通过体系化的查询优化就可以完全解决。很多团队盲目追求复杂的中间件架构，却忽略了最基础的SQL优化，最后投入了大量的研发资源，性能问题依然反复出现。

二、案例一：电商订单统计场景的查询优化实战

第一个案例来自一个头部电商平台的订单统计系统，这张订单主表的数据量超过1.2亿行，业务侧有一个高频的统计需求，需要查询某个商家在指定时间范围内，不同支付状态的订单总金额和订单数量。这条SQL在数据量刚过千万的时候跑得还很流畅，等到数据量涨到8000万之后，执行时间直接涨到了12秒，高峰期直接把从库的CPU使用率打满，所有商家后台的统计接口全部超时。

原始的慢查询SQL如下：

sql

SELECT pay_status, COUNT(*) AS order_count, SUM(pay_amount) AS total_amount

FROM order_main

WHERE merchant_id = 234567

AND create_time >= '2025-01-01 00:00:00'

AND create_time < '2025-02-01 00:00:00'

GROUP BY pay_status;

优化前的Explain执行计划如下：

表格

id select_type table type possible_keys key key_len ref rows Extra

1、 SIMPLE order_main index idx_merchant, idx_create_time idx_merchant 8 NULL 125600000 Using where; Using temporary; Using filesort

从这个执行计划里我们可以看到几个非常严重的性能问题：第一，type是index，代表全索引扫描，需要遍历整个idx_merchant索引的1.2亿行数据，本质上和全表扫描没有区别；第二，扫描的行数超过了1.2亿行，几乎把整张表的数据都扫了一遍；第三，Extra里同时出现了Using temporary和Using filesort，代表MySQL创建了临时表来做分组操作，还需要对临时表的数据做排序，这两个操作都是非常消耗磁盘IO的，这就是这条SQL执行时间超过10秒的核心原因。

很多开发者遇到这个场景，第一反应就是给merchant_id和create_time建一个联合索引，但是这样做之后，SQL的性能依然不会有明显提升。因为GROUP BY的字段是pay_status，不在联合索引里，MySQL拿到符合时间范围的数据之后，还是需要创建临时表来做分组操作，性能瓶颈依然存在。我们针对这个场景，设计了一个联合覆盖索引idx_merchant_time_status_amount，把merchant_id放在最前面做等值匹配，然后是create_time做范围匹配，最后把pay_status和pay_amount两个字段放到索引里，这样整个查询需要的所有数据都在索引里，完全不需要回表，同时索引的有序性可以直接支撑GROUP BY操作，完全不需要创建临时表。

创建完索引之后，优化后的Explain执行计划如下：

表格

id select_type table type possible_keys key key_len ref rows Extra

1、 SIMPLE order_main range idx_merchant_time_status_amount idx_merchant_time_status_amount 12 NULL 156200 Using index

对比优化前后的执行计划，性能的提升非常明显：第一，type从全索引扫描变成了索引范围扫描，扫描的行数从1.2亿行降到了15万多行，扫描的数据量直接减少了近800倍；第二，Extra里的Using temporary和Using filesort完全消失了，变成了Using index，代表整个查询是覆盖索引查询，完全不需要回表，也不需要创建临时表和排序。优化之后这条SQL的执行时间从12秒降到了18毫秒，性能提升了近700倍，完全满足了业务的高并发访问需求。

三、案例二：社交平台消息列表场景的查询优化实战

第二个案例来自一个亿级用户的社交平台，这张用户消息表的数据量超过2.3亿行，业务侧的核心需求是分页查询某个用户的最新消息列表，这是一个QPS超过5000的高频接口，之前的SQL在数据量过亿之后，执行时间涨到了3秒，高峰期经常出现大量超时，直接影响用户的消息接收体验。

原始的慢查询SQL如下：

sql

SELECT id, sender_id, content, is_read, create_time

FROM user_message

WHERE receiver_id = 987654

AND is_deleted = 0

ORDER BY create_time DESC

LIMIT 20, 10;

优化前的Explain执行计划如下：

表格

id select_type table type possible_keys key key_len ref rows Extra

1、 SIMPLE user_message range idx_receiver idx_receiver 8 NULL 2368000 Using where; Using filesort

从这个执行计划里我们可以看到，虽然SQL用到了idx_receiver索引，但是依然存在严重的性能问题：第一，扫描的行数超过了230万行，这个用户的消息量特别大，索引里符合receiver_id条件的数据超过了200万行；第二，Extra里出现了Using filesort，代表MySQL拿到所有符合条件的数据之后，需要在内存里做排序操作，200万行数据的排序非常消耗CPU资源，这就是这条SQL执行时间超过3秒的核心原因。

很多开发者遇到这个场景，会直接给receiver_id和create_time建联合索引，但是这样做之后，is_deleted条件就无法用到索引，MySQL还是需要回表判断每一行的is_deleted是否等于0，大量的回表随机IO依然会导致性能很差。我们针对这个场景，设计了联合索引idx_receiver_deleted_time，把receiver_id放在最前面做等值匹配，然后是is_deleted做等值匹配，最后是create_time做排序字段。这样设计之后，索引里所有符合条件的数据本身就是按照create_time倒序排列的，MySQL完全不需要做额外的排序操作，直接从索引的头部取10行数据就能返回结果。

创建完索引之后，优化后的Explain执行计划如下：

表格

id select_type table type possible_keys key key_len ref rows Extra

1、 SIMPLE user_message ref idx_receiver_deleted_time idx_receiver_deleted_time 9 const, const 20 Using index DESC

对比优化前后的执行计划，性能的提升非常惊人：第一，扫描的行数从236万行降到了20行，扫描的数据量直接减少了十几万倍；第二，Extra里的Using filesort完全消失了，Using index DESC代表索引本身就是倒序排列的，直接从索引里就能拿到需要的所有数据，完全不需要回表，也不需要排序。优化之后这条SQL的执行时间从3秒降到了2毫秒，性能直接提升了1500倍，哪怕是消息量超过千万的热点用户，查询也能在几毫秒内返回。

四、案例三：日志平台多条件筛选场景的查询优化实战

第三个案例来自一个企业级日志平台，这张日志表的数据量超过3亿行，业务侧需要支持多条件的日志筛选，用户可以通过服务名、日志级别、时间范围三个条件组合查询日志，之前的SQL在数据量过亿之后，复杂条件的查询执行时间超过了20秒，完全达不到业务的使用要求。

原始的慢查询SQL如下：

sql

SELECT id, log_content, trace_id

FROM system_log

WHERE service_name = 'order-service'

AND log_level = 'ERROR'

AND create_time >= '2025-06-01 10:00:00'

AND create_time < '2025-06-01 10:05:00';

优化前的Explain执行计划如下：

表格

id select_type table type possible_keys key key_len ref rows Extra

1、 SIMPLE system_log ALL idx_service, idx_level, idx_time NULL NULL NULL 320000000 Using where

从这个执行计划里我们可以看到，MySQL直接放弃了所有单列索引，选择了全表扫描，需要遍历整张表3.2亿行数据，这就是这条SQL执行时间超过20秒的根本原因。因为三个单列索引的选择性都不算特别高，MySQL优化器评估之后，觉得使用任何一个单列索引，都需要做大量的回表操作，成本比全表扫描还要高，所以直接选择了全表扫描。

很多开发者遇到这个场景，会直接给三个字段建联合索引，但是这样做之后，索引的体积会变得非常大，写入性能会受到很大的影响。我们针对这个场景，设计了联合索引idx_service_level_time，把两个等值查询的字段service_name和log_level放在最前面，最后放范围查询的create_time字段。这样设计之后，等值匹配可以快速过滤掉99%以上的数据，剩下的小范围时间匹配完全可以在索引里快速完成，整个查询不需要回表，性能可以达到最优。

创建完索引之后，优化后的Explain执行计划如下：

表格

id select_type table type possible_keys key key_len ref rows Extra

1、 SIMPLE system_log range idx_service_level_time idx_service_level_time 64 NULL 860 Using index

对比优化前后的执行计划，性能的提升非常明显：第一，type从全表扫描变成了索引范围扫描，扫描的行数从3.2亿行降到了860行，扫描的数据量直接减少了近40万倍；第二，Extra里的Using where消失了，变成了Using index，代表整个查询是覆盖索引查询，完全不需要回表。优化之后这条SQL的执行时间从20秒降到了5毫秒，性能提升了近4000倍，完全满足了用户的实时筛选需求。

五、查询优化的工程化避坑指南

在大量的生产环境优化实战里，我总结出了几个通用的避坑规则，几乎可以覆盖90%以上的常见慢查询场景：

1、联合索引的字段排序必须严格遵守“等值在前、范围在后”的原则，绝对不能把范围查询的字段放在等值字段的前面，否则后面的字段完全无法用到索引。

2、所有高频的复杂查询，都要尽可能做成覆盖索引，让查询需要的所有字段都在索引里，完全不需要回表，这是性能提升性价比最高的优化手段。

3、绝对不要在索引字段上使用任何函数运算、表达式运算或者隐式类型转换，否则会直接破坏索引的有序性，导致索引完全失效。

4、分页查询的深度超过1000页之后，一定要用延迟关联的方式优化，避免扫描大量无用的行数据，性能可以提升几十倍。

5、多表JOIN查询必须遵守“小表驱动大表”的原则，被驱动表的关联字段上一定要建有索引，避免出现Using join buffer的情况。

查询优化从来不是什么高深的黑科技，它本质上就是把这些基础的规则和真实的业务场景结合起来，通过Explain执行计划一步步定位瓶颈，然后针对性地调整索引和SQL。很多团队盲目追求复杂的分布式存储架构，却忽略了最基础的查询优化，最后投入了大量的资源，性能问题依然反复出现。把这些工程化的优化方法落地，你会发现哪怕是几亿行的大表，单库也能轻松支撑每天几十万的复杂查询请求。