正文

电商大促MySQL崩盘实战双11秒杀如何扛住每秒10万订单从拼多多抢票到王者荣耀登录高并发下MySQL性能优化策略

/0 浏览量
0903

电商大促MySQL崩盘实战双11秒杀如何扛住每秒10万订单从拼多多抢票到王者荣耀登录高并发下MySQL性能优化策略

先跟你聊个真实的场景吧。

2021年双十一，某头部电商平台的一个小型促销活动，峰值QPS刚过5万，MySQL主库直接报警，主从延迟飙到30秒以上，下单页面卡得用户直接放弃支付。后来复盘发现，问题的根源根本不是MySQL本身有多菜，而是架构设计从一开始就埋了雷——所有请求一股脑全打在主库上，索引设计不合理，慢查询堆积，事务锁竞争严重。

我见过太多团队在大促前把MySQL当成”最后一道防线”，但实际上MySQL应该是最先被保护的对象。今天这篇文章，我会从拼多多抢票、王者荣耀登录、双十一秒杀这三个真实高并发场景出发，把MySQL的优化策略拆开了揉碎了讲清楚。

一、先搞清楚”每秒10万订单”到底意味着什么

很多团队一听到”10万QPS”就觉得不可思议，但如果你把它拆开看，其实没那么夸张。

假设双十一当天峰值是10万笔订单/秒，这不是说10万个请求同时到达MySQL，而是：

  • 下单请求：约3万 QPS
  • 库存扣减：约2万 QPS
  • 支付状态更新：约1万 QPS
  • 订单查询（用户端+运营端）：约4万 QPS

关键在于，这些操作的性质完全不同。查询可以容忍最终一致性，库存扣减必须强一致，支付状态更新需要事务保障。把它们混在一起打MySQL，不崩才怪。

我曾经帮一家年GMV超过50亿的电商公司做架构改造，他们最初的MySQL配置是这样的：

innodb_buffer_pool_size = 4G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
max_connections = 500

跑1万QPS的下单压力测试，30秒后MySQL直接OOM崩溃。问题出在哪？我来逐一分析。

innodb_buffer_pool_size = 4G：对于生产级MySQL，这个值太小了。Buffer Pool是InnoDB最重要的性能配置，它缓存了数据页和索引页。如果这个值不够大，每次查询都要从磁盘读数据，I/O延迟直接拖垮整个系统。通常建议设为物理内存的60%-70%。

innodb_flush_log_at_trx_commit = 1：这是最安全但也最慢的配置。每次事务提交都强制刷盘，保证数据零丢失，但在高并发下，这个”sync写磁盘”的操作会成为巨大的瓶颈。双十一当天，每笔订单都要sync一次，10万QPS意味着每秒10万次fsync，磁盘根本扛不住。

max_connections = 500：这个值看起来不小，但如果每个连接都持有一个长事务，500个连接就能把innodb_open_files、锁资源全部耗尽。更致命的是，连接数上限太低会导致连接池频繁创建和销毁，消耗CPU。

这些配置问题在正常流量下可能不明显，但大促峰值一来，全部暴露。

二、拼多多抢票场景：秒杀的本质是”库存竞争”

拼多多那种几块钱的限量商品秒杀，本质上是库存并发竞争问题。几万人同时抢1000张票，MySQL直接执行UPDATE stock SET count = count - 1 WHERE product_id = 123 AND count > 0会怎样？

答案是：所有请求串行排队，第一个拿到行锁的请求执行完后，第二个才能执行，如此类推。1000个库存，理论上最多支持1000笔并发，剩下的9000个请求全部堵在锁等待上， MySQL连接池迅速被耗尽。

2.1 为什么行锁会成为瓶颈

InnoDB的行锁机制是这样的：

-- 伪代码描述锁的竞争过程
BEGIN;
SELECT * FROM stock WHERE product_id = 123 FOR UPDATE;  -- 加行锁
-- 其他所有请求都在等这把锁
UPDATE stock SET count = count - 1 WHERE product_id = 123;
COMMIT;  -- 锁释放，下一个请求才能拿到

每一笔请求都是一个完整的事务，事务未提交前锁不释放。10万QPS下，锁等待队列会瞬间膨胀到数万，每个等待的请求都占着一个MySQL连接，连接数爆炸，服务器直接宕机。

