Appearance
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 的“影子表 + 增量同步 + 原子切表”;无论哪种都要低峰、限流、监控、可回滚,并先在从库/影子库演练。分层要点:
- 先判断能不能 Online DDL:MySQL 5.6+ 支持 Online DDL;加二级索引通常是
ALGORITHM=INPLACE, LOCK=NONE,允许并发 DML,但开始/结束仍要短暂 MDL 排他锁。加列在 8.0.12+ 可ALGORITHM=INSTANT;改列类型、改字符集、改主键等可能只能COPY,会锁写、重建表。操作支持矩阵要以官方文档和SHOW WARNINGS为准,不能背口诀。 - Online DDL 的原理与代价:
INPLACE尽量不复制整表,但加索引仍要读表、排序、写索引;DDL 期间的 DML 记录到innodb_online_alter_log_max_size(默认 128MB)的日志里,超限 DDL 失败;临时文件用innodb_tmpdir/tmpdir;会消耗 IO、CPU、磁盘和 BP。 - MDL 是最大暗坑:Online DDL 开始和结束都要拿 MDL 排他锁;长事务、未提交事务、慢查询持有 MDL 读锁时,DDL 会等待;DDL 一旦排队,后续 DML 又排在 DDL 后面,形成“MDL 等待风暴”。低峰只是降低概率,执行前必须查长事务。
- gh-ost 原理:创建 ghost 表(同结构 + 新索引),按 chunk 逐批 copy 老数据,同时通过 binlog(ROW 格式)持续把增量应用到 ghost 表;最后用
RENAME TABLE原子切换。无触发器、可限流、可暂停、支持在从库迁移;需要binlog_format=ROW、binlog_row_image=FULL、复制权限。 - pt-osc 原理:
pt-online-schema-change创建 shadow 表,用触发器同步INSERT/UPDATE/DELETE增量,逐批 copy 老数据,最后RENAME TABLE切换。成熟、兼容广,但触发器有性能开销,已有触发器/外键场景复杂,需要触发器权限。 - 方案选择:能原生 Online DDL 且支持
LOCK=NONE的,优先原生;大表、长事务多、触发器多、需要限流/暂停/从库迁移的,用 gh-ost;自建、无触发器冲突、习惯 Percona 工具链的,用 pt-osc。云 RDS 可能限制 SUPER/触发器/复制权限,优先云厂商的无锁变更工具或原生 Online DDL。 - 实施流程:评估表大小/行数/索引/主键/外键/触发器/分区;查长事务和慢查询;确认从库延迟、binlog 格式、磁盘/临时目录;备份;低峰执行;设置限流和告警;切表窗口;切后验证索引、计划、数据一致性、复制延迟;清理 ghost/old 表和触发器。
- 回滚与治理:gh-ost 可在 cut-over 前暂停/放弃,切表后保留旧表一段时间;pt-osc 用
--no-drop-old-table保留旧表;原生 Online DDL 失败/回滚代价高,必须备份。长期靠索引设计评审、SQL 审核、未使用/冗余索引治理、在线变更平台和变更窗口防回归。
- 先判断能不能 Online DDL:MySQL 5.6+ 支持 Online DDL;加二级索引通常是
图/流程:
text
方案决策:
加索引/加列等操作
├─ 支持 ALGORITHM=INPLACE, LOCK=NONE?
│ ├─ 是 → 原生 Online DDL(低峰 + 查长事务 + 监控)
│ └─ 否 → 大表/高风险?
│ ├─ 是 → gh-ost(无触发器、限流、可暂停)
│ └─ 否 → pt-osc(成熟、触发器同步)
└─ 云 RDS 权限受限 → 云厂商工具 / 原生 Online DDLtext
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 | 说明 |
|---|---|---|---|
| 加二级索引 | INPLACE | NONE | 允许 DML;仍需短暂 MDL |
| 删二级索引 | INPLACE | NONE | 元数据 + 标记删除 |
| 加列 | INSTANT(8.0.12+)/ INPLACE | NONE | 5.7 可能重建表 |
| 删列 | INPLACE/INSTANT(版本相关) | NONE | 版本差异大 |
| 改列类型 | COPY/INPLACE | 可能 SHARED/EXCLUSIVE | 常重建表 |
| 改字符集 | COPY | 锁写 | 重建表,高风险 |
| 加主键 | INPLACE | NONE/共享 | 可能重建 |
| 改主键 | COPY | 锁写 | 高风险 |
| 加全文索引 | INPLACE | SHARED | 版本/参数相关 |
| 加外键 | INPLACE | SHARED | 受 foreign_key_checks 影响 |
| 分区表加索引 | 视版本 | 视操作 | 工具支持有限,优先原生 |
- 三种方案对比:
| 方案 | 原理 | 优点 | 缺点 | 适用 |
|---|---|---|---|---|
| Online DDL | in-place 重建/建索引 | 原生、简单、无需额外工具 | 支持矩阵有限、MDL/日志上限、回滚难 | 支持 LOCK=NONE 的操作 |
| gh-ost | ghost 表 + binlog 增量 | 无触发器、可限流/暂停、可从库迁移 | 需 ROW binlog/权限、切表仍有 MDL | 大表、触发器多、要求可控 |
| pt-osc | ghost 表 + 触发器增量 | 成熟、兼容广、资料多 | 触发器开销/冲突、外键复杂 | 自建、无触发器冲突 |
- 关键参数/选项:
| 方案 | 参数/选项 | 作用 | 注意 |
|---|---|---|---|
| Online DDL | innodb_online_alter_log_max_size | DDL 期间 DML 日志上限 | 默认 128MB,超限失败 |
| Online DDL | innodb_tmpdir / tmpdir | 临时文件目录 | 确保磁盘空间 |
| Online DDL | lock_wait_timeout | MDL 等待超时 | 可设短一点快速失败 |
| Online DDL | innodb_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_waits | kill 长事务、低峰、短 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 个):
- Online DDL 不是“零锁”:它允许并发 DML,但开始和结束都要短暂 MDL 排他锁;长事务持有 MDL 读锁时,DDL 会一直等,后续 DML 又排在 DDL 后面。执行前查
INNODB_TRX比选低峰更重要。 innodb_online_alter_log_max_size是硬上限:DDL 期间的 DML 写入 online log,超过 128MB 默认值会失败;大表 + 高写入要把这个因素纳入方案,必要时改用 gh-ost/pt-osc 或低峰执行。- gh-ost 与 pt-osc 的本质差异:gh-ost 读 binlog,无触发器;pt-osc 用触发器,兼容广但有额外写开销和触发器冲突。表上已有触发器、写入量大、要求可暂停/限流时,gh-ost 通常更合适;自建、无触发器冲突时 pt-osc 也够用。
- 复制延迟是切表前的硬门槛:DDL 在从库执行、大表 copy、binlog 量都会造成延迟;读写分离下从库延迟会直接影响业务。gh-ost 可用
--throttle-control-replicas限流,pt-osc 用--max-load/--critical-load,切表前必须确认追上。 - 切表仍是短暂阻塞点:
RENAME TABLE是原子元数据操作,但需要 MDL;长事务/慢查询会让切表等待,应用可能短暂报错。要在低峰切、准备重试,并确认连接池/预处理语句能恢复。 - 回滚方案要提前设计:gh-ost 可在 cut-over 前暂停/放弃,切表后保留旧表;pt-osc 用
--no-drop-old-table;原生 Online DDL 失败/回滚代价高,必须备份。没有回滚方案的变更,不算可上线方案。
- Online DDL 不是“零锁”:它允许并发 DML,但开始和结束都要短暂 MDL 排他锁;长事务持有 MDL 读锁时,DDL 会一直等,后续 DML 又排在 DDL 后面。执行前查
追问链:
- 千万级大表加索引怎么做? → 先评估操作是否支持 Online DDL;支持则
ALGORITHM=INPLACE, LOCK=NONE低峰执行;不支持/风险高用 gh-ost/pt-osc;全程监控 MDL/延迟/IO,准备回滚。 - Online DDL 是什么? → MySQL 5.6+ 的在线变更能力,允许 DDL 期间并发 DML,减少锁表时间。
ALGORITHM=INPLACE/COPY/INSTANT区别? → INPLACE 尽量原地/重建索引不复制整表;COPY 建新表复制数据、锁写;INSTANT 只改元数据,速度最快但支持操作有限。LOCK=NONE含义? → DDL 期间允许并发读写;SHARED允许读不允许写;EXCLUSIVE读写都阻塞。- 加索引能 Online 吗? → InnoDB 加二级索引通常可以
INPLACE, LOCK=NONE;仍需短暂 MDL,且受版本/表结构影响。 - Online DDL 会锁表吗? → 会短暂拿 MDL;不是“完全无锁”,长事务会让等待放大。
- 为什么还会 MDL 等待? → 长事务/慢查询持有 MDL 读锁;DDL 要排他 MDL;DDL 排队后 DML 又排在后面。
innodb_online_alter_log_max_size是什么? → DDL 期间记录并发 DML 的日志上限;超限 DDL 失败。- DDL 期间 DML 怎么处理? → Online DDL 写入 online log,结束后应用;gh-ost/pt-osc 通过 binlog/触发器同步到影子表。
- DDL 失败会怎样? → 可能回滚/留下临时文件;大表回滚耗时;要备份和重试方案。
- gh-ost 原理? → ghost 表 + 分批 copy + binlog 增量 + 原子
RENAME TABLE切换。 - gh-ost 需要什么条件? →
binlog_format=ROW、binlog_row_image=FULL、复制权限、足够的磁盘/连接。 - gh-ost 怎么限流? →
--max-load/--critical-load、--chunk-size、--throttle-control-replicas、throttle flag file。 - gh-ost 切表怎么原子? →
RENAME TABLE原子交换新旧表;切表前要追上 binlog 并确认无长事务。 - gh-ost 能回滚吗? → cut-over 前可暂停/放弃;切表后保留旧表,必要时 rename 回去。
- pt-osc 原理? → shadow 表 + 触发器同步增量 + 分批 copy +
RENAME TABLE。 - pt-osc 触发器开销? → 每次 DML 多执行触发器写入 shadow 表,增加写放大和锁竞争。
- pt-osc 外键/触发器限制? → 已有触发器可能冲突(
--preserve-triggers);外键用--alter-foreign-keys-method评估。 - gh-ost 和 pt-osc 怎么选? → 大表/触发器多/要限流暂停选 gh-ost;自建/无触发器冲突/工具链熟选 pt-osc;能原生 Online DDL 优先原生。
- 云 RDS 怎么选? → 看权限(SUPER/触发器/复制);优先云厂商无锁变更工具或原生 Online DDL。
- 从库延迟怎么办? → 限流、降低 chunk、暂停切表、从库迁移、并行复制、错峰。
- 大表 DDL 磁盘空间? → 可能重建表/建索引/临时文件,需要额外空间;监控数据目录和 tmpdir。
- 临时文件在哪? →
innodb_tmpdir(默认用tmpdir);确保空间和 IO。 - 怎么监控 DDL? →
metadata_locks、SHOW PROCESSLIST、SHOW REPLICA STATUS、Threads_running、磁盘/IO、应用错误率。 - 切表时应用会闪断吗? → 可能有短暂 MDL 阻塞/连接错误;低峰切、重试、连接池预热。
- 连接池/预处理语句影响? → 切表是元数据操作,通常连接不断;但短暂阻塞可能触发超时,准备重试。
- 怎么验证数据一致性? → 行数、checksum、
pt-table-checksum、抽样查询、索引/计划验证。 - 怎么回滚? → gh-ost 暂停/保留旧表;pt-osc
--no-drop-old-table;原生 DDL 靠备份恢复。 - 什么时候低峰? → 业务低峰 + 从库延迟低 + 无长事务;不是固定凌晨,要看业务曲线。
- 需要备份吗? → 需要;高风险变更前做可恢复备份,验证恢复流程。
- 主从切换? → 变更期间避免主从切换;先确认复制健康,切表后观察延迟。
- 大表加索引替代方案? → 索引设计评审、覆盖索引、分区、归档、读写分离、ES 异构;不是所有查询都要加索引。
- 索引设计怎么评审? → Top SQL、选择性、列序、覆盖、写放大、冗余;用
EXPLAIN/压测验证(4.1-6)。 - 为什么不用 pt-osc 的触发器? → 写放大、锁竞争、已有触发器冲突、外键复杂;gh-ost 用 binlog 避开。
- gh-ost 的 binlog 格式要求? → ROW;
binlog_row_image=FULL,否则增量不完整。 RENAME TABLE是原子的吗? → 是原子元数据操作;但需要 MDL,可能等待。- 外键表怎么加索引? → 评估外键约束、
foreign_key_checks、工具支持;必要时先处理外键/用原生 DDL。 - 分区表加索引? → 优先原生 Online DDL;gh-ost/pt-osc 对分区表支持有限,逐分区/整体评估。
- 8.0 INSTANT 能加索引吗? → 不能;INSTANT 主要支持加列等元数据操作,加索引走 INPLACE。
- 变更平台? → Yearning/Archery/Bytebase/云 DMS 等,集成审核、执行、回滚、审计。
- 失败案例? → MDL 等待、复制延迟、磁盘满、online log 超限、切表阻塞、触发器冲突;事前检查 + 限流 + 回滚。
- 怎么防回归? → 索引评审、SQL 审核、监控告警、变更窗口、容量规划、定期治理。
- 千万级大表加索引怎么做? → 先评估操作是否支持 Online DDL;支持则
生产案例/取舍(示例,替换成你的项目):
- 千万订单表加联合索引,原生 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 流程与限制 → 方案选择 → 实施检查清单 → 监控/回滚 → 生产案例与取舍。