关系型数据库商业场景训练营

这一页不是“再列一堆数据库名词”，而是把数据库知识变成能一步步练出来的能力。目标是：零基础读者照着做，能从建表、写 SQL、加索引、看执行计划、处理事务、理解日志、排查慢查询，逐步走到生产项目能用。

真正学会数据库，不是看懂一篇概念文，而是能面对一个业务场景，设计表、写查询、解释为什么快或慢、保证并发正确、知道宕机后为什么能恢复，并能把 MySQL、PostgreSQL、Oracle、SQL Server 的差异讲清楚。

训练目标

完成本页训练后，你要能做到：

根据订单、支付、库存、医疗资产采集这些商业场景独立建模。
写出可运行的建表、插入、查询、更新、事务 SQL。
解释主键、唯一约束、非空、check、外键为什么不是“可有可无”。
解释联合索引为什么要按业务查询顺序设计。
看懂执行计划里的访问路径、扫描行数、回表、排序、Join。
解释为什么覆盖索引仍然可能慢。
解释 MVCC、锁、undo、redo、WAL、Transaction Log 与业务正确性的关系。
能把同一个业务分别映射到 MySQL、PostgreSQL、Oracle、SQL Server。
能按标准流程排查慢 SQL、锁等待、长事务、日志暴涨和复制延迟。

学习方式

不要只读。每个训练都按下面顺序做：

mermaid

flowchart TD
    A["读懂业务问题"] --> B["画出实体关系"]
    B --> C["写建表 SQL"]
    C --> D["插入测试数据"]
    D --> E["写业务查询"]
    E --> F["查看执行计划"]
    F --> G["解释为什么这样执行"]
    G --> H["优化索引或 SQL"]
    H --> I["模拟并发或异常"]
    I --> J["写面试回答"]

这条路线的意义是：先理解业务，再设计结构；先让 SQL 跑起来，再看计划；先知道问题怎么发生，再谈优化和排查。

训练一：订单支付系统建模

业务目标

订单支付系统最核心的问题不是“存几张表”，而是保证：

订单号不能重复。
支付单号不能重复。
支付回调可能重复，但订单只能成功一次。
支付状态和订单状态不能不一致。
用户订单列表要能按状态和时间快速查询。
对账时要能追踪每一次支付流水。

表关系

mermaid

flowchart TD
    A["order_main<br/>订单主表"] --> B["order_item<br/>订单明细"]
    A --> C["payment_order<br/>支付单"]
    C --> D["payment_log<br/>支付流水"]
    A --> E["order_status_log<br/>订单状态流水"]

MySQL Demo

sql

create table order_main (
  id bigint primary key auto_increment,
  order_no varchar(64) not null,
  user_id bigint not null,
  status varchar(20) not null,
  total_amount decimal(12, 2) not null,
  created_at datetime not null,
  updated_at datetime not null,
  unique key uk_order_no (order_no),
  key idx_user_status_created (user_id, status, created_at)
) engine = InnoDB default charset = utf8mb4;

create table payment_order (
  id bigint primary key auto_increment,
  pay_no varchar(64) not null,
  order_no varchar(64) not null,
  pay_channel varchar(32) not null,
  status varchar(20) not null,
  amount decimal(12, 2) not null,
  paid_at datetime null,
  created_at datetime not null,
  updated_at datetime not null,
  unique key uk_pay_no (pay_no),
  unique key uk_order_no (order_no),
  key idx_status_created (status, created_at)
) engine = InnoDB default charset = utf8mb4;

为什么要这样设计：

设计	原因	不这样会怎样
`order_no` 唯一	防止重复订单	并发创建或重试时生成重复订单
`pay_no` 唯一	防止重复支付单	对账时一笔支付对应多条记录
`payment_order.order_no` 唯一	一个订单只对应一个当前支付单	多次回调可能让状态混乱
金额用 `decimal`	避免浮点误差	金额出现 0.3000000004 这类问题
状态字段	支持状态机和幂等	不知道订单当前能不能继续处理
联合索引	服务用户订单列表	大用户订单列表扫描过多

支付回调事务

支付回调必须幂等。不能只靠 Java 先查状态再更新，因为并发回调可能同时看到“未支付”。

sql

begin;

update payment_order
set status = 'SUCCESS',
    paid_at = now(),
    updated_at = now()
where pay_no = 'P202607060001'
  and status = 'WAIT_PAY';