2.2 真正的解法：把竞争从MySQL挪到Redis

业界成熟的方案是Redis预扣库存 + MySQL异步落库，核心思路是：

  1. 活动开始前，把库存预热到Redis
  2. 用户下单时，先在Redis做原子扣减
  3. Redis扣减成功，异步写入MQ
  4. 消费者从MQ消费，写入MySQL
// 伪代码：Redis预扣库存逻辑
public boolean deductStock(Long productId, int quantity) {
    String stockKey = "stock:" + productId;
    
    // Lua脚本保证原子性，一次请求完成检查和扣减
    String luaScript = 
        "local count = redis.call('GET', KEYS[1]) " +
        "if count == false then return 0 end " +
        "local qty = tonumber(count) " +
        "if qty < tonumber(ARGV[1]) then return 0 end " +
        "redis.call('DECRBY', KEYS[1], ARGV[1]) " +
        "return 1";
    
    Long result = redisTemplate.execute(
        new DefaultRedisScript<>(luaScript, Long.class),
        Collections.singletonList(stockKey),
        String.valueOf(quantity)
    );
    
    return result != null && result == 1L;
}

为什么用Lua脚本？因为GET和DECRBY分开执行会有竞态条件——两个请求同时读到库存为1，然后都执行扣减，库存变成-1。Lua脚本在Redis单线程模型下保证原子性，彻底避免这个问题。

扣减成功后，异步发送消息：

// 发送MQ消息，异步落库
public void asyncCreateOrder(Long productId, int quantity, String userId) {
    OrderMessage message = new OrderMessage();
    message.setProductId(productId);
    message.setQuantity(quantity);
    message.setUserId(userId);
    message.setTimestamp(System.currentTimeMillis());
    
    // 发送到RocketMQ，设置重试策略
    rocketMQTemplate.sendMessage("ORDER_TOPIC", message);
}

消费者从MQ消费，写入MySQL：

// 消费者：异步落库到MySQL
@RocketMQMessageListener(
    topic = "ORDER_TOPIC",
    consumerGroup = "order-consumer-group",
    maxRetries = 3
)
public class OrderConsumer implements RocketMQListener<OrderMessage> {
    
    @Override
    public void onMessage(OrderMessage message) {
        try {
            // 幂等性检查，防止重复消费
            String idempotentKey = "order:" + message.getUserId() + ":" + message.getProductId();
            if (redisTemplate.opsForValue().setIfAbsent(idempotentKey, "1", 24, TimeUnit.HOURS)) {
                
                // 写入订单主表
                Order order = new Order();
                order.setUserId(message.getUserId());
                order.setProductId(message.getProductId());
                order.setQuantity(message.getQuantity());
                order.setStatus(OrderStatus.PENDING_PAYMENT);
                order.setCreateTime(new Date());
                orderMapper.insert(order);
                
                // 写入订单明细
                OrderDetail detail = new OrderDetail();
                detail.setOrderId(order.getId());
                detail.setProductId(message.getProductId());
                detailMapper.insert(detail);
                
            } else {
                log.warn("重复消费，跳过: {}", idempotentKey);
            }
        } catch (Exception e) {
            log.error("订单落库失败", e);
            throw new RuntimeException("订单落库失败", e);
        }
    }
}

这套方案的核心优势在于：MySQL只承受MQ消费的速度，而不是前端请求的峰值速度。假设MQ消费者每秒能处理2000笔订单，那MySQL的QPS就是2000，而不是10万。Redis扛10万QPS没有问题，因为它完全在内存中操作，不涉及磁盘I/O。

2.3 幂等性为什么这么重要

很多团队在异步落库时忽略了幂等性，结果MQ消息重复消费，导致同一用户下单多次。上面的代码里用Redis的setIfAbsent做了分布式锁保证幂等，key是userId + productId，有效期24小时，覆盖了整个秒杀活动窗口。

还有一种更稳妥的做法是用数据库的唯一索引：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    status TINYINT NOT NULL DEFAULT 0,
    create_time DATETIME NOT NULL,
    UNIQUE KEY uk_user_product (user_id, product_id)  -- 唯一索引防重
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

