凌晨改表致 28 分钟锁表！千万级 MySQL 大表加字段的血泪教训

一、开篇痛点引入：为什么不能直接加字段？

1.1 真实故障案例

某电商平台订单表 t_order 存储了超过 5000 万条订单记录，业务高峰期 QPS 达到 3000+。某日，开发团队需要在订单表中新增一个 coupon_id 字段用于记录优惠券信息。运维工程师在业务低峰期（凌晨 2 点）直接执行了 ALTER TABLE t_order ADD COLUMN coupon_id BIGINT DEFAULT NULL COMMENT '优惠券ID';。

结果：

• 表锁持续 28 分钟，期间所有订单相关业务（下单、支付、查询）全部阻塞
• 支付流程超时，用户无法完成支付，导致大量订单流失
• 主从延迟飙升到 1800 秒，从库复制线程长时间处于 Waiting for table metadata lock 状态
• 最终引发生产故障，业务损失严重

1.2 直接加字段的致命问题

在千万级大表上直接执行 ALTER TABLE ADD COLUMN 会引发以下问题：

1. 长时间锁表：InnoDB 在执行 DDL 时需要对表加排他锁（X锁），阻塞所有读写操作
2. 主从延迟：主库执行 DDL 时，从库需要等待主库完成，导致复制延迟急剧上升
3. 资源消耗：表拷贝过程会消耗大量 CPU、内存和磁盘 IO，可能影响其他业务
4. 不可中断：一旦开始执行，无法暂停或回滚，只能等待完成或强制终止（风险极高）
5. 磁盘空间：需要额外空间存储临时表，空间不足会导致操作失败

二、底层原理剖析：ALTER TABLE 的执行机制

2.1 InnoDB 存储引擎的 DDL 执行流程

2.1.1 传统 DDL（MySQL 5.5 及以前）

1. 获取表级排他锁（X锁）
2. 创建临时表结构（包含新字段）
3. 逐行拷贝原表数据到临时表
4. 重命名临时表为原表名
5. 删除原表
6. 释放锁

特点： 整个过程完全锁表，无法并发读写，耗时与表数据量成正比。

2.1.2 Online DDL（MySQL 5.6+）

MySQL 5.6 引入了 Online DDL 特性，对于 ADD COLUMN 操作，支持在线执行：

1. 准备阶段：创建临时 frm 文件，获取共享元数据锁（MDL）
2. 执行阶段：
   • 修改数据字典（内存中）
   • 在表末尾追加新列（物理文件层面）
   • 生成 redo log 和 undo log
3. 提交阶段：更新数据字典，释放 MDL 锁

关键点：

• 添加列到表末尾时，不需要重建表，只需修改数据字典
• 允许并发 DML 操作（INSERT、UPDATE、DELETE、SELECT）
• 但仍需要短暂的排他锁来更新数据字典

2.2 不同 MySQL 版本的 DDL 优化差异

MySQL 5.6

• 引入 Online DDL，支持部分 DDL 操作在线执行
• ADD COLUMN 操作在表末尾添加时，支持 ALGORITHM=INPLACE, LOCK=NONE
• 但仍需要短暂的排他锁更新数据字典

MySQL 5.7

• 优化了 Online DDL 的性能，减少锁持有时间
• 支持 ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE 显式指定
• 改进 redo log 写入机制，减少 IO 压力

MySQL 8.0

• 原子 DDL：DDL 操作要么全部成功，要么全部回滚，不会出现中间状态
• 数据字典存储在 InnoDB 表中，支持事务
• 更好的并发控制，减少锁等待时间
• 支持 INSTANT 算法，某些操作（如添加可空列到表末尾）可以瞬间完成

2.3 影响加字段效率的核心因素

1. 表数据量：数据量越大，即使使用 Online DDL，也需要更多时间更新数据字典和生成日志
2. 索引数量：如果新字段需要建立索引，会触发索引重建，耗时更长
3. 行格式（ROW_FORMAT）：
   • COMPACT/REDUNDANT：需要重建表，不支持 Online DDL
   • DYNAMIC/COMPRESSED：支持 Online DDL，效率更高
4. 服务器资源：
   • IO 能力：影响 redo log 和 undo log 写入速度
   • CPU 性能：影响数据字典更新和日志生成
   • 内存大小：影响缓冲池命中率
