一次 MySQL 连接数被打满，我花了一晚上重构了订单查询

2023 年双十一过后第二天早上七点被监控报警吵醒。MySQL 连接数飙到 800max_connections 才设的 1000基本快满了。登上去一看**processlist 里 80%都是同一个查询**——订单列表页的 count 查询。当时我们做跨境代购业务订单表一天新增十几万条用户端要展示订单状态、物流信息、商品快照还要支持按时间、状态、关键词搜索。最坑的是我们自研的 taocarts 系统初期设计时订单查询是直接 JOIN 了五张表订单主表、订单商品表、物流表、支付表、会员表。每次翻页都要先 count 一遍再 limit。用户一多直接炸了。## 问题出在哪先说结论**不是 MySQL 扛不住是查询设计就没考虑过数据量上去后的场景。**我们当时的订单表结构大概是这样的sqlCREATE TABLE order_main (id int(11) NOT NULL AUTO_INCREMENT,order_sn varchar(32) NOT NULL,user_id int(11) NOT NULL,status tinyint(4) NOT NULL DEFAULT 0,total_amount decimal(10,2) NOT NULL,create_time int(11) NOT NULL,PRIMARY KEY (id),KEY idx_user_id (user_id),KEY idx_status (status),KEY idx_create_time (create_time)) ENGINEInnoDB;看着没啥问题对吧索引也建了。但问题在于业务查询是这么写的php// 伪代码实际比这还复杂$count $db-query(SELECT COUNT(*) FROM order_main oLEFT JOIN order_goods g ON o.id g.order_idLEFT JOIN logistics l ON o.id l.order_idLEFT JOIN payment p ON o.id p.order_idWHERE o.user_id {$userId}AND o.status IN (1,2,3));**五张表 LEFT JOIN还要 count数据量到了百万级直接要了命。**当时 count 一次大概 1.2 秒用户翻页就要 count 一次并发一上来连接池全被占着等 count 返回后面的请求排队最终雪崩。## 重构思路分治缓存降级### 第一步干掉 JOIN count这是最蠢的地方。订单列表页的 count根本不需要关联那么多表。**订单状态、用户 ID 这些字段都在主表里**count 只需要主表就够了。php// 改造后$count $db-query(SELECT COUNT(*) FROM order_mainWHERE user_id {$userId}AND status IN (1,2,3));就这么简单的一改count 从 1.2 秒降到了 0.03 秒。**因为 order_main 表的 user_idstatus 联合索引能覆盖查询**不需要回表。### 第二步订单列表不走 JOIN用组装以前是一次查出来所有字段现在改成**先查主表再按需查子表**。php// 1. 查主表$orders $db-query(SELECT id, order_sn, user_id, status, total_amount, create_timeFROM order_mainWHERE user_id {$userId}ORDER BY create_time DESCLIMIT {$offset}, {$pageSize});// 2. 收集订单 ID$orderIds array_column($orders, id);// 3. 批量查子表$goods $db-query(SELECT * FROM order_goods WHERE order_id IN (.implode(,, $orderIds).));$logistics $db-query(SELECT * FROM logistics WHERE order_id IN (.implode(,, $orderIds).));// 4. PHP 里组装foreach ($orders as $order) {$order[goods_list] array_filter($goods, fn($g) $g[order_id] $order[id]);$order[logistics] array_filter($logistics, fn($l) $l[order_id] $order[id]);}看着好像查询次数变多了但**每个查询都是主键或索引查询单次耗时在毫秒级**。相比之前一次 JOIN 扫描几百万行性能提升是数量级的。### 第三步订单数量缓存用户每次翻页都要 count其实**大部分用户只看前几页**。我们加了一层文件缓存phpfunction getOrderCount($userId, $status) {$cacheKey order_count_{$userId}_ . implode(_, $status);$count S($cacheKey);if ($count false) {$count $db-query(SELECT COUNT(*) FROM order_mainWHERE user_id {$userId} AND status IN (.implode(,, $status).));S($cacheKey, $count, 60); // 缓存 60 秒}return $count;}**缓存命中率大概 70%**剩下的 30%是用户切换状态或者新订单产生。60 秒的过期时间对于订单列表来说完全够用用户不会感知到数据延迟。### 第四步分页改游标这是终极方案但改动较大我们只在用户量最大的几个接口用了。传统分页LIMIT 100000, 20MySQL 要扫描 100020 行然后丢掉前 100000 行。游标分页WHERE create_time {$lastCreateTime} ORDER BY create_time DESC LIMIT 20直接走索引定位。php// 前端传 last_id 或 last_create_time$lastTime $_GET[last_time] ?? time();$orders $db-query(SELECT id, order_sn, user_id, status, total_amount, create_timeFROM order_mainWHERE user_id {$userId}AND create_time {$lastTime}ORDER BY create_time DESCLIMIT 20);**性能差异巨大**传统分页翻到第 100 页假设每页 20 条游标分页比 LIMIT 分页快了两个数量级。实测数据LIMIT 2000,20 耗时 0.08 秒游标分页 0.001 秒。## 最终效果重构完成后同样的业务场景**MySQL 连接数从 800降到了 50 左右**。订单列表页的接口响应时间从平均 3 秒降到了 200 毫秒以内。但也不是没有代价- **代码复杂度增加了**。以前一个 SQL 搞定现在要拆成多个查询PHP 组装。- **缓存一致性要自己维护**。用户下单后要主动清除对应的 count 缓存。- **游标分页不能跳页**。用户只能上一页下一页不能直接跳到第 50 页。这个我们做了个折中前 10 页用传统分页10 页之后用游标。## 适用场景这套方案最适合**订单、日志、流水这类写多读多、查询条件相对固定的业务**。如果是 CMS 文章列表这种读多写少、查询条件复杂的场景更适合用搜索引擎或者专门的查询服务。有更好的方案欢迎评论区交流。