当重复消息到来时，唯一索引约束会抛出Duplicate entry异常，在catch块里捕获后忽略即可。这种方法不依赖Redis，更可靠。

三、王者荣耀登录场景：读多写少的高并发优化

王者荣耀登录和电商下单是两种完全不同的场景。登录是典型的读多写少场景——几百万玩家同时在线，每秒可能有几十万次登录请求，但真正写入数据库的操作很少（主要是更新登录时间和在线状态）。

这个场景的核心痛点是：数据库连接数被查询请求打满，而不是被写请求打满。

3.1 查询缓存的陷阱

很多开发者第一个想到的优化方案是给查询加缓存。但这里有个关键认知：MySQL的Query Cache在5.7版本就已经被标记为废弃，8.0版本直接移除了。这意味着你不能依赖MySQL自带的查询缓存。

那正确的做法是什么？用Redis做应用层缓存。

// 用户信息缓存读取
public User getUserInfo(Long userId) {
    String cacheKey = "user:info:" + userId;
    
    // 1. 先查Redis
    String json = redisTemplate.opsForValue().get(cacheKey);
    if (json != null) {
        return JSON.parseObject(json, User.class);
    }
    
    // 2. Redis没有，查MySQL
    User user = userMapper.selectById(userId);
    if (user != null) {
        // 3. 写入Redis，设置过期时间防雪崩
        redisTemplate.opsForValue().set(
            cacheKey, 
            JSON.toJSONString(user), 
            30, 
            TimeUnit.MINUTES
        );
    }
    
    return user;
}

这里有两个细节很重要：

过期时间抖动：如果所有key都在30分钟后同时过期，下一波请求会全部打到MySQL，造成缓存穿透。解决办法是给过期时间加一个随机抖动：

// 过期时间加1-5分钟的随机抖动，避免集中过期
long expireTime = 30 + new Random().nextInt(5);
redisTemplate.opsForValue().set(cacheKey, JSON.toJSONString(user), expireTime, TimeUnit.MINUTES);

缓存穿透：如果查询一个不存在的userId，Redis和MySQL都没有，每次请求都会打到数据库。解决办法是用布隆过滤器或者对空值也做缓存（设置较短的过期时间，比如60秒）：

// 缓存空值，防止穿透
if (user == null) {
    redisTemplate.opsForValue().set(cacheKey, "", 60, TimeUnit.SECONDS);
    return null;
}

3.2 连接池的正确配置

登录场景下，MySQL的连接数问题往往出在连接池配置不合理。很多团队用的是默认配置：

# 错误的连接池配置
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=30000

maximum-pool-size=10意味着同时只有10个数据库连接，几万并发请求排队等连接，响应时间直接爆炸。但如果你把连接数开到500，又会有另一个问题：每个连接都有内存开销，MySQL端也要维护这些连接，连接数太多会导致MySQL的max_connections报警，甚至触发OOM。

正确的做法是根据MySQL的承载能力和应用需求，计算合理的连接数：

# 合理的连接池配置
spring.datasource.hikari.maximum-pool-size=50
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.connection-timeout=30000

maximum-pool-size=50意味着每个应用实例最多占用50个数据库连接。如果你的应用有10个实例，总共占用500个连接。MySQL的max_connections设置为800，留200个连接给管理操作和突发流量。

同时，一定要开启连接泄漏检测：

spring.datasource.hikari.leak-detection-threshold=60000

这会在连接超过60秒没归还时记录警告日志，帮助你快速定位那些没有正确关闭连接的业务代码。

3.3 分库分表的抉择

王者荣耀级别的流量，单表数据量也会成为问题。假设每个玩家每天产生10条登录日志，100万玩家一年就是36.5亿条记录，单表查询性能会急剧下降。

这时候需要考虑分库分表。ShardingSphere是目前最成熟的方案：

# ShardingSphere分库分表配置
spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://ds0:3306/game_db_0
        username: root
        password: xxx
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://ds1:3306/game_db_1
        username: root
        password: xxx
    rules:
      sharding:
        tables:
          login_log:
            actual-data-nodes: ds$->{0..1}.login_log_$->{0..3}
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: user_id_inline
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
        sharding-algorithms:
          user_id_inline:
            type: INLINE
            props:
              algorithm-expression: login_log_$->{user_id % 4}
        key-generators:
          snowflake:
            type: SNOWFLAKE