5. 字段位置：添加到表末尾比添加到中间位置效率更高
6. 字段类型和默认值：NOT NULL 字段需要扫描全表设置默认值，耗时更长

2.4 锁机制详解

元数据锁（MDL - Metadata Lock）

-- 查看当前 MDL 锁等待情况
SELECT * FROM performance_schema.metadata_locks 
WHERE OBJECT_TYPE = 'TABLE' AND OBJECT_SCHEMA = '{db_name}';

-- 查看锁等待详情
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

MDL 锁类型：

• MDL_SHARED_READ：共享读锁，允许并发读
• MDL_SHARED_WRITE：共享写锁，允许并发写
• MDL_EXCLUSIVE：排他锁，阻塞所有操作

三、主流方案对比实战

3.1 方案一：原生 Online DDL（推荐用于低并发场景）

3.1.1 适用场景

• MySQL 5.6+ 版本
• 表数据量在千万级（5000 万以下）
• 业务并发量较低（QPS < 1000）
• 表索引数量较少（< 10 个）
• 新字段允许为 NULL 或设置默认值
• 可以在业务低峰期执行

3.1.2 操作步骤

步骤 1：检查表结构和行格式

-- 查看表结构
SHOW CREATE TABLE {db_name}.{table_name}\G

-- 查看行格式
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    ROW_FORMAT,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = '{db_name}' 
  AND TABLE_NAME = '{table_name}';

-- 如果行格式不是 DYNAMIC，需要先修改
ALTER TABLE {db_name}.{table_name} ROW_FORMAT=DYNAMIC;

步骤 2：优化 MySQL 参数

-- 临时调整 DDL 相关参数（需要重启生效，建议在配置文件中提前设置）
SET GLOBAL innodb_online_alter_log_max_size = 2147483648;  -- 2GB，允许更大的在线日志
SET GLOBAL innodb_lock_wait_timeout = 120;  -- 增加锁等待超时时间

-- 查看当前参数
SHOW VARIABLES LIKE 'innodb_online_alter_log_max_size';
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

步骤 3：执行 Online DDL

-- 方式 1：显式指定算法和锁类型（推荐）
ALTER TABLE {db_name}.{table_name} 
ADD COLUMN coupon_id BIGINT DEFAULT NULL COMMENT '优惠券ID' AFTER order_status,
ALGORITHM=INPLACE, 
LOCK=NONE;

-- 方式 2：MySQL 8.0 可以使用 INSTANT 算法（如果支持）
ALTER TABLE {db_name}.{table_name} 
ADD COLUMN coupon_id BIGINT DEFAULT NULL COMMENT '优惠券ID',
ALGORITHM=INSTANT, 
LOCK=NONE;

步骤 4：监控执行进度

-- 查看当前执行的 DDL 操作
SELECT 
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    INFO
FROM information_schema.PROCESSLIST 
WHERE COMMAND != 'Sleep' 
  AND INFO LIKE '%ALTER%';

-- 查看 InnoDB 状态（关注 DDL 进度）
SHOW ENGINE INNODB STATUS\G

-- 查看表锁等待情况
SELECT * FROM sys.schema_table_lock_waits;

3.1.3 优缺点分析

优点：

• 原生支持，无需安装第三方工具
• 操作简单，一条 SQL 即可完成
• 支持在线执行，不阻塞 DML 操作（理论上）
• MySQL 8.0 的 INSTANT 算法速度极快

缺点：

• 仍需要短暂的排他锁更新数据字典
• 高并发场景下可能出现锁等待
• 无法暂停或回滚操作
• 主从延迟仍然存在（虽然较小）
• 需要足够的磁盘空间存储在线日志

3.1.4 避坑指南

1. 确保行格式为 DYNAMIC：COMPACT 格式不支持 Online DDL
2. 字段添加到表末尾：添加到中间位置可能需要重建表
3. 避免同时执行多个 DDL：会导致锁竞争
4. 监控磁盘空间：确保有足够空间存储在线日志
5. 业务低峰期执行：减少对业务的影响

3.2 方案二：第三方工具方案

3.2.1 pt-online-schema-change（Percona Toolkit）

原理： 基于触发器实现，创建新表 → 创建触发器同步数据 → 分批拷贝数据 → 原子性切换表名。

步骤 1：安装 Percona Toolkit

# CentOS/RHEL
sudo yum install percona-toolkit

# Ubuntu/Debian
sudo apt-get install percona-toolkit

