在线 DDL 原理与最佳实践：MySQL/PostgreSQL 大表变更的锁与数据拷贝

MySQL 表结构变更的两种成本：
  1. MDL（元数据锁）：
     任何 DDL 先要拿到 MDL 写锁
     → 期间阻塞所有读写请求
  2. 数据拷贝：
     变更需要重建表（如加列且非原地、改类型、加索引）
     → 逐行 COPY 旧数据到新表，期间持续占用资源

两种成本叠加 = 大表 DDL 慢且阻塞

历史教训：
  · MySQL <5.6：ALTER 全程持锁（表级写锁），大表变更≈宕机
  · MySQL 5.6+：Online DDL 支持，部分操作不再全程持锁
  · 但"拷贝行数据"阶段的锁仍需控制

① INSTANT（MySQL 8.0.12+）：
   只改元数据，不动行数据
   例：加列（列在末尾、无默认值外）、改列默认值
   速度：毫秒级，零阻塞

② INPLACE（原地重建）：
   在同一表空间中重建，期间可并发读写
   例：加普通索引、加/删列（部分）、修改 varchar 长度（同空间）

③ COPY（拷贝表）：
   创建新表 + 拷贝全部数据 + 原子替换
   例：改主键、改字符集、压缩表
   期间按锁策略控制读写并发

MySQL 在线 DDL 过程：
  阶段1：准备（拿 MDL 写锁，短）
  阶段2：执行（In-place 重建 / Copy）
  阶段3：提交（拿 MDL 写锁，短）

并发控制由参数决定：
  ALGORITHM  = INSTANT | INPLACE | COPY
  LOCK       = NONE | SHARED | EXCLUSIVE
  → ALGORITHM 决定"怎么改"，LOCK 决定"允许的并发度"

-- 尽量选 NONE：变更期间允许并发读写
ALTER TABLE orders
  ADD COLUMN status TINYINT DEFAULT 0,
  ALGORITHM=INPLACE, LOCK=NONE;

-- 明确拒绝阻塞：若不能 NONE 就报错（安全策略）
ALTER TABLE orders
  ADD INDEX idx_user (user_id),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 只改元数据（最快）
ALTER TABLE orders
  ALTER COLUMN note SET DEFAULT 'x',
  ALGORITHM=INSTANT;

InnoDB 加二级索引的 INPLACE 过程：
  1. 读现有聚集索引，生成新索引的 B+ 树（后台线程）
  2. 期间 DML 正常执行，同时把变更记录到 redo log
  3. 提交时用 redo 把增量合并进新索引

→ 设计成"构建索引"与"业务写"并行，各自记录、最终合并
→ 关键风险：redo 增长、磁盘 IO 压力、长事务

PostgreSQL 每个表 = 堆文件 + 索引
ALTER 大部分操作是"加列/加索引"：

加列：
  ALTER TABLE ADD COLUMN → 只更新 catalog 元数据
  → O(1) 完成，不重写表（版本 12+ 不再需要整表重写，仅加物理空列）

加索引：
  CREATE INDEX CONCURRENTLY → 与业务并发建索引
  三阶段：扫描建树 → 等待 → 增量合并
  → 不阻塞 DML，只需短锁

修改类型/删除列：需要重写表
  → 使用 PG 12+ 的"默认并行/触发重写"，仍需谨慎

-- 并发建索引：不阻塞读写
CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);

-- 大表场景：分块执行，避免一次性资源占用
SET maintenance_work_mem = '1GB';   -- 建索引内存
SET max_parallel_maintenance_workers = 8;   -- 并行度

-- 大表加 NOT NULL 约束的安全三步（PG 12+）
ALTER TABLE orders ADD CONSTRAINT c_required CHECK (user_id IS NOT NULL) NOT VALID;
-- 校验现有数据（慢，但不阻塞）
ALTER TABLE orders VALIDATE CONSTRAINT c_required;

数据库原生在线 DDL 的局限：
  · MySQL 部分操作仍 COPY + 需手动权衡锁
  · 无法控制"变更期间的复制延迟"（主从）
  · 变更大表时 redo/binlog 增量可能失控

第三方工具的思路（trigger-less 更好）：
  · 创建新表（改好的结构）
  · 从旧表拷贝存量数据到新表（后台、限速）
  · 拷贝期间持续同步增量（binlog / 触发器）
  · 校验一致 → 原子换名（RENAME）
  → 全程不阻塞写，主从可逐步切换

# gh-ost 核心优点：基于 binlog 同步，无触发器开销
gh-ost \
  --host=db-1 --port=3306 \
  --user=app --password=*** \
  --database=orders_db \
  --table=orders \
  --alter="ADD COLUMN status TINYINT DEFAULT 0" \
  --allow-on-master \
  --execute

