记得那是一个周五的傍晚，公司的监控大屏突然红了一片。报警信息像瀑布一样刷下来：“核心接口响应时间超过 5 秒”。作为当时的值班负责人，我盯着那条该死的慢查询日志，心里清楚：又是那个被业务方疯狂刷新的“热门帖子详情页”。
那一刻，我深刻意识到，数据库优化不仅仅是写几个好索引那么简单，它是一场涉及应用层、缓存层、SQL层甚至物理层的系统工程。今天，我想把当时那个从“卡顿”到“毫秒级”的完整复盘过程分享给你，不讲大道理，只讲我们是怎么一步步把问题啃下来的。
第一阶段：止血与定位，先让系统活下来
当报警响起时，我们的首要目标不是“彻底修复”，而是“止血”。如果数据库直接被打挂，那才是灾难性的。
1.1 紧急查看连接数与锁等待
我们第一时间登录服务器，执行了几条最基础但至关重要的命令。很多人跳过这一步直接去改代码，这是非常危险的，因为你不知道瓶颈到底在哪。
-- 查看当前活跃连接数和最大连接数
SHOW PROCESSLIST;
-- 查看当前存在的锁等待情况
SELECT * FROM information_schema.INNODB_TRX;
在那个案例中，我们发现 Threads_running（当前正在执行的线程数）飙到了 50+，而我们的数据库最大连接数只配了 200。这意味着数据库已经处于过载边缘。更糟糕的是，有一张 hits 表（用于记录浏览量）出现了大量的 INSERT 锁竞争，导致读取请求也被阻塞。
这里有个很多人不知道的坑： 高并发下的 COUNT(*) 或者大表的 INSERT 可能会导致表级锁或间隙锁，进而阻塞 SELECT 查询。如果你发现查询阻塞，先看看是不是有别的操作在“堵路”。
1.2 快速熔断：引入本地缓存
在等待DBA调整参数和改代码的空档期，我们做了一个临时的“土办法”——在应用层（Java/Go）增加了一个 Caffeine/Guava 本地缓存。
对于 PV（Page View）计数，其实并不需要“强一致性”。也就是说，如果一秒钟内有 100 次点击，缓存里只记了 90 次，或者延迟了 5 秒才同步到数据库，对用户来说是完全无感的。
// 伪代码：本地缓存策略
// 缓存 key: post_id, value: 缓存的 PV 值
// 过期时间: 30 秒
Cache<String, Long> pvCache = Caffeine.newBuilder()
.expireAfterWrite(30, TimeUnit.SECONDS)
.maximumSize(10000)
.build();
// 获取 PV
public long getPv(Long postId) {
return pvCache.get(String.valueOf(postId), key -> {
// 只有缓存穿透时才查数据库
return db.queryPv(postId);
});
}
这个动作直接将数据库的读压力下降了 90%。大屏上的红色报警终于开始转绿，系统活了。但这只是权宜之计，接下来我们要解决根本问题。
第二阶段：抽丝剥茧，找到那个“罪魁祸首”
系统稳定后，我们开始复盘那条让服务器“窒息”的 SQL。
2.1 慢查询日志分析
数据库自带的慢查询日志（Slow Query Log）是我们的第一份证据。我们配置了 long_query_time = 1（即超过 1 秒的查询都记录），然后从日志中提取出最频繁的那几条。
-- 典型的慢 SQL 长这样
SELECT
post_id,
title,
content,
author_id,
created_at,
(SELECT COUNT(*) FROM comment WHERE post_id = p.post_id) as comment_count,
(SELECT COUNT(*) FROM pv_log WHERE post_id = p.post_id) as view_count
FROM post p
WHERE p.status = 1
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
乍一看，这 SQL 好像也没什么大问题？不就是查个文章列表吗？但是，请注意那两个子查询！
2.2 执行计划揭秘：EXPLAIN 不会说谎
当我们用 EXPLAIN 分析这条 SQL 时，真相令人触目惊心：
| id | select_type | table | type | possible_keys | key | rows | Extra |
|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | p | ALL | NULL | NULL | 5000000 | Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | comment | ref | idx_post_id | idx_post_id | 15 | NULL |
| 3 | DEPENDENT SUBQUERY | pv_log | ref | idx_post_id | idx_post_id | 8000 | NULL |
解读：
- 全表扫描：主查询对 500 万行的
post表进行了全表扫描，并且用了filesort（文件排序），这在大数据量下是致命的。 - 相关子查询：最可怕的是那两个
DEPENDENT SUBQUERY。对于主查询查出的每一行（最多 500 万行），数据库都要去comment和pv_log表里再查一次。 - 计算量爆炸：粗略估算，这可能是 \(5,000,000 \times 15\) 次索引查询，数据库 CPU 直接被干爆。
这就是为什么查询需要 5 秒以上。你以为你在查 20 条数据，实际上数据库在为你“脑补”整个表的数据关联。
第三阶段：SQL 与索引的重构艺术
找到病因后，我们开始了具体的优化手术。
3.1 消除相关子查询，改用 JOIN 或分离查询
首先，我们要干掉那两个子查询。对于 comment_count，我们可以用 LEFT JOIN 配合 GROUP BY，但更好的方式是分离查询。
业务场景是：用户看列表，不需要实时知道每一篇文章精确到个位的评论数。
方案 A：预计算（推荐）
在 post 表中增加 comment_count 和 view_count 字段。每次新增评论或浏览时，异步或同步更新这个字段。查询时直接 SELECT 这个字段。
-- 优化后的 SQL
SELECT
post_id,
title,
content,
author_id,
created_at,
comment_count, -- 直接查表字段，O(1) 复杂度
view_count -- 直接查表字段，O(1) 复杂度
FROM post
WHERE status = 1
ORDER BY created_at DESC
LIMIT 20;
方案 B：如果不允许改表结构，用 JOIN 代替子查询
SELECT
p.post_id,
p.title,
p.content,
COALESCE(c.cnt, 0) as comment_count,
COALESCE(v.cnt, 0) as view_count
FROM post p
LEFT JOIN (
SELECT post_id, COUNT(*) as cnt
FROM comment
GROUP BY post_id
) c ON p.post_id = c.post_id
LEFT JOIN (
SELECT post_id, COUNT(*) as cnt
FROM pv_log
GROUP BY post_id
) v ON p.post_id = v.post_id
WHERE p.status = 1
ORDER BY p.created_at DESC
LIMIT 20;
注意：方案 B 虽然消除了相关子查询，但对于 500 万数据的大表，LEFT JOIN 大结果集仍然可能较慢，且内存消耗大。预计算字段是互联网大厂的通用做法。
3.2 索引优化：覆盖索引与最左前缀
回到优化后的 SQL，主查询依然有 ORDER BY created_at DESC 和 LIMIT 20。如果 created_at 上没有索引，或者索引选择不当，数据库依然会全表扫描后排序。
我们需要在 post 表上建立联合索引：
-- 创建联合索引
CREATE INDEX idx_status_created ON post(status, created_at);
为什么是 (status, created_at) 而不是 (created_at, status)？
因为 status = 1 是一个等值查询，能快速过滤掉大量无效数据（比如已删除的文章）。而 created_at 是范围/排序字段。根据最左前缀原则，等值列应该放在前面，这样索引的筛选效率最高。
再次 EXPLAIN：
type变成了ref或range。key使用了idx_status_created。Extra变成了Using index condition，甚至如果查询字段都在索引里，会变成Using index（覆盖索引），完全不用回表查询。
3.3 深分页优化：避免 OFFSET 1000000
PV 高的帖子，评论区可能很深。如果用户翻页翻到第 1 万页，传统的 LIMIT 1000000, 10 会扫描 100 万行然后丢弃前 999990 行，这简直是在自杀。
优化方案：游标法（Seek Method）
假设我们上次翻页最后一条数据的 id 是 1000000，那么下一页只需要查：
SELECT * FROM post
WHERE status = 1 AND created_at < '上次最后一条的时间'
ORDER BY created_at DESC
LIMIT 10;
这样，无论翻到第几页，查询量都是常数级的。
第四阶段：架构层面的降维打击
SQL 优化虽然有效，但在巨大的 PV 流量面前，数据库依然是脆弱的。真正的毫秒级响应，必须依靠分层架构。
4.1 读写分离与独立 PV 存储
我们将 pv_log 表从主库剥离出来，单独放入一个专用的“写库”中。因为 PV 记录是典型的写多读少（除了统计报表，极少有人实时查每一条评论的 PV 明细）。
主库只保留文章核心信息（标题、内容、作者），而 PV 数据通过异步消息队列（Kafka/RabbitMQ）堆积，由定时任务批量汇总到 ES 或专门的 Redis 集群中，供统计大盘使用。
4.2 Redis 缓存热点数据
对于首页推荐、热门帖子列表这类热点数据，我们将其整个序列化存入 Redis。
# 结构：Hash
HSET post:hot:20231027 post_id "1001" title "标题A" pv "99999" ...
HSET post:hot:20231027 post_id "1002" title "标题B" pv "88888" ...
当请求进来时，应用层先查 Redis。如果命中（Hit），直接返回，耗时 < 5ms。如果未命中（Miss），再查 DB 并回写 Redis，设置 TTL（如 5 分钟）。
4.3 异步计数：解决高并发写问题
PV 的核心痛点在于“写”。每一秒可能成千上万次点击，每次点击都去写 MySQL 的 pv_log 表，锁竞争是避免不了的。
我们的最终方案是本地计数 + 批量同步：
- 用户访问页面，应用层的内存计数器
+1。 - 后台线程每隔 10 秒，或者累计满 100 次时，批量执行一次
INSERT或UPDATE到数据库。 - 或者，将 PV 增量消息发送到 Kafka，由消费者统一写入数据库。
这样，数据库的写压力从 QPS 10,000 降到了 QPS 1（每秒一条批量 SQL），堪称降维打击。
第五阶段：监控与持续治理
优化不是一次性的工作。在这次战役后，我们建立了一套长效机制：
- 慢查询每日巡检：自动化脚本每天扫描慢查询日志，生成报告推送到开发群。
- 索引缺失监控：使用 pt-index-usage 等工具，定期检查哪些索引从未被使用，果断删除。无用索引会拖慢写速度。
- 连接池调优：监控 HikariCP 或 Druid 的连接池使用率，确保
maximum-pool-size与数据库max_connections匹配，避免连接泄漏。
结语：从“救火”到“防火”
回想那个周五的夜晚，从报警响起，到本地缓存止血，再到 SQL 重构、架构拆分，整个过程耗时约 4 小时。最终，该接口的 P99 响应时间从 5200ms 降到了 15ms。
数据库优化没有银弹，它需要：
- 冷静的心态：先止血，再治病。
- 扎实的基础：精通
EXPLAIN，理解索引原理。 - 架构的思维：能用缓存解决的，不要打到数据库；能用异步解决的，不要同步等待。
希望这篇指南能帮你在面对数据库性能瓶颈时，不再手忙脚乱。记住，每一个毫秒的提升，都是对用户体验最实实在在的尊重。