# 或使用 pip 安装
pip install percona-toolkit

# 验证安装
pt-online-schema-change --version

步骤 2：执行表结构变更

pt-online-schema-change \
  --alter "ADD COLUMN coupon_id BIGINT DEFAULT NULL COMMENT '优惠券ID'" \
  --execute \
  --host={host} \
  --port={port} \
  --user={user} \
  --password={password} \
  --charset=utf8mb4 \
  --chunk-size=1000 \
  --chunk-time=0.5 \
  --max-load="Threads_running=50" \
  --critical-load="Threads_running=100" \
  --progress=time,30 \
  --statistics \
  --print \
  D={db_name},t={table_name}

关键参数说明：

• --alter：要执行的 ALTER 语句（不包含 ALTER TABLE 部分）
• --execute：实际执行，不加此参数仅做预检查
• --chunk-size：每次处理的行数，默认 1000
• --chunk-time：每个 chunk 的执行时间（秒），默认 0.5
• --max-load：超过此负载时暂停执行
• --critical-load：超过此负载时终止执行
• --progress：显示进度，每 30 秒输出一次
• --statistics：显示统计信息

步骤 3：监控执行进度

# 查看进程
ps aux | grep pt-online-schema-change

# 查看新表数据量
mysql -h{host} -u{user} -p{password} -e "
SELECT COUNT(*) FROM {db_name}.{table_name}_new;
"

# 查看触发器
mysql -h{host} -u{user} -p{password} -e "
SHOW TRIGGERS FROM {db_name} LIKE '{table_name}';
"

步骤 4：验证和清理

# 验证数据一致性（pt-table-checksum）
pt-table-checksum \
  --host={host} \
  --user={user} \
  --password={password} \
  --databases={db_name} \
  --tables={table_name}

# 如果执行失败，清理临时表和触发器
pt-online-schema-change \
  --alter "ADD COLUMN coupon_id BIGINT DEFAULT NULL" \
  --host={host} \
  --user={user} \
  --password={password} \
  D={db_name},t={table_name}

优缺点：

• ✅ 优点：无锁表、可暂停、支持断点续跑、进度可控、自动限流
• ❌ 缺点：需要创建触发器（可能冲突）、需要额外磁盘空间、主从环境需要特殊处理

3.2.2 gh-ost（GitHub 开源工具）

原理： 基于 binlog 解析实现，不依赖触发器，通过解析 binlog 获取增量变更。

步骤 1：安装 gh-ost

# 下载最新版本
wget https://github.com/github/gh-ost/releases/download/v1.1.6/gh-ost-binary-linux-20200828140552.tar.gz
tar -xzf gh-ost-binary-linux-20200828140552.tar.gz
sudo mv gh-ost /usr/local/bin/
chmod +x /usr/local/bin/gh-ost

# 验证安装
gh-ost --version

步骤 2：执行表结构变更

gh-ost \
  --host={host} \
  --port={port} \
  --user={user} \
  --password={password} \
  --database={db_name} \
  --table={table_name} \
  --alter="ADD COLUMN coupon_id BIGINT DEFAULT NULL COMMENT '优惠券ID'" \
  --execute \
  --allow-on-master \
  --chunk-size=1000 \
  --max-load="Threads_running=50" \
  --critical-load="Threads_running=100" \
  --throttle-flag-file=/tmp/gh-ost.throttle \
  --postpone-cut-over-flag-file=/tmp/gh-ost.postpone \
  --serve-socket-file=/tmp/gh-ost.sock \
  --panic-flag-file=/tmp/gh-ost.panic \
  --verbose

关键参数说明：

• --alter：要执行的 ALTER 语句
• --execute：实际执行
• --allow-on-master：允许在主库执行（避免主从切换风险）
• --chunk-size：每次处理的行数
• --throttle-flag-file：创建此文件可暂停执行
• --postpone-cut-over-flag-file：创建此文件可延迟切换
• --serve-socket-file：通过 socket 文件控制执行

步骤 3：实时控制

# 暂停执行
touch /tmp/gh-ost.throttle

# 恢复执行
rm /tmp/gh-ost.throttle

# 延迟切换（数据拷贝完成后不立即切换）
touch /tmp/gh-ost.postpone

# 执行切换（删除延迟文件）
rm /tmp/gh-ost.postpone

# 紧急停止
touch /tmp/gh-ost.panic

