主键和唯一索引到底有什么区别这个问题做过两三年开发的人基本都撞上过面试被问一次线上排查重复数据又被绕晕一次。网上讲两者区别的文章不少但多数停在一张表只能有一个主键、唯一索引可以有多个、主键不能为空这种表面答案。这些当然没错可真正落地到建表、改表、排查慢查询和生产事故时光靠这几句话根本撑不住。这篇文章我用自己的实际经验来讲以 MySQL InnoDB 为主线穿插 Oracle 和 PostgreSQL 的具体行为最后给一套平时建表、加索引、清理重复数据时可以直接抄走的方案。顺便把踩过的坑一并倒出来大表改主键导致业务中断、软删除后唯一索引冲突、精心建好的索引因为一个函数查询直接失效。把这些场景走完一遍你对主键和唯一索引的理解会扎实很多。1. 先从本质上分清约束和索引不是一回事1.1 主键是一个约束很多文章一上来就对比主键 VS 唯一索引把两者放在同一层这本身就容易误导。主键在 SQL 标准里的定义非常明确主键约束PRIMARY KEY 非空约束 唯一约束它是一组完整性规则用来保证每一行都能被唯一标识。约束是逻辑概念它回答的是什么数据可以进入这张表的问题。主键约束决定了这一列或这几列的值不允许为空且不允许重复。至于数据库底层拿什么数据结构去实现这个约束主键约束本身并不关心。打个比方主键约束像是公司规定每个员工必须有唯一的工号且工号不能为空这是一个制度层面的规则。1.2 唯一索引是一个物理结构索引就完全是另一层的东西了。索引是一种独立的存储结构它的存在是为了加速查询、维护数据的有序性。唯一索引UNIQUE INDEX只是在这个物理结构上加了一层值不能重复的限定。索引是物理概念它回答的是数据怎么被快速找到的问题。唯不唯一只是它的一个属性真正干活的还是那颗 B 树。还是拿工号举例唯一索引相当于给工号字段建了一本专门的查找目录并且这本目录规定每个工号只能出现一次。它是物理存在的树结构会占磁盘空间会随数据写入而更新会出现在 EXPLAIN 的执行计划里。1.3 两者靠什么搭上关系正因为主键约束需要一种机制来快速检查唯一性几乎所有主流数据库在创建主键约束时都会自动在对应列上创建一个唯一索引来支撑它。MySQL 的 InnoDB 里主键直接就是聚簇索引Oracle 创建主键约束时会隐式创建一个唯一索引PostgreSQL 同样会自动为 PRIMARY KEY 建一个唯一 B-tree 索引。所以你会看到一些人说主键就是唯一索引这是从物理实现角度说的没错但不完整。主键约束 非空 唯一 通常一个底层唯一索引支撑唯一索引 一个带唯一属性的索引结构仅此而已。想清楚这一层后面所有区别都能顺下来。2. 六大核心区别逐项拆解2.1 数量限制一个主键还是多个唯一索引一张表只能有一个主键但可以有多个唯一索引。这个限制的根源在于主键的语义一张表的记录只能有一种身份标识。不过很多人不知道的是InnoDB 里只能有一个主键还叠了一层物理原因聚簇索引只能有一个。聚簇索引决定了数据行在磁盘上的物理排列顺序一个表的数据只能按照一种顺序存放所以天然只能有一个聚簇索引也就只能有一个主键。唯一索引就没有这个限制。真实业务里你经常能看到一张表同时挂着好几个唯一索引CREATE TABLE user_account ( id BIGINT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(64), mobile VARCHAR(20), identity_no VARCHAR(18), UNIQUE KEY uk_email (email), UNIQUE KEY uk_mobile (mobile), UNIQUE KEY uk_identity (identity_no) );邮箱、手机号、身份证号都承担着各自的业务唯一性但它们都不是记录的身份标识。这种场景下多个唯一索引并行非常常见。2.2 NULL 值处理能不能为空是分水岭主键列绝对不允许 NULL哪怕你只在复合主键的一列上插入 NULL整行都会插入失败。这是主键约束的一部分SQL 标准写死了。唯一索引就灵活得多。在 MySQL、PostgreSQL、Oracle 里唯一索引列允许出现多个 NULL 值。原因在于唯一性判断的逻辑NULL 不等于任何值包括 NULL 本身所以每一行 NULL 都被视为互不相同的值不违反唯一约束。只有 SQL Server 比较特殊它的唯一索引默认只允许一个 NULL除非使用过滤索引。如果你在 SQL Server 上给可空列建唯一索引插入第二个 NULL 会直接报冲突这是个容易踩的坑。这个差异在实际设计里非常有用。比如用户选填手机号没填的存 NULL那么CREATE TABLE customer ( id BIGINT PRIMARY KEY, name VARCHAR(32) NOT NULL, mobile VARCHAR(20), UNIQUE KEY uk_mobile (mobile) );多个用户不填手机号时可以共存填了手机号的用户之间不允许重复。这个效果用主键是做不到的因为主键不允许 NULL。2.3 聚簇索引InnoDB 下的物理排序差异这一条是 MySQL 使用者必须理解的核心。InnoDB 表默认是聚簇索引组织表数据行的主键值就是聚簇索引的键值叶子节点直接存放整行数据。这意味着表数据本身按照主键顺序物理排列逻辑意义上的顺序。主键查询为什么快因为走聚簇索引直接定位到数据行一次索引查找就能拿到整行不需要回表。而普通唯一索引二级索引的叶子节点存的是主键值而不是整行数据。通过唯一索引查询时先在一棵二级索引 B 树里找到主键值再回到聚簇索引里捞整行这个过程叫回表。多一次 IO。这里有个隐藏知识点如果你建表时没有定义主键InnoDB 会按顺序检查是否有非空的唯一索引如果有就拿它当聚簇索引如果连这都没有就生成一个隐藏的 6 字节 rowid 列作为聚簇索引。换句话说没有主键的表InnoDB 会自己偷偷补一个。很多人以为没主键就只是没主键实际上物理存储层面它从来没缺席过。2.4 外键引用谁有资格被认亲外键约束要求被引用的列必须是一个主键或唯一键。MySQL 里外键必须引用主键或唯一键Oracle、PostgreSQL 也类似——被引用列需要具备唯一性。但实践中几乎没人用唯一索引当外键的参照目标。原因很实际唯一索引列可能为空空值意味着没有参照对象这种语义在业务上很容易产生二义性另外唯一索引列通常承载业务属性业务属性恰恰是最容易变化的。比如用手机号做外键参照哪天用户注销手机号号码被运营商回收再分配给另一个人你的历史数据关联就全乱了。主键因为不承载业务含义尤其是代理主键天然稳定所以外键默认指向主键这是行业默认的最佳实践。2.5 变更代价删主键和删唯一索引完全不是一个量级给大表加一个唯一索引在 MySQL 8.0 里可以用 INPLACE 算法在线执行虽然会占用额外空间、增加 IO 压力但业务基本可以继续写入。给大表删一个唯一索引代价更小二级索引重建即可。改主键就是另一个故事了。修改主键意味着重建聚簇索引而聚簇索引的叶子节点是整行数据重建它等于把整张表的行数据全部重排一遍。对千万行以上的表这可能意味着几分钟到几十分钟的锁表窗口很多线上事故就是这么来的。Oracle 里同样有坑禁用主键约束DISABLE CONSTRAINT不等于删除索引约束停用后底层唯一索引可能还在但唯一性校验已经停止这时候插入重复值约束不会拦你但索引本身还在正常工作可以加速查询。很多人误以为禁用约束后索引也没了排查问题时会看走眼。2.6 语义差异标识记录还是保证不重复说了这么多实现层面的东西回到最朴素的问题两者到底各自解决什么问题主键解决的是记录身份问题。它的存在是为了让每一行都有一张独一无二的身份证哪怕这个身份证号本身毫无业务意义比如自增 ID。主键甚至不关心业务只关心我能不能在一堆记录里准确区分这一条。唯一索引解决的是业务值不重复问题。它保护的是某个业务字段的取值唯一性比如订单号不能重复、邮箱不能重复注册、同一租户下的编码不能重复。判断标准很简单问自己一句这一列如果未来业务上允许变化变了之后表的关联和标识还成立吗如果成立它可能是唯一索引如果不成立那它是主键。3. 不同数据库里的具体行为表现3.1 MySQL InnoDB主键直接决定存储形态InnoDB 是最典型的主键驱动型引擎。建表时指定主键数据就按主键聚簇。这里有几个实际影响第一主键值越小二级索引包括唯一索引的叶子节点就越小因为叶子节点存主键值。用自增 BIGINT 做主键二级索引占空间最小用 VARCHAR(64) 的 UUID 做主键每个二级索引的叶子节点都要多存一行 64 字符索引体积翻倍甚至更多。第二自增主键写入时是顺序追加新行落在聚簇索引的最后面不会频繁触发页分裂。UUID 主键是随机值每插入一行都可能插到现有数据中间导致频繁页分裂和随机 IO写入性能肉眼可见地下降。这个差异在数据量上千万后极其明显。第三如果你真的不建主键InnoDB 会挑一个非空唯一索引当聚簇索引。这会导致一个有意思的现象你自己建的唯一索引在物理层面悄悄变成了主键后续再想加真正的主键就要重建整张表。所以建表时老老实实给个代理主键别指望 InnoDB 兜底。3.2 Oracle主键约束默认自带唯一索引Oracle 里执行CREATE TABLE students ( student_id NUMBER PRIMARY KEY, name VARCHAR2(50) );Oracle 会自动为 STUDENT_ID 创建一个唯一索引索引名和主键约束名一致。你可以通过USER_INDEXES看到这个索引。如果想复用已有索引来支撑新主键可以ALTER TABLE students ADD CONSTRAINT pk_students PRIMARY KEY (student_id) USING INDEX idx_students_existing;热词里提到Oracle 主键无效化后会怎样展开讲一下。执行ALTER TABLE students DISABLE CONSTRAINT pk_students;后果有三点约束的唯一性校验停了你能插入重复的 STUDENT_ID底层索引并没有被自动删除它还在只是不再作为唯一性约束的检查工具索引依然能用来查询加速。如果你想启用约束但数据已经出现了重复ENABLE的时候会直接报 ORA-02437必须先清理重复数据。如果你想彻底删掉主键约束默认情况下 Oracle 会连带把支撑它的索引也删掉。不想删索引必须显式写ALTER TABLE students DROP CONSTRAINT pk_students KEEP INDEX;这个细节很多人不知道删完约束发现查询开始走全表扫描才反应过来。3.3 PostgreSQL 和 SQLite大同小异但各有脾气PostgreSQL 里PRIMARY KEY会自动创建一个 B-tree 唯一索引NULL 规则和 MySQL 一致唯一索引允许多个 NULL。它和 MySQL 最大的区别是索引类型更丰富你甚至可以让主键走 Hash 索引虽然一般不建议。PostgreSQL 还有一个细节外键如果引用的是唯一约束在更新被引用列时也会做额外的检查约束的级联行为要仔细设计。SQLite 比较特殊。它的INTEGER PRIMARY KEY在大多数表里会变成 rowid 的别名这时候主键不仅唯一还直接对应物理行号查询效率最高。但如果你用一个非 INTEGER 类型做主键SQLite 并不会自动把它变成 rowid 别名本质上只是建了一个带唯一约束的索引物理排列还是按 rowid 来的。这个差异经常导致同样的 SQL 在 SQLite 里聊性能时结论完全不一样。4. 实操选型建表时到底该用哪个4.1 代理主键与自然主键之争主键到底用自增 ID、UUID还是业务自然键我直接给结论绝大多数业务表用自增 BIGINT 或雪花 ID 这类代理主键不要用业务字段做主键。自然主键的典型反面教材是用身份证号、手机号、订单号做主键。问题在于这些业务字段都可能变化手机号可以换、身份证号存在极少数重号的历史问题、订单号在不同系统合并时可能格式冲突。主键一旦要改聚簇索引重建关联外键全部要动代价极高。代理主键唯一的缺点是需要额外维护一套生成规则但对单机自增和分布式雪花 ID 来说这个成本已经低到可以忽略。不要因为少一列就觉得省事用自然主键的后续维护成本远比多一列高。如果纠结 UUID 和自增单库单表、写入以顺序追加为主选自增分布式环境、需要全局唯一且无法依赖单点序号选雪花 ID 或带时间的 UUID 变体。纯粹随机 UUID 做主键我是劝退的页分裂和索引膨胀会在高并发写入下教做人。4.2 联合主键与联合唯一索引复合主键出现在明细表这类场景里比如订单明细表CREATE TABLE order_item ( order_id BIGINT NOT NULL, line_no INT NOT NULL, sku_id BIGINT NOT NULL, quantity INT NOT NULL, PRIMARY KEY (order_id, line_no) );(order_id, line_no) 联合主键保证同一个订单内部行号不重复同时天然承担了按订单查明细的聚簇加速。这个设计是合理的因为 order_id 已经有独立的订单主表做关联明细表的主键用于标识明细内的一条记录。但要注意联合主键的列顺序对查询影响很大。联合索引走的是最左前缀原则(order_id, line_no) 能加速 WHERE order_idxxx 和 WHERE order_idxxx AND line_noyyy但单独用 line_no 查就是全索引扫描。如果业务里经常单独用 line_no 过滤就得额外建一个 line_no 的二级索引代价和收益要想清楚。另一种情况是联合唯一索引。比如多租户系统里每个租户内部的自定义编码不能重复CREATE TABLE tenant_code ( id BIGINT PRIMARY KEY AUTO_INCREMENT, tenant_id BIGINT NOT NULL, code VARCHAR(32) NOT NULL, UNIQUE KEY uk_tenant_code (tenant_id, code) );tenant_id code 联合唯一索引保证了租户内唯一但跨租户允许相同 code。这种业务约束用主键根本不合适因为没有租户 ID 时这个编码没有全局标识意义。判断联合主键和联合唯一索引的标准还是那句这一组列是不是用来唯一标识记录本身的身份。4.3 哪些场景用唯一索引比主键更合适第一字段允许为 NULL。主键强制非空但很多业务字段确实允许空。多个空值要共存唯一索引是唯一解法。第二软删除场景。表结构里加了 is_deleted 做逻辑删除业务上希望未删除的数据里业务编码唯一但删除后允许复用编码。这时候直接给业务编码加唯一索引是不行的因为软删的记录还在表里。常见解法是把 deleted 状态做成生成列或把唯一索引改成包含删除标记的复合索引。MySQL 8.0.13 之后可以用函数索引或者维护一个 deleted_at配合 NULL 不参与唯一判断的特性CREATE TABLE coupon ( id BIGINT PRIMARY KEY, coupon_no VARCHAR(32) NOT NULL, deleted_at DATETIME NULL, UNIQUE KEY uk_coupon_no (coupon_no, deleted_at) );同一 coupon_no 插入第二行时如果 deleted_at 都是 NULL会被拦住软删除时把 deleted_at 写成时间值该行不再参与冲突判断因此允许再次插入相同 coupon_no 的新数据。这个技巧非常实用。第三全局唯一但非标识的字段。比如支付流水号、对账批次号、单号快照。它们是强业务唯一约束但表的记录身份已经有自增主键了这里用唯一索引精准表达。5. 性能与索引失效那些坑怎么绕5.1 哪些写法和场景会导致索引失效热词里有哪些场景用会导致索引失效这个必须展开。先给一个容易误解的前提所谓索引失效很多时候不是索引真的坏了而是优化器觉得走这个索引不如全表扫描划算或者你的写法让索引本身无法被高效利用。对索引列使用函数。最常见WHERE UPPER(email) AQQ.COM WHERE DATE(create_time) 2025-01-01这类写法会破坏索引的排序结构B 树没法按原始值快速定位MySQL 里基本只能放弃索引。避免方式是把函数移到等号右侧或者对查询列预先处理。MySQL 8.0.13 起也可以建函数索引但要小心维护成本。隐式类型转换。字符串列和数字比较时MySQL 会把字符串转成数字再比较这一转索引就废了。经典案例手机号列是 VARCHAR查询写成WHERE mobile 13800138000优化器对 mobile 做数值转换索引失效。正确写法是WHERE mobile 13800138000。LIKE 前置通配符。LIKE %abc%没法走索引因为不知道从哪个前缀开始扫。但LIKE abc%是可以用索引的很多人一棍子打死说 LIKE 全部失效这是不对的。OR 连接非索引列。WHERE id 1 OR name xx如果 name 没有索引优化器很可能干脆全表扫。用 UNION ALL 拆分或者给 name 也建索引才是正解。联合索引不满足最左前缀。前面已经讲过(tenant_id, code) 联合索引只查 code 时不走索引只查 tenant_id 时走。对索引列做计算。WHERE age 1 30这种写法也会让索引失效把计算拆出去变成WHERE age 29。还有一种容易被忽略的情况优化器主动放弃索引。当索引列区分度太低比如 status 只有三个值三分之一的行都是同一个值优化器算一下发现全表扫反而更快就会忽略索引。这不是失效是优化器的理性选择你要做的是把这类低区分度过滤条件和其他高区分度条件组合而不是干瞪眼。5.2 主键查询与唯一索引查询的性能差异同样是精确查一行SELECT * FROM t WHERE id 100; SELECT * FROM t WHERE unique_col abc;第一条走聚簇索引一次 B 树定位直接拿整行没有回表。第二条走二级唯一索引先在一棵二级索引树里定位到主键值再回聚簇索引找整行多一次回表。如果唯一索引是覆盖的查询列都在索引里第二条也能免回表。比如SELECT unique_col FROM t WHERE unique_col abc索引本身就够了。写入方面主键和唯一索引都要做唯一性检查。主键的检查在聚簇索引上做唯一索引的检查在二级索引上做两块索引都要更新。所以一张表每多一个唯一索引写入成本都会增加。这也是为什么我不建议无脑给所有字段加唯一索引——写入频繁的表每多一个唯一索引都是肉眼可见的性能损耗。另外说一个高并发下的隐形问题热点行。大量并发同时插入同一个唯一索引值比如抢同一个手机号注册唯一性检查会对该索引键加锁容易引发锁等待或死锁。数据库死锁的热搜词背后很多就是这么来的。解决方案是控制并发、缩短事务、必要时用插入前先查的幂等逻辑减轻索引冲突。5.3 大表变更主键的线上处理经验我接过一个线上事故某订单表接近 2 亿行原主键是订单号字符串业务要改成自增代理主键。当时如果直接在源表上 ALTER锁表窗口保守估计半小时起步业务根本扛不住。最终方案是新建表 双写 切换新建订单表主键改为自增 BIGINT原订单号列设为唯一索引保证业务唯一性开启双写新数据同时写旧表和新表离线任务把历史数据分批迁入新表每批几千行控制 binlog 和主从延迟校验两表数据量和关键字段一致性跑对比 SQL不一致的重跑对应批次确定一致后在低峰期做读写切换切换前短暂停写确认切换成功后再放开写入。整个过程花了两个晚上但业务几乎无感。这个案例核心想说的是主键是存储结构的骨架动它等于给整栋楼重新换承重墙宁可麻烦一点拆墙重砌也不要在承重墙上直接用电钻。如果只是加唯一索引没必要这么兴师动众。MySQL 8.0 的 INPLACE、或者用 pt-osc / gh-ost 这类工具都可以在线加但要注意大表加唯一索引如果碰上已有重复数据过程会失败。所以加之前必须先把重复数据排查并处理掉这条下面详细说。6. 常见问题排查与避坑速查6.1 高频问题与解决思路常见问题原因与结论处理方式一张表可以有两个主键吗不可以主键唯一标识 聚簇索引唯一需要多组唯一性时用多个唯一索引主键列能存 NULL 吗不能主键约束强制非空可空字段要用唯一索引而不是主键唯一索引列能存多个 NULL 吗MySQL/PostgreSQL/Oracle 可以SQL Server 只能一个按数据库类型设计空值策略表里已有重复数据加唯一索引报错唯一索引要求现有数据不重复先清理或合并重复行再建索引删除主键有什么连带影响关联外键失效、Oracle 默认连索引一起删用 KEEP INDEX 保留索引评估外键影响禁用 Oracle 主键约束后还能插入重复吗能约束停用后唯一性校验停止注意 ENABLE 前要清理重复数据唯一索引和唯一约束有区别吗逻辑层面不同物理上通常由同一索引支撑二者可互换但唯一约束语义更清晰为什么唯一索引查询还是慢可能是回表、索引区分度低或写法导致失效EXPLAIN 分析检查是否覆盖索引关于表里已有重复数据怎么加唯一索引我给一个最常用的排查脚本-- 找重复 SELECT mobile, COUNT(*) AS cnt FROM customer GROUP BY mobile HAVING COUNT(*) 1; -- 保留最小 id删除其余重复项 DELETE FROM customer WHERE id NOT IN ( SELECT MIN(id) FROM customer GROUP BY mobile );注意大表 DELETE 要分批直接一条大事务删几百万行容易撑爆 undo 和 binlog。建议按主键范围分段删除每段删完提交一次。6.2 我的几条实操心得第一建表时先把业务唯一性和记录身份分开列出来。不要等业务上线后才发现邮箱本来不该为空却被设成了主键也不要为了省事把 varchar 订单号直接当主键。第二加唯一索引之前永远先跑一遍重复数据检查。我见过两次事故都是 DBA 在大表上加唯一索引跑到一半报 Duplicate entry回滚特别痛苦。先查 10 分钟后面省一晚上。第三唯一索引的命名规范要立起来。常见习惯是 uk_ 前缀后面接列名比如 uk_mobile、uk_tenant_code。主键约束用 pk_ 前缀。线上排查时看到一眼能懂的名字比什么都强。第四MySQL 里给大表加唯一索引最好用在线工具或者低峰期执行。虽然 8.0 支持 INPLACE但加唯一索引要扫描全表校验唯一性期间还是有 IO 压力和锁竞争监控要盯住。第五千万记得 EXPLAIN 验证。很多时候你以为走的索引实际执行计划里显示的是 ALL 全表扫描。SQL 优化不是靠猜EXPLAIN 里看到 key 列用的是哪个索引rows 估算多少一目了然。我每写一条复杂查询都会养成先 EXPLAIN 再放行的习惯。踩过几次坑之后我现在设计表结构时的默认套路是任何表先给自增 BIGINT 主键业务字段里凡是不允许重复的逐个评估用唯一索引凡是允许空或者需要软删除复用的一定用唯一索引而不是主键。这套规则简简单单但帮我挡住了绝大多数线上数据问题。希望这篇把主键和唯一索引的区别讲透的文章也能让你的设计少走几个弯。
主键和唯一索引到底有什么区别？从约束本质到线上实战避坑指南
主键和唯一索引到底有什么区别这个问题做过两三年开发的人基本都撞上过面试被问一次线上排查重复数据又被绕晕一次。网上讲两者区别的文章不少但多数停在一张表只能有一个主键、唯一索引可以有多个、主键不能为空这种表面答案。这些当然没错可真正落地到建表、改表、排查慢查询和生产事故时光靠这几句话根本撑不住。这篇文章我用自己的实际经验来讲以 MySQL InnoDB 为主线穿插 Oracle 和 PostgreSQL 的具体行为最后给一套平时建表、加索引、清理重复数据时可以直接抄走的方案。顺便把踩过的坑一并倒出来大表改主键导致业务中断、软删除后唯一索引冲突、精心建好的索引因为一个函数查询直接失效。把这些场景走完一遍你对主键和唯一索引的理解会扎实很多。1. 先从本质上分清约束和索引不是一回事1.1 主键是一个约束很多文章一上来就对比主键 VS 唯一索引把两者放在同一层这本身就容易误导。主键在 SQL 标准里的定义非常明确主键约束PRIMARY KEY 非空约束 唯一约束它是一组完整性规则用来保证每一行都能被唯一标识。约束是逻辑概念它回答的是什么数据可以进入这张表的问题。主键约束决定了这一列或这几列的值不允许为空且不允许重复。至于数据库底层拿什么数据结构去实现这个约束主键约束本身并不关心。打个比方主键约束像是公司规定每个员工必须有唯一的工号且工号不能为空这是一个制度层面的规则。1.2 唯一索引是一个物理结构索引就完全是另一层的东西了。索引是一种独立的存储结构它的存在是为了加速查询、维护数据的有序性。唯一索引UNIQUE INDEX只是在这个物理结构上加了一层值不能重复的限定。索引是物理概念它回答的是数据怎么被快速找到的问题。唯不唯一只是它的一个属性真正干活的还是那颗 B 树。还是拿工号举例唯一索引相当于给工号字段建了一本专门的查找目录并且这本目录规定每个工号只能出现一次。它是物理存在的树结构会占磁盘空间会随数据写入而更新会出现在 EXPLAIN 的执行计划里。1.3 两者靠什么搭上关系正因为主键约束需要一种机制来快速检查唯一性几乎所有主流数据库在创建主键约束时都会自动在对应列上创建一个唯一索引来支撑它。MySQL 的 InnoDB 里主键直接就是聚簇索引Oracle 创建主键约束时会隐式创建一个唯一索引PostgreSQL 同样会自动为 PRIMARY KEY 建一个唯一 B-tree 索引。所以你会看到一些人说主键就是唯一索引这是从物理实现角度说的没错但不完整。主键约束 非空 唯一 通常一个底层唯一索引支撑唯一索引 一个带唯一属性的索引结构仅此而已。想清楚这一层后面所有区别都能顺下来。2. 六大核心区别逐项拆解2.1 数量限制一个主键还是多个唯一索引一张表只能有一个主键但可以有多个唯一索引。这个限制的根源在于主键的语义一张表的记录只能有一种身份标识。不过很多人不知道的是InnoDB 里只能有一个主键还叠了一层物理原因聚簇索引只能有一个。聚簇索引决定了数据行在磁盘上的物理排列顺序一个表的数据只能按照一种顺序存放所以天然只能有一个聚簇索引也就只能有一个主键。唯一索引就没有这个限制。真实业务里你经常能看到一张表同时挂着好几个唯一索引CREATE TABLE user_account ( id BIGINT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(64), mobile VARCHAR(20), identity_no VARCHAR(18), UNIQUE KEY uk_email (email), UNIQUE KEY uk_mobile (mobile), UNIQUE KEY uk_identity (identity_no) );邮箱、手机号、身份证号都承担着各自的业务唯一性但它们都不是记录的身份标识。这种场景下多个唯一索引并行非常常见。2.2 NULL 值处理能不能为空是分水岭主键列绝对不允许 NULL哪怕你只在复合主键的一列上插入 NULL整行都会插入失败。这是主键约束的一部分SQL 标准写死了。唯一索引就灵活得多。在 MySQL、PostgreSQL、Oracle 里唯一索引列允许出现多个 NULL 值。原因在于唯一性判断的逻辑NULL 不等于任何值包括 NULL 本身所以每一行 NULL 都被视为互不相同的值不违反唯一约束。只有 SQL Server 比较特殊它的唯一索引默认只允许一个 NULL除非使用过滤索引。如果你在 SQL Server 上给可空列建唯一索引插入第二个 NULL 会直接报冲突这是个容易踩的坑。这个差异在实际设计里非常有用。比如用户选填手机号没填的存 NULL那么CREATE TABLE customer ( id BIGINT PRIMARY KEY, name VARCHAR(32) NOT NULL, mobile VARCHAR(20), UNIQUE KEY uk_mobile (mobile) );多个用户不填手机号时可以共存填了手机号的用户之间不允许重复。这个效果用主键是做不到的因为主键不允许 NULL。2.3 聚簇索引InnoDB 下的物理排序差异这一条是 MySQL 使用者必须理解的核心。InnoDB 表默认是聚簇索引组织表数据行的主键值就是聚簇索引的键值叶子节点直接存放整行数据。这意味着表数据本身按照主键顺序物理排列逻辑意义上的顺序。主键查询为什么快因为走聚簇索引直接定位到数据行一次索引查找就能拿到整行不需要回表。而普通唯一索引二级索引的叶子节点存的是主键值而不是整行数据。通过唯一索引查询时先在一棵二级索引 B 树里找到主键值再回到聚簇索引里捞整行这个过程叫回表。多一次 IO。这里有个隐藏知识点如果你建表时没有定义主键InnoDB 会按顺序检查是否有非空的唯一索引如果有就拿它当聚簇索引如果连这都没有就生成一个隐藏的 6 字节 rowid 列作为聚簇索引。换句话说没有主键的表InnoDB 会自己偷偷补一个。很多人以为没主键就只是没主键实际上物理存储层面它从来没缺席过。2.4 外键引用谁有资格被认亲外键约束要求被引用的列必须是一个主键或唯一键。MySQL 里外键必须引用主键或唯一键Oracle、PostgreSQL 也类似——被引用列需要具备唯一性。但实践中几乎没人用唯一索引当外键的参照目标。原因很实际唯一索引列可能为空空值意味着没有参照对象这种语义在业务上很容易产生二义性另外唯一索引列通常承载业务属性业务属性恰恰是最容易变化的。比如用手机号做外键参照哪天用户注销手机号号码被运营商回收再分配给另一个人你的历史数据关联就全乱了。主键因为不承载业务含义尤其是代理主键天然稳定所以外键默认指向主键这是行业默认的最佳实践。2.5 变更代价删主键和删唯一索引完全不是一个量级给大表加一个唯一索引在 MySQL 8.0 里可以用 INPLACE 算法在线执行虽然会占用额外空间、增加 IO 压力但业务基本可以继续写入。给大表删一个唯一索引代价更小二级索引重建即可。改主键就是另一个故事了。修改主键意味着重建聚簇索引而聚簇索引的叶子节点是整行数据重建它等于把整张表的行数据全部重排一遍。对千万行以上的表这可能意味着几分钟到几十分钟的锁表窗口很多线上事故就是这么来的。Oracle 里同样有坑禁用主键约束DISABLE CONSTRAINT不等于删除索引约束停用后底层唯一索引可能还在但唯一性校验已经停止这时候插入重复值约束不会拦你但索引本身还在正常工作可以加速查询。很多人误以为禁用约束后索引也没了排查问题时会看走眼。2.6 语义差异标识记录还是保证不重复说了这么多实现层面的东西回到最朴素的问题两者到底各自解决什么问题主键解决的是记录身份问题。它的存在是为了让每一行都有一张独一无二的身份证哪怕这个身份证号本身毫无业务意义比如自增 ID。主键甚至不关心业务只关心我能不能在一堆记录里准确区分这一条。唯一索引解决的是业务值不重复问题。它保护的是某个业务字段的取值唯一性比如订单号不能重复、邮箱不能重复注册、同一租户下的编码不能重复。判断标准很简单问自己一句这一列如果未来业务上允许变化变了之后表的关联和标识还成立吗如果成立它可能是唯一索引如果不成立那它是主键。3. 不同数据库里的具体行为表现3.1 MySQL InnoDB主键直接决定存储形态InnoDB 是最典型的主键驱动型引擎。建表时指定主键数据就按主键聚簇。这里有几个实际影响第一主键值越小二级索引包括唯一索引的叶子节点就越小因为叶子节点存主键值。用自增 BIGINT 做主键二级索引占空间最小用 VARCHAR(64) 的 UUID 做主键每个二级索引的叶子节点都要多存一行 64 字符索引体积翻倍甚至更多。第二自增主键写入时是顺序追加新行落在聚簇索引的最后面不会频繁触发页分裂。UUID 主键是随机值每插入一行都可能插到现有数据中间导致频繁页分裂和随机 IO写入性能肉眼可见地下降。这个差异在数据量上千万后极其明显。第三如果你真的不建主键InnoDB 会挑一个非空唯一索引当聚簇索引。这会导致一个有意思的现象你自己建的唯一索引在物理层面悄悄变成了主键后续再想加真正的主键就要重建整张表。所以建表时老老实实给个代理主键别指望 InnoDB 兜底。3.2 Oracle主键约束默认自带唯一索引Oracle 里执行CREATE TABLE students ( student_id NUMBER PRIMARY KEY, name VARCHAR2(50) );Oracle 会自动为 STUDENT_ID 创建一个唯一索引索引名和主键约束名一致。你可以通过USER_INDEXES看到这个索引。如果想复用已有索引来支撑新主键可以ALTER TABLE students ADD CONSTRAINT pk_students PRIMARY KEY (student_id) USING INDEX idx_students_existing;热词里提到Oracle 主键无效化后会怎样展开讲一下。执行ALTER TABLE students DISABLE CONSTRAINT pk_students;后果有三点约束的唯一性校验停了你能插入重复的 STUDENT_ID底层索引并没有被自动删除它还在只是不再作为唯一性约束的检查工具索引依然能用来查询加速。如果你想启用约束但数据已经出现了重复ENABLE的时候会直接报 ORA-02437必须先清理重复数据。如果你想彻底删掉主键约束默认情况下 Oracle 会连带把支撑它的索引也删掉。不想删索引必须显式写ALTER TABLE students DROP CONSTRAINT pk_students KEEP INDEX;这个细节很多人不知道删完约束发现查询开始走全表扫描才反应过来。3.3 PostgreSQL 和 SQLite大同小异但各有脾气PostgreSQL 里PRIMARY KEY会自动创建一个 B-tree 唯一索引NULL 规则和 MySQL 一致唯一索引允许多个 NULL。它和 MySQL 最大的区别是索引类型更丰富你甚至可以让主键走 Hash 索引虽然一般不建议。PostgreSQL 还有一个细节外键如果引用的是唯一约束在更新被引用列时也会做额外的检查约束的级联行为要仔细设计。SQLite 比较特殊。它的INTEGER PRIMARY KEY在大多数表里会变成 rowid 的别名这时候主键不仅唯一还直接对应物理行号查询效率最高。但如果你用一个非 INTEGER 类型做主键SQLite 并不会自动把它变成 rowid 别名本质上只是建了一个带唯一约束的索引物理排列还是按 rowid 来的。这个差异经常导致同样的 SQL 在 SQLite 里聊性能时结论完全不一样。4. 实操选型建表时到底该用哪个4.1 代理主键与自然主键之争主键到底用自增 ID、UUID还是业务自然键我直接给结论绝大多数业务表用自增 BIGINT 或雪花 ID 这类代理主键不要用业务字段做主键。自然主键的典型反面教材是用身份证号、手机号、订单号做主键。问题在于这些业务字段都可能变化手机号可以换、身份证号存在极少数重号的历史问题、订单号在不同系统合并时可能格式冲突。主键一旦要改聚簇索引重建关联外键全部要动代价极高。代理主键唯一的缺点是需要额外维护一套生成规则但对单机自增和分布式雪花 ID 来说这个成本已经低到可以忽略。不要因为少一列就觉得省事用自然主键的后续维护成本远比多一列高。如果纠结 UUID 和自增单库单表、写入以顺序追加为主选自增分布式环境、需要全局唯一且无法依赖单点序号选雪花 ID 或带时间的 UUID 变体。纯粹随机 UUID 做主键我是劝退的页分裂和索引膨胀会在高并发写入下教做人。4.2 联合主键与联合唯一索引复合主键出现在明细表这类场景里比如订单明细表CREATE TABLE order_item ( order_id BIGINT NOT NULL, line_no INT NOT NULL, sku_id BIGINT NOT NULL, quantity INT NOT NULL, PRIMARY KEY (order_id, line_no) );(order_id, line_no) 联合主键保证同一个订单内部行号不重复同时天然承担了按订单查明细的聚簇加速。这个设计是合理的因为 order_id 已经有独立的订单主表做关联明细表的主键用于标识明细内的一条记录。但要注意联合主键的列顺序对查询影响很大。联合索引走的是最左前缀原则(order_id, line_no) 能加速 WHERE order_idxxx 和 WHERE order_idxxx AND line_noyyy但单独用 line_no 查就是全索引扫描。如果业务里经常单独用 line_no 过滤就得额外建一个 line_no 的二级索引代价和收益要想清楚。另一种情况是联合唯一索引。比如多租户系统里每个租户内部的自定义编码不能重复CREATE TABLE tenant_code ( id BIGINT PRIMARY KEY AUTO_INCREMENT, tenant_id BIGINT NOT NULL, code VARCHAR(32) NOT NULL, UNIQUE KEY uk_tenant_code (tenant_id, code) );tenant_id code 联合唯一索引保证了租户内唯一但跨租户允许相同 code。这种业务约束用主键根本不合适因为没有租户 ID 时这个编码没有全局标识意义。判断联合主键和联合唯一索引的标准还是那句这一组列是不是用来唯一标识记录本身的身份。4.3 哪些场景用唯一索引比主键更合适第一字段允许为 NULL。主键强制非空但很多业务字段确实允许空。多个空值要共存唯一索引是唯一解法。第二软删除场景。表结构里加了 is_deleted 做逻辑删除业务上希望未删除的数据里业务编码唯一但删除后允许复用编码。这时候直接给业务编码加唯一索引是不行的因为软删的记录还在表里。常见解法是把 deleted 状态做成生成列或把唯一索引改成包含删除标记的复合索引。MySQL 8.0.13 之后可以用函数索引或者维护一个 deleted_at配合 NULL 不参与唯一判断的特性CREATE TABLE coupon ( id BIGINT PRIMARY KEY, coupon_no VARCHAR(32) NOT NULL, deleted_at DATETIME NULL, UNIQUE KEY uk_coupon_no (coupon_no, deleted_at) );同一 coupon_no 插入第二行时如果 deleted_at 都是 NULL会被拦住软删除时把 deleted_at 写成时间值该行不再参与冲突判断因此允许再次插入相同 coupon_no 的新数据。这个技巧非常实用。第三全局唯一但非标识的字段。比如支付流水号、对账批次号、单号快照。它们是强业务唯一约束但表的记录身份已经有自增主键了这里用唯一索引精准表达。5. 性能与索引失效那些坑怎么绕5.1 哪些写法和场景会导致索引失效热词里有哪些场景用会导致索引失效这个必须展开。先给一个容易误解的前提所谓索引失效很多时候不是索引真的坏了而是优化器觉得走这个索引不如全表扫描划算或者你的写法让索引本身无法被高效利用。对索引列使用函数。最常见WHERE UPPER(email) AQQ.COM WHERE DATE(create_time) 2025-01-01这类写法会破坏索引的排序结构B 树没法按原始值快速定位MySQL 里基本只能放弃索引。避免方式是把函数移到等号右侧或者对查询列预先处理。MySQL 8.0.13 起也可以建函数索引但要小心维护成本。隐式类型转换。字符串列和数字比较时MySQL 会把字符串转成数字再比较这一转索引就废了。经典案例手机号列是 VARCHAR查询写成WHERE mobile 13800138000优化器对 mobile 做数值转换索引失效。正确写法是WHERE mobile 13800138000。LIKE 前置通配符。LIKE %abc%没法走索引因为不知道从哪个前缀开始扫。但LIKE abc%是可以用索引的很多人一棍子打死说 LIKE 全部失效这是不对的。OR 连接非索引列。WHERE id 1 OR name xx如果 name 没有索引优化器很可能干脆全表扫。用 UNION ALL 拆分或者给 name 也建索引才是正解。联合索引不满足最左前缀。前面已经讲过(tenant_id, code) 联合索引只查 code 时不走索引只查 tenant_id 时走。对索引列做计算。WHERE age 1 30这种写法也会让索引失效把计算拆出去变成WHERE age 29。还有一种容易被忽略的情况优化器主动放弃索引。当索引列区分度太低比如 status 只有三个值三分之一的行都是同一个值优化器算一下发现全表扫反而更快就会忽略索引。这不是失效是优化器的理性选择你要做的是把这类低区分度过滤条件和其他高区分度条件组合而不是干瞪眼。5.2 主键查询与唯一索引查询的性能差异同样是精确查一行SELECT * FROM t WHERE id 100; SELECT * FROM t WHERE unique_col abc;第一条走聚簇索引一次 B 树定位直接拿整行没有回表。第二条走二级唯一索引先在一棵二级索引树里定位到主键值再回聚簇索引找整行多一次回表。如果唯一索引是覆盖的查询列都在索引里第二条也能免回表。比如SELECT unique_col FROM t WHERE unique_col abc索引本身就够了。写入方面主键和唯一索引都要做唯一性检查。主键的检查在聚簇索引上做唯一索引的检查在二级索引上做两块索引都要更新。所以一张表每多一个唯一索引写入成本都会增加。这也是为什么我不建议无脑给所有字段加唯一索引——写入频繁的表每多一个唯一索引都是肉眼可见的性能损耗。另外说一个高并发下的隐形问题热点行。大量并发同时插入同一个唯一索引值比如抢同一个手机号注册唯一性检查会对该索引键加锁容易引发锁等待或死锁。数据库死锁的热搜词背后很多就是这么来的。解决方案是控制并发、缩短事务、必要时用插入前先查的幂等逻辑减轻索引冲突。5.3 大表变更主键的线上处理经验我接过一个线上事故某订单表接近 2 亿行原主键是订单号字符串业务要改成自增代理主键。当时如果直接在源表上 ALTER锁表窗口保守估计半小时起步业务根本扛不住。最终方案是新建表 双写 切换新建订单表主键改为自增 BIGINT原订单号列设为唯一索引保证业务唯一性开启双写新数据同时写旧表和新表离线任务把历史数据分批迁入新表每批几千行控制 binlog 和主从延迟校验两表数据量和关键字段一致性跑对比 SQL不一致的重跑对应批次确定一致后在低峰期做读写切换切换前短暂停写确认切换成功后再放开写入。整个过程花了两个晚上但业务几乎无感。这个案例核心想说的是主键是存储结构的骨架动它等于给整栋楼重新换承重墙宁可麻烦一点拆墙重砌也不要在承重墙上直接用电钻。如果只是加唯一索引没必要这么兴师动众。MySQL 8.0 的 INPLACE、或者用 pt-osc / gh-ost 这类工具都可以在线加但要注意大表加唯一索引如果碰上已有重复数据过程会失败。所以加之前必须先把重复数据排查并处理掉这条下面详细说。6. 常见问题排查与避坑速查6.1 高频问题与解决思路常见问题原因与结论处理方式一张表可以有两个主键吗不可以主键唯一标识 聚簇索引唯一需要多组唯一性时用多个唯一索引主键列能存 NULL 吗不能主键约束强制非空可空字段要用唯一索引而不是主键唯一索引列能存多个 NULL 吗MySQL/PostgreSQL/Oracle 可以SQL Server 只能一个按数据库类型设计空值策略表里已有重复数据加唯一索引报错唯一索引要求现有数据不重复先清理或合并重复行再建索引删除主键有什么连带影响关联外键失效、Oracle 默认连索引一起删用 KEEP INDEX 保留索引评估外键影响禁用 Oracle 主键约束后还能插入重复吗能约束停用后唯一性校验停止注意 ENABLE 前要清理重复数据唯一索引和唯一约束有区别吗逻辑层面不同物理上通常由同一索引支撑二者可互换但唯一约束语义更清晰为什么唯一索引查询还是慢可能是回表、索引区分度低或写法导致失效EXPLAIN 分析检查是否覆盖索引关于表里已有重复数据怎么加唯一索引我给一个最常用的排查脚本-- 找重复 SELECT mobile, COUNT(*) AS cnt FROM customer GROUP BY mobile HAVING COUNT(*) 1; -- 保留最小 id删除其余重复项 DELETE FROM customer WHERE id NOT IN ( SELECT MIN(id) FROM customer GROUP BY mobile );注意大表 DELETE 要分批直接一条大事务删几百万行容易撑爆 undo 和 binlog。建议按主键范围分段删除每段删完提交一次。6.2 我的几条实操心得第一建表时先把业务唯一性和记录身份分开列出来。不要等业务上线后才发现邮箱本来不该为空却被设成了主键也不要为了省事把 varchar 订单号直接当主键。第二加唯一索引之前永远先跑一遍重复数据检查。我见过两次事故都是 DBA 在大表上加唯一索引跑到一半报 Duplicate entry回滚特别痛苦。先查 10 分钟后面省一晚上。第三唯一索引的命名规范要立起来。常见习惯是 uk_ 前缀后面接列名比如 uk_mobile、uk_tenant_code。主键约束用 pk_ 前缀。线上排查时看到一眼能懂的名字比什么都强。第四MySQL 里给大表加唯一索引最好用在线工具或者低峰期执行。虽然 8.0 支持 INPLACE但加唯一索引要扫描全表校验唯一性期间还是有 IO 压力和锁竞争监控要盯住。第五千万记得 EXPLAIN 验证。很多时候你以为走的索引实际执行计划里显示的是 ALL 全表扫描。SQL 优化不是靠猜EXPLAIN 里看到 key 列用的是哪个索引rows 估算多少一目了然。我每写一条复杂查询都会养成先 EXPLAIN 再放行的习惯。踩过几次坑之后我现在设计表结构时的默认套路是任何表先给自增 BIGINT 主键业务字段里凡是不允许重复的逐个评估用唯一索引凡是允许空或者需要软删除复用的一定用唯一索引而不是主键。这套规则简简单单但帮我挡住了绝大多数线上数据问题。希望这篇把主键和唯一索引的区别讲透的文章也能让你的设计少走几个弯。