1. 联合主键到底是个什么东西第一次接触“联合主键”这个词很多人脑子里冒出来的画面大概是把两个字段拼在一起当身份证用。这个直觉其实挺准的但只对了一半。联合主键Composite Primary Key / 复合主键的本质是把表中两个或两个以上的列组合起来作为一个整体来唯一标识一行记录。注意是“组合起来”唯一而不是“每一列都唯一”。举个最经典的例子订单明细表。一张订单里有多个商品同一个商品也可能出现在多张订单里。你单独拿订单ID出来它重复单独拿商品ID出来它也重复。但“订单ID 商品ID”这个组合在整张表里是唯一的。这就是联合主键最自然的落点。它解决的问题很具体当业务上没有任何一个单一字段能独立承担唯一标识的责任时用多个字段的组合来兜底。这跟给表硬加一个自增ID是两条路。自增ID是“人造主键”联合主键是“业务主键”。选哪条路取决于你对这张表的定位、后续的查询模式、以及数据一致性的要求。这篇文章适合谁看如果你写过CRUD但一遇到多对多关系就习惯性加自增ID如果你在面试里被问过“联合主键和唯一索引有什么区别”却答得含糊如果你正在设计一张中间表或者明细表拿不定主意那接下来的内容应该能帮你把这块知识补扎实。我会从设计动机、底层原理、实操建表、索引行为、踩坑记录几个角度把联合主键这件事讲透。2. 为什么要在设计阶段就想清楚主键策略2.1 联合主键与自增主键的路线之争很多人建表的第一反应是id INT AUTO_INCREMENT PRIMARY KEY然后所有业务字段都当普通列处理。这个习惯本身没错在大多数场景下甚至是更优解。但问题在于有些场景下自增主键会带来额外的负担而联合主键反而更贴合业务语义。我拿一个实际遇到过的场景来说。某系统里有一张“用户角色关联表”早期设计是自增ID做主键用户ID和角色ID都是普通列。结果上线三个月后发现同一对“用户-角色”被重复插入了十几次因为应用层没有做幂等判断数据库层也没有任何约束。后来改成联合主键用户ID, 角色ID重复插入直接报错问题从根上被堵住了。这就是联合主键的第一个核心价值它天然带有唯一性约束而且这个约束是业务语义级别的。自增主键只能保证“每一行不同”但保证不了“每一对业务组合不同”。你要额外加唯一索引才能达到同样效果那既然都要加为什么不直接让它当主键当然自增主键也有联合主键比不了的优势。比如InnoDB的聚簇索引特性决定了如果主键是自增整数插入永远是追加操作页分裂少性能好。而联合主键如果是字符串或者无序的数值插入时可能导致频繁的页分裂和索引重组。这个后面会展开讲。2.2 什么场景下联合主键是更自然的选择不是所有表都适合联合主键。我总结下来以下几类场景用联合主键会更顺多对多关系的中间表用户-角色、订单-商品、文章-标签这类表的业务语义就是“两个实体的组合”联合主键几乎是标准答案。明细表/从表订单明细、入库明细主表ID加行号或者主表ID加商品ID组合起来唯一。多租户场景下的业务表租户ID加业务编号保证不同租户之间编号可以重复但同一租户内不重复。版本化数据表文档ID加版本号每次修改产生新版本联合主键保证同一文档的版本号不冲突。反过来如果一张表有明确的单一业务编号比如身份证号、订单号或者数据量极大且写入频繁那自增主键或者雪花ID可能更合适。关键判断标准是你的业务里有没有一个“天然的组合唯一键”。有就用联合主键没有就别硬凑。2.3 联合主键对后续查询和索引的连锁影响选主键不是选完就完了它会连锁影响你的索引结构和查询性能。InnoDB里主键就是聚簇索引的键所有二级索引的叶子节点存的都是主键值。这意味着主键越长二级索引占的空间就越大。假设你用三个字段做联合主键每个字段平均20字节那主键就是60字节。一张表如果有5个二级索引每个二级索引的每条记录都要多存60字节的主键值。数据量上千万的时候这个空间放大效应非常可观。另外联合主键的字段顺序直接影响查询能否命中索引。这个在下一章会详细拆解。这里先建立一个认知联合主键不是“把几个字段标成主键”这么简单它决定了整张表的索引骨架。3. 联合主键的底层原理与索引行为3.1 联合主键在B树里是怎么排的要理解联合主键的行为得先理解它在B树里的排序规则。假设联合主键是(A, B, C)那排序规则是先按A排A相同再按B排B相同再按C排。这个“字典序”规则决定了一切。这意味着什么呢意味着你的查询条件如果只包含B和C不包含A那这个查询用不上主键索引的有序性。就像一本先按省份排、再按城市排、最后按街道排的电话簿你直接翻“中山路”是找不到的因为中山路在每个城市都有。这个特性叫“最左前缀原则”。它不只适用于联合主键也适用于联合索引。但联合主键的特殊之处在于它是聚簇索引数据行本身就挂在这个B树的叶子节点上。所以如果查询能命中主键的最左前缀效率极高直接定位到数据页。3.2 最左前缀原则在联合主键上的具体表现我用一个具体的表来演示。假设有张表CREATE TABLE order_detail ( order_id BIGINT NOT NULL, product_id BIGINT NOT NULL, quantity INT NOT NULL, price DECIMAL(10,2) NOT NULL, PRIMARY KEY (order_id, product_id) );下面几种查询命中索引的情况完全不同查询条件是否命中主键索引原因WHERE order_id 1001是命中联合主键最左列WHERE order_id 1001 AND product_id 2001是命中完整联合主键WHERE product_id 2001否跳过最左列无法使用有序性WHERE product_id 2001 AND order_id 1001是优化器会重排条件顺序最后一行值得注意虽然你写的顺序是product_id在前但查询优化器会自动调整成符合最左前缀的顺序。所以写SQL时条件顺序不影响但字段的“存在性”影响。注意如果联合主键是(A, B, C)查询条件是A和C那只能用到A的索引有序性C是用不上的。这时候C的条件会变成回表后的过滤条件而不是索引查找条件。3.3 联合主键与二级索引的交互关系InnoDB的二级索引叶子节点存的是“索引列 主键值”。如果主键是联合主键那每个二级索引的每条记录都会带上完整的联合主键。这带来两个后果第一二级索引会变“胖”。上面说过空间放大的问题这里不再重复。但还有一个隐性影响二级索引的B树会更高因为每条记录更大了同样大小的页能装的记录数变少树的高度可能增加一层。树高增加一层就多一次磁盘IO。第二覆盖索引的判定会变复杂。如果一个查询只需要二级索引的列和主键的列那它可以直接从二级索引返回不用回表。但如果联合主键有三个字段而你只需要其中一个那这个二级索引仍然会带上全部三个字段覆盖索引的“覆盖范围”比你想象的要大。我个人的经验是如果一张表要用联合主键尽量控制主键字段的总长度。能用INT就别用BIGINT能用SMALLINT就别用INT能用定长CHAR就别用VARCHAR。这不是抠门是在给二级索引减负。4. 联合主键的实操建表与配置4.1 建表时定义联合主键的两种写法定义联合主键有两种写法效果一样但风格不同-- 写法一列定义后统一声明 CREATE TABLE user_role ( user_id BIGINT NOT NULL, role_id INT NOT NULL, granted_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (user_id, role_id) ); -- 写法二列内联声明不推荐用于联合主键 CREATE TABLE user_role ( user_id BIGINT NOT NULL, role_id INT NOT NULL, granted_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (user_id, role_id) );实际上MySQL里联合主键只能用第一种写法因为PRIMARY KEY关键字后面跟多列时必须放在表级约束里。列级的PRIMARY KEY只能修饰单列。这一点跟某些数据库比如SQLite的语法略有差异迁移时要注意。字段顺序不是随便写的。我的习惯是把选择性高区分度大的字段放在前面把查询频率高的字段放在前面。如果两个条件冲突优先满足查询频率。因为索引的第一列决定了能否快速定位而选择性决定了定位后需要扫描的范围。4.2 联合主键的字段顺序怎么定字段顺序的决策可以拆成三个问题哪个字段最常单独出现在WHERE里把它放第一列。因为只有第一列能单独享受索引有序性。哪个字段的选择性最高选择性 不重复值数量 / 总行数。选择性越高索引过滤效果越好。通常放前面。哪个字段最短在满足前两个条件的前提下把最短的放前面减少索引体积。我拿一个实际案例来说明。某日志表联合主键候选是(tenant_id, log_date, log_type)。tenant_id选择性不高可能就几十个租户但它是所有查询的必带条件log_date选择性很高每天一个值但查询时不一定带log_type选择性中等。最终我选了(tenant_id, log_date, log_type)。理由是tenant_id虽然选择性低但它是多租户隔离的强制条件所有查询都会带。把它放第一列能保证所有查询至少命中索引的第一层。如果反过来把log_date放第一列那不带日期范围的查询就完全用不上索引了。4.3 联合主键与AUTO_INCREMENT的兼容性陷阱这里有一个很多人踩过的坑InnoDB要求自增列必须是索引的最左列。如果你把自增列放在联合主键的第二位MySQL会直接报错。-- 这样建表会报错 CREATE TABLE bad_example ( tenant_id INT NOT NULL, id INT AUTO_INCREMENT, PRIMARY KEY (tenant_id, id) ); -- ERROR 1075: Incorrect table definition; -- there can be only one auto column and it must be defined as a key正确的做法是把自增列放第一位或者干脆不用自增列。如果你的业务需要“每个租户内自增”那自增列不能直接用AUTO_INCREMENT得在应用层或者用触发器实现。这是联合主键设计时一个很实际的约束。实操心得我见过有团队为了“每个租户独立编号”把自增列放在联合主键第二位结果建表失败后改成普通索引但普通索引上的AUTO_INCREMENT在InnoDB里行为很微妙不建议这么干。更稳妥的方案是用单独的自增主键租户内编号用应用层生成。5. 联合主键在真实业务中的落地案例5.1 多对多中间表的联合主键设计多对多中间表是联合主键最经典的战场。我拿一个内容平台的“文章-标签”关系来举例CREATE TABLE article_tag ( article_id BIGINT NOT NULL, tag_id INT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (article_id, tag_id), KEY idx_tag (tag_id, article_id) );这里有两个索引主键(article_id, tag_id)和二级索引(tag_id, article_id)。为什么要建第二个索引因为业务上有“查某个标签下所有文章”的需求。如果只有主键这个查询用不上索引只能全表扫。注意二级索引的字段顺序是(tag_id, article_id)而不是(tag_id)。因为InnoDB的二级索引会自动带上主键值但如果你显式写出article_id这个索引就变成了覆盖索引查询“标签下的文章ID”时不用回表。这是一个很实用的优化技巧。这张表的写入模式是典型的“高频插入、低频更新、中频查询”。联合主键在这里的作用是防止重复关联同时为“查某文章的所有标签”提供聚簇索引支持。而二级索引为反向查询提供支持。两个索引各司其职。5.2 多租户SaaS系统中的联合主键实践多租户系统里联合主键的用法又不一样。假设有一张“项目表”每个租户有自己的项目编号体系CREATE TABLE project ( tenant_id INT NOT NULL, project_no VARCHAR(32) NOT NULL, project_name VARCHAR(128) NOT NULL, status TINYINT DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (tenant_id, project_no) );这里tenant_id放第一列是因为所有查询都会带租户条件。project_no放第二列保证同一租户内项目编号不重复。不同租户之间可以有相同的project_no这是业务允许的。这种设计的一个隐性好处是数据天然按租户聚簇。同一个租户的项目数据在物理上相邻存储查询某个租户的项目列表时磁盘IO是连续的。这在数据量大、租户多的场景下性能提升很明显。但也要注意一个问题如果某个大租户的数据量特别大会导致数据分布不均。这时候可以考虑在tenant_id前面再加一个哈希列或者用分区表按tenant_id分区。不过那是另一个话题了。5.3 联合主键在分库分表场景下的特殊考量分库分表之后联合主键的玩法会变。因为全局唯一性不能只靠单库的主键保证了。常见的做法是分片键进主键比如按user_id分片那主键就是(user_id, biz_id)。这样每个分片内的主键唯一性能保证跨分片的全局唯一性由分片键保证。全局ID生成器用雪花算法或者号段模式生成全局唯一ID联合主键退化为普通唯一索引。基因法把分片键的基因融入ID生成保证同一用户的订单落在同一分片。我个人的建议是如果已经决定分库分表联合主键的设计要提前考虑分片键。不要等分片了才发现主键里没有分片键导致跨分片查询无法避免。这是一个架构层面的决策不是建表时拍脑袋能解决的。6. 联合主键使用中的常见问题与排查6.1 联合主键导致的死锁问题联合主键在并发插入时死锁概率比单列主键高。原因在于联合主键的插入需要比较多个字段加锁的顺序和范围更复杂。我遇到过一个真实案例两个事务同时插入相邻的联合主键值比如(100, 1)和(100, 2)。在InnoDB的间隙锁机制下这两个插入可能互相等待形成死锁。单列自增主键因为插入总是追加在末尾几乎不会出现这种问题。排查这类死锁的方法是看SHOW ENGINE INNODB STATUS的输出找到LATEST DETECTED DEADLOCK段落分析两个事务的锁等待关系。解决方案通常有两个一是调整插入顺序让并发事务按相同顺序插入二是降低隔离级别到READ COMMITTED减少间隙锁。注意死锁不是联合主键的“bug”而是任何非顺序插入都可能遇到的问题。联合主键只是让这个问题更容易暴露。如果你的业务写入并发很高建议用自增主键或者有序的分布式ID。6.2 联合主键字段更新带来的连锁反应联合主键的字段一旦更新代价比普通列大得多。因为InnoDB需要删除旧行、插入新行同时更新所有二级索引。如果联合主键有三个字段更新其中一个所有二级索引都要重建对应的条目。更麻烦的是如果这个表被其他表通过外键引用更新主键可能导致级联更新。虽然生产环境一般不建议用外键但逻辑上的关联仍然需要应用层处理。我的经验是联合主键的字段应该是“一旦写入就不再变化”的。如果某个字段可能变就不要放进主键。比如“用户角色关联表”里user_id和role_id都不应该变变了就是删除重建。而“granted_at”这种时间字段放普通列就好。6.3 联合主键与唯一索引的取舍很多人会问既然联合主键和联合唯一索引都能保证组合唯一那用哪个我的判断标准是维度联合主键联合唯一索引唯一性约束强制且非空强制但允许NULL聚簇索引是数据按主键组织否是二级索引每表数量只能一个可以有多个外键引用可以被引用一般不被引用字段更新代价高中如果这个组合是表的“身份标识”用联合主键。如果只是“业务规则要求不重复”用联合唯一索引。比如“用户表的手机号”应该用唯一索引因为手机号不是用户表的身份标识用户ID才是。还有一个细节联合唯一索引允许NULL值而联合主键不允许。如果你的组合字段中可能有NULL那只能用唯一索引。但话说回来主键字段设计成允许NULL本身就不是好习惯。7. 联合主键的性能优化与替代方案7.1 什么时候该用自增主键替代联合主键联合主键不是银弹。以下几种情况我会建议用自增主键写入量极大且并发高自增主键的顺序插入特性对InnoDB最友好页分裂最少。主键字段总长度超过30字节二级索引的空间放大效应太明显。业务上不需要组合唯一性如果应用层已经保证了唯一性数据库层用自增主键更轻量。需要被其他表频繁引用单列主键做外键更简单JOIN效率也更高。替代方案很简单加一个id BIGINT AUTO_INCREMENT PRIMARY KEY然后把原来的联合字段建成UNIQUE KEY uk_xxx (col1, col2)。这样既保证了唯一性又享受了自增主键的写入性能。代价是多了一个索引但通常值得。7.2 用代理键加唯一索引的混合方案混合方案是我在大多数生产环境里推荐的CREATE TABLE user_role ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, role_id INT NOT NULL, granted_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_user_role (user_id, role_id), KEY idx_role (role_id, user_id) );这个设计里id是代理键负责聚簇索引和写入性能uk_user_role负责业务唯一性idx_role负责反向查询。三个索引各司其职没有一个是多余的。代价是存储空间比纯联合主键方案多了一个BIGINT列8字节乘以行数以及多维护一个唯一索引。但在大多数场景下这个代价换来的写入性能和灵活性是值得的。7.3 联合主键在分布式数据库中的变体在分布式数据库比如TiDB、OceanBase里联合主键的行为又不一样。这些数据库通常支持“聚簇索引表”和“非聚簇索引表”两种模式。如果声明为聚簇索引表联合主键的行为跟InnoDB类似如果是非聚簇索引表主键只是一个唯一约束数据按隐藏的RowID组织。在分布式场景下联合主键的一个关键考量是数据分布。如果联合主键的第一列是单调递增的比如时间戳那写入会集中在最后一个分片形成热点。解决方案是把随机性强的字段放第一列或者用哈希分区。我试过在一个分布式数据库里用(tenant_id, create_time)做联合主键结果发现写入热点严重因为create_time是递增的。后来改成(tenant_id, hash(create_time), create_time)把哈希值放第二列写入就均匀了。这个技巧在分布式场景下很实用。8. 我踩过的坑和给你的实操建议8.1 联合主键字段顺序选错的代价早期我做一张“设备状态表”联合主键是(device_id, status_time)。device_id是字符串status_time是时间戳。查询模式主要是“查某设备最近的状态”条件带device_id和status_time范围。这个设计本身没问题但我犯了一个错device_id用了VARCHAR(64)而且没有控制长度。结果这张表数据量到5000万时主键索引占了将近20GB二级索引也水涨船高。后来把device_id改成BIGINT应用层做了映射主键体积直接降到原来的四分之一。教训是联合主键的字段类型要抠到极致。能用数值就别用字符串能用小类型就别用大类型。这不是过早优化是索引设计的基本功。8.2 联合主键与ORM框架的配合问题用ORM框架时联合主键的映射比单列主键麻烦。以JPA为例需要用IdClass或者EmbeddedId来定义复合主键。MyBatis则需要手动处理id标签的多个字段。我遇到的一个坑是某些ORM框架在生成equals()和hashCode()时对联合主键的处理不一致导致缓存失效或者集合操作出错。解决方案是手动实现这两个方法确保基于所有主键字段。另一个坑是联合主键的表在ORM里做批量插入时框架可能无法正确判断“已存在”还是“新记录”导致重复插入或者更新失败。这时候要么用原生SQL要么在应用层做幂等控制。8.3 联合主键的监控与日常维护要点联合主键的表需要额外关注几个监控指标索引大小主键索引和二级索引的体积增长趋势。如果主键索引增长过快考虑字段类型是否合理。页分裂率SHOW ENGINE INNODB STATUS里的Pages split相关指标。如果页分裂频繁说明插入顺序不够有序。死锁频率联合主键的死锁概率更高需要定期检查死锁日志。慢查询特别关注那些“只带联合主键部分列”的查询它们可能用不上索引。日常维护方面OPTIMIZE TABLE对联合主键的表效果可能不明显因为聚簇索引的重建代价很大。更推荐用ALTER TABLE ... FORCE来重建或者用pt-online-schema-change这类工具在线操作。最后分享一个小技巧如果你不确定联合主键的字段顺序怎么定可以把候选方案都建出来用真实数据跑一遍EXPLAIN看哪个方案的rows和filtered最优。数据不会骗人实测比拍脑袋靠谱。