MySQL主键选型：逻辑主键、业务主键与二级索引回表成本

前阵子帮人排查一条订单详情慢查询，接口 P99 从 80ms 涨到 600ms。慢日志里那条 SQL 写得干干净净，走的是订单号上的唯一索引，问题出在回表：这张表的主键直接用了CHAR(32)的订单号，二级索引的叶子节点里存的就是这 32 个字符，回表时还得拿它去聚簇索引里比对一遍。数据量爬到四千万行以后，每次回表基本等于一次随机页读取，Buffer Pool 根本压不住。这事让我再次确认，逻辑主键和业务主键怎么选，从来不是一条设计规范的问题，而是一笔会真金白银落到磁盘、内存和延迟上的工程账。下面我就把这几年踩过的坑、算过的账、写过的建表语句摊开讲，尤其是那些文档里不写、只有上线后才会疼的细节。

1. 先把概念摆正：逻辑主键与业务主键到底指什么

1.1 逻辑主键：系统生成的唯一标识，不承载任何业务含义

逻辑主键还有几个常见叫法：代理键、技术主键、无意义主键。它唯一的工作就是给一行数据发个号，这个号本身不解释任何事情。用户表里id = 10086，你从这个数字里读不出用户的手机号、注册时间、所属渠道，它只是逻辑上标识了“这是第 10086 行”。常见的实现形态有三种：数据库自增的BIGINT UNSIGNED、UUID、以及雪花类的时间有序 ID。

它最大的价值在于稳定和与业务解耦。业务规则怎么改、手机号怎么换、编码规则怎么升位，主键都纹丝不动。这看起来是个很小的优点，但它是整个数据模型能不能扛住业务迭代的底盘。我见过太多表因为主键带业务含义，在规则变更时被迫做全表刷数据，一刷就是一整夜，刷完还得担心下游系统里存的老值没同步。

另一个容易被忽略的点是：逻辑主键通常由数据库或发号器统一生成，插入时不需要业务代码参与。这意味着你写一张新表的插入逻辑时，可以把注意力全放在业务字段校验上，不用分心去构造一个“保证不重复”的编号。

1.2 业务主键：拿天然唯一的业务字段直接当主键

业务主键也叫自然键，指的是业务上本来就唯一、且能被人读懂的字段，比如订单号、身份证号、学号、设备序列号、ISBN 号。用它当主键的好处非常直观：少一列、少一个索引、join 的时候少一层映射，出问题时看一眼数据就知道是哪条记录。

早些年很多系统确实是这么设计的，尤其是一些从 Excel 迁移过来的内部管理系统，直接用“工号”或者“项目编号”当主键，简单粗暴，开发速度极快。数据量小、业务规则稳定、单库单表的年代，这套做法几乎没毛病。

但它的风险同样直观：业务字段的唯一性是被业务规则保证的，而业务规则是会变的。订单号从 18 位升到 22 位、身份证从 15 位升到 18 位、商品编码体系整体重构、手机号被用户换绑，任何一次变更都会直接冲击主键。更麻烦的是，主键一旦被别的表引用、被写进日志、被下游系统落库，改起来就是一场跨团队的协调战。

1.3 更常见的形态其实是“逻辑主键 + 业务唯一索引”

现实中绝大多数正经项目既不是纯逻辑主键，也不是纯业务主键，而是两者的组合：主键用自增BIGINT或雪花 ID，业务字段上再建一个UNIQUE索引。这样既拿到了逻辑主键的稳定性，又用唯一索引兜住了业务层面的不重复。

这里要澄清一个中文语境里常见的混淆。有人说的“逻辑主键”，指的是“逻辑上存在、物理上靠唯一索引实现的主键”，强调的是不设物理主键、只用唯一约束。而标题里对比的“逻辑主键 vs 业务主键”，说的是代理键和自然键的区别。两件事不是一回事，但结论殊途同归：物理主键尽量选无业务含义的短字段，业务唯一性交给唯一索引去保证。

理解了这一层，后面的所有取舍其实都是在回答一个问题：这个业务字段，到底该不该被提升到主键的位置上。

