当前位置：首页 > news >正文

库存管理系统的数据库设计：从进销存到分布式库存的一体化方案

news 2026/7/25 7:11:49

库存管理系统的数据库设计：从进销存到分布式库存的一体化方案

一、"库存显示有货，仓库却说发不出"：库存数据不一致的根源

某电商仓库凌晨2点收到紧急告警：系统显示某SKU还有523件库存，但实际盘点只剩7件。追查发现，过去72小时内有3次退货入库操作在数据库主从切换时丢失——主库在提交事务后宕机，从库提升为主库时选择了到主库宕机前那一刻的数据，退货记录丢失了但Redis缓存中的库存扣减记录还在。

库存数不一致有三个经典来源：

数据库主从延迟：主库入库写入，从库还有2秒延迟，查询走从库看到的是旧数据
Redis与MySQL不一致：高性能的库存扣减走Redis，异步同步到MySQL时有丢失风险
分布式事务的影子库存：跨仓库调拨时，调出仓库已经扣减，调入仓库还未增加——中间态的库存"暂时消失了"

二、库存系统的三阶段架构演进

三、Redis+MySQL双写的库存实现

@Service public class InventoryService { private final RedisTemplate<String, Integer> redis; private final JdbcTemplate mysql; private final KafkaTemplate<String, InventoryEvent> kafka; // Redis库存Key前缀 private static final String STOCK_KEY = "inventory:stock:"; // Lua脚本：原子性扣减库存 private static final String DEDUCT_SCRIPT = """ local key = KEYS[1] local quantity = tonumber(ARGV[1]) local current = tonumber(redis.call('GET', key) or '0') if current >= quantity then redis.call('DECRBY', key, quantity) return 1 else return 0 end """; private final RedisScript<Long> deductScript; public boolean deductStock(String skuId, String warehouseId, int quantity) { String redisKey = STOCK_KEY + skuId + ":" + warehouseId; try { // Step 1: Redis原子扣减 Long result = redis.execute( deductScript, Collections.singletonList(redisKey), String.valueOf(quantity) ); if (result != 1) { return false; // 库存不足 } // Step 2: 异步写MySQL + 发送事件 asyncExecutor.submit(() -> { try { mysql.update( "UPDATE inventory SET stock = stock - ?, " + "version = version + 1 " + "WHERE sku_id = ? AND warehouse_id = ? " + "AND stock >= ?", quantity, skuId, warehouseId, quantity ); // 发送库存变更事件 kafka.send("inventory_change", new InventoryEvent(skuId, warehouseId, -quantity) ); } catch (Exception e) { // MySQL写入失败 → 补偿回Redis redis.opsForValue().increment(redisKey, quantity); // 写入对账队列 reconciliationQueue.add(skuId, warehouseId, quantity); throw new InventoryException("库存MySQL同步失败", e); } }); return true; } catch (RedisException e) { // Redis不可用 → 降级到MySQL直接扣减 return deductStockFromMySQL(skuId, warehouseId, quantity); } } private boolean deductStockFromMySQL(String skuId, String warehouseId, int quantity) { try { int rows = mysql.update( "UPDATE inventory SET stock = stock - ? " + "WHERE sku_id = ? AND warehouse_id = ? AND stock >= ?", quantity, skuId, warehouseId, quantity ); return rows > 0; } catch (Exception e) { throw new InventoryException("库存MySQL扣减失败", e); } } // 定时对账任务：每小时比对Redis和MySQL的库存数 @Scheduled(fixedDelay = 3600000) public void reconcileInventory() { List<InventoryRecon> diffs = mysql.query( "SELECT sku_id, warehouse_id, stock AS mysql_stock FROM inventory", (rs, rowNum) -> new InventoryRecon( rs.getString("sku_id"), rs.getString("warehouse_id"), rs.getInt("mysql_stock") ) ); for (InventoryRecon recon : diffs) { String redisKey = STOCK_KEY + recon.skuId + ":" + recon.warehouseId; Integer redisStock = redis.opsForValue().get(redisKey); if (redisStock != null && redisStock != recon.mysqlStock) { // 差异 > 1% 时触发告警 double diffPct = Math.abs(redisStock - recon.mysqlStock) / (double) Math.max(redisStock, recon.mysqlStock); if (diffPct > 0.01) { alertInventoryDifference(recon, redisStock); // 以MySQL为准修正Redis redis.opsForValue().set(redisKey, recon.mysqlStock); } } } } }