正文

MySQL高并发处理策略从架构优化到代码细节 解决连接超时和锁竞争问题 提升数据库读写性能实战

/0 浏览量
1009

记得有一次深夜，我盯着监控大屏上那条几乎要冲破天花板的QPS曲线，心里咯噔一下——那是大促前的压测，MySQL的连接数正在以每秒几百个的速度增长，慢查询日志疯狂滚动，锁等待队列长得像春运买票的队伍。那一刻我深刻意识到，数据库从来不是孤岛，它是整个系统的“心脏”，一旦心率失常，全身都会瘫痪。今天，我就把这些年从架构设计到代码细节的实战经验，掰开揉碎讲给你听，咱们一起搞定高并发这块硬骨头。

一、先别急着改代码，看看连接池是不是在“打架”

高并发场景下，连接超时（Connection Timeout）往往是第一个报警的信号。你以为加了连接池就万事大吉？其实很多故障源于连接池配置不当。

想象一下，连接池就像一个小型停车场。如果车位太少（maxConnections设得太小），车辆（请求）就会堵在门口；如果车位太多但管理员效率低下（wait_timeout设置不合理），空闲车位占着茅坑不拉屎，新来的车又进不来。

在Spring Boot项目中，我们常用HikariCP。很多团队直接沿用默认配置，这在高并发下简直是埋雷。比如，默认的最大连接数可能只有10或20，面对上千并发时，请求只能在池外排队，一旦超过maxWaitTime，直接抛出“Cannot acquire connection”异常。

// 一个优化后的高并发连接池配置示例
spring:
  datasource:
    hikari:
      maximum-pool-size: 50          # 根据CPU核数和IO密集型任务调整，一般设为CPU核数*2 + 磁盘数
      minimum-idle: 10               # 保持最小空闲连接，避免频繁创建销毁
      idle-timeout: 30000            # 空闲连接存活时间，不超过数据库wait_timeout
      max-lifetime: 1800000          # 连接最大生命周期，建议比数据库wait_timeout短
      connection-timeout: 30000      # 获取连接超时时间，30秒内拿不到就报错
      leak-detection-threshold: 60000 # 开启泄漏检测，防止连接泄漏

但光有配置还不够，你得监控HikariPool-1 connection pool size和HikariPool-1 connections idle。如果idle连接长期为0，说明池子太小；如果active连接一直满载且等待时间长，就要考虑扩容或优化SQL。

另外，数据库端的max_connections也要调大。MySQL默认是151，高并发时建议调到500以上，但要结合innodb_buffer_pool_size（内存占比70-80%）来权衡，避免内存溢出。

二、锁竞争：高并发的“隐形杀手”

如果说连接超时是“进门难”，那锁竞争就是“进屋后抢椅子”。在高并发下，尤其是写操作密集的场景，行锁、表锁、间隙锁会让事务排队排到怀疑人生。

举个真实例子：电商订单系统中，秒杀活动时成千上万的用户同时抢购同一款限量商品。如果不加控制，所有事务都会去锁住那行库存记录，导致严重的锁等待。

-- 错误的做法：直接UPDATE，锁住整个行甚至范围
UPDATE stock SET count = count - 1 WHERE product_id = 1001;

-- 优化思路：利用版本号乐观锁，减少锁持有时间
UPDATE stock SET count = count - 1, version = version + 1 
WHERE product_id = 1001 AND count >= 1 AND version = #{currentVersion};

乐观锁的核心思想是：先不锁，读出现有数据，提交时检查版本是否被改动。如果改动过，说明有并发冲突，重试或失败。这样避免了长时间持锁，大幅提升了并发能力。

但对于需要强一致性的场景，比如金融转账，乐观锁就不适用了。这时候要考虑分库分表，将热点数据分散到多个实例上，每个实例处理自己的小部分请求，锁竞争自然稀释。

还有一种常见锁是“元数据锁”（MDL）。当你在高并发下执行ALTER TABLE或CREATE INDEX，所有查询该表的会话都会被阻塞。解决方案是避开业务高峰期进行DDL操作，或者使用pt-online-schema-change工具在线修改表结构。

三、读写分离：让主库喘口气

高并发下，读多写少是常态。如果所有请求都打到主库，主库压力大，同步延迟也会加剧。读写分离是解决这个问题的经典方案。

架构上，我们通常设一个主库（Master）负责写操作和强一致读，多个从库（Slave）负责读操作。MySQL的异步复制机制让主库专注于写，从库承担读流量。

# Spring多数据源配置示例
spring:
  datasource:
    master:
      url: jdbc:mysql://master-host:3306/db?useSSL=false
      username: root
      password: secret
    slave:
      url: jdbc:mysql://slave-host:3306/db?useSSL=false
      username: root
      password: secret