2. 选型背后的五个真实考量

2.1 稳定性：业务规则比你以为的更容易变

做技术方案时最容易犯的错，是拿“当前”的业务规则去推演“未来三年”。我参与过一个商品中心的改造，早期商品编码是 8 位数字，主键就用它，跑了四年相安无事。第五年业务要接入外部供应商，编码体系必须扩展成“渠道前缀 + 12 位数字”，长度和格式全变了。结果就是主表刷数据、六张关联表跟着改外键、三个下游系统重新对接，前后折腾了两个月。

这件事给我的教训很直接：判断一个字段能不能当主键，不要问“它现在唯不唯一”，要问“它未来会不会变”。订单号会变，因为营销规则会调整；身份证号会变，因为证件体系升级过；手机号会变，因为用户会换号；设备序列号会变，因为厂商会召回重编。反过来，自增 ID 和雪花 ID 的唯一性只依赖于发号器本身，跟业务规则零耦合。

有一种情况可以例外：字典表里的国家代码、币种代码、行政区划代码，这类字段的变更频率极低，而且变更是全局统一的，用它当主键反而省事。判断标准不是“有没有业务含义”，而是“变更的概率和影响面有多大”。

2.2 存储与索引成本：主键长度会被二级索引成倍放大

这是最容易被低估、也是最能算出真金白银的一条。InnoDB 是索引组织表，聚簇索引的叶子节点就是数据行本身，而每一个二级索引的叶子节点里都存着一份主键值。也就是说，主键有多长，你的每一个二级索引就要跟着胖多少。

拿一张订单表算笔账。假设主键用CHAR(32)的订单号，字符集utf8mb4，那么主键在索引里占 32 × 4 = 128 字节；换成BIGINT UNSIGNED，只占 8 字节，差 120 字节。这张表如果建了 4 个二级索引，四千万行数据：

• 每个二级索引多占：120 字节 × 4000 万 ≈ 4.8 GB
• 4 个二级索引合计多占：约 19.2 GB
• 聚簇索引本身多占：约 4.8 GB
• 总计约 24 GB 的额外空间

这还只是磁盘。索引变大直接导致 Buffer Pool 能缓存的页数变少，同样的内存配置下命中率下降，随机 IO 上升。上面那条慢查询就是这么来的。而且这个成本是持续性的，不是一次性投入，每次全表扫描、每次索引重建、每次备份恢复，都要为这 120 字节买单。

提示：这张表里的数字是按“主键值本身占用”估算的，实际还要加上行头、变长字段长度列表、NULL 位图等开销，真实差距只会更大。

2.3 写入性能：顺序插入和随机插入差的是一个数量级

自增主键的另一个隐性优势是写入模式。自增值单调递增，新行永远插在 B+ 树最右侧的页里，页写满就开新页，填充率能稳定在 15/16 左右，也就是约 93.75%。这种顺序写对磁盘和 Buffer Pool 都极其友好。

UUID v4 就完全是另一回事了。它是随机分布的，新行会插到 B+ 树的任意位置，一旦目标页满了就要触发页分裂：把一个页拆成两个，各装一半数据，同时还要更新父节点的指针。频繁分裂的直接后果是页填充率掉到 50% 到 70%，同样的数据量占用更多页，扫描时 IO 次数上升，写入时维护成本也上升。数据量越大，这个雪球滚得越狠。

MySQL 8.0 默认的innodb_autoinc_lock_mode = 2（交叉模式）已经大幅降低了自增锁的争用，批量插入时性能表现很好。如果确实需要 UUID 的全局唯一性，有两个缓解手段：一是 MySQL 8.0 提供的UUID_TO_BIN(uuid, 1)，通过交换时间高低位把随机 UUID 变成时间有序的二进制，存成BINARY(16)；二是直接改用 UUID v7 或 ULID 这类天生有序的方案。这两个做法的本质都是把随机写掰回顺序写。

2.4 分布式与数据集成：自增的边界在哪里

