别再瞎建索引了！这才是MySQL优化的正确打开方式

凌晨三点，手机突然疯狂震动，线上监控告警亮了一片。订单创建接口全部超时，数据库CPU直接打满。紧急排查了二十分钟，发现罪魁祸首竟然是一条跑批用的SQL——没有写过滤条件，硬生生把一张800万行的大表做了全表扫描。这次事故之后，我开始认真研究SQL优化，从Explain分析到索引策略，踩了无数坑之后终于总结出了一套实用的方法论。今天这篇文章，我会把这些年积累的经验全部掏出来，希望能帮你少走几年弯路。

一、为什么SQL优化是每个后端开发者的必修课

很多人觉得SQL优化是DBA的事，跟开发没关系。这种想法非常危险。在实际工作中，大部分慢查询都是开发写出来的。DBA能做的事情有限，他们可以帮你建索引、调参数，但如果SQL本身写得有问题，谁来都救不了。

☆ 举个真实的例子：

我之前在一家电商公司，有个统计报表需要查询最近30天的订单数据。最初的SQL长这样：

sql

SELECT user_id, COUNT(*) as order_count, SUM(amount) as total_amount

FROM orders

WHERE DATE(create_time) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)

GROUP BY user_id

ORDER BY total_amount DESC;

这条SQL在数据量只有几万条的时候跑得还算正常，大概两三秒就出结果了。但随着业务增长，订单表慢慢涨到了500万行，这条SQL的执行时间直接飙到了8秒以上，高峰期甚至超过15秒。

更要命的是，这个报表每天凌晨跑一次，跑的时候会占用大量数据库资源，导致其他业务的查询也跟着变慢。运营同事天天投诉，技术总监也开始关注这个问题。

后来我花了一下午的时间把这条SQL优化到了0.3秒以内，整个过程其实并不复杂，关键是要找对方法。

二、Explain：定位问题的第一把利器

想要优化SQL，首先得知道它慢在哪里。MySQL提供了一个非常强大的工具——Explain。只要在SQL前面加上Explain关键字，就能看到这条SQL的执行计划。

☆ 我们先来看看优化前那条SQL的执行计划：

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE orders ALL NULL NULL NULL NULL 5000000 Using where; Using temporary; Using filesort

☆ 从这张表中可以读出以下关键信息：

1、type列显示为ALL，这意味着MySQL在做全表扫描，逐行检查每一条记录。这是所有访问类型中最差的一种。

2、possible_keys和key都是NULL，说明完全没有用到任何索引。

3、rows显示为5000000，也就是说MySQL需要扫描整整500万行数据才能找到结果。

4、Extra字段中出现了Using temporary和Using filesort，这两个都是性能杀手。Using temporary表示MySQL需要创建临时表来做分组操作，Using filesort表示需要额外的文件排序，这两个操作都非常消耗资源。

看到这里，问题已经很清楚了：没有索引、全表扫描、还要做临时表和文件排序，这条SQL不慢才怪。

三、索引策略：不是建得越多越好

很多开发人员遇到慢查询的第一反应就是加索引，觉得索引越多查询越快。但实际上，索引是一把双刃剑，用得好能提速几十倍，用不好反而会拖慢整个系统。

☆ 索引的优势非常明显：

1、能够大幅减少需要扫描的数据行数，从几百万行降到几十行甚至几行。

2、对于排序和分组操作，合适的索引可以直接避免Using temporary和Using filesort。

3、覆盖索引可以让查询完全不需要回表，效率极高。

☆ 但索引也有不小的代价：

1、每个索引都会占用额外的磁盘空间，一张大表上建五六个索引，光索引文件就可能比数据文件还大。

2、每次INSERT、UPDATE、DELETE操作都需要同步更新索引，索引越多写操作越慢。

3、过多的索引会让MySQL优化器在选择执行计划时产生困惑，有时候反而会选错索引，导致性能更差。

回到我们的案例。最初我的做法是给create_time字段单独建了一个索引：

sql

ALTER TABLE orders ADD INDEX idx_create_time (create_time);

再次用Explain看一下：

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE orders range idx_create_time idx_create_time 5 NULL 1500000 Using where; Using temporary; Using filesort

☆ 效果有提升，但远不够理想：

1、type从ALL变成了range，说明走了索引范围扫描，比全表扫描好了很多。

2、rows从500万降到了150万，扫描行数减少了70%。

3、但Using temporary和Using filesort依然存在，说明光有create_time索引还不够。

问题出在哪里？原因是我们的WHERE条件中用了DATE(create_time)这个函数，这会导致索引失效。MySQL没办法对经过函数处理后的字段使用索引，所以虽然建了索引，但实际用不上。

☆ 正确的做法是改写SQL，避免对字段使用函数：

sql

SELECT user_id, COUNT(*) as order_count, SUM(amount) as total_amount

FROM orders

WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)

AND create_time < CURDATE()

GROUP BY user_id

ORDER BY total_amount DESC;

注意我把原来的DATE(create_time) >= ...改成了create_time >= ... AND create_time < ...，这样就能正常使用索引了。

但光改SQL还不够，我们还需要一个更合理的索引。因为查询中涉及到了create_time的范围过滤、user_id的分组、以及amount的求和，所以最好建一个联合索引：

sql

ALTER TABLE orders DROP INDEX idx_create_time;

ALTER TABLE orders ADD INDEX idx_time_user_amount (create_time, user_id, amount);

这个联合索引的设计思路是这样的：create_time放在最左边，因为WHERE条件中有范围查询；user_id放在中间，因为GROUP BY要用到它；amount放在最后，因为SELECT中需要SUM(amount)，放在索引里可以实现覆盖索引，避免回表。

