MySQL 千万级数据表结构修改实践-Setyg的博客

给几百行的小表加索引、加字段，一条 ALTER TABLE 随手就执行了；但当表涨到百万、千万行，同样的语句可能锁表几十分钟、把磁盘和 IO 打满，业务直接不可用。本文整理千万级大表做结构变更的几种方式：原生 Online DDL、影子表手工迁移、pt-osc / gh-ost 在线工具，以及每种方式各自的限制和踩坑点。

一、为什么大表不能直接改结构

没有 Online DDL 之前，绝大多数 ALTER TABLE 的流程是：

加锁 → 建一张新结构的临时表 → 逐行把数据复制过去 → 删除原表 → 重命名临时表 → 解锁

代价有两个：

锁表时间长：整个复制过程中表不可写（早期版本连读也阻塞），千万行表复制动辄几分钟到几十分钟。
不可轻易中断：老版本（5.5 及之前）里这个 ALTER 一旦开始基本只能等它跑完，中断的代价往往比等下去更大。

从 MySQL 5.6 引入 Online DDL 之后，很多操作可以做到「边改结构边读写」，但仍有一部分操作必须重建表。所以关键动作是先判断这次 DDL 属于哪一类，再选方案。

二、先判断：这次 DDL 能不能在线做

操作	5.6 / 5.7（InnoDB）	8.0
添加 / 删除二级索引	`INPLACE`，可 `LOCK=NONE`	同左
添加列	`INPLACE`（重建表，可并发 DML）	`INSTANT`，秒级完成（8.0.12+）
删除列	需 `COPY`（重建表）	`INSTANT`（8.0.29+）
修改列类型 / 字符集	需重建表	多数仍需重建
增删主键	需重建表	需重建表
修改列名、设置默认值	多为 `INPLACE`	`INSTANT` 或 `INPLACE`

这只是常见几类，具体以官方文档的 Online DDL Operations 表格为准。最省事的判断办法是直接把要求写进语句里试——不支持时 MySQL 会当场报错，而不是悄悄退化成锁表：

-- 要求不重建表、不阻塞读写；不支持会直接报错
ALTER TABLE ih_order ADD INDEX idx_consulate (consulate) ALGORITHM=INPLACE, LOCK=NONE;

-- 报错示例
-- ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: ... Try ALGORITHM=COPY.

三、方式一：原生 Online DDL（首选）

ALGORITHM 与 LOCK 选项

选项	取值	含义
`ALGORITHM`	`DEFAULT`	由 MySQL 自行选择，不满足条件时会退化为 `COPY`
	`INPLACE`	尽量原地修改，不复制整表数据
	`INSTANT`	只改元数据，秒级完成（8.0.12+）
	`COPY`	复制整表到新表，最慢，且 DDL 期间不能并发 DML
`LOCK`	`DEFAULT`	由 MySQL 自行选择锁级别
	`NONE`	不加锁，读写都不阻塞（最理想）
	`SHARED`	允许读、阻塞写
	`EXCLUSIVE`	读写都阻塞

加索引的推荐写法：

ALTER TABLE ih_order
  ADD INDEX idx_ih_order_consulate (consulate)
  ALGORITHM=INPLACE, LOCK=NONE;

8.0 下加列可以做到秒级（只改元数据，不碰数据文件）：

ALTER TABLE ih_order ADD COLUMN remark VARCHAR(255) NULL, ALGORITHM=INSTANT;

注意 8.0.29 之前的 INSTANT 限制较多——新列只能加在最后、一张表能做的 INSTANT 操作次数有限，需要时用 ALTER TABLE ... FORCE 重建一次。装的是 8.0.29+ 就可以放心用。

使用 Online DDL 时必须注意的点

DDL 期间的增量写有上限：INPLACE 期间产生的并发 DML 会记入在线日志，总量超过 innodb_online_alter_log_max_size（默认 128 MB）会导致 ALTER 失败并回滚。大表 + 高写入的场景要评估这个值。
看进度：8.0 可以查 performance_schema.events_stages_current（stage 里出现 altering table）；老版本只能靠 SHOW PROCESSLIST 和观察 IO。
主从延迟：DDL 在从库上是串行执行的，大表 DDL 很容易把延迟拉高，最好在从库先验证一遍所需时长。
仍然会吃资源：LOCK=NONE 只是不阻塞读写，建索引本身会大量读表 + 排序，占用 innodb_sort_buffer_size、临时目录和 IO。
不是所有情况都能 NONE：需要重建表的操作（改类型、增删主键）只能用 ALGORITHM=COPY，此时无法并发 DML，本质和手工迁移一样。

四、方式二：影子表手工迁移（理解原理用，慎用）

下面这套流程在笔记里很常见，思路和 Online DDL 的 COPY 方式完全一致，可以用来理解原理，也可以在没有工具、允许停服时使用。以一个千万行表 ih_order 加索引为例：

-- 1. 备份数据（可选，但强烈建议）
SELECT * FROM ih_order INTO OUTFILE '/bak/order.txt';
-- Query OK, 10001000 rows affected (1 min 30.18 sec)

-- 2. 复制原表结构
CREATE TABLE ih_order_bak LIKE ih_order;

-- 3. 在空表上先做结构修改，此时表里没数据，速度极快
ALTER TABLE ih_order_bak ADD INDEX (consulate);

-- 4. 把原表数据导入新表
INSERT INTO ih_order_bak SELECT * FROM ih_order;
-- Query OK, 10001000 rows affected (6 min 10.30 sec)