# 通过 socket 查看进度
echo "status" | socat - /tmp/gh-ost.sock

步骤 4：监控和验证

# 查看执行日志（gh-ost 会输出详细日志）
tail -f /tmp/gh-ost.log

# 查看新表数据量
mysql -h{host} -u{user} -p{password} -e "
SELECT COUNT(*) FROM {db_name}._{table_name}_gho;
"

优缺点：

• ✅ 优点：无触发器、无锁表、可控性强、支持暂停/恢复、基于 binlog 更可靠
• ❌ 缺点：需要 binlog 格式为 ROW、需要额外磁盘空间、学习成本较高

3.2.3 两种工具对比

对比项	pt-online-schema-change	gh-ost
原理	基于触发器	基于 binlog 解析
触发器	需要创建 3 个触发器	不需要触发器
binlog 要求	STATEMENT/ROW/MIXED	必须 ROW 格式
可控性	支持暂停/恢复	支持暂停/恢复/延迟切换
主从环境	需要在主库执行	支持在主库或从库执行
学习成本	较低	中等
社区活跃度	高（Percona 维护）	高（GitHub 维护）

选型建议：

• 如果表已有触发器或触发器可能冲突 → 选择 gh-ost
• 如果 binlog 格式不是 ROW 且无法修改 → 选择 pt-online-schema-change
• 如果需要更精细的控制（延迟切换） → 选择 gh-ost
• 如果团队更熟悉 Percona 工具 → 选择 pt-online-schema-change

3.3 方案三：分库分表 + 双写迁移方案

3.3.1 适用场景

• 表数据量达到亿级以上（1 亿+）
• 核心业务表，QPS 极高（> 5000）
• 需要长期维护，未来可能继续扩容
• 有足够的技术团队和开发资源

3.3.2 实施步骤

步骤 1：设计分表策略

-- 假设原表：t_order（1 亿条数据）
-- 分表策略：按订单ID取模，分成 16 张表

