复合主键怎么设计

复合主键（Composite Primary Key，也叫联合主键）是指由多个字段组合来唯一标识一行记录的主键。下面从设计原则、常见场景、示例、优缺点几个方面讲清楚。

一、什么时候用复合主键？

✅ 适合以下场景：

• 表中没有天然的单一唯一标识
• 数据本身具有多维度组合关系
• 多用于关联表 / 中间表 / 明细表

❌ 不适合：

• 需要频繁作为外键被引用
• 主键字段会变化
• 业务上后期可能拆分或扩展

二、复合主键设计原则

1️⃣ 字段数量尽量少（2~3 个最佳）

• 太多字段 → 索引大、性能差
• 外键引用复杂

2️⃣ 字段稳定、不可变更

• 主键一旦变更代价极高
• 避免用“状态”“数量”等可变字段

3️⃣ 字段类型尽量小

• 优先：INT、BIGINT、CHAR(固定)
• 避免：TEXT、VARCHAR(大)、JSON

4️⃣ 顺序有讲究（很重要）

复合索引遵循最左前缀原则

例如：

PRIMARY KEY (user_id, role_id)

✅ 能命中索引的查询：

• user_id = ?
• user_id = ? AND role_id = ?

❌ 不能命中：

• role_id = ?

三、常见设计示例

示例 1：用户角色关系表（典型）

CREATE TABLE user_role (
    user_id BIGINT,
    role_id BIGINT,
    created_at DATETIME,
    PRIMARY KEY (user_id, role_id)
);

✔ 含义：一个用户可以有多个角色
✔ 天然唯一

示例 2：订单明细表

CREATE TABLE order_item (
    order_id BIGINT,
    product_id BIGINT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

✔ 一个订单中一种商品只能出现一次

示例 3：日志 / 时序类

PRIMARY KEY (device_id, event_time)

⚠ 注意：时间字段做主键时，要确保精度足够（避免重复）

四、复合主键 vs 代理主键

方案 A：复合主键

PRIMARY KEY (user_id, role_id)

方案 B：代理主键（推荐在复杂系统中）

id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT,
role_id BIGINT,
UNIQUE (user_id, role_id)

✅ 优点：

• 外键简单
• 扩展性强

❌ 缺点：

• 多一个字段
• 需要额外唯一约束

五、常见坑 ⚠

1. 用业务字段当主键，后期业务变了
2. 字段顺序设计错，导致索引失效
3. 在外键里引用复合主键，层级太深
4. 复合主键里包含 NULL（不允许）

六、简单判断口诀

✅ 表是“关系型 / 明细型” → 可考虑复合主键
✅ 表是“核心业务实体” → 用单字段主键更稳
✅ 不确定 → 用代理主键 + 唯一索引

如果你有具体业务表结构，我可以直接帮你设计复合主键方案。