干了六年才总结出来的查询优化经验，全是干货

半夜两点半，手机突然炸了，运维在群里疯狂@所有人："数据库CPU 99%，系统快崩了！"我从被窝里爬起来，打开慢查询日志一看——果然，又是一条SQL在作妖。干了六年后端，我太清楚了：百分之八十的线上事故，根子都出在数据库查询上。今天我不讲那些虚头巴脑的理论，直接把我这些年亲手优化过的三个真实案例掏出来，从索引设计到Explain分析，全是能直接拿去用的干货。

数据库工程与查询优化案例实战

一、先搞清楚：为什么你的SQL会慢

☆ 很多开发者写SQL的时候，只关心"能不能查出来"，从来不关心"查得快不快"。等数据量一上来，问题就全暴露出来了。其实SQL慢，归根结底就那么几个原因：

1、没有走索引，全表扫描。数据库得把几百万行数据一行一行地比对，能不慢吗？

2、索引建错了，或者压根没建在该建的地方。比如你按时间查，结果给姓名建了个索引，那基本等于白搭。

3、JOIN写法有问题，导致中间结果集爆炸。两张大表一JOIN，中间可能产生几百万行的临时结果，内存直接撑爆。

4、SELECT * 查了一堆用不上的字段，网络传输和内存开销都白白浪费了。

☆ 所以查询优化这件事，说白了就是四个字：减少扫描。让数据库用最小的代价，找到你真正需要的那几行数据。接下来我用三个真实案例，带你一步步把慢查询干掉。

二、案例一：电商订单列表，从5秒优化到3毫秒

1、问题描述

☆ 去年接手了一个电商项目，用户反馈"我的订单"页面加载特别慢，动辄四五秒。我打开代码一看，SQL是这么写的：

sql

SELECT * FROM orders WHERE user_id = 10086 ORDER BY create_time DESC LIMIT 20;

☆ orders表有将近600万条数据，user_id字段上没有任何索引。

2、问题出在哪

☆ 这条SQL至少犯了三个错误：

（1）用了SELECT *，把订单详情、备注、物流信息全拉出来了，但页面其实只需要订单号、金额、状态、时间这几个字段。

（2）user_id没有索引，MySQL只能全表扫描，600万行一行一行地比对user_id。

（3）虽然加了LIMIT 20，但数据库得先把所有符合条件的行全部找出来、排好序，然后才能取前20条。也就是说，LIMIT在这里几乎没起到作用。

3、优化过程

☆ 第一步，建联合索引：

sql

CREATE INDEX idx_user_time ON orders(user_id, create_time DESC);

☆ 为什么要把create_time也放进索引？因为查询里有ORDER BY create_time DESC，把排序字段也加进索引，数据库就可以直接按索引顺序读取，不用再额外做文件排序了。

☆ 第二步，改写SQL，只查需要的字段：

sql

SELECT order_id, create_time, total_amount, status

FROM orders

WHERE user_id = 10086

ORDER BY create_time DESC

LIMIT 20;

4、效果对比

对比项 优化前 优化后

扫描方式 全表扫描（type=ALL） 索引范围扫描（type=range）

扫描行数 约600万行 20行

执行时间 4.7秒 0.003秒

是否文件排序 是（Using filesort） 否（索引已有序）

☆ 从将近5秒降到3毫秒，提升了一千五百多倍。就改了两行代码、加了一个索引，效果就是这么炸裂。

三、案例二：统计报表慢到离谱，换个写法快了20倍

1、问题描述

☆ 另一个项目有个运营后台，需要统计每个商品分类的销售总额。原始SQL长这样：

sql

SELECT c.category_name, SUM(o.total_amount) as total_sales

FROM categories c

LEFT JOIN orders o ON c.id = o.category_id

GROUP BY c.category_name;

☆ categories表大概300条数据，orders表有1200万条。这条SQL跑一次要将近10秒，运营同学天天投诉。

2、问题分析

☆ 很多人一看"统计每个分类的总额"，第一反应就是JOIN加GROUP BY。逻辑上没毛病，但性能上是个大坑。

☆ 你想啊，LEFT JOIN会先把两张表做关联，orders有1200万行，关联之后中间结果集依然是1200万行级别。然后再对这1200万行做GROUP BY聚合，MySQL得开临时表、做文件排序，开销巨大。

3、优化方案：先聚合再关联

sql

SELECT c.category_name,

COALESCE(t.total_sales, 0) as total_sales

FROM categories c

LEFT JOIN (

SELECT category_id, SUM(total_amount) as total_sales

FROM orders

GROUP BY category_id

) t ON c.id = t.category_id;

☆ 核心思路是：先在orders表内部完成聚合，把1200万行压缩成几百行（每个分类一行），然后再和categories表做关联。这样JOIN的数据量就从1200万降到了几百，性能自然起飞。

4、效果对比

对比项 直接JOIN写法 子查询先聚合写法

JOIN时的数据量 约1200万行 约300行

是否使用临时表 是 否

执行时间 9.8秒 0.45秒

性能提升倍数 基准 约22倍

☆ 同样的业务结果，换一种写法，性能直接提升二十多倍。所以说，不要迷信JOIN，有时候把子查询用对了，比JOIN快得多。

四、案例三：用Explain揪出那个"隐藏杀手"

1、Explain是什么

