MySQL为1300万行表添加外键耗时超2小时，寻求技术帮助

针对1300万行的tactic_themes表关联300万行tactics表添加外键耗时超2小时的问题，可按以下步骤排查优化：

1. 确保父表关联列存在索引 #

添加外键时，MySQL会校验子表所有关联值是否存在于父表，若父表tactics的code_tac列无主键或唯一索引，会触发全表扫描，导致校验耗时爆炸。

• 检查索引：

  SHOW INDEX FROM tactics;
  

• 若code_tac无主键/唯一索引，先添加：

  ALTER TABLE tactics ADD UNIQUE INDEX idx_code_tac (code_tac);
  

2. 调大InnoDB缓冲池 #

当前innodb_buffer_pool_size仅16M，远不足以缓存两张大表的数据，导致大量磁盘IO，直接拖慢操作。

• 临时在线调整（需MySQL 5.7+，值为innodb_buffer_pool_chunk_size的整数倍，当前chunk为16M）：

  -- 示例设为4G，根据服务器内存调整，建议设为物理内存的50%-70%
  SET GLOBAL innodb_buffer_pool_size = 4294967296;
  

• 永久生效需修改my.cnf/my.ini：

  innodb_buffer_pool_size = 4G
  

3. 提前给子表关联列加索引 #

若子表tactic_themes的code_tac列无索引，添加外键时MySQL会自动创建索引，但提前手动创建可更可控，且缓冲池充足时速度更快：

• 检查索引：

  SHOW INDEX FROM tactic_themes;
  

• 无索引则添加：

  ALTER TABLE tactic_themes ADD INDEX idx_code_tac (code_tac);
  

4. 提前清理无效数据 #

若子表存在code_tac不在父表中的数据，添加外键会失败，且校验过程会额外耗时：

• 排查无效数据：

  SELECT COUNT(*) FROM tactic_themes tt 
  LEFT JOIN tactics t ON tt.code_tac = t.code_tac 
  WHERE t.code_tac IS NULL;
  

• 清理无效数据（根据业务需求处理，示例删除）：

  DELETE tt FROM tactic_themes tt 
  LEFT JOIN tactics t ON tt.code_tac = t.code_tac 
  WHERE t.code_tac IS NULL;
  

5. 低峰期执行操作 #

添加外键过程中会锁表（即使Online DDL也存在一致性校验的锁阶段），避开业务高峰执行，避免其他读写操作干扰拖慢进度。

6. 辅助参数调整 #

• 调大排序缓冲：

  SET GLOBAL innodb_sort_buffer_size = 8388608; -- 8M
  

• 若服务器日志文件过小，可调整innodb_log_file_size（需重启MySQL，建议设为512M-1G）：

  innodb_log_file_size = 1G
  

内容的提问来源于stack exchange，提问作者Joee4