1. 数据建模基础与库存表的核心字段
1.1 需求驱动的建模原则
在MySQL买菜系统中，库存表需要具备高并发写入能力与实时可追溯性，以支撑下单、备货与发货等关键路径。建模时要明确业务边界，将库存与销售、采购、物流等域分离，降低耦合度。关注点包括并发冲突点、库存分布和容量预测，以及对历史变更的审计能力。
从建模到实现的核心在于用最小的字段集表达业务含义，同时通过合适的索引和分区来提升查询和更新的性能。字段设计应覆盖核心操作，如扣减库存、查询当前库存、以及查看待处理订单的在途库存。在设计时应考虑未来的扩展需求，例如分库分表、批量处理和多仓库存。
注：以往的经验表明，库存表若直接堆积大量冗余字段，容易成为性能短板。通过明确的领域边界和可追溯字段，可以在后续的索引与分区优化中获得更好的效果。
以下示例展示一个简化的库存表设计思想，强调从建模出发的字段定位与约束设计。
CREATE TABLE stock_inventory (
product_id INT NOT NULL,
warehouse_id INT NOT NULL,
stock INT NOT NULL DEFAULT 0,
reserved INT NOT NULL DEFAULT 0,
version INT NOT NULL DEFAULT 1,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (product_id, warehouse_id)
) ENGINE=InnoDB;在这段基础表结构中，主键由 product_id 和 warehouse_id 组成，便于直接定位某个商品在某个仓库的当前库存。stock表达可用库存，reserved用于预留量，避免超卖。version 字段可以作为乐观锁的版本标记，便于并发场景下的安全更新。
1.2 库存表的核心字段设计
核心字段包括 product_id、warehouse_id、stock、reserved、version、updated_at、created_at。这些字段既覆盖了库存的当前状态，也为并发更新与变更追溯提供基础。通过 复合主键 (product_id, warehouse_id)，可以快速定位单个仓库中某个商品的库存状态。
为了实现更高效的查询与更新，可以再引入少量附加的覆盖字段，如在索引中包含 stock、reserved，以实现覆盖查询，从而避免回表。更新频繁的字段应尽量使用整型类型，并且尽量使用原子更新操作，减少锁争用。
2. 索引策略与查询性能优化
2.1 主键、唯一键与覆盖索引
在高并发场景中，主键和唯一键的设计直接决定锁粒度与查询路径。对库存表而言，使用 (product_id, warehouse_id) 的复合主键可以在更新库存时锁定最小范围，降低锁争用。对于经常以 product_id 与 warehouse_id 进行筛选的查询，创建覆盖索引是一个有效的性能手段。
覆盖索引含义是在一个索引中包含查询需要的所有列，从而避免对数据行的回表读取。对于常见形态，例如查询某商品在某仓的可用库存，可以用一个包含 (product_id, warehouse_id, stock, reserved) 的覆盖索引来实现。这样在执行 SELECT stock, reserved FROM stock_inventory WHERE product_id=? AND warehouse_id=? 时，数据页读取可由索引完成，提升性能。
CREATE INDEX idx_stock_cover
ON stock_inventory (product_id, warehouse_id, stock, reserved);当涉及多条件筛选时，复合索引的列顺序要符合查询的过滤顺序，以便 MySQL 可以利用最前端列的筛选效果。对于经常按 product_id、warehouse_id 组合筛选并读取 stock 的场景，建议把 product_id 放在前面，以提升唯一定位速度。
2.2 常用查询路径的优化思路
常见的库存查询路径包括“按商品与仓库查询当前库存”、“统计某商品在所有仓库的总库存”、“查询待处理订单的在途库存”等。为这些路径设计专用的索引，可以显著降低查询成本。对于扣减库存的写操作，优先考虑单行原子更新，避免跨行锁定和长事务。
使用 SELECT ... FOR UPDATE 的悲观锁与基于版本号的乐观锁各有适用场景。若系统具有高冲突的写入密度，乐观锁可以减少锁等待；若并发写入在同一商品同一仓库处于极高并发，悲观锁能更稳健地避免超卖风险。
-- 乐观锁示例
UPDATE stock_inventory
SET stock = stock - 1,
version = version + 1
WHERE product_id = ? AND warehouse_id = ? AND version = ?;
-- 悲观锁示例
SELECT stock FROM stock_inventory
WHERE product_id = ? AND warehouse_id = ?
FOR UPDATE;
UPDATE stock_inventory
SET stock = stock - 1
WHERE product_id = ? AND warehouse_id = ?;
3. 表结构优化与分区设计
3.1 水平分区策略与分区键
当单表数据量逐步增大，水平分区可以帮助减少单次查询需要扫描的数据量。对于库存表，常见的分区键有 product_id、warehouse_id 或 日期范围（如 updated_at）等。分区的目标是限制扫描范围，让查询在分区级别就能够筛选掉大量无关数据。
基于业务访问模式，可以采用 HASH 分区（按 product_id 或 warehouse_id 进行散列分布）以均匀分布数据，或采用 RANGE 分区（按时间段分区）以便对历史数据进行快速裁剪。在库存场景下，通常先采用按 product_id 的 HASH 分区，以实现跨仓商品的并发写入分散。
CREATE TABLE stock_inventory (
product_id INT NOT NULL,
warehouse_id INT NOT NULL,
stock INT NOT NULL DEFAULT 0,
reserved INT NOT NULL DEFAULT 0,
version INT NOT NULL DEFAULT 1,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (product_id, warehouse_id)
)
PARTITION BY HASH(product_id)
PARTITIONS eight;
3.2 垂直切分与字段压缩
若库存表逐步演进，出现了大量冗余字段或与库存无直接关系的扩展信息，可以考虑垂直切分，将“核心库存字段”与“日志、审计、扩展属性”等分离到独立表中。核心表保持最小字段集，以便于缓存命中和示意统计。
此外，字段类型的选取也影响存储和 I/O 负载。对于库存数量，使用 TINYINT/SMALLINT/INT 根据实际范围来选择，尽量避免使用较大类型浪费内存与磁盘空间。对时间字段与标志位，可考虑使用紧凑的 TIMESTAMP 与 BOOLEAN/TINYINT 表示。
ALTER TABLE stock_inventory
MODIFY stock SMALLINT NOT NULL,
MODIFY reserved SMALLINT NOT NULL,
ADD COLUMN last_modified_week INT AS (WEEK(updated_at)) VIRTUAL;
4. MySQL 事务与并发控制在库存场景中的应用
4.1 悲观/乐观锁的权衡
库存场景对并发控制的要求较高，选择合适的锁策略直接影响可用性与一致性。乐观锁通过版本号实现无锁读写冲突解决，适合冲突概率较低的场景；悲观锁通过锁定行来确保并发更新的原子性，在高冲突场景下更安全但可能降低吞吐量。
结合实际业务，可以将库存扣减操作设计为“乐观锁更新方案”为主线，辅以“必要时的悲观锁保护”来处理极端并发情况。为了完整性，可以在更新前读取版本号，在更新时通过条件版本号确保幂等性。
-- 乐观锁版本更新示例
UPDATE stock_inventory
SET stock = stock - 1,
version = version + 1
WHERE product_id = ? AND warehouse_id = ? AND version = ?;
-- 失败时可重试或回滚
4.2 悲观锁下的库存扣减示例
在高并发场景中，采用 FOR UPDATE 的悲观锁能确保同一时间只有一个事务更新同一库存记录，避免超卖情况。
START TRANSACTION;
SELECT stock FROM stock_inventory
WHERE product_id = ? AND warehouse_id = ?
FOR UPDATE;
UPDATE stock_inventory
SET stock = stock - 1
WHERE product_id = ? AND warehouse_id = ?;
COMMIT;
5. 从建模到索引实战的实用案例
5.1 现实场景建模示例
在真实的买菜场景中，商品维度、仓库维度和时间维度共同决定库存状态。以商品-仓库为单位进行建模，并通过 审计字段（如 created_at、updated_at、version）实现全量变更追踪。此类设计能便于后续的统计分析、趋势预测和容量评估。
为了支持多渠道下单（站内、移动端、线下门店等），库存表的并发需求往往集中在“同一商品在同一仓库的扣减”这一条目。通过复合主键和覆盖索引可以快速定位并更新，也更易扩展到分库分表场景。
/***** 示例：初始化与对照表 *****/
CREATE TABLE stock_inventory (
product_id INT NOT NULL,
warehouse_id INT NOT NULL,
stock INT NOT NULL DEFAULT 0,
reserved INT NOT NULL DEFAULT 0,
version INT NOT NULL DEFAULT 1,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (product_id, warehouse_id)
) ENGINE=InnoDB;
CREATE INDEX idx_stock_basic ON stock_inventory (product_id, warehouse_id, stock);
5.2 针对大并发的索引设计
在高并发场景下，索引设计应优先考虑常用的查询路径与更新路径。将查询最常用的字段组合放在前面的索引中，并尽量让索引覆盖查询需求，以避免回表。对于跨仓统计，可以构建覆盖包含 stock 与 reserved 的联合索引，提升聚合与筛选的效率。
此外，分区设计与索引联合使用，可以将热区数据保留在高效的分区中，冷数据转移到历史分区，以降低查询开销。此策略有助于保持系统在高峰时段的响应性，同时保留审核能力。
-- 针对热点商品的分区与覆盖索引示例
ALTER TABLE stock_inventory
ADD PARTITION IF NOT EXISTS p1 VALUES LESS THAN (100000),
ADD PARTITION IF NOT EXISTS p2 VALUES LESS THAN (200000);
CREATE INDEX idx_hot_product
ON stock_inventory (product_id, warehouse_id, stock, reserved);
