某电商平台秒杀导致数据库崩溃后MySQL高并发优化索引读写分离与分库分表实战指南
说实话，那天晚上我的手机一直在震。凌晨两点，风控系统报警：订单服务响应时间飙到12秒，数据库CPU直接打满100%，最要命的是连接数爆了——3000个连接， MySQL直接OOM崩溃重启。第二天早上一看，秒杀活动刚上线半小时，GMV还没破万，运维团队已经忙成了一团。
这事儿发生在我入职这家电商平台后的第三个月。之前我以为自己挺懂MySQL的，毕竟看了不少优化案例，但真到了线上，才发现理论和实战之间隔着一整个宇宙。今天就把这次血泪教训梳理出来，希望能帮到正在经历或者即将面临类似场景的你。
一、秒杀场景的”死亡螺旋”是怎么形成的
先复盘一下当时的情况。我们的秒杀活动是平台上年度最大的促销之一，设计了一个”限量1000台手机，1元抢购”的活动页面。活动开始前，我做了基本的压力测试——单机MySQL扛住500 QPS没问题，心想这点量级应该轻松。
问题出在哪？
第一个坑：热点行锁竞争
所有用户都在抢购同一款商品，这意味着所有写请求都在更新同一行记录。MySQL的InnoDB引擎会对这一行加行锁，前一个事务没提交，后面的全部排队。压测时500 QPS分散在几百个SKU上没问题，但集中到1个SKU上，QPS直接乘以100，行锁竞争让数据库瞬间瘫痪。
第二个坑：全表扫描+回表
我们的订单表当时没有建合适的索引。查询订单状态时，MySQL走的是全表扫描，每扫一行都要回表取完整数据。百万级数据量下，这种查询代价极高。更糟糕的是，秒杀期间大量并发查询同一个订单，IOPS直接打满。
第三个坑：连接数爆炸
业务代码里，每个请求都开一个数据库连接，而且没有合理设置连接池。秒杀开始后，请求量从平时的几百瞬间飙到上万，每个请求都要等锁、扫表，连接迟迟不释放，短时间内积累了3000+个连接，MySQL的max_connections默认就151，直接崩了。
我把这些问题画了张图，大概就是这样的：
用户请求涌入
↓
大量并发UPDATE同一行 → 行锁排队 → 事务等待超时
↓
大量并发SELECT无合适索引 → 全表扫描 → IOPS打满
↓
连接池耗尽 → max_connections超限 → MySQL崩溃
二、索引优化：让查询从”扫全表”变成”秒命中”
优化从索引开始，这是最直接也最有效的手段。
2.1 秒杀订单表的索引设计
我们的订单表结构大致如下：
CREATE TABLE `seckill_order` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` bigint(20) NOT NULL COMMENT '用户ID',
`product_id` bigint(20) NOT NULL COMMENT '商品ID',
`seckill_id` bigint(20) NOT NULL COMMENT '秒杀活动ID',
`order_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单状态:0待支付1已支付2已取消3已完成',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
`pay_time` datetime DEFAULT NULL COMMENT '支付时间',
PRIMARY KEY (`id`),
KEY `idx_user_product` (`user_id`, `product_id`),
KEY `idx_seckill_status` (`seckill_id`, `order_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='秒杀订单表';
这里有两个关键索引：
idx_user_product：联合索引，用于查询”某个用户在某个秒杀活动下的订单”。秒杀场景下，用户频繁查询自己的订单状态，这个索引让查询从全表扫描变成索引扫描。idx_seckill_status：按秒杀活动ID和订单状态建立索引，用于后台查询某个活动的订单统计。
2.2 覆盖索引：减少回表
覆盖索引是秒杀场景下特别有用的技巧。所谓覆盖索引，就是查询所需的列都在索引中，不需要回表查主键。
比如，秒杀页面需要展示”用户A在秒杀活动B下的订单状态”，我们可以这样设计：
-- 覆盖索引示例：查询只需要这3列，索引已经包含
SELECT user_id, seckill_id, order_status
FROM seckill_order
WHERE user_id = 123456 AND seckill_id = 789;
如果这个查询走的是联合索引(user_id, seckill_id, order_status)，那么所有数据都在索引树中，不需要回表，性能提升非常明显。
2.3 避免索引失效的常见写法
很多开发者写SQL时不经意间就让索引失效了，这里列出几个秒杀场景下常见的坑：
-- ❌ 错误写法1：对索引列做函数运算
SELECT * FROM seckill_order WHERE DATE(create_time) = '2024-01-01';
-- ✅ 正确写法：范围查询
SELECT * FROM seckill_order WHERE create_time >= '2024-01-01 00:00:00'
AND create_time < '2024-01-02 00:00:00';
-- ❌ 错误写法2：隐式类型转换
-- 假设user_id是bigint，传了字符串
SELECT * FROM seckill_order WHERE user_id = '123456';
-- ✅ 正确写法：保持类型一致
SELECT * FROM seckill_order WHERE user_id = 123456;
-- ❌ 错误写法3：LEFT JOIN导致右表索引失效
SELECT o.*, p.name FROM seckill_order o
LEFT JOIN product p ON o.product_id = p.id
WHERE o.seckill_id = 789;
-- ✅ 优化：确保JOIN条件和WHERE条件都能走索引
2.4 慢查询日志分析与优化
优化索引不能凭空猜，要用数据说话。MySQL的慢查询日志是最直接的线索：
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过1秒的查询记录为慢查询
-- 使用mysqldumpslow分析慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
分析结果会告诉你哪些查询最耗时间，然后针对性地加索引。我们的经验是：索引不是越多越好，每个索引都会增加写操作的开销，特别是INSERT、UPDATE、DELETE，因为索引树也要同步维护。一般单表索引不超过5个，且要优先保证高频查询的覆盖。
三、读写分离：把读压力分出去
索引优化解决了”查得快”的问题，但秒杀场景的读请求可能是写请求的几十倍甚至上百倍。这时候，读写分离就是必须的。
3.1 主从复制架构
我们采用的是MySQL原生主从复制架构：
┌─────────────┐
│ Master │
│ (写操作) │
└──────┬──────┘
│ binlog
┌────────────┼────────────┐
↓ ↓ ↓
┌──────────┐ ┌──────────┐ ┌──────────┐
│ Slave 1 │ │ Slave 2 │ │ Slave 3 │
│ (读操作) │ │ (读操作) │ │ (读操作) │
└──────────┘ └──────────┘ └──────────┘
主库负责所有写操作，从库负责读操作。主库通过binlog把变更同步到从库，从库通过IO线程和SQL线程回放日志，实现数据一致性。
3.2 代码层面的读写分离实现
我们使用ShardingSphere来实现读写分离，配置非常简单：
# sharding.yaml 配置示例
dataSources:
ds_master:
url: jdbc:mysql://master-host:3306/seckill_db
username: root
password: your_password
ds_slave_0:
url: jdbc:mysql://slave1-host:3306/seckill_db
username: root
password: your_password
ds_slave_1:
url: jdbc:mysql://slave2-host:3306/seckill_db
username: root
password: your_password
rules:
- !READWRITE_SPLITTING
dataSources:
readwrite_ds:
writeDataSourceName: ds_master
readDataSources:
- ds_slave_0
- ds_slave_1
loadBalancerName: RANDOM
props:
write-read-splitting-enabled: true
在Java代码中，读写分离是透明的：
@Service
public class SeckillOrderService {
@Resource
private SeckillOrderMapper orderMapper;
/**
* 查询订单详情 - 走从库
*/
public SeckillOrder queryOrder(Long orderId) {
// 这个查询自动路由到从库
return orderMapper.selectById(orderId);
}
/**
* 创建订单 - 走主库
*/
@Transactional
public boolean createOrder(SeckillOrder order) {
// 这个写入自动路由到主库
int result = orderMapper.insert(order);
return result > 0;
}
/**
* 查询秒杀活动库存 - 强制走主库（保证数据新鲜度）
*/
public int queryStock(Long productId) {
// 使用Hint强制走主库
return orderMapper.selectStockWithMasterHint(productId);
}
}
3.3 主从延迟问题：秒杀场景的特殊处理
读写分离最大的痛点是主从延迟。从库通过binlog同步，存在几毫秒到几秒不等的延迟。在秒杀场景下，这会导致严重问题：
- 用户刚下单，立刻查询订单状态，结果查到的是旧数据
- 库存扣减后，立即查询剩余库存，结果还是显示有货
我们的解决方案是关键查询强制走主库：
/**
* 秒杀下单后，立即查询订单状态
* 必须走主库，不能用从库
*/
public SeckillOrder queryOrderAfterCreate(Long orderId) {
// ShardingSphere提供的主库强制路由
MasterSlaveHintManager.setMasterOnly();
try {
return orderMapper.selectById(orderId);
} finally {
MasterSlaveHintManager.clear();
}
}
/**
* 扣减库存后，立即查询剩余库存
*/
public int queryRemainingStock(Long productId) {
MasterSlaveHintManager.setMasterOnly();
try {
return orderMapper.selectRemainingStock(productId);
} finally {
MasterSlaveHintManager.clear();
}
}
当然，强制走主库会降低读分离的效果，所以需要权衡。我们的策略是：
- 普通查询（订单列表、活动详情）→ 从库
- 关键查询（下单后查状态、库存查询）→ 主库
- 统计类查询（订单量、GMV）→ 从库（允许一定延迟）
3.4 监控主从延迟
必须建立主从延迟的监控，一旦延迟超过阈值，要能及时发现：
-- 查看主从延迟（在从库执行）
SHOW SLAVE STATUS\G
-- 关键字段
Seconds_Behind_Master: 0 -- 延迟秒数，0表示无延迟
我们用了Prometheus + Grafana监控这个指标，延迟超过5秒就告警。
四、分库分表：从根本上解决单库压力
读写分离解决了读的扩展性问题，但写压力依然集中在主库。当QPS继续上涨，单台MySQL实在扛不住时，分库分表就是终极解决方案。
4.1 分库分表的思路
分库分表的核心思想是”把大象装进冰箱，分三步”：
- 分表：把一个大表拆成多个小表，分散存储
- 分库：把多个表分配到不同的数据库实例
- 路由：根据路由规则，把请求分发到正确的库和表
我们的订单表采用”按用户ID取模分库，按订单ID取模分表”的策略：
用户ID % 8 = 0 → 数据库0 → 订单表0,1,2,3
用户ID % 8 = 1 → 数据库1 → 订单表0,1,2,3
...
用户ID % 8 = 7 → 数据库7 → 订单表0,1,2,3
这样，8个数据库实例，每个库4张表，总共32张表，分散了压力。
4.2 ShardingSphere配置实战
# sharding.yaml - 分库分表配置
dataSources:
ds_0:
url: jdbc:mysql://db-host-0:3306/seckill_db_0
username: root
password: your_password
ds_1:
url: jdbc:mysql://db-host-1:3306/seckill_db_1
username: root
password: your_password
# ... 共8个数据源
rules:
- !SHARDING
tables:
seckill_order:
actualDataNodes: ds_$->{0..7}.seckill_order_$->{0..3}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: order-table-inline
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: user-db-inline
shardingAlgorithms:
user-db-inline:
type: INLINE
props:
algorithm-expression: ds_$->{user_id % 8}
order-table-inline:
type: INLINE
props:
algorithm-expression: seckill_order_$->{order_id % 4}
# 主键生成策略：雪花算法
keyGenerators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 1
4.3 跨库查询的处理
分库分表后，跨库查询是个大问题。比如”查询某个秒杀活动的所有订单”，订单分散在8个库中，需要广播查询：
/**
* 查询某个秒杀活动的所有订单
* 需要广播查询所有分片
*/
public List<SeckillOrder> queryOrderBySeckillId(Long seckillId) {
// ShardingSphere支持SQL改写，自动广播查询
String sql = "SELECT * FROM seckill_order WHERE seckill_id = ?";
return orderMapper.queryBySeckillId(seckillId);
}
/**
* 查询某个用户的所有订单
* 根据user_id路由到特定分片，性能更好
*/
public List<SeckillOrder> queryOrderByUserId(Long userId) {
String sql = "SELECT * FROM seckill_order WHERE user_id = ?";
return orderMapper.queryByUserId(userId);
}
广播查询性能较差，所以设计上要避免。我们的策略是：
- 高频查询尽量设计成单分片查询（按user_id查询）
- 必要的广播查询加缓存，减少直接查数据库
4.4 分库分表后的事务问题
分布式事务是分库分表必须面对的难题。我们采用了本地事务 + 最终一致性的方案：
/**
* 秒杀下单：扣库存 + 创建订单
* 使用本地事务保证单分片内的一致性
*/
@Transactional(rollbackFor = Exception.class)
public SeckillOrder createSeckillOrder(SeckillOrderRequest request) {
Long userId = request.getUserId();
Long productId = request.getProductId();
Long seckillId = request.getSeckillId();
// 1. 扣减库存（单分片操作）
int remaining = inventoryMapper.decreaseStock(productId);
if (remaining < 0) {
throw new RuntimeException("库存不足");
}
// 2. 创建订单（单分片操作，和库存同库）
SeckillOrder order = new SeckillOrder();
order.setUserId(userId);
order.setProductId(productId);
order.setSeckillId(seckillId);
order.setOrderStatus(OrderStatus.PENDING);
order.setCreateTime(new Date());
orderMapper.insert(order);
return order;
}
如果涉及跨库操作，我们使用消息队列 + 补偿机制：
/**
* 跨库操作：创建订单 + 发送消息
*/
public void createOrderWithMessage(SeckillOrder order) {
// 1. 创建订单（本地事务）
orderMapper.insert(order);
// 2. 发送消息（保证消息发送成功）
Message message = new Message("seckill_order_created", order);
try {
rocketMQTemplate.syncSend("seckill_order_topic", message);
} catch (Exception e) {
// 消息发送失败，订单回滚
throw new RuntimeException("订单创建失败", e);
}
}
/**
* 消息消费：更新用户库存记录（跨库操作）
*/
@RocketMQMessageListener(topic = "seckill_order_topic", consumerGroup = "order_consumer")
public class OrderMessageConsumer implements RocketMQListener<Message> {
@Override
public void onMessage(Message message) {
SeckillOrder order = JSON.parseObject(message.getBody(), SeckillOrder.class);
// 3. 更新用户维度的库存记录（可能在另一个库）
userInventoryMapper.updateOrderCount(order.getUserId(), 1);
}
}
4.5 数据迁移：从单库到分库分表
分库分表不是小事，数据迁移需要谨慎。我们的迁移方案是双写 + 校验 + 切换：
阶段1：双写
- 应用同时写主库和分库分表
- 主库作为数据源，分库分表作为目标
阶段2：数据校验
- 比对主库和分库分表的数据一致性
- 修复不一致的数据
阶段3：切换读
- 应用先从分库分表读
- 确认数据正确后，关闭主库读
阶段4：切换写
- 应用只写分库分表
- 关闭主库写
- 归档主库数据
迁移过程中，我们用了一个开源工具DataX做数据同步，配合自研的校验脚本确保数据一致性。
五、秒杀场景的完整优化方案
索引优化、读写分离、分库分表，这三步解决了数据库层面的问题。但秒杀场景还有其他关键优化点，这里一并给出。
5.1 缓存层：Redis扛住读请求
秒杀场景下，读请求远超写请求。我们把读请求全部打到Redis缓存上：
/**
* 秒杀商品详情查询 - 走缓存
*/
public SeckillProduct queryProduct(Long productId) {
String cacheKey = "seckill:product:" + productId;
// 1. 先查缓存
SeckillProduct product = redisTemplate.opsForValue().get(cacheKey);
if (product != null) {
return product;
}
// 2. 缓存未命中，查数据库
product = productMapper.selectById(productId);
// 3. 写入缓存，TTL 5分钟
if (product != null) {
redisTemplate.opsForValue().set(cacheKey, product, 5, TimeUnit.MINUTES);
}
return product;
}
/**
* 秒杀库存查询 - 走Redis原子操作
*/
public boolean tryDeductStock(Long productId) {
String stockKey = "seckill:stock:" + productId;
// Redis原子操作，保证并发安全
Long remaining = redisTemplate.opsForValue().decrement(stockKey);
if (remaining < 0) {
// 库存不足，恢复并返回失败
redisTemplate.opsForValue().increment(stockKey);
return false;
}
return true;
}
5.2 限流：保护数据库不被打爆
即使优化了数据库，也架不住瞬间的流量洪峰。必须在网关层做限流：
/**
* 基于Redis的分布式限流
*/
@Component
public class RateLimiter {
@Resource
private RedisTemplate<String, String> redisTemplate;
/**
* 限流：同一用户每秒最多10次请求
*/
public boolean tryAcquire(String userId) {
String key = "rate_limit:" + userId;
Long count = redisTemplate.opsForValue().increment(key);
if (count != null && count == 1) {
redisTemplate.expire(key, 1, TimeUnit.SECONDS);
}
return count != null && count <= 10;
}
}
5.3 队列异步：削峰填谷
秒杀下单的核心流程，可以用队列异步处理：
用户点击"立即购买"
↓
网关限流（快速拒绝超限请求）
↓
Redis预扣库存（原子操作）
↓
下单请求入队（Kafka/RocketMQ）
↓
用户看到"排队中"提示
↓
消费者异步处理：创建订单、扣库存
↓
发送消息通知用户：下单成功/失败
/**
* 秒杀下单接口 - 快速响应
*/
@PostMapping("/seckill/order")
public Result createOrder(@RequestBody SeckillOrderRequest request) {
String userId = request.getUserId().toString();
// 1. 限流检查
if (!rateLimiter.tryAcquire(userId)) {
return Result.fail("请求过于频繁，请稍后重试");
}
// 2. Redis预扣库存
boolean success = stockService.tryDeductStock(request.getProductId());
if (!success) {
return Result.fail("库存不足");
}
// 3. 下单请求入队
SeckillOrderMessage message = new SeckillOrderMessage();
message.setUserId(request.getUserId());
message.setProductId(request.getProductId());
message.setSeckillId(request.getSeckillId());
rocketMQTemplate.syncSend("seckill_order_topic", message);
// 4. 立即返回"排队中"
return Result.ok("排队中，请耐心等待");
}
/**
* 消息消费者 - 异步创建订单
*/
@RocketMQMessageListener(topic = "seckill_order_topic", consumerGroup = "order_consumer")
public class OrderCreateConsumer implements RocketMQListener<Message> {
@Override
public void onMessage(Message message) {
SeckillOrderMessage msg = JSON.parseObject(message.getBody(), SeckillOrderMessage.class);
try {
// 创建订单
SeckillOrder order = orderService.createOrder(msg);
// 发送成功通知
notificationService.sendSuccessMessage(msg.getUserId(), order.getId());
} catch (Exception e) {
// 失败处理：恢复库存、发送失败通知
stockService.restoreStock(msg.getProductId());
notificationService.sendFailMessage(msg.getUserId(), e.getMessage());
}
}
}
六、优化效果对比
优化前后，我们的系统表现有了质的飞跃：
| 指标 | 优化前 | 优化后 |
|---|---|---|
| 最大并发QPS | 500 | 5000+ |
| 订单创建平均响应时间 | 12秒 | 200ms |
| 订单查询平均响应时间 | 8秒 | 50ms |
| 数据库CPU使用率 | 100%（崩溃） | 40% |
| 连接数峰值 | 3000+（OOM） | 500 |
当然，这些数字背后是无数次的压测、调优和踩坑。MySQL的优化没有银弹，需要根据实际业务场景反复打磨。
七、一些实战中的踩坑提醒
最后分享几个血泪教训：
1. 不要在高并发下用SELECT *
每次查询只取需要的字段，减少网络传输和内存占用。特别是分库分表后，网络开销会被放大。
2. 大事务是小事务的敌人
秒杀场景下，一个事务最好控制在毫秒级。事务持有锁的时间越长，并发竞争越激烈。我们的订单创建事务从最初的500ms优化到了50ms以内。
3. 连接池参数要合理设置
# HikariCP连接池配置
spring:
datasource:
hikari:
maximum-pool-size: 50 # 根据实际并发调整
minimum-idle: 10
idle-timeout: 30000
max-lifetime: 1800000
connection-timeout: 30000
连接池太小会排队，太大会耗尽数据库连接。50个连接，每个连接每秒处理100个请求，理论QPS是5000，足以应对大多数秒杀场景。
4. 压测要贴近真实场景
我们的压测工具是JMeter，但关键是压测场景要贴近真实：
真实秒杀场景：
- 10万人同时访问
- 80%读请求，20%写请求
- 热点商品集中抢购
- 正常用户穿插浏览
压测时模拟这些场景，才能发现真正的问题。
这篇指南写得有点长，但都是实战中摸爬滚打出来的经验。MySQL高并发优化是个系统工程，索引、读写分离、分库分表只是其中的一部分，还需要配合缓存、限流、队列等多种手段。希望这篇文章能帮你少走一些弯路。如果你正在面临类似的场景，欢迎交流，咱们一起探讨。