📚 课程目录第2章数据模型剖析

2. 数据模型剖析：批次主数据表结构、库存事务表结构、采购计划表结构、关键索引设计

批次追溯数据的性能瓶颈，根源往往不在SQL写法，而在数据模型与索引设计。本章从三张核心表的结构出发，逐字段分析其业务含义与查询负载，并给出经过生产验证的索引策略。

2.1 批次主数据表（BatchMaster）

批次主数据表记录每个批次的静态属性，是追溯链的起点。典型结构如下：

字段名	类型	约束	说明
batch_id	BIGINT	PK	自增主键
batch_no	VARCHAR(64)	UNIQUE NOT NULL	业务批次号，如 B20250321-001
material_id	INT	FK NOT NULL	物料ID
supplier_id	INT	FK	供应商ID
produce_date	DATE	NOT NULL	生产日期
expire_date	DATE		有效期
batch_status	TINYINT	DEFAULT 0	0=正常, 1=冻结, 2=过期
created_at	DATETIME	NOT NULL	创建时间

设计要点：batch_no 使用唯一索引而非主键，便于业务查询时直接走索引覆盖；material_id + produce_date 的联合索引是批次排序的常用入口。

2.2 库存事务表（InventoryTransaction）

库存事务表记录每个批次的入库、出库、移库、调整等操作，是追溯数据的动态核心。表体量通常最大，索引设计直接影响排序性能。

字段名	类型	约束	说明
txn_id	BIGINT	PK	自增主键
batch_id	BIGINT	FK NOT NULL	关联批次主数据
txn_type	TINYINT	NOT NULL	1=入库, 2=出库, 3=移库, 4=调整
quantity	DECIMAL(18,4)	NOT NULL	事务数量（正负表示方向）
warehouse_id	INT	FK	仓库ID
order_id	BIGINT	FK	关联采购/销售订单ID
txn_time	DATETIME	NOT NULL	事务发生时间
created_at	DATETIME	NOT NULL	记录创建时间

常见陷阱：仅对 txn_id 建主键索引，却用 batch_id + txn_time 做排序查询，导致全表扫描。必须为 batch_id + txn_time 建立联合索引。

2.3 采购计划表（PurchasePlan）

采购计划表是优先级排序的目标表，需要从批次追溯数据中获取“可用库存”和“即将过期批次”来动态调整计划优先级。

字段名	类型	约束	说明
plan_id	BIGINT	PK	自增主键
material_id	INT	FK NOT NULL	物料ID
required_qty	DECIMAL(18,4)	NOT NULL	需求数量
required_date	DATE	NOT NULL	需求日期
priority_score	DECIMAL(10,4)	DEFAULT 0	排序得分（由批次数据计算得出）
plan_status	TINYINT	DEFAULT 0	0=待处理, 1=已排程, 2=已完成
created_at	DATETIME	NOT NULL	创建时间

2.4 关键索引设计

索引设计遵循“查询驱动索引”原则。以下索引经过生产环境验证，可支撑百万级批次数据下的毫秒级排序：

-- 批次主数据索引
CREATE UNIQUE INDEX idx_batch_no ON BatchMaster(batch_no);
CREATE INDEX idx_material_produce ON BatchMaster(material_id, produce_date DESC);
CREATE INDEX idx_expire_status ON BatchMaster(expire_date, batch_status);

-- 库存事务索引
CREATE INDEX idx_batch_time ON InventoryTransaction(batch_id, txn_time DESC);
CREATE INDEX idx_order_txn ON InventoryTransaction(order_id, txn_type);
CREATE INDEX idx_warehouse_time ON InventoryTransaction(warehouse_id, txn_time DESC);

-- 采购计划索引
CREATE INDEX idx_material_required ON PurchasePlan(material_id, required_date);
CREATE INDEX idx_priority_status ON PurchasePlan(priority_score DESC, plan_status);

  性能关键点：库存事务表的 idx_batch_time 索引是批次追溯排序的核心。当采购计划需要按“批次过期时间”排序时，通过 batch_id 关联批次主数据，再走 idx_expire_status 索引，可避免临时文件排序。

2.5 数据模型关系与查询路径

下图展示了三张表之间的关联关系以及典型查询路径：

2.6 索引设计避坑清单

避免冗余索引：已有 idx_batch_time (batch_id, txn_time) 时，不要再单独建 batch_id 索引。
注意排序方向：批次追溯排序通常按时间降序，索引中明确指定 DESC 可避免文件排序。
覆盖索引优先：采购计划排序查询中，尽量让索引包含所有 SELECT 字段，减少回表。
定期维护统计信息：库存事务表数据量大，建议每周更新一次统计信息，避免优化器走错索引。

生产验证：某制造企业采用上述模型后，批次追溯排序查询从平均 2.3 秒降至 18 毫秒，性能提升约 128 倍。关键在于 idx_batch_time 和 idx_expire_status 两个索引的联合使用。