上周接手一个别人留下的库打开一张三百多万行的成绩表建表语句干干净净一个主键都没有连个唯一索引都找不到。当时想给某几行打个标记改了半天下不去手——因为没有稳定唯一的东西来定位这一行。在 MySQL 里表没有主键PRIMARY KEY它就等于没有身份证你嘴上说得清是哪一条SQL 里说不清。给表加主键看着只是在建表语句里敲几个单词真动手的时候牵出来的是存储结构、索引设计、自增机制、在线 DDL、主从延迟一整套东西。这篇就把MySQL 如何添加主键从里到外捋一遍包括建表时怎么定、已有数据的表怎么补、补完之后怎么验、以及我这些年踩过的坑。刚接触 MySQL 的同学能照着抄写过几年 SQL 的同学也能在联合主键、类型选型、大表变更这几处找到点新东西。1. 主键不是加个约束那么简单1.1 三条硬性规则先记牢再动手主键的规则不复杂但每一条都在实际操作里绊过人。第一条是唯一性同一个主键值在一张表里只能出现一次插入重复值直接报 1062。第二条是非空主键列上写不写 NOT NULL 都一样MySQL 会隐式把它变成 NOT NULL你硬塞一个 NULL 进去它要么报错要么按环境配置把 NULL 转成 0 或空串反正不会让你得逞。第三条是一张表只能有一个主键注意这里的一个指的是一个主键约束不是一列——由多列组成的主键叫组合主键也算一个主键。很多人第一次想给表再加一个主键的时候会撞上 1068 报错Multiple primary key defined意思就是你已经有了先删再加。还有人想我给两列分别加主键行不行不行只能合成一个组合主键。另外主键不能建在 BLOB、TEXT 这类大字段上主键也不支持前缀长度这种写法——普通索引可以写INDEX (url(20))主键不行MySQL 会直接拒绝。写完这三条再补一句最容易被忽略的主键在 InnoDB 里不只是一个逻辑约束它直接决定了数据行在磁盘上怎么排。这一点才是后面所有性能话题的源头。1.2 聚簇索引主键在 InnoDB 里的真实身份InnoDB 的表是按主键组织存储的学名叫聚簇索引。整张表的数据行就挂在主键的那棵 B 树叶子节点上主键相邻的行在物理上也挨着。你在表上建的其它索引二级索引叶子节点存的不是行地址而是主键值查的时候先走二级索引找到主键再拿主键回到聚簇索引里取整行这就是大家常说的回表。这里有个关键推论如果你的表没显式定义主键InnoDB 并不会裸奔它会找一个非空且唯一的索引来当聚簇索引要是连这样的索引都没有它会自己造一个 6 字节的隐藏 row_id。也就是说没主键的表性能不一定立刻崩但你对数据组织的控制权丢了。隐藏 row_id 由内部机制分配你既不能在 SQL 里用它定位也没法保证它在备份恢复、主从切换之后还和业务数据稳定对应。线上做过一次数据修复的人都知道这种看不见的定位键有多难受。再顺带说清楚一个高频疑问主键和索引是什么关系。加了主键InnoDB 就自动有了一棵聚簇索引你不需要再为主键列单独写一条CREATE INDEX写了也是浪费。反过来加普通索引不会让列变成主键。下面这张表把三种常见键的区别摊开对比一下维度主键 PRIMARY KEY唯一索引 UNIQUE普通索引 INDEX允许重复值不允许不允许允许允许 NULL不允许隐式 NOT NULL允许多个 NULL允许一张表能有几个1 个多个多个是否聚簇索引InnoDB 下是不是不是叶子节点存什么完整数据行主键值回表主键值回表典型用途行唯一标识业务唯一约束手机号、邮箱加速查询提示唯一索引允许多个 NULL是因为 SQL 标准里 NULL 不等于 NULL所以多个 NULL 之间不算重复。这个特性经常被用来做软删除唯一约束用 deleted_at 为 NULL 表示未删除未删除的行只允许一条。2. 建表阶段就把主键定好几种写法和选型逻辑2.1 自增整数主键默认答案但类型得算清楚绝大多数业务表我给的建议都是同一个id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY。理由有三层。顺序写入能让 InnoDB 不断往 B 树最右侧追加页几乎没有页分裂的随机 IO8 字节的整型足够窄二级索引里存主键值不会把索引撑大自增由数据库负责应用层不用操心并发冲突。类型选择上INT UNSIGNED 上限大约 42.9 亿BIGINT UNSIGNED 是 1.8 乘 10 的 19 次方实际等于用不完。很多人觉得42 亿够了吧我给个测算你就知道该不该赌主键类型占用字节理论上限日均 100 万行的表能撑日均 1000 万行的表能撑INT4约 21.4 亿有符号约 5.9 年约 7 个月INT UNSIGNED4约 42.9 亿约 11.7 年约 1.2 年BIGINT8约 9.2×10^18天文数字天文数字日志表、埋点表、流水表这类写多读少的表日增千万行不稀奇用 INT 就是在给自己埋一颗定时炸弹——真到溢出那天再改类型是要重建整表的。所以我的习惯是写多的大表一律 BIGINT UNSIGNED小配置表才用 INT。再说两个语法细节。自增列必须是某个索引的最左列否则报 1075也就是Incorrect table definition; there can be only one auto column and it must be defined as a key。你和别人一起写建表语句时如果报了这个先看自增列的索引位置对不对。另外自增锁模式innodb_autoinc_lock_mode在 MySQL 8.0 默认是 2交错模式5.7 默认是 1模式 2 并发插入性能更好代价是批量插入时自增值会不连续这个现象正常不要在业务里假设 id 连续。2.2 业务主键、UUID 和雪花 ID什么时候不用自增也不是所有表都该用自增整数。字典表、配置表这种数据量稳定、需要跨环境同步的表直接拿业务编码做主键挺合适比如country_code CHAR(2) PRIMARY KEY、config_key VARCHAR(64) PRIMARY KEY。好处是导出导入时主键不会串多环境对数据时不用做映射写 SQL 排查也直观看一眼就知道是哪条配置。前提是这个业务编码在流程上真的稳定不会被改。只要出现过改编码的需求这种主键就是灾难——一个值改了所有引用它的地方都得跟着改。UUID 我用得比较谨慎。CHAR(36) 的字符串 UUID 有两个问题一是宽36 字节比 INT 大 9 倍你的每个二级索引叶子都会带上这个主键值索引体积膨胀得非常明显二是随机新插入的行主键值乱跳会不断往 B 树中间插页触发页分裂和磁盘随机写。实测过一张用 UUID 做主键的日志表写入吞吐大概是同结构自增主键表的一半左右。如果确实需要 UUID至少存成 BINARY(16)体积能压到 16 字节但随机性带来的页分裂还是躲不掉。真正在分布式场景下合理的折中是趋势递增的 ID 方案比如按时间戳加机器位加序列位生成的 64 位整数或者 ULID 这类有序标识。它们既能由应用自己生成、不依赖数据库又保证了整体递增插入还是往右追加为主。代价是需要自己维护发号逻辑并且要接受时间回拨这类边界问题。选型时的判断标准很简单如果这张表将来要分库分表自增就会成为阻碍那就提前上趋势递增 ID如果单库单表能撑很多年老老实实用自增别为了想象中的扩展性把现在搞复杂。2.3 联合主键什么时候值得用列的顺序怎么排联合主键最经典的场景是关联表和明细表。比如学生成绩表一个学生一门课只有一条成绩记录学生的学号加课程号天然唯一直接PRIMARY KEY (student_id, course_id)就行。这样做还有个好处InnoDB 的聚簇索引本身就是按这两列排序的查某学生所有课程的成绩能用上主键的最左前缀查某门课所有学生的成绩也只需要扫主键的一段范围不用额外建索引。但联合主键的列顺序不是随便写的要按查询模式来定。上面那张成绩表如果业务里更大的查询量是按课程统计平均分那顺序就该反过来把 course_id 放前面。最左前缀原则在这里是硬约束PRIMARY KEY (student_id, course_id)能加速WHERE student_id ?但加速不了WHERE course_id ?后者只能全表扫或者额外建索引。我见过不少项目组合主键的顺序是按哪个列名念起来顺口排的上线后慢查询一堆最后只能补索引白白多花一份写开销。联合主键的另一个代价是宽度。如果组合里有 VARCHAR(64) 这样的大列那所有二级索引都要背上这 64 字节主键越宽索引膨胀越严重。所以组合主键里我一般只放短列长文本列即使要参与唯一性约束也倾向用唯一索引或者加一个哈希列而不是塞进主键。最后一个反直觉的点联合主键的两列都是不能为 NULL如果业务上某一列确实可能为空比如可选的规格 ID那它就不适合做主键硬塞进去等于逼着应用层造一个假值来占位时间长了数据就脏了。2.4 设计阶段就该定下来的几件小事写建表语句之前有几件事在评审阶段定下来比事后补主键便宜得多。第一是命名我习惯统一用id做主键列名业务含义列单独命名关联表里用表名_id这样几乎不会出现两个含义不同的东西都叫 id。第二是在 ER 图上把主键标出来主键属性加下划线是通用画法评审的时候谁负责哪张表一目了然也可以直接在 ER 工具里配置成生成 PRIMARY KEY 约束避免手写建表语句时漏掉。第三是明确这张表有没有天然的唯一定位方式如果连业务上都说不出什么东西是唯一的那这张表大概率需要一个代理主键。顺手记一份评审清单我每次建表都会过一遍有没有显式主键、主键类型够不够宽、有没有意外变成组合主键的地方、外键引用的是不是主键、将来是否会分库分表。这五条过完基本能挡掉八成后期返工。3. 已有数据的表怎么补主键按数据现状分四条路走3.1 有唯一且非空的列直接 ADD PRIMARY KEY这是最省事的一种表里已经有一列数据上唯一且没有 NULL那就一条语句的事ALTER TABLE student ADD PRIMARY KEY (student_no);但动手之前一定要先自己确认别等 MySQL 报错。三条自查 SQL 我每次都跑-- 1. 看有没有重复 SELECT student_no, COUNT(*) AS c FROM student GROUP BY student_no HAVING c 1 LIMIT 20; -- 2. 看有没有 NULL 或空串 SELECT COUNT(*) FROM student WHERE student_no IS NULL OR student_no ; -- 3. 看现有主键和历史遗留的索引 SHOW INDEX FROM student; SHOW CREATE TABLE student;第一条有结果就说明有重复得先去重第二条有结果就得先把 NULL 补掉。第三条经常被人忽略有些历史表里已经有一个 AUTO_INCREMENT 列但没设成主键这时直接 ADD PRIMARY KEY 会撞上 1075因为自增列必须挂在某个索引上。还有一种情况是表上已经存在同名或同列的索引加主键之后会留下一个冗余索引白白增加写开销加完记得清理。注意ADD PRIMARY KEY 这一步在 MySQL 5.7 和 8.0 里通常支持在线执行、不阻塞写但它会重建整张表磁盘空间至少预留表数据量的一倍以上。执行前先看df -h别把实例写满。3.2 没有唯一列加一个自增列再提为主键现实中最常见的就是这种表里全是业务列同一时刻确实没有哪一列能唯一标识一行。最干净的解法是加一个自增列并一步到位设成主键ALTER TABLE student ADD COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;这条语句 MySQL 会按现有的物理存储顺序给每一行填上自增值然后把它建成聚簇索引。FIRST是让新列排在第一列方便阅读不加就默认追加到末尾功能上没差别。这里有个坑值得单独说。如果你拆成两步先ADD COLUMN id BIGINT NOT NULL再ADD PRIMARY KEY (id)那第一步在非严格模式下会把所有行填成 0第二步立刻报 1062 主键重复在严格模式下第一步可能直接报错。所以要么一条语句带上 AUTO_INCREMENT 和 PRIMARY KEY 一起写要么先加上带默认值的普通列赋值完再改。这个顺序问题坑过太多人我第一次做线上变更时就是分了两步深夜回滚。如果你的诉求不是自增而是按某个顺序重新编号比如想让 id 按创建时间从小到大排那就需要手动赋值。MySQL 8.0 里推荐用窗口函数别再用用户变量那套老写法8.0 已经把 UPDATE 中的用户变量赋值标记为不推荐-- 先把列加上不带自增 ALTER TABLE student ADD COLUMN id BIGINT UNSIGNED NOT NULL DEFAULT 0; -- 按 created_at 顺序编号 UPDATE student s JOIN ( SELECT id AS old_key, ROW_NUMBER() OVER (ORDER BY created_at, id) AS rn FROM student ) x ON s.id x.old_key SET s.id x.rn;注意上面这个自连接写法有个前提表里得有另一列能唯一标识行比如原来的 old_key。如果连这个都没有就得靠临时表或者分页批量处理。5.7 环境下没有窗口函数可以用变量写法SET rn : 0; UPDATE student SET id (rn : rn 1) ORDER BY created_at;这个写法能用但它对执行计划有依赖加索引后行为可能变线上用之前务必在预发环境验一遍结果。3.3 有重复值或空值先去重再补约束数据有重复的话加主键必然报 1062Duplicate entry。去重这件事没有通用答案得先问清楚业务重复数据是重复插入造成的还是本来就有多条有效记录。如果是前者保留哪一条我的默认策略是保留最早创建的那条id 最小因为它大概率是原始记录。MySQL 8.0 用窗口函数去重最直观DELETE s FROM student s JOIN ( SELECT id, ROW_NUMBER() OVER (PARTITION BY student_no ORDER BY created_at, id) AS rn FROM student WHERE student_no IS NOT NULL ) d ON s.id d.id AND d.rn 1;5.7 里用自连接效果一样只是大表上要注意走索引DELETE s1 FROM student s1 JOIN student s2 ON s1.student_no s2.student_no AND s1.id s2.id WHERE s1.student_no IS NOT NULL;空值处理相对简单但也要小心。先把 NULL 或空串替换成一个业务上不可能出现的占位值或者从其它列推导出真实值再改成 NOT NULL 并加主键。替换时最好分批发UPDATE student SET student_no CONCAT(TMP, id) WHERE student_no IS NULL OR student_no LIMIT 10000;加 LIMIT 分批是为了控制单事务大小避免长事务把 undo 撑爆、把主从延迟拉高。去重和改值这类操作一定要先备份或至少先 SELECT 出受影响的行存成临时表线上直接 DELETE 是新手最容易犯的错。3.4 大表加主键的代价与执行窗口加主键意味着重建整张表把所有数据读出来按新的聚簇索引写一遍再替换原表。这个过程的资源消耗跟表大小成正比几个容易被低估的点磁盘需要额外空间表数据量级别、binlog 会产生大量事件如果开了 row 格式更明显、主从延迟会被拉高、期间这条 DDL 持有元数据锁的时间可能影响新连接。几点实操经验。第一先估算时间用一张全量数据量相当的预发表跑一遍记录耗时再决定窗口。我一般要求操作窗口至少是估算耗时的三倍。第二试探在线算法能力ALTER TABLE student ADD PRIMARY KEY (student_no), ALGORITHMINPLACE, LOCKNONE;如果返回ALGORITHMINPLACE is not supported这类错误就说明当前场景必须走拷贝表那就更要慎选时间。第三超大表不要硬来用现成的在线改表工具把流程做成一小批一小批的数据搬迁加增量同步主库压力可控中途中断也能恢复。切表方案要提前准备好回滚脚本别到了出问题的时候临时想。第四变更期间盯几个指标Threads_running、SHOW SLAVE STATUS里的延迟、磁盘剩余、以及业务侧的慢查询数量。任何一个异常就停。4. 主键的后期变更与维护操作4.1 换列、改名、改类型顺序错了就报错线上换主键的需求不罕见比如原来用业务编码做主键现在要换成自增 id。最容易踩的坑是 DROP 和 MODIFY 的顺序。假设表结构是id INT AUTO_INCREMENT PRIMARY KEY你直接执行ALTER TABLE t DROP PRIMARY KEY;会收到 1075 报错——自增列必须挂在某个索引上你把主键删了它就悬空了。正确顺序是先去掉自增属性再删主键ALTER TABLE t MODIFY id BIGINT UNSIGNED NOT NULL; ALTER TABLE t DROP PRIMARY KEY;上面两条也可以合并成一条ALTER TABLE t MODIFY id BIGINT UNSIGNED NOT NULL, DROP PRIMARY KEY;多数版本能一次处理完但如果报 1075拆开执行即可不用纠结。换成新主键时一条语句搞定更省事也更安全因为中间不会出现没有主键的窗口ALTER TABLE t DROP PRIMARY KEY, ADD PRIMARY KEY (new_col);前提是 new_col 已经唯一且非空否则会报 1062。还要检查外键如果你的表被别的表引用改主键类型或列时外键约束会拦住你典型的报错是 1215 或者 errno 150提示无法添加外键约束。处理方式是先删外键、改完主键再加回来或者用SET FOREIGN_KEY_CHECKS0临时关闭校验这个操作风险高只建议在明确知道后果、且能在同一事务窗口内恢复时使用。列改名的情况现在好办多了。MySQL 8.0 支持ALTER TABLE t RENAME COLUMN old_name TO new_name;主键约束会自动跟着改名不会丢。5.7 及更早版本只能CHANGE COLUMN写的时候必须把列定义完整重复一遍少写一个 NOT NULL 或者类型写错主键定义就可能被悄悄改掉改完一定要SHOW CREATE TABLE复核。4.2 自增值的重置、跳号与持久化加完主键之后经常要调自增起点比如数据迁移过来的表想让新数据从一个特定的号开始ALTER TABLE t AUTO_INCREMENT 100000;这条语句只能往上调如果填的值小于等于当前已有最大值MySQL 会忽略它实际生效值变成当前最大值加一。所以别指望用它来重新从 1 开始物理删除所有行也不行删除后计数器仍然记着历史最大值8.0 起自增值是持久化的重启也不会退回去。自增跳号是另一个高频困惑。原因有三类批量插入时 MySQL 会一次性预分配一批值没用完的就丢了事务回滚不会归还已经分配出去的值innodb_autoinc_lock_mode2下多个并发插入交错取值。这三种情况都正常自增主键只保证唯一和递增趋势不保证连续。任何依赖id 连续做业务逻辑的设计都是错的比如用id差值算新增用户数、用id % N做分片分片要用专门的哈希列。我在一个项目里见过用自增 id 做订单号尾号规则的跳号之后对账全乱最后只能加一个真正的订单号列。4.3 加完主键之后必做的一轮体检变更执行完不等于结束我固定会跑这几个检查。第一SHOW CREATE TABLE t;看主键定义是否符合预期特别是组合主键的列顺序和自增属性。第二SHOW INDEX FROM t;确认 Key_name 为 PRIMARY 的那一行 Non_unique 是 0顺便看看 Cardinality 是否正常如果显示为 1 或明显偏小执行ANALYZE TABLE t;刷新统计信息。第三从information_schema交叉验证一遍SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA your_db AND CONSTRAINT_NAME PRIMARY AND TABLE_NAME student ORDER BY ORDINAL_POSITION;第四用EXPLAIN看一条按主键查的语句type 应该是 const 或 eq_ref如果还是 ALL 或者 index说明有别的因素在干扰。第五检查应用侧ORM 映射里的主键注解要跟着改MyBatis 的 resultMap、JPA 的 Id、自增回填配置useGeneratedKeys一个都不能漏这块漏了的表现是插入成功但拿不到 id。第六跑一轮核心业务的冒烟尤其是依赖主键做幂等、做去重的逻辑。5. 踩坑实录与排查速查5.1 报错速查表下面这张表是我这些年攒下来的遇到报错先来这儿对一眼能省不少搜索时间报错编号/提示触发场景处理思路1068 Multiple primary key defined表里已经有主键又执行 ADD PRIMARY KEY先DROP PRIMARY KEY再重新添加或合并成一条 ALTER1075 auto column must be defined as a key删主键时表里有 AUTO_INCREMENT 列或自增列不在索引最左先去自增属性再删主键加自增列时同步建索引1062 Duplicate entry目标列有重复值先去重再考虑加主键1170 BLOB/TEXT column used in key specification想用 TEXT/BLOB 列做主键换短列或加一个哈希列做主键1215 / errno 150改主键时被外键约束拦住先删外键、改完再加回1071 Specified key was too long组合主键总长度超限缩短列长度或改用更紧凑的类型ALGORITHMINPLACE is not supported当前 DDL 不能在线执行改用在线改表工具或选低峰窗口Lock wait timeout exceededDDL 期间与长事务抢锁先找出长事务并处理再重试 DDL5.2 性能层面的坑热点、页分裂、索引膨胀第一类坑是随机主键带来的页分裂。UUID 或者随机字符串做主键时插入位置在 B 树中间InnoDB 不得不拆页、搬数据同时产生大量随机 IO。表现在监控上就是写 QPS 上不去、iowait 高、表文件比数据本身大不少碎片多。解法就是换成递增主键或者至少换成趋势递增的方案。第二类坑是二级索引膨胀。二级索引的叶子存的是主键值主键从 INT 换成 BIGINT、或者换成 CHAR(36)每个二级索引的每个条目都要变大。我算过一笔账一张一千万行的表上有 5 个二级索引主键从 4 字节换成 36 字节的字符串索引部分多占的空间轻轻松松超过 1.5 GB缓存命中率跟着往下掉。所以你看到主键类型这四个字要想到的不只是主键自己。第三类坑是把主键当成会变的业务字段。主键值一旦更新InnoDB 的行为是删除旧行加插入新行等于一次主键变更触发一次行搬家加所有二级索引更新代价极高。所以主键要选永不改变的列业务上会变的字段用户名、手机号、部门归属只适合放唯一索引。第四类坑是组合主键过宽。组合主键里塞三四个列甚至带上时间戳和长字符串聚簇索引的每一条都变胖页能存的行数变少同样的数据占用更多页范围扫描时要读更多页。经验值组合主键总宽度尽量控制在 16 字节以内超过就该重新想想是不是把一部分列挪到唯一索引里。5.3 被人问过最多的几个问题一张表能有几个主键一个。想要多列唯一就别分开写两个主键写成一个组合主键。反过来说如果你已经有主键、又想保证另一组列唯一用唯一索引别动主键。主键列要不要再建索引不需要。主键本身就是聚簇索引再建一个索引是纯浪费还会拖慢写入。主键能不能为 NULL不能。写不写 NOT NULL 都会变成 NOT NULL试图插入 NULL 会报错或者被隐式转换别依赖隐式转换显式给值。有没有主键是不是无所谓反正有唯一索引不行。唯一索引可以让 InnoDB 拿它当聚簇索引但这个临时主键是数据库替你选的你没有控制权一旦表结构变动或者索引删掉聚簇方式就变了。显式主键的意义就是把这个决定权握在自己手里顺便让所有人一看表结构就知道怎么定位一行。主键对查询计划影响大吗很大。按主键等值查是最快的访问路径EXPLAIN里显示为 const优化器在生成计划阶段就能把值定下来。回表也是靠主键完成的二级索引查完拿到主键再去聚簇索引里取整行。你在排查慢查询时如果发现某条 SQL 明明走了索引却还是慢常常是因为回表次数太多而回表的效率又和主键宽度、聚簇索引的局部性直接相关。加主键之后之前的数据会不会错乱不会。加主键只是加了约束和索引行的内容不动但如果加了自增列新列的值是按物理顺序分配的和你的业务时间顺序不一定一致需要按业务排序时要用业务列别用新生成的 id 当时间序。最后再分享一个小技巧任何一次主键变更我都会在预发环境把同一份数据量导一份跑完整流程并记下耗时、磁盘增量、主从延迟峰值三个数字写成一张小卡片贴到变更单里。有这三个数字打底再决定是夜里两点动手还是申请停机窗口心里踏实得多。真正在生产上让我翻过车的从来不是语句写错而是对代价估计不足。