-- 5. 删除原表
DROP TABLE ih_order;

-- 6. 新表改名回原表名（RENAME 是原子操作）
RENAME TABLE ih_order_bak TO ih_order;

每一步的注意点：

第 1 步：INTO OUTFILE 是在数据库服务器上写文件，受 secure_file_priv 限制，目录还必须对 mysqld 运行用户可写；导出的也不是 SQL 脚本，恢复时只能用 LOAD DATA INFILE。真要做备份，用 mysqldump --single-transaction 或物理备份（XtraBackup）更可靠。
第 2 步：CREATE TABLE ... LIKE 只复制列定义、索引和表选项，不复制外键定义；触发器、分区等也要自行核对，别以为结构完全一致。
第 4 步：这是整个方案最大的风险——INSERT ... SELECT 不是原子的，复制期间的新增和修改不会同步到新表，切换之后这些数据就丢了。要么停写再做，要么在切换前补一次增量（记录一个位点，按主键 / 时间戳把差异同步过去）。
第 5、6 步：DROP + RENAME 之间有一个极短的不可用窗口（表不存在），切换要在低峰期做，并提前准备好回滚方案（原表改名而不是删掉，确认无误后再删更保险）。

五、方式三：在线 DDL 工具 pt-osc / gh-ost

手工迁移的坑（增量丢失、切换窗口、无法限流）正是在线工具要解决的问题。两者思路相同——建影子表、把数据分批搬过去、最后原子切换表名——区别在增量同步的实现：

对比项	pt-online-schema-change（pt-osc）	gh-ost
增量同步方式	在原表上建触发器，把 DML 写入影子表	读取 binlog，无需触发器
对原表的侵入	要建/删触发器，与已有触发器可能冲突	不改原表结构
限流与暂停	支持，按 chunk 控制节奏	支持动态暂停 / 恢复 / 切换，可指定在从库上跑
前提条件	表必须有主键或唯一索引；外键需要额外处理	表必须有主键；要求 binlog 为 ROW 格式且 `binlog_row_image=FULL`
适用场景	传统运维工具链，无 binlog 解析依赖	需要精细控制、想降低对主库影响

典型用法（先在从库 / 预发环境演练，确认耗时和触发器无冲突再上生产）：

# pt-online-schema-change：给 ih_order 的 consulate 加索引
pt-online-schema-change \
  --alter "ADD INDEX idx_consulate (consulate)" \
  D=test,t=ih_order,h=127.0.0.1,P=3306,u=root,p=*** \
  --alter-foreign-keys-method=auto \
  --max-load='Threads_running=50' \
  --critical-load='Threads_running=200' \
  --chunk-size=1000 \
  --dry-run   # 正式执行时去掉，先跑 dry-run 看是否可行

# gh-ost：同样目标，可在从库上执行
gh-ost \
  --host=127.0.0.1 --port=3306 --user=root --password=*** \
  --database=test --table=ih_order \
  --alter="ADD INDEX idx_consulate (consulate)" \
  --max-load='Threads_running=50' \
  --critical-load='Threads_running=200' \
  --allow-on-master \
  --execute   # 换成 --dry-run 只做校验

六、其他数据库的做法（顺带一提）

PostgreSQL：建索引用 CREATE INDEX CONCURRENTLY，不阻塞读写（但失败后会留下 invalid 索引需要清理）。
SQL Server：ALTER INDEX ... WITH (ONLINE = ON)，仅企业版支持。
Oracle：CREATE INDEX ... ONLINE，配合 PARALLEL / NOLOGGING 优化建索引速度。

七、操作清单

先判断类型：能 INSTANT 最好，其次 INPLACE + LOCK=NONE，只有必须重建表时才上工具或影子表。
低峰期执行，并提前估算耗时（在从库或预发用同样的数据量实测一遍）。
先备份：mysqldump --single-transaction 或物理备份，确认备份可恢复而不只是有文件。
写清回滚方案：加索引可以 DROP INDEX 回滚；改结构则要能回到原表（原表改名保留，而不是直接 DROP）。
全程监控：主从延迟、Threads_running、磁盘 IO 与剩余空间、临时目录用量，工具都支持配 --max-load / --critical-load 自动退让。
保证一致性：涉及数据搬运用事务或 --single-transaction；用工具时确认增量同步链路（触发器 / binlog）真的在工作。

八、速查卡

-- 在线加索引（首选，不阻塞读写）
ALTER TABLE t ADD INDEX idx_col (col), ALGORITHM=INPLACE, LOCK=NONE;

-- 8.0.12+ 秒级加列（8.0.29+ 支持任意位置、可多次）
ALTER TABLE t ADD COLUMN c VARCHAR(64) NULL, ALGORITHM=INSTANT;

-- 查看正在执行的 DDL 进度（8.0）
SELECT * FROM performance_schema.events_stages_current WHERE EVENT_NAME LIKE '%altering table%';

-- 关键参数：在线 DDL 期间累积的 DML 超过该值会失败回滚
SHOW VARIABLES LIKE 'innodb_online_alter_log_max_size';   -- 默认 128M

# 大表改用工具：先 dry-run，再加限流阈值
pt-online-schema-change --alter "ADD INDEX idx (col)" D=db,t=tbl,h=host,u=user,p=pass --dry-run
gh-ost --host=host --database=db --table=tbl --alter="ADD INDEX idx (col)" --dry-run

目录CONTENT

MySQL 千万级数据表结构修改实践