企业官网建设流程全解析

前几天一个真实的慢查询问题，搞了挺久，最后一步步优化下来，学到了不少东西。这里把整个过程记录下来，如果你也刚入门，希望能帮到你。

场景是什么

后台管理系统有一个很常见的功能：按创建时间倒序，分页查询状态为"已支付"的订单，同时需要关联用户表拿到用户昵称。

原来的 SQL 长这样：

SELECTo.*,u.nicknameFROMorders oLEFTJOINusers uONo.user_id=u.idWHEREo.status=1ANDDATE_FORMAT(o.create_time,'%Y-%m')='2023-10'ORDERBYo.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.status=1ANDo.create_time>='2023-10-01 00:00:00'ANDo.create_time<'2023-11-01 00:00:00'

效果一样，但索引就能正常使用了。这个坑我后来发现挺多人踩的，只要记住一条：索引列上不要做任何计算或函数操作。

第四步：不要 SELECT *

原来的 SQL 写的是SELECT o.*，把 orders 表所有字段都查出来了。但实际上前端列表页只展示了订单编号、金额、用户昵称这几个字段。

查了多余的字段有两个坏处：一是如果没用到覆盖索引，MySQL 还得拿着主键回主键索引表里取完整行数据，这叫回表；二是数据传输量变大了，网络开销也跟着涨。

改成只查需要的字段：

SELECTo.id,o.order_no,o.total_amount,u.nicknameFROMorders oLEFTJOINusers uONo.user_id=u.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.status=1ANDo.create_time>='2023-10-01 00:00:00'ANDo.create_time<'2023-11-01 00:00:00'ORDERBYo.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）：

1. 读请求：先查缓存，命中就返回；没命中就查数据库，查完写回缓存
2. 写请求（比如下单、改订单状态）：先更新数据库，更新成功后删掉缓存里对应的数据

为什么是删缓存而不是更新缓存呢？因为更新缓存可能出现并发问题，两个请求同时更新，最后缓存里的数据可能是旧的。删掉缓存让下次读的时候重新去数据库拿，反而更安全。

当然这只是一种基础方案，实际生产中可能还会遇到删缓存失败、读写并发导致不一致等问题，需要用延迟双删或者消息队列来兜底。这些我还在学，后面搞明白了再写。

效果

这七步走下来，最开始那条要跑好几秒甚至超时的 SQL，优化到毫秒级别就能返回了。当然不是每一步都必须做，要看具体的数据量和业务场景。数据量小的时候，加个索引就够了；数据量大了，才需要分表、加缓存这些手段。

如果你也是刚接触 SQL 优化，希望这篇能帮你理清楚一个大致的思路。