1. 字段模型设计与数据字典
核心字段与命名规范
在设计用于买菜系统的价格表时，字段粒度、数据类型和命名规范是第一道风控线。合理的字段模型能够支撑多区域、不同商品及多时段的价格变化，避免后续的字段演进成本。此阶段要重点明确价格、时间、商品、地区等核心维度，并为未来的历史价格留出扩展空间。
一个可落地的字段清单通常包括：价格ID、商品ID、城市ID、价格、货币、起始日期、结束日期、版本号、创建时间与更新时间等字段，其中对价格单位的处理要统一为货币字段（如 currency CHAR(3)），并且对时间维度设置严格的有效期字段以支撑价格的历史追溯。
在数据字典层面，应为每一个字段撰写含义、取值范围与索引建议的说明，建立一个与之对应的ER图。数据字典与字段模型的清晰化，能降低开发对接成本并提升后续维护效率。下面给出一个简化的字段模型示例，用于表达价格的时效性与历史演进。
CREATE TABLE price_price (
price_id BIGINT AUTO_INCREMENT PRIMARY KEY,
item_id INT NOT NULL,
city_id INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
currency CHAR(3) NOT NULL DEFAULT 'CNY',
start_date DATE NOT NULL,
end_date DATE,
version INT NOT NULL DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY idx_item_city_start (item_id, city_id, start_date)
);
2. 价格表结构与字段分区的索引设计
组合索引与覆盖查询
在价格表的设计中，组合索引（Item-City-StartDate）是优化常见查询的关键。通过将(item_id, city_id, start_date)作为前置条件的联合索引，可以让查询更快地定位到最近的有效价格并避免回表。与此同时，另一个常用的组合索引是(city_id, item_id, start_date, end_date)，以支持城市维度的快速筛选及时间区间的并行处理。
除了索引，覆盖查询（即查询只取 price 等少量字段，无需访问主键以外的行）可以显著降低 I/O 成本，提升高并发场景下的稳定性。对外暴露的价格字段应尽量通过覆盖索引完成读取，以减少回表的风险。
为了确保数据完整性，价格表通常会保留外键约束指向商品表(item)和城市表(city)，并在价格变动时通过版本号进行记录，便于历史追踪与回滚策略。下列索引示例展示了典型的设计方向。
CREATE INDEX idx_item_city_start ON price_price (item_id, city_id, start_date);
CREATE INDEX idx_city_item ON price_price (city_id, item_id, start_date, end_date);
3. 实战查询优化与字段模型的映射
按日期查询价格的高效路径
实际查询中，需快速获取某个商品在某个城市在指定日期的价格。此类查询的核心决策点是：日期条件、商品与城市的唯一性、以及价格的最近起始时间。通过在(item_id, city_id, start_date)上的组合索引以及对(end_date)的判断，可以在一个有界的范围内返回结果，并避免全表扫描。
典型查询模式包括：读取当前有效价格或给定日期的历史价格。请使用以下形式的查询，以确保能够利用覆盖索引并返回最近的有效记录：
SELECT price
FROM price_price
WHERE item_id = ? AND city_id = ? AND start_date <= ?
AND (end_date IS NULL OR end_date >= ?)
ORDER BY start_date DESC
LIMIT 1;
在实际应用中，往往还需要结合应用层的缓存策略实现快速响应，同时通过解释执行计划（EXPLAIN）对查询是否落在正确的索引上进行核验。通过对查询计划的监控，可以及时发现慢查询和索引失效的场景。下面给出一个分析查询计划的示例：
EXPLAIN SELECT price
FROM price_price
WHERE item_id = 101 AND city_id = 5 AND start_date <= '2025-08-15'
AND (end_date IS NULL OR end_date >= '2025-08-15')
ORDER BY start_date DESC
LIMIT 1;
4. 水平分区与表分区策略
按时间分区的设计原则
当价格表数据量增大时，水平分区成为提升查询性能和维护性的重要手段。常见的分区策略包括基于时间的分区（如按 start_date 分区）或按区域维度分区。分区的核心目标是让查询在分区裁剪时就尽可能减少扫描的分区数量，从而提升查询效率。
若选择基于时间的分区，则通常采用范围分区，并结合按列分区（PARTITION BY RANGE COLUMNS (start_date)）实现分区的向前扩展。对于历史价格数据，可将历史分离到独立分区，便于归档与备份。以下示例展示一个基于 start_date 的简化分区方案：
CREATE TABLE price_price (
price_id BIGINT AUTO_INCREMENT PRIMARY KEY,
item_id INT NOT NULL,
city_id INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
currency CHAR(3) NOT NULL,
start_date DATE NOT NULL,
end_date DATE,
version INT NOT NULL DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY idx_item_city_start (item_id, city_id, start_date)
) PARTITION BY RANGE COLUMNS (start_date) (
PARTITION p2023 VALUES LESS THAN ('2024-01-01'),
PARTITION p2024 VALUES LESS THAN ('2025-01-01'),
PARTITION p2025 VALUES LESS THAN ('2026-01-01')
);
5. 缓存与数据一致性
缓存策略与失效机制
为了降低数据库压力并提升响应速度，缓存层（如 Redis）通常用于存放高频查询的价格结果。缓存应具备明确的失效策略，通常以价格变动时触发 无效化 或直接刷新为准。合理的 TTL（生存时间）能在高并发场景下平衡新鲜度与命中率。
实现缓存时，推荐在价格更新时通过应用端或中间件进行缓存清空，并将新价格写入数据库。通过在价格表增加一个 版本号 字段或改动时间戳，可以在缓存中实现简单的一致性判断，避免回放旧价格带来的误差。
SET price:item:123:city:5 12.34
EXPIRE price:item:123:city:5 300
6. 写入性能与并发控制
UPSERT与乐观锁
写入场景常见需求是对价格进行<增量更新或版本递增，可通过 MySQL 的 Upsert 语句实现原子性更新，同时避免并发写入引发的数据不一致。
一个典型的写入模式是将价格作为一个版本化的记录进行自增更新：若存在相同 item_id、city_id、start_date 的唯一组合，则更新价格与日期等字段，并将版本号自增。以下示例展示了常用的 Upsert 写法：
INSERT INTO price_price (item_id, city_id, price, currency, start_date, end_date, version)
VALUES (123, 5, 12.50, 'CNY', '2025-08-01', NULL, 1)
ON DUPLICATE KEY UPDATE
price = VALUES(price),
end_date = VALUES(end_date),
version = version + 1,
updated_at = CURRENT_TIMESTAMP;
在高并发场景中，除了 Upsert，还可以结合应用层的乐观锁策略：读取价格时同时获取版本号，更新时将版本号作为条件的一部分进行更新，以避免“脏写”。
此外，批量写入与分批提交可以降低锁竞争，提升吞吐量。对于历史价格的积累，建议按日期范围分批写入，避免单次大批量操作对在线查询的影响。
7. 监控、测试与持续优化流程
查询分析与基线测试
持续的性能优化需要对查询进行定期分析与基线测试。通过 EXPLAIN 或 JSON FORMAT 查看查询计划，判断是否走上正确的索引路径；开启慢查询日志，定位高成本的 SQL 语句。
在测试阶段，建议使用真实世界的数据规模进行基线测量，包括读写吞吐、延迟分布与缓存命中率。通过持续集成的测试用例，将对价格表的变更和分区策略的影响纳入评估。
EXPLAIN SELECT price
FROM price_price
WHERE item_id = 101 AND city_id = 5 AND start_date <= '2025-08-15'
AND (end_date IS NULL OR end_date >= '2025-08-15')
ORDER BY start_date DESC
LIMIT 1;
