多门店进销存系统：数据透视表与动态独立表选型咨询

为什么动态建表方案不可行？ #

• 维护成本爆炸：每个门店一张表，后续写任何跨门店的逻辑（比如全门店库存统计、调拨报表）都要动态拼接SQL遍历所有表；新增字段（比如库存预警阈值）要给所有表批量操作；备份、迁移时要处理几十上百张表，完全是给自己埋长期隐患。
• 性能优化无解：每张小表单独建索引，重复劳动不说，数据库对大量小表的管理效率远低于少量大表，跨门店查询时的UNION操作会大幅拖慢性能。
• 风险极高：门店名称包含特殊字符（比如-、空格、非英文字符）时，自动生成的表名可能非法，直接导致创建失败；后续删除门店还要同步删表，误操作概率极大。
• 扩展性为0：以后要加门店分组、区域统计这类需求，动态表结构根本没法高效实现，最后只能推翻重来。

第一种方案的“记录数倍增”不是真问题 #

• 数据库天生就是用来处理大规模数据的，只要索引建对（shop_id+product_id的联合主键，再加单独的shop_id、product_id索引），百万级甚至千万级的记录都能做到毫秒级查询。
• 实际业务中，不会每个门店都有所有商品的库存——只有门店实际入库过的商品才需要存记录，库存为0的可以选择不存（查询时用左连接默认返回0），或者仅保留有过交易的记录，实际数据量远小于“门店数×商品数”的理论值。
• 真到了超大规模（比如上万门店+百万商品），可以用分区表（按shop_id范围分区）或者分表（按shop_id哈希分表）来优化，这种可控的分表比动态建表靠谱100倍。

优化第一种方案的具体建议 #

• 推荐的库存表结构：

  CREATE TABLE shop_inventories (
      shop_id INT NOT NULL,
      product_id INT NOT NULL,
      stock_quantity INT NOT NULL DEFAULT 0,
      last_updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (shop_id, product_id), -- 确保唯一，避免重复记录
      INDEX idx_product (product_id), -- 方便按商品查所有门店库存
      INDEX idx_shop (shop_id), -- 方便查单个门店的所有商品库存
      FOREIGN KEY (shop_id) REFERENCES shops(id),
      FOREIGN KEY (product_id) REFERENCES products(id)
  );
  

• 配合调拨功能，新增库存流转追踪表：

  CREATE TABLE inventory_transfers (
      id INT AUTO_INCREMENT PRIMARY KEY,
      from_shop_id INT NOT NULL,
      to_shop_id INT NOT NULL,
      product_id INT NOT NULL,
      transfer_quantity INT NOT NULL,
      transfer_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      operator_id INT NOT NULL,
      FOREIGN KEY (from_shop_id) REFERENCES shops(id),
      FOREIGN KEY (to_shop_id) REFERENCES shops(id),
      FOREIGN KEY (product_id) REFERENCES products(id),
      FOREIGN KEY (operator_id) REFERENCES users(id)
  );
  

• 库存为0的处理：如果业务不需要追踪历史库存为0的记录，可以在出库后删除对应记录，查询时用LEFT JOIN关联products表，默认库存为0，减少无效数据量。

内容的提问来源于stack exchange，提问作者Galactus