某电商大促PV暴涨10倍数据库崩了 运维扩容救场 千万级流量网站查询优化实战指南
凌晨2点17分，我盯着监控大屏上的那条曲线，手心全是汗。”卧槽，QPS突破10万了！”同事的声音有点变调。这不是演习，是我们公司三年一度最大规模的双十一大促，流量池子终于打开了。
前一秒一切正常，下一秒报警群炸了。
Redis缓存命中率从99.7%掉到42%，MySQL的连接数像坐过山车一样往上冲，最要命的是慢查询日志疯狂滚动。主库CPU直接飙到98%，某条商品详情页的查询SQL，响应时间从12毫秒变成了8秒——8秒啊，用户等得起吗？
那一刻我才真正体会到什么叫”流量洪峰”。之前做压测，千万级PV我们扛得住，但那是理想状态。现实是，大促开始后，订单查询、库存扣减、用户登录、商品推荐这些接口同时打过来，数据库的连接池瞬间被吃光。
一、数据库崩了，别慌，先看这三件事
1.1 搞清楚是哪条SQL在作妖
报警群里有127条消息，但你不能眉毛胡子一把抓。第一件事就是找到那个最占资源的SQL。
-- 在MySQL里直接查正在执行的慢查询
SELECT
DIGEST_TEXT,
COUNT_STAR as 执行次数,
AVG_TIMER_WAIT/1000000000 as 平均耗时_秒,
MAX_TIMER_WAIT/1000000000 as 最大耗时_秒,
SUM_ROWS_EXAMINED as 总扫描行数
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'ecommerce_db'
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;
这条查出来，我们找到了一条让人头皮发麻的SQL：
-- 这条是商品详情查询，原本设计用来拉取商品基本信息
SELECT
p.id, p.name, p.price, p.stock, p.category_id,
c.name as category_name,
s.name as shop_name,
p.description,
p.created_at
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN shops s ON p.shop_id = s.id
WHERE p.id = ? AND p.status = 1
看着还行对吧？但在大促期间，每秒有超过2000次这样的查询。问题出在哪？LEFT JOIN在大规模并发下是个隐形杀手。status = 1这个条件看似简单，但如果products表的索引设计不合理，MySQL可能要走全表扫描。
-- 查看这条SQL的执行计划
EXPLAIN SELECT
p.id, p.name, p.price, p.stock, p.category_id,
c.name as category_name,
s.name as shop_name,
p.description,
p.created_at
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN shops s ON p.shop_id = s.id
WHERE p.id = 9527 AND p.status = 1;
执行计划显示type: ref，key: PRIMARY，看起来没问题。但Extra字段里有个Using index condition，这说明MySQL在执行索引查询的同时，还需要回表去读数据。当并发量上去，回表操作就撑不住了。
1.2 连接数暴涨，连接池被掏空
-- 查看当前连接数和状态
SHOW PROCESSLIST;
SHOW STATUS LIKE '%Connection%';
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Connections | 158742 |
| Max_used_connections | 582 |
| Aborted_connects | 2341 |
| Threads_connected | 582 |
| Threads_running | 127 |
+--------------------------+-----------+
582个连接，127个正在运行。我们的max_connections设的是1000，但问题是这582个连接里，有400多个都是慢查询占着茅坑不拉屎。
-- 查看哪些连接占着资源不释放
SELECT
ID, USER, HOST, DB,
COMMAND, TIME as 占用秒数,
STATE, LEFT(INFO, 100) as 当前查询
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC;
这些慢查询就像堵车时的事故车辆，占着车道不走，后面的车全堵死。
1.3 磁盘IO和Buffer Pool的压力
-- 查看Buffer Pool的命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
+---------------------------------------+------------+
| Variable_name | Value |
+---------------------------------------+------------+
| Innodb_buffer_pool_read_requests | 89234567 |
| Innodb_buffer_pool_reads | 234567 |
+---------------------------------------+------------+
命中率 = 1 - (234567 / 89234567) = 99.7%，看起来还行。但问题是，在高并发场景下，Buffer Pool里的热数据被冷数据挤出去，导致read_requests暴增，磁盘IO压力大。
# 用iostat看磁盘IO
iostat -x 1 5
Device r/s w/s rkB/s wkB/s await svctm %util
sda 45.2 128.7 1843.2 5124.8 23.4 4.2 67.8
%util到了67.8%，还没到100%，但await已经23.4毫秒了，正常应该小于2毫秒。
二、运维扩容的实战操作
2.1 紧急扩容：读从库加机器
大促当天，我们第一反应是加从库。怎么加最快？
# 使用Ansible批量部署MySQL从库
# inventory文件
[mysql_slaves]
slave1 ansible_host=10.0.1.101
slave2 ansible_host=10.0.1.102
slave3 ansible_host=10.0.1.103
slave4 ansible_host=10.0.1.104
# playbook: deploy_mysql_slave.yaml
- hosts: mysql_slaves
become: true
tasks:
- name: 安装MySQL
apt:
name: mysql-server
state: present
- name: 配置从库参数
template:
src: mysql_slave.cnf.j2
dest: /etc/mysql/mysql.conf.d/slave.cnf
- name: 启动MySQL并设置从库复制
shell: |
mysql -u root -p'${db_password}' -e "
CHANGE MASTER TO
MASTER_HOST='${master_host}',
MASTER_USER='repl',
MASTER_PASSWORD='${repl_password}',
MASTER_LOG_FILE='${master_log_file}',
MASTER_LOG_POS=${master_log_pos};
START SLAVE;
"
# mysql_slave.cnf.j2
[mysqld]
server-id = {{ inventory_hostname.split('.')[2] | int + 100 }}
log-bin = mysql-bin
binlog-format = ROW
relay-log = relay-bin
read-only = 1
super-read-only = 1
加完4台从库，通过读写分离，把查询流量分散出去。我们的架构是ShardingSphere做读写分离，配置非常简单：
# ShardingSphere读写分离配置
dataSources:
ds_master:
url: jdbc:mysql://10.0.1.100:3306/ecommerce_db
username: root
password: ${master_password}
ds_slave_0:
url: jdbc:mysql://10.0.1.101:3306/ecommerce_db
username: root
password: ${slave_password}
ds_slave_1:
url: jdbc:mysql://10.0.1.102:3306/ecommerce_db
username: root
password: ${slave_password}
ds_slave_2:
url: jdbc:mysql://10.0.1.103:3306/ecommerce_db
username: root
password: ${slave_password}
ds_slave_3:
url: jdbc:mysql://10.0.1.104:3306/ecommerce_db
username: root
password: ${slave_password}
rules:
- !READWRITE-SPLITTING
dataSources:
primaryDataSourceName: ds_master
slaveDataSourceNames:
- ds_slave_0
- ds_slave_1
- ds_slave_2
- ds_slave_3
props:
read-only-route-rules:
- load-balancer-type: ROUND_ROBIN
配置热加载，不需要重启应用。效果立竿见影，主库的QPS从8万降到了1.5万。
2.2 紧急扩容：MySQL参数调优
光加机器不够，还得优化参数。我们紧急修改了以下配置：
# /etc/mysql/mysql.conf.d/performance.cnf
# 内存相关
innodb_buffer_pool_size = 32G # 原来是16G，直接翻倍
innodb_buffer_pool_instances = 8 # 配合大Buffer Pool，减少锁竞争
# 并发相关
innodb_thread_concurrency = 0 # 0表示不限制，让MySQL自己决定
innodb_write_io_threads = 8
innodb_read_io_threads = 8
# 日志相关
innodb_flush_log_at_trx_commit = 2 # 原来1，改为2（每秒刷盘一次）
log_bin_trust_function_creators = 1
# 连接相关
max_connections = 2000 # 从1000调到2000
wait_timeout = 1800 # 30分钟超时，原来是28800（8小时）
interactive_timeout = 1800
# 查询缓存（MySQL 5.7废弃，但测试环境还在用）
query_cache_type = 0
query_cache_size = 0
# 应用配置
mysql -u root -p'${password}' -e "SHOW VARIABLES LIKE '%buffer_pool%';"
mysql -u root -p'${password}' -e "SHOW VARIABLES LIKE '%max_connections%';"
注意：innodb_flush_log_at_trx_commit = 2这个改动有风险——宕机可能丢1秒数据。但大促期间，宁可丢点数据，也不能崩。后来我们复盘的时候，又改回了1，并通过主从复制保证数据一致性。
2.3 紧急扩容：应用层限流
数据库扛不住，就从应用层下手。我们用Sentinel做限流：
// Sentinel限流规则配置
import com.alibaba.csp.sentinel.slots.block.RuleConstant;
import com.alibaba.csp.sentinel.slots.block.flow.FlowRule;
import com.alibaba.csp.sentinel.slots.block.flow.FlowRuleManager;
import java.util.ArrayList;
import java.util.List;
public class SentinelConfig {
public static void initFlowRules() {
List<FlowRule> rules = new ArrayList<>();
// 商品详情查询：QPS限制5000
FlowRule rule1 = new FlowRule();
rule1.setResource("getProductDetail");
rule1.setGrade(RuleConstant.FLOW_GRADE_QPS);
rule1.setCount(5000);
rule1.setControlBehavior(RuleConstant.CONTROL_BEHAVIOR_RATE_LIMITER);
rules.add(rule1);
// 订单查询：QPS限制2000
FlowRule rule2 = new FlowRule();
rule2.setResource("getOrderList");
rule2.setGrade(RuleConstant.FLOW_GRADE_QPS);
rule2.setCount(2000);
rules.add(rule2);
// 库存查询：QPS限制10000
FlowRule rule3 = new FlowRule();
rule3.setResource("getStock");
rule3.setGrade(RuleConstant.FLOW_GRADE_QPS);
rule3.setCount(10000);
rules.add(rule3);
FlowRuleManager.loadRules(rules);
}
}
// 在Service层使用
@SentinelResource(value = "getProductDetail", blockHandler = "handleBlock")
public ProductDTO getProductDetail(Long productId) {
// 正常逻辑
return productService.getById(productId);
}
// 限流降级处理
public ProductDTO handleBlock(Long productId, BlockException e) {
log.warn("Product detail限流: productId={}", productId);
// 返回缓存数据或默认值
return cacheService.getFromCache(productId);
}
限流的效果很明显，虽然有些请求被降级了，但系统整体没崩。用户端看到的可能就是”页面加载稍慢”或者”显示缓存数据”，总比白屏强。
三、查询优化实战
扩容只是救急，根本还是要优化查询。我们花了三天时间，把核心查询全部过了一遍。
3.1 最狠的一刀：把JOIN拆成多次查询
之前的SQL用了LEFT JOIN，在大促高并发下，JOIN操作的开销巨大。我们的优化方案是：放弃JOIN，改成应用层多次查询，然后用内存组装。
// 优化后的查询逻辑
@Service
public class ProductDetailService {
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private RedisTemplate<String, String> redisTemplate;
/**
* 优化前：一条SQL带两个LEFT JOIN
* 优化后：拆成三次查询，用内存组装
*/
public ProductDetailVO getProductDetail(Long productId) {
// 第一次查：商品基本信息（走缓存）
String cacheKey = "product:detail:" + productId;
ProductBasicVO cached = redisTemplate.opsForValue().get(cacheKey);
if (cached != null) {
return convertToVO(cached, null, null);
}
// 查主表，只需要id, name, price, stock, category_id, shop_id
String sql = "SELECT id, name, price, stock, category_id, shop_id, status " +
"FROM products WHERE id = ? AND status = 1";
ProductBasicVO product = jdbcTemplate.queryForObject(sql,
new BeanPropertyRowMapper<>(ProductBasicVO.class), productId);
if (product == null) {
throw new ProductNotFoundException(productId);
}
// 第二次查：分类名称（单独查，可缓存）
String categorySql = "SELECT id, name FROM categories WHERE id = ?";
CategoryVO category = jdbcTemplate.queryForObject(categorySql,
new BeanPropertyRowMapper<>(CategoryVO.class), product.getCategoryId());
// 第三次查：店铺名称（单独查，可缓存）
String shopSql = "SELECT id, name FROM shops WHERE id = ?";
ShopVO shop = jdbcTemplate.queryForObject(shopSql,
new BeanPropertyRowMapper<>(ShopVO.class), product.getShopId());
// 组装结果
ProductDetailVO vo = convertToVO(product, category, shop);
// 写缓存，TTL 5分钟
redisTemplate.opsForValue().set(cacheKey, cached, 5, TimeUnit.MINUTES);
return vo;
}
private ProductDetailVO convertToVO(ProductBasicVO p, CategoryVO c, ShopVO s) {
ProductDetailVO vo = new ProductDetailVO();
vo.setId(p.getId());
vo.setName(p.getName());
vo.setPrice(p.getPrice());
vo.setStock(p.getStock());
vo.setCategoryName(c != null ? c.getName() : "未知分类");
vo.setShopName(s != null ? s.getName() : "未知店铺");
return vo;
}
}
为什么拆JOIN更好？
JOIN操作在数据库层面需要构建临时表、排序、合并，这些操作在高并发下会占用大量内存和CPU。拆成多次简单查询后，每次查询都可以走索引，而且可以独立缓存。虽然多了两次网络往返，但单次查询的复杂度大幅降低。
我们用JMeter做了压测：
# 优化前：JOIN查询
Thread Group: 1000 threads
Ramp-up: 60s
Loop count: 10
平均响应时间: 847ms
90%响应时间: 2341ms
错误率: 12.7%
# 优化后：拆分查询
Thread Group: 1000 threads
Ramp-up: 60s
Loop count: 10
平均响应时间: 34ms
90%响应时间: 89ms
错误率: 0.0%
从847ms降到34ms，提升24倍。
3.2 索引优化：让查询更快
拆完JOIN，还要检查索引。我们用了下面这个脚本扫描所有慢查询，分析索引使用情况：
-- 扫描过去24小时的慢查询，分析索引命中情况
SELECT
DIGEST_TEXT as 查询语句,
COUNT_STAR as 执行次数,
AVG_TIMER_WAIT/1000000000 as 平均耗时_ms,
SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0) as 平均扫描行数,
FIRST_SEEN,
LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'ecommerce_db'
AND AVG_TIMER_WAIT > 100000000 -- 超过100ms
AND LAST_SEEN > DATE_SUB(NOW(), INTERVAL 24 HOUR)
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 20;
结果发现，products表上有个索引设计得有问题：
-- 原来的索引
CREATE INDEX idx_products_status_category ON products (status, category_id, id);
-- 问题：status只有两个值（0=下架, 1=上架），区分度极低
-- MySQL优化器看到区分度低的字段放在前面，可能不会用这个索引
-- 优化后的索引
-- 去掉status，因为查询时status=1是固定条件，不影响索引选择
ALTER TABLE products DROP INDEX idx_products_status_category;
CREATE INDEX idx_products_category_id ON products (category_id, id);
-- 同时加一个覆盖索引，避免回表
CREATE INDEX idx_products_covering ON products (id, name, price, stock, category_id, shop_id);
覆盖索引的意思是：查询需要的所有字段都在索引里，不需要回表查聚簇索引。这能大幅减少IO。
3.3 深分页优化：LIMIT offset太大是坑
电商网站的商品列表页，用户翻到第100页时，SQL大概是这样的：
-- 问题：offset越大，MySQL要扫描并丢弃的行数越多
SELECT id, name, price, image
FROM products
WHERE category_id = 5 AND status = 1
ORDER BY sales DESC
LIMIT 20 OFFSET 1980;
OFFSET 1980意味着MySQL要扫描并丢弃前1980行，才返回最后20行。这在大数据量下是灾难。
我们的优化方案有两种：
方案一：延迟关联
-- 先查主键，再关联回原表拿数据
SELECT p.id, p.name, p.price, p.image
FROM products p
INNER JOIN (
SELECT id FROM products
WHERE category_id = 5 AND status = 1
ORDER BY sales DESC
LIMIT 20 OFFSET 1980
) AS tmp ON p.id = tmp.id;
子查询只返回id，走的是覆盖索引，非常快。然后INNER JOIN回原表拿其他字段。
方案二：游标分页（推荐）
-- 记录上一页最后一条的id和sales
-- 下一页查询
SELECT id, name, price, image
FROM products
WHERE category_id = 5 AND status = 1
AND (sales < ? OR (sales = ? AND id < ?)) -- 游标条件
ORDER BY sales DESC, id DESC
LIMIT 20;
这种方式完全没有OFFSET，性能恒定，不管翻到第几页都是同样的速度。我们在商品列表页全面替换成了游标分页，体验提升明显。
3.4 缓存策略：Redis是关键
查询优化到这一步，还需要配合缓存。我们的缓存策略是这样的：
@Service
public class CacheService {
@Autowired
private RedisTemplate<String, Object> redisTemplate;
private static final long PRODUCT_DETAIL_TTL = 5 * 60; // 5分钟
private static final long PRODUCT_LIST_TTL = 2 * 60; // 2分钟
private static final long STOCK_TTL = 30; // 30秒，库存变化快
/**
* 商品详情缓存
*/
public ProductDetailVO getOrLoadProductDetail(Long productId) {
String key = "product:detail:" + productId;
// 1. 先查缓存
ProductDetailVO cached = (ProductDetailVO) redisTemplate.opsForValue().get(key);
if (cached != null) {
return cached;
}
// 2. 缓存穿透保护：查询空值也缓存（短TTL）
ProductDetailVO result = loadFromDB(productId);
if (result == null) {
redisTemplate.opsForValue().set(key, new ProductDetailVO(), 30, TimeUnit.SECONDS);
return null;
}
// 3. 缓存击穿保护：分布式锁
String lockKey = "lock:product:" + productId;
Boolean locked = redisTemplate.opsForValue()
.setIfAbsent(lockKey, "1", 10, TimeUnit.SECONDS);
if (Boolean.TRUE.equals(locked)) {
// 拿到锁，双重检查
cached = (ProductDetailVO) redisTemplate.opsForValue().get(key);
if (cached != null) {
return cached;
}
result = loadFromDB(productId);
redisTemplate.opsForValue().set(key, result, PRODUCT_DETAIL_TTL, TimeUnit.SECONDS);
redisTemplate.delete(lockKey);
} else {
// 没拿到锁，等50ms再查一次缓存
try {
Thread.sleep(50);
} catch (InterruptedException e) {
Thread.currentThread().interrupt();
}
cached = (ProductDetailVO) redisTemplate.opsForValue().get(key);
result = cached != null ? cached : loadFromDB(productId);
}
return result;
}
/**
* 库存缓存（注意：库存需要强一致性）
*/
public Integer getOrLoadStock(Long productId) {
String key = "product:stock:" + productId;
// 库存缓存TTL很短，保证实时性
Object cached = redisTemplate.opsForValue().get(key);
if (cached != null) {
return Integer.parseInt(cached.toString());
}
// 缓存击穿：用Redis原子操作
Long stock = redisTemplate.execute(new RedisCallback<Long>() {
@Override
public Long doInRedis(RedisConnection connection) {
StringRedisTemplate template = new StringRedisTemplate(connection.getNativeConnection());
// 先查缓存
String value = template.opsForValue().get(key);
if (value != null) {
return Long.parseLong(value);
}
// 查DB
Long dbStock = queryStockFromDB(productId);
if (dbStock != null) {
// 设置短TTL缓存
template.opsForValue().set(key, dbStock.toString(), STOCK_TTL, TimeUnit.SECONDS);
}
return dbStock;
}
});
return stock != null ? stock.intValue() : 0;
}
private ProductDetailVO loadFromDB(Long productId) {
// 调用之前优化的查询逻辑
return productDetailService.getProductDetail(productId);
}
private Long queryStockFromDB(Long productId) {
String sql = "SELECT stock FROM products WHERE id = ? AND status = 1";
return jdbcTemplate.queryForObject(sql, Long.class, productId);
}
}
缓存策略的核心是：不同数据不同TTL。商品详情可以缓存5分钟，但库存只能缓存30秒。促销价格、秒杀库存这些变化更快的数据，甚至不需要缓存，直接走DB或者Redis原子操作。
四、大促后的复盘：我们学到了什么
大促结束后，我们做了详细的复盘。有几个关键教训：
第一，压测要真实。 之前的压测是在测试环境做的，数据量和并发量都不够。这次大促的流量模式是：开头爆发式增长，中间平台期，结尾冲刺。这种模式在压测时没有模拟出来。后来我们引入了混沌工程，在大促前用故障注入测试系统的容错能力。
第二，监控要前置。 这次报警都是事后报警，如果能实时监控SQL的执行计划变化，就能提前发现问题。我们后来接入了Prometheus + Grafana，对慢查询、连接数、Buffer Pool命中率等指标做了实时监控和预警。
第三，架构要有冗余。 这次能扛住，主要靠读写分离和限流降级。但我们的缓存层是单点的，如果Redis挂了一台，影响很大。后来我们改造成了Redis Cluster，消除了单点故障。
第四，索引不是一劳永逸的。 这次发现的索引问题，其实在上线时就该发现。我们后来引入了定期索引审查机制，每月用pt-online-schema-change工具检查索引使用情况，及时清理无效索引。
五、给你的建议：如果明天就要做大促
如果你明天就要面临大促，别慌，按这个 checklist 来：
□ 数据库层面
□ 主从复制是否正常，延迟是否在可接受范围
□ max_connections是否足够（建议设为压测峰值的1.5倍）
□ innodb_buffer_pool_size是否调到物理内存的60-70%
□ innodb_flush_log_at_trx_commit是否可以根据业务容忍度调整
□ 慢查询日志是否开启，最近24小时的慢查询是否分析过
□ 索引层面
□ 核心查询的执行计划是否合理（用EXPLAIN检查）
□ 是否存在全表扫描
□ 索引区分度低的字段是否放错了位置
□ 是否可以用覆盖索引避免回表
□ 应用层面
□ 读写分离是否配置好
□ 限流规则是否配置（特别是查询类接口）
□ 缓存策略是否合理（TTL、穿透保护、击穿保护）
□ 深分页是否改成了游标分页
□ 监控层面
□ 数据库QPS、连接数、慢查询是否有实时监控
□ 是否配置了预警阈值
□ 告警是否通知到了值班人员
最后一句实话：数据库崩了不可怕，可怕的是没有预案。这次大促让我们明白，优化不是大促前的临时抱佛脚，而是日常工作的持续积累。每一次上线、每一次压测、每一次复盘，都是在为下一次大促攒经验。
希望这篇文章能帮到你。如果真的遇到大促压力，别怕，一步步来，先止血，再优化，最后复盘。我们都是从踩坑中成长起来的。