这个配置的含义是：

  • 两个数据库（ds0、ds1），每个库4张表（login_log_0到login_log_3），总共8张表
  • 按user_id % 4路由到对应的分片表
  • 主键用雪花算法生成，避免分布式ID冲突

分片后的查询需要注意：跨分片的查询性能很差，所以设计表结构时要尽量让查询走分片键。比如登录日志查询通常是按user_id查某个用户的登录历史，这是走单分片的，性能很好。但如果是查”今天所有登录用户数”，就需要扫所有分片，这种场景可以用定时任务预聚合到宽表里。

四、双11秒杀场景：全链路优化的综合实践

双11秒杀是最复杂的场景，因为它同时包含高并发写入（下单）、高并发读取（查库存/查活动）、事务一致性（扣库存+创建订单）和海量数据（订单表膨胀）四个挑战。

4.1 数据库层面的核心优化

索引设计：从建表开始就要想清楚

很多团队的数据库表结构是临时拼凑的，没有考虑查询模式。下面是一个典型的错误订单表设计：

-- 错误的表设计：没有合理索引，查询性能极差
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    product_id BIGINT,
    order_no VARCHAR(32),
    amount DECIMAL(10,2),
    status TINYINT,
    create_time DATETIME,
    pay_time DATETIME,
    receive_time DATETIME,
    comment_time DATETIME
);

这张表有几个问题：

  1. 没有order_no的唯一索引：order_no是业务核心字段，用户查订单、客服查订单都靠它，但没有索引只能全表扫描
  2. 没有(user_id, create_time)联合索引：用户查”我的订单”是高频操作，没有索引就全表扫
  3. 没有(status, create_time)联合索引：运营查”待支付订单”是高频操作，同样需要索引

正确的建表方式：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    order_no VARCHAR(32) NOT NULL COMMENT '订单号',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    product_id BIGINT NOT NULL COMMENT '商品ID',
    product_name VARCHAR(255) COMMENT '商品名称(冗余，避免关联查询)',
    quantity INT NOT NULL DEFAULT 1 COMMENT '购买数量',
    amount DECIMAL(10,2) NOT NULL COMMENT '订单金额',
    status TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态:0待支付1已支付2已发货3已完成4已取消',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
    pay_time DATETIME COMMENT '支付时间',
    ship_time DATETIME COMMENT '发货时间',
    receive_time DATETIME COMMENT '确认收货时间',
    cancel_time DATETIME COMMENT '取消时间',
    
    UNIQUE KEY uk_order_no (order_no) COMMENT '订单号唯一索引',
    KEY idx_user_id_create_time (user_id, create_time) COMMENT '用户订单查询索引',
    KEY idx_status_create_time (status, create_time) COMMENT '状态+时间索引，用于运营查询',
    KEY idx_product_id_status (product_id, status) COMMENT '商品订单查询索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单主表';

这里的关键设计思想是覆盖索引和冗余字段。product_name冗余在订单表里，这样查订单详情时不需要关联商品表，减少一次IO。联合索引的顺序也很重要，idx_user_id_create_time把user_id放前面，因为等值查询的字段应该放在联合索引的前面，这样索引选择性最高。

事务优化：缩小事务范围

秒杀下单的事务如果包含太多操作，锁持有时间就长，并发能力就低。

// 错误的事务设计：事务包含太多操作
@Transactional(rollbackFor = Exception.class)
public Order createOrder(Long userId, Long productId, int quantity) {
    // 1. 查询商品信息（不必要的查询）
    Product product = productMapper.selectById(productId);
    
    // 2. 查询用户信息（不必要）
    User user = userMapper.selectById(userId);
    
    // 3. 扣减库存（核心操作）
    int affected = stockMapper.deductStock(productId, quantity);
    if (affected == 0) {
        throw new BusinessException("库存不足");
    }
    
    // 4. 创建订单
    Order order = new Order();
    order.setUserId(userId);
    order.setProductId(productId);
    order.setQuantity(quantity);
    order.setAmount(product.getPrice().multiply(BigDecimal.valueOf(quantity)));
    order.setStatus(OrderStatus.PENDING_PAYMENT);
    order.setOrderNo(generateOrderNo());
    orderMapper.insert(order);
    
    // 5. 插入订单明细
    OrderDetail detail = new OrderDetail();
    detail.setOrderId(order.getId());
    detail.setProductId(productId);
    detail.setQuantity(quantity);
    detailMapper.insert(detail);
    
    return order;
}

