面试官：MySQL 唯一索引和主键索引的区别？(修订版)

在线 Java 面试刷题持续更新https://www.quanxiaoha.com/java-interview面试考察点索引类型理解面试官不仅仅是想知道 有什么区别更是想考察你是否理解主键索引聚簇索引和唯一索引二级索引在存储结构上的根本差异。NULL 值处理考察你是否清楚主键不允许 NULL而唯一索引可以但只能有一个这是很多面试者容易忽略的细节。性能优化意识理解聚簇索引和非聚簇索引的查询效率差异能否在设计表结构时做出正确选择。核心答案主键索引一种特殊的唯一索引每张表只能有一个用于唯一标识每一行记录InnoDB 中主键是聚簇索引。唯一索引保证索引列的值唯一一张表可以有多个唯一索引InnoDB 中唯一索引是二级索引非聚簇索引。核心区别对比对比维度主键索引PRIMARY唯一索引UNIQUE数量限制每表只能有 1 个每表可以有多个NULL 值不允许NULL允许NULL但最多 1 个存储结构聚簇索引叶子节点存完整数据二级索引叶子节点存主键值索引回表不需要回表需要回表查询非索引列时创建语法PRIMARY KEY (col)UNIQUE KEY uk_name (col)是否必须建议有但不强制按需创建一句话总结主键是唯一的聚簇索引不允许 NULL唯一索引是可以有多个的非聚簇索引允许 NULL。深度解析一、存储结构差异聚簇索引 vs 二级索引主键索引和唯一索引在 InnoDB 中的存储结构完全不同img上图对比了主键索引和唯一索引的存储结构。关键区别在于主键索引聚簇索引叶子节点直接存储完整的行数据通过主键查询可以直接获取所有字段无需回表唯一索引二级索引叶子节点只存储索引列的值 主键值如果查询其他字段需要拿着主键值回表查询聚簇索引二、NULL 值处理差异这是面试中的高频考点-- 创建测试表 CREATETABLEuser ( idBIGINT PRIMARY KEY, -- 主键不允许 NULL email VARCHAR(50) UNIQUE, -- 唯一索引允许 NULL phone VARCHAR(20) UNIQUE -- 唯一索引允许 NULL ); -- 主键测试插入 NULL 会报错 INSERTINTOuser (id, email) VALUES (NULL, testqq.com); -- ❌ ERROR 1048: Column id cannot be null -- 唯一索引测试允许 NULL且 MySQL 中可以插入多个 NULL INSERTINTOuser (id, email) VALUES (1, NULL); -- ✅ 成功 INSERTINTOuser (id, email) VALUES (2, NULL); -- ✅ 成功MySQL 认为多个 NULL 不重复 INSERTINTOuser (id, email) VALUES (3, aqq.com); -- ✅ 成功 INSERTINTOuser (id, email) VALUES (4, aqq.com); -- ❌ Duplicate entryimg关键结论主键绝对不允许 NULL这是主键的基本约束唯一索引允许 NULL且 MySQL 中可以插入多个 NULL 值因为NULL ! NULL三、查询性能差异回表问题通过主键查询 vs 通过唯一索引查询的性能差异-- 表结构 CREATETABLEuser ( idBIGINT PRIMARY KEY, email VARCHAR(50) UNIQUE, nameVARCHAR(50), age INT ); -- 场景 1通过主键查询 SELECT * FROMuserWHEREid 1; -- ✅ 直接走聚簇索引一次查询即可获取完整数据 -- 场景 2通过唯一索引查询所有字段 SELECT * FROMuserWHERE email testqq.com; -- ⚠️ 需要回表先查唯一索引得到 id再回表查聚簇索引 -- 场景 3通过唯一索引查询索引列覆盖索引 SELECT email FROMuserWHERE email testqq.com; -- ✅ 覆盖索引不需要回表img四、使用场景建议场景推荐索引类型原因标识每一行记录主键索引每表必须有唯一标识推荐自增 BIGINT用户邮箱不能重复唯一索引业务唯一性约束允许未设置邮箱NULL手机号唯一唯一索引允许用户暂未绑定手机号身份证号唯一唯一索引允许 NULL可能未录入联合唯一用户 日期联合唯一索引UNIQUE KEY uk_user_date (user_id, date)最佳实践-- 推荐使用 BIGINT 自增主键 CREATETABLE orders ( idBIGINTUNSIGNED AUTO_INCREMENT PRIMARY KEY, order_no VARCHAR(32) UNIQUENOTNULL, -- 业务订单号不允许 NULL user_id BIGINTNOTNULL, INDEX idx_user (user_id) ); -- 不推荐没有主键的表 -- MySQL 会自动选择一个非空唯一索引作为聚簇索引 -- 如果没有合适的会生成一个隐藏的 6 字节主键五、如果没有主键会怎样InnoDB 要求每张表必须有聚簇索引img面试高频追问追问一为什么推荐使用自增主键而不是 UUID答自增主键是顺序插入的B 树叶子节点顺序追加不会产生页分裂UUID 是无序的插入会导致频繁页分裂影响性能。此外UUID 占用空间大36 字符 vs 8 字节降低索引效率。追问二一张表可以没有主键吗答可以但 InnoDB 会自动选择一个非空唯一索引作为聚簇索引如果没有合适的会生成隐藏的 6 字节ROW_ID。但强烈不建议这样做应该显式定义主键。追问三联合主键和联合唯一索引有什么区别答本质区别和单列一样——联合主键是聚簇索引不允许任何列为 NULL联合唯一索引是二级索引允许列为 NULL。常见面试变体主键索引和唯一索引有什么区别聚簇索引和非聚簇索引的区别是什么MySQL 查询走唯一索引时为什么可能需要回表唯一索引允许 NULL 吗主键呢记忆口诀主键 vs 唯一索引主键聚簇叶子存完整数据查询不回表唯一二级叶子存主键值查询需回表NULL 有别主键不允许唯一索引可以数量不同主键唯一一个唯一索引多个总结主键索引是聚簇索引每表只能有一个不允许 NULL叶子节点存储完整行数据唯一索引是二级索引可以有多个允许 NULL可多个叶子节点存储主键值。查询时主键直接获取数据唯一索引需要回表。推荐使用BIGINT自增主键业务唯一约束用唯一索引。 欢迎加入小哈的星球你将获得:专属的项目实战多个项目 / 1v1 提问 /Java 学习路线 /学习打卡 / 每月赠书 / 社群讨论新项目《Spring AI 项目实战》正在更新中..., 基于 Spring AI Spring Boot 3.x JDK 21;《从零手撸仿小红书微服务架构》 已完结基于 Spring Cloud Alibaba Spring Boot 3.x JDK 17..., 点击查看项目介绍演示地址http://116.62.199.48:7070/《从零手撸前后端分离博客项目全栈开发》2期已完结,演示链接http://116.62.199.48/;专栏阅读地址https://www.quanxiaoha.com/column截止目前累计输出 100w 字讲解图 4013 张还在持续爆肝中..后续还会上新更多项目目标是将 Java 领域典型的项目都整一波如秒杀系统, 在线商城, IM 即时通讯Spring Cloud Alibaba 等等戳我加入学习解锁全部项目已有4500小伙伴加入1. 我的私密学习小圈子从0到1手撸企业实战项目~ 2. 面试官灵魂一问MySQL 深度分页如何优化修订版 3. 锁明明还没过期为什么另一个线程能抢进去 4. 阿里二面什么是 MySQL 回表查询如何避免修订版最近面试BAT整理一份面试资料《Java面试BATJ通关手册》覆盖了Java核心技术、JVM、Java并发、SSM、微服务、数据库、数据结构等等。 获取方式点“在看”关注公众号并回复 Java 领取更多内容陆续奉上。PS因公众号平台更改了推送规则如果不想错过内容记得读完点一下“在看”加个“星标”这样每次新文章推送才会第一时间出现在你的订阅列表里。 点“在看”支持小哈呀谢谢啦