Skip to content

4.1-12 千万级大表加索引:Online DDL、gh-ost、pt-osc ​

所属:04 数据存储 / 4.1 MySQL 分级:B(按 A 类模板展开) 状态:◐ 初稿 关联:4.1-2、4.1-5、4.1-6、4.1-9、4.1-10、4.1-11、4.1-13、4.1-16

  • 题型:场景 / 流程 / 取舍 / 运维

  • 一句话结论:大表加索引的核心不是“执行 ALTER TABLE”,而是控制 MDL、复制延迟、磁盘/IO、回滚和业务可用性:优先用 ALGORITHM=INPLACE, LOCK=NONE 的 Online DDL;不支持或风险高时用 gh-ost / pt-osc 的“影子表 + 增量同步 + 原子切表”;无论哪种都要低峰、限流、监控、可回滚,并先在从库/影子库演练。

  • 分层要点:

    1. 先判断能不能 Online DDL:MySQL 5.6+ 支持 Online DDL;加二级索引通常是 ALGORITHM=INPLACE, LOCK=NONE,允许并发 DML,但开始/结束仍要短暂 MDL 排他锁。加列在 8.0.12+ 可 ALGORITHM=INSTANT;改列类型、改字符集、改主键等可能只能 COPY,会锁写、重建表。操作支持矩阵要以官方文档和 SHOW WARNINGS 为准,不能背口诀。
    2. Online DDL 的原理与代价:INPLACE 尽量不复制整表,但加索引仍要读表、排序、写索引;DDL 期间的 DML 记录到 innodb_online_alter_log_max_size(默认 128MB)的日志里,超限 DDL 失败;临时文件用 innodb_tmpdir/tmpdir;会消耗 IO、CPU、磁盘和 BP。
    3. MDL 是最大暗坑:Online DDL 开始和结束都要拿 MDL 排他锁;长事务、未提交事务、慢查询持有 MDL 读锁时,DDL 会等待;DDL 一旦排队,后续 DML 又排在 DDL 后面,形成“MDL 等待风暴”。低峰只是降低概率,执行前必须查长事务。
    4. gh-ost 原理:创建 ghost 表(同结构 + 新索引),按 chunk 逐批 copy 老数据,同时通过 binlog(ROW 格式)持续把增量应用到 ghost 表;最后用 RENAME TABLE 原子切换。无触发器、可限流、可暂停、支持在从库迁移;需要 binlog_format=ROW、binlog_row_image=FULL、复制权限。
    5. pt-osc 原理:pt-online-schema-change 创建 shadow 表,用触发器同步 INSERT/UPDATE/DELETE 增量,逐批 copy 老数据,最后 RENAME TABLE 切换。成熟、兼容广,但触发器有性能开销,已有触发器/外键场景复杂,需要触发器权限。
    6. 方案选择:能原生 Online DDL 且支持 LOCK=NONE 的,优先原生;大表、长事务多、触发器多、需要限流/暂停/从库迁移的,用 gh-ost;自建、无触发器冲突、习惯 Percona 工具链的,用 pt-osc。云 RDS 可能限制 SUPER/触发器/复制权限,优先云厂商的无锁变更工具或原生 Online DDL。
    7. 实施流程:评估表大小/行数/索引/主键/外键/触发器/分区;查长事务和慢查询;确认从库延迟、binlog 格式、磁盘/临时目录;备份;低峰执行;设置限流和告警;切表窗口;切后验证索引、计划、数据一致性、复制延迟;清理 ghost/old 表和触发器。
    8. 回滚与治理:gh-ost 可在 cut-over 前暂停/放弃,切表后保留旧表一段时间;pt-osc 用 --no-drop-old-table 保留旧表;原生 Online DDL 失败/回滚代价高,必须备份。长期靠索引设计评审、SQL 审核、未使用/冗余索引治理、在线变更平台和变更窗口防回归。
  • 图/流程:

text
方案决策:
  加索引/加列等操作
    ├─ 支持 ALGORITHM=INPLACE, LOCK=NONE?
    │    ├─ 是 → 原生 Online DDL(低峰 + 查长事务 + 监控)
    │    └─ 否 → 大表/高风险?
    │         ├─ 是 → gh-ost(无触发器、限流、可暂停)
    │         └─ 否 → pt-osc(成熟、触发器同步)
    └─ 云 RDS 权限受限 → 云厂商工具 / 原生 Online DDL
