MySQL 外键支撑索引迁移：列序、重复索引与存量核验

迁移脚本把业务查询用到的索引都复制过去，外键却建不起来。另一次清理“重复索引”，DROP INDEX 又被数据库拒绝。外键需要的访问路径也属于表结构的一部分，不能只从慢查询或 ORM 注解里收集索引。

在 MySQL 8.4 的 InnoDB 中，子表需要以外键列为前导列的索引，父表也需要符合被引用键要求的结构。列名相同还不够，组合次序、类型和唯一性都要对应。这个实验用租户内订单与明细，把创建、清理和迁移核验连起来。

先把租户隔离写进引用关系

全部操作只在独立实验库进行。父表既有全局主键，也显式定义租户与订单的唯一组合，避免依赖非标准的非唯一引用键。

CREATE DATABASE fk_index_lab;
USE fk_index_lab;

CREATE TABLE purchase_order (
    id BIGINT NOT NULL,
    tenant_id INT NOT NULL,
    order_state VARCHAR(20) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_tenant_order (tenant_id, id),
    KEY idx_tenant_state (tenant_id, order_state)
) ENGINE=InnoDB;

CREATE TABLE order_line (
    id BIGINT NOT NULL,
    tenant_id INT NOT NULL,
    order_id BIGINT NOT NULL,
    sku VARCHAR(30) NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (id),
    KEY idx_tenant_order (tenant_id, order_id),
    CONSTRAINT fk_line_order
        FOREIGN KEY (tenant_id, order_id)
        REFERENCES purchase_order (tenant_id, id)
        ON DELETE RESTRICT
) ENGINE=InnoDB;

INSERT INTO purchase_order VALUES
    (1001, 7, 'open'), (1002, 8, 'open');
INSERT INTO order_line VALUES
    (1, 7, 1001, 'A', 2), (2, 8, 1002, 'B', 1);

为什么明细里已经有 order_id，还要引用租户组合？因为只引用全局订单编号，只能证明订单存在，不能证明明细声明的租户与订单所属租户一致。这里的额外唯一键有明确的完整性职责，不是看到前缀重叠就该删除。

验证一条跨租户错误写入：

-- 预期失败：订单1002属于租户8，不属于租户7。
INSERT INTO order_line VALUES (3, 7, 1002, 'C', 1);

失败后核对表里没有 id=3。这比只验证“完全不存在的订单不能插入”更接近多租户业务真正需要的保护。

支撑外键的索引不能直接删

-- 预期失败：这是当前支撑外键检查的子表索引。
ALTER TABLE order_line DROP INDEX idx_tenant_order;

外键检查需要快速找到引用记录。删除父记录时，数据库也需要判断子表是否仍有对应行；没有合适入口，既影响检查成本，也影响约束实现。

若想让同一索引兼顾常见的租户订单 SKU 查询，可以先增加一个前缀兼容的候选：

CREATE INDEX idx_tenant_order_sku
    ON order_line (tenant_id, order_id, sku);

SHOW INDEX FROM order_line;
ALTER TABLE order_line DROP INDEX idx_tenant_order;
SHOW CREATE TABLE order_line;

新索引的前两列仍是 (tenant_id, order_id)，因此具备支撑这条引用关系的列序条件。若把新索引写成 (sku, tenant_id, order_id)，即使三列一个不少，也不具有相同前缀，不能用来替代。

删除前保留旧索引、建立新索引、核验约束仍在、最后清理，是一个能解释的过程。不要在同一次盲目清理里先去掉外键，再删除索引，最后因为历史脏数据而无法恢复约束。

还要留意自动创建的索引。建外键时没有合适子表索引，InnoDB 可以自动创建；以后出现其他可支持的索引，自动生成的那个还可能被隐式清理。索引名或索引数量变化，不必然说明迁移漏执行，应以当前实际定义和外键关系为准。

CREATE TABLE LIKE 不等于复制了所有约束

CREATE TABLE order_line_copy LIKE order_line;
SHOW CREATE TABLE order_line_copy;

这里需要直接查看输出：MySQL 8.4 的 LIKE 可以复制列与索引等结构，但不复制外键定义。迁移工具若只比较字段、主键和普通索引，就可能把一张没有引用保护的表判成“结构一致”。

在复制表上尝试同一条跨租户写入，检查它是否被拒绝；不要让这个演示与原表混淆。随后清掉实验脏记录，再显式添加新外键名称：

INSERT INTO order_line_copy VALUES (3, 7, 1002, 'C', 1);
DELETE FROM order_line_copy WHERE id = 3;

ALTER TABLE order_line_copy
ADD CONSTRAINT fk_line_copy_order
FOREIGN KEY (tenant_id, order_id)
REFERENCES purchase_order (tenant_id, id)
ON DELETE RESTRICT;

