MySQL 亿级别表结构变更优化:如何安全添加字段而不阻塞业务 & gh-ost安装使 ...

前言
在 MySQL 数据库运维和开发过程中，表结构变更是常见的操作，尤其是 新增字段（ADD COLUMN）。然而，假如操作不当，可能会导致 锁表、阻塞业务读写，以致引发线上故障。
本文将从 MySQL 差别版本的 DDL 行为、Online DDL 机制、锁策略优化等方面，深入探讨如何安全高效地执行 ALTER TABLE 操作，确保业务不受影响。

一、MySQL 表结构变更的挑战

1.1 为什么 ALTER TABLE 可能阻塞业务？

在 MySQL 中，修改表结构（DDL）通常涉及元数据变更或表数据重建。若操作方式不当，可能会导致：
锁表（LOCK=EXCLUSIVE），阻塞所有读写（SELECT/INSERT/UPDATE/DELETE）。
长时间执行，特别是大表（百万/千万级数据）。
连接池耗尽，导致应用报错（如 Too many connections）。
1.2 典型案例：新增字段导致业务卡顿

-- 假设执行以下 DDL（MySQL 5.6）

1. ALTER TABLE `base_user` ADD COLUMN `vip_level` INT NULL DEFAULT 0;

复制代码

• MySQL 5.6：直接锁表，阻塞所有读写，直到 ALTER 完成。
  
• MySQL 8.0：默认 ALGORITHM=INPLACE，仅短暂阻塞，业务影响较小。
  

二、MySQL Online DDL 机制

2.1、 MySQL 5.6 vs 5.7 vs 8.0 的 DDL 行为

MySQL 版本	Online DDL 支持	默认 ALGORITHM	锁级别	影响
5.6 及更早	❌ 不支持	COPY（重建表）	EXCLUSIVE	锁表，阻塞读写
5.7	✅ 部分支持	INPLACE（尽量原地修改）	通常 NONE/SHARED	短暂阻塞
8	✅ 完整支持	INPLACE	通常 NONE	几乎无阻塞

2.2、差别 ALTER 操作的锁行为

操作类型	MySQL 5.6	MySQL 5.7+ (InnoDB)
添加 NULL 列	锁表	不锁表（INPLACE）
添加 NOT NULL 列（无默认值）	锁表	锁表（需重建数据）
添加 NOT NULL DEFAULT x 列	锁表	可能短暂阻塞
修改列类型（INT → BIGINT）	锁表	锁表（COPY 方式）

三、 如何安全执行 ADD COLUMN

3.1、使用 ALGORITHM=INPLACE 和 LOCK=NONE

1. ALTER TABLE base_user
2. ADD COLUMN col_test1 varchar(10),
3. add column col_test2 int not null default 0 comment 'test',
4. ALGORITHM=INPLACE,
5. LOCK=NONE;

复制代码

• ALGORITHM=INPLACE：尽量不重建表，仅修改元数据。
  
• LOCK=NONE：答应并发读写，制止阻塞业务
  

3.2 分批操作（实用于超大表）

假如表数据量极大（亿级），可以：

• 先加 NULL 列（不阻塞）。
  
• 再分批 UPDATE 默认值（制止长事件）
  

-- 步骤1：快速加列（不阻塞）
ALTER TABLE `user` ADD COLUMN ` col_test1` INT NULL;
-- 步骤2：分批更新默认值（制止锁全表）
UPDATE `user` SET ` col_test1` = 0 WHERE `id` BETWEEN 1 AND 100000;
UPDATE `user` SET ` col_test1` = 0 WHERE `id` BETWEEN 100001 AND 200000;
3.3 使用 Online Schema Change 工具

• gh-ost（GitHub 开源的零阻塞工具）
  

四、gh-ost的安装使用

前置条件：gh-ost目前须要MySQL版本为5.7及更高版本。
4.1、下载

https://github.com/github/gh-ost/releases

wget https://github.com/github/gh-ost/releases/download/v1.1.7/gh-ost-1.1.7-1.x86_64.rpm

4.2、安装并验证

使用 rpm 命令进行安装
rpm -ivh gh-ost-1.1.7-1.x86_64.rpm

# 验证是否安装乐成
gh-ost --version

gh-ost须要具有以下权限的帐户：
在迁徙表地点的数据库（模式）上具有ALTER、CREATE、DELETE、DROP、INDEX、INSERT、LOCK TABLES、SELECT、TRIGGER、UPDATE权限，或者当然也可以在*.*上具有这些权限。
要么：
在*.*上具有SUPER、REPLICATION SLAVE权限，或者：
在*.*上具有REPLICATION CLIENT、REPLICATION SLAVE权限。
4.3、关键指标检查