-- 创建分表结构（包含新字段）
CREATE TABLE t_order_0 (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    order_status TINYINT NOT NULL,
    coupon_id BIGINT DEFAULT NULL COMMENT '优惠券ID',  -- 新字段
    create_time DATETIME NOT NULL,
    INDEX idx_user_id(user_id),
    INDEX idx_create_time(create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建 t_order_1 到 t_order_15（共 16 张表）
-- 可以使用脚本批量创建

步骤 2：部署双写逻辑（应用层）

// Java 示例：双写逻辑
@Service
public class OrderService {
    
    @Autowired
    private OrderMapper orderMapper;
    
    // 分表路由规则
    private String getTableName(Long orderId) {
        int tableIndex = (int) (orderId % 16);
        return "t_order_" + tableIndex;
    }
    
    // 双写：同时写旧表和新表
    public void createOrder(Order order) {
        // 1. 写旧表（保证业务不中断）
        orderMapper.insertToOldTable(order);
        
        // 2. 写新表（分表）
        String tableName = getTableName(order.getId());
        orderMapper.insertToNewTable(tableName, order);
        
        // 3. 异步校验数据一致性（可选）
        asyncValidateData(order);
    }
    
    // 读逻辑：优先读新表，失败则读旧表
    public Order getOrder(Long orderId) {
        String tableName = getTableName(orderId);
        try {
            return orderMapper.selectFromNewTable(tableName, orderId);
        } catch (Exception e) {
            // 降级到旧表
            return orderMapper.selectFromOldTable(orderId);
        }
    }
}

步骤 3：数据全量同步

# 使用 DataX 或自研脚本进行全量同步
# 示例：使用 mysqldump + 分批导入

# 1. 导出旧表数据（分批导出，避免内存溢出）
mysqldump -h{old_host} -u{user} -p{password} \
  --single-transaction \
  --where="id >= 1 AND id < 10000000" \
  {db_name} t_order > t_order_part1.sql

# 2. 数据转换和分表路由（Python 脚本示例）
python3 migrate_data.py \
  --source-file=t_order_part1.sql \
  --target-tables=t_order_0,t_order_1,...,t_order_15 \
  --route-key=id \
  --route-mod=16

# 3. 导入到对应分表
mysql -h{new_host} -u{user} -p{password} {db_name} < t_order_0_data.sql

步骤 4：数据一致性校验

-- 方式 1：COUNT 校验
SELECT 
    (SELECT COUNT(*) FROM t_order) AS old_count,
    (SELECT SUM(cnt) FROM (
        SELECT COUNT(*) AS cnt FROM t_order_0
        UNION ALL SELECT COUNT(*) FROM t_order_1
        -- ... 其他表
        UNION ALL SELECT COUNT(*) FROM t_order_15
    ) AS t) AS new_count;

-- 方式 2：SUM 校验（对数值字段求和）
SELECT 
    (SELECT SUM(order_amount) FROM t_order) AS old_sum,
    (SELECT SUM(total) FROM (
        SELECT SUM(order_amount) AS total FROM t_order_0
        UNION ALL SELECT SUM(order_amount) FROM t_order_1
        -- ... 其他表
        UNION ALL SELECT SUM(order_amount) FROM t_order_15
    ) AS t) AS new_sum;

-- 方式 3：抽样对比（Python 脚本）
python3 validate_data.py \
  --old-table=t_order \
  --new-tables=t_order_0,t_order_1,...,t_order_15 \
  --sample-size=10000 \
  --route-mod=16

步骤 5：切换读写流量

// 灰度切换：逐步将读流量切换到新表
// 阶段 1：10% 流量读新表
if (orderId % 10 == 0) {
    return readFromNewTable(orderId);
} else {
    return readFromOldTable(orderId);
}

// 阶段 2：50% 流量读新表
if (orderId % 2 == 0) {
    return readFromNewTable(orderId);
}

// 阶段 3：100% 流量读新表
return readFromNewTable(orderId);

步骤 6：下线旧表

-- 1. 确认新表数据稳定运行 1-2 周
-- 2. 停止写旧表（代码已全部切换到新表）
-- 3. 最后一次数据校验
-- 4. 重命名旧表（备份）
ALTER TABLE t_order RENAME TO t_order_backup_20240101;

-- 5. 观察 1 周，确认无问题后删除
DROP TABLE t_order_backup_20240101;

3.3.3 优缺点分析

优点：

• 彻底解决大表问题，支持水平扩展
• 不影响现有业务（双写期间）
• 可以逐步迁移，风险可控
• 为未来扩容打下基础

缺点：

• 开发工作量大，需要修改应用代码
• 双写期间数据一致性需要保障
• 需要额外的存储空间
• 运维复杂度增加

四、风险管控与避坑指南

4.1 操作前的准备

4.1.1 业务评估

-- 1. 评估表数据量
SELECT 
    TABLE_ROWS,
    ROUND(DATA_LENGTH/1024/1024, 2) AS DATA_SIZE_MB,
    ROUND(INDEX_LENGTH/1024/1024, 2) AS INDEX_SIZE_MB,
    ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024, 2) AS TOTAL_SIZE_MB
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = '{db_name}' AND TABLE_NAME = '{table_name}';

-- 2. 评估业务并发量
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Com_insert';
SHOW GLOBAL STATUS LIKE 'Com_update';

-- 3. 评估磁盘空间
SELECT 
    ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024, 2) AS DB_SIZE_GB
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = '{db_name}';

-- 查看磁盘使用情况（Linux）
df -h

4.1.2 备份数据

# 1. 全量备份（使用 mysqldump）
mysqldump -h{host} -u{user} -p{password} \
  --single-transaction \
  --master-data=2 \
  --routines \
  --triggers \
  {db_name} {table_name} > {table_name}_backup_$(date +%Y%m%d).sql

# 2. 或使用物理备份（Percona XtraBackup）
innobackupex --host={host} --user={user} --password={password} \
  --databases={db_name}.{table_name} \
  /backup/path/

# 3. 验证备份文件
mysql -h{host} -u{user} -p{password} -e "
SELECT COUNT(*) FROM {db_name}.{table_name};
" > before_count.txt

4.1.3 限流降级准备

-- 1. 设置慢查询阈值（临时调整）
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log = 'ON';

-- 2. 准备限流脚本（pt-kill）
pt-kill --host={host} --user={user} --password={password} \
  --busy-time=60 \
  --kill \
  --print

-- 3. 准备降级方案（应用层）
-- 如果 DDL 执行时间过长，可以考虑：
-- - 临时关闭非核心功能
-- - 增加缓存命中率，减少数据库查询
-- - 启用只读模式（如果可以接受）

4.2 操作中的监控

4.2.1 锁等待监控

