3. 数据模型设计：批次追溯表结构设计、价格快照表设计、索引策略
在供应商交付批次追溯场景中，数据模型的设计直接决定了价格偏差预防的实时性和准确性。本章围绕批次追溯表与价格快照表两张核心表，从表结构、字段约束到索引策略进行系统性设计，确保在千万级数据量下仍能保持毫秒级查询响应。
3.1 批次追溯表结构设计
批次追溯表（batch_trace）用于记录每一批物料的完整流转链路，从供应商发货、入库、质检到生产消耗。设计原则是扁平化存储 + 冗余关键维度，避免在追溯时产生大量关联查询。
| 字段名 | 类型 | 约束 | 说明 |
|---|---|---|---|
trace_id |
BIGINT UNSIGNED |
PK, AUTO_INCREMENT | 追溯主键 |
batch_no |
VARCHAR(64) |
NOT NULL, UNIQUE | 供应商批次号（业务唯一） |
material_code |
VARCHAR(32) |
NOT NULL | 物料编码 |
supplier_id |
INT UNSIGNED |
NOT NULL | 供应商ID |
receipt_time |
DATETIME(3) |
NOT NULL | 收货时间（毫秒精度） |
quantity |
DECIMAL(18,4) |
NOT NULL | 交付数量 |
unit_price |
DECIMAL(18,6) |
NOT NULL | 实际结算单价 |
price_snapshot_id |
BIGINT UNSIGNED |
NOT NULL | 关联价格快照ID |
trace_status |
TINYINT |
DEFAULT 0 | 0=正常, 1=价格偏差, 2=待复核 |
create_time |
DATETIME(3) |
DEFAULT CURRENT_TIMESTAMP(3) | 记录创建时间 |
设计要点：
batch_no使用唯一索引而非主键，保留自增主键的写入性能优势。receipt_time精确到毫秒，支持高频批次的时间范围查询。price_snapshot_id作为外键逻辑关联，避免追溯时实时计算价格。
3.2 价格快照表设计
价格快照表（price_snapshot）在每次供应商价格变更或新合同生效时生成一条记录，为批次追溯提供基准价格。该表是价格偏差判定的唯一依据，必须保证历史数据不可篡改。
| 字段名 | 类型 | 约束 | 说明 |
|---|---|---|---|
snapshot_id |
BIGINT UNSIGNED |
PK, AUTO_INCREMENT | 快照主键 |
material_code |
VARCHAR(32) |
NOT NULL | 物料编码 |
supplier_id |
INT UNSIGNED |
NOT NULL | 供应商ID |
effective_date |
DATE |
NOT NULL | 价格生效日期 |
expiry_date |
DATE |
DEFAULT '9999-12-31' | 价格失效日期 |
standard_price |
DECIMAL(18,6) |
NOT NULL | 标准单价 |
contract_no |
VARCHAR(32) |
NOT NULL | 关联合同号 |
snapshot_hash |
CHAR(64) |
NOT NULL | SHA-256防篡改校验 |
create_time |
DATETIME(3) |
DEFAULT CURRENT_TIMESTAMP(3) | 快照生成时间 |
快照防篡改机制：
snapshot_hash 字段存储对整行关键字段（material_code + supplier_id + effective_date + standard_price）的 SHA-256 哈希值。每次追溯时校验哈希，确保历史价格未被后台修改。
3.3 索引策略：聚簇索引与覆盖索引
索引设计是性能调优的核心。针对批次追溯和价格快照的查询模式，采用聚簇索引优化范围扫描，使用覆盖索引消除回表开销。
3.3.1 聚簇索引设计
InnoDB 引擎中，聚簇索引即主键索引，数据行直接存储在索引的叶子节点。对于批次追溯表，主键 trace_id 自增保证了写入的顺序性，避免页分裂。但业务查询通常按 batch_no 或 receipt_time 过滤，因此需要额外设计二级聚簇索引。
-- 批次追溯表：按收货时间聚簇，支持时间范围追溯
ALTER TABLE batch_trace ADD INDEX idx_receipt_time (receipt_time);
-- 价格快照表：按物料+供应商+有效期聚簇，快速定位生效价格
ALTER TABLE price_snapshot ADD INDEX idx_material_supplier_effective
(material_code, supplier_id, effective_date);
注意：不要对
batch_no 建立聚簇索引。虽然业务上唯一，但批次号是字符串类型，自增主键的写入性能远优于 UUID 或字符串聚簇。
3.3.2 覆盖索引设计
覆盖索引是指查询所需的所有字段都包含在索引中，无需回表访问数据行。在价格偏差预防场景中，以下两个查询最为频繁：
- 查询批次价格与标准价格对比：需要
batch_trace.unit_price和price_snapshot.standard_price。 - 按供应商+物料统计偏差批次：需要
trace_status、quantity、unit_price。
-- 覆盖索引：批次追溯表，避免回表获取价格和状态
ALTER TABLE batch_trace ADD INDEX idx_cover_price_check
(material_code, supplier_id, receipt_time, unit_price, trace_status);
-- 覆盖索引：价格快照表，快速获取标准价格
ALTER TABLE price_snapshot ADD INDEX idx_cover_standard_price
(material_code, supplier_id, effective_date, standard_price);
覆盖索引效果验证：
使用
使用
EXPLAIN 查看执行计划，当 Extra 列出现 Using index 时，表示查询完全由索引覆盖，无需回表。在千万级数据量下，覆盖索引可将查询延迟从 200ms 降至 5ms 以内。
3.4 数据模型关系与查询路径
下图展示了批次追溯表与价格快照表之间的关联关系，以及典型的价格偏差判定查询路径。
3.5 索引策略对比与选择建议
不同索引策略在写入性能、查询性能和存储开销上各有优劣。下表总结了三种核心索引策略的适用场景：
| 索引类型 | 写入性能 | 查询性能 | 存储开销 | 推荐场景 |
|---|---|---|---|---|
| 聚簇索引（主键自增） | 极高（顺序写入） | 高（范围扫描） | 低（无额外索引页） | 批次追溯表主键、时间范围查询 |
| 二级聚簇索引 | 中（需维护索引树） | 高（按字段聚簇） | 中 | 价格快照表按物料+供应商+有效期查询 |
| 覆盖索引 | 低（索引字段多） | 极高（零回表） | 高（冗余存储） | 高频价格对比查询、偏差统计 |
索引设计黄金法则：
对于写入频繁的批次追溯表，优先保证主键自增聚簇；对于查询频繁的价格快照表，优先建立覆盖索引。在性能调优时，使用
对于写入频繁的批次追溯表，优先保证主键自增聚簇；对于查询频繁的价格快照表，优先建立覆盖索引。在性能调优时，使用
SHOW INDEX FROM table_name 检查索引基数（Cardinality），确保索引选择性大于 30%。
3.6 数据模型与索引的协同优化
下图展示了索引在查询执行计划中的协同工作方式，以及如何通过索引下推（Index Condition Pushdown, ICP）进一步减少回表次数。
索引协同优化总结：
- 聚簇索引：适合时间范围查询，如“查询某日所有批次”。
- 覆盖索引：适合价格偏差判定，如“对比批次单价与标准价”。
- 索引下推（ICP）：当二级索引无法完全覆盖时，MySQL 会在索引层面过滤不满足条件的行，减少回表次数。使用
EXPLAIN确认Extra列是否包含Using index condition。