# 常用控制参数
--max-load=Threads_running=200          # 负载过高自动减速
--max-lag-millis=1500                   # 从库延迟上限
--chunk-size=1000                       # 拷贝批大小
--throttle-control-replicas="db-2"      # 按从库延迟限速
--cut-over-lock-timeout-seconds=1       # 切换锁超时
--execute                               # 真正执行（否则 dry-run）

# pt-osc 使用触发器跟踪增量，与 gh-ost 思路不同
pt-online-schema-change \
  --alter "ADD COLUMN status TINYINT DEFAULT 0" \
  --chunk-size=1000 \
  --max-lag=1 \
  --max-load "Threads_Running=200" \
  --critical-load "Threads_Running=500" \
  D=orders_db,t=orders

1. 评估操作类型（INSTANT? INPLACE? COPY?）
2. 估算成本（表大小、rows、磁盘空间、redo）
3. 低峰期执行 + 窗口审批
4. 先小表演练，再大表
5. 全程监控：锁等待、redo 增长、IO、从库延迟
6. 预留回滚预案（备份/快照/可回滚设计）
7. 变更后验证：数据一致性 + 性能

· 禁止在主库直接 COPY 级 ALTER 大表（用 gh-ost/pt-osc）
· 变更前确认磁盘空间（重建表需≈表大小空间）
· 观察长事务：长事务持有 MDL，会阻塞 DDL
· 主从场景：先备库变更 → 灰度切换 → 再主库变更
· 设置 MDL 锁超时（lock_wait_timeout）防无限等待
· 监控 performance_schema.metadata_locks 定位持锁者

-- 定位阻塞 DDL 的会话
SELECT * FROM performance_schema.metadata_locks
WHERE LOCK_STATUS='PENDING';

-- 定位持锁长事务并评估终止
SELECT * FROM information_schema.innodb_trx
WHERE trx_state != 'COMMITTED' ORDER BY trx_started;

-- 极端情况：终止阻塞会话（谨慎）
CALL mysql.rds_kill(?);   -- 或 KILL <thread_id>

gh-ost 三阶段：
  1. 建新表（目标结构）
  2. 拷贝存量：SELECT 旧表 → chunk 写入新表
     同时应用 binlog 增量（INSERT/UPDATE/DELETE）
  3. cut-over：RENAME old→old_hidden, new→old（原子）

增量同步关键：
  · binlog 事件按主键应用，顺序一致
  · 拷贝期间主键并发写 → 用唯一键去重
  · 校验：chunk 数 / 行数对比

变更后一致性验证：
  · 行数对比（COUNT 或校验和）
  · 抽样对比字段值
  · 索引完整性检查（ANALYZE / 查询性能测试）
  · 主从：对比主备库表结构一致

-- 8.0.12+：末尾加列即时完成
ALTER TABLE orders ADD COLUMN remark VARCHAR(100), ALGORITHM=INSTANT;

-- 约束：不能加在非末尾 / 不能加主键 / 瞬时加列数有限制
-- 每表 INSTANT 列数量受 row 大小与版本限制

-- 修改列默认值：INSTANT
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 1;

-- 索引改可见性/隐藏：INPLACE
ALTER TABLE orders ALTER INDEX idx_user INVISIBLE;

TiDB 的 DDL 设计（借鉴 Google F1）：
  · 异步 DDL：变更请求提交后由 DDL Owner 后台执行
  · 多阶段状态机：none → delete-only → write-only → ready → public
  · 全程不阻塞读写
  · 可随时暂停/恢复（ADMIN PAUSE DDL）

ALTER TABLE orders ADD COLUMN status TINYINT DEFAULT 0;  -- 立即返回，后台执行

· 优点：大表无感、可并行、可取消
· 代价：实现复杂、元数据一致性依赖一致协议
· 云原生库（PolarDB/Aurora）多数也提供"异步变更"

变更类型判断：
  MySQL：
    INSTANT 可做 → 直接 ALTER（毫秒）
    INPLACE 可做 → 可 ALTER + LOCK=NONE（注意执行时长）
    COPY 才可做 → gh-ost / pt-osc（推荐）
  PostgreSQL：
    加列/加索引 → 原生（CONCURRENTLY 建索引）
    重写类 → 评估分批 / 工具 / 停机窗口

□ 识别变更算法与锁模型
□ 磁盘/内存/IO 容量预评估
□ 低峰窗口 + 变更审批
□ dry-run 演练（gh-ost --test-on-replica 更佳）
□ 监控：锁等待、redo、IO、从库延迟
□ max-load / max-lag 兜底限速
□ 回滚预案（备份、快照）
□ 变更后一致性 + 性能验证