某电商大促MySQL死锁频发业务瘫痪排查从索引优化连接池调到读写分离的全链路高并发解决方案

那天是“618”大促的开抢时刻，凌晨0点刚过，监控大屏上的QPS（每秒查询率）像疯了一样往上窜。就在大家以为能平稳度过第一波峰值时，报警群炸了。

Deadlock found when trying to get lock; try restarting transaction

这个错误就像瘟疫一样，迅速蔓延到核心订单服务。客服电话被打爆，用户端显示“系统繁忙，请稍后重试”，库存扣减失败，甚至出现了超卖现象。CTO的脸色比屏幕还黑，全员进入战时状态。

我是被紧急呼叫去救火的技术专家。当时现场情况极其混乱，数据库CPU飙升到95%以上，活跃连接数打满，慢查询日志堆积如山。今天，我就把这次“生死时速”的排查全过程，拆解给你们看。这不是教科书式的理论堆砌，而是带着血泪教训的真实战场复盘。

---

第一幕：至暗时刻——现象与初步诊断

当我们登录服务器时，第一眼看到的不是代码，而是监控图。

1.1 故障现象全景图

• 应用层：Java应用服务器（Tomcat/Spring Boot）大量线程阻塞，HTTP响应时间从正常的200ms飙升到10秒以上，甚至有大量504 Gateway Timeout。
• 数据库层：MySQL的Threads_connected接近配置上限，Threads_running居高不下。InnoDB deadlock number指标每分钟增长数十次。
• 业务层：下单接口成功率暴跌至30%，库存服务频繁回滚。

1.2 快速止血：我们做了什么？

在深入分析之前，必须先用“断臂求生”的方式稳定局势。我们立即执行了以下操作：

1. 熔断降级：关闭了非核心功能（如用户评论点赞、历史订单推荐），将资源全部集中在下单链路。
2. 限流保护：在网关层对IP和用户ID进行限流，防止单个用户恶意刷单加剧数据库压力。
3. 临时扩容：紧急横向扩展应用服务器实例，虽然治标不治本，但缓解了应用侧的线程阻塞。

然而，这些措施只是让崩溃来得慢一点，并没有解决根本问题。真正的“病灶”还在数据库深处。

---

第二幕：抽丝剥茧——死锁的根本原因

要解决死锁，必须先看懂死锁。很多人认为死锁是“两个事务互相等待”，但这只是表象。在大促高并发场景下，死锁往往源于不合理的索引设计和过大的事务范围。

2.1 捕获死锁现场：SHOW ENGINE INNODB STATUS

我们首先执行了关键指令，查看最新的死锁详情：

SHOW ENGINE INNODB STATUS\G

在输出的LATEST DETECTED DEADLOCK部分，我们看到了这样的片段：

*** (1) TRANSACTION:
TRANSACTION 12345678, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
...
*** (1) WAITING FOR THIS LOCK to be removed:
RECORD LOCKS space id 123 page no 456 n bits 72 index PRIMARY of table `ecommerce`.`orders` trx id 12345678 lock_mode X locks rec but not gap waiting
...
*** (2) TRANSACTION:
TRANSACTION 12345679, ACTIVE 0 sec starting index read
...
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 456 n bits 72 index PRIMARY of table `ecommerce`.`orders` trx id 12345679 lock mode X
...

解读关键信息：

• index PRIMARY：说明死锁发生在主键索引上，或者是因为没有合适的二级索引，导致查询回退到了主键扫描。
• lock_mode X locks rec but not gap waiting：这是记录锁（Record Lock），而不是间隙锁（Gap Lock）。这意味着两个事务都在尝试更新同一条或相邻的几行数据。
• table ecommerce.orders：问题核心在订单表。

2.2 罪魁祸首：超卖逻辑与长事务

通过排查业务代码，我们发现了两个致命问题：

问题一：库存扣减的“先查后改”

在抢购场景下，库存扣减逻辑大致如下：

