有一次周五下午两点十九分我盯着监控屏上的曲线数据库 CPU 从 12% 一路飙到 97%连接数打满订单接口的响应时间从 80ms 涨到三秒开外。群里已经开始刷“怎么回事”“要不要重启”“谁动线上配置了”。这种场面大多数后端和 DBA 都不陌生平时好好的系统一到业务高峰就变脸慢查询、锁等待、连接耗尽一套组合拳下来服务直接“半死不活”。这篇文章就把这件事讲透——高峰期数据库扛不住的根因是什么现场怎么排障哪些操作能立刻止血哪些优化能治本以及怎么通过压测和容量规划把“扛不住”变成“早就知道扛不住”。1. 高峰期“扛不住”的第一现场先搞清楚瓶颈长什么样1.1 数据库告警时的典型症状先说说“扛不住”长什么样。不同业务形态、不同数据库版本表现可能不一样但核心症状比较集中我按出现频率排一下。连接数打满应用日志里出现类似“Too many connections”的报错新请求拿不到数据库连接直接失败。这个最常见而且最容易引起恐慌——因为看起来像数据库挂了。CPU 使用率飙升用户态 CPU 高说明 SQL 本身在大量计算系统态 CPU 高说明线程切换、锁竞争、IO 等待导致的上下文切换太频繁。慢查询数量激增平时慢查询日志里一天只有几条高峰时每分钟几百条而且某些 SQL 的单次执行时间比平时慢了一个数量级。锁等待和死锁变多show engine innodb status里频繁出现锁等待信息应用日志报 Lock wait timeout exceeded。磁盘 IO 延迟变大iostat看到await达到几十甚至上百毫秒说明磁盘在读上已经忙不过来了一般伴随大量的全表扫描或临时表落盘。内存不足或 Swap 占用InnoDB Buffer Pool 命中率下降大量查询直接走磁盘响应时间随之恶化。这些症状往往一起出现但总有一个是“起点”。比如先是一条糟糕 SQL 打满 CPU然后查询全部变慢、事务持有锁的时间变长、连接不释放、连接池耗尽最后表现为“数据库挂了”。只看表象去重启或扩容大概率治标不治本重启之后下个高峰照样崩。1.2 一次 CPU 打满的快速定位记录我用一个实际见过的场景举例。某个系统午高峰突然接口超时监控显示 CPU 99%连接池被占满。当时第一件事不是看监控大屏而是直接登录数据库执行了下面这条命令SHOW FULL PROCESSLIST;结果基本可以分成三类大量 SELECT 处于Sending data状态、个别 UPDATE 处于Updating状态、还有几个连接卡在Waiting for table metadata lock。再配合慢查询日志找到了一条统计类 SQL——join 了 8 张表其中一张核心业务表没走索引单次执行花了 4 秒高峰期被调用了几百次直接把 CPU 吃满。注意一个细节那条 SQL 平时跑 1 秒多虽然不理想但业务量小的时候感觉不出来一旦并发上来每条 SQL 多出的 2~3 秒就会把数据库的处理能力全部拖住。这引出一个关键问题为什么很多性能问题平时不暴露非要等到高峰才爆发下一部分讲底层逻辑。2. 为什么平时没事、一到高峰就崩性能衰减的底层逻辑2.1 排队效应和雪崩是怎么发生的数据库本质上是“请求-处理-返回”的排队系统。假设数据库每秒最多处理 500 个请求平时业务每秒只来 300 个多余的处理能力全部空闲每请求平均响应时间很短。高峰期每秒来了 600 个请求超出的 100 个就只能在队列里等着。关键在于排在后面的请求等待时间并不只是“100 个请求的处理时间”这么简单——前面每个请求的处理时间在负载升高时也会变长比如锁竞争、IO 排队、CPU 上下文切换于是队列越来越长最终形成一个恶性循环负载越高等得越久等得越久连接占用越久连接占用越久新请求越进不来。你可以把它理解成餐饮门店排队平时翻台快等位五分钟周末客流翻倍但每桌吃饭时间也从三十分钟拉长到一个小时等位人数就会指数级上涨。数据库也是这样到达率超过处理能力的瞬间响应时间不是线性增长而是像滚雪球一样恶化。这里有个判断点所谓“扛不住”往往不是数据库“完全不干活”而是处理能力被排队消耗殆尽有效吞吐降到了业务需要以下。所以扩容机器只能缓解一部分问题——如果根因是某一条 SQL 本身消耗过大加再多机器也挡不住那一条 SQL 把资源吃光。2.2 慢查询的放大效应“一条慢 SQL 拖垮整个库”在高峰期特别常见。我见过一个真实案例某统计报表功能新上线一条 SQL 要全表扫描近百万行单次执行约 2 秒平时每天只被手动打开几次大家都没注意。大促那天这个报表被加入了大屏自动刷新每 5 秒跑一次全表扫描频繁触发直接把数据库 CPU 打满核心交易接口跟着超时。这里的关键是“放大效应”慢 SQL 占用的不只是它自己的执行时间还包括连接资源、内存临时表、磁盘 IO、锁。当它把 CPU 打满时原本 10ms 能执行完的快查询也被迫排队表现为“什么都没改系统突然就慢了”。更隐蔽的是某些场景下慢查询不直接出现在业务接口的调用链路上而是来自定时任务、报表系统、数据同步任务排查时要留意这些“隐性消费者”。2.3 锁竞争在并发场景下被成倍放大锁是数据库并发控制的核心机制平时事务执行快锁的持有时间以毫秒计锁竞争几乎无感。但高峰期事务变慢之后锁的持有时间被拉长比如一个 UPDATE 本来 5ms 能提交因为前面排队、IO 变慢变成了 50ms那后续针对同一行数据的更新都要多等 45ms。如果有大量热点行的并发更新就会形成一个明显的“锁等待队列”表现就是活跃会话数很高、但吞吐量上不去、CPU 也未必高因为大家都在等待锁而不是在计算。最常见的坑是热点行更新典型场景就是库存扣减所有人都在 update 同一件商品的库存记录行锁天然串行并发再高也只能一个个过。这种问题靠加索引、加缓存都未必有效需要从业务设计层面去解决比如库存分片、异步扣减、Redis 预扣。后面我会专门讲。2.4 连接数并非越高越好很多团队遇到数据库变慢第一反应是把连接池调大觉得“连接多了吞吐肯定上来”。但这里有一个常见的反直觉现象连接数的增加不会线性提升吞吐超过某个阈值后吞吐反而下降。原因不复杂。每个数据库连接都要占用内存线程栈、排序缓冲区、临时表空间等MySQL 为例每个连接在内部还可能有独立的排序和临时表资源。更重要的是连接多了以后线程上下文切换成了 CPU 的主要开销大量 CPU 时间被花在“切换线程”而不是“执行 SQL”上。另一个问题在于应用侧如果应用线程池也开得很大几千个线程同时去数据库抢连接请求队列和线程切换会形成双重压力数据库连接层反而成了最脆弱的地方。所以连接池不是越大越好合理阈值需要结合压测数据和数据库配置去定。这也解释了为什么某些系统一到大促就“调连接池、加机器、重启”三件套做完还是崩——方向就搞反了。3. 排障方法论从指标到根因的诊断路线3.1 五个必看的核心指标真实排障时先看哪些指标最容易判断问题在哪一层我按优先级整理如下。指标怎么查说明CPU 使用率top / dstat区分用户态与系统态用户态高偏 SQL 计算系统态高偏连接/上下文切换活跃线程数show processlist观察有多少会话真正在执行 SQL而不是睡眠连接数show global status like Threads_connected和 max_connections 对比看是否逼近上限慢查询数量show global status like Slow_queries和基线对比看是否激增锁等待与临时表show engine innodb status看锁等待、事务堆积、临时表使用情况这五个指标要结合起来看不能单看某一个。比如 CPU 不高但接口很慢大概率是锁等待或者 IO 瓶颈连接数快满了但 CPU 很低大概率是连接被大量空闲会话占住或者存在“获取连接后不释放”的应用代码问题。3.2 抓取当前会话与慢查询排障现场第一件事永远是看当前正在干什么。我在 1.2 节里提到过SHOW FULL PROCESSLIST这里补充一些使用细节。-- 查看当前所有连接的状态分布 SELECT command, state, count(*) FROM information_schema.processlist GROUP BY command, state; -- 按执行时间倒序看最“拖”的会话 SELECT id, user, host, db, command, time, state, info FROM information_schema.processlist ORDER BY time DESC LIMIT 20;第二句最重要。执行时间最长的会话不一定就是问题根源但大概率是“被卡住”的代表。比如大量会话卡在Waiting for table metadata lock说明有人持有了一张表的元数据锁通常是有 DDL 操作长时间未提交大量会话卡在Sending data说明确实有慢查询在执行大量会话卡在Updating则要看是不是锁等待。慢查询日志也要提前配置好。生产环境建议开启以下参数slow_query_log ON long_query_time 1 log_queries_not_using_indexes ONlong_query_time可以按业务情况调整一般 1 秒是合理起点如果业务还不算大也可以先调到 0.5 秒抓一段时间的全量慢查询分析归类之后再改回来。需要注意开启慢查询日志本身也有性能开销但以现在的硬件水平看这笔开销远小于问题本身的代价建议默认开启。3.3 读懂执行计划判断索引是否走对定位到具体 SQL 后用EXPLAIN看执行计划。重点看三列type、key、rows。type是访问类型从好到差大致是system→const→eq_ref→ref→range→index→ALL。看到ALL说明全表扫描优先怀疑索引缺失。key指示实际使用的索引如果为 NULL就是没走索引。rows是优化器预估需要扫描的行数实际扫描行数比预期大很多或者rows达到几十万上百万就该检查索引设计和数据分布了。另外还要看Extra列出现Using filesort说明排序走外部文件可能引发临时表落盘出现Using temporary说明用了临时表大概率会造成磁盘 IO 压力出现Using index是好的信号说明覆盖索引生效。这里有一个重要提醒rows是预估值可能因为统计信息过旧而严重失准这时候可以执行ANALYZE TABLE刷新统计信息再看执行计划。4. 优化实战先救火再治本4.1 能立刻止血的临时手段线上已经出问题了第一步永远是止血而不是马上改架构。以下手段按效率排序。杀掉最耗资源的会话。通过SHOW FULL PROCESSLIST找到执行时间特别长、info里能看到明确 SQL 的会话评估后KILL id。注意先确认这不是核心事务杀错了可能导致业务回滚我通常在高峰期只杀“非关键路径”的查询类会话。给应用设置合理的超时和熔断。比如数据库连接超时设置短一点让应用快速失败而不是无限等待或者对非核心功能做降级处理先把核心交易的流量保住。临时扩容只读节点。如果读流量是主要压力来源可以快速给 MySQL 加一个只读从库把读请求分担过去。前提是主从延迟在可接受范围。临时调低某些参数比如把lock_wait_timeout调小让锁等待的会话更快放弃而不是长时间挂在库里。救火手段不要长期保留理清根因后要及时恢复常规配置。比如杀会话只能在故障期间用长期依赖杀会话会导致核心事务被误伤。4.2 缓存和读写分离把流量挡在数据库外面真正治本的第一步是把不该打到数据库上的流量挡在外面。先看读多写少的场景这是最常见的优化机会。热点数据上缓存是性价比最高的手段。一个商品详情页假设每秒有 5000 次查询其中 90% 都在读同一个商品的信息不加缓存的时候这 5000 次请求全部打到 MySQL加一层 Redis 缓存之后命中率按 90% 算MySQL 只需要承担 500 次请求压力直接降一个数量级。缓存失败的场景要设置短过期时间做保护避免缓存雪崩时流量全量冲回数据库。再说读写分离。MySQL 主从架构已经非常成熟业务上按照读写比例拆分流量写操作走主库读操作走从库。要注意两个问题一是主从延迟如果业务逻辑要求“写完立即读到刚写入的数据”这种强一致读不能路由到从库二是从库也不是无限扩展的每个从库都在消费主库的 binlog 同步数据如果写入量极大从库的同步也会成为瓶颈。4.3 SQL 重写与索引设计的关键细节在优化完“要不要查数据库”之后再优化“怎么查数据库”。索引设计有一些高频踩坑点我挑几个重点说。首先是最左前缀原则联合索引(user_id, status, created_at)可以支持user_id...和user_id... AND status...的查询但不能直接支持status...的查询。很多同学建了联合索引却命中不了就是没搞懂这一点。其次是不要在索引列上做函数运算或隐式类型转换。比如WHERE DATE(created_at) 2024-01-01这会让索引失效应该改写为WHERE created_at 2024-01-01 AND created_at 2024-01-02。隐式类型转换更隐蔽比如WHERE user_id 123如果user_id是数值类型字符串和数值比较会触发转换同样导致索引失效。此外覆盖索引是一个性价比非常高的优化手段。业务上最常见的查询是“根据某个条件取出某些字段”如果建立一个覆盖所有查询字段的联合索引查询可以直接从索引中取数连回表都省了磁盘 IO 和内存消耗都会大幅下降。但索引也不是越多越好每一个索引都会拖慢写入速度、占用额外存储空间所以要用实际业务查询模式来设计而不是“把所有字段都建上索引”。SQL 重写方面分页查询是一个经典优化点。ORDER BY create_time DESC LIMIT 100000, 20需要扫描 10 万行之后丢弃前面的数据浪费严重。可以改成-- 优化前 SELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 20; -- 优化后 SELECT * FROM orders WHERE create_time (SELECT create_time FROM orders ORDER BY create_time DESC LIMIT 99999, 1) ORDER BY create_time DESC LIMIT 20;或者用上一页最后一条记录的排序字段值直接做游标翻页而不是用偏移量。这里要注意游标翻页需要一个唯一的、有序的键实践中最常用的是自增主键或created_at加主键的组合。4.4 连接池和数据库参数怎么调连接池配置是很多团队容易忽略又特别容易出错的地方。以应用侧的连接池参数为例重点关注maximumPoolSize、minimumIdle、connectionTimeout、maxLifetime。先说一个经验值maximumPoolSize不建议一味调大。很多项目的配置是“机器的 CPU 核心数 × 2 1”比如 8 核机器设置 17 个连接。这个公式适用于单纯的连接池基准场景实际业务还要考虑单条 SQL 的耗时、数据库负载、连接占用时间等多种因素。更合理的做法是结合压测从 10 个连接开始逐步加压观察 QPS 曲线找到吞吐不再增长的拐点再把连接数设置为拐点附近的 80%。数据库侧的max_connections同理不能随便设置成几千。每个连接在 MySQL 中大约要占用几百 KB 到几 MB 内存连接数越大线程调度开销越大。通常 2C4G 的实例设置个 300~500 是合理的大型实例可以再往上加但必须配合监控和压测来定。再提两个 InnoDB 核心参数。innodb_buffer_pool_size是 InnoDB 缓存表和索引的内存区域建议设置为可用内存的 60%~75%但不能超过物理内存导致 swap。调大 buffer pool 之后查询命中缓存的概率提升磁盘 IO 压力会明显下降。innodb_flush_log_at_trx_commit这个参数控制事务日志的刷盘策略设为 1 是最安全的每个事务提交都 fsync 一次性能最差设为 2 表示每秒刷一次性能更好但断电可能丢数据。对大多数互联网业务来说设为 2 是常见的折中选择但金融类、强一致类业务建议维持 1。需要反复强调参数调整必须基于监控和压测数据不要照搬别人的配置不同业务、不同访问模型下最优值差异很大。5. 长期治理压测、容量规划与预案演练5.1 容量评估的常用公式经历过几次“扛不住”之后我最大的体会是高峰能不能扛住在流量还没进来之前就应该知道了。容量规划的计算并不复杂先把高峰期数据库需要承受的 QPS 算出来。假设某系统日活 100 万活跃用户高峰期集中在 2 小时内人均在这 2 小时内产生 50 次请求那么峰值请求数粗略计算为100万 × 50 / 7200秒 ≈ 6944 QPS落实到数据库的压力还要再打折。通常只有一部分请求会直接查数据库其余命中缓存或静态资源假设比例是 15%则数据库峰值 QPS 约为6944 × 15% ≈ 1041 QPS再看单实例能扛多少。决定这个数字最靠谱的方法是压测用一个和生产环境相同规格、相同数据量的实例跑业务的真实查询模型观察它在 CPU 安全水位比如 60%~70%下能支撑多少 QPS。假设结果是单实例能抗 800 QPS那么按“峰值需求 1041 QPS 20% 冗余”计算就需要至少 2 个实例来处理。这样的估算比拍脑袋定“我们要加四台机器”靠谱得多。5.2 慢查询治理闭环慢查询治理不要做成“出了问题才查一次”要形成一个持续运转的闭环。第一步是建立基线。至少连续观察一两周的慢查询日志记录每天慢查询数量、平均耗时、Top SQL 排行得出这个系统的“正常水平”。第二步是分类治理。针对 Top N 慢 SQL逐条审查执行计划判断是缺索引、SQL 写法问题、还是统计信息不准然后让对应的业务负责人限期优化。第三步是上线验证。优化上线之后持续观察慢查询日志和监控指标确认数量和耗时真的降了下来再把这个 SQL 从待办清单里划掉。闭环的关键是责任到人。慢查询治理最常见的失败方式是“DBA 发现慢 SQL发给开发开发说最近没空然后就没有然后了”。更好的做法是把慢查询数量和 Top 慢 SQL 作为团队的日常质量指标每周在例会上过一遍让每个业务模块对自己的 SQL 负责。5.3 数据归档与冷热分离大表是性能的隐形杀手。一张积累了三四年的订单表可能已经有一亿行数据即使每条查询都走了索引单表数据量过大也会让索引层级变深、扫描成本变大同时增加备份和恢复的时间。这时候该考虑的不是分库分表而是先做数据归档和冷热分离。以订单为例最自然的拆法是按时间维度把历史订单迁移到历史库或归档表线上只保留最近三个月到半年的数据。迁移过程推荐使用pt-archiver这类工具按主键分批删除和归档避免一次性锁大量行导致主库抖动。我遇到过不少团队用简单的DELETE ... WHERE create_time xxx一次性删几百万行结果锁表、主从延迟、业务超时一次全来了教训非常深刻。分库分表是更进一步的手段但它会引入分布式事务、跨库 join、全局主键等一系列复杂度。我的建议是在引入分库分表之前先把 SQL 优化、缓存、读写分离、数据归档这些手段都用尽确认数据量的增长趋势和生产实例的硬件天花板之后再考虑分库分表否则很可能“拆了更麻烦”。5.4 监控阈值与故障预案监控的目的不是“出事了告警”而是“在出事之前给出信号”。阈值设置要结合历史基线而不是随便填一个数。比如数据库 CPU 平时稳定在 20%~30%把告警阈值设成 90% 就太晚了因为从 30% 飙到 90% 可能只需要几分钟设成 70% 再结合持续时长告警就能在性能明显受损之前介入。故障预案要具体到操作步骤。比如“连接数打满”的预案应该写明第一步看哪些指标、第二步执行哪条命令、第三步哪些会话可以杀、第四步谁能执行扩容、第五步怎么判断恢复。预案不能停留在文档层面每个季度最好安排一次演练让值班同学真正跑一遍流程否则真出事时手忙脚乱文档写得再详细也没用。6. 一个完整复盘从“扛不住”到 P99 降到 250ms6.1 故障现场之前做的一个订单中心系统某大促预热当日午后流量明显上涨监控告警开始刷屏数据库 CPU 从 35% 升到 95%连接数逼近峰值部分地区用户反馈下单白屏。查看监控曲线可以发现午高峰读流量翻了三倍但其间夹杂了一个定时统计任务和一个新上线的报表功能这两条线叠加在一起把数据库资源吃满了。现场的关键指标是这样的活跃连接数一度超过 200慢查询数量从每分钟 3 条涨到每分钟 400 条一条关联 8 张表的统计 SQL 单次执行耗时超过 4 秒并且被报表页面每 5 秒触发一次另外某个热门商品的库存记录上有大量的并发 UPDATE 阻塞在行锁等待上。6.2 定位过程我当时的排查顺序可以给大家参考。第一步SHOW FULL PROCESSLIST看会话分布发现大量Sending data和一个长期存在的Waiting for table metadata lock第二步查看慢查询日志定位到那条统计 SQL第三步用EXPLAIN分析确认命中了一张百万级业务表的全表扫描第四步查information_schema.innodb_trx和锁等待信息发现热点库存行的锁等待已经积累了几十秒。这里有个排障上的小经验同时出现多个问题时先找“占用资源最大的会话”再找“等待最久的会话”前者是原因后者是结果。一开始我们差点去处理锁等待但杀掉几个卡住的会话之后发现锁还是不断堆积根源其实是统计 SQL 把 CPU 打满之后所有事务都变慢了锁自然越积越多。6.3 优化措施与最终结果处理的顺序是先恢复再优化。第一杀掉那条统计 SQL 的会话数据库 CPU 立刻从 95% 降到 60% 左右接口响应开始恢复第二禁止报表系统在高峰期直接查数据库报表数据改用离线预计算结果替换第三针对库存热点行更新把单行库存拆成多行分片并引入 Redis 预扣库存第四重新设计统计 SQL 的索引并修正字段类型避免隐式转换导致的索引失效。最终结果是这样的P99 响应时间从故障时的 3000ms 以上降到了 250ms 左右数据库 CPU 稳定在 30% 上下慢查询数量归零。从这次故障里提炼出的三条经验我至今每次做系统评审都会用到第一任何新上线的 SQL 都要做执行计划审查尤其要警惕 join 过多、没有合理条件的统计查询第二高峰期最怕的不是流量大而是流量大叠加定时任务和报表查询这几种流量要错峰第三热点数据的并发更新要从设计上规避而不是靠调数据库参数硬扛。最后再分享一个小技巧每次大促或业务高峰结束后花半小时把这次故障的监控截图、排查命令、处理过程、参数变更整理成一篇内部复盘文档下次再做容量评估或者评审新需求时它能帮你少踩一大半的坑。数据库调优没有银弹但有了正确的排查思路和长期治理机制至少不会再被高峰期打一个措手不及。
