1. 设计目标与范围
1.1 业务场景要点
在买菜系统中，商品往往以多种规格出售，例如重量、单位、包装、以及可能的促销价。商品规格表需要记录每一个可售 SKU 的价格、库存等信息，以支持前端快速筛选和后端库存管理。
本文围绕 MySQL 中买菜系统的商品规格表设计指南：从字段建模到索引与性能优化的实战要点展开，帮助开发与运维团队清晰理解字段建模与查询优化之间的耦合关系。通过明确的表关系和索引策略，系统在商品规模提升时仍能保持稳定的响应速度。
1.2 数据建模原则
为兼顾灵活性和可维护性，推荐将规格作为 SKU 层面的属性进行分层建模，采用规格定义、规格值、SKU 与规格映射三层结构。字段建模应避免把所有规格字段硬塞在单一表中，以减少表宽度膨胀和索引失效的风险。
通过将规格定义与 SKU 映射分离，可以在不修改主表结构的情况下扩展新的规格项，提升系统的演化能力。向后兼容性也随之增强，历史 SKU 可以继续使用而不会被新字段破坏。
1.3 性能目标
核心目标包括：在海量 SKU 场景下实现快速按商品筛选、快速定位 SKU、以及高并发写入下的价格与库存一致性。索引设计、覆盖查询、以及尽量减少跨表联接的策略，是实现这些目标的关键。
同时，需关注维护成本与查询计划的稳定性，避免因规格数量增长导致的二次开发成本上升。通过合理的表结构和索引组合，可以在不牺牲灵活性的前提下实现高效查询。
2. 字段建模与表结构设计
2.1 统一规格字段与商品关系
引入独立的规格键表与规格值表来承载规格信息，并以 SKU 作为商品具体规格组合的载体。规格键表与 规格值表实现对规格的统一管理，确保不同商品可共享同一组规格定义。
核心实体包括：spec_keys（规格键）、spec_values（规格值）以及 product_skus（SKU 与其元信息）。下面的建模示例给出一个清晰的初始结构。
-- 规格键表：记录规格的名称
CREATE TABLE spec_keys (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(64) NOT NULL,
description VARCHAR(256),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uniq_key_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 规格值表：一个规格键下的可选值
CREATE TABLE spec_values (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
spec_key_id BIGINT UNSIGNED NOT NULL,
value VARCHAR(64) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (spec_key_id) REFERENCES spec_keys(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- SKU 表：一个具体商品的某一定规格组合的销售单位
CREATE TABLE product_skus (
sku_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
product_id BIGINT UNSIGNED NOT NULL,
sku_code VARCHAR(64) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT UNSIGNED NOT NULL DEFAULT 0,
weight DECIMAL(10,2) NULL,
unit VARCHAR(16) NULL,
barcode VARCHAR(32) NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (sku_id),
UNIQUE KEY uniq_sku_code (product_id, sku_code),
INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- SKU 与规格的映射表：一个 SKU 可以关联多对（规格键-规格值）关系
CREATE TABLE product_sku_spec (
sku_id BIGINT UNSIGNED NOT NULL,
spec_key_id BIGINT UNSIGNED NOT NULL,
spec_value_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (sku_id, spec_key_id),
FOREIGN KEY (sku_id) REFERENCES product_skus(sku_id) ON DELETE CASCADE,
FOREIGN KEY (spec_key_id) REFERENCES spec_keys(id) ON DELETE CASCADE,
FOREIGN KEY (spec_value_id) REFERENCES spec_values(id) ON DELETE CASCADE,
INDEX idx_sku_spec (sku_id, spec_key_id, spec_value_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2.2 维度扩展与灵活性
新增规格项时，不需要修改商品主表结构，只需在 spec_keys 与 spec_values 中新增记录即可。向后兼容性良好，历史 SKU 继续可用，新的规格也能无缝接入。
当 SKU 与规格映射表的行数快速增长时，设计应支持按需分区或分库分表的扩展，以保持查询响应时间稳定。通过将规格定义与 SKU 映射分离，扩展成本和维护成本都有所降低。
3. 索引设计与查询优化
3.1 常用查询模式
买菜系统的常见查询包括：按商品筛选可用 SKU、按多规格筛选 SKU、以及获取 SKU 的价格与库存等信息。索引策略需要覆盖这些查询模式，同时避免不必要的联合索引以降低写入成本。
通过清晰的实体边界与索引组合，可以在保持灵活规格定义的同时，确保在大规模 SKU 场景下也能保持高性能。上述设计与本文的主题紧密相关，即围绕 MySQL 下的商品规格表设计与优化要点展开。
3.2 联合索引与覆盖查询
对 product_skus 表，常见的查询模式包括按 product_id 筛选并读取价格和库存。为此，推荐建立组合索引 (product_id, sku_code)，并考虑覆盖查询以减少回表开销。
下面的示例展示了如何添加有助于查询的联合索引，以及一个简单的覆盖查询场景。
ALTER TABLE product_skus
ADD INDEX idx_product_sku (product_id, sku_code),
ADD INDEX idx_price_stock (price, stock),
ADD INDEX idx_product_id_cover (product_id, sku_id, price, stock);
3.3 过滤场景与性能技巧
在按规格过滤时，通常需要对 product_sku_spec 与 spec_values 进行多表联接。为提升性能，给连接字段建立外键并使用组合索引，例如 (sku_id, spec_key_id, spec_value_id)，以实现快速定位 SKU 的具体规格组合。
此外，可以通过将高频访问的规格组合缓存到临时表或物化视图来降低复杂联接的成本；同时，监控慢查询并定期重建统计信息，以维持优化器的决策质量。
3.4 监控与执行计划
通过 EXPLAIN 等工具分析执行计划，关注 索引覆盖率、连接类型 与 行数估计。对超过阈值的慢查询，优先考虑创建覆盖索引或重写查询。
在复杂的规格筛选场景中，必要时可把常用组合映射为单独的视图或临时表，以减少多表联接的成本，确保响应时间在可控范围内。
4. 数据一致性与扩展性
4.1 事务与外键约束
规格键、规格值以及 SKU 的变更应在事务中原子执行，外键约束确保数据的一致性。FOREIGN KEY 约束可以有效防止孤儿记录，维护商品、规格与 SKU 的完整性。
对于高并发写入场景，合理使用锁粒度与事务边界，避免长事务造成的阻塞。通过分离表结构，可以将更新影响缩小到相关子表，提高并发吞吐能力。
4.2 演进策略与版本化
对新 SKU 的上线，推荐通过新增记录的方式实现，尽量避免修改已有 SKU 的关键字段。通过引入 version 或 status 等版本化字段，可实现灰度发布和回滚能力，降低风险。
版本化不仅有助于回滚，还能在数据迁移或 schema 演进时保持历史数据的一致性，方便后续审计和数据分析。
4.3 分布式与分库分表考虑
当商品规模持续扩大时，可以采用水平分割策略：按品类、区域或商户把 SKU 放入不同数据库实例。全局唯一键设计、跨库事务策略以及全局查询聚合能力是关键点。
分布式架构需要额外的运维工作，例如全局唯一性管理、跨库查询的性能影响，以及故障隔离策略。合理的分区策略可以显著提升并发和存储扩展的灵活性。
5. 实战要点与代码示例
5.1 创建表结构示例
以下代码给出完整的建模设计在 MySQL 环境中的实现要点。关键点包括：外键约束、唯一约束、以及覆盖索引设计。请根据实际字符集与版本微调字段长度。
-- 规格键表：记录规格的名称
CREATE TABLE spec_keys (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(64) NOT NULL,
description VARCHAR(256),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uniq_key_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 规格值表：一个规格键下的可选值
CREATE TABLE spec_values (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
spec_key_id BIGINT UNSIGNED NOT NULL,
value VARCHAR(64) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (spec_key_id) REFERENCES spec_keys(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- SKU 表：一个具体商品的某一定规格组合的销售单位
CREATE TABLE product_skus (
sku_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
product_id BIGINT UNSIGNED NOT NULL,
sku_code VARCHAR(64) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT UNSIGNED NOT NULL DEFAULT 0,
weight DECIMAL(10,2) NULL,
unit VARCHAR(16) NULL,
barcode VARCHAR(32) NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (sku_id),
UNIQUE KEY uniq_sku_code (product_id, sku_code),
INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- SKU 与规格的映射表：一个 SKU 可以关联多对（规格键-规格值）关系
CREATE TABLE product_sku_spec (
sku_id BIGINT UNSIGNED NOT NULL,
spec_key_id BIGINT UNSIGNED NOT NULL,
spec_value_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (sku_id, spec_key_id),
FOREIGN KEY (sku_id) REFERENCES product_skus(sku_id) ON DELETE CASCADE,
FOREIGN KEY (spec_key_id) REFERENCES spec_keys(id) ON DELETE CASCADE,
FOREIGN KEY (spec_value_id) REFERENCES spec_values(id) ON DELETE CASCADE,
INDEX idx_sku_spec (sku_id, spec_key_id, spec_value_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
5.2 常见查询示例
示例1：按商品筛选可用 SKU，并排序价格以供展示。
SELECT ps.sku_id, ps.sku_code, ps.price, ps.stock
FROM product_skus ps
WHERE ps.product_id = ?
ORDER BY ps.price ASC
LIMIT 50;
示例2：按多规格筛选 SKU（示例：重量=500g 且 包装单位=袋）。
SELECT ps.sku_id
FROM product_skus ps
JOIN product_sku_spec s1 ON ps.sku_id = s1.sku_id
JOIN spec_values v1 ON s1.spec_value_id = v1.id
JOIN spec_keys k1 ON s1.spec_key_id = k1.id
WHERE ps.product_id = ?
AND k1.name = '重量' AND v1.value = '500g'
AND EXISTS (
SELECT 1
FROM product_sku_spec s2
JOIN spec_keys k2 ON s2.spec_key_id = k2.id
JOIN spec_values v2 ON s2.spec_value_id = v2.id
WHERE s2.sku_id = ps.sku_id AND k2.name = '包装单位' AND v2.value = '袋'
);
示例3：获取 SKU 的完整规格明细（重量与单位作为示例字段）。
SELECT ps.sku_id, ps.sku_code, pv1.value AS weight, pv2.value AS unit
FROM product_skus ps
JOIN product_sku_spec s1 ON ps.sku_id = s1.sku_id AND s1.spec_key_id = (SELECT id FROM spec_keys WHERE name = '重量')
JOIN spec_values pv1 ON s1.spec_value_id = pv1.id
JOIN product_sku_spec s2 ON ps.sku_id = s2.sku_id AND s2.spec_key_id = (SELECT id FROM spec_keys WHERE name = '单位')
JOIN spec_values pv2 ON s2.spec_value_id = pv2.id
WHERE ps.product_id = ?;
5.3 设计评估与对比要点
与将所有字段放在单一表中的传统设计相比，分层规格表 + SKU 映射在扩展性和可维护性方面具有明显优势。对于动态规格场景，规格键和值表 + SKU-规格映射的模式能够更高效地支持新规格的接入与组合查询。
在性能方面，适当的联合索引和覆盖查询对比冗长的联表筛选，通常能带来显著提升。需要通过实际数据分布和查询场景进行细粒度的索引复用与优化。
5.4 维护与演练建议
定期对执行计划进行审阅，保持统计信息的准确性；对经常访问的规格组合考虑使用缓存或物化视图。对于大规模数据，逐步引入分区或分库分表方案，并保持全局一致性与可观测性。