单库单表时，自增主键几乎没有对手。一旦上了分库分表，自增就立刻失效——两个库各自从 1 开始自增，主键必然冲突。这时候要么改造发号机制，要么换主键方案。

常见的分布式 ID 方案有四类：数据库号段模式、雪花算法、Redis 自增、以及数据库自增配合步长。号段模式是从数据库里一次批量取一段 ID 缓存在本地，用完再取，把数据库压力降到千分之一；雪花算法是 64 位整数，1 位符号 + 41 位毫秒时间戳 + 10 位机器 ID + 12 位序列号，单机每秒能产出约 409.6 万个 ID，但依赖机器时钟，时钟回拨要做处理。这两类方案产出的都是BIGINT，对索引长度友好，是目前的主流选择。

数据集成场景还要多考虑一层。做数据同步、跨库合并、历史数据归档时，自增 ID 在不同环境里会重复，测试库的id = 100和生产库的id = 100完全不是同一条数据，合并时必须重新映射。而业务主键因为带业务语义，天然适合做对账的锚点。所以很多团队的做法是：主键用自增，同时保留业务唯一键，同步和对账都走业务键，两套体系各司其职。

2.5 安全与可读性：两笔账要分开算

自增主键有一个常被忽视的副作用：它会暴露业务量。URL 里出现/order/1001，稍微有点心思的人就能通过遍历估算出你的日订单量级，甚至能批量爬取数据。这不是危言耸听，早期不少系统就吃过这个亏。解决办法不是换主键，而是在对外接口层加一层不可枚举的对外编号，比如对外暴露一个随机生成的order_no，内部查询仍然走自增主键。

可读性则是另一笔账。业务主键确实方便人工排查，看到ORD202405120001就知道是哪天哪批的单子。但这个便利完全可以通过在表里多存一个业务单号字段来实现，没必要把它提升到主键位置。可读性是查询和运维层面的需求，不该由存储结构层面的主键来承担，这两件事混在一起，后面往往两头不讨好。

3. 落地实操：一张订单表的主键设计全过程

3.1 建表语句与字段类型选择

下面是我现在做订单表的标准写法，删掉了业务字段，只保留跟主键设计相关的部分：

CREATE TABLE `t_order` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '逻辑主键，无业务含义', `order_no` VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT '业务订单号，对外暴露', `user_id` BIGINT UNSIGNED NOT NULL COMMENT '下单用户ID', `amount` DECIMAL(12,2) NOT NULL DEFAULT '0.00' COMMENT '订单金额', `status` TINYINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '订单状态', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`), UNIQUE KEY `uk_order_no` (`order_no`), KEY `idx_user_created` (`user_id`, `created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单主表';

几个细节值得单独说。第一，主键用BIGINT UNSIGNED而不是INT，原因在下一节算。第二，order_no显式指定ascii字符集和ascii_bin排序规则，订单号都是数字加字母，用 ascii 每个字符只占 1 字节，比 utf8mb4 省四分之三，而且二进制排序避免了大小写不敏感的坑。第三，唯一索引命名统一用uk_前缀，普通索引用idx_前缀，出问题时一眼能分清索引类型。

注意：不要给自增主键加UNSIGNED之外的花哨修饰，也不要试图用AUTO_INCREMENT之外的方式手工维护自增值，比如应用层先SELECT MAX(id)再 +1，并发下必然撞车。

3.2 容量估算：INT 到底能撑多久

INT UNSIGNED的最大值是 4294967295，约 42.9 亿。这个数字听起来很大，但算一下就知道不够用：

BIGINT UNSIGNED的最大值是 18446744073709551615，约 1.8 × 10¹⁹，按日均 1 亿条算也能撑几百万年，实际约束根本不是数值上限，而是磁盘容量。所以结论很简单：新表一律用BIGINT UNSIGNED，不要为了省 4 个字节去赌业务增长速度。4 个字节乘以一亿行也才 400MB，而一次主键类型变更带来的停机、刷数据、改关联表的成本，是这个数字的几百倍。

估算公式可以记一下：可支撑天数 = 最大值 ÷ 日均写入量。做容量规划时再留 30% 的余量，因为大促、补数据、批量导入都会让瞬时写入量远超日均值。

