前阵子帮人排查一条订单详情慢查询接口 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 GB4 个二级索引合计多占约 19.2 GB聚簇索引本身多占约 4.8 GB总计约 24 GB 的额外空间这还只是磁盘。索引变大直接导致 Buffer Pool 能缓存的页数变少同样的内存配置下命中率下降随机 IO 上升。上面那条慢查询就是这么来的。而且这个成本是持续性的不是一次性投入每次全表扫描、每次索引重建、每次备份恢复都要为这 120 字节买单。主键类型单行占用4000 万行聚簇索引4 个二级索引额外开销典型场景BIGINT UNSIGNED8 字节约 320 MB基准绝大多数业务表BINARY(16)存 UUID16 字节约 640 MB约 3.2 GB需要全局唯一 IDCHAR(32)订单号128 字节约 5.1 GB约 19.2 GB早期遗留设计VARCHAR(64)业务编码最长 256 字节更大更大不建议提示这张表里的数字是按“主键值本身占用”估算的实际还要加上行头、变长字段长度列表、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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 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 亿。这个数字听起来很大但算一下就知道不够用日均写入量INT UNSIGNED可支撑天数BIGINT UNSIGNED可支撑年数100 万约 4295 天11.8 年约 5.8 亿年1000 万约 429 天1.2 年约 5800 万年1 亿约 43 天约 580 万年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 方案对比方案产出类型单机吞吐依赖主要风险适用场景数据库自增BIGINT受限于 DB单库分库后冲突单库单表号段模式BIGINT很高DB 本地缓存服务重启丢段、ID 不连续中大型业务表雪花算法BIGINT约 409 万/秒机器 ID 分配时钟回拨高并发分布式UUID v4BINARY(16)极高无随机写、页分裂日志、临时表、非核心链路Redis INCRBIGINT高Redis持久化丢失、重启回退中小规模、已有 Redis选型时我的优先顺序是单库用自增分库用号段跨机房或对时延极敏感的场景用雪花。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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;用(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 常见问题速查表现象可能原因排查方向处理方式插入报主键重复且值等于上限自增耗尽查SHOW TABLE STATUS的Auto_increment改BIGINT UNSIGNED唯一索引冲突但业务判断不重复排序规则大小写不敏感 / 尾部空格查字段COLLATION改_bin排序规则软删除后无法重新插入同单号唯一索引未排除已删除行查索引定义加删除标记列进唯一索引分页查询越翻越慢主键过长导致二级索引回表成本高EXPLAIN看rows和回表次数换短主键或覆盖索引批量插入耗时随数据量线性上升随机主键导致页分裂看表空间增长与填充率改有序 ID 或调整插入顺序INSERT IGNORE后数据缺失其他错误被静默吞掉查SHOW WARNINGS改用ON DUPLICATE KEY UPDATE主从数据不一致REPLACE INTO触发级联删除看 binlog 中的 DELETE 记录禁用REPLACE改判重更新我自己在这些坑里栽得最深的是第二个一个短链服务上线三个月后开始零星报唯一冲突查了两天才定位到排序规则。从那以后凡是字符串类型的业务唯一键我建表时一律显式写COLLATE绝不含糊。6. 一点个人取舍现在我做新表默认就是BIGINT UNSIGNED自增主键加业务唯一索引这一套但在动手之前一定会多问一句这张表的业务键未来会不会变如果答案是“可能会”那连唯一索引都要想清楚要不要加、加了以后业务规则变更时怎么迁移。这个习惯帮我省过好几次返工。反过来字典表、关联表、归档对账表这几类我会毫不犹豫地用业务主键或复合主键因为它们的数据量、变更频率和使用方式和业务主表完全不是一回事。主键设计没有标准答案只有对场景的判断判断的依据就是稳定性、长度、写入模式和变更成本这四个变量。把这四个变量在纸上列出来答案基本就自己浮出来了。 
长河君 · 2026/11/3 4:36:08 · 阅读 1.2k 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 GB4 个二级索引合计多占约 19.2 GB聚簇索引本身多占约 4.8 GB总计约 24 GB 的额外空间这还只是磁盘。索引变大直接导致 Buffer Pool 能缓存的页数变少同样的内存配置下命中率下降随机 IO 上升。上面那条慢查询就是这么来的。而且这个成本是持续性的不是一次性投入每次全表扫描、每次索引重建、每次备份恢复都要为这 120 字节买单。主键类型单行占用4000 万行聚簇索引4 个二级索引额外开销典型场景BIGINT UNSIGNED8 字节约 320 MB基准绝大多数业务表BINARY(16)存 UUID16 字节约 640 MB约 3.2 GB需要全局唯一 IDCHAR(32)订单号128 字节约 5.1 GB约 19.2 GB早期遗留设计VARCHAR(64)业务编码最长 256 字节更大更大不建议提示这张表里的数字是按“主键值本身占用”估算的实际还要加上行头、变长字段长度列表、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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 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 亿。这个数字听起来很大但算一下就知道不够用日均写入量INT UNSIGNED可支撑天数BIGINT UNSIGNED可支撑年数100 万约 4295 天11.8 年约 5.8 亿年1000 万约 429 天1.2 年约 5800 万年1 亿约 43 天约 580 万年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 方案对比方案产出类型单机吞吐依赖主要风险适用场景数据库自增BIGINT受限于 DB单库分库后冲突单库单表号段模式BIGINT很高DB 本地缓存服务重启丢段、ID 不连续中大型业务表雪花算法BIGINT约 409 万/秒机器 ID 分配时钟回拨高并发分布式UUID v4BINARY(16)极高无随机写、页分裂日志、临时表、非核心链路Redis INCRBIGINT高Redis持久化丢失、重启回退中小规模、已有 Redis选型时我的优先顺序是单库用自增分库用号段跨机房或对时延极敏感的场景用雪花。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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;用(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 常见问题速查表现象可能原因排查方向处理方式插入报主键重复且值等于上限自增耗尽查SHOW TABLE STATUS的Auto_increment改BIGINT UNSIGNED唯一索引冲突但业务判断不重复排序规则大小写不敏感 / 尾部空格查字段COLLATION改_bin排序规则软删除后无法重新插入同单号唯一索引未排除已删除行查索引定义加删除标记列进唯一索引分页查询越翻越慢主键过长导致二级索引回表成本高EXPLAIN看rows和回表次数换短主键或覆盖索引批量插入耗时随数据量线性上升随机主键导致页分裂看表空间增长与填充率改有序 ID 或调整插入顺序INSERT IGNORE后数据缺失其他错误被静默吞掉查SHOW WARNINGS改用ON DUPLICATE KEY UPDATE主从数据不一致REPLACE INTO触发级联删除看 binlog 中的 DELETE 记录禁用REPLACE改判重更新我自己在这些坑里栽得最深的是第二个一个短链服务上线三个月后开始零星报唯一冲突查了两天才定位到排序规则。从那以后凡是字符串类型的业务唯一键我建表时一律显式写COLLATE绝不含糊。6. 一点个人取舍现在我做新表默认就是BIGINT UNSIGNED自增主键加业务唯一索引这一套但在动手之前一定会多问一句这张表的业务键未来会不会变如果答案是“可能会”那连唯一索引都要想清楚要不要加、加了以后业务规则变更时怎么迁移。这个习惯帮我省过好几次返工。反过来字典表、关联表、归档对账表这几类我会毫不犹豫地用业务主键或复合主键因为它们的数据量、变更频率和使用方式和业务主表完全不是一回事。主键设计没有标准答案只有对场景的判断判断的依据就是稳定性、长度、写入模式和变更成本这四个变量。把这四个变量在纸上列出来答案基本就自己浮出来了。