这个事务的问题在于：步骤1和2的查询完全没必要放在事务里，它们只是读操作，不会改变数据。更重要的是，如果步骤1查询商品失败，整个事务回滚，但实际上步骤3的库存扣减已经被回滚了，这没问题。但问题在于事务持有时间过长，其他请求在等这个事务的锁。

正确的做法是把事务拆小，只包核心写操作：

// 正确的事务设计：事务范围最小化
public Order createOrder(Long userId, Long productId, int quantity) {
    
    // 1. 先查商品和库存（事务外，只读）
    Product product = productMapper.selectById(productId);
    if (product == null) {
        throw new BusinessException("商品不存在");
    }
    
    // 2. 检查库存（事务外）
    int availableStock = stockMapper.getStock(productId);
    if (availableStock < quantity) {
        throw new BusinessException("库存不足");
    }
    
    // 3. 开始事务，只做核心写操作
    return transactionTemplate.execute(status -> {
        try {
            // 3.1 扣减库存（行锁，事务内）
            int affected = stockMapper.deductStock(productId, quantity);
            if (affected == 0) {
                status.setRollbackOnly();
                throw new BusinessException("库存扣减失败，请重试");
            }
            
            // 3.2 创建订单（事务内）
            Order order = new Order();
            order.setUserId(userId);
            order.setProductId(productId);
            order.setProductName(product.getName());
            order.setQuantity(quantity);
            order.setAmount(product.getPrice().multiply(BigDecimal.valueOf(quantity)));
            order.setStatus(OrderStatus.PENDING_PAYMENT);
            order.setOrderNo(generateOrderNo());
            orderMapper.insert(order);
            
            // 3.3 插入订单明细（事务内）
            OrderDetail detail = new OrderDetail();
            detail.setOrderId(order.getId());
            detail.setProductId(productId);
            detail.setProductName(product.getName());
            detail.setQuantity(quantity);
            detailMapper.insert(detail);
            
            return order;
        } catch (Exception e) {
            status.setRollbackOnly();
            throw e;
        }
    });
}

这里的关键变化：查询操作全部放在事务外，事务只包含扣库存和创建订单这两个写操作。事务持有时间从原来的”查询+写”缩短到只有”写”，锁竞争大幅减少。

大表拆分的时机和策略

订单表数据量增长很快，双11之后可能达到数亿条。这时候必须做冷热数据分离：