text
Online DDL 时间线:
  [开始] 拿 MDL 排他锁(短暂)
    → 建索引/重建(INPLACE,允许 DML)
    → DML 写入 online alter log
    → [结束] 再拿 MDL,应用日志,切换元数据
  长事务持有 MDL → DDL 等待 → 后续 DML 排队
text
gh-ost:
  1. 创建 _ghost 表(同结构 + 新索引)
  2. 按 chunk copy 老数据(可限流)
  3. 读 binlog,把增量应用到 _ghost
  4. 校验/追上进度
  5. RENAME TABLE 原子切换
  6. 保留旧表,观察后清理
text
pt-osc:
  1. 创建 _new 表(新结构)
  2. 创建 INSERT/UPDATE/DELETE 触发器
  3. 按 chunk copy 老数据
  4. 触发器同步增量
  5. RENAME TABLE 原子切换
  6. 删触发器/旧表(可保留)
text
风险监控:
  MDL 等待   → metadata_locks / sys.schema_table_lock_waits
  复制延迟   → SHOW REPLICA STATUS / Seconds_Behind_Source
  IO/CPU     → Threads_running / iostat / BP 命中
  磁盘       → 数据目录 / tmpdir / innodb_tmpdir
  业务错误   → 应用错误率 / 连接池 / 慢查询
  • 版本/源码锚点:

    • MySQL 5.6:Online DDL(INPLACE/LOCK=NONE);5.7 完善;8.0 增强 INSTANT、原子 DDL。
    • MySQL 8.0.12+:ALGORITHM=INSTANT 加列;后续版本扩展 instant 操作;加索引仍以 INPLACE 为主。
    • 关键参数:innodb_online_alter_log_max_size=128MB、innodb_tmpdir、tmpdir、lock_wait_timeout、innodb_sort_buffer_size、binlog_format=ROW、binlog_row_image=FULL。
    • 工具:gh-ost(GitHub)、pt-online-schema-change(Percona Toolkit)、pt-table-checksum、pt-table-sync。
    • 源码/实现:sql/sql_alter.cc(ALTER 流程)、sql/sql_table.cc(Online DDL/COPY)、storage/innobase/handler/ha_innodb.cc(in-place alter)、storage/innobase/row/row0merge.cc(索引构建)、sql/mdl.cc(MDL)。
  • Online DDL 操作矩阵(常见,具体以版本为准):

操作常见 ALGORITHM常见 LOCK说明
加二级索引INPLACENONE允许 DML;仍需短暂 MDL
删二级索引INPLACENONE元数据 + 标记删除
加列INSTANT(8.0.12+)/ INPLACENONE5.7 可能重建表
删列INPLACE/INSTANT(版本相关)NONE版本差异大
改列类型COPY/INPLACE可能 SHARED/EXCLUSIVE常重建表
改字符集COPY锁写重建表,高风险
加主键INPLACENONE/共享可能重建
改主键COPY锁写高风险
加全文索引INPLACESHARED版本/参数相关
加外键INPLACESHARED受 foreign_key_checks 影响
分区表加索引视版本视操作工具支持有限,优先原生
  • 三种方案对比:
方案原理优点缺点适用
Online DDLin-place 重建/建索引原生、简单、无需额外工具支持矩阵有限、MDL/日志上限、回滚难支持 LOCK=NONE 的操作
gh-ostghost 表 + binlog 增量无触发器、可限流/暂停、可从库迁移需 ROW binlog/权限、切表仍有 MDL大表、触发器多、要求可控
pt-oscghost 表 + 触发器增量成熟、兼容广、资料多触发器开销/冲突、外键复杂自建、无触发器冲突
  • 关键参数/选项:
方案参数/选项作用注意
Online DDLinnodb_online_alter_log_max_sizeDDL 期间 DML 日志上限默认 128MB,超限失败
Online DDLinnodb_tmpdir / tmpdir临时文件目录确保磁盘空间
Online DDLlock_wait_timeoutMDL 等待超时可设短一点快速失败
Online DDLinnodb_sort_buffer_size索引构建排序缓冲影响 IO/内存
gh-ost--max-load / --critical-load负载限流/中止按 Threads_running 等
gh-ost--chunk-size每批 copy 行数越小越平滑、越慢
gh-ost--throttle-control-replicas从库延迟限流保护复制
gh-ost--postpone-cut-over-flag-file延迟切表人工确认窗口
gh-ost--allow-on-master / --migrate-on-replica主库/从库迁移按架构选择
gh-ost--test-on-replica从库演练切表前停止
pt-osc--max-load / --critical-load负载限流/中止同上
pt-osc--chunk-size / --chunk-time每批大小/耗时控制平滑度
pt-osc--dry-run / --print演练/打印先看再执行
pt-osc--preserve-triggers保留已有触发器版本相关
pt-osc--alter-foreign-keys-method外键处理复杂,需评估
  • 实施检查清单:
