个人主页 for_ever_love__ 欢迎各位大佬莅临其他栏目: 大模型开发从0到1 其他栏目: iOS项目总结大全 其他栏目: 我想学python了 其他栏目: iOS UI 文章目录MySQL Online DDL 完全指南INSTANT/INPLACE/COPY 三种算法、MDL 锁事故与 gh-ost 实战一、先把Online这个词掰清楚二、三种算法的执行过程2.1 COPY最原始也最危险2.2 INPLACE原地改执行阶段放行 DML2.3 INSTANT只改元数据8.0 的救命特性三、一张表看懂常见 DDL 走哪条路四、MDL 锁Online DDL 事故的第一杀手4.1 一次典型的雪崩4.2 执行前必做的检查4.3 让 DDL 自己别变成塞子五、INPLACE 期间的两个隐藏上限5.1 row log 溢出5.2 磁盘与临时目录5.3 8.0 给大表 DDL 的性能开关六、什么时候该上 gh-ost / pt-osc6.1 两个工具的原理差异6.2 对比选型6.3 gh-ost 实战6.4 pt-osc 的写法七、一套可复制的 DDL 操作流程八、常见误区小结MySQL Online DDL 完全指南INSTANT/INPLACE/COPY 三种算法、MDL 锁事故与 gh-ost 实战“MySQL 不是支持 Online DDL 吗为什么我加个字段业务就雪崩了”这是 DBA 日常被问得最多的问题之一。真相是Online DDL 说的是执行期间不阻塞 DML但它没说拿锁的时候不阻塞。而真正的生产事故几乎都发生在那拿锁的几秒里。这篇把三种算法、MDL 锁、inplace 的 row log 上限、以及 gh-ost / pt-osc 怎么选一次讲透。一、先把Online这个词掰清楚业界口语里的 “Online DDL” 其实混了三件事必须先分开概念含义层级ALGORITHMCOPY建临时表、逐行拷数据、再 rename执行方式ALGORITHMINPLACE原地操作可能重建表执行期间允许 DML执行方式ALGORITHMINSTANT只改数据字典元数据秒级完成8.0.12执行方式LOCKNONE / SHARED / EXCLUSIVE执行期间允许什么级别的并发并发度INPLACE ≠ 不锁表。INPLACE 只说明不拷贝整表数据它在准备阶段和提交阶段依然要拿排他 MDL 锁。真正决定能不能写的是LOCK子句LOCKNONE才表示全程可读写。语法长这样ALTERTABLEordersADDCOLUMNchannelVARCHAR(32)NOTNULLDEFAULTCOMMENT下单渠道,ALGORITHMINPLACE,LOCKNONE;⚠️生产上不建议硬写ALGORITHM和LOCK。MySQL 不指定时会按 INSTANT → INPLACE → COPY 自动挑最优的你一旦硬指定本来能 INPLACE 的被你指定成INSTANT会直接报错指定成COPY就是一场事故。让 MySQL 自己选才是默认正确的做法。硬指定的价值在于验证写ALGORITHMINSTANT能跑通就说明它确实是 instant 的。二、三种算法的执行过程2.1 COPY最原始也最危险准备阶段 ─ 拿排他 MDL → 建临时表server 层 create like 执行阶段 ─ 逐行 copy 数据到临时表全程持排他锁DML 全部阻塞 提交阶段 ─ rename 临时表为原表名 → 删原表 → 释放锁特征执行期间只读、写全阻塞需要约1 倍表大小的额外磁盘8.0 里仍有一批操作只能走它。2.2 INPLACE原地改执行阶段放行 DML准备阶段 ─ 共享锁升级为排他 MDL → 判断是否 rebuild → 若 rebuild 则申请 row log 空间 执行阶段 ─ 降级为共享 MDLDML 放行→ 扫描/重建数据 → 并发 DML 记入 row log 提交阶段 ─ 再次升级为排他 MDL → 回放 row log → rename → 释放锁关键点执行阶段不阻塞 DML但首尾各有一小段排他锁窗口rebuild 类操作加主键、删列、改数据类型虽是 INPLACE 却要重建整表耗时与 COPY 同量级no-rebuild 类操作加/删二级索引、改默认值、改名只动元数据或局部数据很快。判断是哪种看ALTER返回的rows affected。0 rows affected 说明没拷数据有行数说明重建了表。2.3 INSTANT只改元数据8.0 的救命特性8.0.12 引入由腾讯互娱 DBA 团队贡献只修改数据字典不动数据页不加排他 MDL秒级完成。8.0.12 起支持在表末尾追加列新增 / 删除虚拟列设置 / 删除列默认值修改 ENUM / SET 定义往后追加值修改索引类型、表重命名8.0.29 起进一步增强支持在任意位置instant 加列以及 instant删列。限制要记住限制说明行格式不支持ROW_FORMATCOMPRESSED的表全文索引表上有 FULLTEXT 索引时不支持 instant 加列累计次数同一张表 instant 加列有累计上限超了需要重建看INFORMATION_SCHEMA.INNODB_TABLES.INSTANT_COLS版本8.0.12 之前完全没有这个能力5.7 加列一定是 INPLACE rebuild很慢-- 验证是不是真的 instantALTERTABLEtADDCOLUMNext JSONDEFAULTNULL,ALGORITHMINSTANT;-- 不报错就说明是 instant报 ERROR 1846 就说明不支持得换方案三、一张表看懂常见 DDL 走哪条路基于 MySQL 8.0InnoDB操作INSTANTINPLACE重建表可并行 DML只改元数据加二级索引❌✅❌✅❌删二级索引❌✅❌✅✅索引重命名❌✅❌✅✅修改索引可见性❌✅❌✅✅加主键❌✅✅✅❌删主键❌❌✅❌❌删并同时加主键❌✅✅✅❌末尾加列✅ 8.0.12✅❌✅✅任意位置加列✅ 8.0.29✅✅✅❌删列✅ 8.0.29✅✅✅❌修改列数据类型❌❌✅❌❌扩展 VARCHAR 长度❌✅❌✅✅≤255 或同档位列重命名❌✅❌✅✅改列默认值✅✅❌✅✅加 STORED 虚拟列❌❌✅❌❌加 VIRTUAL 虚拟列✅✅❌✅✅转换表字符集❌❌✅❌❌OPTIMIZE TABLE❌✅✅✅❌表重命名✅✅❌✅✅几个值得单独拎出来的点VARCHAR 扩长度VARCHAR(50) → VARCHAR(100)这种255 以内的变化是纯元数据操作秒完但VARCHAR(100) → VARCHAR(300)跨过了字节数档位1 字节长度前缀 → 2 字节就要重建表。加自增列 / 把列改成自增会锁表并重建属于高危操作。第一个全文索引5.6 会自动补FTS_DOC_ID从而避免 COPY但仍会重建表且阻塞写。带 FULLTEXT 索引的表做OPTIMIZE TABLE/ALTER TABLE ... ENGINEInnoDB退化成 COPY 并阻塞写。四、MDL 锁Online DDL 事故的第一杀手4.1 一次典型的雪崩-- 会话 A一个跑了 40 分钟还没提交的慢事务或忘了 COMMIT 的手工操作BEGIN;SELECT*FROMordersWHEREid1;-- 事务没提交一直持有 MDL 共享锁-- 会话 BDBA 加字段ALTERTABLEordersADDCOLUMNchannelVARCHAR(32)DEFAULT;-- INPLACE看上去很安全但准备阶段要拿排他 MDL → 卡住等待 A 释放-- 会话 C/D/E……所有业务 SQLSELECT*FROMordersWHEREuser_id100;-- 全部卡住为什么会连锁MySQL 的 MDL 获取是排队的。B 的排他锁请求排在 A 后面而 C 之后的共享锁请求又排在 B 后面。于是明明只读的 SELECT 也被阻塞连接数迅速打满业务雪崩。这才是Online DDL 造成事故的真实机制——不是 DDL 本身慢而是它变成了队列里的一个塞子。4.2 执行前必做的检查-- 1. 有没有长事务重点关注 trx_started 很早的SELECTtrx_id,trx_state,trx_started,trx_mysql_thread_idASconn_id,trx_queryFROMinformation_schema.innodb_trxWHERETIMESTAMPDIFF(SECOND,trx_started,NOW())60ORDERBYtrx_started;-- 2. 谁在等 MDL需先开启 instrumentUPDATEperformance_schema.setup_instrumentsSETENABLEDYES,TIMEDYESWHERENAMEwait/lock/metadata/sql/mdl;SELECTOBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_STATUS,OWNER_THREAD_IDFROMperformance_schema.metadata_locksWHEREOBJECT_TYPETABLE;-- 3. 当前连接都在干什么SELECTid,user,db,command,time,state,LEFT(info,100)ASsql_textFROMinformation_schema.PROCESSLISTWHEREdbyour_dbANDcommandSleepORDERBYtimeDESC;确认没有长事务和慢查询之后再动手。4.3 让 DDL 自己别变成塞子-- 给 DDL 设一个短的锁等待超时拿不到锁就快速失败不要堵住后面的人SETSESSIONlock_wait_timeout10;ALTERTABLEordersADDCOLUMNchannelVARCHAR(32)DEFAULT;-- MySQL 8.0 还可以直接写在语句上ALTERTABLEorders NOWAITADDCOLUMNchannelVARCHAR(32)DEFAULT;ALTERTABLEorders WAIT10ADDCOLUMNchannelVARCHAR(32)DEFAULT;快速失败比默默等待安全得多失败了你重试就行等待会把整个库拖挂。五、INPLACE 期间的两个隐藏上限5.1 row log 溢出INPLACE 执行期间并发 DML 会记到一块叫row log的区域里最后回放。这块区域的大小由innodb_online_alter_log_max_size控制默认 128MB。如果表很大、写入很猛、DDL 又跑得久就会撞上ERROR 1799 (HY000): Creating index idx_xxx required more than innodb_online_alter_log_max_size bytes of modification log后果是DDL 直接失败回滚前面跑的几个小时白费。应对SETGLOBALinnodb_online_alter_log_max_size1073741824;-- 临时调到 1G但更根本的办法是在业务低峰做或者直接用 gh-ost它没有这个限制。5.2 磁盘与临时目录INPLACE rebuild 需要约等于表大小的额外空间建二级索引时的排序会在临时目录产生大文件tmpdir所在分区要够大8.0 可以用innodb_tmpdir单独给 DDL 指定临时目录SHOWVARIABLESLIKEinnodb_tmpdir;SHOWVARIABLESLIKEtmpdir;SHOWVARIABLESLIKEinnodb_online_alter_log_max_size;5.3 8.0 给大表 DDL 的性能开关参数版本作用innodb_ddl_threads8.0.27并行创建二级索引的线程数innodb_ddl_buffer_size8.0.27并行建索引时的 buffer 大小innodb_parallel_read_threads8.0.14并行扫描聚簇索引加速建表/校验-- 大表加索引前临时调大会话级即可SETSESSIONinnodb_ddl_threads8;SETSESSIONinnodb_ddl_buffer_size8388608;ALTERTABLEbig_tableADDINDEXidx_created(created_at);六、什么时候该上 gh-ost / pt-osc先说结论除了 8.0 上确认是 INSTANT 的那些操作其余大表 DDL 都建议用工具做。原因很简单原生 Online DDL 有几个绕不过去的问题无法真正限速一旦开始就是全速跑IO 和 CPU 打满你只能 kill而 kill 大 DDL 的回滚代价极高。无法暂停发现主库扛不住了停不下来。主从延迟DDL 在主库跑完后从库要串行回放同一个 DDL大表会造成严重延迟8.0 的并行复制对 DDL 帮助有限。row log 溢出风险。6.1 两个工具的原理差异pt-online-schema-changePercona1. 建新表 _tbl_new新结构 2. 在原表上建 3 个触发器INSERT / UPDATE / DELETE把增量同步到 _tbl_new 3. 按 chunk 拷贝存量数据 4. RENAME TABLE 切换gh-ostGitHub1. 建幽灵表 _tbl_gho新结构 变更记录表 _tbl_ghc 2. 伪装成一个 slave拉取 ROW 格式的 binlog 获取增量 3. 按 chunk 拷贝存量数据INSERT IGNORE ... SELECT 4. 持续 apply binlog 增量 5. cut-over短暂拿一次表锁原子 RENAME 切换核心区别就一句话pt-osc 用触发器捕获增量gh-ost 用 binlog 捕获增量。6.2 对比选型维度pt-online-schema-changegh-ost增量捕获触发器ROW 格式 binlog对主库额外负载触发器同步执行开销大实测约 12% 吞吐下降异步读 binlog开销极小接近 0能否限速 / 暂停支持但粒度粗✅ 非常灵活chunk-size、max-load、nice-ratio、throttle 文件已有触发器❌ 冲突不支持✅ 不受影响外键需要--alter-foreign-keys-method额外处理❌ 不支持有外键的表需要主键/唯一键需要需要用于 chunk 切分和行定位binlog 要求无特殊要求必须 ROW 格式且binlog_row_imageFULL追增量能力触发器并行能扛高写入单线程 apply binlog超高写入下可能永远追不上可测试性一般✅--test-on-replica先在从库演练写入停顿切换瞬间cut-over 瞬间有毫秒到秒级锁等待空负载下的速度更快约 gh-ost 的两倍稍慢⚠️gh-ost 那个追不上的坑是真实的社区 benchmark 显示在 25% 满负载写入下gh-ost 因为单线程回放 binlog 而永远无法完成迁移。所以高写入表用 gh-ost 前先观察它的Backlog指标如果持续打满 100/100 且Applied增长缓慢就要调小chunk-size或改在低峰执行。6.3 gh-ost 实战# 最简执行gh-ost --host127.0.0.1 --userdba --password*** --databaseshop \# --tableorders --alterADD COLUMN channel VARCHAR(32) NOT NULL DEFAULT \# --allow-on-master --execute# 生产推荐带限速、带阈值、可随时暂停gh-ost\--host127.0.0.1--userdba--password***\--databaseshop--tableorders\--alterADD INDEX idx_created (created_at)\--allow-on-master\--chunk-size1000\--max-lag-millis1500\--max-loadThreads_running50\--critical-loadThreads_running200\--throttle-flag-file/tmp/gh-ost.throttle\--throttle-querySELECT IF(HOUR(NOW()) BETWEEN 9 AND 21, 1, 0)\--nice-ratio0.5\--serve-socket-file/tmp/gh-ost.orders.sock\--execute运行时的输出要看懂Copy: 3200000/9872432 32.4%; Applied: 15840; Backlog: 3/100; Time: 12m30s(total), 12m20s(copy); streamer: mysql-bin.000018:641578191; State: migrating; ETA: 26m10sCopy— 存量数据拷贝进度Applied— 已回放的 binlog 事件数Backlog—待回放堆积量。持续 100/100 说明跟不上要降速State—migrating正常throttled说明被你限流了postponing cut-over说明在等切换时机运行时可动态控制touch /tmp/gh-ost.throttle暂停rm恢复也可以通过 socket 在线改参数echo chunk-size500 | nc -U /tmp/gh-ost.orders.sock。先演练再上线--test-on-replica会在从库上完整跑一遍但不切换用来验证 SQL 正确性和耗时。6.4 pt-osc 的写法pt-online-schema-change--host127.0.0.1--userdba--password***\Dshop,torders\--alterADD COLUMN channel VARCHAR(32) NOT NULL DEFAULT \--chunk-size1000--max-lag1.5--check-interval1\--recursion-methodprocesslist --no-check-replication-filters--execute外键表要额外指定--alter-foreign-keys-methodrebuild_constraints或drop_swap更快但有短暂窗口。七、一套可复制的 DDL 操作流程分类先用ALGORITHMINSTANT在测试库试一下判断它是 INSTANT / INPLACE / COPY 中的哪一类。INSTANT→ 直接执行但仍避开业务高峰。小表 100MB→ 原生 Online DDL 直接跑几秒钟的事。大表→ 上 gh-ost / pt-osc。执行前检查有没有长事务 / 慢查询、磁盘剩余空间够不够至少 1 倍表大小、主从延迟是否健康、innodb_online_alter_log_max_size是否要临时调大。执行中监控主从延迟、Threads_running、磁盘 IO、gh-ost 的 Backlog。执行后清理gh-ost 会留下_tbl_old旧表和_tbl_ghc确认无误后再DROP并ANALYZE TABLE刷新统计信息。ANALYZETABLEorders;DROPTABLEIFEXISTS_orders_ghc;DROPTABLEIFEXISTS_orders_old;-- 确认新结构没问题之后再删八、常见误区误区 1INPLACE 就不锁表。——INPLACE 在 prepare 和 commit 各要一次排他 MDL真正的杀手是排队效应。误区 2加个字段而已很快。——8.0.12 之前加列是 INPLACE 重建整表5000 万行要跑几十分钟到几小时。这就是 INSTANT ADD COLUMN 被称为救命特性的原因。误区 3DDL 卡住了就 kill 掉。——大 DDL 的回滚可能比执行还慢且期间锁一直不释放。正确做法是先找源头长事务。误区 4gh-ost 比 pt-osc 快。——空负载下 pt-osc 通常更快gh-ost 的优势是可控、低侵入、可暂停不是绝对速度高写入下甚至可能永远跑不完。误区 5Online DDL 可以在高峰期随便做。——黄金准则DDL 永远不要在业务高峰期执行哪怕它是 INSTANT。误区 68.0 了就不需要 gh-ost。——INSTANT 只覆盖加列 / 删列 / 改默认值 / 加虚拟列这一小撮改数据类型、转字符集、删主键、加全文索引照样是重活。小结Online DDL 不等于不锁表INPLACE 在准备和提交阶段各要一次排他 MDL真正的风险是MDL 排队导致的连锁阻塞三种算法COPY拷数据、阻塞写、INPLACE原地、执行期放行、INSTANT8.0.12 只改元数据秒级不要硬写ALGORITHM/LOCK让 MySQL 自动选硬指定只用于验证8.0.29 起 instant 支持任意位置加列和删列COMPRESSED 行格式、有全文索引的表不支持动手前必查长事务和 MDL 等待并给 DDL 设置lock_wait_timeout让它快速失败INPLACE 的row log 上限默认 128MB大表要调innodb_online_alter_log_max_size8.0.27 可用innodb_ddl_threads/innodb_ddl_buffer_size加速建索引大表 DDL 用工具pt-osc 靠触发器更快但侵入大gh-ost 靠 binlog低侵入、可限速可暂停但单线程追增量有上限gh-ost 要求ROW 格式 binlog binlog_row_imageFULL且不支持有外键的表上线前用--test-on-replica演练运行时盯住Backlog指标最后一条DDL 永远不要在业务高峰期执行下一篇聊备份与恢复——DDL 能改回来的前提是你有一份能用的备份。