如何优化2600万行InnoDB表添加自增ID索引的ALTER语句？

问题背景 #

现有一张包含2600万条记录的rules表，表结构如下：

CREATE TABLE `rules` (
  `uuid` char(36) NOT NULL DEFAULT uuid(),
  `company_id` int(10) unsigned NOT NULL,
  `status_id` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `alternate_name` varchar(255) NOT NULL,
  `user_name` varchar(255) NOT NULL,
  `rule` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `advanced` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `notify_emails` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`uuid`),
  KEY `rules_company_id_foreign` (`company_id`),
  KEY `rules_status_id_foreign` (`status_id`),
  CONSTRAINT `rules_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `rules_status_id_foreign` FOREIGN KEY (`status_id`) REFERENCES `rule_statuses` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

需求是添加一个INT(10) UNSIGNED类型的自增ID列并设为索引，为现有记录生成1、2、3……的递增值以加速查询。原ALTER语句如下：

ALTER TABLE rules ADD id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD INDEX (id)

本地360万条记录执行耗时117秒，担心生产环境2600万条记录执行时超时或数据库崩溃，以下是可行的优化方案：

1. 利用InnoDB在线DDL特性（MySQL 5.6+） #

InnoDB支持多数ALTER操作在线执行，可大幅降低锁表时间。添加自增列并设为FIRST需重建整张表，但可通过参数调整和语句优化减少影响：

• 提前调大innodb_buffer_pool_size，确保有足够内存缓存表数据，减少磁盘IO开销
• 增大innodb_online_alter_log_max_size，避免在线DDL过程中因日志溢出导致操作失败
• 执行语句时指定算法和锁级别（MySQL 8.0+支持更完善，5.6/5.7部分场景兼容）：

  ALTER TABLE rules ADD id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD INDEX (id), ALGORITHM=INPLACE, LOCK=NONE;
  

  若MySQL版本不支持INPLACE算法，可尝试ALGORITHM=COPY, LOCK=SHARED，仅限制写操作，不影响读请求。

2. 分批次分步操作（无锁低风险） #

如果在线DDL仍有风险，可拆分步骤逐步完成：

• 第一步：添加普通可空INT列
  这一步无需重建整张表，执行速度极快：

  ALTER TABLE rules ADD id INT(10) UNSIGNED NULL;
  

• 第二步：分批次更新现有记录
  利用uuid范围或现有索引分批更新，每次处理1000-5000条（根据服务器性能调整），避免一次性更新锁表：

  SET @row := 0;
  UPDATE rules SET id = @row := @row + 1 WHERE uuid > '[上一批的最后uuid]' LIMIT 1000;
  

  用脚本或存储过程循环执行，直到所有记录的ID值填充完成。
• 第三步：修改列属性为自增非空
  此时InnoDB会自动将自增起始值设为现有最大ID+1，后续新记录自动递增：

  ALTER TABLE rules MODIFY id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
  

• 第四步：添加索引（可选）
  若保留uuid为主键，可添加普通索引；若允许，建议将id设为主键（自增主键的查询/插入性能远高于随机UUID主键）：

  ALTER TABLE rules ADD INDEX idx_id(id);
  -- 若替换主键：
  -- ALTER TABLE rules DROP PRIMARY KEY, ADD PRIMARY KEY(id), ADD INDEX idx_uuid(uuid);
  

3. 临时表重建方案（离线高安全） #

如果生产环境允许短时间只读，可采用临时表迁移的方式，风险最低：

• 第一步：创建新表结构
  包含自增ID列，与原表结构一致：

  CREATE TABLE rules_new (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    uuid char(36) NOT NULL DEFAULT uuid(),
    company_id int(10) unsigned NOT NULL,
    status_id int(10) unsigned NOT NULL,
    name varchar(255) NOT NULL,
    alternate_name varchar(255) NOT NULL,
    user_name varchar(255) NOT NULL,
    rule longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    advanced longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
    notify_emails text DEFAULT NULL,
    created_at timestamp NULL DEFAULT NULL,
    updated_at timestamp NULL DEFAULT NULL,
    KEY `rules_company_id_foreign` (`company_id`),
    KEY `rules_status_id_foreign` (`status_id`),
    KEY `idx_uuid` (`uuid`),
    CONSTRAINT `rules_new_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
    CONSTRAINT `rules_new_status_id_foreign` FOREIGN KEY (`status_id`) REFERENCES `rule_statuses` (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  

• 第二步：分批次迁移数据
  避免一次性插入导致锁表和IO过载：

  INSERT INTO rules_new (uuid, company_id, status_id, name, alternate_name, user_name, rule, advanced, notify_emails, created_at, updated_at)
  SELECT uuid, company_id, status_id, name, alternate_name, user_name, rule, advanced, notify_emails, created_at, updated_at
  FROM rules WHERE uuid > '[上一批的最后uuid]' LIMIT 1000;
  

  循环执行直到所有数据迁移完成。
• 第三步：切换表名（短暂锁表）
  确保无写入请求时执行，切换瞬间完成：

  RENAME TABLE rules TO rules_old, rules_new TO rules;
  

• 第四步：验证后删除旧表
  确认数据无误后清理旧表：

  DROP TABLE rules_old;
  

4. 临时调整MySQL参数提升性能 #

• 调大innodb_write_io_threads和innodb_read_io_threads，提升IO并发处理能力
• 临时设置innodb_flush_log_at_trx_commit=2（操作完成后改回1），减少日志刷盘频率，提升写入速度
• 批量操作时关闭autocommit，减少事务提交开销

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