update order_main
set status = 'PAID',
    updated_at = now()
where order_no = 'O202607060001'
  and status = 'WAIT_PAY';

insert into payment_log(pay_no, event_type, content, created_at)
values ('P202607060001', 'CALLBACK', 'pay success', now());

commit;

真实项目里要检查 update payment_order 和 update order_main 的影响行数。如果影响行数是 0，说明可能已经处理过，应该按幂等成功或异常状态处理。

mermaid

flowchart TD
    A["收到支付回调"] --> B["开启事务"]
    B --> C["按 pay_no + WAIT_PAY 更新支付单"]
    C --> D{"影响行数是否为 1"}
    D -- "否" --> E["判断是否重复回调"]
    D -- "是" --> F["更新订单 WAIT_PAY -> PAID"]
    F --> G["写支付流水"]
    G --> H["提交事务"]
    E --> I["返回幂等成功或告警"]

为什么状态条件要放进 SQL：

数据库的 update ... where status = 'WAIT_PAY' 是原子判断和修改。
并发回调时，只有一个事务能把状态从 WAIT_PAY 改成 SUCCESS。
另一个事务再执行时影响行数是 0，可以识别为重复处理。
如果先查再改，中间存在并发窗口。

训练二：用户订单列表索引优化

查询需求

sql

select id, order_no, status, total_amount, created_at
from order_main
where user_id = 1001
  and status = 'PAID'
order by created_at desc
limit 20;

合理索引

sql

create index idx_user_status_created
on order_main(user_id, status, created_at);

为什么是这个顺序：

索引列	作用
`user_id`	先缩小到某个用户
`status`	再缩小到某种状态
`created_at`	支持按时间范围扫描和排序

如果索引是 (status, created_at, user_id)，对于一个大状态值，例如 PAID 占全表 80%，数据库可能先扫描大量已支付订单，再过滤用户，效率更差。

执行计划要看什么

sql

explain
select id, order_no, status, total_amount, created_at
from order_main
where user_id = 1001
  and status = 'PAID'
order by created_at desc
limit 20;

字段	要理解的来源
`possible_keys`	优化器认为理论上可用的索引
`key`	最终选择的索引
`rows`	根据统计信息估算要扫描的行数，不是真实返回行数
`filtered`	扫描后预计剩余比例
`Extra`	是否出现 `Using filesort`、`Using temporary`、`Using index`

如果 rows 很大，说明索引定位范围仍然大；如果出现 Using filesort，说明排序没有被索引顺序很好地利用；如果 key 为空，说明优化器没有选择索引，可能是统计信息、选择性、函数包列或条件写法问题。

覆盖索引仍然慢的训练

假设你建了覆盖索引：

sql

create index idx_status_created_cover
on order_main(status, created_at, user_id, order_no, total_amount);

下面这个 SQL 仍然可能慢：

sql

select user_id, order_no, total_amount
from order_main
where status = 'PAID'
order by created_at desc
limit 100000, 20;

原因：

status = 'PAID' 范围太大。
覆盖索引只是不回表，不代表不用扫描。
limit 100000, 20 要跳过大量索引项。
扫描大量索引叶子页会消耗 CPU、缓存和 IO。

改成 Seek 分页：

sql

select user_id, order_no, total_amount
from order_main
where status = 'PAID'
  and created_at < '2026-07-06 10:00:00'
order by created_at desc
limit 20;

这不是语法技巧，而是把“跳过前 100000 条”改成“从上一页最后一个时间点继续向后扫”，扫描范围更小。

训练三：医疗资产采集平台建模

业务目标

医疗资产采集平台要解决：

每个资产编号唯一。
资产属于医院和科室。
采集任务不能重复执行。
采集结果要能按任务、时间、状态查询。
异常资产要能快速检索。
大量历史采集结果要能归档或同步到 ES。

表关系

mermaid

flowchart TD
    A["medical_asset<br/>资产主表"] --> B["asset_change_log<br/>资产变更流水"]
    C["collect_task<br/>采集任务"] --> D["collect_result<br/>采集结果"]
    D --> A
    D --> E["collect_error<br/>异常明细"]

PostgreSQL Demo

sql

create schema if not exists biz;