-- 1. 实时监控锁等待
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_time_sec,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- 2. 监控 MDL 锁
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    LOCK_TYPE,
    LOCK_DURATION,
    LOCK_STATUS,
    OWNER_THREAD_ID
FROM performance_schema.metadata_locks 
WHERE OBJECT_SCHEMA = '{db_name}' 
  AND OBJECT_NAME = '{table_name}';

4.2.2 CPU/IO 负载监控

# 1. 系统负载监控
top
htop
iostat -x 1

# 2. MySQL 状态监控
mysqladmin -h{host} -u{user} -p{password} extended-status | grep -E 'Threads_connected|Threads_running|Questions|Slow_queries'

# 3. InnoDB 状态监控
mysql -h{host} -u{user} -p{password} -e "SHOW ENGINE INNODB STATUS\G" | grep -E 'Log sequence number|Log flushed up to|Pages read|Pages written'

4.2.3 主从延迟监控

-- 1. 查看主从延迟（主库）
SHOW MASTER STATUS;

-- 2. 查看主从延迟（从库）
SHOW SLAVE STATUS\G

-- 关注字段：
-- Seconds_Behind_Master: 从库延迟秒数
-- Slave_SQL_Running: SQL 线程是否运行
-- Slave_IO_Running: IO 线程是否运行
-- Last_Error: 错误信息

-- 3. 使用 pt-heartbeat 精确监控延迟
pt-heartbeat --update -h{master_host} -u{user} -p{password} --create-table --database={db_name}
pt-heartbeat --monitor -h{slave_host} -u{user} -p{password} --database={db_name} --interval=1

4.3 操作后的验证

4.3.1 字段可用性验证

-- 1. 检查字段是否存在
DESC {db_name}.{table_name};

-- 2. 检查字段属性
SELECT 
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    COLUMN_COMMENT
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = '{db_name}' 
  AND TABLE_NAME = '{table_name}' 
  AND COLUMN_NAME = 'coupon_id';

-- 3. 测试字段读写
INSERT INTO {db_name}.{table_name} (coupon_id, ...) VALUES (12345, ...);
SELECT coupon_id FROM {db_name}.{table_name} WHERE id = 0;
UPDATE {db_name}.{table_name} SET coupon_id = 67890 WHERE id = 0;

4.3.2 业务功能回归

# 1. 执行自动化测试用例
# 2. 手动验证核心业务流程
# 3. 监控业务指标（错误率、响应时间、QPS）
# 4. 检查应用日志是否有异常

4.3.3 性能测试

-- 1. 对比 DDL 前后的查询性能
EXPLAIN SELECT * FROM {db_name}.{table_name} WHERE coupon_id = 12345;

-- 2. 检查执行计划是否变化
-- 3. 监控慢查询日志
-- 4. 对比 DDL 前后的表统计信息
ANALYZE TABLE {db_name}.{table_name};
SHOW TABLE STATUS FROM {db_name} LIKE '{table_name}';

4.4 典型坑点及解决方案

坑点 1：忽略行格式导致空间浪费

问题： 表使用 COMPACT 格式，不支持 Online DDL，导致锁表时间过长。

解决方案：

-- 提前修改行格式
ALTER TABLE {db_name}.{table_name} ROW_FORMAT=DYNAMIC;

-- 注意：修改行格式需要重建表，建议在业务低峰期执行

坑点 2：触发器冲突

问题： 使用 pt-online-schema-change 时，表已有触发器，导致工具执行失败。

解决方案：

-- 1. 检查现有触发器
SHOW TRIGGERS FROM {db_name} LIKE '{table_name}';

-- 2. 方案 A：临时禁用触发器（如果业务允许）
-- 方案 B：使用 gh-ost（不依赖触发器）

坑点 3：磁盘空间不足

问题： DDL 执行过程中磁盘空间不足，导致操作失败。

解决方案：

# 1. 提前检查磁盘空间
df -h

# 2. 估算所需空间（至少是表大小的 2 倍）
# 3. 清理临时文件或扩容磁盘
# 4. 使用第三方工具时可以指定临时目录
pt-online-schema-change \
  --tmp-table-path=/data/mysql/tmp \
  ...

坑点 4：主从延迟过高

问题： DDL 执行后，从库延迟飙升，影响读业务。

解决方案：

