生产环境下修改主键并实现MySQL分区的最优停机方案

问题背景 #

在MySQL 8.0服务器上对超1000万条数据的data表做分区，由于分区规则强制要求分区键created_at必须包含在主键中，需要修改原表主键（原主键为自增字段seq_id），将created_at纳入主键范围。直接执行ALTER语句会锁表，本地同量级数据测试耗时超2小时，目前可安排停机窗口，寻求最优上线方案。

原表结构 #

CREATE TABLE `data` (
  `seq_id` int NOT NULL AUTO_INCREMENT,
  `id` varchar(100) NOT NULL,
  `doc_id` varchar(100) NOT NULL,
  `page_no` int DEFAULT '1',
  `file_store` varchar(100) DEFAULT NULL,
  `s_id` varchar(100) NOT NULL,
  `s_f_id` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `f_store` varchar(100) DEFAULT 'GCP',
  `purged` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`seq_id`),
  UNIQUE KEY `id` (`id`),
  KEY `doc_id` (`doc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

目标分区规则 #

ALTER TABLE `data`
PARTITION BY RANGE (UNIX_TIMESTAMP(created_at))
(
  PARTITION `partition_2020` VALUES LESS THAN (UNIX_TIMESTAMP('2020-01-01 00:00:00')),
  PARTITION `partition_2021` VALUES LESS THAN (UNIX_TIMESTAMP('2021-01-01 00:00:00')), 
  PARTITION `partition_2022` VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-01 00:00:00')), 
  PARTITION `partition_2023_05` VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-31 00:00:00')),
  PARTITION `partition_future` VALUES LESS THAN (MAXVALUE)
);

（注：原分区语句存在重复的partition_2022名称，已修正为partition_2023_05）

最优上线方案（基于停机窗口） #

方案一：新建分区表+预迁移+切换（最快最稳妥） #

直接修改原表主键并分区耗时过长，推荐采用分阶段迁移的方式：

1. 提前创建目标分区表
   在生产环境提前建好符合要求的新表，包含调整后的主键和分区规则：

CREATE TABLE `data_new` (
  `seq_id` int NOT NULL AUTO_INCREMENT,
  `id` varchar(100) NOT NULL,
  `doc_id` varchar(100) NOT NULL,
  `page_no` int DEFAULT '1',
  `file_store` varchar(100) DEFAULT NULL,
  `s_id` varchar(100) NOT NULL,
  `s_f_id` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `f_store` varchar(100) DEFAULT 'GCP',
  `purged` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`, `created_at`, `seq_id`), -- 主键包含分区键created_at，同时保留seq_id确保唯一性
  UNIQUE KEY `seq_id` (`seq_id`), -- 保留原自增字段的唯一约束
  UNIQUE KEY `id` (`id`),
  KEY `doc_id` (`doc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (UNIX_TIMESTAMP(created_at))
(
  PARTITION `partition_2020` VALUES LESS THAN (UNIX_TIMESTAMP('2020-01-01 00:00:00')),
  PARTITION `partition_2021` VALUES LESS THAN (UNIX_TIMESTAMP('2021-01-01 00:00:00')), 
  PARTITION `partition_2022` VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-01 00:00:00')), 
  PARTITION `partition_2023_05` VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-31 00:00:00')),
  PARTITION `partition_future` VALUES LESS THAN (MAXVALUE)
);

2. 预迁移历史数据（非停机时段）
   在停机窗口到来前，先迁移大部分历史数据，此时业务仍可正常访问原表：

-- 按日期范围分批次迁移，避免单次操作影响性能
INSERT INTO data_new SELECT * FROM data WHERE created_at < '2023-05-01 00:00:00';

如果数据量过大，可以按月份拆分多次执行，每次迁移一个月的数据。

3. 停机窗口内同步增量+切换表名

• 先阻断业务对原表的写入操作；
• 同步剩余的增量数据：

INSERT INTO data_new SELECT * FROM data WHERE created_at >= '2023-05-01 00:00:00';

• 快速切换表名：

RENAME TABLE data TO data_old, data_new TO data;

4. 验证与清理
   检查新表的数据量、索引、分区是否正常，确认业务可正常访问后，再根据需求删除旧表data_old。

方案二：优化ALTER语句执行效率（适合停机窗口充足场景） #

如果坚持直接修改原表，可以通过以下参数优化执行速度，减少耗时：

-- 临时关闭外键和唯一键检查，仅在停机时操作
SET foreign_key_checks = 0;
SET unique_checks = 0;
-- 合并主键修改和分区操作，避免多次ALTER
ALTER TABLE data 
DROP PRIMARY KEY, 
ADD PRIMARY KEY (`id`, `created_at`, `seq_id`),
PARTITION BY RANGE (UNIX_TIMESTAMP(created_at))
(
  PARTITION `partition_2020` VALUES LESS THAN (UNIX_TIMESTAMP('2020-01-01 00:00:00')),
  PARTITION `partition_2021` VALUES LESS THAN (UNIX_TIMESTAMP('2021-01-01 00:00:00')), 
  PARTITION `partition_2022` VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-01 00:00:00')), 
  PARTITION `partition_2023_05` VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-31 00:00:00')),
  PARTITION `partition_future` VALUES LESS THAN (MAXVALUE)
);
SET foreign_key_checks = 1;
SET unique_checks = 1;

额外优化建议：

• 将innodb_buffer_pool_size调整为服务器内存的70%左右，减少磁盘IO；
• 增大innodb_log_file_size至2G（需重启MySQL生效，提前规划），减少日志刷新频率。

关键注意事项 #

• 操作前必须对原表做全量备份，防止数据丢失；
• 若原表存在created_at = '0000-00-00 00:00:00'的数据，需提前修正，否则UNIX_TIMESTAMP(created_at)会返回NULL，导致分区失败；
• 预迁移数据时，可临时将innodb_autoinc_lock_mode设置为2（MySQL8.0默认是1），提升批量插入的速度，操作完成后改回默认值。

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