MySQL 大表 DDL 操作导致锁表

FreeGuideOnline 最新 2026-07-06

bash pt-online-schema-change
--host=127.0.0.1
--user=root
--password=your_pass
--alter "ADD COLUMN email VARCHAR(255) DEFAULT NULL"
D=test,t=users
--execute


**重要参数**：
- `--chunk-size`：每次拷贝的行数，避免长事务。
- `--max-load`：设置阈值自动暂停，保护生产环境。
- `--recursion-method`：检测从库延迟，通常用 `dsn` 配合。

### 方案二：使用 GitHub 的 gh-ost
`gh-ost` 是无触发器的在线迁移工具，通过解析 binlog 同步增量数据，更安全、可控。

**优势**：
- 无需安装触发器，避免触发器带来的额外锁和性能开销。
- 支持暂停、动态调整负载、可随时切换回原表。
- 原生支持主从架构，可直接在从库执行再同步主库。

**工作流程**：
1. 创建影子表，应用 DDL。
2. 模拟从库订阅 binlog，解析出原表的变更事件，应用到影子表。
3. 同时分批拷贝存量数据。
4. 数据追平后，进行 CUT-OVER（原子表名交换）。

**常用命令**：
```bash
gh-ost \
  --host=127.0.0.1 \
  --user="root" \
  --password="your_pass" \
  --database="test" \
  --table="users" \
  --alter="ADD COLUMN email VARCHAR(255) DEFAULT NULL" \
  --initially-drop-ghost-table \
  --initially-drop-old-table \
  --max-load='Threads_running=30' \
  --critical-load='Threads_running=100' \
  --chunk-size=1000 \
  --execute

关键参数：

--max-load 和 --critical-load：根据系统负载弹性调整。
--cut-over：默认使用原子表名交换，可选择两阶段提交等策略。
--postpone-cut-over-flag-file：允许人工控制最终切换时机。

方案三：原生 Online DDL + 合理控制

如果业务允许短暂影响，可合理利用 MySQL 5.7/8.0 的优化，配合策略减少风险。

先处理长事务：通过 SELECT * FROM information_schema.innodb_trx 找出并杀死空闲连接，避免 MDL 阻塞。
设置 lock_wait_timeout：SET SESSION lock_wait_timeout=2; 防止 DDL 无限等待阻塞整个实例。
低峰期分步操作：将复杂 DDL 拆解，比如先加列（允许 INPLACE），后更新默认值。
监控 MDL 等待：使用 SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_NAME='table_name'; 监控锁等待链。

预防锁表的长治久安之策

设计阶段避免大表 DDL

尽量使用可扩展的表结构，如字段预留、采用 JSON 列存储非核心属性。
避免随意修改大表列类型，日期用 bigint 或 datetime 统一存储。
分区表对某些 DDL 操作只有局部锁，可减轻影响。

SQL 审核与灰度流程

所有生产 DDL 必须经过审核，并在从库或预发环境模拟。
使用 pt-osc 或 gh-ost 前先在从库测试，观察复制延迟和负载。
制定标准操作流程：申请窗口 → 执行工具 → 监控 → 回滚预案。

监控和快速发现

周期性检查 metadata_locks 表，设置告警。
监控线程活跃数和锁等待时间，及时 kill 阻塞源。
使用 Orchestrator 等工具管理拓扑，自动检测 DDL 引发的从库延迟。

锁表后的应急处理

当大表 DDL 已经开始锁表，立即行动降低损害：

找出阻塞源头：

SELECT waiting_pid, waiting_query, blocking_pid, blocking_query
FROM sys.schema_table_lock_waits WHERE waiting_table='db.table';