1. 先搞清楚一个老生常谈的问题它俩到底是不是一回事做了这么多年数据库相关工作几乎每隔一段时间就能看到有人在论坛或者技术群里问主键和唯一索引到底有什么区别是不是只要建了唯一索引就能当成主键用甚至有些刚入行的同学直接把这两个概念画上等号。我先直接给结论主键和唯一索引在底层存储上确实有不少交集但在逻辑语义、约束力度、执行计划和数据组织方式上有本质区别。如果你只是背下来“主键不能为空、唯一索引可以有空值”这种表面答案那遇到线上性能问题或者数据一致性事故的时候还是会一头雾水。这篇内容适合谁看适合正在做表结构设计、写SQL优化、处理数据去重或者排查慢查询的同学。不管你是用MySQL、PostgreSQL还是Oracle核心原理都相通我会把通用逻辑讲清楚同时针对主流数据库的差异做补充说明。先说个生活化的类比帮你建立直觉。主键就像一个人的身份证号全国唯一、一人一个、并且从出生起就固定不变你用它来唯一定位一个人。唯一索引更像是工号或者学号它也要求唯一但你可以在不同公司用不同工号甚至可以用手机号去关联一个人——它服务于某个具体业务场景而不是从“身份本质”上去定义这个人。这背后的差异远比一句“唯一索引允许NULL”要深刻得多。2. 概念与机制拆解主键和唯一索引各自的底层逻辑2.1 主键的本质它不只是一个索引主键从关系模型诞生的第一天起就是一个逻辑概念。它表示“用哪一个或哪几个字段来唯一标识一条记录”。在数据库实现上主键会伴随一个唯一索引但这个唯一索引只是主键的“物理载体”主键本身还带了更多约束非空约束NOT NULL主键字段不允许出现NULL值因为NULL代表“未知”不能用未知值去唯一标识任何东西。唯一性约束UNIQUE表内任何两行的主键值不能相同。每张表只能有一个主键这是关系模型的硬性规定。当然主键可以由多个字段组成叫复合主键但它仍然只能存在一个。默认作为聚簇索引的键在InnoDB这类使用聚簇索引的存储引擎里主键直接决定了数据在磁盘上的物理排列顺序。也就是说表里的数据行是按照主键值排序存储的这个特点对写入性能和范围查询有非常大的影响后面会详细展开。从用户角度来看主键存在的意义是保证每一行都是可区分的实体。如果你插入两条主键相同的数据数据库会直接报错这个约束严格到没有任何商量余地。2.2 唯一索引的本质它是为了业务规则服务的唯一索引是一个物理对象。它存在的目的是在某个或某几个字段上建立“唯一性规则”防止业务数据出现重复。比如用户表里的手机号字段你不想让两个人注册同一个手机号那就建一个唯一索引。与主键明显的几个不同点允许NULL值在MySQL的InnoDB里唯一索引允许存在多个NULL值因为NULL和NULL之间并不被视为相等。在PostgreSQL里同样如此。这引发了一个值得注意的问题如果业务上某字段“要么为空、要么全班唯一”唯一索引是能直接满足的但主键做不到。一张表可以有多个唯一索引你可以给手机号建一个给身份证号再建一个给邮箱再建一个它们互不冲突各自独立保证唯一性。默认不改变数据物理排列唯一索引是二级索引Secondary Index它单独维护一份索引结构索引里存放的是“索引值主键引用”或者说行指针数据本身的物理存储顺序不受它控制。所以从设计意图上看唯一索引更像是一个业务规则校验器专门用来回答“这个字段的值是否允许重复”这个问题。而主键回答的是“这一行是什么”。2.3 一张表只能有一个主键但主键不一定只有一个字段这里有个细节很容易被忽略。主键可以是一个字段也可以是多字段组合。比如订单明细表里用“订单ID商品ID”作为复合主键是常见做法。在复合主键的场景下主键约束的“唯一性”是对组合值而言的不是对单个字段而言的。这带来一个连锁影响主键越复杂索引体积越大写入时维护成本也越高。因为所有二级索引的叶子节点都要引用主键值主键字段多、类型长每个二级索引都会被迫变大。这也是为什么业内经验强烈推荐“单字段、自增、短类型”作为主键的原因——不是因为它难而是因为它性价比最高。3. 二者真正的核心差异从约束语义到存储结构3.1 约束语义的对比身份证号 vs 业务编号用表格来看会更直观对比维度主键Primary Key唯一索引Unique Index逻辑定位实体的唯一身份标识业务字段的唯一性校验是否允许NULL绝对不允许一般允许视数据库而定单表数量只能有一个可以有多个是否必然改变数据排列是聚簇索引场景下否能否被外键引用可以可以但前提是它本身也是唯一约束删除/修改难度涉及面广影响物理存储相对独立可在线处理是否自动创建索引是而且是数据库自动完成是但你也可以显式创建作用范围整行唯一的保证某个字段或字段组合唯一的保证这里面最核心的一句话主键是表结构设计的根基唯一索引是业务规则的延伸。如果你的表结构设计得合理主键就是一个你不会去动它的稳定锚点而唯一索引则是可以随时根据新业务需求动态加减的弹性规则。3.2 物理存储差异聚簇索引和二级索引的分水岭在MySQL的InnoDB存储引擎里主键就是聚簇索引。什么叫聚簇索引意思是数据行的物理存储顺序和索引顺序一致表数据本身就存放在主键索引的B树叶子节点上。你按照主键范围查数据InnoDB只需要顺序扫描这一段物理区域效率极高。而普通唯一索引是二级索引索引的叶子节点只存放索引键值和对应的主键值。你要通过某个唯一索引字段查数据流程是先在二级索引的B树里查到匹配的主键值然后回表根据主键再去聚簇索引里找完整行数据这叫回表查询。要不要回表以及回表多少次对查询性能影响极大。举个实际例子-- 假设用户表有主键 id唯一索引 uk_mobile 在 mobile 字段上 -- 这条查询只需要 mobile 和 id 两个字段 SELECT id, mobile FROM user WHERE mobile 13800138000;因为id和mobile都存在于唯一索引uk_mobile的索引树上MySQL可以直接从二级索引里拿到结果不需要回表这叫做覆盖索引优化。但如果这条查询还要查nickname字段SELECT id, mobile, nickname FROM user WHERE mobile 13800138000;那二级索引里没有nicknameMySQL就必须拿着查到的id值回到聚簇索引里再找一次完整行数据。数据量大时大量的回表会拉高查询延迟。理解了这一点你也就明白了为什么主键选型会影响整张表的性能上限。如果用随机字符串比如UUID做主键InnoDB在插入新行时数据页会因为随机值导致频繁的页分裂和碎片化写入性能会明显劣于自增整数主键。而如果用自增整数做主键新行总是追加到B树的末尾页分裂几乎不会发生写入性能非常稳定。我实测过一张千万级数据量的表把UUID主键换成自增整数主键后纯插入吞吐提升了差不多3到4倍同时表占用空间也减少了大概25%到30%。这就是聚簇索引选型对存储和性能造成的真实影响。3.3 逻辑层面的影响对数据完整性的控制力不同主键约束是数据库对外承诺的“数据完整性基线”。你只要声明了主键数据库就会自动做三件事建唯一索引、加非空约束、指定聚簇索引键。这相当于替你做了全部决定不给你偷懒的机会。唯一索引则更像“局部规则”。你可以在一个已经有主键的表上给身份证号、手机号、邮箱分别建唯一索引保证各自的业务唯一性而不影响整张表的数据组织方式。举个例子。假设订单表结构如下CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id INT NOT NULL, status TINYINT NOT NULL, created_at DATETIME NOT NULL, UNIQUE KEY uk_order_no (order_no), KEY idx_user_created (user_id, created_at) );主键id保证每一行订单都有独立标识。唯一索引uk_order_no保证订单号不重复这是业务刚需。索引idx_user_created用于加速用户维度的查询。你会发现这三件事的职责完全不同互相不能替代。如果你试图用order_no做唯一索引来替代主键虽然也能保证行不被重复插入但整张表缺少了稳定的聚簇索引键数据物理组织会变得混乱外键、关联查询、备份恢复都会受影响。生产环境里“用业务字段当主键”带来的教训太多了。最典型的就是手机号做主键手机号会变、可以注销、还能被运营商回收重新放号。一旦发生变更你要面对的不只是UPDATE一行数据而是所有引用这个主键的外键表、二级索引全部要跟着更新。而用自增id做主键手机号就只是一个普通业务字段随便改数据库层面完全不受影响。4. 实际工程场景里头怎么选怎么用4.1 什么时候必须用主键只要一张表要存储业务实体数据就应该有主键。下面这些场景尤其严格需要被其他表外键引用时主键是外键的唯一合法参照当然唯一索引也能被引用但实践上几乎都是主键。**需要精确数据同步、数据对比如binlog、CDC工具**时主键是定位一条记录的关键依据。没有主键的表在同步时经常出现漏数据或者重复数据。需要快速点查单行记录时主键查询走聚簇索引通常最快。需要保证每一行都有稳定身份时比如日志表虽然你可能会疑惑日志表也需要主键吗答案是建议有哪怕它只是自增id。没有主键的日志表在后续做数据清理、去重分析、关联查询时处处掣肘。4.2 什么时候加唯一索引更合适唯一索引的应用场景比很多人想得更宽只要业务上有“唯一”诉求但又不适合放在主键位置上的字段都适合用唯一索引业务账号类字段手机号、邮箱、身份证号、微信号、user_name等它们唯一但可变更。防重提交场景比如支付表中用“订单号操作类型”做唯一索引防止重复支付回调处理时插入两条一样的记录。冗余字段的唯一性兜底比如在分库分表后全局ID已经用分布式ID生成器保证了唯一但为了安全起见仍然会在分表里对全局ID字段建唯一索引防止数据重复。业务上不要求非空的唯一语义比如一个“升级优惠券码”字段用户可能没领过那么字段为空领过的用户每人只能领一个这时候用普通字段搭配唯一索引天然合适。很多人忽略的一点是唯一索引也是优化器的重要统计信息来源。MySQL优化器在执行SQL时会利用唯一索引的高区分度来评估行数从而选出更优的执行计划。如果一个字段有大量重复值但没建索引优化器可能会误判行数选了全表扫描。4.3 复合唯一索引的实际用法复合唯一索引不只是“多个字段一起唯一”这么简单它实际上定义了一个去重规范。比如CREATE TABLE user_follow ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, follow_user_id INT NOT NULL, created_at DATETIME NOT NULL, UNIQUE KEY uk_user_follow (user_id, follow_user_id) );这张表记录用户关注关系唯一索引保证了同一用户不能重复关注同一个人。这种写法比在代码里先SELECT再INSERT要可靠得多因为它把并发场景下的重复问题交给数据库层解决天然防重。这里有个设计细节id这个自增主键在user_follow表里其实是有点冗余的因为唯一索引本身可以充当行标识。但保留一个主键仍然有价值——如果你后续要做数据修复、批量更新、或者需要按主键定位记录会方便很多。这也是我在实际设计里倾向于保留主键加唯一索引组合的原因。4.4 再说说MySQL和Oracle在实现上的差异同一种概念在不同数据库里实现细节其实有明显差异踩过坑的人才知道滋味。在MySQL的InnoDB中主键强制非空且自动成为聚簇索引唯一索引默认不改变数据物理排列。但MyISAM存储引擎不太一样MyISAM不支持聚簇索引无论主键还是唯一索引都只是独立的索引文件数据存储是堆式Heap的与索引顺序无关。这意味着在MyISAM表上主键的“物理排列优势”是不存在的。好在现代MySQL默认使用InnoDBMyISAM基本已经退出生产主流。在Oracle中主键和唯一约束的实现也很有意思。Oracle里主键实际上就是一个“非空唯一约束”但它会连同唯一约束一起创建一个同名的唯一索引。在Oracle中主键无效化disable之后主键约束本身被禁用但底层唯一索引可能保留也可能被删除这取决于你禁用约束时是否选择保留索引。很多DBA在这上面吃过亏禁用了主键约束但没让索引一起失效结果数据插入了一些重复值之后想重新启用主键约束时发现已经无法启用了因为底层数据已经不满足主键的唯一性要求了。PostgreSQL的逻辑又略有不同。在PostgreSQL里主键约束和唯一约束在功能上高度接近但如果你在某个字段上建了主键系统会自动创建唯一索引并强制非空而普通唯一索引则没有非空要求。PostgreSQL允许对唯一索引做部分索引、表达式索引等高级操作灵活性比MySQL更高一些。所以在跨数据库迁移时千万别以为主键和唯一索引的语义可以一比一平移。从Oracle迁到MySQL如果你的原表里主键是允许NULL的虽然Oracle里理论上不会出现这种情况或者用了复合主键带漂移字段迁移过去之后MySQL可能直接拒绝建表。5. 索引失效与优化真正影响线上的细节5.1 哪些场景会导致索引用不上这个问题几乎是所有数据库面试里必问的但在实际工作中真正理解它并能在写SQL时提前规避的人并不多。我梳理一下最常见的索引失效场景并解释失效的底层原因对索引列使用函数或计算比如WHERE DATE(created_at) 2024-01-01这会让索引失效因为你改变了索引列的值。正确做法是改写成范围查询WHERE created_at 2024-01-01 AND created_at 2024-01-02。隐式类型转换索引字段是varchar类型查询条件是整型数字MySQL会在比较时把字段转成数字一转换就索引失效。比如WHERE mobile 13800138000当mobile是varchar时这种写法很可能索引失效。前导模糊匹配LIKE %abc肯定失效因为B树索引是按前缀排序的无法从不确定的起点开始搜索。LIKE abc%则可以使用索引。联合索引未遵循最左前缀原则比如索引是(a, b, c)你查询只用b和c索引基本用不上。优化器的选择是必须在a上有等值条件才可以顺利用上这个联合索引。对索引列进行运算WHERE id 1 10和WHERE id 9看起来结果一样但前者索引失效。NULL值的处理有些数据库里对允许NULL的列做IS NULL查询是可以走索引的比如MySQL 8.0.21之后对IS NULL有额外优化但从经验上看业务字段尽量设置NOT NULL更有利于索引选择也能省去很多“NULL还是空字符串”的语义纠缠。5.2 用EXPLAIN实操判断索引是否生效纸上谈兵没用我们直接看实际SQL的执行计划。下面是一个典型的场景-- 表结构 CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, mobile VARCHAR(20) NOT NULL, nickname VARCHAR(50), created_at DATETIME NOT NULL, UNIQUE KEY uk_mobile (mobile) ); -- 正常查询 EXPLAIN SELECT * FROM user WHERE mobile 13800138000;执行计划里key字段会显示uk_mobiletype为const或者ref说明走了唯一索引且效率极高。再看另一个查询-- 对索引列做了函数操作 EXPLAIN SELECT * FROM user WHERE LEFT(mobile, 3) 138;执行计划里key变成NULLtype为ALL说明全表扫描已经发生。这就是函数操作导致索引失效的直接证据。真正排查慢查询时EXPLAIN是我最常用的工具。遇到线上SQL变慢第一步就是EXPLAIN而不是瞎猜或重启。5.3 死锁与唯一索引的隐蔽关系唯一索引还有一个很少被提及但非常实际的隐患并发插入时容易引发死锁。这在做订单、支付类高并发系统时尤其常见。举个具体场景。支付回调接口同时收到两条针对同一订单的重复请求都向支付流水表插入记录。表结构如下CREATE TABLE pay_log ( id INT PRIMARY KEY AUTO_INCREMENT, order_id VARCHAR(32) NOT NULL, amount DECIMAL(10, 2) NOT NULL, status TINYINT NOT NULL, UNIQUE KEY uk_order (order_id) );两个并发事务同时尝试插入相同order_id的记录。InnoDB在插入时会对唯一索引的gap加锁间隙锁用于检查唯一性冲突。如果两个事务在不同间隙插入然后由于某种原因需要互相等待对方释放间隙锁就会形成死锁。MySQL会检测到死锁并回滚其中一个事务但高并发下频繁的死锁会让业务重试量剧增。我处理过的真实案例里这种死锁发生频率可能每小时几十次。排查方式是通过SHOW ENGINE INNODB STATUS查看最近的死锁信息确认锁等待链。解决思路也不是去关闭唯一索引它必要而是调整业务逻辑先对订单级别加分布式锁或者对同一订单的检查-插入流程做串行化处理减少锁竞争窗口。5.4 数据迁移和归档时主键的唯一性要格外盯紧还有一个实操细节。你在做数据迁移、分表归档时主键和唯一索引容易出现两类问题。第一类从旧表迁移到新表时如果新旧主键生成策略不一致比如旧表是业务编号做主键新表改成自增id那么迁移脚本里必须维护新旧主键的映射关系。很多人漏了这一步导致关联数据全部串号。第二类分库分表后唯一索引的分片键选择很关键。如果你把唯一索引建立在非分片键字段上插入时无法在单个分片上完成唯一性校验要么依赖分布式ID方案从源头保证唯一要么就得接受“最终一致、允许短暂重复”的妥协。这在设计阶段就应该想清楚不要等到上线后才发现唯一约束形同虚设。6. 常见问题排雷与经验补丁6.1 常见问题速查表问题现象可能原因解决方案主键是自增的但中间跳号了插入冲突或回滚导致自增值消耗属于正常现象不必修复若业务要求主键严格连续需改造生成策略唯一索引建在可空字段上多个NULL不冲突业务期望“全表唯一”但没有实现业务上若必须“非NULL时唯一”可配合触发器或改用字段默认空串线上误删了唯一索引重复数据开始混入先全表扫描定位重复行清理后再重建带唯一约束的索引主键改为唯一索引后查询变慢数据由聚簇存储变为堆式存储重新建立主键让表回到聚簇索引组织模式两张表join用唯一索引但速度慢唯一索引类型不匹配varchar vs int统一字段类型和排序规则修改主键字段时报错外键或引用表阻止变更按顺序先移除关联约束改完再重建6.2 自增主键被删掉后真的需要重建吗有一种情况是为了“优化写入”直接把原有主键删掉只保留唯一索引。这几乎是灾难性的操作。在InnoDB里删掉主键意味着表退化成使用隐藏主键的堆表排列二级索引全部要重建曾经有序的数据物理组织被打乱查询性能很可能不升反降。我见到过一例某团队为了“少写一点主键数据”把一张几千万行的流水表主键删了只留唯一索引。上线没几天线上高峰期经常出现CPU飙升。后来排查发现所有关联表都要回表查二级索引因为底层主键引用失效聚簇能力荡然无存。最后花了两天时间重建表结构才恢复。这就是典型的只看表层语义、不理解物理结构而埋下的雷。6.3 唯一索引用来实现“有则更新无则插入”MySQL的INSERT ... ON DUPLICATE KEY UPDATE和PostgreSQL的ON CONFLICT ... DO UPDATE都是依赖唯一索引或主键来实现upsert语义的。使用时要注意触发更新的唯一键可以是主键也可以是唯一索引。很多人在这个环节会踩坑比如唯一索引包含了多个字段ON DUPLICATE时只指定部分字段可能因为组合唯一约束没匹配上而更新错行。-- MySQL中通过唯一索引实现upsert INSERT INTO user (id, mobile, nickname) VALUES (1, 13800138000, 张三) ON DUPLICATE KEY UPDATE nickname VALUES(nickname);这个SQL依赖的主键或 uk_mobile 只要有一个存在冲突就会走更新路径。如果你的表里同时存在多个唯一索引而你期望的冲突检测是针对某一个特定的唯一索引那么用这种语法存在误触发的可能务必要小心。6.4 关于索引失效的老问题再补一个容易被忽略的案例联合索引最左前缀原则大家都很熟了但有一个场景容易被忽略范围查询字段在中间。比如联合索引是(a, b, c)查询条件是WHERE a 1 AND b 100 AND c 5。此时b是范围条件c的等值条件无法继续使用索引因为在B树中范围查询一旦开始后面的字段就已经无法继续按索引有序匹配了。所以设计联合索引时要把等值条件字段放在前面范围条件放在后面。另一个容易被忽略的坑是IN和OR也可能会让索引失效但这不是绝对的。在MySQL 8.0里优化器对IN列表做了不少优化可以做到range扫描。真正需要警惕的是OR连接两个不同列的等值条件尤其是其中一列有索引另一列没有时MySQL可能选择全表扫描。6.5 补充主键和唯一索引在备份、恢复时的差异备份恢复场景里两者也有区别。全库备份的binlog回放是基于主键定位行的。如果没有主键MySQL在回放UPDATE或DELETE语句时会退化成全表扫描逐行匹配恢复速度慢得惊人。曾经遇到一个极端案例一张几十万行的表没有主键恢复时整整跑了几个小时而同样数据量有主键的表只需几分钟。所以在做表结构设计时哪怕是一张临时表我也建议至少有个主键这不是教条而是经验的沉淀。7. 实操中我个人常用的几个选型原则多年做数据库设计和优化的经验最后沉淀下来其实就是几条朴素的判断准则分享给你参考。第一条凡是业务实体表无条件要有主键。主键优选自增整数其次雪花ID、分布式ID最后才是业务字段。第二条业务上需要保证唯一但兼具可变更性的字段用唯一索引而不是主键。第三条唯一索引的数量不要贪多。每个唯一索引都意味着写入时的额外唯一性检查开销索引文件也占用磁盘和内存。一张表三个以内唯一索引是常见状态再多就要反思业务设计是否合理。第四条创建唯一索引时一定要确定好字段是否允许NULL。如果业务含义是“未填写则为空”允许NULL并用唯一索引没问题如果是业务上必须存在就加NOT NULL约束避免应用层出现脏数据。第五条做SQL优化时EXPLAIN看执行计划是最可靠的第一步。不要凭感觉判断索引是否生效数据会告诉你答案。最后再分享一个我在实际排查中养成的习惯每次新SQL上线前先扔到测试环境的慢查询日志或者EXPLAIN里看一眼确认执行计划没有全表扫描、没有filesort、没有临时表再允许发版。这种前置检查虽然多花几分钟但换来的线上稳定性是实实在在的。数据无小事索引选型和解法多一分理解线上就能少一分折腾。
