电商秒杀导致MySQL宕机怎么办 索引优化读写分离架构调整手把手教你扛住百万级并发
说实话，做电商的兄弟们谁没经历过那种”系统崩了、老板骂了、用户跑了”的至暗时刻。尤其是搞秒杀活动的时候，明明想着用低价引流，结果直接把数据库给干趴下了，那种感觉比失恋还难受。
先搞清楚，到底是谁在”杀”你的MySQL
别一上来就调参数、加索引，先搞清楚问题出在哪。
我见过太多案例，后台监控一看，QPS（每秒查询数）突然从几百飙到几十万，然后MySQL连接数直接打满，CPU飙到100%，整个服务就挂了。但你知道具体是哪条SQL在搞事吗？
举个真实例子，之前有个做鞋服的电商朋友，搞了个”9块9秒杀限量球鞋”的活动。表面上看起来很简单——用户点抢购，扣库存，下单。实际上他们的数据库里有一张订单表，没加索引，每次查询都是全表扫描。百万用户同时涌入的时候，每张表都扫一遍，数据库直接喘不过气。
-- 问题代码：没有索引的查询
SELECT * FROM orders WHERE user_id = 12345 AND status = 0;
-- 全表扫描，百万级数据每来一个请求就扫一遍，服务器能扛得住才怪
所以第一步，先开慢查询日志，看看到底哪些SQL在拖后腿。
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过1秒的查询记录为慢查询
-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';
-- 用mysqldumpslow分析慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
-- -s t 按执行时间排序，-t 10 取前10条
这一步很关键，很多人直接跳过去，结果折腾半天，发现就是几条烂SQL在搞鬼。
索引优化：给数据库装上”高速公路”
索引是MySQL性能优化的核心，没有之一。但在秒杀场景下，光有索引还不够，得知道怎么加、加在哪、加什么。
1. 复合索引的正确姿势
很多开发者喜欢加索引，但加得很随意。比如上面那张订单表，有人会在user_id上加索引，有人在status上加索引，还有人两个都加。但实际上，复合索引比单列索引高效得多。
-- 错误示范：多个单列索引，MySQL只能用一个
ALTER TABLE orders ADD INDEX idx_user (user_id);
ALTER TABLE orders ADD INDEX idx_status (status);
-- 正确做法：复合索引，注意顺序
-- 等值查询列在前，范围查询列在后
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);
-- 为什么user_id在前？因为等值查询可以精确匹配，过滤更多数据
-- 如果反过来，status是范围查询，MySQL就无法利用后面的user_id了
2. covering index（覆盖索引）：让MySQL不”回表”
这是很多高阶开发者都知道，但普通人不太会用的高级技巧。
什么叫回表？简单说，就是你查了索引，但索引里没包含你需要的全部字段，MySQL还得回去原表查，这一来一回，IO代价很大。
-- 普通查询，可能需要回表
SELECT * FROM orders WHERE user_id = 12345 AND status = 0;
-- 如果select *包含了大量字段，而索引只覆盖了user_id和status，MySQL就要回表
-- 覆盖索引优化：只查索引里有的字段
SELECT id, user_id, status FROM orders WHERE user_id = 12345 AND status = 0;
-- 如果(id, user_id, status)构成了覆盖索引，MySQL直接从小索引树就能拿到所有数据，不用回表
在秒杀场景下，这个优化效果极其明显。因为你的查询往往只需要几个关键字段，没必要把整个订单记录都拉回来。
3. 前缀索引：VARCHAR字段的救命稻草
秒杀场景中，经常会有根据商品名称、用户昵称模糊查询的情况。但VARCHAR字段建索引，如果字符串很长，索引会非常大，影响性能。
-- 商品表，name字段很长
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(255),
price DECIMAL(10, 2),
INDEX idx_name (name) -- 255长度的索引，浪费空间
);
-- 前缀索引：只取前N个字符建立索引
CREATE INDEX idx_name_prefix ON products(name(20));
-- 前20个字符通常已经足够区分不同商品了
-- 可以用以下命令找出合适的前缀长度
SELECT COUNT(DISTINCT LEFT(name, 5)) / COUNT(*) FROM products;
-- 如果比例接近1，说明前缀长度够了；如果很低，就加大前缀长度
4. 最左前缀原则：复合索引的”铁律”
复合索引(a, b, c)就像一条链子，查询必须从最左边开始匹配，不能跳跃。
-- 索引：(user_id, status, create_time)
-- 正确用法：从最左列开始
SELECT * FROM orders WHERE user_id = 123 AND status = 0; -- 可以使用索引
SELECT * FROM orders WHERE user_id = 123 AND status = 0 AND create_time > '2024-01-01'; -- 可以使用索引
SELECT * FROM orders WHERE status = 0 AND create_time > '2024-01-01'; -- user_id没指定，status索引失效
-- 错误用法：跳过了最左列
SELECT * FROM orders WHERE status = 0; -- 索引完全失效，全表扫描
5. 避免在索引列上做运算
这是新手最容易犯的错误，但在高并发场景下，后果极其严重。
-- 错误：在索引列上做运算，导致索引失效
SELECT * FROM orders WHERE YEAR(create_time) = 2024;
-- MySQL对每一行都做YEAR()运算，索引完全失效
-- 正确：把运算放到查询条件那边
SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';
-- 范围查询，可以利用create_time索引
-- 错误：对索引列使用函数
SELECT * FROM orders WHERE LEFT(user_id, 3) = '123';
-- 正确：直接用范围查询
SELECT * FROM orders WHERE user_id >= '123000' AND user_id < '124000';
秒杀场景的特殊处理：库存扣减的”生死博弈”
秒杀最核心的问题，其实是库存扣减。这个操作看起来简单，但并发一高，要么超卖（卖多了），要么数据库直接崩掉。
方案一：CAS乐观锁（适合低并发）
-- 表结构
CREATE TABLE stock (
id INT PRIMARY KEY,
product_id INT,
quantity INT,
version INT DEFAULT 0 -- 版本号，用于CAS
);
-- 扣减库存的SQL
UPDATE stock
SET quantity = quantity - 1, version = version + 1
WHERE product_id = 1001 AND quantity > 0 AND version = 5;
-- 只有version匹配时才会更新，成功返回1，失败返回0
-- 前端拿到0就提示"已售罄"
这个方案简单，但在高并发下，大量请求同时读到version=5，然后同时尝试更新，只有一个能成功，其他都失败。失败的部分会被数据库”吞掉”，然后重试，形成争抢。
方案二：Redis预减库存（中大规模秒杀必备）
真正扛住百万并发的方案，是把库存放到Redis里预减，数据库只是最终落库。
// 伪代码示例，用Redis做库存扣减
// 第一步：活动开始前，把库存加载到Redis
// Redis设置：stock:1001 = 1000（假设1000件）
// 第二步：秒杀请求进来，先在Redis里扣
String stockKey = "stock:" + productId;
long remaining = redis.decr(stockKey); // 原子操作，直接减1
if (remaining < 0) {
// 库存已抢完
redis.incr(stockKey); // 恢复，避免后续查询出错
return "秒杀已结束";
}
// 第三步：扣减成功，异步写入数据库
// 用消息队列（Kafka/RabbitMQ）把扣减请求发出去
kafkaTemplate.send("order-topic",
new OrderRequest(userId, productId, remaining)
);
// 第四步：数据库消费消息，落库
// 这里可以用批量插入，大大减轻数据库压力
-- 数据库侧：批量插入订单，比单条插入快10倍以上
INSERT INTO orders (user_id, product_id, status, create_time) VALUES
(1001, 100, 1, NOW()),
(1002, 100, 1, NOW()),
(1003, 100, 1, NOW()),
-- ... 一次批量几百条
(1200, 100, 1, NOW());
方案三：本地缓存+队列+数据库（终极方案）
如果是千万级甚至更大的秒杀，光靠Redis还不够，需要在应用层再做一层缓冲。
用户请求 -> 本地缓存（Caffeine/Guava） -> Redis -> 消息队列 -> MySQL
↑ 拒绝请求 ↑ 排队请求 ↑ 最终落库
// 本地缓存层：快速拦截大部分请求
// 用Caffeine做本地缓存，有效期100ms
Cache<String, Boolean> localCache = Caffeine.newBuilder()
.expireAfterWrite(100, TimeUnit.MILLISECONDS)
.build();
public boolean trySecKill(String userId, String productId) {
String cacheKey = userId + ":" + productId;
// 第一步：本地缓存快速判断（微秒级）
if (localCache.getIfPresent(cacheKey) != null) {
return false; // 已经买过了
}
// 第二步：Redis判断（毫秒级）
Boolean result = redisTemplate.opsForValue().setIfAbsent(
"seckill:" + cacheKey, "1", 10, TimeUnit.SECONDS
);
if (!Boolean.TRUE.equals(result)) {
localCache.put(cacheKey, false); // 缓存到本地
return false; // 没抢到
}
// 第三步：放入消息队列，异步处理
kafkaTemplate.send("sec Kill-topic", cacheKey);
localCache.put(cacheKey, true);
return true; // 排队成功
}
这个架构的核心思想是：能拒绝的越早拒绝越好，能异步的越异步越好。本地缓存挡掉99%的请求，Redis挡住剩下的，消息队列把最后一点压力平滑到数据库上。
读写分离：让数据库”分而治之”
秒杀的时候，读多写少是常态。大部分用户在”看”，只有少数人在”买”。读写分离就是利用这个特点，让读操作分散到从库，写操作集中在主库。
基础架构
┌──────────┐
│ 应用层 │
└────┬─────┘
│
┌─────────────┼─────────────┐
│ │ │
┌──────▼──────┐ ┌───▼────┐ ┌──────▼──────┐
│ 主库 │ │ 从库1 │ │ 从库2 │
│ (写操作) │ │(读操作)│ │ (读操作) │
└─────────────┘ └────────┘ └─────────────┘
MySQL主从复制配置
# 主库配置（my.cnf）
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW # 推荐用ROW格式，数据一致性更好
expire_logs_days = 7
max_binlog_size = 100M
# 创建复制账号
CREATE USER 'repl'@'%' IDENTIFIED BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
# 从库配置（my.cnf）
[mysqld]
server-id = 2
log-bin = mysql-bin
relay-log = relay-bin
read_only = 1 # 从库只读，防止误写
-- 从库连接主库
CHANGE MASTER TO
MASTER_HOST = '主库IP',
MASTER_USER = 'repl',
MASTER_PASSWORD = 'your_password',
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 154;
START SLAVE;
-- 查看从库状态
SHOW SLAVE STATUS\G
-- 关注这两个字段：
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- 两个都是Yes才算复制正常
应用层读写分离实现
// Spring Boot + MyBatis 读写分离配置
@Configuration
public class DataSourceConfig {
@Bean
@Primary
public DataSource masterDataSource() {
DruidDataSource ds = new DruidDataSource();
ds.setUrl("jdbc:mysql://master:3306/ecommerce");
ds.setUsername("root");
ds.setPassword("your_password");
return ds;
}
@Bean
public DataSource slave1DataSource() {
DruidDataSource ds = new DruidDataSource();
ds.setUrl("jdbc:mysql://slave1:3306/ecommerce");
ds.setUsername("root");
ds.setPassword("your_password");
return ds;
}
@Bean
public DataSource slave2DataSource() {
DruidDataSource ds = new DruidDataSource();
ds.setUrl("jdbc:mysql://slave2:3306/ecommerce");
ds.setUsername("root");
ds.setPassword("your_password");
return ds;
}
// 路由数据源，写入走主库，读取走从库
@Bean
public RoutingDataSource routingDataSource(
@Qualifier("masterDataSource") DataSource master,
@Qualifier("slave1DataSource") DataSource slave1,
@Qualifier("slave2DataSource") DataSource slave2
) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceType.MASTER, master);
targetDataSources.put(DataSourceType.SLAVE_1, slave1);
targetDataSources.put(DataSourceType.SLAVE_2, slave2);
RoutingDataSource routingDs = new RoutingDataSource();
routingDs.setTargetDataSources(targetDataSources);
routingDs.setDefaultTargetDataSource(master); // 默认走主库
return routingDs;
}
}
// 读写分离的AOP切面
@Aspect
@Component
public class DataSourceAspect {
@Before("@annotation(readOnly)")
public void setReadDataSource(JoinPoint point, ReadOnly readOnly) {
DataSourceType.set(DataSourceType.SLAVE);
}
@Before("@annotation(write)")
public void setWriteDataSource(JoinPoint point, Write write) {
DataSourceType.set(DataSourceType.MASTER);
}
}
// 自定义注解
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ReadOnly {}
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Write {}
// 使用示例
@Service
public class ProductService {
@ReadOnly // 读操作，走从库
public Product getById(Long id) {
return productMapper.selectById(id);
}
@Write // 写操作，走主库
public void updateStock(Long id, int quantity) {
productMapper.updateStock(id, quantity);
}
}
读写分离的坑：数据延迟
主从复制不是实时的，从库会有几毫秒到几秒的延迟。在秒杀场景下，这可能导致用户刚下单，去查订单时还没同步到从库。
解决方案很简单：下单后查订单，强制走主库。
@Write // 下单后查询订单，强制走主库
public Order queryOrder(Long orderId) {
return orderMapper.selectById(orderId);
}
架构升级：扛住百万并发的完整方案
光靠索引和读写分离，百万并发还是有点悬。要想真正扛住，得把整个架构升级。
完整架构图
┌─────────────────────────────────────────────────────────┐
│ 用户请求层 │
└─────────────────────────┬───────────────────────────────┘
│
┌───────────▼───────────┐
│ Nginx 负载均衡 │ ← 抗住第一波流量
└───────────┬───────────┘
│
┌─────────────────┼─────────────────┐
│ │ │
┌───────▼──────┐ ┌──────▼──────┐ ┌───────▼──────┐
│ Web服务器1 │ │ Web服务器2 │ │ Web服务器3 │ ← 无状态，可水平扩展
└───────┬──────┘ └──────┬──────┘ └───────┬──────┘
│ │ │
└────────────────┼─────────────────┘
│
┌──────────────▼──────────────┐
│ Redis 集群（哨兵+哨兵） │ ← 缓存+扣减库存
│ - 热点数据缓存 │
│ - 秒杀令牌发放 │
│ - 库存预扣减 │
└──────────────┬──────────────┘
│
┌──────────▼──────────┐
│ Kafka 消息队列 │ ← 削峰填谷
└──────────┬──────────┘
│
┌──────────▼──────────┐
│ 数据库集群 │ ← MySQL主从+分库分表
│ - 主库：写操作 │
│ - 从库：读操作 │
│ - 分库：按user_id取模 │
└─────────────────────┘
秒杀令牌机制：控制入场人数
不是所有人都能参与秒杀，提前发令牌，拿到令牌的人才有资格去抢。
@Service
public class SecKillService {
@Autowired
private RedisTemplate<String, String> redisTemplate;
/**
* 发放秒杀令牌
* 每个用户每个商品只有一个令牌，有效期10秒
*/
public boolean issueToken(String userId, String productId) {
String key = "token:" + userId + ":" + productId;
Boolean result = redisTemplate.opsForValue()
.setIfAbsent(key, "1", 10, TimeUnit.SECONDS);
return Boolean.TRUE.equals(result);
}
/**
* 验证并扣减库存
* 先验证令牌，再扣库存，两步都在Redis里原子完成
*/
public String executeSecKill(String userId, String productId) {
// 1. 验证令牌
String tokenKey = "token:" + userId + ":" + productId;
Boolean hasToken = redisTemplate.hasKey(tokenKey);
if (!Boolean.TRUE.equals(hasToken)) {
return "未获得秒杀资格";
}
redisTemplate.delete(tokenKey); // 令牌用完即删
// 2. 原子扣减库存
Long remaining = redisTemplate.opsForValue().decrement(
"stock:" + productId
);
if (remaining == null || remaining < 0) {
return "库存不足";
}
// 3. 发送订单消息到Kafka
OrderRequest request = new OrderRequest(userId, productId, remaining);
kafkaTemplate.send("order-topic", request);
return "秒杀成功，排队中";
}
}
分库分表：把压力分散到多个节点
当单库单表扛不住的时候，分库分表是终极方案。
// 分库分表策略：按用户ID取模
public class ShardingStrategy {
private static final int DB_COUNT = 4; // 4个数据库
private static final int TABLE_COUNT = 16; // 每张库16张表
/**
* 根据用户ID和商品ID，计算出应该路由到哪个库的哪张表
*/
public RoutingResult route(Long userId, Long productId) {
// 数据库路由：按userId取模
int dbIndex = Math.abs(userId % DB_COUNT);
String dbName = "ecommerce_db_" + dbIndex;
// 表路由：按userId取模
int tableIndex = Math.abs(userId % TABLE_COUNT);
String tableName = "orders_" + tableIndex;
return new RoutingResult(dbName, tableName);
}
}
-- 分库分表后的建表示例
-- 每张表只存部分数据，压力分散到4个库×16张表=64张表
CREATE TABLE orders_0 (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT DEFAULT 1,
status TINYINT DEFAULT 0,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id),
INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 同理创建 orders_1 到 orders_15...
数据库连接池优化
秒杀场景下，连接池配置直接影响性能。
# application.yml 连接池配置
spring:
datasource:
master:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://master:3306/ecommerce?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
username: root
password: your_password
# 连接池配置
hikari:
maximum-pool-size: 50 # 最大连接数，根据并发量调整
minimum-idle: 10 # 最小空闲连接
idle-timeout: 30000 # 空闲连接超时30秒
max-lifetime: 1200000 # 连接最大生命周期20分钟
connection-timeout: 30000 # 获取连接超时30秒
leak-detection-threshold: 60000 # 连接泄漏检测60秒
-- MySQL服务器端参数调优
-- 调整这些参数，可以显著提升并发处理能力
-- 最大连接数，根据服务器内存调整（建议：连接数 × 每连接内存 < 服务器内存的50%）
SET GLOBAL max_connections = 1000;
-- 线程缓存，减少线程创建开销
SET GLOBAL thread_cache_size = 64;
-- InnoDB缓冲池，建议设置为物理内存的50%-70%
SET GLOBAL innodb_buffer_pool_size = 4G;
-- InnoDB日志文件大小，大日志文件提升写入性能
SET GLOBAL innodb_log_file_size = 512M;
SET GLOBAL innodb_log_buffer_size = 16M;
-- 刷盘策略，提升写入速度（牺牲一点崩溃恢复能力）
SET GLOBAL innodb_flush_log_at_trx_commit = 2; -- 每秒刷盘，不是每次事务都刷
监控与预警：别等挂了才知道
再好的架构，也需要监控来保驾护航。
// 用Micrometer + Prometheus监控MySQL关键指标
@Component
public class MySQLMetrics {
@Autowired
private MeterRegistry meterRegistry;
@Scheduled(fixedRate = 10000) // 每10秒采集一次
public void collectMetrics() {
try {
Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SHOW GLOBAL STATUS");
while (rs.next()) {
String variable = rs.getString(1);
String value = rs.getString(2);
// 连接数
if ("Threads_connected".equals(variable)) {
meterRegistry.gauge("mysql.threads.connected",
Double.parseDouble(value));
}
// 活跃连接
if ("Threads_running".equals(variable)) {
meterRegistry.gauge("mysql.threads.running",
Double.parseDouble(value));
}
// QPS
if ("Questions".equals(variable)) {
meterRegistry.gauge("mysql.questions.total",
Double.parseDouble(value));
}
// 慢查询数
if ("Slow_queries".equals(variable)) {
meterRegistry.gauge("mysql.slow_queries.total",
Double.parseDouble(value));
}
// 死锁数
if ("Innodb_deadlocks".equals(variable)) {
meterRegistry.gauge("mysql.innodb.deadlocks",
Double.parseDouble(value));
}
}
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
log.error("采集MySQL指标失败", e);
}
}
}
# Grafana + Prometheus 告警规则
groups:
- name: mysql_alerts
rules:
- alert: MysqlHighConnections
expr: mysql_threads_connected > 800
for: 1m
labels:
severity: warning
annotations:
summary: "MySQL连接数过高"
description: "当前连接数{{ $value }}，超过阈值800"
- alert: MysqlSlowQueries
expr: rate(mysql_slow_queries_total[5m]) > 10
for: 2m
labels:
severity: critical
annotations:
summary: "MySQL慢查询激增"
description: "每分钟慢查询{{ $value }}条，超过阈值10条"
- alert: MysqlReplicationLag
expr: mysql_slave_delay_seconds > 5
for: 1m
labels:
severity: warning
annotations:
summary: "主从延迟过高"
description: "从库延迟{{ $value }}秒"
实战 Checklist：秒杀前必做的10件事
最后，给你一个实战检查清单，每次搞秒杀活动前，按这个过一遍：
- 索引审查：用
EXPLAIN分析每条SQL的执行计划，确保用了索引 - 慢查询清理：导出过去7天的慢查询日志，逐一优化
- 连接池配置：确认连接池大小合适，避免连接不够用或浪费资源
- Redis预热：把热点数据提前加载到Redis，避免缓存穿透
- 库存预扣减：确认Redis库存扣减逻辑正确，不会出现超卖
- 消息队列配置：确认Kafka分区数足够，消费能力跟得上
- 读写分离延迟测试：确认主从延迟在可接受范围内
- 分库分表验证：确认数据路由正确，不会出现数据倾斜
- 压测：用JMeter或wrk做全链路压测，找到系统瓶颈
- 监控告警：确认所有关键指标都有监控，告警能及时触达
说实话，搞秒杀架构不是一蹴而就的事，它需要你对MySQL、Redis、消息队列、分布式系统都有深入的理解。但只要你把上面的每一步都做好，百万级并发也不是什么难事。
最重要的是，不要等到崩了才开始优化。每次大促前，把这套流程跑一遍，该优化的优化，该加资源的加资源。毕竟，用户的耐心是有限的，你的服务器也是。