1. -- 查看表大小（GB）
2. SELECT  table_name, ROUND(data_length/1024/1024,2) AS size_mb
3. FROM information_schema.tables
4. WHERE table_schema = 'testdb' AND table_name = 'base_user';
6. -- 检查当前长事务
7. SELECT * FROM information_schema.innodb_trx
8. WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;
10. -- 查看 DDL 状态
11. SHOW PROCESSLIST;

复制代码

4.4、创建操作用户

在主数据库上（须要修改的数据库）

1. -- 创建用户
2. create user ghuser@'%' identified by '1203';
4. -- 为用户设置可执行gh-ost的权限（SUPER）
5. grant super on *.* to ghuser@'%';
6. grant all on *.* to ghuser@'%';
8. -- 刷新权限立即生效
9. flush privileges;

复制代码

4.5、执行命令（无需触发器）

# 空跑测试（不实际执行：去掉--execute参数）

1. gh-ost \
2. -host=192.168.1.118 \
3. -user="ghuser" \
4. -password="1203" \
5. -database="testdb" \
6. -table="base_user" \
7. -alter="ADD COLUMN gh_test1 varchar(10),add column gh_test2 int not null default 0 comment 'test' " \
8. -allow-on-master

复制代码

实例：

1. gh-ost \
2. -host=192.168.1.118 \
3. -user="ghuser" \
4. -password="1203" \
5. -database="testdb" \
6. -table="base_user" \
7. -alter="ADD COLUMN gh_test1 varchar(10),add column gh_test2 int not null default 0 comment 'test' " \
8. -allow-on-master \-execute

复制代码

执行结果

参数阐明：

• --host=192.168.1.118: 指定目标MySQL服务器地址
  
• --user/--password: 数据库账号（需有足够权限）
  
• --database/--table: 指定要操作的目标库表
  
• --alter: DDL语句（根据需求调解字段类型）
  
• --allow-on-master: 答应直接在master上操作（无slave时必需）
  
• --execute: 实际执行迁徙（测试时可先移除该参数）
  

1. gh-ost \
2. -user="ghuser" \
3. -password="1203" \
4. -host=192.168.1.118 \
5. -database="testdb" \
6. -table="base_user" \
7. -allow-on-master \
8. -max-load=Threads_running=20 \
9. -critical-load=Threads_running=100 \
10. -chunk-size=2000 \
11. -alter="engine=innodb" \
12. -alter="ADD COLUMN gh_test1 varchar(10),add column gh_test2 int not null default 0 comment 'test' " \
13. -cut-over=default \
14. -exact-rowcount \
15. -concurrent-rowcount \
16. -default-retries=120 \
17. -timestamp-old-table \
18. -assume-rbr \
19. -panic-flag-file=/tmp/ghost.panic.flag \
20. -execute

复制代码

执行结果

注意事项：

• 确保捏造机与192.168.1.118（数据库服务器）网络互通，且MySQL端口（默认3306）开放
  
• 账号需有以下权限：   
  
  
  
  • ALTER, CREATE, DELETE, DROP, INDEX, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATE
    
  • REPLICATION CLIENT, REPLICATION SLAVE, SUPER（gh-ost 要求）
    
  
  
• 建议先在测试环境验证，可通过--test-on-replica参数测试
  
• 生产环境建议使用--assume-rbr（若使用ROW复制格式）提升性能
  
• 可通过--chunk-size等参数优化执行速率
  

五、总结 & 最佳实践

5.1、结论

MySQL 5.7+ 支持 Online DDL，ADD COLUMN NULL DEFAULT x 通常不阻塞。
大表 ALTER 仍可能短暂阻塞，建议使用 gh-ost。
Java 应用层可优化：监控长事件、动态切从库、分批更新。
5.2、首选方案：

• MySQL 8.0 → 原生 ALGORITHM=INSTANT（秒级完成）
  
• MySQL 5.7 → gh-ost（无触发器影响）
  

5.3 推荐操作流程

• 检查 MySQL 版本（SELECT VERSION();）。
  
• 评估表大小（SELECT COUNT(*) FROM table）。
  
• 选择合适策略：
  
• 小表 → 直接 ALTER TABLE ... ALGORITHM=INPLACE。
  
• 大表 → 使用 gh-ost 或分批更新。
  
• 低峰期执行，并监控数据库线程（SHOW PROCESSLIST）。
  

参考文档：
https://zhuyh.blog.csdn.net/article/details/146591501

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。