前几天一个真实的慢查询问题搞了挺久最后一步步优化下来学到了不少东西。这里把整个过程记录下来如果你也刚入门希望能帮到你。场景是什么后台管理系统有一个很常见的功能按创建时间倒序分页查询状态为已支付的订单同时需要关联用户表拿到用户昵称。原来的 SQL 长这样SELECTo.*,u.nicknameFROMorders oLEFTJOINusers uONo.user_idu.idWHEREo.status1ANDDATE_FORMAT(o.create_time,%Y-%m)2023-10ORDERBYo.create_timeDESCLIMIT100000,10;这条 SQL 在生产环境跑得很慢有时候直接超时。orders 表大概有两千万条数据。第一步用 EXPLAIN 看看 MySQL 在执行什么我在 SQL 前面加了个EXPLAIN跑了一下结果挺吓人的type那一列显示ALL意思是全表扫描两千万条数据一条一条过Extra里有Using filesort说明排序也没用到索引是额外做了一次文件排序key那一列是空的没走任何索引到这儿问题就比较清楚了缺索引加上对字段用了函数再叠加深分页和排序几个问题凑到一起了。第二步建一个合适的索引看了一下 WHERE 和 ORDER BY 用到的字段status和create_time都出现了。那就建一个联合索引ALTERTABLEordersADDINDEXidx_status_createtime(status,create_time);联合索引的好处是它先按status排好status一样的再按create_time排好。这样既能快速过滤出status 1的数据又因为create_time本身已经是有序的排序也省了。不过建完之后我又跑了一遍 EXPLAIN发现索引还是没生效。这就引出了下一个问题。第三步别让函数把索引搞失效了原始 SQL 里有一个DATE_FORMAT(o.create_time, %Y-%m) 2023-10。这个写法的意思是把create_time格式化成年-月的形式然后和2023-10比较。逻辑上没问题但对 MySQL 来说你在索引列上套了一层函数它就没法直接用索引了只能一行一行取出来算。解决方法很简单把函数运算改成范围查询WHEREo.status1ANDo.create_time2023-10-01 00:00:00ANDo.create_time2023-11-01 00:00:00效果一样但索引就能正常使用了。这个坑我后来发现挺多人踩的只要记住一条索引列上不要做任何计算或函数操作。第四步不要 SELECT *原来的 SQL 写的是SELECT o.*把 orders 表所有字段都查出来了。但实际上前端列表页只展示了订单编号、金额、用户昵称这几个字段。查了多余的字段有两个坏处一是如果没用到覆盖索引MySQL 还得拿着主键回主键索引表里取完整行数据这叫回表二是数据传输量变大了网络开销也跟着涨。改成只查需要的字段SELECTo.id,o.order_no,o.total_amount,u.nicknameFROMorders oLEFTJOINusers uONo.user_idu.idWHERE...关于那个LEFT JOIN其实也有优化空间。如果查询量大可以考虑在 orders 表里直接冗余一个nickname字段这样就变成了单表查询省掉了联表的开销。当然冗余字段会带来数据一致性的问题更新用户昵称的时候需要同步更新 orders 表这个要根据业务场景权衡。第五步解决深分页这一步我觉得是最关键的。原来的写法是LIMIT 100000, 10。这个写法的意思是跳过前 100000 条取 10 条。MySQL 实际上是查出了 100010 条数据然后把前 100000 条丢掉只返回最后 10 条。你翻的页数越深它浪费的越多。优化思路是用游标分页。前端每次请求的时候把上一页最后一条数据的 ID 传过来SQL 改成SELECTo.id,o.order_no,o.total_amount,o.nicknameFROMorders oWHEREo.id#{lastId}ANDo.status1ANDo.create_time2023-10-01 00:00:00ANDo.create_time2023-11-01 00:00:00ORDERBYo.idDESCLIMIT10;这样不管翻到第几页MySQL 都是从lastId的位置开始往后取 10 条性能是稳定的。不过这种方式也有局限它只能按顺序一页一页翻不能直接跳到第 500 页。好在大部分后台管理系统的列表页用户也不太会跳到那么后面去通常够用了。第六步表太大了拆一下orders 表两千万条数据B 树的层级会变高不管是查询还是写入磁盘 IO 次数都会增加。这时候可以考虑分表。水平拆分就是按行拆。比如按user_id做哈希取模分到 4 张表里或者按create_time按月分表每个月一张表。目标是让单表数据量控制在五百万以内。垂直拆分是按列拆。orders 表里可能有一些字段特别占地方但很少被查比如订单商品快照 JSON、发票信息之类的。把这些字段挪到一张orders_ext扩展表里主表就变轻了。主表每行占的空间小了一个数据页就能装更多行缓冲池的命中率也就上去了。分表这个事情说起来简单实际落地要考虑的东西很多比如跨表查询怎么搞、分布式 ID 怎么生成。我目前还没有实操过这里只是记录一下思路。第七步加缓存对于 C 端用户查我的近期订单这种场景读请求量大但数据变化没那么频繁适合用 Redis 做缓存。做法是把用户的近期订单列表序列化之后存到 Redis 里设一个过期时间比如 30 分钟。用户来查的时候先看缓存有没有有就直接返回不用打到 MySQL。那缓存和数据库的一致性怎么保证呢我了解到一种比较常用的方式叫旁路缓存模式Cache-Aside读请求先查缓存命中就返回没命中就查数据库查完写回缓存写请求比如下单、改订单状态先更新数据库更新成功后删掉缓存里对应的数据为什么是删缓存而不是更新缓存呢因为更新缓存可能出现并发问题两个请求同时更新最后缓存里的数据可能是旧的。删掉缓存让下次读的时候重新去数据库拿反而更安全。当然这只是一种基础方案实际生产中可能还会遇到删缓存失败、读写并发导致不一致等问题需要用延迟双删或者消息队列来兜底。这些我还在学后面搞明白了再写。效果这七步走下来最开始那条要跑好几秒甚至超时的 SQL优化到毫秒级别就能返回了。当然不是每一步都必须做要看具体的数据量和业务场景。数据量小的时候加个索引就够了数据量大了才需要分表、加缓存这些手段。如果你也是刚接触 SQL 优化希望这篇能帮你理清楚一个大致的思路。