MySQL / 系统课时

变更主键与唯一约束

变更主键与唯一约束

本课解决主键与唯一约束变更中的两个核心风险:目标键是否真正满足唯一性要求,以及 InnoDB 重建聚簇索引带来的线上影响。最终形成一套可验证、可回退的变更方式。

学习目标

  • 解释主键变化为何会影响聚簇索引和二级索引
  • 在添加主键或唯一约束前验证 NULL 与重复数据
  • 使用一次性 DDL 和变更后检查降低线上风险

主键变化为什么代价特别高

InnoDB 的数据行存放在聚簇索引叶子页中,通常按主键组织;普通二级索引不保存独立的行地址,而是保存二级索引键及对应主键值。因此主键一旦变化,聚簇结构需要重建,二级索引中的主键值也会随之变化。

主键变更对聚簇索引与二级索引的连锁影响

这也是为什么“删除旧主键再添加新主键”不应拆成两个独立上线步骤。InnoDB 在没有显式主键时,会优先寻找合适的非空唯一索引作为聚簇键;如果没有,则生成内部行 ID。中间态会增加额外重建和验证成本。

下面的演示可在测试库直接运行。external_ref 故意保留两个 NULL,用来验证唯一索引的 NULL 语义。

sql
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL,
  tenant_id BIGINT UNSIGNED NOT NULL,
  order_no BIGINT UNSIGNED NOT NULL,
  external_ref VARCHAR(32) NULL,
  status VARCHAR(16) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_status (status)
) ENGINE=InnoDB;

INSERT INTO orders
  (id, tenant_id, order_no, external_ref, status)
VALUES
  (1001, 1, 5001, 'EXT-A', 'PAID'),
  (1002, 1, 5002, NULL, 'NEW'),
  (1003, 2, 5001, NULL, 'PAID');

预期结果:

text
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 3 rows affected

先证明目标键真的唯一

目标主键是 (tenant_id, order_no)。执行 DDL 前先检查目标列是否含 NULL、是否存在重复组合,同时确认没有外键、ORM 映射或业务代码仍把旧主键定义当作不可变契约。

sql
SELECT COUNT(*) AS null_key_rows
FROM orders
WHERE tenant_id IS NULL OR order_no IS NULL;

SELECT tenant_id, order_no, COUNT(*) AS duplicate_count
FROM orders
GROUP BY tenant_id, order_no
HAVING COUNT(*) > 1
LIMIT 10;

预期结果:

text
null_key_rows
0

Empty set

只要第一条不是 0,或第二条返回任意一行,就不能直接建立目标主键。主键列必须非空,不能把“ALTER 时再处理 NULL”作为上线策略。

普通唯一约束也需要重复检查,但 MySQL 唯一索引允许多行包含 NULL。如果业务要求“字段必须存在且全局唯一”,必须同时使用 NOT NULL 和 UNIQUE,不能只加唯一索引。

把主键替换合并成一次 DDL

主键切换应尽量在同一条 ALTER TABLE 中删除旧主键并添加新主键,避免表经历“无显式主键”的中间状态。若旧 id 仍被应用当作全局唯一标识,可以同时把它保留为唯一索引。

sql
ALTER TABLE orders
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (tenant_id, order_no),
  ADD UNIQUE KEY uk_orders_id (id),
  ALGORITHM=INPLACE,
  LOCK=NONE;

预期结果:

text
Query OK, 0 rows affected
Records: <VALUE>  Duplicates: 0  Warnings: 0

<VALUE> 与版本、表规模和执行路径有关。即使使用 ALGORITHM=INPLACE,主键变化仍属于需要重建聚簇结构的重操作;“INPLACE”不等于“不重建表数据”。显式写出 LOCK=NONE 的目的,是让不满足在线执行条件的环境直接拒绝,而不是在上线窗口里接受更强的锁级别。

添加唯一约束也要先处理业务语义

对 external_ref 增加唯一约束前,先排除非空值中的重复项。示例中的两个 NULL 不构成冲突,因为唯一索引允许多个 NULL。

sql
SELECT external_ref, COUNT(*) AS duplicate_count
FROM orders
WHERE external_ref IS NOT NULL
GROUP BY external_ref
HAVING COUNT(*) > 1
LIMIT 10;

ALTER TABLE orders
  ADD UNIQUE KEY uk_orders_external_ref (external_ref),
  ALGORITHM=INPLACE,
  LOCK=NONE;

预期结果:

text
Empty set

Query OK, 0 rows affected
Records: <VALUE>  Duplicates: 0  Warnings: 0

如果业务不允许 NULL,应先完成数据回填,再把列改为 NOT NULL,最后建立唯一约束。把“清数据、收紧 NULL、增加唯一索引”分阶段执行,通常比在一条复杂 DDL 中同时处理更容易验证和回退。

上线后检查索引定义与业务路径

主键或唯一约束变更后,应立即核对索引列顺序和唯一性,并回归使用旧主键或唯一键的查询、外键、ORM 映射和写入逻辑。

sql
SELECT
  INDEX_NAME,
  NON_UNIQUE,
  SEQ_IN_INDEX,
  COLUMN_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'orders'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

预期结果中至少应看到:

text
PRIMARY                  0  1  tenant_id
PRIMARY                  0  2  order_no
uk_orders_external_ref   0  1  external_ref
uk_orders_id             0  1  id

实际结果还会包含表上其他索引。确认结构正确后,再观察关键 SQL 延迟、buffer pool 命中、redo 写入和复制延迟是否回到基线。

如果表很大、业务无法承受重建期间的资源波动,或者 ALGORITHM=INPLACE, LOCK=NONE 无法满足目标窗口,就不要强行在线执行。主键变更应进入更严格的迁移方案:预先建立影子结构、增量同步、校验数据一致性,再在受控窗口切换。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

变更主键与唯一约束

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通