正文

孩子摔了哭大人哄：MySQL高并发下数据库崩了别慌，锁等待超时加连接池优化实战，从死锁排查到读写分离，手把手教你让网站扛住万人同时访问不宕机

/0 浏览量
0924

哎哟，别哭别哭，擦擦眼泪。

是不是刚看见线上的红灯报警，心里咯噔一下，感觉天都塌了？我懂，真的懂。那感觉就像是你在家逗孩子，孩子突然摔了一跤，扯着嗓子嚎啕大哭，你第一反应不是分析地心引力，而是赶紧抱起来哄——先让局面稳住，再谈别的。

数据库崩了，也一样。别慌，深呼吸。今天我就陪你一起，把这个“哭闹”的 MySQL 哄好，顺便教它怎么学会自己乖乖睡觉，不再因为人太多就歇菜。咱们不整那些虚头巴脑的学术定义，就聊聊怎么让你的网站在万人同时在线的时候，依然稳如老狗。

第一步：先别急着重启，看看是谁在“撒泼打滚”

很多时候，数据库崩不是因为它本身坏了，而是因为它太累了，被一堆人堵在门口进不去也出不来。这就好比十个孩子同时要抢一个玩具，谁也不让谁，最后大家哭成一团。

在 MySQL 里，这叫锁等待超时。

想象一下，有个事务 A 正在修改一条数据，它给这条数据加了把锁。这时候，事务 B、C、D……全来了，都想改这条数据。它们排着队，等着 A 释放锁。如果 A 卡住了（比如在做复杂的计算，或者干脆忘了提交），后面的 B、C、D 就得一直等。

等太久怎么办？MySQL 有个默认策略，叫 innodb_lock_wait_timeout。默认是 50 秒。也就是说，让这帮人等 50 秒，还没轮到，就报错：“超时了！我不干了！”然后抛出一个 Lock wait timeout exceeded 的错误。

这时候，你的应用层可能会看到一堆奇怪的红字。用户那边就会卡顿、报错。

怎么排查？别猜，直接看日志。

你可以登录数据库，执行这条命令，看看现在是谁在堵路：

SHOW ENGINE INNODB STATUS\G

在输出的一大堆文字里，找到 LATEST DETECTED DEADLOCK 和 TRANSACTIONS 部分。你会看到类似这样的信息：

---TRANSACTION 42133, ACTIVE 120 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

这一看，就知道有个事务已经挂了 120 秒还在等锁。再往下看 SENDER 和 RECEIVER，你就能清楚是谁锁住了谁。

实战技巧： 如果发现锁等待特别多，先看看是不是有慢查询。很多时候，锁等待的根源是一个没加索引的 UPDATE 或 DELETE 语句，它扫描了全表，锁住了无数行，导致其他事务全排队。

第二步：连接池——别让“搬运工”累死在半路上

数据库崩了，除了锁的问题，还有一个常见原因：连接数爆了。

你可以把 MySQL 想象成一个餐厅，数据库连接就是服务员。如果来了 1000 个顾客（高并发请求），而你只有 10 个服务员，那肯定乱套。更糟糕的是，如果服务员去了厨房（数据库）取菜，结果卡在厨房门口不出来，顾客还在外面排队等着买单，那餐厅就瘫痪了。

这就是为什么我们需要连接池。

连接池就像是一个“服务员休息区+预培训营地”。在系统启动的时候，我们就准备好了一堆连接（服务员），让它们先待在那儿，随时准备接客。当用户请求来了，直接从池子里拿一个连接用；用完了，还回去，而不是每次都去新建、销毁连接。

为什么不用连接池会崩？ 每次新建一个数据库连接，MySQL 都要分配内存、验证权限、建立会话，这过程挺耗资源的。如果一秒钟有几万个请求，每个请求都去新建连接，MySQL 的 CPU 和内存瞬间就被耗尽了，别说处理业务了，连自己都得挂。

