殷龙飞的博客

MySQL 硬核解析专题 - 第五篇：实战案例

 数据库/MySQL  1684  4 分钟

好的，以下是“MySQL 硬核解析专题”的第五篇完整内容，主题是 实战案例。这一篇将通过具体的场景和解决方案，把前四章的知识串起来，带你从问题分析到优化落地，体验 MySQL 的硬核实战过程。内容会贴近真实案例，既有技术深度，又有操作细节。让我们开始吧！

MySQL 硬核解析专题 - 第五篇：实战案例

前四章我们聊了 MySQL 的架构、SQL 执行、性能优化和高可用分布式，现在是时候把这些“招数”用起来了。这一篇，我会带你走进两个真实场景：一张 1 亿行的表查询慢到崩溃，高并发下事务死锁频发。通过分析问题、制定方案、落地优化，看看 MySQL 怎么从“瘸腿”变“飞奔”。

案例一：一张表 1 亿行，查询慢到怀疑人生

场景：某电商平台的 orders 表，数据量 1 亿行，字段包括 order_id（主键）、user_id、order_time、amount 等。业务跑了个查询：

1

SELECT * FROM orders WHERE order_time > '2025-01-01' ORDER BY order_time LIMIT 10;

结果耗时 20 秒，页面卡死，用户投诉不断。

  1. 问题分析

    • 执行计划：跑 EXPLAIN：
      type: ALL
rows: 100000000
key: NULL
Extra: Using filesort
      全表扫描 1 亿行，没用索引，排序还用文件临时表，性能崩了。
    • 表结构：SHOW CREATE TABLE orders; 显示只有主键索引，order_time 没索引。
    • 硬件：缓冲池 innodb_buffer_pool_size=1GB，内存不够，频繁读磁盘。

  2. 优化方案

    • 加索引：针对 order_time 建索引。
      1

      CREATE INDEX idx_time ON orders(order_time);
    • 改 SQL：用覆盖索引，减少回表。
      1
2
3

      SELECT order_id, order_time FROM orders 
WHERE order_time > '2025-01-01' 
ORDER BY order_time LIMIT 10;
    • 分表：数据量太大，考虑按时间分片，比如 orders_2024、orders_2025。
    • 调配置：缓冲池调到 8GB（假设机器 16GB 内存）。
      1

      innodb_buffer_pool_size=8G

  3. 落地执行

    • 先加索引，跑 EXPLAIN，结果：
      type: range
rows: 500000
key: idx_time
Extra: Using index
      查询降到 2 秒。
    • 改 SQL 后，用覆盖索引，耗时缩到 0.3 秒。
    • 分表后，每表 2000 万行，查询稳定在 0.1 秒。
    • 调缓冲池，重启 MySQL，SHOW STATUS LIKE 'Innodb_buffer_pool_reads'; 明显减少。

  4. 硬核点

    • 分表路由：应用层用 order_time 年份判断走哪张表，或者用 MyCat 自动分片。
    • 归档：历史数据移到冷表（如 orders_history），用分区表实现：
      1
2
3
4

      ALTER TABLE orders PARTITION BY RANGE (YEAR(order_time)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

  5. 结果：查询从 20 秒优化到 0.1 秒，页面秒开，用户体验起飞。

案例二：高并发下事务死锁，业务频频中断

场景：某支付系统，accounts 表存账户余额，高峰期每秒 5000 次并发更新。业务逻辑是转账：

1
2
3
4

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

结果经常报 Deadlock found when trying to get lock，服务宕机。

  1. 问题分析

    • 死锁日志：跑 SHOW ENGINE INNODB STATUS\G，看到：
      TRANSACTION 1: UPDATE accounts ... WHERE account_id = 1 (持有行锁)
TRANSACTION 2: UPDATE accounts ... WHERE account_id = 2 (持有行锁)
TRANSACTION 1: 请求 account_id = 2 的锁
TRANSACTION 2: 请求 account_id = 1 的锁
      两个事务互相等待对方释放锁，形成死锁。
    • 锁粒度：InnoDB 默认行锁，但更新顺序不一致导致冲突。
    • 并发压力：5000 QPS 下，锁竞争加剧。

  2. 优化方案

    • 固定更新顺序：按 account_id 升序更新，避免交叉等待。
      1
2
3
4

      START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
      改成：
      1
2
3
4

      START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = LEAST(1, 2);
UPDATE accounts SET balance = balance + 100 WHERE account_id = GREATEST(1, 2);
COMMIT;
    • 缩短事务：减少锁持有时间，把无关逻辑移出事务。
    • 加索引：确保 account_id 有唯一索引（默认主键已覆盖）。
    • 降并发：用队列（如 Redis）缓冲写请求，平滑数据库压力。

  3. 落地执行

    • 改代码，按 account_id 排序更新，死锁率降 80%。
    • 加队列，高峰期 QPS 控制在 2000，死锁几乎消失。
    • 检查锁状态：SELECT * FROM information_schema.INNODB_TRX; 监控事务，锁等待大幅减少。

  4. 硬核点

    • 锁超时：设置 innodb_lock_wait_timeout=5（默认 50 秒），快速失败重试。
    • 悲观锁：用 SELECT ... FOR UPDATE 显式加锁：
      1
2
3
4
5

      START TRANSACTION;
SELECT * FROM accounts WHERE account_id IN (1, 2) ORDER BY account_id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
    • 乐观锁：加版本字段 version，更新时校验：
      1
2

      UPDATE accounts SET balance = balance - 100, version = version + 1 
WHERE account_id = 1 AND version = 老版本;

  5. 结果：死锁从每分钟 10 次降到 0，系统稳定运行，QPS 提升到 6000。

三、实战总结

  • 大表优化：索引、分表、配置三管齐下，化整为零。
  • 死锁处理：规范顺序、缩短事务、降并发，防患未然。
  • 工具利器：EXPLAIN、SHOW ENGINE INNODB STATUS、慢查询日志是排查神器。

结语

这一篇通过两个案例，把 MySQL 的核心知识点落地实战，从慢查询到死锁，展示了优化全流程。希望你能从中摸到“硬核”调优的门道。

updatedupdated2025-03-312025-03-31

相关文章：