MySQL 商业场景训练营

这页不是“博客实战”，而是把 MySQL 的核心知识变成可验证训练。你学完 MySQL，如果只会背“索引、事务、MVCC、redo log”，还不算能上项目。真正能用，是看到订单、支付、库存、采集、大表、主从延迟、死锁这类场景时，能设计表、写 SQL、看执行计划、解释原理、排查问题。

训练目标：每个场景都要做到“能跑 SQL、能解释为什么、能指出不这样会怎样、能说出线上排查路径、能回答面试追问”。

学习前准备

建议用 MySQL 8.x 练习。如果公司仍用 MySQL 5.7，也可以练，但要注意：

能力	MySQL 5.7	MySQL 8.x
窗口函数	不支持	支持
CTE	不支持	支持
`EXPLAIN ANALYZE`	不支持	支持
隐藏索引 invisible index	不支持	支持
默认字符集	常见配置不一定是 utf8mb4	默认更偏 utf8mb4
直方图统计	能力弱	更完善

如果是面试，MySQL 5.7 和 8.x 的区别要能讲清；如果是生产，必须确认版本，因为 SQL 写法和优化工具会不同。

训练总流程

mermaid

flowchart TD
    A["建立业务表"] --> B["插入测试数据"]
    B --> C["写高频 SQL"]
    C --> D["查看 EXPLAIN"]
    D --> E["判断是否命中索引"]
    E --> F["模拟并发事务"]
    F --> G["观察锁等待或死锁"]
    G --> H["解释 undo / redo / binlog"]
    H --> I["总结面试回答"]

每个训练都不要只停在“SQL 能跑”。你必须追问：

这个表为什么这样设计？
哪个约束保证数据不会错？
哪个索引服务哪个查询？
执行计划是否符合预期？
并发时会不会超卖、重复处理、死锁？
宕机后靠什么恢复？
数据量大后会慢在哪里？

训练一：订单支付表设计

场景

用户下单后生成订单，支付平台回调时更新支付状态。这个场景考察表设计、唯一约束、状态流转、联合索引和幂等。

建表 Demo

sql

drop table if exists payment_order;

create table payment_order (
  id bigint primary key auto_increment,
  order_no varchar(64) not null,
  user_id bigint not null,
  pay_channel varchar(32) not null,
  status tinyint not null,
  amount decimal(12, 2) not null,
  paid_at datetime null,
  created_at datetime not null default current_timestamp,
  updated_at datetime not null default current_timestamp on update current_timestamp,
  unique key uk_order_no (order_no),
  key idx_user_status_created (user_id, status, created_at)
) engine = InnoDB default charset = utf8mb4;

插入数据：

sql

insert into payment_order(order_no, user_id, pay_channel, status, amount)
values
('P202607060001', 1001, 'ALIPAY', 0, 99.90),
('P202607060002', 1001, 'WECHAT', 1, 199.00),
('P202607060003', 1002, 'ALIPAY', 0, 59.00);

查询用户待支付订单：

sql

explain
select id, order_no, amount, created_at
from payment_order
where user_id = 1001
  and status = 0
order by created_at desc
limit 20;

原理解释

idx_user_status_created(user_id, status, created_at) 的顺序不是随便排的。查询先用 user_id 缩小到某个用户，再用 status 缩小到待支付，最后按 created_at 排序取最近 20 条。联合索引按组合 key 有序，只有前面的列条件能形成连续范围，后面的排序才更可能被利用。

如果只建三个单列索引：

sql

create index idx_user on payment_order(user_id);
create index idx_status on payment_order(status);
create index idx_created on payment_order(created_at);

优化器通常不能像你想象的那样“完美合并三棵索引”。即使能 index merge，也可能比一个合适的联合索引更慢，因为它要合并结果、回表、再排序。

不这样会怎样

错误做法	后果
没有 `uk_order_no`	重复回调或重试可能插入重复订单
金额用 `double`	金额计算可能出现精度误差
只建 `user_id` 单列索引	用户订单很多时仍要扫描大量记录
查询 `select *`	回表和网络传输变重
没有状态流转约束	已支付订单可能被重复支付

训练二：支付回调幂等更新

场景

