复合主键与唯一约束的性能对比及替代方案探讨

核心逻辑先明确 #

你要阻止的是同一用户重复发现同一首歌，所以核心是保证user_id + song_id的组合唯一——毕竟一个用户不可能“发现”同一首歌两次，discovery_date只是这个事件的附属属性，不需要纳入唯一性判断（反而如果把它加进去，会误允许同一用户同一歌在不同时间的重复记录，违背需求）。

复合主键 vs 唯一约束：对比&最佳实践 #

1. 复合主键（推荐场景：单纯记录发现事件） #

直接把user_id和song_id设为复合主键，这是最贴合业务语义的方案：

CREATE TABLE song_discoveries (
    user_id INT NOT NULL,
    song_id INT NOT NULL,
    discovery_date DATETIME NOT NULL,
    PRIMARY KEY (user_id, song_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (song_id) REFERENCES songs(id)
);

• 语义清晰：user_id + song_id本身就能唯一标识一条“用户发现歌曲”的记录，完全符合主键的定义。
• 性能优势：InnoDB引擎下主键默认是聚簇索引，查询某个用户的所有发现记录、或某首歌的发现用户列表时，聚簇索引的遍历效率比普通索引更高，尤其数据量上来后差异会更明显。

2. 唯一约束（推荐场景：后续需扩展关联） #

如果后续业务可能需要给这条发现记录加其他关联（比如关联收藏、分享操作），可以加一个自增主键，同时给user_id + song_id加唯一约束：

CREATE TABLE song_discoveries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    song_id INT NOT NULL,
    discovery_date DATETIME NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (song_id) REFERENCES songs(id),
    UNIQUE KEY (user_id, song_id)
);

• 灵活性更高：自增id作为单一主键，后续其他表关联时更方便（比如favorite表存discovery_id）。
• 性能差异：唯一约束会创建普通唯一索引，查询时比聚簇索引多一层跳转，但中小规模数据下基本感知不到差异。

⚠️ 别做的事：给所有三列加唯一约束
如果把user_id + song_id + discovery_date设为唯一约束，相当于允许同一用户同一歌在不同时间重复插入，这完全违背了你“用户仅能发现同一歌曲一次”的需求，绝对不要这么做。

可行的辅助方案 #

• 业务层校验+数据库约束：插入前先查一遍该user_id + song_id是否存在，不存在再插入。但必须配合数据库层面的约束（主键/唯一约束），否则并发场景下会出现重复插入的问题。
• 插入时用INSERT IGNORE或ON DUPLICATE KEY UPDATE：不管用哪种方案，都可以用这两个语法处理重复插入的情况，比如：

INSERT INTO song_discoveries (user_id, song_id, discovery_date)
VALUES (1, 100, NOW())
ON DUPLICATE KEY UPDATE discovery_date = NOW(); -- 可选更新时间，或者什么都不做也可以

内容的提问来源于stack exchange，提问作者Yosif Taibi