Laravel迁移：MySQL大表添加可空外键列锁表问题求助

我在执行Laravel迁移，要给包含数百万条记录的click表添加可空的state_id外键列并创建索引。迁移文件名为2024_04_29_125513_add_state_id_column_to_click_table，对应的Laravel Schema代码如下：

Schema::table('click', function (Blueprint $table) {
        $table->foreignId('state_id')->nullable()->constrained('state')->after('country_id');
    });

生成的原生SQL：

ALTER TABLE click
ADD COLUMN state_id INT UNSIGNED NULL AFTER country_id
ADD CONSTRAINT click_state_id_foreign FOREIGN KEY (state_id) REFERENCES state(id)
ADD INDEX click_state_id_index (state_id);

我尝试拆分三步操作：1. 添加可空列；2. 添加外键约束；3. 添加索引，但仅执行第一步添加列时，表还是会被长时间锁定。click表结构如下：

CREATE TABLE click (
  id bigint unsigned NOT NULL AUTO_INCREMENT,
  uuid char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  placement_id bigint unsigned NOT NULL,
  category_id bigint unsigned NOT NULL,
  vertical_id bigint unsigned NOT NULL,
  advertiser_id bigint unsigned NOT NULL,
  campaign_id bigint unsigned NOT NULL,
  lander_id bigint unsigned NOT NULL,
  publisher_id bigint unsigned NOT NULL,
  traffic_source_id bigint unsigned NOT NULL,
  traffic_type_id bigint unsigned NOT NULL,
  sub_source_id bigint unsigned DEFAULT NULL,
  platform_id bigint unsigned DEFAULT NULL,
  operating_system_id bigint unsigned DEFAULT NULL,
  country_id bigint unsigned DEFAULT NULL,
  external_id varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  ip varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  url text COLLATE utf8mb4_unicode_ci NOT NULL,
  click_out_url text COLLATE utf8mb4_unicode_ci,
  referer varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  bid decimal(8, 4) DEFAULT NULL,
  revenue varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
  payout varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
  position int NOT NULL DEFAULT '0',
  utm_source varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  utm_medium varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  iso_code varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  country varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  city varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  state varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  postal_code varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  lat decimal(9, 6) DEFAULT NULL,
  lon decimal(9, 6) DEFAULT NULL,
  timezone varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  device varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  device_name varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  platform varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  platform_version varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  is_robot tinyint(1) DEFAULT NULL,
  robot_name varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  browser varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  browser_version varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  ua varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  footprint varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  algo_id tinyint DEFAULT NULL,
  campaign_pool_size int DEFAULT NULL,
  status tinyint NOT NULL DEFAULT '1',
  created_at timestamp NULL DEFAULT NULL,
  updated_at timestamp NULL DEFAULT NULL,
  state_id bigint unsigned DEFAULT NULL,
  PRIMARY KEY (id),
  KEY click_uuid_created_at_updated_at_index (uuid, created_at, updated_at),
  KEY click_created_at_index (created_at),
  KEY click_updated_at_index (updated_at),
  KEY click_uuid_index (uuid),
  KEY click_placement_id_index (placement_id),
  KEY click_category_id_index (category_id),
  KEY click_vertical_id_index (vertical_id),
  KEY click_advertiser_id_index (advertiser_id),
  KEY click_campaign_id_index (campaign_id),
  KEY click_lander_id_index (lander_id),
  KEY click_publisher_id_index (publisher_id),
  KEY click_traffic_source_id_index (traffic_source_id),
  KEY click_traffic_type_id_index (traffic_type_id),
  KEY click_sub_source_id_index (sub_source_id),
  KEY click_platform_id_index (platform_id),
  KEY click_country_id_index (country_id),
  KEY click_external_id_index (external_id),
  KEY click_algo_id_index (algo_id),
  KEY click_status_index (status),
  KEY click_operating_system_id_index (operating_system_id),
  CONSTRAINT click_advertiser_id_foreign FOREIGN KEY (advertiser_id) REFERENCES client (id),
  CONSTRAINT click_campaign_id_foreign FOREIGN KEY (campaign_id) REFERENCES campaign (id) ON DELETE CASCADE,
  CONSTRAINT click_category_id_foreign FOREIGN KEY (category_id) REFERENCES category (id),
  CONSTRAINT click_country_id_foreign FOREIGN KEY (country_id) REFERENCES country (id),
  CONSTRAINT click_lander_id_foreign FOREIGN KEY (lander_id) REFERENCES lander (id) ON DELETE CASCADE,
  CONSTRAINT click_placement_id_foreign FOREIGN KEY (placement_id) REFERENCES placement (id),
  CONSTRAINT click_platform_id_foreign FOREIGN KEY (platform_id) REFERENCES platform (id),
  CONSTRAINT click_publisher_id_foreign FOREIGN KEY (publisher_id) REFERENCES client (id),
  CONSTRAINT click_sub_source_id_foreign FOREIGN KEY (sub_source_id) REFERENCES sub_source (id) ON DELETE CASCADE,
  CONSTRAINT click_traffic_source_id_foreign FOREIGN KEY (traffic_source_id) REFERENCES traffic_source (id) ON DELETE CASCADE,
  CONSTRAINT click_traffic_type_id_foreign FOREIGN KEY (traffic_type_id) REFERENCES traffic_source_type (id),
  CONSTRAINT click_vertical_id_foreign FOREIGN KEY (vertical_id) REFERENCES vertical (id)
) ENGINE = InnoDB AUTO_INCREMENT = 48845998 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci

针对InnoDB引擎的百万级数据表，要避免ALTER TABLE操作长时间锁表，可采用以下几种方案：

1. 利用MySQL 8.0+的在线DDL特性 #

MySQL 8.0及以上版本中，添加可空列、创建索引这类操作默认支持在线执行，不会长时间锁表。确认你的MySQL版本符合要求，同时确保innodb_alter_table参数设置为INSTANT或INPLACE（默认值）：

SET GLOBAL innodb_alter_table = 'INPLACE';

此版本下无需拆分步骤，直接执行原迁移代码即可完成操作。

2. 分阶段执行并控制锁等待 #

如果使用MySQL 5.7版本，可通过以下方式优化：

• 将添加列、创建外键、创建索引拆分为三个独立的迁移文件，分别执行，减少单次操作的锁持有时间。
• 执行迁移前设置较短的锁等待超时，避免长时间阻塞业务：

SET SESSION innodb_lock_wait_timeout = 10;

若操作因锁等待失败，可在业务低峰期重试。

3. 使用pt-online-schema-change工具 #

这是处理大数据表DDL操作的成熟工具，通过创建临时表、逐步同步数据、交换表名的方式实现无锁变更。操作步骤：

1. 安装Percona Toolkit
2. 执行命令完成结构变更：

pt-online-schema-change \
  --alter "ADD COLUMN state_id INT UNSIGNED NULL AFTER country_id, ADD CONSTRAINT click_state_id_foreign FOREIGN KEY (state_id) REFERENCES state(id), ADD INDEX click_state_id_index (state_id)" \
  D=你的数据库名,t=click \
  --execute

该工具会自动同步数据，不影响原表的读写操作。

4. Laravel中手动指定在线DDL参数 #

在Laravel迁移中直接执行原生SQL，指定ALGORITHM和LOCK参数实现无锁/低锁操作：

Schema::table('click', function (Blueprint $table) {
    $table->rawQuery("ALTER TABLE click 
        ADD COLUMN state_id INT UNSIGNED NULL AFTER country_id,
        ADD CONSTRAINT click_state_id_foreign FOREIGN KEY (state_id) REFERENCES state(id),
        ADD INDEX click_state_id_index (state_id)
        ALGORITHM=INPLACE, LOCK=NONE;");
});

ALGORITHM=INPLACE表示使用原地算法，LOCK=NONE表示不锁表，需根据MySQL版本确认是否支持该组合。

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