支付平台可能重复回调。业务必须保证同一订单只从“未支付”变成“已支付”一次。

正确写法

sql

update payment_order
set status = 1,
    paid_at = now()
where order_no = 'P202607060001'
  and status = 0;

执行后检查影响行数：

sql

select row_count();

如果返回 1，说明本次完成支付状态更新；如果返回 0，说明订单不存在或已经处理过，业务应按幂等成功或异常流程处理。

为什么状态条件必须放进 SQL

错误做法是：

sql

select status from payment_order where order_no = 'P202607060001';
-- Java 判断 status == 0
update payment_order set status = 1 where order_no = 'P202607060001';

并发回调时，两个线程可能都读到 status = 0，然后都执行后续业务，比如重复发积分、重复写流水。把 status = 0 放进 update where 条件里，数据库会在更新时加锁并重新判断条件，只有一个事务能成功把状态改掉。

流程图

mermaid

flowchart TD
    A["支付回调进入"] --> B["按 order_no and status=0 更新"]
    B --> C{"影响行数"}
    C -- "1" --> D["第一次处理成功"]
    D --> E["写支付流水 / 发事件"]
    C -- "0" --> F["已处理或订单异常"]
    F --> G["按幂等规则返回"]

面试标准回答

text

支付回调必须做幂等，不能先查状态再在应用里判断。常见做法是在 update 条件里带上业务状态，例如 where order_no = ? and status = UNPAID，然后检查影响行数。数据库会对命中的记录加锁并在更新时判断条件，并发回调只有一个能更新成功。配合订单号唯一约束、支付流水唯一约束和事务，可以避免重复处理。

原理跳转：事务、锁机制、InnoDB 更新事务全过程。

训练三：库存扣减防超卖

建表

sql

drop table if exists inventory;

create table inventory (
  sku_id bigint primary key,
  stock int not null,
  updated_at datetime not null default current_timestamp on update current_timestamp
) engine = InnoDB default charset = utf8mb4;

insert into inventory(sku_id, stock) values (10001, 5);

正确扣减

sql

update inventory
set stock = stock - 1
where sku_id = 10001
  and stock > 0;

检查影响行数：

sql

select row_count();

原理

这条 SQL 的关键是 stock > 0 在数据库里判断，而不是先查出库存再在 Java 里减。InnoDB 会根据主键定位记录，对记录加排他锁，然后判断条件和执行更新。并发请求排队执行，每次更新都基于数据库当前值判断，因此不会把库存扣成负数。

如果写成：

java

// 错误示意
int stock = selectStock(skuId);
if (stock > 0) {
    updateStock(skuId, stock - 1);
}

两个线程都可能读到 1，然后都写回 0，业务以为卖出两件，但库存只扣了一件，这就是典型并发错误。

事务边界

如果扣库存后还要写流水，应该放在一个事务里：

sql

start transaction;

update inventory
set stock = stock - 1
where sku_id = 10001
  and stock > 0;

insert into inventory_flow(sku_id, change_count, reason, created_at)
values (10001, -1, 'ORDER_PAY', now());

commit;

真实项目里还要检查第一条 update 的影响行数。影响行数为 0 时，不应该写扣减流水。

训练四：覆盖索引仍然慢

场景

面试常问：单表数据量很大，明明走了覆盖索引，为什么还是慢？

准备表：

sql

create table asset_event (
  id bigint primary key auto_increment,
  hospital_id bigint not null,
  event_type varchar(32) not null,
  created_at datetime not null,
  asset_no varchar(64) not null,
  detail varchar(512) not null,
  key idx_hospital_type_time_asset (hospital_id, event_type, created_at, asset_no)
) engine = InnoDB default charset = utf8mb4;

查询：

sql

explain
select asset_no, created_at
from asset_event
where hospital_id = 1001
  and event_type = 'OFFLINE'
order by created_at desc
limit 100000, 20;

这个查询可能显示 Using index，说明它是覆盖索引，不需要回表。但它仍然可能慢，因为 limit 100000, 20 要先扫描并丢弃前 100000 条索引记录，再取 20 条。覆盖索引减少了回表，但没有减少深分页扫描量。

优化方式

用游标分页：

sql

