电商平台订单与库存系统的数据库设计 -

题目

电商平台订单与库存系统的数据库设计

信息

类型：问答
难度：⭐⭐

考点

实体关系设计, 事务处理, 数据一致性

快速回答

设计电商订单与库存系统的核心要点：

创建订单表(orders)、订单明细表(order_items)和库存表(inventory)
使用事务处理确保下单时库存扣减的原子性
实现乐观锁机制防止超卖（如版本号或条件更新）
订单状态机设计包含待支付、已支付、已发货、已完成等状态
库存字段需区分可用库存和锁定库存

## 解析

1. 核心表结构设计

-- 库存表（核心防超卖）
CREATE TABLE inventory (
  product_id INT PRIMARY KEY,
  available_stock INT NOT NULL CHECK (available_stock >= 0),
  locked_stock INT DEFAULT 0 CHECK (locked_stock >= 0),
  version INT DEFAULT 0  -- 乐观锁版本号
);

-- 订单主表
CREATE TABLE orders (
  order_id BIGSERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'completed', 'canceled')),
  total_amount DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 订单明细表
CREATE TABLE order_items (
  item_id BIGSERIAL PRIMARY KEY,
  order_id BIGINT REFERENCES orders(order_id),
  product_id INT NOT NULL,
  quantity INT NOT NULL CHECK (quantity > 0),
  price DECIMAL(10,2) NOT NULL
);

2. 下单事务处理流程

关键步骤（伪代码）：

BEGIN TRANSACTION;

-- 步骤1：检查库存是否充足
SELECT available_stock FROM inventory WHERE product_id = 1001 FOR UPDATE;

-- 步骤2：扣减可用库存并增加锁定库存
UPDATE inventory 
SET available_stock = available_stock - 1,
    locked_stock = locked_stock + 1,
    version = version + 1
WHERE product_id = 1001 
AND available_stock >= 1;

-- 步骤3：创建订单记录
INSERT INTO orders (...) VALUES (...);
INSERT INTO order_items (...) VALUES (...);

COMMIT;

3. 最佳实践与原理

防超卖机制：通过UPDATE条件检查（available_stock >= 需求数量）和乐观锁（version字段）确保并发安全
库存分离设计：
- available_stock：可售库存
- locked_stock：已下单未支付的预留库存
事务隔离级别：使用Read Committed + FOR UPDATE避免脏读，但需注意死锁风险
订单状态流转：
- 支付成功：locked_stock清零
- 订单取消：locked_stock回退到available_stock
- 发货后：实际库存减少（若使用独立库存系统）

4. 常见错误

先查后改导致超卖：在并发场景下，查询库存后不立即更新会导致数据不一致
缺少状态回退机制：未处理支付超时订单的库存释放（需定时任务）
过度锁定：长时间FOR UPDATE锁表影响系统吞吐量
硬删除订单：应使用逻辑删除标记（is_deleted）保留操作记录

5. 扩展知识

分库分表策略：订单表按user_id分片，库存表按product_id分片
最终一致性方案：
- 场景：秒杀等高并发场景可先扣Redis库存，异步同步到数据库
- 补偿机制：通过消息队列实现库存回滚（如RabbitMQ的死信队列）
历史库存快照：在order_items中保存下单时的库存版本号，便于售后追踪