create table biz.medical_asset (
  id bigserial primary key,
  asset_no varchar(64) not null,
  hospital_id bigint not null,
  department_id bigint,
  asset_name varchar(128) not null,
  status varchar(20) not null,
  attrs jsonb not null default '{}',
  created_at timestamp not null default now(),
  updated_at timestamp not null default now(),
  constraint uk_medical_asset_no unique (asset_no),
  constraint ck_asset_status
    check (status in ('IDLE', 'USED', 'REPAIR', 'SCRAPPED'))
);

create index idx_asset_hospital_status_time
on biz.medical_asset(hospital_id, status, updated_at desc);

create index idx_asset_attrs_gin
on biz.medical_asset using gin(attrs);

为什么 PostgreSQL 适合这个训练：

核心字段用关系列，能加约束和 B-tree 索引。
低频扩展字段用 jsonb，可以兼容不同设备属性。
jsonb 查询可以用 GIN 索引。
大量更新后能观察 dead tuple、VACUUM 和膨胀。

JSONB 查询

sql

explain (analyze, buffers)
select id, asset_no, asset_name
from biz.medical_asset
where attrs @> '{"deviceType": "CT"}';

如果没有 GIN 索引，可能全表扫描；有 GIN 索引后，能根据 JSONB 内部键值倒排结构定位候选行。

但不要把所有字段都塞进 JSONB：

错误做法	后果
医院、状态、时间也放 JSONB	高频过滤难以稳定优化
JSONB 存很大的设备详情	更新成本高，行变宽
不加约束	资产编号、状态合法性无法兜底
不看执行计划	以为 GIN 一定快，实际返回行太多仍慢

训练四：库存扣减并发正确性

错误写法

text

1. select stock from inventory where sku_id = 1001;
2. Java 判断 stock > 0。
3. stock = stock - 1。
4. update inventory set stock = 新值 where sku_id = 1001;

并发下两个请求可能都读到 stock = 1，然后都写回 0，造成逻辑上卖出两件但库存只扣一件。

正确核心 SQL

sql

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

然后检查影响行数：

影响行数	含义
1	扣减成功
0	库存不足或商品不存在

mermaid

flowchart TD
    A["扣库存请求"] --> B["执行条件更新"]
    B --> C{"影响行数"}
    C -- "1" --> D["写库存流水"]
    D --> E["提交事务"]
    C -- "0" --> F["返回库存不足"]

为什么这样能防并发问题：

stock > 0 和 stock = stock - 1 在同一条 SQL 内完成。
数据库会对被更新的行加写锁。
同一行的两个更新不能同时无序修改。
后一个事务会在前一个提交后重新判断条件。

训练五：四大数据库同一业务建表差异

同一个资产表，不同数据库写法不同。差异不是背语法，而是要理解自增、时间、字符串、索引和约束的实现不同。

MySQL

sql

create table medical_asset (
  id bigint primary key auto_increment,
  asset_no varchar(64) not null,
  hospital_id bigint not null,
  asset_name varchar(128) not null,
  status varchar(20) not null,
  updated_at datetime not null,
  unique key uk_asset_no (asset_no),
  key idx_hospital_status_time (hospital_id, status, updated_at)
) engine = InnoDB default charset = utf8mb4;

PostgreSQL

sql

create table medical_asset (
  id bigserial primary key,
  asset_no varchar(64) not null,
  hospital_id bigint not null,
  asset_name varchar(128) not null,
  status varchar(20) not null,
  updated_at timestamp not null,
  constraint uk_asset_no unique (asset_no)
);

create index idx_hospital_status_time
on medical_asset(hospital_id, status, updated_at);

Oracle

sql

create table medical_asset (
  id number(19) primary key,
  asset_no varchar2(64) not null,
  hospital_id number(19) not null,
  asset_name varchar2(128) not null,
  status varchar2(20) not null,
  updated_at timestamp not null,
  constraint uk_asset_no unique (asset_no)
);

create sequence seq_medical_asset start with 1 increment by 1;

create index idx_hospital_status_time
on medical_asset(hospital_id, status, updated_at);

SQL Server

sql

create table dbo.MedicalAsset (
  Id bigint identity(1,1) not null,
  AssetNo varchar(64) not null,
  HospitalId bigint not null,
  AssetName nvarchar(128) not null,
  Status varchar(20) not null,
  UpdatedAt datetime2 not null,
  constraint PK_MedicalAsset primary key clustered (Id),
  constraint UX_MedicalAsset_AssetNo unique (AssetNo)
);

create index IX_MedicalAsset_Hospital_Status_Time
on dbo.MedicalAsset(HospitalId, Status, UpdatedAt);