select asset_no, created_at
from asset_event
where hospital_id = 1001
  and event_type = 'OFFLINE'
  and created_at < '2026-07-06 10:00:00'
order by created_at desc
limit 20;

原理总结

现象	原因
`Using index`	查询字段在索引中，减少回表
仍然慢	扫描范围大，深分页丢弃大量记录
CPU/IO 高	大量索引页扫描、排序或缓存未命中
优化方向	缩小范围、游标分页、冷热归档、分区或拆表

原理跳转：大表覆盖索引仍然慢。

训练五：模拟锁等待

打开两个 MySQL 会话。

会话 A

sql

start transaction;

update inventory
set stock = stock - 1
where sku_id = 10001
  and stock > 0;

-- 暂时不要 commit

会话 B

sql

start transaction;

update inventory
set stock = stock - 1
where sku_id = 10001
  and stock > 0;

会话 B 会等待，因为会话 A 持有同一行的排他锁。

排查 SQL

MySQL 8 可以看：

sql

select * from performance_schema.data_locks;
select * from performance_schema.data_lock_waits;

也可以看：

sql

show engine innodb status\G

原理

InnoDB 行锁不是加在抽象“行对象”上，而是加在索引记录上。sku_id 是主键，更新时能精准定位一条记录，所以锁范围小。如果条件没有索引，可能扫描更多记录，导致锁范围和等待风险变大。

训练六：模拟死锁

准备表：

sql

drop table if exists account_balance;

create table account_balance (
  account_id bigint primary key,
  balance decimal(12, 2) not null
) engine = InnoDB default charset = utf8mb4;

insert into account_balance(account_id, balance)
values (1, 1000.00), (2, 1000.00);

会话 A

sql

start transaction;
update account_balance set balance = balance - 100 where account_id = 1;
-- 等一下，再执行
update account_balance set balance = balance + 100 where account_id = 2;

会话 B

sql

start transaction;
update account_balance set balance = balance - 50 where account_id = 2;
-- 等一下，再执行
update account_balance set balance = balance + 50 where account_id = 1;

两个事务更新顺序相反，可能出现 A 持有账户 1 等账户 2，B 持有账户 2 等账户 1，形成死锁。InnoDB 会检测死锁，回滚其中一个事务。

正确做法

转账类操作要统一加锁顺序，比如永远先锁较小 account_id，再锁较大 account_id。

mermaid

flowchart TD
    A["转账请求"] --> B["比较两个 account_id"]
    B --> C["先更新较小 ID"]
    C --> D["再更新较大 ID"]
    D --> E["写转账流水"]
    E --> F["提交事务"]

面试标准回答

text

死锁通常是多个事务以不同顺序持有和等待锁。比如转账时事务 A 先锁账户 1 再锁账户 2，事务 B 先锁账户 2 再锁账户 1，就可能互相等待。解决思路是统一资源访问顺序、缩短事务、让条件命中索引、减少范围更新，并通过 show engine innodb status 或 performance_schema 查看死锁日志。

训练七：解释一次 update 的日志链路

执行：

sql

update payment_order
set status = 1,
    paid_at = now()
where order_no = 'P202607060001'
  and status = 0;

内部大致发生：

mermaid

flowchart TD
    A["Server 层收到 update"] --> B["优化器选择 uk_order_no"]
    B --> C["InnoDB 查索引并加排他锁"]
    C --> D["写 undo log 旧版本"]
    D --> E["修改 Buffer Pool 中的数据页"]
    E --> F["写 redo log prepare"]
    F --> G["Server 层写 binlog"]
    G --> H["redo log commit"]
    H --> I["事务提交成功"]

为什么要 undo

如果事务回滚，要能把 status 从 1 改回 0；如果有旧快照读，也可能需要通过 undo 版本链读取旧版本。

为什么要 redo

事务提交后，数据页可能还没刷盘。如果宕机，InnoDB 可以用 redo log 重放已提交修改，保证持久性。

为什么要 binlog

binlog 用于主从复制和时间点恢复。从库通过重放 binlog 或 relay log 得到同样的逻辑变更。

为什么要两阶段提交

