在电子商务场景中，商城用户收藏表的设计直接影响查询性能与系统吞吐。本文围绕 MySQL 下的商城用户收藏表结构设计与优化指南，从数据模型、索引策略、分区与清理等维度，给出可落地的设计方案与实践要点。
1. 场景分析与目标
1.1 商城用户收藏的核心需求
核心需求是记录用户对商品的收藏行为，并能够快速检索某个用户的收藏列表、按照时间排序加载最近收藏项，以及在删除或屏蔽某条收藏时保持数据的一致性。
在实现上，字段设计需覆盖 user_id、product_id、created_at、is_deleted等关键维度，确保后续的筛选、排序和去重操作具备高效性与可维护性。
唯一性约束与软删除是常见设计要点：通过对 (user_id, product_id) 设定唯一约束，避免重复收藏；通过 is_deleted 实现逻辑删除，避免物理删除带来的数据不连续性。
CREATE TABLE user_favorites (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
is_deleted TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE KEY uq_user_product (user_id, product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2. 表结构设计原则
2.1 字段设计要点
自增主键 versus 复合主键的取舍影响后续关联与查询的稳定性。通常选择以 自增主键作为主键，保留 唯一性约束在 (user_id, product_id) 上，既利于外部引用也便于并发写入。
软删除字段 is_deleted提供灵活的删除策略，有助于保留历史数据，避免频繁的物理删除带来的性能波动。
时间字段 created_at、updated_at用于排序、归档与数据治理，务必确保时区一致性与默认值的正确性。
ALTER TABLE user_favorites
ADD INDEX idx_user_deleted_created (user_id, is_deleted, created_at),
ADD INDEX idx_user_product (user_id, product_id),
ADD INDEX idx_created_at (created_at);
3. 性能优化策略
3.1 索引策略与查询优化
合理的索引组合能够实现覆盖索引，避免回表，显著提升查询性能。常见的优化组合是让查询中的筛选条件和排序字段出现在一个联合索引内。
例如，若常见查询为获取某个用户最近的收藏项，语句通常为 按 user_id、is_deleted 过滤、按 created_at 排序并限制数量，此时应确保索引覆盖这些列。
SELECT product_id, created_at
FROM user_favorites
WHERE user_id = ? AND is_deleted = 0
ORDER BY created_at DESC
LIMIT 20;
EXPLAIN SELECT product_id, created_at
FROM user_favorites
WHERE user_id = 123 AND is_deleted = 0
ORDER BY created_at DESC
LIMIT 20;
3.2 数据分区与水平扩展
为应对巨量数据场景，可以考虑对表进行水平分区或分库分表。分区策略有助于减少单次扫描的数据量，提高大量历史数据的查询效率。
一种常见做法是在 MySQL 中使用哈希分区，将数据均匀分布在若干分区上，以提升并发下的写入吞吐与查询并发性。
ALTER TABLE user_favorites PARTITION BY HASH(user_id) PARTITIONS 4;
对于企业级应用，也可以结合分库分表来进一步解耦热数据与冷数据，降低单库的压力。
3.3 数据清理与存档策略
为了控制表的体积与维护成本，定期清理或归档历史数据是关键。可以将超过一定时间的收藏记录迁移到归档表，或将其标记为已归档并定期合并清理。
CREATE TABLE user_favorites_archive LIKE user_favorites;
INSERT INTO user_favorites_archive
SELECT * FROM user_favorites WHERE created_at < '2023-01-01';
DELETE FROM user_favorites WHERE created_at < '2023-01-01';
4. 事务与一致性
4.1 事务边界与锁粒度
在高并发写入场景中，应注重事务边界和锁粒度，尽量将写入操作控制在较小的范围内，避免长事务导致的阻塞。
在 InnoDB 引擎下，行级锁是主要机制，结合合适的索引可以减少锁冲突。
START TRANSACTION;
INSERT INTO user_favorites (user_id, product_id, created_at) VALUES (1001, 3010, NOW());
COMMIT;
5. 监控与运维
5.1 指标与告警要点
通过监控指标来把握性能瓶颈与容量风险，慢查询、索引命中率、缓存命中率、IOUtil、锁等待等是关键指标。
常见的监控实践包括定期审查 innodb_buffer_pool_size、query_cache_size（若存在）、innodb_log_file_size 等参数，以及对慢查询进行分析与优化。
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL STATUS LIKE 'Handler_read%';
6. 兼容性与迁移注意事项
6.1 版本差异要点
在不同 MySQL 版本之间，分区实现、外键支持、以及表行格式（DYNAMIC/COMPRESSED）的行为可能不同，应在上线前进行版本兼容性验证与回滚策略设计。
在进行结构变更时，优先考虑在线DDL能力，结合短事务策略，避免对高峰期的商城收藏查询产生影响。
ALTER TABLE user_favorites ADD COLUMN note VARCHAR(255) AFTER is_deleted, ALGORITHM=INPLACE, LOCK=NONE;