☆ MySQL提供了一个超级好用的命令叫EXPLAIN，你在任何SELECT语句前面加上它，MySQL就会告诉你它打算怎么执行这条SQL。这就相当于给SQL做了个CT扫描，哪里有问题一目了然。

2、一次真实的排查经历

☆ 有一次线上出现慢查询告警，SQL看着特别简单：

sql

SELECT * FROM user_login_log WHERE action = 'login' AND log_time > '2024-01-01';

☆ 我跑了一下Explain：

id select_type table type possible_keys key key_len rows Extra

1 SIMPLE user_login_log ALL NULL NULL NULL 4500000 Using where

☆ 一看就知道问题大了：

（1）type = ALL，全表扫描，这是最差的情况。

（2）possible_keys = NULL，说明压根没有可用的索引。

（3）rows = 4500000，预计要扫描450万行。

（4）Extra = Using where，说明过滤是在存储引擎层做的，效率很低。

3、加索引，再看一次

sql

ALTER TABLE user_login_log ADD INDEX idx_action_time(action, log_time);

☆ 再次Explain：

id select_type table type possible_keys key key_len rows Extra

1 SIMPLE user_login_log ref idx_action_time idx_action_time 767 280 Using index condition

☆ 变化非常直观：

（1）type从ALL变成了ref，说明走索引了。

（2）rows从450万降到了280，扫描行数断崖式下降。

（3）Extra变成了Using index condition，说明索引下推生效了。

4、Explain到底怎么看

☆ 这几年我排查慢查询，Explain看了不下几千次，总结下来最关键的就看这几列：

（1）type列：这是最重要的指标。从好到差依次是 system > const > eq_ref > ref > range > index > ALL。你的SQL至少要达到range级别，最好是ref或eq_ref。如果是ALL，那就是全表扫描，必须优化。

（2）key列：看实际用了哪个索引。如果是NULL，说明没用上任何索引。

（3）rows列：预估扫描行数。这个数字越小越好，最好是个位数或者两位数。

（4）Extra列：这里面信息量很大。如果出现Using filesort，说明有额外的文件排序开销；如果出现Using temporary，说明用了临时表，这两种情况都要尽量避免。最理想的情况是出现Using index，说明走了覆盖索引，性能最优。

五、索引策略：不是越多越好

1、一个常见的坑

☆ 我见过不少团队，觉得索引是万能药，恨不得给每个字段都建一个。结果呢？写入性能暴跌，因为每次INSERT、UPDATE、DELETE都要同步更新所有索引。而且索引太多，优化器反而会选错索引，性能更差。

2、几条实用的索引原则

（1）优先给WHERE、JOIN、ORDER BY、GROUP BY里出现的字段建索引。这是最基本的，也是最有效的。

（2）联合索引一定要注意最左前缀原则。比如你建了(a, b, c)的联合索引，那么WHERE a=1能用上索引，WHERE a=1 AND b=2也能用上，但如果你只写WHERE b=2，索引就废了。所以建联合索引的时候，把区分度最高、最常用的字段放在最左边。

（3）区分度低的字段别建索引。比如"性别"字段，只有男和女两个值，建了索引也没啥用，因为数据库扫描完索引还是得回表查大量数据。但像"身份证号""手机号"这种区分度极高的字段，建索引效果立竿见影。

（4）能用覆盖索引就别回表。所谓覆盖索引，就是索引本身就包含了查询需要的所有字段，数据库根本不用去查原始数据行。比如前面案例一里的idx_user_time(user_id, create_time)，如果你只查user_id、create_time、order_id这几个字段，那就能走覆盖索引，速度是最快的。

3、覆盖索引长什么样

sql

-- 建一个覆盖索引

CREATE INDEX idx_cover ON orders(user_id, create_time, order_id, total_amount);

-- 这条SQL可以完全走覆盖索引，不需要回表

SELECT order_id, create_time, total_amount

FROM orders

WHERE user_id = 10086

ORDER BY create_time DESC

LIMIT 20;

☆ 你用Explain看一下，Extra里会出现Using index，这就是覆盖索引的标志，意味着这条SQL的性能已经拉满了。

六、写在最后

☆ SQL查询优化这件事，说难也难，说简单也简单。难的是你得真正理解数据库底层是怎么执行SQL的，简单的是只要你掌握了Explain分析、索引设计、JOIN优化这几板斧，大部分慢查询都能搞定。

☆ 最后再帮大家划一下重点：

1、遇到慢查询，第一件事不是改SQL，而是先跑一下EXPLAIN，看看执行计划到底长什么样。

2、索引不是越多越好，要建在刀刃上。联合索引注意最左前缀，区分度低的字段别浪费空间。

3、能用覆盖索引就别回表，这是性能优化里性价比最高的手段之一。

4、JOIN不一定比子查询快，大数据量场景下，先聚合再关联往往更高效。

5、永远不要用SELECT *，只查你真正需要的字段。

☆ 数据库优化不是一锤子买卖，是个持续的活。业务在增长，数据量在变，之前的优化方案可能过几个月就不够用了。所以养成定期看慢查询日志、定期review索引的习惯，比什么都强。

☆ 希望这篇文章能帮你在实际工作中少踩几个坑。如果你也有什么SQL优化的血泪经历，欢迎一起交流。

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

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

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

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

博文入口：https://blog.csdn.net/Start_mswin 复制到【浏览器】打开即可,宝贝入口：https://pan.quark.cn/s/b42958e1c3c0 宝贝：https://pan.quark.cn/s/1eb92d021d17

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