别再瞎加索引了！这才是索引策略的正确打开方式

别再瞎加索引了这才是索引策略的正确打开方式你有没有遇到过这种情况线上接口突然变慢用户投诉一大堆你打开慢查询日志一看一条看起来很普通的SQL竟然跑了8秒钟。你加了索引没用你改了配置还是慢。最后折腾了半天才发现问题根本不在索引本身而在于你根本没搞懂MySQL到底是怎么执行这条SQL的。今天这篇文章我拿一个真实的订单查询案例从头到尾演示一遍索引策略该怎么定、Explain该怎么看、SQL该怎么改。全部是实战经验没有一句废话。数据库工程与索引策略示例从全表扫描到毫秒响应的调优实录一、先看事故现场一条订单SQL把接口搞崩了上周我们的订单管理系统出了一次线上事故。用户端的我的订单列表接口平时响应时间在40到60毫秒之间算比较正常。但那天中午流量高峰的时候这个接口突然飙到了8秒多前端直接报超时用户那边投诉电话一个接一个打过来。先把这条出问题的SQL贴出来sqlSELECTo.id, o.order_no, o.user_id, o.total_amount, o.status, o.create_time,u.nickname, u.phoneFROM order oLEFT JOIN user u ON o.user_id u.idWHERE o.status IN (1, 2, 3)AND o.create_time 2025-01-01 00:00:00AND o.create_time 2025-12-31 23:59:59ORDER BY o.create_time DESCLIMIT 20;这条SQL看着是不是特别普通订单表order大概有850万条数据用户表user有140万条。查询条件是按状态筛选加时间范围最后按创建时间倒序取前20条。☆ 关键背景order表当时只有一个单列索引idx_create_time没有任何联合索引。二、第一刀用Explain把执行计划扒开看调优SQL第一步永远是看Explain不看执行计划就加索引跟闭着眼睛打针没什么区别。我直接跑了一遍sqlEXPLAIN SELECTo.id, o.order_no, o.user_id, o.total_amount, o.status, o.create_time,u.nickname, u.phoneFROM order oLEFT JOIN user u ON o.user_id u.idWHERE o.status IN (1, 2, 3)AND o.create_time 2025-01-01 00:00:00AND o.create_time 2025-12-31 23:59:59ORDER BY o.create_time DESCLIMIT 20;执行结果如下id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE o ALL idx_create_time NULL NULL NULL 8523641 Using where; Using filesort1 SIMPLE u eq_ref PRIMARY PRIMARY 8 o.user_id 1 NULL一看就知道问题在哪了1、order表的type是ALL全表扫描850多万行全部过了一遍。2、Extra里有Using filesort说明排序没用上索引MySQL额外做了一次文件排序这个在大数据量下非常耗性能。3、possible_keys显示idx_create_time是可用的但key是NULL说明这个索引压根没用上。原因很简单WHERE条件里还有status IN (1,2,3)这个字段不在idx_create_time索引里MySQL觉得用索引还不如直接全表扫描。三、第一轮优化加联合索引看Explain对比找到原因之后我建了一个联合索引sqlALTER TABLE orderADD INDEX idx_status_create_time (status, create_time);这里必须说一下字段顺序的问题这是很多人最容易搞错的地方1、等值匹配的字段必须放在最左边。 status IN (1,2,3)虽然是多值但本质上属于等值匹配应该放在联合索引的第一位。2、范围查询的字段放在等值字段后面。 create_time ? AND create_time ?是范围查询放在第二位。3、排序字段要能接上索引才有效。 ORDER BY create_time DESC因为create_time在索引的第二位当status确定之后create_time本身就是有序的MySQL可以直接按索引顺序取数据不需要额外排序。加完索引再跑一次Explainid select_type table type possible_keys key key_len ref rows Extra1 SIMPLE o range idx_status_create_time idx_status_create_time 12 NULL 178945 Using index condition1 SIMPLE u eq_ref PRIMARY PRIMARY 8 o.user_id 1 NULL效果非常明显我把加索引前后的数据做了一个对比表对比项 加索引前 加索引后 变化幅度type ALL全表扫描 range索引范围扫描 质变扫描行数 8523641 178945 降低约47.6倍Extra Using filesort Using index condition 文件排序消失key NULL idx_status_create_time 索引生效执行时间 8200ms 52ms 提升约158倍从8秒降到52毫秒已经非常可以了。但我觉得还没完因为MySQL虽然走了索引但还是扫描了将近18万行才找到前20条。LIMIT 20是在扫描完所有符合条件的行之后才生效的并不是边扫边取。四、第二轮优化用子查询改写扫描行数直接降到20☆ 核心思路能不能让MySQL只扫描20行就完事答案是可以的。方法是用子查询先把ID取出来再去关联用户表sqlSELECTo.id, o.order_no, o.user_id, o.total_amount, o.status, o.create_time,u.nickname, u.phoneFROM (SELECT id, order_no, user_id, total_amount, status, create_timeFROM orderWHERE status IN (1, 2, 3)AND create_time 2025-01-01 00:00:00AND create_time 2025-12-31 23:59:59ORDER BY create_time DESCLIMIT 20) oLEFT JOIN user u ON o.user_id u.id;这个改写的精髓在于先在order表上利用联合索引直接取出最新的20条记录然后再去关联user表。这样order表的扫描行数从18万直接降到了20行。再看Explain结果id select_type table type possible_keys key key_len ref rows Extra1 PRIMARY ALL NULL NULL NULL NULL 20 NULL1 PRIMARY u eq_ref PRIMARY PRIMARY 8 o.user_id 1 NULL2 DERIVED order index idx_status_create_time idx_status_create_time 12 NULL 20 Using where这次的数据更漂亮了1、order表的type变成了index说明完全按索引顺序在取数据。2、扫描行数直接变成了20一行多余的都没扫。3、Extra里没有Using filesort了因为ORDER BY create_time DESC直接走了索引的倒序。执行时间从52毫秒进一步降到了28毫秒。从最初的8.2秒到现在的28毫秒整体提升了将近293倍。五、三轮方案的完整Explain对比为了让大家看得更清楚我把三个方案的关键指标全部汇总成了一张表方案 type 扫描行数 Extra 执行时间 提升倍数原始SQL无索引 ALL 8523641 Using filesort 8200ms 基准方案一加联合索引 range 178945 Using index condition 52ms 约158倍方案二子查询改写 index 20 无 28ms 约293倍这张表非常能说明问题索引解决了能不能走索引的问题SQL改写解决了走了索引还要扫多少行的问题。两步都做了才能把性能拉到极致。六、实战中容易踩的几个坑1、☆ 联合索引的字段顺序是灵魂。很多人习惯性把create_time放前面觉得时间查询最常见。但一旦create_time在第一位status就彻底用不上索引了因为范围查询之后的字段全部失效。记住一句话等值在前范围在后排序接最后。2、☆ IN和在索引使用上是等价的都算等值匹配。但如果IN里面的值特别多比如超过十几个MySQL的优化器可能会觉得全表扫描更划算这时候可以考虑拆成多个UNION ALL。3、☆ LEFT JOIN的时候右表的关联字段必须有索引。上面例子里user.id是主键天然有索引所以没问题。但实际业务中很多关联字段是普通字段根本没建索引这又是一个大坑。4、☆ LIMIT优化一定要配合子查询。不加子查询的话MySQL还是会先找到所有符合条件的行排序之后再取前N条。这个技巧在数据量超过百万级别的时候特别管用差一个子查询性能可能差几十倍。5、☆ 别太迷信Explain里的rows数值。那只是MySQL的估算值实际扫描行数可能会有偏差。最靠谱的验证方式还是看实际执行时间和慢查询日志里的实际扫描行数。6、☆ 索引不是越多越好。每个索引都会占用磁盘空间而且每次写操作都要维护索引。联合索引能覆盖的场景就不要建两个单列索引。上面这个案例一个idx_status_create_time就同时覆盖了status查询、create_time范围查询和create_time排序比两个单列索引强得多。七、总结索引策略的核心方法论回顾这次调优其实就三步但每一步都不能少1、看Explain定位瓶颈在哪。 全表扫描、文件排序、索引失效这些问题在执行计划里一目了然别靠猜。2、建对索引选对字段顺序。 等值在前、范围在后、排序接尾这是联合索引的黄金法则背下来能少走很多弯路。3、改写SQL用子查询把扫描行数压到最低。 索引解决了能不能走索引的问题SQL改写解决了走了索引还要扫多少行的问题。两步都做了才能把性能拉满。说到底SQL优化不是什么黑魔法就是对MySQL执行逻辑的深刻理解。你越清楚它是怎么一步步执行查询的你就越知道该在哪里动手。希望这篇文章能帮你在下次遇到慢查询的时候少折腾几个小时。毕竟生产环境的每一秒卡顿背后大概率都是一条没优化好的SQL在搞事情。注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口https://blog.csdn.net/Start_mswin 复制到【浏览器】打开即可,宝贝入口https://pan.quark.cn/s/b42958e1c3c0 宝贝https://pan.quark.cn/s/1eb92d021d17作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围