一、真实的入场券：为什么要用复合主键
电商系统的订单明细表在深夜突然抛出新需求：同一订单内商品条目不能重复。技术方案会上，王工拿出这样的建表语句：
-- SQLite 3.39.4 示例
CREATE TABLE order_items (
order_id INTEGER NOT NULL,
sku VARCHAR(12) NOT NULL,
quantity INTEGER CHECK(quantity > 0),
-- 组合主键相当于保险库的双指纹认证
PRIMARY KEY (order_id, sku)
);
-- 违反主键约束的插入会被立即阻止
INSERT INTO order_items VALUES (1001, 'IPHONE_14_PRO', 2);
INSERT INTO order_items VALUES (1001, 'IPHONE_14_PRO', 1); -- 触发主键冲突
当多个字段的组合才能确保数据唯一性时，复合主键就像精确的分子锁，相比自动递增的代理键，直接使用业务键的组合更能保证天然的数据约束。某社交平台的活动参与记录表就吃过单主键的亏——用户ID+活动ID的组合重复导致数据污染，后来改用复合主键才从根源解决问题。
二、构造联合密钥：设计原则拆解
2.1 字段选择三定律
物流轨迹追踪表的设计案例颇具代表性：
CREATE TABLE parcel_tracking (
logistics_no CHAR(18) NOT NULL, -- 主运单号
sub_no CHAR(2) NOT NULL, -- 分拣批次号
station_code CHAR(6), -- 站点编码
scan_time DATETIME, -- 扫描时间
PRIMARY KEY (logistics_no, sub_no)
);
设计阶段需要警惕的三大风险点：
- 不可变原则：主键字段要像身份证号终身不变，避免因字段修改导致的级联更新
- 最小化原则：某P2P平台把用户ID+标ID+投标时间组成主键，结果索引膨胀三倍
- 离散性原则：短视频平台曾用连续递增的直播间ID+用户ID组合，导致B树结构失衡
2.2 索引的共生关系
在SQLite中，复合主键本身就是一个聚簇索引。某资讯平台的热榜数据表就利用了这个特性：
-- 热点新闻排行表
CREATE TABLE hot_news (
category_id INTEGER, -- 新闻分类
heat_score INTEGER, -- 热度值
news_id INTEGER,
PRIMARY KEY (category_id, heat_score DESC)
) WITHOUT ROWID; -- 显式指定使用聚簇索引存储
-- 查询分类下实时Top10
SELECT news_id FROM hot_news
WHERE category_id = 5
ORDER BY heat_score DESC
LIMIT 10; -- 完美利用主键索引的有序性
WITHOUT ROWID选项让表结构变成纯索引组织表，这对高频范围查询场景就像开启涡轮增压。但要注意这种设计会牺牲部分插入性能，某实时日志系统就因此遭遇写入瓶颈。
三、性能的阴阳两面：速度与空间的博弈
3.1 查询加速的黄金通道
某连锁门店的销售明细表在使用复合主键后，月报生成速度提升8倍：
-- 原始单主键结构
CREATE TABLE sales_old (
id INTEGER PRIMARY KEY,
store_id INTEGER,
sale_date DATE,
amount REAL
);
-- 优化后的复合主键结构
CREATE TABLE sales_new (
store_id INTEGER,
sale_date DATE,
cashier_id INTEGER,
amount REAL,
PRIMARY KEY (store_id, sale_date) -- 查询条件完全匹配左前缀
);
-- 典型查询场景
EXPLAIN QUERY PLAN
SELECT SUM(amount) FROM sales_new
WHERE store_id = 1024 AND sale_date BETWEEN '2023-01-01' AND '2023-01-31';
执行计划显示后者完美命中主键索引，而前者需要全表扫描。但代价是主键字节数从4字节增加到8字节，整体存储增加12%。
3.2 批量插入的隐藏陷阱
测试对比揭示惊人的性能差异：
| 主键类型 | 数据量 | 插入耗时 | 索引大小 |
|---|---|---|---|
| 复合主键 | 10万 | 4.2s | 4.8MB |
| 自增主键 | 10万 | 1.7s | 2.1MB |
当主键字段总长度超过6字节时，索引维护成本呈指数增长。某物联网项目中的传感器数据表，复合主键导致写入QPS从5000骤降到800，后采用分表策略才解决。
四、关联技术的交响乐
4.1 与外键的配合演出
在库存管理系统中看到经典配合：
CREATE TABLE warehouses (
region_code CHAR(2),
wh_code CHAR(4),
PRIMARY KEY (region_code, wh_code)
);
CREATE TABLE inventory (
region_code CHAR(2),
wh_code CHAR(4),
sku VARCHAR(12),
stock INTEGER,
FOREIGN KEY (region_code, wh_code)
REFERENCES warehouses(region_code, wh_code),
PRIMARY KEY (region_code, wh_code, sku)
);
这种设计保证库存记录必须指向有效仓库，某电商大促期间的外键验证耗时占比从18%降到5%。
4.2 与覆盖索引的互补
在用户行为分析中的精妙应用：
CREATE TABLE user_actions (
user_id INTEGER,
action_date DATE,
action_type VARCHAR(20),
device_hash CHAR(32),
-- 主键负责快速定位
PRIMARY KEY (user_id, action_date DESC)
);
-- 创建覆盖索引提升特定查询
CREATE INDEX idx_cover ON user_actions(action_type, device_hash);
-- 高频查询可以直接走索引
SELECT user_id FROM user_actions
WHERE action_type = 'login' AND device_hash = 'a1b2c3...'
复合主键与覆盖索引的组合，让某社交平台的用户行为分析查询速度提升3倍。
五、生存指南：避坑要点汇总
某国企ERP系统踩过的三个典型雷区：
- 动态字段入主键：将订单状态加入主键，导致主键频繁变更
- 大字段参与主键：用BASE64编码的图片哈希做主键，索引体积爆炸
- 忽视业务时态：未在会员有效期表中加入时间范围，导致历史数据混乱
实践中的生存法则：
- 主键字段总长度控制在16字节以内
- 复合主键的最左前缀需要命中80%的查询条件
- 定期用
ANALYZE命令更新统计信息 - 并发写入场景考虑
BEGIN EXCLUSIVE事务
六、技术选型的灵魂拷问
适合复合主键的三大经典场景：
- 交易流水类的天然业务键组合
- 时序数据需要范围查询的场景
- 多租户系统的数据隔离需求
而当遇到这些情况时请三思：
- 字段存在NULL值可能
- 主键需要定期归档清理
- 高频单字段精确查询场景
某金融系统在账户表上采用复合主键后，联合查询效率提升40%，但批量开户耗时增加120%，最终通过读写分离解决矛盾。