怎么配置？以最常见的 HikariCP 为例（Java 后端常用）：

spring:
  datasource:
    hikari:
      # 最大连接数，根据你数据库能承受的上限来定，别给太大
      maximum-pool-size: 20
      # 最小空闲连接，保持一定的预热
      minimum-idle: 5
      # 连接超时时间，30秒内拿不到连接就报错，别让用户一直等
      connection-timeout: 30000
      # 空闲连接存活时间，太久不用就回收
      idle-timeout: 600000
      # 最大生命周期，防止连接长时间占用导致内存泄漏
      max-lifetime: 1800000

关键点：

  1. maximum-pool-size 别设太大。一般 20-50 就够了。MySQL 默认最大连接数是 151，如果你应用层开了 200 个连接，数据库端会直接拒绝连接，报错 Too many connections。
  2. 监控连接池状态。用 Actuator 或者 Prometheus 监控 active、idle、pending 连接数。如果 pending（等待连接的请求）一直很高，说明连接池太小，或者数据库处理太慢。

第三步：死锁——两个小孩抢玩具，谁都别想玩

锁等待超时是“排队等”，而死锁是“互相掐脖子”。

事务 A 锁了行 1，想拿行 2；事务 B 锁了行 2，想拿行 1。俩人谁也不让谁，一直僵持下去。MySQL 的 InnoDB 引擎很聪明，它会定期检测死锁，然后选择一个“受害者”事务，强制回滚，让另一个事务继续。

死锁长什么样？ 在 SHOW ENGINE INNODB STATUS 里，你会看到一段详细的死锁报告，包括两个事务各自锁了啥、等了啥。

怎么避免死锁？

  1. 统一加锁顺序。比如，所有事务都先锁 ID 小的行，再锁 ID 大的行。这样就不会出现 A 等 B、B 等 A 的循环。
  2. 缩短事务长度。事务里别做太多事，尤其是别调用外部接口、做 HTTP 请求。能一步搞定的，别分两步。
  3. 用 SELECT ... FOR UPDATE 要小心。除非你真的需要排他锁，否则别乱用。有时候 SELECT 不带锁就能解决问题。

代码示例：统一加锁顺序

// 错误示范：随机顺序加锁，容易死锁
@Transactional
public void transferWrong(Long userId1, Long userId2, BigDecimal amount) {
    User user1 = userRepository.findById(userId1).orElseThrow();
    User user2 = userRepository.findById(userId2).orElseThrow();
    // 如果大量并发，可能一个事务锁了 1 等 2，另一个锁了 2 等 1
    user1.setBalance(user1.getBalance().subtract(amount));
    user2.setBalance(user2.getBalance().add(amount));
    userRepository.save(user1);
    userRepository.save(user2);
}

// 正确示范：始终按 ID 从小到大加锁
@Transactional
public void transferRight(Long userId1, Long userId2, BigDecimal amount) {
    Long minId = Math.min(userId1, userId2);
    Long maxId = Math.max(userId1, userId2);
    
    User minUser = userRepository.findById(minId).orElseThrow();
    User maxUser = userRepository.findById(maxId).orElseThrow();
    
    // 因为总是先锁小的，再锁大的，所以不会形成环
    minUser.setBalance(minUser.getBalance().subtract(amount));
    maxUser.setBalance(maxUser.getBalance().add(amount));
    userRepository.save(minUser);
    userRepository.save(maxUser);
}

第四步：读写分离——把“算账的”和“查账的”分开

当并发量上来了，单台 MySQL 往往扛不住。这时候，读写分离就派上用场了。

原理很简单： 主库（Master）负责写操作（INSERT、UPDATE、DELETE），从库（Slave）负责读操作（SELECT）。主库的数据会通过 binlog 同步到从库。

这样，写压力集中在主库，读压力分散到多个从库。就像餐厅里，厨师只负责炒菜（写），服务员只负责上菜和收桌子（读），分工明确，效率更高。

