企业官网建设流程全解析

上周接手一个别人留下的库，打开一张三百多万行的成绩表，建表语句干干净净，一个主键都没有，连个唯一索引都找不到。当时想给某几行打个标记，改了半天下不去手——因为没有稳定唯一的东西来定位"这一行"。在 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，写了也是浪费。反过来，加普通索引不会让列变成主键。下面这张表把三种常见"键"的区别摊开对比一下：

提示：唯一索引允许多个 NULL，是因为 SQL 标准里 NULL 不等于 NULL，所以多个 NULL 之间不算"重复"。这个特性经常被用来做"软删除"唯一约束：用 deleted_at 为 NULL 表示未删除，未删除的行只允许一条。

2. 建表阶段就把主键定好：几种写法和选型逻辑

2.1 自增整数主键：默认答案，但类型得算清楚

绝大多数业务表，我给的建议都是同一个：id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY。理由有三层。顺序写入能让 InnoDB 不断往 B+ 树最右侧追加页，几乎没有页分裂的随机 IO；8 字节的整型足够窄，二级索引里存主键值不会把索引撑大；自增由数据库负责，应用层不用操心并发冲突。

类型选择上，INT UNSIGNED 上限大约 42.9 亿，BIGINT UNSIGNED 是 1.8 乘 10 的 19 次方，实际等于用不完。很多人觉得"42 亿够了吧"，我给个测算你就知道该不该赌：

日志表、埋点表、流水表这类写多读少的表，日增千万行不稀奇，用 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 有重复值或空值：先去重，再补约束

数据有重复的话，加主键必然报 1062，Duplicate 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), ALGORITHM=INPLACE, LOCK=NONE;

如果返回"ALGORITHM=INPLACE 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_CHECKS=0临时关闭校验（这个操作风险高，只建议在明确知道后果、且能在同一事务窗口内恢复时使用）。

列改名的情况现在好办多了。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_mode=2下多个并发插入交错取值。这三种情况都正常，自增主键只保证唯一和递增趋势，不保证连续。任何依赖"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 报错速查表

下面这张表是我这些年攒下来的，遇到报错先来这儿对一眼，能省不少搜索时间：

5.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 当时间序。

最后再分享一个小技巧：任何一次主键变更，我都会在预发环境把同一份数据量导一份，跑完整流程并记下耗时、磁盘增量、主从延迟峰值三个数字，写成一张小卡片贴到变更单里。有这三个数字打底，再决定是夜里两点动手还是申请停机窗口，心里踏实得多。真正在生产上让我翻过车的，从来不是语句写错，而是对代价估计不足。