检查项要确认什么工具/命令
表规模行数、数据量、索引数、分区information_schema.TABLES、SHOW TABLE STATUS
结构主键、外键、触发器、字符集SHOW CREATE TABLE、information_schema.TRIGGERS
长事务是否有未提交事务/慢查询INNODB_TRX、SHOW PROCESSLIST
复制从库延迟、binlog 格式SHOW REPLICA STATUS、@@binlog_format
磁盘数据目录/tmpdir 空间df -h、innodb_tmpdir
备份可恢复备份xtrabackup/mysqldump
低峰业务低峰窗口业务监控
限流gh-ost/pt-osc 负载阈值--max-load/--critical-load
监控MDL/延迟/IO/错误率Prometheus/Grafana
回滚旧表/备份/切换方案gh-ost 暂停、pt-osc 保留旧表
验证索引、计划、数据一致性SHOW INDEX、EXPLAIN、checksum
清理ghost/old 表、触发器观察后清理
  • 风险、监控与回滚:
风险监控缓解/回滚
MDL 等待metadata_locks、sys.schema_table_lock_waitskill 长事务、低峰、短 lock_wait_timeout
复制延迟Seconds_Behind_Source、SHOW REPLICA STATUS限流、从库迁移、暂停切表
磁盘满数据目录/tmpdir 使用率扩容、清理、分批
IO/CPU 抖动Threads_running、iostat、BP 命中限流、低峰、调 chunk
DDL 失败错误日志、SHOW WARNINGS调大 online log、改 gh-ost、恢复备份
切表闪断应用错误率、连接池低峰、重试、连接池预热
数据不一致checksum、行数、抽样pt-table-checksum、回滚旧表
  • 深挖点(面试官常追的 6 个):

    1. Online DDL 不是“零锁”:它允许并发 DML,但开始和结束都要短暂 MDL 排他锁;长事务持有 MDL 读锁时,DDL 会一直等,后续 DML 又排在 DDL 后面。执行前查 INNODB_TRX 比选低峰更重要。
    2. innodb_online_alter_log_max_size 是硬上限:DDL 期间的 DML 写入 online log,超过 128MB 默认值会失败;大表 + 高写入要把这个因素纳入方案,必要时改用 gh-ost/pt-osc 或低峰执行。
    3. gh-ost 与 pt-osc 的本质差异:gh-ost 读 binlog,无触发器;pt-osc 用触发器,兼容广但有额外写开销和触发器冲突。表上已有触发器、写入量大、要求可暂停/限流时,gh-ost 通常更合适;自建、无触发器冲突时 pt-osc 也够用。
    4. 复制延迟是切表前的硬门槛:DDL 在从库执行、大表 copy、binlog 量都会造成延迟;读写分离下从库延迟会直接影响业务。gh-ost 可用 --throttle-control-replicas 限流,pt-osc 用 --max-load/--critical-load,切表前必须确认追上。
    5. 切表仍是短暂阻塞点:RENAME TABLE 是原子元数据操作,但需要 MDL;长事务/慢查询会让切表等待,应用可能短暂报错。要在低峰切、准备重试,并确认连接池/预处理语句能恢复。
    6. 回滚方案要提前设计:gh-ost 可在 cut-over 前暂停/放弃,切表后保留旧表;pt-osc 用 --no-drop-old-table;原生 Online DDL 失败/回滚代价高,必须备份。没有回滚方案的变更,不算可上线方案。
  • 追问链:

    1. 千万级大表加索引怎么做? → 先评估操作是否支持 Online DDL;支持则 ALGORITHM=INPLACE, LOCK=NONE 低峰执行;不支持/风险高用 gh-ost/pt-osc;全程监控 MDL/延迟/IO,准备回滚。
    2. Online DDL 是什么? → MySQL 5.6+ 的在线变更能力,允许 DDL 期间并发 DML,减少锁表时间。
    3. ALGORITHM=INPLACE/COPY/INSTANT 区别? → INPLACE 尽量原地/重建索引不复制整表;COPY 建新表复制数据、锁写;INSTANT 只改元数据,速度最快但支持操作有限。
    4. LOCK=NONE 含义? → DDL 期间允许并发读写;SHARED 允许读不允许写;EXCLUSIVE 读写都阻塞。
    5. 加索引能 Online 吗? → InnoDB 加二级索引通常可以 INPLACE, LOCK=NONE;仍需短暂 MDL,且受版本/表结构影响。
    6. Online DDL 会锁表吗? → 会短暂拿 MDL;不是“完全无锁”,长事务会让等待放大。
    7. 为什么还会 MDL 等待? → 长事务/慢查询持有 MDL 读锁;DDL 要排他 MDL;DDL 排队后 DML 又排在后面。
    8. innodb_online_alter_log_max_size 是什么? → DDL 期间记录并发 DML 的日志上限;超限 DDL 失败。
    9. DDL 期间 DML 怎么处理? → Online DDL 写入 online log,结束后应用;gh-ost/pt-osc 通过 binlog/触发器同步到影子表。
    10. DDL 失败会怎样? → 可能回滚/留下临时文件;大表回滚耗时;要备份和重试方案。
    11. gh-ost 原理? → ghost 表 + 分批 copy + binlog 增量 + 原子 RENAME TABLE 切换。
    12. gh-ost 需要什么条件? → binlog_format=ROW、binlog_row_image=FULL、复制权限、足够的磁盘/连接。
    13. gh-ost 怎么限流? → --max-load/--critical-load、--chunk-size、--throttle-control-replicas、throttle flag file。
    14. gh-ost 切表怎么原子? → RENAME TABLE 原子交换新旧表;切表前要追上 binlog 并确认无长事务。
    15. gh-ost 能回滚吗? → cut-over 前可暂停/放弃;切表后保留旧表,必要时 rename 回去。
    16. pt-osc 原理? → shadow 表 + 触发器同步增量 + 分批 copy + RENAME TABLE。
    17. pt-osc 触发器开销? → 每次 DML 多执行触发器写入 shadow 表,增加写放大和锁竞争。
    18. pt-osc 外键/触发器限制? → 已有触发器可能冲突(--preserve-triggers);外键用 --alter-foreign-keys-method 评估。
    19. gh-ost 和 pt-osc 怎么选? → 大表/触发器多/要限流暂停选 gh-ost;自建/无触发器冲突/工具链熟选 pt-osc;能原生 Online DDL 优先原生。
    20. 云 RDS 怎么选? → 看权限(SUPER/触发器/复制);优先云厂商无锁变更工具或原生 Online DDL。
    21. 从库延迟怎么办? → 限流、降低 chunk、暂停切表、从库迁移、并行复制、错峰。
    22. 大表 DDL 磁盘空间? → 可能重建表/建索引/临时文件,需要额外空间;监控数据目录和 tmpdir。
    23. 临时文件在哪? → innodb_tmpdir(默认用 tmpdir);确保空间和 IO。
    24. 怎么监控 DDL? → metadata_locks、SHOW PROCESSLIST、SHOW REPLICA STATUS、Threads_running、磁盘/IO、应用错误率。
    25. 切表时应用会闪断吗? → 可能有短暂 MDL 阻塞/连接错误;低峰切、重试、连接池预热。
    26. 连接池/预处理语句影响? → 切表是元数据操作,通常连接不断;但短暂阻塞可能触发超时,准备重试。
    27. 怎么验证数据一致性? → 行数、checksum、pt-table-checksum、抽样查询、索引/计划验证。
    28. 怎么回滚? → gh-ost 暂停/保留旧表;pt-osc --no-drop-old-table;原生 DDL 靠备份恢复。
    29. 什么时候低峰? → 业务低峰 + 从库延迟低 + 无长事务;不是固定凌晨,要看业务曲线。
    30. 需要备份吗? → 需要;高风险变更前做可恢复备份,验证恢复流程。
    31. 主从切换? → 变更期间避免主从切换;先确认复制健康,切表后观察延迟。
    32. 大表加索引替代方案? → 索引设计评审、覆盖索引、分区、归档、读写分离、ES 异构;不是所有查询都要加索引。
    33. 索引设计怎么评审? → Top SQL、选择性、列序、覆盖、写放大、冗余;用 EXPLAIN/压测验证(4.1-6)。
    34. 为什么不用 pt-osc 的触发器? → 写放大、锁竞争、已有触发器冲突、外键复杂;gh-ost 用 binlog 避开。
    35. gh-ost 的 binlog 格式要求? → ROW;binlog_row_image=FULL,否则增量不完整。
    36. RENAME TABLE 是原子的吗? → 是原子元数据操作;但需要 MDL,可能等待。
    37. 外键表怎么加索引? → 评估外键约束、foreign_key_checks、工具支持;必要时先处理外键/用原生 DDL。
    38. 分区表加索引? → 优先原生 Online DDL;gh-ost/pt-osc 对分区表支持有限,逐分区/整体评估。
    39. 8.0 INSTANT 能加索引吗? → 不能;INSTANT 主要支持加列等元数据操作,加索引走 INPLACE。
    40. 变更平台? → Yearning/Archery/Bytebase/云 DMS 等,集成审核、执行、回滚、审计。
    41. 失败案例? → MDL 等待、复制延迟、磁盘满、online log 超限、切表阻塞、触发器冲突;事前检查 + 限流 + 回滚。
    42. 怎么防回归? → 索引评审、SQL 审核、监控告警、变更窗口、容量规划、定期治理。
  • 生产案例/取舍(示例,替换成你的项目):

    • 千万订单表加联合索引,原生 Online DDL 低峰执行;长事务导致 MDL 等待,kill 长事务后完成。
    • 高写入表 DDL 期间 online log 超 128MB 失败;改 gh-ost 限流迁移,切表成功。
    • 表上已有触发器,pt-osc 冲突;改用 gh-ost,无触发器同步。
    • gh-ost 从从库迁移,主库压力小;--throttle-control-replicas 控制延迟,切表前追上。
    • 切表时应用短暂报错;连接池重试 + 低峰切,错误率恢复。
    • 磁盘空间不足,DDL 失败;扩容 + 清理临时文件后重试。
    • 分区表加索引,工具不支持;用原生 Online DDL 逐分区评估。
    • 云 RDS 无 SUPER 权限,gh-ost 受限;用云厂商无锁变更工具。
    • 切表后保留旧表一周,观察无问题再清理;有回滚路径。
    • 变更前用 pt-table-checksum 校验,切后抽样验证数据一致。
    • 取舍:原生 Online DDL 简单但支持有限;gh-ost 可控但需 ROW binlog/权限;pt-osc 成熟但有触发器开销;低峰换风险,代价是窗口;限流换稳定,代价是时长。
  • 易错点:

    • 认为 Online DDL 完全不锁表。
    • 认为 LOCK=NONE 没有 MDL。
    • 忽略长事务导致的 MDL 等待。
    • 忽略从库延迟。
    • 忽略磁盘/临时目录空间。
    • 认为 gh-ost 不需要 ROW binlog。
    • 认为 pt-osc 没有触发器开销。
    • 认为切表一定无感。
    • 认为 DDL 失败回滚没有代价。
    • 认为大表加索引可以高峰做。
    • 认为 ALGORITHM=INSTANT 能加索引。
    • 认为 gh-ost 一定优于 pt-osc。
    • 认为 pt-osc 支持所有外键/触发器。
    • 忽略 innodb_online_alter_log_max_size。
    • 忽略 innodb_tmpdir/tmpdir。
    • 不备份就变更。
    • 不验证数据一致性。
    • 不监控 MDL/延迟/IO。
    • 不做回滚方案。
    • 认为云 RDS 权限和自建一样。
    • 认为加索引一定值得,忽略写放大/冗余。
  • 30 秒口述稿:

千万级大表加索引,先看能不能原生 Online DDL:加二级索引通常 ALGORITHM=INPLACE, LOCK=NONE,但开始/结束仍有短暂 MDL,长事务会让 DDL 排队并堵住后续 DML。大表或高风险场景用 gh-ost 或 pt-osc:gh-ost 是 ghost 表 + binlog 增量,无触发器、可限流暂停;pt-osc 是 ghost 表 + 触发器增量,成熟但有触发器开销。无论哪种都要低峰、查长事务、看从库延迟、留磁盘、做备份、监控 MDL/IO,并准备切表回滚。

  • 3 分钟展开顺序:结论(控制 MDL/延迟/磁盘/回滚)→ Online DDL 支持矩阵与原理 → MDL/online log 风险 → gh-ost 流程与条件 → pt-osc 流程与限制 → 方案选择 → 实施检查清单 → 监控/回滚 → 生产案例与取舍。