3.3 业务键做唯一索引的正确写法

把业务字段降级成唯一索引，有几个坑必须提前处理。

第一是软删除与唯一索引的冲突。很多表用deleted_at做软删除，一条订单号删了以后再想重新插入同一条，唯一索引会直接报冲突。解决方案要么是唯一索引里带上删除标记列（UNIQUE KEY uk_order_no (order_no, deleted_flag)），要么干脆用物理删除加归档表。带deleted_at时间戳进唯一索引是不行的，因为NULL在唯一索引里不参与去重，多条NULL会共存，反而破坏了约束。

第二是幂等插入的写法选择。INSERT IGNORE用起来最省事，但它会把所有错误都吞掉，包括字段超长、类型不匹配、非空约束失败，出问题时排查起来极其痛苦。REPLACE INTO更危险，它的实现是“删除 + 插入”，会触发级联删除、丢失未指定字段的值、在 binlog 里留下两条记录。相对稳妥的是INSERT ... ON DUPLICATE KEY UPDATE，但要注意它会影响自增序列，即使没插入成功也会消耗一个自增值。更严谨的做法是先SELECT判断再插入，或者用ON DUPLICATE KEY UPDATE id = id这种只做判重不改数据的写法。

第三是唯一索引的列顺序。如果是复合唯一索引，比如(tenant_id, order_no)，把区分度高的列放后面、等值查询用得多的列放前面，能顺带服务一些查询场景，省掉一个单独的索引。

3.4 分库分表场景下的 ID 方案对比

选型时我的优先顺序是：单库用自增，分库用号段，跨机房或对时延极敏感的场景用雪花。UUID 我只在非核心链路用，比如消息去重、临时任务 ID，绝不拿它当核心业务表的主键。

4. 业务主键不该被一棍子打死

4.1 字典表、配置表：语义清晰比性能更重要

前面说了那么多逻辑主键的好话，但有一类表我至今仍然倾向用业务主键：字典表和配置表。

比如币种表，主键直接用CHAR(3)的币种代码（CNY、USD、EUR），好处是任何一张业务表存了币种字段，看到值就知道是什么，不需要 join 回字典表。这类表的数据量通常在几十到几百行，索引长度带来的开销可以忽略不计，业务规则也基本不会变，稳定性风险极低。国家代码、行政区划代码、行业分类代码，都是同一个逻辑。

判断标准还是那两条：数据量小到主键长度无关紧要，业务规则的变更概率低到可以忽略。两条都满足，用业务主键是划算的；只满足一条，就要谨慎了。

4.2 关联表与复合主键的适用边界

多对多关联表是复合主键的经典场景。比如用户角色表：