-- 1. 在从库先执行 DDL（如果使用 gh-ost）
-- 2. 增加从库配置（innodb_flush_log_at_trx_commit=2）
-- 3. 使用多线程复制（slave_parallel_workers）
-- 4. 临时增加从库资源（CPU、内存、IO）

坑点 5：未预留足够时间

问题： 低估 DDL 执行时间，在业务高峰期仍未完成。

解决方案：

# 1. 在测试环境先执行，评估耗时
# 2. 预留至少 3 倍的时间窗口
# 3. 使用可暂停的工具（gh-ost、pt-osc）
# 4. 准备回滚方案

五、总结与选型建议

5.1 方案选型表

表数据量	业务并发量	MySQL 版本	推荐方案	理由
< 1000 万	< 500 QPS	5.6+	Online DDL	简单快速，影响小
1000-5000 万	500-2000 QPS	5.6+	Online DDL（低峰期）	原生支持，成本低
1000-5000 万	2000-5000 QPS	5.6+	gh-ost / pt-osc	无锁表，可控性强
5000 万-1 亿	任意	5.6+	gh-ost / pt-osc	必须使用工具，避免锁表
> 1 亿	任意	任意	分库分表 + 双写	长期方案，支持扩展
核心业务表	> 5000 QPS	任意	gh-ost（推荐）	无触发器，更可靠

5.2 实战经验小贴士

1. 执行 DDL 前建议将 MySQL 的慢查询日志临时调优

   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL long_query_time = 0.1;  -- 临时降低阈值，便于排查问题
   

2. 使用 gh-ost 时建议开启 --allow-on-master 参数

   --allow-on-master  # 避免主从切换风险
   

3. Online DDL 执行前检查 innodb_online_alter_log_max_size

   -- 确保足够大，避免因日志空间不足导致操作失败
   SET GLOBAL innodb_online_alter_log_max_size = 2147483648;  -- 2GB
   

4. 使用 pt-osc 时建议设置合理的 chunk-size

   -- 根据表大小和业务负载调整
   -- 小表：chunk-size=5000
   -- 大表：chunk-size=1000
   -- 超大表：chunk-size=500
   

5. 主从环境建议先在从库测试

   # 使用 gh-ost 可以在从库执行，测试通过后再在主库执行
   gh-ost --test-on-replica ...
   

6. DDL 执行期间监控应用错误日志

   # 实时监控应用日志，及时发现异常
   tail -f /var/log/app/error.log | grep -i "timeout\|lock\|deadlock"
   

5.3 延伸思考

MySQL 8.0 原子 DDL 的优势

MySQL 8.0 引入的原子 DDL 特性，使得 DDL 操作具有以下优势：

1. 原子性保证：DDL 操作要么全部成功，要么全部回滚，不会出现中间状态
2. 崩溃恢复：即使服务器崩溃，重启后也能自动恢复或回滚未完成的 DDL
3. 元数据一致性：数据字典存储在 InnoDB 表中，支持事务，保证元数据一致性

-- MySQL 8.0 示例
ALTER TABLE {db_name}.{table_name} 
ADD COLUMN coupon_id BIGINT DEFAULT NULL,
ALGORITHM=INSTANT,  -- 瞬间完成（如果支持）
LOCK=NONE;

云数据库（RDS MySQL）的一键加字段功能原理

云数据库（如阿里云 RDS、腾讯云 CDB）提供的一键加字段功能，底层原理通常是：

1. 智能调度：根据表大小、业务负载自动选择最优方案
2. 工具封装：内部使用 gh-ost 或 pt-osc，对用户透明
3. 资源保障：提供足够的计算和存储资源
4. 监控告警：实时监控执行进度和系统负载，异常自动告警
5. 回滚机制：支持一键回滚，降低操作风险

使用建议：

• 如果使用云数据库，优先使用云厂商提供的 DDL 工具
• 如果自建 MySQL，建议使用 gh-ost 或 pt-osc
• 核心业务表建议在业务低峰期执行，并做好监控和回滚准备

六、参考资料

1. MySQL 官方文档：dev.mysql.com/doc/refman/…
2. Percona Toolkit 文档：www.percona.com/software/da…
3. gh-ost 项目：github.com/github/gh-o…
4. 《高性能 MySQL》（第 3 版）- Baron Schwartz 等著
5. 《MySQL 技术内幕：InnoDB 存储引擎》- 姜承尧著

---

版权声明： 本文为技术分享文章，欢迎转载，请注明出处。