电商库存核心设计白皮书 —— 基于采购批次的冻结库存模型

版本：V2.0 Final
适用场景：常规电商下单、WMS仓储管理、订单履约、售后逆向物流
核心目标：金融级数据一致性、高并发零超卖、全链路批次追溯、支持复杂售后场景（退货/报损/部分退款）

一、设计理念与核心原则

1.1 设计哲学

“资产账”与“实物账”分离，核心资产（库存）必须实时锁定，非核心旁路（消息、积分）异步解耦。

1.2 三大铁律

1.3 数学恒等式（基石）

总库存（Total）= 可用库存（Available）+ 冻结库存（Frozen）

所有表设计、SQL 操作、对账逻辑都必须始终满足此等式。

二、核心数据模型（表结构设计）

2.1 商品主表（goods）—— 实时汇总层

作用：缓存冗余，避免频繁 SUM 批次表导致性能瓶颈；前端展示库存的快速查询入口。

CREATE TABLE `goods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sku_code` varchar(64) NOT NULL COMMENT '商品编码',
  `total_stock` int(11) NOT NULL DEFAULT 0 COMMENT '物理总库存（所有批次之和）',
  `total_frozen` int(11) NOT NULL DEFAULT 0 COMMENT '总冻结库存（所有批次冻结之和）',
  `version` int(11) DEFAULT 0 COMMENT '乐观锁（可选）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

2.2 采购批次表（goods_batch）—— 资产明细账（核心）

作用：记录每一批货的入库明细，支持过期时间、采购成本、批次类型管理。

CREATE TABLE `goods_batch` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `goods_id` int(11) NOT NULL,
  `batch_no` varchar(32) NOT NULL COMMENT '批次号/入库单号',
  `batch_type` tinyint(4) DEFAULT 1 COMMENT '1-采购批次 2-退货良品批次 3-报损批次',
  `total` int(11) NOT NULL COMMENT '该批次原始总数量',
  `frozen` int(11) NOT NULL DEFAULT 0 COMMENT '该批次被冻结数量',
  `available` int(11) GENERATED ALWAYS AS (total - frozen) STORED COMMENT '可用库存（虚拟列，支持索引）',
  `expire_date` date DEFAULT NULL COMMENT '过期日期（FIFO排序依据）',
  `purchase_price` decimal(10,2) DEFAULT NULL COMMENT '采购成本（财务核算用）',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_goods_expire` (`goods_id`, `expire_date`, `available`)
) ENGINE=InnoDB;

2.3 订单批次锁定明细表（order_batch_lock）—— 链路追踪表

作用：记录每个订单具体锁定了哪些批次的多少货量，支撑部分取消、退款、报损等复杂场景的精准追溯。

CREATE TABLE `order_batch_lock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(32) NOT NULL,
  `item_id` int(11) NOT NULL COMMENT '订单明细ID（支持部分取消/退款）',
  `batch_id` int(11) NOT NULL COMMENT '采购批次ID',
  `qty` int(11) NOT NULL COMMENT '该批次锁定数量',
  `status` tinyint(4) NOT NULL DEFAULT 0 COMMENT '0-已锁定 1-已扣减 2-已释放 3-报损已处理',
  `locked_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_batch` (`order_no`, `batch_id`, `item_id`),
  KEY `idx_batch_status` (`batch_id`, `status`)
) ENGINE=InnoDB;

2.4 订单相关表（简化）

-- 订单主表
CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(32) NOT NULL UNIQUE,
  `user_id` int(11) NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `status` tinyint(4) NOT NULL DEFAULT 0 COMMENT '0-待支付 1-已支付 2-已取消 3-退款中 4-已退款 5-部分退款',
  `pay_time` int(11) DEFAULT 0,
  `create_time` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 订单明细表
CREATE TABLE `order_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(32) NOT NULL,
  `goods_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `status` tinyint(4) DEFAULT 0 COMMENT '0-正常 1-已取消 2-已退款',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

三、库存生命周期全链路状态机

3.1 完整状态流转图

[下单] 
  ↓ (冻结库存 +1)
[待支付] →→→→→→→→→→→→→→→→→→→→→→→→→→→→→→→ [超时/主动取消]
  ↓ (支付回调)                                    ↓ (解冻库存 -1, 可用库存 +1)