关键在于路由策略。我们通常用AOP拦截注解，标记哪些Service方法走从库：

@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface Slave {
}

@Aspect
@Component
public class DataSourceAspect {
    @Around("@annotation(slave)")
    public Object around(ProceedingJoinPoint point, Slave slave) throws Throwable {
        DataSourceContextHolder.setDataSourceType(DataSourceType.SLAVE);
        try {
            return point.proceed();
        } finally {
            DataSourceContextHolder.clearDataSourceType();
        }
    }
}

但读写分离有个痛点：数据延迟。从库同步主库可能有几百毫秒到几秒的延迟。对于实时性要求高的读（比如刚下单后立刻查询订单状态），必须走主库。这时需要业务上区分“最终一致”和“强一致”场景，或者使用半同步复制（Semi-Sync Replication）减少延迟。

MySQL 5.7+支持半同步复制，主库提交事务前至少等待一个从库确认收到binlog，既保证数据不丢，又比异步复制延迟低。

-- 安装半同步复制插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

-- 开启半同步
SET GLOBAL rpl_semi_sync_master_enabled = ON;
SET GLOBAL rpl_semi_sync_master_timeout = 1000; -- 1秒无响应降级为异步

四、SQL优化：细节决定性能上限

再好的架构，也抵不过一条烂SQL。高并发下，单条慢查询就能拖垮整个数据库。

首先，确保每块查询都走索引。用EXPLAIN分析执行计划，关注type（最好达到ref或range）、key（实际使用的索引）、rows（扫描行数）。

-- 假设用户表有userId和status字段
EXPLAIN SELECT * FROM users WHERE status = 1 AND create_time > '2023-01-01' ORDER BY create_time DESC LIMIT 10;

如果type是ALL（全表扫描），而rows很大，那就需要在(status, create_time)上建联合索引。注意最左前缀原则。

其次，避免SELECT *，只取需要的字段。网络传输和内存占用都会减少。

第三，大分页优化。LIMIT 1000000, 10这种深分页会扫描大量无用数据。可以用“延迟关联”优化：

-- 原始慢查询
SELECT * FROM orders WHERE user_id = 123 ORDER BY create_time DESC LIMIT 100000, 10;

-- 优化后：先查主键，再回表
SELECT o.* FROM orders o 
INNER JOIN (SELECT id FROM orders WHERE user_id = 123 ORDER BY create_time DESC LIMIT 100000, 10) t 
ON o.id = t.id;

第四，批量操作代替循环单条插入。高并发写入时，每条INSERT都要一次网络往返和事务提交，开销巨大。用INSERT INTO ... VALUES (...), (...), (...)批量插入，性能提升十倍不止。

// MyBatis批量插入示例
@Insert("<script>" +
        "INSERT INTO batch_table (id, name, value) VALUES " +
        "<foreach collection='list' item='item' separator=','>" +
        "(#{item.id}, #{item.name}, #{item.value})" +
        "</foreach>" +
        "</script>")
int batchInsert(@Param("list") List<BatchEntity> list);

五、缓存加持：数据库的“减压阀”

缓存是提升读性能最直接的手段。Redis作为内存数据库，响应时间在毫秒级，能挡住绝大部分读请求。

但缓存不是银弹，要注意一致性问题。常见策略有：先更新数据库，再删除缓存（而非更新缓存，避免并发覆盖）。

// 更新缓存的伪代码
public void updateUser(User user) {
    userMapper.update(user); // 1. 先更新DB
    redisTemplate.opsForValue().set("user_" + user.getId(), null); // 2. 再删除缓存
}

对于热点数据，可以采用本地缓存（如Caffeine）+ 分布式缓存（Redis）的多级缓存架构，进一步减少网络开销。

// Caffeine本地缓存示例
Cache<String, User> localCache = Caffeine.newBuilder()
    .maximumSize(1000)
    .expireAfterWrite(10, TimeUnit.MINUTES)
    .build();

public User getUser(String id) {
    User user = localCache.getIfPresent(id);
    if (user == null) {
        user = redisCache.get(id);
        if (user != null) {
            localCache.put(id, user);
        }
    }
    return user;
}

六、监控与预警：看见才能管好

最后，没有监控的高并发优化都是盲人摸象。部署Prometheus + Grafana监控MySQL的关键指标：QPS、TPS、连接数、慢查询数、锁等待时长、复制延迟等。

设置阈值告警，比如连接数超过80%、慢查询超过10条/秒、主从延迟超过5秒，立即通知运维和开发。

记住，高并发处理是一个持续迭代的过程。架构优化是骨架，SQL和锁优化是肌肉，缓存和监控是神经。只有四肢协调，系统才能在高峰冲击下稳如泰山。下次再遇到连接超时或锁竞争，别慌，按这个思路一步步排查，你一定能找到症结所在。

-- 展开阅读全文 --