// 伪代码：错误的库存扣减逻辑
@Transactional
public void deductStock(Long skuId, int quantity) {
    // 1. 先查询当前库存
    Stock stock = stockMapper.selectById(skuId);
    
    // 2. 判断库存是否充足
    if (stock.getQuantity() < quantity) {
        throw new BusinessException("库存不足");
    }
    
    // 3. 扣减库存
    stockMapper.updateById(stock); // 这里触发了全表扫描或索引扫描
}

为什么这会导致死锁？

1. 热点行竞争：在大促期间，热门商品（如iPhone）的库存行是绝对的热点。成千上万的用户同时抢购同一款商品，导致成千上万个事务同时争抢这一行记录的行锁。
2. 锁等待队列过长：MySQL InnoDB引擎在处理大量并发更新同一行时，会形成漫长的锁等待队列。一旦两个事务的锁获取顺序不一致（虽然这里都是update同一行，但在复杂事务中更明显），或者事务执行时间过长，就会触发死锁检测机制，杀死其中一个事务。
3. 大事务风险：如果@Transactional注解包裹了过多的操作（如发送MQ消息、调用外部接口），事务持有锁的时间就会变长，进一步加剧竞争。

问题二：缺失的索引导致锁升级

我们在orders表上发现，虽然(user_id, status)上有索引，但在扣减库存的关联查询中，由于逻辑复杂，优化器选择了全表扫描或大量的行锁，而不是高效的索引定位。

---

第三幕：精准打击——索引优化与SQL重构

明确了病因，开始对症下药。第一步，优化索引和SQL。

3.1 库存扣减：改为“原子更新”

这是最有效的一步。我们将“先查后改”改为原子性更新，利用数据库自身的乐观锁机制，避免行锁的长时间持有。

-- 优化后的SQL：通过WHERE条件保证原子性
UPDATE stock 
SET quantity = quantity - #{quantity} 
WHERE sku_id = #{skuId} 
  AND quantity >= #{quantity};

Java代码改造：

// 改造后的扣减逻辑
public int deductStock(Long skuId, int quantity) {
    // 直接执行原子更新，返回受影响行数
    int rows = stockMapper.deductStock(skuId, quantity);
    
    if (rows == 0) {
        // 更新行数为0，说明库存不足，无需查询
        throw new BusinessException("库存不足");
    }
    return rows;
}

优势：

• 无锁竞争：不需要先加读锁再加重写锁，数据库内部通过CAS（Compare And Swap）思想处理，极大地减少了锁的持有时间。
• 性能提升：避免了额外的SELECT查询，减少了网络IO和数据库负载。

3.2 订单插入：覆盖索引与批量插入

对于订单表orders，我们发现插入操作也存在问题。每个用户下订单时，都会插入一条记录。为了加速查询，我们添加了覆盖索引。

-- 原有索引
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

-- 新增覆盖索引，避免回表
-- 假设查询场景是：根据用户ID和订单状态查询订单列表，且只需要订单ID和时间
ALTER TABLE orders ADD INDEX idx_user_id_status_create_time (user_id, status, create_time);

批量插入优化：

// 使用批量插入减少数据库交互次数
@InsertProvider(type = OrderSqlProvider.class, method = "batchInsert")
int batchInsert(@Param("list") List<Order> orders);

对应的SQL片段：