[已支付] →→→→→→→→→→→→→→→→→→→→→→→→→→→→→→ [已取消]
  ↓ (物理扣减 total -1, frozen -1, 发货)         (库存回到下单前, 无资产损失)
[履约中]
  ↓ (售后申请)
[退货质检]
  ├── 良品 → [退货良品入库] (新增良品批次 total +1)
  ├── 报损 → [报损处理] (财务冲减, 新增报损批次记录)
  └── 退回供应商 → [逆向物流] (扣减总库存, 生成退货单)

3.2 各阶段详细变化表

四、核心操作 SQL 原子示例

4.1 下单（先进先出锁定批次）

-- 事务内执行
-- 1. 查找最早过期且有库存的批次（FIFO）
SELECT id, (total - frozen) AS available 
FROM goods_batch 
WHERE goods_id = ? AND total - frozen > 0 
ORDER BY expire_date ASC;

-- 2. 逐批锁定（原子条件更新）
UPDATE goods_batch 
SET frozen = frozen + ? 
WHERE id = ? AND (total - frozen) >= ?;

-- 3. 插入锁定明细
INSERT INTO order_batch_lock (order_no, item_id, batch_id, qty, status) 
VALUES (?, ?, ?, ?, 0);

-- 4. 更新主表
UPDATE goods SET total_frozen = total_frozen + ? WHERE id = ?;

4.2 支付回调（物理扣减+解冻）

-- 1. 扣减批次物理库存并解冻
UPDATE goods_batch 
SET total = total - ?, frozen = frozen - ? 
WHERE id = ? AND frozen >= ?;

-- 2. 更新锁定明细状态为已扣减
UPDATE order_batch_lock SET status = 1 WHERE order_no = ? AND batch_id = ?;

-- 3. 同步主表
UPDATE goods SET total_stock = total_stock - ?, total_frozen = total_frozen - ? WHERE id = ?;

4.3 支付前取消（解冻释放）

-- 1. 释放冻结库存
UPDATE goods_batch SET frozen = frozen - ? WHERE id = ? AND frozen >= ?;

-- 2. 更新锁定明细
UPDATE order_batch_lock SET status = 2 WHERE order_no = ?;

-- 3. 同步主表（仅减少冻结）
UPDATE goods SET total_frozen = total_frozen - ? WHERE id = ?;

4.4 售后报损（财务冲减 + 损耗记录）

-- 1. 查找最老批次（FIFO 财务冲减）
SELECT id FROM goods_batch 
WHERE goods_id = ? AND total > 0 
ORDER BY expire_date ASC LIMIT 1 FOR UPDATE;

-- 2. 冲减该批次 total
UPDATE goods_batch SET total = total - ? WHERE id = ? AND total >= ?;

-- 3. 新增报损批次（审计追踪）
INSERT INTO goods_batch (goods_id, batch_no, batch_type, total, frozen) 
VALUES (?, 'LOSS_', 3, ?, 0);

-- 4. 更新主表（净减少）
UPDATE goods SET total_stock = total_stock - ? WHERE id = ?;

-- 5. 更新锁定明细状态
UPDATE order_batch_lock SET status = 3 WHERE order_no = ? AND batch_id = ?;

五、并发安全与防超卖机制

5.1 MySQL 行锁原子操作

-- ✅ 正确的写法（利用 affected_rows 判断）
UPDATE goods_batch 
SET frozen = frozen + ? 
WHERE id = ? AND (total - frozen) >= ?;

-- 如果 affected_rows == 0，说明该批次库存不足，直接回滚事务。

• MySQL InnoDB 在执行 UPDATE 时自动对该行加行级写锁（X 锁）
• 并发请求排队执行，绝不会出现两个线程同时扣减同一批次最后一件库存的情况
• 配合 ORDER BY expire_date ASC FOR UPDATE 可防止批次分配冲突

5.2 分布式锁防重（支付/取消接口）

$lockKey = "order:pay:{$orderNo}";
$locked = $redis->set($lockKey, 1, ['nx', 'ex' => 10]);
if (!$locked) {
    return json(['code' => 429, 'msg' => '请勿重复提交']);
}
// 执行支付逻辑... finally 中释放锁

5.3 悲观锁保证订单状态一致性

$order = Db::table('orders')->where('order_no', $orderNo)->lock(true)->find();
if ($order['status'] != 0) {
    // 幂等返回，防止重复处理
}

