MySQL 综合调优实战案例

核心概念

性能调优需要综合考虑硬件、配置、SQL、架构等多个方面，通过真实案例学习调优思路和方法。

调优方法论

调优流程：
1. 设定目标（QPS、延迟、可用性）
2. 收集数据（监控、日志、指标）
3. 定位瓶颈（CPU、IO、内存、锁）
4. 制定方案（配置、SQL、架构）
5. 实施方案（灰度、回滚计划）
6. 验证效果（对比指标）
7. 持续优化（定期 review）

调优原则

1. 先优化 SQL，再调整配置
2. 先优化应用，再扩容硬件
3. 先解决瓶颈，再优化细节
4. 数据驱动，避免猜测
5. 小步快跑，逐步验证

案例 1：电商订单系统优化

背景

场景：电商大促期间订单系统性能问题
数据量：订单表 5000 万行
并发：峰值 10 万 QPS
问题：
- 下单延迟高（P99 > 500ms）
- 查询订单慢
- 数据库 CPU 持续 90%+

问题分析

-- 1. 查看慢查询
SELECT digest_text, count_star, avg_timer_wait 
FROM performance_schema.events_statements_summary_by_digest 
ORDER BY sum_timer_wait DESC LIMIT 10;

-- 发现问题 SQL：
-- SELECT * FROM orders WHERE user_id = ? AND create_time > ?
-- 全表扫描，avg_time: 200ms

-- 2. 查看锁等待
SELECT * FROM performance_schema.data_lock_waits;
-- 发现热点行竞争：订单状态更新

-- 3. 查看表大小
SELECT table_name, data_length, index_length, table_rows 
FROM information_schema.tables 
WHERE table_name = 'orders';
-- 5000 万行，单表过大

优化方案

1. SQL 优化

-- 添加复合索引
CREATE INDEX idx_user_time ON orders(user_id, create_time DESC);

-- 优化查询（覆盖索引）
SELECT id, order_no, status, amount FROM orders 
WHERE user_id = ? AND create_time > ?;

-- 分页优化（延迟关联）
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders 
    WHERE user_id = ? 
    ORDER BY create_time DESC 
    LIMIT 100000, 10
) tmp ON o.id = tmp.id;

2. 表结构优化

-- 垂直分表（大字段分离）
CREATE TABLE orders_detail (
    order_id BIGINT PRIMARY KEY,
    receiver_info TEXT,
    remark TEXT
);

-- 水平分表（按用户 ID 分表）
-- orders_0, orders_1, ..., orders_9
-- 路由规则：hash(user_id) % 10

3. 架构优化

读写分离：
- 主库：写操作（下单、更新状态）
- 从库：读操作（查询订单）

缓存优化：
- Redis 缓存热点订单
- 缓存 TTL: 30 分钟

异步处理：
- 下单后异步发短信
- 异步更新统计

优化效果

优化前：
- P99 延迟：500ms
- CPU 使用率：95%
- 慢查询：1000/min

优化后：
- P99 延迟：50ms（10 倍提升）
- CPU 使用率：40%
- 慢查询：10/min

案例 2：金融支付系统优化

背景

场景：支付系统转账业务
数据量：账户表 1000 万行
并发：峰值 1 万 TPS
问题：
- 转账死锁频繁
- 余额更新慢
- 数据一致性要求高

问题分析

-- 1. 查看死锁日志
SHOW ENGINE INNODB STATUS\G
-- 发现交叉更新死锁

-- 2. 查看长事务
SELECT * FROM information_schema.INNODB_TRX 
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 10;
-- 发现事务持有时间过长

-- 3. 查看热点账户
SELECT account_id, COUNT(*) AS update_count 
FROM balance_update_log 
WHERE update_time > NOW() - INTERVAL 1 HOUR
GROUP BY account_id 
ORDER BY update_count DESC LIMIT 10;
-- 发现热点账户（更新频繁）

优化方案

1. 死锁优化

// 优化前：可能死锁
@Transactional
public void transfer(Long fromId, Long toId, BigDecimal amount) {
    accountMapper.updateBalance(fromId, amount.negate());
    accountMapper.updateBalance(toId, amount);
}

// 优化后：固定顺序
@Transactional
public void transfer(Long fromId, Long toId, BigDecimal amount) {
    Long first = Math.min(fromId, toId);
    Long second = Math.max(fromId, toId);
    
    accountMapper.updateBalance(first, first.equals(fromId) ? amount.negate() : amount);
    accountMapper.updateBalance(second, second.equals(fromId) ? amount.negate() : amount);
}

2. 热点账户优化

-- 方案 1：队列化串行处理
-- 同一账户的更新进入同一队列

-- 方案 2：Redis 预扣减
// 余额扣减先在 Redis 执行
redis.decrBy("balance:" + accountId, amount);
// 异步同步到 MySQL

3. 事务优化

// 优化前：长事务
@Transactional
public void processTransfer(Transfer transfer) {
    accountMapper.updateBalance(...);  // 更新余额
    transferMapper.insert(transfer);   // 记录流水
    messageService.sendNotification(); // 发送通知（耗时）
    logService.logTransfer(transfer);  // 写日志（耗时）
}

// 优化后：短事务
@Transactional
public void processTransfer(Transfer transfer) {
    accountMapper.updateBalance(...);
    transferMapper.insert(transfer);
}
// 事务外执行耗时操作
messageService.sendNotification();
logService.logTransfer(transfer);

优化效果