怎么实现？

  1. 配置主从复制。在 MySQL 配置文件中设置 server-id、log-bin 等参数，然后执行 CHANGE MASTER TO 命令，让从库同步主库。
  2. 应用层切换数据源。用 Spring 的 AbstractRoutingDataSource 或者 MyBatis 的动态数据源，根据方法名（@Read、@Write）或注解，自动路由到主库或从库。

代码示例：简单的读写路由

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSourceType();
    }
}

// 在 Service 层使用 AOP 切面
@Aspect
@Component
public class DataSourceAspect {
    @Before("@annotation(readOnly)")
    public void setReadDataSource(ReadOnly readOnly) {
        DataSourceContextHolder.setDataSourceType(DataSourceType.SLAVE);
    }

    @Before("@annotation(write)")
    public void setWriteDataSource(Write write) {
        DataSourceContextHolder.setDataSourceType(DataSourceType.MASTER);
    }
}

注意： 读写分离有延迟。主库写完，从库同步需要时间（通常几十毫秒到几秒）。如果用户刚写完数据，立刻去读，可能读不到最新数据。这时候，关键业务（比如支付成功后查订单）要强制读主库。

第五步：分库分表——最后的大招

如果读写分离还不够用，那只能上分库分表了。

为什么？ 单表数据量太大（比如超过 500 万行），查询性能会急剧下降。索引再完美，也架不住数据量本身太大。分库分表就是把一个大表拆成很多小表，分散到多个数据库里。

怎么分？

  1. 垂直拆分：把一个大表按列拆分。比如，用户表里有 100 个字段，其中 80 个是 rarely used 的详细信息，可以把这些信息拆到另一个表里，主表只留核心字段。
  2. 水平拆分：把一个大表按行拆分。比如，用户表按 user_id % 10 分成 10 个表，每个表存 1⁄10 的数据。

实战建议：

  • 不要过早分库分表。先优化索引、读写分离、连接池。这些能解决 80% 的问题。
  • 如果非要分，选个好点的中间件，比如 ShardingSphere、MyCat，别自己造轮子。
  • 分库分表后，跨库查询、分页、排序会变得很复杂，设计时要提前考虑。

第六步：监控与预警——别等崩了才看见

最后，也是最重要的一点：监控。

你得知道数据库现在干得咋样。CPU 使用率、连接数、QPS（每秒查询数）、TPS（每秒事务数）、慢查询数……这些指标都得盯着。

推荐工具：

  • Prometheus + Grafana：开源免费，可视化效果棒。
  • Percona Monitoring and Management (PMM)：专为 MySQL 设计的监控平台。
  • 阿里云 RDS 监控：如果用云数据库，自带监控很方便。

设置告警：

  • 连接数超过 80% 时报警。
  • 慢查询数突然飙升时报警。
  • CPU 使用率持续超过 90% 时报警。

这样，数据库还没崩，你就能收到通知，提前介入处理，而不是等用户投诉了才慌忙补救。

结语：哄好数据库，也哄好你自己

你看，MySQL 高并发崩了，其实也没那么可怕。就像孩子摔了哭，你哄一哄，分析一下原因，改进一下方法，下次就不会摔了。

总结一下今天的干货：

  1. 锁等待超时：看 SHOW ENGINE INNODB STATUS，排查慢查询和未加索引的语句。
  2. 连接池优化：合理配置 HikariCP，别开太大，别让用户一直等。
  3. 死锁避免：统一加锁顺序，缩短事务长度。
  4. 读写分离：主写从读，解决读压力，注意延迟问题。
  5. 分库分表：最后的大招，慎用，选对中间件。
  6. 监控预警：提前发现，主动处理。

记住，数据库是你的战友，不是敌人。你对它好，它就能帮你扛住万人访问。下次再遇到报警，别慌，先泡杯茶，然后按这个流程一步步来。你一定能哄好它。

-- 展开阅读全文 --