六、事务边界与异步解耦架构

6.1 强一致性（必须在同一个事务中）

6.2 最终一致性（事务提交后异步处理）

6.3 核心原则

事务内只操作数据库资产（钱、货），事务外处理旁路逻辑（通知、积分）。
若 MQ 发送失败，记录日志或写入本地消息表，由定时任务重试，绝不能影响用户下单成功的主流程返回。

七、异常场景与补偿机制

7.1 常见异常处理策略

7.2 定时对账任务（兜底方案）

-- 每日凌晨执行，检测主表与批次表是否一致
SELECT 
    g.id,
    g.total_stock,
    g.total_frozen,
    SUM(b.total) AS batch_total,
    SUM(b.frozen) AS batch_frozen
FROM goods g
LEFT JOIN goods_batch b ON g.id = b.goods_id
GROUP BY g.id
HAVING g.total_stock != batch_total OR g.total_frozen != batch_frozen;
-- 若有结果，触发告警并自动修正（以批次表为准）

八、售后场景专项处理决策树

用户申请退货/退款
  ↓
检查订单状态（已支付/已发货）
  ↓
是否已发货？
  ├── 否（未发货）→ 直接回滚原批次（total +N, frozen -N）→ 退款完成
  └── 是（已发货）→ 生成退货单 → 仓库收货质检
        ↓
    质检结果分类
        ├── 良品 → 新增退货良品批次（batch_type=2, total +N）→ 退款完成
        ├── 报损 → 冲减最老批次（FIFO, total -N） + 新增报损批次（batch_type=3）→ 退款完成
        └── 退回供应商 → 扣减总库存（total -N） + 生成退货单 → 退款完成（或换货）

关键原则：

• 未发货退款：精准回滚原批次，账实零误差。
• 已发货退货良品：生成新退货批次（batch_type=2），独立管理，不与原批次混淆。
• 已发货退货报损：财务冲减最老批次（FIFO）做成本核算，物理新增报损批次做审计追踪。
• 所有售后操作必须更新 order_batch_lock.status，确保链路可追溯。

九、性能优化建议

9.1 索引策略

-- 批次表核心索引（支持 FIFO 查询 + 库存筛选）
CREATE INDEX idx_goods_expire ON goods_batch(goods_id, expire_date, available);
-- 锁定明细表索引（支持订单快速查询批次）
CREATE INDEX idx_order_batch ON order_batch_lock(order_no, status);

9.2 批量操作优化

• 跨批次锁定/扣减时，使用 循环 + 逐条原子 UPDATE（避免复杂 CASE WHEN 导致锁范围扩大）。
• 主表更新使用 total_stock = total_stock ± N 增量更新，避免查询再赋值。

9.3 缓存策略

• 前端展示库存：从 goods 主表读取（单行查询，毫秒级）。
• 热点商品库存：可配合 Redis 缓存 goods.total_stock - goods.total_frozen，缓存 TTL 设置为 5 秒（短暂不一致不影响核心业务），降低 DB 压力。

十、总结

10.1 设计亮点

✅ 资产安全：总库存（Total）与冻结库存（Frozen）分离，下单不扣资产，支付才扣，杜绝“未付款却账实不符”。
✅ 批次可追溯：所有库存变动关联到具体采购批次，支持 FIFO、财务审计、报损溯源。
✅ 高并发安全：利用 MySQL 行锁 + 条件更新，无超卖风险。
✅ 复杂售后支持：部分取消、部分退款、退货良品、报损全场景覆盖。
✅ 金融级一致性：事务强一致 + 异步 MQ 最终一致，核心资产零误差。

10.2 核心代码哲学

强一致事务内：订单 + 批次冻结/扣减/释放 + 主表同步 + 锁定明细
最终一致 MQ：积分 + 消息 + 搜索 + 通知
兜底对账：定时核对主表与批次表，发现偏差自动修复/告警

10.3 适用性说明

本设计适用于常规电商下单（QPS < 5000），若遇到秒杀级峰值流量（QPS > 1万），可在本模型之上叠加 Redis 预扣库存层，将行锁从 MySQL 提升到 Redis Lua 原子操作，异步 MQ 最终落库（需额外处理补偿回滚逻辑）。

文档结束。该方案已同时满足：财务合规性、高并发安全性、批次可追溯性、系统高性能、复杂售后兼容性、金融级数据一致性。