-- 热表：近3个月的订单，放在高性能SSD上
CREATE TABLE orders_hot (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    product_name VARCHAR(255),
    quantity INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status TINYINT NOT NULL DEFAULT 0,
    create_time DATETIME NOT NULL,
    pay_time DATETIME,
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_user_id_create_time (user_id, create_time),
    KEY idx_status_create_time (status, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
  DATA DIRECTORY='/data/mysql_hot/orders_hot'
  INDEX DIRECTORY='/data/mysql_hot/orders_hot';

-- 冷表：3个月前的订单，归档到低成本存储
CREATE TABLE orders_cold (
    id BIGINT PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    product_name VARCHAR(255),
    quantity INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status TINYINT NOT NULL DEFAULT 0,
    create_time DATETIME NOT NULL,
    pay_time DATETIME,
    archive_time DATETIME NOT NULL COMMENT '归档时间',
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_user_id_create_time (user_id, create_time),
    KEY idx_archive_time (archive_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
  DATA DIRECTORY='/data/mysql_cold/orders_cold'
  INDEX DIRECTORY='/data/mysql_cold/orders_cold';

冷热分离的触发逻辑：

// 定时任务：每月1号凌晨执行，把3个月前的订单归档
@Scheduled(cron = "0 0 2 1 * ?")
public void archiveOldOrders() {
    LocalDate archiveDate = LocalDate.now().minusMonths(3);
    DateTime cutoffTime = LocalDateTime.of(archiveDate, LocalTime.MIN);
    
    // 分批归档，每次处理1万条
    int batchSize = 10000;
    long lastId = 0;
    
    while (true) {
        List<Order> ordersToArchive = orderMapper.selectOlderThan(cutoffTime, lastId, batchSize);
        if (ordersToArchive.isEmpty()) {
            break;
        }
        
        // 批量写入冷表
        for (Order order : ordersToArchive) {
            OrderCold cold = new OrderCold();
            BeanUtils.copyProperties(order, cold);
            cold.setArchiveTime(LocalDateTime.now());
            orderColdMapper.insert(cold);
        }
        
        // 删除热表中的数据
        long maxId = ordersToArchive.stream()
            .mapToLong(Order::getId)
            .max()
            .getAsLong();
        orderMapper.deleteArchiveBatch(cutoffTime, lastId, maxId);
        
        lastId = maxId;
        log.info("归档批次完成，lastId: {}, 数量: {}", lastId, ordersToArchive.size());
    }
}

这里用了游标分页的方式分批处理，避免一次性加载大量数据到内存。selectOlderThan查询使用了覆盖索引，只返回需要的字段。

4.2 读写分离的优雅实现

双11期间，订单查询量往往是下单量的5-10倍。如果所有查询都打在主库，主库的写压力已经很大，再扛查询压力就吃不消了。读写分离是最基础的优化手段：

# Spring Boot + MyBatis 读写分离配置
spring:
  shardingsphere:
    datasource:
      names: master,slave0,slave1
      master:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://master:3306/db
        username: root
        password: xxx
      slave0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://slave0:3306/db
        username: root
        password: xxx
      slave1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://slave1:3306/db
        username: root
        password: xxx
    rules:
      readwrite-splitting:
        data-sources:
          ds:
            type: ROUND_ROBIN  # 轮询负载均衡
            props:
              write-data-source-name: master
              read-data-source-names: slave0,slave1
            loader-name: null

但读写分离有一个经典问题：主从延迟。用户在主库下单后，立即去查订单，如果此时从库还没同步完，用户会看到”订单不存在”的错误。

解决主从延迟问题有几种思路：

思路一：下单后立即强制读主库

// 使用ShardingSphere的强制路由
public Order getOrderAfterCreate(Long orderId) {
    // 强制路由到主库
    MasterSlaveRouter.useMaster();
    try {
        return orderMapper.selectById(orderId);
    } finally {
        MasterSlaveRouter.clear();
    }
}

思路二：设置短时间的强一致读

// 下单后的5秒内，强制读主库
public Order createOrderAndReadBack(Long userId, Long productId, int quantity) {
    Order order = createOrder(userId, productId, quantity);
    
    // 下单后等待500ms，给主从同步留时间
    try {
        Thread.sleep(500);
    } catch (InterruptedException e) {
        Thread.currentThread().interrupt();
    }
    
    // 强制读主库
    MasterSlaveRouter.useMaster();
    try {
        return orderMapper.selectById(order.getId());
    } finally {
        MasterSlaveRouter.clear();
    }
}

思路三：业务层面容忍延迟

如果业务允许用户下单后几秒钟内查不到订单（比如支付页面前先展示”下单成功”，不实时查订单详情），那就不需要强制读主库，直接读从库即可。

4.3 慢查询治理：从监控到优化的闭环

再好的架构，如果没有慢查询治理，大促时也会崩。某电商平台的双十一复盘报告显示，30%的MySQL性能问题源于慢查询。

如何发现慢查询

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;  -- 超过0.5秒的查询记录为慢查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 查看慢查询状态
SHOW VARIABLES LIKE 'slow_query%';
SHOW STATUS LIKE 'Slow_queries';

用pt-query-digest分析慢查询

# 安装pt-query-digest
apt-get install percona-toolkit

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > /tmp/slow_report.txt

# 查看最耗时的查询
pt-query-digest --filter '$event->{Query_time_total} > 10' /var/log/mysql/slow.log

典型慢查询的优化案例

案例一：隐式类型转换导致索引失效

-- 错误的查询：user_id是BIGINT，但传了字符串
SELECT * FROM orders WHERE user_id = '123456';

-- 执行计划显示type=ALL，全表扫描
EXPLAIN SELECT * FROM orders WHERE user_id = '123456';

优化方法：传参时保持类型一致。

-- 正确的查询
SELECT * FROM orders WHERE user_id = 123456;

-- 执行计划显示type=ref，走索引
EXPLAIN SELECT * FROM orders WHERE user_id = 123456;

案例二：OR条件导致索引失效

-- 错误的查询：OR两边的字段索引不同
SELECT * FROM orders WHERE user_id = 100 OR status = 1;

-- 执行计划显示type可能为ALL或index，取决于优化器的选择

优化方法：拆成UNION ALL。

-- 正确的查询
SELECT * FROM orders WHERE user_id = 100
UNION ALL
SELECT * FROM orders WHERE status = 1 AND user_id <> 100;

案例三：LIMIT分页深度翻页问题

-- 错误的翻页：offset很大时性能极差
SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time DESC LIMIT 10000, 20;

-- 当offset=10000时，MySQL需要扫描10020行然后丢弃前10000行

优化方法：延迟关联。

-- 正确的翻页：先查主键，再关联回原表
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders WHERE user_id = 100 ORDER BY create_time DESC LIMIT 10000, 20
) tmp ON o.id = tmp.id;

子查询只查主键（覆盖索引），速度极快，然后再JOIN回原表取完整数据。

五、高并发下的MySQL核心配置调优

前面讲了架构层面的优化，但MySQL自身的配置同样关键。很多团队大促前直接拿生产环境的配置去抗压，配置没调优，架构再好也白搭。

5.1 InnoDB核心参数

# my.cnf 核心配置

# Buffer Pool：InnoDB的数据和索引缓存，设为物理内存的60-70%
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 8  # 多实例减少锁竞争

# 日志文件：增大日志文件可以减少checkpoint频率，提升写入性能
innodb_log_file_size = 2G
innodb_log_files_in_group = 2

# 刷盘策略：大促期间可以牺牲一点安全性换取性能
# 0: 每秒刷盘一次（最快，宕机可能丢1秒数据）
# 1: 每次事务提交都刷盘（最安全，最慢）
# 2: 每次事务提交写OS缓存，每秒刷盘（折中方案）
innodb_flush_log_at_trx_commit = 2

# 刷盘方式：O_DIRECT绕过OS缓存，避免双重缓存
innodb_flush_method = O_DIRECT

# IO Capacity：根据磁盘类型设置
innodb_io_capacity = 2000      # SSD磁盘可设为2000-5000
innodb_io_capacity_max = 4000

# 脏页刷新：增大比例可以减少刷盘频率
innodb_max_dirty_pages_pct = 90
innodb_max_dirty_pages_pct_lwm = 10

# 线程并发：根据CPU核数设置
innodb_thread_concurrency = 0  # 0表示不限制，让InnoDB自动调节
innodb_write_io_threads = 8
innodb_read_io_threads = 8

# 行锁优化
innodb_lock_wait_timeout = 5      # 锁等待超时时间
innodb_deadlock_detect = on       # 开启死锁检测

innodb_flush_log_at_trx_commit = 2这个参数在双十一期间尤其重要。设置为1时，每笔订单都要sync一次磁盘，10万QPS就是每秒10万次sync，机械硬盘根本扛不住（机械硬盘的随机写性能大概500-1000 IOPS），即使SSD也很难稳定支撑。设置为2后，事务提交时只写OS Page Cache，MySQL每秒刷一次盘，性能提升10倍以上，宕机最多丢1秒数据。对于电商订单来说，这个风险是可以接受的——支付环节有额外的对账机制兜底。

5.2 连接和线程优化

# 连接管理
max_connections = 2000          # 最大连接数
max_user_connections = 1800     # 每个用户最大连接数
thread_cache_size = 200         # 线程缓存，减少创建销毁开销

# 临时表优化
tmp_table_size = 64M
max_heap_table_size = 64M

# 排序和JOIN缓冲区
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 4M

# 网络
net_read_timeout = 30
net_write_timeout = 60

注意：sort_buffer_size和join_buffer_size是每个连接的缓冲区，不是全局的。如果max_connections=2000，每个连接都分配2M，理论上最多占用4GB内存。所以要根据实际并发量调整，不要盲目放大。

5.3 监控告警配置

没有监控的MySQL优化就是盲人摸象。大促期间必须配置完整的监控体系：

# Prometheus + Grafana MySQL监控配置
# mysqld_exporter配置
mysql_exporter:
  datanames:
    - performance_schema
    - information_schema
  collect:
    - info_schema.innodb_metrics
    - info_schema.tablestats
    - global_status
    - global_variables
    - processlist
    - slave_status

# 关键告警规则
alerting_rules:
  - alert: MySQLHighQPS
    expr: rate(mysql_global_status_questions[1m]) > 50000
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: "MySQL QPS超过5万"
      
  - alert: MySQLReplicationLag
    expr: mysql_slave_status_seconds_behind_master > 10
    for: 1m
    labels:
      severity: critical
    annotations:
      summary: "主从延迟超过10秒"
      
  - alert: MySQLConnectionsUsage
    expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: "连接数使用率超过80%"
      
  - alert: MySQLSlowQueries
    expr: rate(mysql_global_status_slow_queries[1m]) > 100
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "每秒慢查询超过100个"
      
  - alert: MySQLInnodbBufferPoolHitRate
    expr: (1 - mysql_info_schema_memory_buffer_bytes / mysql_global_status_innodb_buffer_pool_bytes) < 0.95
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "Buffer Pool命中率低于95%"

六、从拼多多到王者荣耀：场景化的优化思路总结

聊了这么多技术细节，最后把三个场景的优化思路做个对比总结，这样你能更清楚地看到不同场景下的侧重点。

优化维度 拼多多抢票 王者荣耀登录 双11秒杀
核心挑战 库存并发竞争 读多写少 读写混合高并发
MySQL角色 异步落库（非实时） 缓存命中后的兜底 核心事务库
关键优化 Redis原子扣减+MQ异步 应用层缓存+连接池调优 读写分离+分库分表
事务策略 缩小事务范围 几乎无事务 最小化事务+分片事务
数据一致性 最终一致性 强一致性（登录态） 最终一致性+对账
容量规划 预热库存到Redis 预热用户信息到Redis 冷热分离+归档

拼多多抢票的本质是”抢”，重点在于把竞争从MySQL转移到Redis。MySQL在这里的角色是异步落库，压力很小。关键在于MQ的消费能力要和Redis的扣减速度匹配，否则MQ积压会导致订单数据延迟。

王者荣耀登录的本质是”查”，重点在于缓存命中率。MySQL在这里是最后一道防线，99%的请求应该被Redis挡住。关键是缓存的失效策略和穿透防护，以及连接池的合理配置。

双11秒杀的本质是”交易”，重点在于全链路的协调。MySQL在这里既是写库也是读库，既要扛下单压力也要扛查询压力。关键在架构分层（缓存层+MQ层+DB层）和配置调优（刷盘策略、索引设计、分库分表）。

最后说一个很多团队容易忽视的点：大促前的压测不是可选项，是必选项。

没有压测的架构优化就是赌博。你配置调得再好、索引设计得再完美，不知道真实峰值下系统会不会崩，就不敢上大促。压测的方法也很关键：

# 使用sysbench进行MySQL压力测试
sysbench oltp_read_write --table-size=1000000 --tables=10 \
  --mysql-host=127.0.0.1 --mysql-port=3306 \
  --mysql-user=root --mysql-password=xxx \
  --threads=100 --time=60 --report-interval=10 \
  run

# 结果示例：
# transactions:                        125000 (2082.45 per sec.)
# read/write requests:                 1750000 (29154.38 per sec.)
# other operations:                    250000 (4164.91 per sec.)
# total time:                          60.0102s

压测不是一次性的，应该在每次架构调整、配置变更、版本迭代后都做。建立基线数据，对比优化前后的差异，才能真正知道优化有没有效果。

MySQL在高并发场景下的表现，从来不是单一因素决定的。它是架构设计、数据库配置、索引策略、缓存方案、监控体系共同作用的结果。希望这篇文章能帮你建立起一套完整的高并发MySQL优化思维框架，而不是只记住几个配置参数。毕竟，工具会变，架构会演进，但”分层抗压、异步解耦、读写分离、冷热分离”这些核心思想是相通的。

-- 展开阅读全文 --