CREATE TABLE `t_user_role` ( `user_id` BIGINT UNSIGNED NOT NULL, `role_id` BIGINT UNSIGNED NOT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`user_id`, `role_id`), KEY `idx_role_user` (`role_id`, `user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

用(user_id, role_id)当复合主键，天然保证了不重复，省掉一个自增列和一个唯一索引，插入时也不需要额外查询判重——重复插入直接报主键冲突，用INSERT IGNORE就能实现幂等。这个场景下，复合主键的优势非常明显。

但要注意一个 InnoDB 的细节：复合主键的所有列都会被完整复制到每一个二级索引里。上面那张表的idx_role_user索引，叶子节点里存的是(role_id, user_id, user_id, role_id)的完整组合。如果复合主键的列很多、很长，二级索引就会膨胀得厉害。所以复合主键适合用在“列少、列短、关联表本身不建太多二级索引”的场景，一旦主键列超过三列或者包含长字符串，就该考虑加一个代理主键了。

4.3 数据归档、对账与人工排查场景

还有一个场景，业务主键的价值会被放大：数据归档和跨系统对账。

归档表通常要跟主表做数据比对，确认哪些数据已经迁移、哪些还没迁。这时候拿业务单号做比对锚点，比拿自增 ID 靠谱得多，因为自增 ID 在不同环境、不同批次里完全不通用。对账更是如此，两个系统之间核对同一笔交易，靠的只能是业务流水号，不可能靠各自的自增 ID。

人工排查也是同理。半夜被告警叫起来，运维同事给你一个订单号，你WHERE order_no = 'xxx'就能直接定位，不需要先查映射表。这个便利不需要把order_no设成主键，只要建一个唯一索引就够了。主键负责存储结构，唯一索引负责业务语义，两者分工明确，各干各的活，这是我认为最舒服的设计。

5. 踩坑记录与问题速查

5.1 自增主键耗尽：报错信息会让你误判方向

INT UNSIGNED主键达到上限后，MySQL 不会友好地告诉你“自增用完了”，而是抛出一个Duplicate entry '4294967295' for key 'PRIMARY'。第一次遇到的人往往会以为是数据重复，去查业务逻辑，查半天才发现是自增到头了。INT有符号版本更早，21 亿就用完。

这类事故的可怕之处在于它发生在业务增长最快的时候，往往还伴随着大促或者活动高峰，属于典型的“屋漏偏逢连夜雨”。预防手段只有一个：建表时就用BIGINT UNSIGNED，别省那 4 个字节。已经上线的表如果要改，ALTER TABLE ... MODIFY COLUMN在大表上会锁表或者触发在线 DDL 的长耗时重建，几千万行的表动辄几十分钟，必须安排低峰期窗口，并且提前用pt-online-schema-change之类的工具评估。

5.2 唯一索引冲突引发的幂等事故

有一次线上出现间歇性的订单创建失败，日志里是唯一索引冲突，但业务侧确认是不同用户的不同订单。查下来发现订单号的生成规则里用了“日期 + 用户 ID 后四位 + 随机两位”，用户量上来以后，同一天同一时间段内碰撞概率大幅上升。这个问题的根子不在唯一索引，而在于把“大概率唯一”当成了“绝对唯一”。

唯一索引的价值恰恰在这里：它会把这种概率性错误变成确定性报错，让你在上线前就发现，而不是等到数据脏了以后再去清洗。所以我对业务单号的态度是：生成规则可以带随机，但必须有唯一索引兜底，并且插入逻辑要能正确处理冲突重试。重试时重新生成单号，最多重试三次，三次都冲突就告警，说明生成规则的设计有问题。

5.3 字符串主键的排序规则坑

用字符串当主键或唯一索引时，排序规则选错会带来非常隐蔽的问题。utf8mb4_general_ci是大小写不敏感的，'ABC123'和'abc123'会被判定为重复，插入时报唯一索引冲突。如果你的业务单号允许大小写共存（比如 Base62 编码的短链），这个坑一定会踩。

解决办法是显式指定二进制排序规则：ascii_bin或utf8mb4_bin。前者适合纯数字字母，每个字符只占 1 字节，索引更小；后者适合可能包含中文或其他字符的场景。另外CHAR类型会自动去掉尾部空格，VARCHAR则会保留，如果业务字段可能带尾部空格，两者行为不一致，也会导致唯一索引判断出现意外。

5.4 常见问题速查表

我自己在这些坑里栽得最深的是第二个，一个短链服务，上线三个月后开始零星报唯一冲突，查了两天才定位到排序规则。从那以后，凡是字符串类型的业务唯一键，我建表时一律显式写COLLATE，绝不含糊。

6. 一点个人取舍

现在我做新表，默认就是BIGINT UNSIGNED自增主键加业务唯一索引这一套，但在动手之前一定会多问一句：这张表的业务键，未来会不会变？如果答案是“可能会”，那连唯一索引都要想清楚要不要加、加了以后业务规则变更时怎么迁移。这个习惯帮我省过好几次返工。

反过来，字典表、关联表、归档对账表这几类，我会毫不犹豫地用业务主键或复合主键，因为它们的数据量、变更频率和使用方式和业务主表完全不是一回事。主键设计没有标准答案，只有对场景的判断，判断的依据就是稳定性、长度、写入模式和变更成本这四个变量。把这四个变量在纸上列出来，答案基本就自己浮出来了。