增加约束失败时，先查历史数据和字段兼容性。整数类型的有无符号、字符列的字符集与排序规则、组合列次序，都是需要逐项对应的条件；不能只对照两边字段名称。

MySQL 8.4 对非标准被引用键的兼容限制也应在升级前明确。最容易长期维护的引用结构，是父表提供明确的主键或适用唯一键，子表引用同样的列组合，而不是依赖旧版本容忍的模糊结构。

关闭检查后再开启，不会替你补做全表核验

批量导入常见的陷阱，是先关闭外键检查，再认为重新开启就代表所有记录都已合法。可以在复制表上隔离验证：

SET SESSION foreign_key_checks = 0;
INSERT INTO order_line_copy VALUES (4, 7, 9999, 'orphan', 1);
SET SESSION foreign_key_checks = 1;

SELECT c.id, c.tenant_id, c.order_id
FROM order_line_copy AS c
LEFT JOIN purchase_order AS p
       ON p.tenant_id = c.tenant_id AND p.id = c.order_id
WHERE p.id IS NULL;

重新开启并不自动扫描、拒绝此前已经插入的全部孤儿行。这个查询应找到实验创建的那一行。开启后新写入受检查，与历史存量已经通过验证，是两份不同证据。

正式迁移若为了性能或装载顺序临时关闭检查，需要独立的引用完整性核验，并明确失败时不切流。只统计总行数不够：父表少了某个租户订单、子表多了另一条无主明细，总数可能仍然恰好相等。

清理演示数据后，再验证后续非法写入确实被拒绝。不要把关闭检查的会话归还连接池；会话状态泄漏会使下一段毫不相关的业务写入也绕过约束。

核验应从数据库现状出发

SELECT CONSTRAINT_NAME, COLUMN_NAME,
       REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME,
       ORDINAL_POSITION
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'fk_index_lab'
  AND TABLE_NAME IN ('order_line', 'order_line_copy')
  AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME, CONSTRAINT_NAME, ORDINAL_POSITION;

这份结果核对的是实际外键列序，不是应用模型里希望存在的关系。再结合 SHOW CREATE TABLE 和 SHOW INDEX，才能同时看到引用定义、支撑索引和实际列属性。

索引清理还应验证读路径。一个更宽的联合索引虽然能支撑外键，不代表它对所有查询都比旧索引更便宜；额外 SKU 字段会增加宽度，可能改变页数量与缓存占用。约束兼容和查询性能要分别通过，不要拿其中一项代替另一项。

反过来，数据库允许删除某棵索引，也只能证明当前约束仍有其他合适支撑，不能证明业务查询不再依赖它。迁移验收至少保留引用关系检查、非法写入测试和代表性查询计划三类证据。

同名字段还可能藏着类型差异

迁移时可以查询实际列定义，而不是只比较建表文件中的简写：

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE,
       IS_NULLABLE, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'fk_index_lab'
  AND TABLE_NAME IN ('purchase_order', 'order_line')
  AND COLUMN_NAME IN ('tenant_id', 'id', 'order_id')
ORDER BY TABLE_NAME, ORDINAL_POSITION;

INT 与 INT UNSIGNED 在应用语言里可能都映射为数字，但外键类型兼容不能忽略符号属性。字符串标识则需要核对字符集和排序规则。把导出文件里的默认值统一替换，可能意外改变这些属性，导致恢复阶段约束创建失败。

可空性也要有业务依据。本例复合外键的两列都声明非空，避免用缺失租户或缺失订单绕开关系。若迁移工具把其中一列改成可空，应重新检查允许的空组合，而不是只看约束名称仍然存在。完整性规则真正保护的是可接受的数据集合，不是一条表面相同的定义名称。

把迁移失败留在切流之前

导入顺序最好与依赖关系一致：先准备父对象，再装载子对象，明确何时创建和验证约束。如果为了缩短导入时间延后创建外键，应把它列为切流前的必经阶段，而不是可以日后补齐的优化项。

假设历史核验发现孤儿明细，补一个虚假的父订单虽然能让约束创建通过，却改变了业务事实。正确处理可能是补回确实漏导的父对象、隔离无效明细，或按业务批准撤销它们。处理记录应保留原对象标识和依据，便于之后核对。

核验期间仍有写入时，还需要控制数据继续变化的窗口。先做一次静态检查，再让旧写入入口继续产生不一致记录，最后才加约束，依然可能失败。应用规则先兼容、数据再修复、约束最后落地，是可以解释的顺序；真正切换以前要再次确认实际约束已成功创建。

外键创建与索引替换也可能等待元数据锁。迁移脚本应设置明确等待预算并记录执行结果，不要在连接超时后直接宣布完成。重试前先查现有定义，避免同一操作已经成功却再次提交，产生同名约束或索引冲突。

实验完成后先确认没有会话遗留 foreign_key_checks=0，再删除独立库：

DROP DATABASE fk_index_lab;