对比要点：

维度	MySQL	PostgreSQL	Oracle	SQL Server
自增	`auto_increment`	`bigserial` 或 identity	sequence 或 identity	`identity`
字符串	`varchar`	`varchar`	`varchar2`	`varchar` / `nvarchar`
时间	`datetime`	`timestamp`	`timestamp` / `date`	`datetime2`
主键组织	InnoDB 聚簇索引	主键索引指向 heap tuple	B-tree + ROWID	聚集主键常见
版本机制	undo 版本链	tuple 多版本	Undo + SCN	锁或 TempDB 行版本

训练六：一条 UPDATE 在不同数据库中的原理

业务 SQL：

sql

update medical_asset
set status = 'USED',
    updated_at = current_timestamp
where asset_no = 'A001'
  and status = 'IDLE';

共同流程：

mermaid

flowchart TD
    A["解析 SQL"] --> B["通过索引定位资产行"]
    B --> C["检查 status 条件"]
    C --> D["获取写锁"]
    D --> E["记录可回滚信息或旧版本"]
    E --> F["修改内存页"]
    F --> G["写事务恢复日志"]
    G --> H["提交事务"]
    H --> I["后台刷数据页"]

差异：

数据库	旧版本/回滚	恢复日志	特别关注
MySQL InnoDB	undo log	redo log + binlog	两阶段提交、主从复制
PostgreSQL	旧 tuple + xmin/xmax	WAL	VACUUM 清理 dead tuple
Oracle	Undo	Redo	SCN、一致性读、Undo 保留
SQL Server	Transaction Log + 可能版本存储	Transaction Log	LDF、锁、RCSI、TempDB

面试时不要只说“都会加锁”。更完整的回答是：更新要先定位行，再保证并发写正确，再保存回滚或旧版本信息，再写恢复日志，提交后通过日志保证宕机可恢复。

训练七：线上慢 SQL 标准排查

慢 SQL 不要凭感觉改。按下面流程：

mermaid

flowchart TD
    A["发现慢 SQL"] --> B["拿到完整 SQL 和参数"]
    B --> C["确认数据量和返回行数"]
    C --> D["查看执行计划"]
    D --> E{"慢在哪里"}
    E -- "扫描多" --> F["索引或条件选择性问题"]
    E -- "回表多" --> G["覆盖索引或减少返回列"]
    E -- "排序慢" --> H["索引顺序或内存不足"]
    E -- "Join 慢" --> I["Join 顺序、驱动表、统计信息"]
    E -- "等待慢" --> J["锁、IO、连接、日志刷盘"]
    F --> K["修改后用真实数据复测"]
    G --> K
    H --> K
    I --> K
    J --> K

不同数据库排查入口：

数据库	常用入口	看什么
MySQL	`EXPLAIN`、慢日志、`performance_schema`	`type`、`rows`、`Extra`、锁等待
PostgreSQL	`EXPLAIN (ANALYZE, BUFFERS)`、`pg_stat_activity`	实际行数、Buffers、dead tuple、锁
Oracle	`DBMS_XPLAN`、AWR/ASH、等待事件	执行计划、等待事件、Undo、Redo
SQL Server	实际执行计划、`STATISTICS IO`、Query Store	逻辑读、Lookup、参数嗅探、等待

训练八：面试回答闭环

面试页可以背标准答案，但真正追问时要跳回知识点页和训练页。

问：为什么有索引还是慢？

标准回答：

text

索引只是提供访问路径，不保证扫描量小。有索引仍然慢，可能是范围太大、低区分度、回表多、深分页、排序不能利用索引、统计信息不准、锁等待、缓存未命中或返回列太多。排查时要看执行计划里的访问方式、扫描行数、回表、排序、临时表和真实耗时，而不是只看有没有 key。

训练跳转：

原理	页面
覆盖索引仍慢	大表覆盖索引优化
执行计划字段来源	MySQL EXPLAIN
关系库共同原理	关系型数据库底层原理

问：MySQL、PG、Oracle、SQL Server 事务日志有什么区别？

标准回答：

text

