不停机修改主键字段的SQL技巧

先说一个核心结论：直接执行 ALTER TABLE 修改主键，会锁表，而且是全表锁。MySQL 5.7 及之前版本，无论是 DROP PRIMARY KEY 还是 ADD PRIMARY KEY，InnoDB 默认走 copy-alter 流程——重建整张表、逐行复制数据、重建索引。期间写操作被完全阻塞，读操作虽然可以继续，但受 MVCC 版本链拖累，性能也会明显下降。

真正影响服务的地方不是“查询失败”，而是“写入被卡住”。所以“不停服”的关键不是彻底避免锁，而是把锁的时间压缩到秒级甚至毫秒级。

MySQL 中直接 ALTER TABLE 修改主键会锁表吗？

答案是会，全表锁。执行 ALTER TABLE ... DROP PRIMARY KEY 或 ADD PRIMARY KEY 时，MySQL（尤其是 InnoDB 在 5.7 及之前版本）默认走 copy-alter 流程：重建整张表、逐行复制数据、重建索引——期间写操作被阻塞，读操作虽可继续但可能因 MVCC 版本链拉长而变慢。

真正影响服务的是「写入阻塞」，不是「查询失败」。所以「不停服」的关键不是避免锁，而是把锁的时间压到秒级甚至毫秒级。

• MySQL 8.0+ 支持部分 ALTER TABLE 操作为 ALGORITHM=INSTANT，但修改主键（哪怕只是改字段名或类型）不支持 INSTANT
• ADD COLUMN、DROP COLUMN（非主键）、RENAME COLUMN 可以 INSTANT；但主键变更必须重建聚簇索引，无法绕过
• 如果主键是自增 INT，想改成 BIGINT，即使只改类型也需 copy-alter —— 因为存储长度和索引结构都变了

用在线 DDL 工具（如 pt-online-schema-change）绕过锁表

这是生产环境最常用、风险可控的方案。它不直接改原表，而是新建影子表、同步数据、交换表名，全程原表可读写。

前提：表必须有唯一/主键（用于增量同步定位），且不能有触发器（pt-osc 不兼容）。

• 安装 Percona Toolkit：apt install percona-toolkit 或 pip install percona-toolkit
• 基本命令示例（将 id 主键从 INT 扩展为 BIGINT）：

  pt-online-schema-change --alter "MODIFY id BIGINT UNSIGNED AUTO_INCREMENT" --execute --host=localhost --user=root --database=testdb --table=users

• 它会自动检查外键、生成影子表、挂 INSERT/UPDATE/DELETE 触发器捕获变更、分 chunk 拷贝数据、最后原子性 RENAME TABLE
• 注意：触发器会带来轻微写入延迟；大表迁移耗时取决于网络和 I/O，建议在低峰期启动

手动分步切换：适合字段类型不变、仅需调整主键列组合

如果只是把主键从单列 id 改成联合主键 (id, tenant_id)，且新字段已存在、类型兼容，可以不用工具，靠 SQL 分步完成。

• 先加唯一索引（确保新主键值不重复）：CREATE UNIQUE INDEX idx_pk_new ON users (id, tenant_id)
• 停写短窗口（秒级），删除原主键：ALTER TABLE users DROP PRIMARY KEY
• 立刻添加新主键：ALTER TABLE users ADD PRIMARY KEY (id, tenant_id)
• 恢复写入。整个锁表时间取决于 DROP PK 和 ADD PK 的执行速度，通常远小于全表重建
• 风险点：中间状态无主键，若应用层依赖主键做乐观锁或分页，可能出错；务必确认业务代码不假设「有且仅有一个主键字段」

PostgreSQL 怎么办？它支持更灵活的主键变更

PostgreSQL 的 ALTER TABLE ... DROP CONSTRAINT 和 ADD CONSTRAINT 默认不锁表（仅需短时间排他锁），因为它的索引构建可并发进行。

• 删旧主键约束：ALTER TABLE users DROP CONSTRAINT users_pkey
• 建新主键索引（可并行）：CREATE UNIQUE INDEX CONCURRENTLY users_new_pkey ON users (id, tenant_id)
• 加新主键约束：ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY USING INDEX users_new_pkey
• 注意：CONCURRENTLY 创建索引期间允许写入，但不能与事务块嵌套，且失败后需手动清理无效索引
• 对比 MySQL，PG 这套流程天然更适合「不停服」，但前提是别在事务里执行，且要预留足够磁盘空间存临时索引

实际操作中，最容易被忽略的是外键依赖和应用缓存。比如主键字段类型变了，ORM 的 model 定义、Redis 里按主键缓存的 key、下游 CDC 解析逻辑，全得同步更新——否则服务没停，数据却对不上。