优化前：
- 死锁次数：50/天
- 转账延迟：P99 = 200ms
- 事务平均时间：500ms

优化后：
- 死锁次数：0/天
- 转账延迟：P99 = 20ms
- 事务平均时间：50ms

案例 3：日志系统优化

背景

场景：应用日志存储和查询
数据量：日志表 10 亿行，每天新增 5000 万
并发：查询 1000 QPS
问题：
- 历史数据查询慢
- 删除旧数据影响性能
- 存储成本高

优化方案

1. 分区表优化

-- 按时间分区（每月一个分区）
CREATE TABLE app_logs (
    id BIGINT NOT NULL AUTO_INCREMENT,
    app_id INT NOT NULL,
    log_level VARCHAR(10),
    message TEXT,
    create_time DATETIME NOT NULL,
    PRIMARY KEY (id, create_time)
)
PARTITION BY RANGE (TO_DAYS(create_time)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    ...
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 删除旧数据（瞬间完成）
ALTER TABLE app_logs DROP PARTITION p202301;

2. 归档优化

-- 历史数据归档到冷存储
CREATE TABLE app_logs_archive LIKE app_logs;

-- 移动数据
INSERT INTO app_logs_archive 
SELECT * FROM app_logs 
WHERE create_time < '2024-01-01';

-- 删除热数据
DELETE FROM app_logs WHERE create_time < '2024-01-01' LIMIT 10000;
-- 分批删除

3. 查询优化

-- 查询时指定分区（分区裁剪）
SELECT * FROM app_logs PARTITION (p202403, p202404) 
WHERE create_time >= '2024-03-01';

-- 覆盖索引
CREATE INDEX idx_app_time ON app_logs(app_id, create_time DESC);
SELECT id, app_id, log_level, create_time FROM app_logs 
WHERE app_id = 1 AND create_time > '2024-01-01';

4. 搜索引擎优化

架构调整：
MySQL → 存储最近 7 天日志（热数据）
Elasticsearch → 存储全部日志（查询）

流程：
1. 日志写入 MySQL
2. 异步同步到 ES
3. 查询走 ES
4. 7 天后数据从 MySQL 归档

优化效果

优化前：
- 查询延迟：P99 = 5s
- 删除数据：30 分钟
- 存储：单表 10 亿行

优化后：
- 查询延迟：P99 = 200ms（ES）
- 删除数据：1 秒（删除分区）
- 存储：MySQL 7 天 + ES 全量

案例 4：用户中心优化

背景

场景：用户信息系统
数据量：用户表 5000 万行
并发：读 5 万 QPS，写 5000 QPS
问题：
- 用户查询慢
- 数据库连接不足
- 缓存命中率低

优化方案

1. 缓存优化

// 多级缓存架构
public class UserCache {
    
    // L1: 本地缓存（Caffeine）
    private Cache<Long, User> localCache = Caffeine.newBuilder()
        .maximumSize(10000)
        .expireAfterWrite(1, TimeUnit.MINUTES)
        .build();
    
    // L2: 分布式缓存（Redis）
    @Autowired
    private RedisTemplate<String, User> redisTemplate;
    
    public User getUser(Long userId) {
        // L1 缓存
        User user = localCache.getIfPresent(userId);
        if (user != null) {
            return user;
        }
        
        // L2 缓存
        String key = "user:" + userId;
        user = redisTemplate.opsForValue().get(key);
        if (user != null) {
            localCache.put(userId, user);  // 回写 L1
            return user;
        }
        
        // 查询数据库
        user = userMapper.selectById(userId);
        if (user != null) {
            redisTemplate.opsForValue().set(key, user, 30, TimeUnit.MINUTES);
            localCache.put(userId, user);
        }
        return user;
    }
}

2. 批量查询优化

// 优化前：N+1 查询
for (Long userId : userIds) {
    User user = userService.getUser(userId);
}

// 优化后：批量查询
List<User> users = userService.batchGetUsers(userIds);

3. 连接池优化

# HikariCP 配置
spring.datasource.hikari:
  maximum-pool-size: 50
  minimum-idle: 20
  connection-timeout: 30000
  idle-timeout: 600000
  max-lifetime: 1800000
  connection-test-query: SELECT 1

优化效果

优化前：
- 查询延迟：P99 = 100ms
- 缓存命中率：60%
- 数据库连接：200

优化后：
- 查询延迟：P99 = 10ms（L1 命中）
- 缓存命中率：95%
- 数据库连接：50

调优检查清单

SQL 优化

[ ] 使用 EXPLAIN 分析执行计划
[ ] 确保 WHERE 条件使用索引
[ ] 避免 SELECT *
[ ] 避免深度分页
[ ] 避免大事务
[ ] 使用批量操作

配置优化

[ ] innodb_buffer_pool_size = 物理内存的 70%
[ ] innodb_log_file_size 适当调大
[ ] max_connections 根据业务设置
[ ] 开启慢查询日志
[ ] 开启 performance_schema

架构优化

[ ] 读写分离
[ ] 分库分表（数据量大时）
[ ] 缓存热点数据
[ ] 异步处理非核心业务
[ ] 监控告警完善

总结

调优思路

1. 数据驱动：通过监控和日志定位问题
2. 先易后难：先 SQL 优化，再配置调整，最后架构改造
3. 逐步验证：小步快跑，每次只改一个变量
4. 持续优化：定期 review，持续改进

参考资料

• MySQL 官方文档 - 优化
• 《高性能 MySQL》
• 阿里数据库性能优化最佳实践