它们都需要日志保证提交、回滚、恢复和复制。MySQL InnoDB 用 redo 做崩溃恢复，undo 做回滚和 MVCC，binlog 做复制和时间点恢复；PostgreSQL 用 WAL 做崩溃恢复、流复制和归档恢复，旧版本在 tuple 中；Oracle 用 Redo 做恢复和 Data Guard，Undo 做回滚和一致性读，SCN 标记一致性时间点；SQL Server 用 Transaction Log 做回滚、恢复、日志备份和 Always On。名字不同，但核心都是先有可恢复日志，再允许数据页异步落盘。

训练跳转：

原理	页面
四大关系库差异	主流关系库差异与选型
MySQL redo/binlog	redo log 与 binlog
PostgreSQL WAL/VACUUM	MVCC、VACUUM 与 WAL
Oracle Undo/Redo/SCN	Undo、Redo 与 SCN
SQL Server 日志与行版本	索引、锁与行版本原理

最终验收清单

如果你能独立完成下面任务，才算这部分“从零到生产级”基本过关：

任务	合格标准
订单支付建模	能设计订单、支付单、流水表，并解释唯一约束和事务边界
用户订单列表优化	能设计联合索引，并用执行计划证明
医疗资产建模	能设计资产表、采集任务、采集结果，并处理扩展属性
库存扣减	能用条件更新和影响行数防并发超卖
执行计划	能解释扫描行数、回表、排序、Join 和统计信息来源
覆盖索引仍慢	能解释深分页和大范围扫描问题
四大关系库差异	能讲出索引叶子层、MVCC、日志、执行计划差异
线上排查	能按 SQL、索引、统计、锁、IO、日志、连接池逐层定位

关联学习

方向	页面
数据库学习路线	数据库学习路线
从零到精通验收路线	数据库从零到精通验收路线
关系型数据库底层原理	关系型数据库底层原理
主流关系库差异	MySQL、PostgreSQL、Oracle、SQL Server 核心差异
MySQL 主线	MySQL 从零到生产级掌握
PostgreSQL 主线	PostgreSQL 从零到生产级掌握
Oracle 主线	Oracle 从零到生产级掌握
SQL Server 主线	SQL Server 从零到生产级掌握

本章小结

关系型数据库不是“会写 SQL”就算会。真正的学习闭环是：从业务建模开始，用约束守住数据正确性，用索引和执行计划解释查询性能，用事务和锁保证并发正确，用日志解释提交和恢复，用排查流程处理线上问题。MySQL、PostgreSQL、Oracle、SQL Server 名词不同，但都围绕这些问题做工程取舍。你要学的是这条主线，而不是孤立背每个数据库的标准答案。

JDK 8/11/17/21 LTS演进

线程池

关系型数据库商业场景训练营 ​

训练目标 ​

学习方式 ​

训练一：订单支付系统建模 ​

业务目标 ​

表关系 ​

MySQL Demo ​

支付回调事务 ​

训练二：用户订单列表索引优化 ​

查询需求 ​

合理索引 ​

执行计划要看什么 ​

覆盖索引仍然慢的训练 ​

训练三：医疗资产采集平台建模 ​

业务目标 ​

表关系 ​

PostgreSQL Demo ​

JSONB 查询 ​

训练四：库存扣减并发正确性 ​

错误写法 ​

正确核心 SQL ​

训练五：四大数据库同一业务建表差异 ​

MySQL ​

PostgreSQL ​

Oracle ​

SQL Server ​

训练六：一条 UPDATE 在不同数据库中的原理 ​

训练七：线上慢 SQL 标准排查 ​

训练八：面试回答闭环 ​

问：为什么有索引还是慢？ ​

问：MySQL、PG、Oracle、SQL Server 事务日志有什么区别？ ​

最终验收清单 ​

关联学习 ​

本章小结 ​

关系型数据库商业场景训练营

训练目标

学习方式

训练一：订单支付系统建模

业务目标

表关系

MySQL Demo

支付回调事务

训练二：用户订单列表索引优化

查询需求

合理索引

执行计划要看什么

覆盖索引仍然慢的训练

训练三：医疗资产采集平台建模

业务目标

表关系

PostgreSQL Demo

JSONB 查询

训练四：库存扣减并发正确性

错误写法

正确核心 SQL

训练五：四大数据库同一业务建表差异

MySQL

PostgreSQL

Oracle

SQL Server

训练六：一条 UPDATE 在不同数据库中的原理

训练七：线上慢 SQL 标准排查

训练八：面试回答闭环

问：为什么有索引还是慢？

问：MySQL、PG、Oracle、SQL Server 事务日志有什么区别？

最终验收清单

关联学习

本章小结