创建表后如何添加主键索引？

Question

**常见技术问题：** 在MySQL中，创建表后如何安全、高效地添加主键索引？若原表已存在大量数据且无主键，执行 `ALTER TABLE t ADD PRIMARY KEY (id);` 时可能遭遇“Duplicate entry”错误（因候选列含重复值或NULL），或引发长时间锁表、阻塞DML操作，影响线上服务。此外，若该列未定义为 `NOT NULL`，MySQL会隐式添加 `NOT NULL` 约束，导致语句失败；而InnoDB要求主键列必须非空且唯一。更复杂的是，当表已有唯一索引但非主键，或需变更现有主键时，直接添加会报错“Multiple primary key defined”。运维人员常误用 `DROP INDEX` 或 `MODIFY COLUMN` 操作引发元数据不一致。那么，在不停服前提下，如何验证数据合规性、选择合适时机、并借助工具（如pt-online-schema-change）实现平滑添加主键？

Answer 1

```html

一、现象层：典型报错与线上症状

• Duplicate entry 'NULL' for key 'PRIMARY' —— 主键列含 NULL 值（InnoDB 不允许）
• Duplicate entry '1001' for key 'PRIMARY' —— id 列存在重复值
• 执行阻塞超 30 分钟，SHOW PROCESSLIST 显示 Waiting for table metadata lock
• 业务写入延迟飙升，Prometheus 监控显示 innodb_row_lock_time_avg > 500ms
• 从库复制延迟跳涨，Seconds_Behind_Master 持续 ≥ 300s

二、根因层：InnoDB 存储引擎约束与 DDL 执行机制深度解析

MySQL 8.0+ 默认使用 InnoDB 引擎，其主键设计具有三重刚性约束：

约束类型	技术要求	隐式行为
非空性（NOT NULL）	主键列必须显式或隐式定义为 NOT NULL	ALTER TABLE ... ADD PRIMARY KEY 自动添加 NOT NULL，若已有 NULL 值则失败
唯一性（UNIQUE）	全表范围内值不可重复	不依赖索引类型，需全量扫描验证
聚簇性（Clustered）	主键即数据物理组织方式	ADD PRIMARY KEY 触发整表重建（copy-alter），锁表时间 ∝ 数据量 × 行宽

三、验证层：数据合规性四步校验法（生产环境实测有效）

1. 空值探测：SELECT COUNT(*) FROM t WHERE id IS NULL;
2. 重复值定位：SELECT id, COUNT(*) c FROM t GROUP BY id HAVING c > 1 LIMIT 10;
3. 候选键完备性：检查是否满足函数依赖（如 id → (name, created_at)），可用 ANALYZE TABLE t + INFORMATION_SCHEMA.STATISTICS 辅助判断
4. 索引冲突扫描：SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME='t' AND CONSTRAINT_SCHEMA=DATABASE();

四、策略层：不停服添加主键的三级演进方案

graph TD
  A[原始状态：无主键大表] --> B{数据是否合规？}
  B -->|否| C[清洗阶段：补全ID/去重/生成UUID]
  B -->|是| D[轻量方案：ALGORITHM=INSTANT MySQL 8.0.12+]
  C --> E[中量方案：pt-online-schema-change]
  D --> F[高危场景：主键变更/唯一索引升主键]
  E --> F
  F --> G[灰度验证：对比 checksum + binlog position]

五、工具层：pt-online-schema-change 实战参数详解

pt-online-schema-change \
  --host=localhost \
  --user=dba \
  --password=*** \
  --alter "ADD PRIMARY KEY (id)" \
  --database=testdb \
  --table=t \
  --chunk-size=1000 \
  --max-load="Threads_running=25" \
  --critical-load="Threads_running=50" \
  --check-interval=5 \
  --dry-run \  # 首轮务必启用
  --execute

关键参数说明：
• --chunk-size：控制每批次迁移行数，建议设为 min(1000, 0.1% of total rows)
• --max-load：当 Threads_running ≥ 25 时自动暂停，避免雪崩
• --dry-run：模拟执行并输出 SQL 计划，验证元数据兼容性

六、兜底层：失败回滚与元数据一致性保障

• pt-osc 失败时自动删除 _t_new、_t_old 临时表，但需人工校验触发器残留：SELECT * FROM information_schema.TRIGGERS WHERE EVENT_OBJECT_TABLE='t';
• 若误执行 DROP INDEX uk_id ON t 导致主键丢失，切勿直接 ALTER TABLE ... DROP PRIMARY KEY（会删聚簇索引！）→ 应先 CREATE UNIQUE INDEX pk_id ON t(id) USING BTREE; 再 ALTER TABLE t DROP PRIMARY KEY, ADD PRIMARY KEY (id);
• 元数据校验脚本（Python）：mysql -e "CHECKSUM TABLE t;" | awk '{print $2}' 对比新旧表 checksum

```