如果只写 redo 不写 binlog，主库宕机恢复后有这次修改，但从库没有，主从不一致。如果只写 binlog 不写 redo，主库恢复后没有这次修改，但从库可能有，也不一致。两阶段提交就是为了协调 InnoDB redo 和 Server 层 binlog。

原理跳转：redo log 与 binlog、InnoDB 更新事务全过程。

训练八：主从延迟排查

场景

用户刚支付成功，马上查询订单详情却还是未支付。常见原因是写主库、读从库，而从库有延迟。

mermaid

flowchart TD
    A["主库提交支付成功"] --> B["写 binlog"]
    B --> C["从库 IO 线程拉取"]
    C --> D["写 relay log"]
    D --> E["从库 SQL 线程重放"]
    E --> F["从库可读到新状态"]

排查

sql

show replica status\G

旧版本也可能是：

sql

show slave status\G

重点看：

字段	含义
`Seconds_Behind_Source`	从库落后主库的大致秒数
`Replica_IO_Running`	IO 线程是否正常拉取日志
`Replica_SQL_Running`	SQL 线程是否正常重放日志
`Relay_Log_File`	当前 relay log 文件
`Last_SQL_Error`	SQL 线程错误

处理策略

场景	策略
写后立刻读	读主库或短时间强制读主
大事务导致延迟	拆小事务，避免一次改太多
从库跑报表	报表隔离到专门实例
从库机器弱	升级从库规格
高并发复制压力	开启并行复制并评估表设计

最终验收清单

完成这页训练后，你应该能回答：

为什么订单号要唯一约束？
为什么支付回调用 where status = 0 做幂等？
为什么库存扣减要检查影响行数？
为什么联合索引顺序会影响查询？
为什么 Using index 仍可能慢？
行锁为什么说加在索引上？
死锁如何产生，怎么减少？
update 为什么会同时涉及 undo、redo、binlog？
redo 和 binlog 为什么需要两阶段提交？
主从延迟为什么会导致写后读不一致？

关联知识点

知识点	入口
MySQL 主线	从零到生产级掌握
SQL 执行	SQL 执行全过程原理
EXPLAIN	执行计划
锁	锁机制
MVCC	MVCC
日志	redo log 与 binlog
大表	大表覆盖索引仍然慢
主从	主从复制
面试	MySQL 面试

JDK 8/11/17/21 LTS演进

线程池

MySQL 商业场景训练营 ​

学习前准备 ​

训练总流程 ​

训练一：订单支付表设计 ​

场景 ​

建表 Demo ​

原理解释 ​

不这样会怎样 ​

训练二：支付回调幂等更新 ​

场景 ​

正确写法 ​

为什么状态条件必须放进 SQL ​

流程图 ​

面试标准回答 ​

训练三：库存扣减防超卖 ​

建表 ​

正确扣减 ​

原理 ​

事务边界 ​

训练四：覆盖索引仍然慢 ​

场景 ​

优化方式 ​

原理总结 ​

训练五：模拟锁等待 ​

会话 A ​

会话 B ​

排查 SQL ​

原理 ​

训练六：模拟死锁 ​

会话 A ​

会话 B ​

正确做法 ​

面试标准回答 ​

训练七：解释一次 update 的日志链路 ​

为什么要 undo ​

为什么要 redo ​

为什么要 binlog ​

为什么要两阶段提交 ​

训练八：主从延迟排查 ​

场景 ​

排查 ​

处理策略 ​

最终验收清单 ​

关联知识点 ​

MySQL 商业场景训练营

学习前准备

训练总流程

训练一：订单支付表设计

场景

建表 Demo

原理解释

不这样会怎样

训练二：支付回调幂等更新

场景

正确写法

为什么状态条件必须放进 SQL

流程图

面试标准回答

训练三：库存扣减防超卖

建表

正确扣减

原理

事务边界

训练四：覆盖索引仍然慢

场景

优化方式

原理总结

训练五：模拟锁等待

会话 A

会话 B

排查 SQL

原理

训练六：模拟死锁

会话 A

会话 B

正确做法

面试标准回答

训练七：解释一次 update 的日志链路

为什么要 undo

为什么要 redo

为什么要 binlog

为什么要两阶段提交

训练八：主从延迟排查

场景

排查

处理策略

最终验收清单

关联知识点