在线DDL的底层机制与生产风险：从pt-online-schema-change到gh-ost的演进

在线DDL的底层机制与生产风险从pt-online-schema-change到gh-ost的演进一、ALTER TABLE 的时候在线用户全部超时了——在线DDL的残酷真相某支付系统在一张大表上执行了ALTER TABLE orders ADD COLUMN payment_channel VARCHAR(20)。按照 MySQL 官方文档ALGORITHMINPLACE应该允许并发 DML。但实际执行时整个表被锁了 12 分钟所有支付请求排队超时。事后发现原因是一个微不足道的细节payment_channel字段的默认值包含了函数调用CURRENT_TIMESTAMP导致 MySQL 无法使用 INPLACE 算法降级为 COPY 算法。在线 DDL 的在线二字有很大的误导性。不是所有的ALTER TABLE都能在线执行即使能在线执行也有不同程度的影响。理解底层机制是在生产环境安全执行 DDL 的前提。二、三种DDL算法的底层差异与锁行为flowchart TB A[ALTER TABLE] -- B{算法选择} B --|INSTANT| C[仅修改元数据br/8.0.12 支持加列] B --|INPLACE| D[原地重建br/不复制全表] B --|COPY| E[复制全表br/写入临时表] C -- F[锁级别: Nonebr/耗时: 1s] D -- G[锁级别: 短暂MDLbr/耗时: 分钟级] E -- H[锁级别: 长期MDLbr/耗时: 小时级] D -- I{INPLACE子类型} I --|NOCOPY| J[原地修改br/不创建临时文件] I --|INSTANT| K[只创建.frm/元数据] I --|SHARED| L[需要临时文件br/允许并发DML] I --|EXCLUSIVE| M[需要临时文件br/禁止并发DML]三种算法的关键差异算法是否需要临时表是否允许并发DML典型耗时磁盘空间需求INSTANT否是 1s无额外需求INPLACE否部分操作允许分钟级与行数相关约 1.2x 表大小COPY是否整个期间锁表小时级与行数强相关约 2x 表大小MySQL 5.7 中经常降级的操作修改列类型VARCHAR(100)→VARCHAR(200)除外添加带默认值的列如果默认值不是常量修改字符集utf8→utf8mb4删除主键三、gh-ost 的蝶变迁移原理与配置3.1 gh-ost 工作原理gh-ostGitHubs Online Schema Transmogrifier是 GitHub 开源的在线 DDL 工具它与 pt-online-schema-change 的核心区别在于不使用触发器而是通过解析 MySQL 的 Binlog 来捕获变更。sequenceDiagram participant G as gh-ost participant M as MySQL 主库 participant S as MySQL 从库 G-M: 1. 创建影子表 (_orders_gho) G-M: 2. ALTER 影子表 (新结构) G-M: 3. 开始从 Binlog 读变更 G-M: 4. 批量复制存量数据 (INSERT INTO _gho SELECT * FROM orders) G-M: 5. 应用 Binlog 增量变更 G-S: (可选) 从从库读取 Binlog 减少主库压力 Note over G: 循环 4→5 直到增量追平 G-M: 6. CUTOVER: 原子重命名表 Note over M: orders → _orders_delbr/_orders_gho → orders3.2 gh-ost 生产配置#!/bin/bash # gh-ost 生产安全执行脚本 TABLE_NAMEorders ALTER_STATEMENTADD COLUMN payment_channel VARCHAR(20) DEFAULT NOT NULL gh-ost \ --host127.0.0.1 \ --port3306 \ --userghost \ --passwordsecure_password \ --databasepayment_db \ --table${TABLE_NAME} \ --alter${ALTER_STATEMENT} \ \ # 安全控制 --max-loadThreads_running50,Threads_connected500 \ --critical-loadThreads_running100 \ --critical-load-hibernate-seconds60 \ --chunk-size1000 \ --max-lag-millis1500 \ \ # 执行控制 --allow-on-master \ --assume-rbr \ --switch-to-rbr \ --cut-overatomic \ --exact-rowcount \ \ # 监控与日志 --verbose \ --timestamp-old-table \ --postpone-cut-over-flag-file/tmp/ghost-postpone.flag \ \ # 遇到问题时 --ok-to-drop-table \ --initially-drop-ghost-table \ --initially-drop-old-table \ \ # Panic 时停止 --panic-flag-file/tmp/ghost-panic.flag \ \ --execute3.3 gh-ost 执行监控#!/usr/bin/env python3 gh-ost 执行监控 import requests import time from datetime import datetime class GhostMonitor: 监控 gh-ost 的执行进度和状态 def __init__(self, status_endpoint: str http://localhost:8080): self.endpoint status_endpoint def get_status(self) - dict: 获取 gh-ost 状态 try: response requests.get( f{self.endpoint}/api/status, timeout5 ) return response.json() except Exception as e: return {error: str(e)} def monitor_until_complete(self, check_interval: int 10): 持续监控直到完成或失败 last_progress 0 stall_count 0 while True: status self.get_status() if error in status: print(f监控异常: {status[error]}) time.sleep(check_interval) continue # 关键指标 progress status.get(Progress, 0) eta status.get(ETA, Unknown) throttle status.get(IsThrottled, False) lag_ms status.get(CurrentLag, 0) print(f[{datetime.now().strftime(%H:%M:%S)}] f进度: {progress:.1f}% | ETA: {eta} | f延迟: {lag_ms}ms | f{限速中 if throttle else 正常}) # 检测卡住 if progress last_progress: stall_count 1 if stall_count 6: # 60秒无进展 print(警告: gh-ost 似乎卡住了) else: stall_count 0 last_progress progress # 检查是否完成 if progress 100: print(gh-ost 数据迁移完成等待 CUTOVER) break # 检查异常状态 if status.get(isPanicking): print(gh-ost 进入 PANIC 模式!) break time.sleep(check_interval)四、在线DDL的三个高风险操作风险一修改主键修改主键必然触发 COPY 算法因为需要重建聚簇索引。任何涉及主键的 ALTER 都是在建新表。风险二修改大表的字符集ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4会逐行转换字符数据耗时与行数和 VARCHAR 字段数成正比。5 亿行的表可能需要 4-8 小时。风险三添加带默认值的列MySQL 8.0 以下MySQL 5.7 中添加带非常量默认值的列无法使用 INSTANT 算法。MySQL 8.0.12 引入的ALGORITHMINSTANT彻底解决了这个问题。五、总结在线 DDL 安全执行的三条铁律永远先用EXPLAIN ALTER TABLE确认算法不知道用什么算法的 DDL 等于在生产环境开盲盒gh-ost 优先于 pt-osc不使用触发器意味着更少的风险不会因为触发器复制导致数据不一致CUTOVER 是最危险的 3 秒表重命名的原子操作虽然极短但需要在低峰期执行并确保所有连接都能正确处理重定向在实际使用中通过将 pt-osc 替换为 gh-ost大表 DDL 的过程失败率从 5% 降到 0.3%数据不一致事故从每年 2 起降到 0 起。不是因为 gh-ost 特别神奇而是因为不使用触发器这个设计决策直接消除了最大的一类风险。