1. 库存表设计总体原则
1.1 设计目标与范围
在本文的背景下，MySQL 下商城库存表结构设计全解的核心目标是确保数据一致性、高并发写入能力以及系统的可扩展性。库存表通常与商品表、SKU表、订单表等高度耦合，设计时需要考虑跨表的事务性与一致性边界。
范围界定应覆盖多店铺、多仓库场景，以及分布式部署下的库存变动路径，避免单点瓶颈影响全局吞吐。
1.2 数据库与存储引擎选型
为实现高并发写入与事务性，选用 InnoDB 作为存储引擎，并开启外键约束、行级锁和合适的事务隔离级别，以支撑跨表一致性与回滚能力。
在设计初期就应考虑冗余与监控能力，例如启用审计字段、变更日志，以及对关键字段设置非空约束与默认值，以降低异常数据的风险。
2. 字段设计与数据类型
2.1 核心字段与含义
库存表的核心字段通常包括 stock_id、sku_id、warehouse_id、quantity、lock_quantity、reserved_quantity 等，分别对应库存总量、被锁定的数量以及已保留但暂未发货的数量等概念。
为确保数据精确性，数量字段多使用 BIGINT/INT，时间字段使用 TIMESTAMP，并设置默认值以保障新记录的正确性。
2.2 辅助字段与扩展
引入 version 字段实现乐观锁，使用 updated_at 与 created_at 进行审计与事件重放，此外通过 UNIQUE 键防止重复行。
对于未来的扩展，可以预留 source、audit_status 等字段，以便对多来源库存与变动状态进行追踪。
3. 约束与索引策略
3.1 主键、唯一约束与外键
主键 stock_id 提供全表唯一标识，sku_id + warehouse_id 的组合唯一键用于避免同仓库同 SKU 的重复记录，确保数据一致性。
外键约束能提升数据完整性，但在高并发场景下可能增加锁竞争，因此需要权衡分区、分表或应用层校验来降低影响。
3.2 常用索引设计
为常用查询路径创建组合索引，例如 (sku_id, warehouse_id)、(item_id, warehouse_id)，以实现覆盖查询并降低回表成本。
索引选择应结合实际查询模式和更新频率，避免过多的写时锁竞争与索引维护开销。
3.3 乐观锁与版本控制
通过 version 实现乐观锁，降低长时间锁持有，提升并发能力。版本更新策略应与库存变动操作原子化，确保在并发场景下不会出现版本冲突导致的数据不一致。
4. 性能优化实操
4.1 分区与分表策略
面对海量库存数据，分区（如按时间、SKU、地区）或 分表（如按店铺、商品类型）可有效降低单表行数，提升查询与写入吞吐和并发度。
分区设计要点包括分区键的选择、分区数量的上限以及跨分区事务的处理策略，以确保跨区查询仍具备合理性能。
4.2 缓存与读写分离
对热销 SKU 的可用库存采用 缓存层（如 Redis），降低数据库压力。库存变动时应及时刷新或失效相关缓存，确保读取得到最新数据。
缓存一致性策略需要明确，例如写入成功后立即清除相关缓存，避免出现脏读或脏写。
4.3 事务边界与锁策略
尽量将事务范围缩小到单次库存变动的原子操作，使用 行级锁，避免长事务引发的阻塞与死锁风险。
在某些场景下，可以采用 最终一致性 的设计，例如通过异步补偿或消息队列来同步跨表库存状态。
4.4 审计日志与数据变更
记录 库存变动日志，便于对账、回溯与审计；避免在主表使用复杂触发器，以免引入额外的性能开销，优先选择应用层日志或单独的日志表。
日志表应具备高写入吞吐和简洁的查询能力，必要时对日志表进行归档与分区管理。
5. 实践案例：MySQL 下商城库存表结构设计
5.1 实践场景与表设计目标
在商城场景中，库存表需同时支持多 SKU、多仓库的并发写入与读取，目标包括库存精确性、可扩展性和审计能力。
通过以下设计，可在保持数据完整性的前提下提升系统的并发吞吐与维护性。
5.2 具体表结构示例
以下字段集合用于常见的库存场景，包含 stock_id、sku_id、warehouse_id、quantity、lock_quantity、reserved_quantity、version、updated_at、created_at 等。
CREATE TABLE stock_inventory (
stock_id BIGINT AUTO_INCREMENT PRIMARY KEY,
sku_id BIGINT NOT NULL,
warehouse_id BIGINT NOT NULL,
quantity INT NOT NULL DEFAULT 0,
lock_quantity INT NOT NULL DEFAULT 0,
reserved_quantity INT NOT NULL DEFAULT 0,
version INT NOT NULL DEFAULT 1,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uniq_sku_warehouse (sku_id, warehouse_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
5.3 常用查询与更新示例
以下示例展示如何查询可用库存、在并发场景下扣减库存，以及实现乐观锁更新。关键点在于将可用库存计算、锁定机制与版本控制结合在原子操作中。
-- 获取可用库存
SELECT quantity - lock_quantity - reserved_quantity AS available
FROM stock_inventory
WHERE sku_id = ? AND warehouse_id = ? FOR UPDATE;
-- 锁定库存（乐观锁示例）
UPDATE stock_inventory
SET quantity = quantity - ?
WHERE sku_id = ? AND warehouse_id = ? AND version = ?;
