如何设计供应商与商品的多对多关系表结构？

Question

常见技术问题： 在设计供应商与商品的多对多关系时，是否应在关联表（如 `supplier_product`）中直接冗余存储商品名称、供应商简称等字段以提升查询性能？若冗余，如何保证数据一致性？若不冗余，频繁JOIN主表是否会导致高并发下性能瓶颈？此外，当需记录某次供货的具体价格、起止有效期、最小起订量等动态属性时，这些字段应放在关联表还是单独建“供应协议”或“供货条款”子表？若多个供应商提供同一商品但价格/规格不同，又该如何建模才能既支持灵活定价、历史追溯，又避免违反第三范式导致更新异常？最后，在分库分表场景下，该关联表的分片键应选 `supplier_id` 还是 `product_id`，抑或复合键？选择依据是什么？

Answer 1

```html

一、基础建模：多对多关系的范式化设计

供应商（suppliers）与商品（products）天然构成多对多关系，标准第三范式（3NF）解法是引入纯关联表 supplier_product，仅含 supplier_id 和 product_id 两个外键字段，并设复合主键。该设计杜绝名称/简称等冗余，避免更新异常（如修改商品名需同步更新数百条关联记录）。但需注意：范式化不是性能枷锁，而是数据可信性的基石。

二、性能权衡：冗余字段的“必要之恶”与一致性治理

场景	是否建议冗余	一致性保障机制
商品名称（product_name）	❌ 不建议	通过物化视图或应用层缓存（如Redis Hash）按 product_id 缓存，TTL+变更双写失效
供应商简称（supplier_code）	✅ 可选冗余	采用数据库触发器（MySQL BEFORE UPDATE）或应用层事件驱动（如CDC + Kafka）自动同步

关键结论：冗余应限于低频变更、高查询密度、非核心业务语义字段；所有冗余必须配套原子性同步机制，否则将引发“幽灵数据”——即前端展示的商品名与主表不一致，且难以审计。

三、动态属性建模：从关联表到领域子表的演进

价格、有效期、MOQ（最小起订量）等属性具有时态性、契约性、可追溯性，绝不可置于扁平关联表。正确建模如下：

CREATE TABLE supply_terms (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  supplier_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  unit_price DECIMAL(12,4) NOT NULL,
  valid_from DATE NOT NULL,
  valid_to DATE,
  moq INT DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  is_active TINYINT DEFAULT 1,
  -- 历史版本通过 valid_to=NULL 或 is_active=1 标识当前有效条款
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

该设计支持：同一商品多供应商多价格（自然满足）、价格历史快照（通过 valid_from/valid_to 实现SCD2）、条款生命周期管理（启用/停用无需物理删除）。

四、分库分表策略：分片键选择的三维决策模型

在ShardingSphere或MyCat等中间件下，supply_terms 表分片需兼顾查询模式、数据倾斜、事务边界三要素：

graph LR
A[高频查询入口] --> B{查询类型}
B -->|查某供应商所有供货条款| C[按 supplier_id 分片]
B -->|查某商品所有供应方| D[按 product_id 分片]
B -->|两者均高频| E[复合分片键 supplier_id % 1000 + product_id % 1000]
C --> F[优势：供应商维度聚合快，适合采购协同系统]
D --> G[优势：商品主数据平台友好，适合价格比对场景]
E --> H[代价：跨分片JOIN不可避免，需应用层聚合]

生产实践表明：若系统以“供应商为中心”（如SRM系统），首选 supplier_id；若以“商品为中心”（如电商比价中台），首选 product_id；混合型系统建议采用逻辑库分离：将 supply_terms 拆为 supply_terms_by_supplier 和 supply_terms_by_product 两张影子表，由Binlog同步保障最终一致。

五、高并发优化：超越JOIN的工程化方案

当QPS > 5k且95%请求需同时获取供应商简称+商品名称+当前有效价格时，纯JOIN已成瓶颈。推荐三级加速体系：

1. 第一级（毫秒级）：应用内Guava Cache，Key=supplier_id:product_id，Value=DTO含全部字段，加载策略为LoadingCache+refreshAfterWrite(1m)
2. 第二级（百毫秒级）：Redis JSON，Key=sp_term:{supplier_id}:{product_id}，存储结构化条款，TTL=30min+业务变更事件主动DEL
3. 第三级（秒级）：数据库兜底，采用覆盖索引 INDEX idx_sp_active (supplier_id, product_id, is_active) INCLUDE (unit_price, valid_from, valid_to, moq)

该架构实测在8核16G容器上支撑12k QPS，平均P99延迟<42ms，且无JOIN导致的连接池耗尽风险。

```