一、问题初现：慢如蜗牛的查询系统
记得那是去年夏天，公司的订单系统突然开始卡顿。客服电话被打爆，运营同事急得直跳脚。我打开监控一看，好家伙，QPS（每秒查询量）才1000出头，数据库服务器CPU就已经跑到90%以上了。系统响应时间从平时的200ms飙升到2秒多，用户页面转圈转得让人心碎。
登录数据库一看，慢查询日志里全是这样的语句：
-- 技术栈：MySQL 8.0
-- 问题SQL示例（为了保护业务隐私，表名和字段已做脱敏处理）
SELECT o.order_id, o.create_time, u.username, p.product_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
LEFT JOIN products p ON o.product_id = p.product_id
WHERE o.status = 1
ORDER BY o.create_time DESC
LIMIT 1000;
这个看着普通的查询，执行时间竟然长达3.8秒！EXPLAIN一看，好家伙，全表扫描加上filesort，难怪这么慢。
二、第一轮优化：索引的魔法
首先从最明显的痛点下手 - 缺失的索引。我给status和create_time字段加了个联合索引：
-- 创建复合索引
ALTER TABLE orders ADD INDEX idx_status_create_time (status, create_time);
-- 修改后的查询（保持原逻辑不变）
SELECT o.order_id, o.create_time, u.username, p.product_name
FROM orders o FORCE INDEX(idx_status_create_time)
LEFT JOIN users u ON o.user_id = u.user_id
LEFT JOIN products p ON o.product_id = p.product_id
WHERE o.status = 1
ORDER BY o.create_time DESC
LIMIT 1000;
加了FORCE INDEX强制使用新索引后，查询时间直接从3.8秒降到0.2秒！QPS也从1000提升到了3000左右。不过好景不长，随着数据量继续增长，性能又开始下滑。
三、第二轮优化：拆解复杂查询
仔细分析业务场景发现，这个页面其实不需要每次都查1000条记录。和产品经理沟通后，我们决定改为分页查询，每页只查20条：
-- 分页查询优化
SELECT o.order_id, o.create_time, u.username, p.product_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
LEFT JOIN products p ON o.product_id = p.product_id
WHERE o.status = 1
ORDER BY o.create_time DESC
LIMIT 20 OFFSET 0; -- 第一页
-- 配合前端实现无限滚动加载
-- 每次滚动到底部时，OFFSET值递增20
这个改动让单次查询时间降到50ms以内。但用户频繁翻页时，OFFSET值越大查询越慢。于是我们又加入了游标分页：
-- 游标分页优化（基于最后一条记录的create_time）
SELECT o.order_id, o.create_time, u.username, p.product_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
LEFT JOIN products p ON o.product_id = p.product_id
WHERE o.status = 1 AND o.create_time < '2023-05-20 14:30:00' -- 上一页最后一条的时间
ORDER BY o.create_time DESC
LIMIT 20;
这种分页方式让查询时间稳定在了30ms左右，QPS提升到了5000。
四、第三轮优化：读写分离与缓存
随着用户量继续增长，单靠查询优化已经不够了。我们实施了读写分离方案：
- 主库负责所有写操作
- 两个从库负责读操作
- 使用ProxySQL实现负载均衡
同时，对于热点数据引入Redis缓存：
-- 伪代码示例（技术栈：MySQL + Redis）
-- 1. 先查缓存
cacheKey = "recent_orders:page:1"
cachedData = redis.get(cacheKey)
if cachedData:
return cachedData
-- 2. 缓存未命中则查数据库
dbData = execute_sql("""
SELECT o.order_id, o.create_time
FROM orders o
WHERE o.status = 1
ORDER BY o.create_time DESC
LIMIT 20
""")
-- 3. 写入缓存并设置5秒过期
redis.setex(cacheKey, 5, dbData)
这个组合拳让QPS突破了8000大关，但偶尔还是会出现缓存穿透问题。
五、终极优化：架构升级
最后的性能瓶颈出现在JOIN操作上。我们决定：
- 将订单列表改为只查订单表，用户和商品信息通过异步加载
- 对订单表进行水平分片（按用户ID取模）
- 引入Elasticsearch处理复杂搜索
改造后的核心查询变为：
-- 只查询订单表
SELECT order_id, user_id, product_id, create_time
FROM orders_shard_1 -- 根据用户ID路由到具体分片
WHERE status = 1
ORDER BY create_time DESC
LIMIT 20;
-- 用户信息批量查询（通过IN语句）
SELECT user_id, username FROM users
WHERE user_id IN (1, 5, 8, ...);
-- 商品信息同样批量查询
这套架构最终让我们的QPS稳定在了10000以上，99%的请求响应时间控制在100ms内。
六、经验总结与避坑指南
经过这次优化历程，我总结了几个关键点：
索引不是万能的：合理的索引确实能大幅提升性能，但索引过多会影响写入速度。我们曾经在一个表上建了10个索引，结果INSERT速度慢了5倍。
JOIN要谨慎：多表JOIN是性能杀手，能拆就拆。必要时可以考虑反范式化设计，用空间换时间。
缓存策略很重要：缓存时间太短起不到效果，太长又会导致数据不一致。我们最后采用了阶梯过期策略：热点数据5秒，普通数据30秒，冷数据5分钟。
监控不能少：我们搭建了Prometheus+Grafana监控体系，可以实时查看QPS、慢查询、缓存命中率等指标。
与业务方充分沟通：很多优化机会其实来自业务逻辑的调整。比如那个1000条的查询，产品经理最初坚持认为必须展示这么多。
这次优化让我深刻体会到，数据库性能调优是一个系统工程，需要从SQL优化、架构设计、硬件配置等多个维度综合考虑。希望这个案例能给遇到类似问题的朋友一些启发。
评论