INSERT INTO orders (user_id, sku_id, quantity, status, create_time)
VALUES
<foreach collection="list" item="item" separator=",">
  (#{item.userId}, #{item.skuId}, #{item.quantity}, #{item.status}, #{item.createTime})
</foreach>

3.3 慢查询治理：Explain分析

我们导出了一周内所有的慢查询日志，使用EXPLAIN逐一分析。

案例：一个复杂的订单详情查询

-- 原SQL：关联多张表，且WHERE条件不在索引前列
SELECT o.*, p.name, p.image 
FROM orders o
LEFT JOIN products p ON o.sku_id = p.id
WHERE p.category_id = 123 
ORDER BY o.create_time DESC
LIMIT 10;

问题： WHERE p.category_id = 123 在JOIN之后过滤，导致大表关联后再过滤，效率极低。

优化后：

1. 确保products表的category_id上有索引。
2. 将过滤条件前置，或者改写为子查询。

-- 优化后SQL：先过滤商品，再关联
SELECT o.*, p.name, p.image 
FROM orders o
INNER JOIN products p ON o.sku_id = p.id
WHERE p.category_id = 123 
ORDER BY o.create_time DESC
LIMIT 10;

并且，我们确保orders表的sku_id上有索引，这样内连接可以高效执行。

---

第四幕：深层调理——连接池与事务调优

索引优化解决了大部分热点行竞争问题，但数据库连接资源依然紧张。我们需要调整连接池和事务配置。

4.1 HikariCP连接池调优

项目使用的是Spring Boot默认的连接池HikariCP。在大促前，配置是默认值，这在低负载下没问题，但在高并发下会导致连接饥饿或创建开销过大。

优化前配置（application.yml）：

spring:
  datasource:
    hikari:
      maximum-pool-size: 10  # 默认值，严重不足
      minimum-idle: 5
      connection-timeout: 30000

优化后配置：

spring:
  datasource:
    hikari:
      # 根据CPU核数和数据库负载调整，一般设为CPU核数*2 + 磁盘数
      maximum-pool-size: 50 
      minimum-idle: 10
      # 连接空闲超时时间，避免长期占用
      idle-timeout: 600000 
      # 连接最大生命周期，防止连接泄漏
      max-lifetime: 1800000 
      # 连接超时时间，适当缩短以快速失败
      connection-timeout: 10000

关键参数解释：

• maximum-pool-size：增大了连接池大小，允许更多并发请求同时获取连接，避免线程阻塞等待连接。
• connection-timeout：从30秒缩短到10秒。如果10秒内拿不到连接，直接报错，而不是让线程干等，这样可以快速释放资源，让其他请求有机会执行。

4.2 事务粒度瘦身

回顾之前的代码，发现很多Service方法上都加了@Transactional，但方法内部包含了一些与数据库无关的操作，比如调用第三方短信服务、记录日志等。这些操作耗时较长，却占据了数据库事务锁。

优化原则：

1. 小事务：事务只包裹必要的数据库操作。
2. 非数据库操作外移：将耗时操作移到事务之外。

改造示例：

// 改造前：大事务
@Transactional
public void createOrder(OrderDTO dto) {
    // 1. 扣库存（数据库操作）
    stockService.deductStock(dto.getSkuId(), dto.getQuantity());
    
    // 2. 创建订单（数据库操作）
    Order order = orderMapper.insert(dto);
    
    // 3. 发送短信通知（外部IO，耗时500ms+）
    smsService.sendNotification(order.getUserId(), "下单成功"); 
}

// 改造后：小事务 + 异步处理
public void createOrder(OrderDTO dto) {
    // 1. 数据库操作在事务中
    Order order = orderService.createOrderInTransaction(dto);
    
    // 2. 发送短信异步处理，不占用事务时间
    // 使用线程池或MQ异步发送
    CompletableFuture.runAsync(() -> {
        smsService.sendNotification(order.getUserId(), "下单成功");
    });
}

通过这种方式，事务持有锁的时间从原来的几百毫秒缩短到几十毫秒，极大提升了并发能力。

---

第五幕：架构升级——读写分离与缓存引入

即使做了上述优化，当QPS达到万级时，单库的压力依然巨大。我们需要从架构层面进行拆分。

5.1 读写分离

对于读多写少的电商场景（浏览商品、查询订单详情），读写分离可以分担主库的压力。

架构设计：

• 主库（Master）：负责所有的写操作（下单、库存扣减、更新订单状态）。
• 从库（Slave）：负责所有的读操作（商品详情、订单列表）。

实现方式：ShardingSphere-JDBC

我们没有选择复杂的中间件，而是使用了Spring生态友好的ShardingSphere-JDBC。

# sharding-sphere.yaml 配置
dataSources:
  ds_master:
    url: jdbc:mysql://master-host:3306/ecommerce
    username: root
    password: ****
    driver-class-name: com.mysql.cj.jdbc.Driver
  ds_slave:
    url: jdbc:mysql://slave-host:3306/ecommerce
    username: root
    password: ****
    driver-class-name: com.mysql.cj.jdbc.Driver

rules:
  shadow: # 读写分离规则
    dataSources:
      masterDataSourceName: ds_master
      slaveDataSourceNames:
        - ds_slave
    loadBalancerName: random

  # 表分片规则（可选，如果数据量极大）
  sharding:
    tables:
      orders:
        actualDataNodes: ds_${0}.orders
        tableStrategy:
          inline:
            shardingColumn: user_id
            algorithmExpression: ds_${user_id % 2}

注意事项：

• 主从延迟：读从库可能存在几秒的延迟。对于下单后的“立即查询订单状态”场景，必须强制读主库。

  
  @DS("master") // ShardingSphere注解，强制走主库
  public Order getOrderById(Long id) {
      return orderMapper.selectById(id);
  }
  

• 一致性要求：库存扣减后，查询库存必须读主库，确保数据一致性。

5.2 引入Redis缓存，抵御读流量洪峰

即使读写分离，从库的读压力在峰值时依然可能扛不住。我们需要在数据库前面加一层缓存。

缓存策略：Cache Aside Pattern（旁路缓存）

1. 读操作：先查Redis，命中则返回；未命中则查MySQL，并将结果写入Redis，设置过期时间。
2. 写操作：先更新MySQL，再删除Redis（注意：是删除，不是更新，避免并发问题）。

代码实现：

@Service
public class ProductService {

    @Autowired
    private ProductMapper productMapper;
    
    @Autowired
    private StringRedisTemplate redisTemplate;
    
    private static final long CACHE_EXPIRE_TIME = 30; // 30秒
    
    public Product getProduct(Long id) {
        // 1. 查缓存
        String key = "product:" + id;
        String json = redisTemplate.opsForValue().get(key);
        if (json != null) {
            return JSON.parseObject(json, Product.class);
        }
        
        // 2. 查数据库
        Product product = productMapper.selectById(id);
        if (product != null) {
            // 3. 写入缓存
            redisTemplate.opsForValue().set(key, JSON.toJSONString(product), 
                    CACHE_EXPIRE_TIME, TimeUnit.SECONDS);
        }
        return product;
    }
    
    public void updateProduct(Product product) {
        // 1. 更新数据库
        productMapper.updateById(product);
        // 2. 删除缓存
        String key = "product:" + product.getId();
        redisTemplate.delete(key);
    }
}

热点Key特殊处理： 对于秒杀商品这种极端热点，单个Redis实例也可能扛不住。我们可以使用本地缓存（Caffeine/Guava Cache）作为二级缓存，或者使用Redis Cluster进行分片。

// 使用Caffeine本地缓存
LoadingCache<Long, Product> cache = Caffeine.newBuilder()
    .maximumSize(1000)
    .expireAfterWrite(10, TimeUnit.SECONDS)
    .build(id -> {
        // 如果本地缓存没有，再查Redis或数据库
        return productMapper.selectById(id);
    });

5.3 数据库分库分表（终极手段）

如果数据量继续增长，单机MySQL即使读写分离也到达瓶颈，就需要考虑分库分表。

分片策略：

• 用户维度分片：user_id % 16，将数据分散到16个数据库中。
• 时间维度分片：对于订单表，可以按月份分表，如orders_202406，orders_202407，方便历史数据归档。

注意： 分库分表会带来跨库查询、分布式事务、全局ID生成等复杂问题。建议初期先通过读写分离和缓存解决，除非数据量确实达到亿级