☆ 优化后的Explain结果：

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE orders range idx_time_user_amount idx_time_user_amount 8 NULL 85000 Using where; Using index

☆ 优化效果一目了然：

1、type是range，走了索引范围扫描。

2、rows从150万降到了8.5万，减少了94%以上。

3、Using temporary和Using filesort都消失了，因为联合索引覆盖了所有需要的字段。

4、Extra中出现了Using index，说明这是一个覆盖索引，查询完全不需要回表。

实际执行时间从8秒降到了0.3秒以内，提升了将近27倍。

四、查询优化案例：一个多表关联SQL的调优过程

下面再分享一个更复杂的案例，涉及多表关联查询。

业务需求是：查询每个用户最近一次的订单信息，包括订单号、金额、状态和商品名称。

☆ 最初的SQL是这样写的：

sql

SELECT

u.nickname,

o.order_no,

o.amount,

o.status,

g.goods_name

FROM orders o

JOIN users u ON o.user_id = u.id

JOIN goods g ON o.goods_id = g.id

WHERE o.create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)

ORDER BY o.create_time DESC

LIMIT 1000;

☆ 优化前的Explain：

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE o ALL NULL NULL NULL NULL 5000000 Using where; Using temporary; Using filesort

1 SIMPLE u eq_ref PRIMARY PRIMARY 8 db.o.user_id 1 NULL

1 SIMPLE g eq_ref PRIMARY PRIMARY 8 db.o.goods_id 1 NULL

☆ 问题分析：

1、orders表做了全表扫描，这是主要的性能瓶颈。

2、虽然users和goods表都走了主键，但orders表扫描了500万行，前面两张表再快也没用。

3、Using temporary和Using filesort依然存在。

☆ 优化思路：

1、先给orders表的create_time建索引，解决WHERE条件的过滤问题。

2、考虑到ORDER BY create_time DESC和LIMIT 1000的组合，可以利用索引的有序性来避免filesort。

3、用子查询先把orders表的数据筛选出来，再关联其他表，减少关联时的数据量。

☆ 优化后的SQL：

sql

SELECT

u.nickname,

o.order_no,

o.amount,

o.status,

g.goods_name

FROM (

SELECT id, user_id, goods_id, order_no, amount, status

FROM orders

WHERE create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)

ORDER BY create_time DESC

LIMIT 1000

) o

JOIN users u ON o.user_id = u.id

JOIN goods g ON o.goods_id = g.id;

☆ 优化后的Explain：

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY ALL NULL NULL NULL NULL 1000 NULL

1 PRIMARY u eq_ref PRIMARY PRIMARY 8 o.user_id 1 NULL

1 PRIMARY g eq_ref PRIMARY PRIMARY 8 o.goods_id 1 NULL

2 DERIVED orders index idx_create_time idx_create_time 5 NULL 1000 Using where

☆ 关键变化：

1、orders表从全表扫描变成了索引扫描，而且只需要扫描1000行就够了。

2、Using temporary和Using filesort完全消失。

3、整体查询时间从6秒降到了0.1秒以内。

这个案例的核心思路就是：先用子查询把大表的数据量降下来，再去关联其他小表。如果直接三表关联，MySQL可能会先做关联再过滤，导致扫描大量无用数据。

五、Explain对比：优化前后差异一览

为了让大家更直观地感受优化的效果，我把两次优化的关键指标做了一个对比：

对比项 优化前（案例一） 优化后（案例一） 优化前（案例二） 优化后（案例二）

type ALL range ALL index

rows 5000000 85000 5000000 1000

Extra Using temp; Using filesort Using index Using temp; Using filesort Using where

执行时间 8秒+ 0.3秒 6秒 0.1秒

从这个对比可以非常清楚地看到，SQL优化的核心就两件事：让MySQL走对索引，让MySQL少做额外操作。只要把这两点做到位，大部分慢查询都能有数量级的提升。

六、日常开发中的SQL优化实用建议

基于上面这些实战案例，我总结了几条在日常工作中非常实用的建议：

1、写完SQL之后养成用Explain看执行计划的习惯，不要等到上线出了问题才去排查。很多性能问题在开发阶段就能发现，修复成本最低。

2、联合索引一定要遵循最左匹配原则。比如建了(a, b, c)的联合索引，WHERE条件中必须包含a才能用到这个索引。如果只有b和c，索引是用不上的。

3、绝对不要在WHERE条件中对字段使用函数。WHERE YEAR(create_time) = 2024这种写法会让索引直接失效，一定要改成范围查询的形式。

4、LIMIT分页在数据量大的时候要特别小心。LIMIT 1000000, 10这种写法会让MySQL先扫描1000010行再丢弃前1000000行，极其浪费。可以用游标或者子查询来优化。

5、一张表的索引不要超过5个。索引太多不仅占用空间，还会让优化器难以选择最优的执行计划。定期用工具检查一下有没有重复索引或者长期不用的冗余索引，该删就删。

6、对于持续增长的大表，要定期做数据归档。把半年前的历史数据迁移到冷存储，主表只保留热数据，查询速度会有质的飞跃。

七、总结

SQL优化这件事，说难也难，说简单也简单。难的地方在于需要对MySQL的执行原理有比较深的理解，简单的地方在于只要你掌握了Explain这个工具，大部分问题都能定位出来。

回顾今天讲的这些内容，其实所有优化手段都围绕一个核心目标：让MySQL扫描更少的数据，走更短的路径拿到结果。不管是建索引、改写SQL、调整表结构还是用子查询，最终都是为了这个目的。

希望这篇文章能帮你在下次遇到慢查询的时候不再慌张，而是能够冷静地打开Explain，一步步找到问题的根源，然后用最小的代价把它解决掉。

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

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

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

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

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

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