背景

DDL（Data Definition Languages）语句：数据定义语⾔，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常⽤的语句关键字主要包括 create、drop、alter等。
在 mysql 5.5 版本以前，修改表结构如添加索引、修改列，需要锁表，期间不能写⼊，导致库上⼤量线程处于“Waiting for meta data lock”状态。

有⼀系列⼯具从⼏个⻆度解决这个问题。按历史的发展⻆度，可以归纳为

ddl
mysql 原⽣ online DDL
pt-online-schema-change
gh-ost

旧版innodb DDL

5.5版本以前，执⾏ddl主要有两种⽅式copy⽅式和inplace⽅式，inplace⽅式⼜称为(fast indexcreation)。相对于copy⽅式，inplace⽅式不拷⻉数据，因此较快。但是这种⽅式仅⽀持添加、删除⾮主键索引两种⽅式，⽽且与copy⽅式⼀样需要全程锁表，实⽤性不是很强。下⾯以加索引为例，简单介绍这两种⽅式的实现流程。

copy⽅式

新建带索引的临时表
锁原表，禁⽌DML，允许查询
将原表数据拷⻉到临时表(⽆排序，⼀⾏⼀⾏拷⻉)
进⾏rename，升级字典锁，禁⽌读写
完成创建索引操作

inplace⽅式

新建索引的数据字典
锁表，禁⽌DML，允许查询
读取聚集索引，构造新的索引项，排序并插⼊新索引
等待当前表所有已开始的只读事务提交
创建索引结束

inplace创建⼆级索引时会对原表加上⼀个共享锁，创建过程不需要重建表（no-rebuild）；删除InnoDB⼆级索引只需要更新内部视图，并标记这个索引的物理空间可⽤，去掉数据库元数据上该索引的定义即可。

原生online DDL

配置

FIC只对索引的创建删除有效，MySQL 5.6 Online DDL把这种特性扩展到了添加列、删除列、修改列类型、列重命名、设置默认值等等，实际效果要看所使⽤的选项和操作类别来定。MySQL 在线DDL同样分为 INPLACE 和 COPY 两种⽅式。

对于copy⽅式，⽐如修改列类型，删除主键，修改字符集等场景，这些操作都会导致记录格式发⽣变化，⽆法通过简单的全量+增量的⽅式实现online
对于inplace⽅式，mysql内部以“是否修改记录格式”为基准也分为两类，⼀类需要重建表(重新组织记录) ，⽐如optimize table、添加索引、添加/删除列、修改列NULL/NOT NULL属性等；另外
⼀类是只需要修改表的元数据，⽐如删除索引、修改列名、修改列默认值、修改列⾃增值等。

Mysql将这两类⽅式分别称为 rebuild ⽅式和 no-rebuild ⽅式。

? alter table
| ALGORITHM [=] {DEFAULT|INPLACE|COPY}
    | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
    | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
    | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
    | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]

通过在ALTER语句的ALGORITHM参数指定。

ALGORITHM=INPLACE ，可以避免重建表带来的IO和CPU消耗，保证ddl期间依然有良好的性能和并发。
ALGORITHM=COPY ，需要拷⻉原始表，所以不允许并发DML写操作，可读。这种copy⽅式的效率还是不如 inplace ，因为前者需要记录undo和redo log，⽽且因为临时占⽤buffer pool引起短时间内性能受影响。

上⾯只是 Online DDL 内部的实现⽅式，此外还有 LOCK 选项控制是否锁表，根据不同的DDL操作类型有不同的表现：默认mysql尽可能不去锁表，但是像修改主键这样的昂贵操作不得不选择锁表。

LOCK=NONE ，即DDL期间允许并发读写涉及的表，⽐如为了保证 ALTER TABLE 时不影响⽤⼾注册或⽀付，可以明确指定，好处是如果不幸该 alter语句不⽀持对该表的继续写⼊，则会提⽰失败，⽽不会直接发到库上执⾏。 ALGORITHM=COPY 默认LOCK级别
LOCK=SHARED ，即DDL期间表上的写操作会被阻塞，但不影响读取。
LOCK=DEFAULT ，让mysql⾃⼰去判断lock的模式，原则是mysql尽可能不去锁表
LOCK=EXCLUSIVE ，即DDL期间该表不可⽤，堵塞任何读写请求。如果你想alter操作在最短的时间内完成，或者表短时间内不可⽤能接受，可以⼿动指定。

⽆论任何模式下，online ddl开始之前都需要⼀个短时间排它锁(exclusive)来准备环境，所以alter命令发出后，会⾸先等待该表上的其它操作完成，在alter命令之后的请求会出现等待waiting metadata lock。同样在ddl结束之前，也要等待alter期间所有的事务完成，也会堵塞⼀⼩段时间。所以尽量在ALTER TABLE之前确保没有⼤事务在执⾏，否则⼀样出现连环锁表。

online DDL操作类别

In-Place 为Yes是优选项，说明该操作⽀持INPLACE
Copies Table 为No是优选项，因为为Yes需要重建表。⼤部分情况与In-Place是相反的
Allows Concurrent DML? 为Yes是优选项，说明ddl期间表依然可读写，可以指定 LOCK=NONE（如果操作允许的话mysql⾃动就是NONE）
Allows Concurrent Query? 默认所有DDL操作期间都允许查询请求，放在这只是便于参考
Notes 会对前⾯⼏列Yes/No带 * 号的限制说明

Operation	In-Place?	Copies Tables?	Allow Concurrent DML?	Allows Concurent Query?	Notes
添加索引	Yes*	No*	Yes	Yes	对全文索引的一些限制
删除索引	Yes	No	Yes	Yes	仅修改表的元数据
OPTIMIZE TABLE	Yes	Yes	Yes	Yes	从5.6.17开始使用ALGORITHM=INPLACE, 当然如果指定了old_alter_table=1或mysqld启动带–skip-new则将还是COPY模式。如果表上有全文索引只支持Cope
对一列设置默认值	Yes	No	Yes	Yes	仅修改表的元数据
对一列修改auto-increment的值	Yes	No	Yes	Yes	仅修改表的元数据
添加foreign key constraint	Yes	No*	Yes	Yes	为了避免拷贝表，在约束创建时会禁用foreign_key_checks
删除foreign key constraint	Yes	No	Yes	Yes	foreign_key_checks不影响
改变列名	Yes*	No*	Yes*	Yes	为了允许DML并发，如果保持相同数据类型，仅改变列名
添加列	Yes*	Yes*	Yes*	Yes	尽管允许ALGORITHM=INPLACE，但数据大幅重组，所以它仍然是一项昂贵的操作。当添加列是auto-increment,不允许DML并发
删除列	Yes	Yes*	Yes	Yes	修改类型或添加长度，都会拷贝表，而且不允许更新操作
修改列数据类型	No	Yes*	No	Yes	尽管允许ALGORITHM=INPLACE，但数据大幅重组，所以它仍然是一项昂贵的操作。
更改列顺序	Yes	Yes	Yes	Yes	尽管允许ALGORITHM=INPLACE，但数据大幅重组，所以它仍然是一项昂贵的操作。
修改ROW-FORMAT和KEY_BLOCK_SIZE	Yes	Yes	Yes	Yes	尽管允许ALGORITHM=INPLACE，但数据大幅重组，所以它仍然是一项昂贵的操作。
没置列属性NULL或NOT NULL	Yes	Yes	Yes	Yes	尽管允许ALGORITHM=INPLACE，但数据大幅重组，所以它仍然是一项昂贵的操作。如果列定义必须转化NOT NULL，则不允许INPLACE
添加主键	Yes*	Yes	Yes	Yes	在同一个 ALTER TABLE 语句删除就主键、添加新主键时，才允许inplace；数据大幅重组,所以它仍然是一项昂贵的操作。
删除并添加主键	Yes	Yes	Yes	Yes	在同一个ALTER TABLE语句删除旧主键、添加新主键时，才允许INPLACE；数据大幅重组,所以它仍然是一项昂贵的操作。
删除主键	No	Yes	No	Yes	不允许并发DML，要拷贝表，而且如果没有在同一ATLER TABLE 语句里同时添加主键则会收到限制
变更表字符集	No	Yes	No	Yes	如果新的字符集编码不同，重建表

规则总结

In-Place为No，DML⼀定是No，说明 ALGORITHM=COPY ⼀定会发⽣拷⻉表，只读。
ALGORITHM=INPLACEE 也要可能发⽣拷⻉表，但可以并发DML:
- 添加、删除列，改变列顺序
- 添加或删除主键
- 改变⾏格式ROW_FORMAT和压缩块⼤⼩KEY_BLOCK_SIZE
- 改变列NULL或NOT NULL
- 优化表OPTIMIZE TABLE
- 强制 rebuild 该表
不允许并发DML的情况有
- 修改列数据类型
- 删除主键
- 变更表字符集
更改聚集索引，体现了表数据在物理磁盘上的排列，包含了数据⾏本⾝，需要拷⻉表；⽽普通索引通过包含主键列来定位数据，所以普通索引的创建只需要⼀次扫描主键即可，⽽且是在已有数据的表上建⽴⼆级索引，更紧凑，将来查询效率更⾼。
修改主键也就意味着要重建所有的普通索引。

执⾏流程

Prepare阶段 :
1. 创建新的临时frm⽂件(与InnoDB⽆关)
2. 持有EXCLUSIVE-MDL锁，禁⽌读写
3. 根据alter类型，确定执⾏⽅式(copy,online-rebuild,online-norebuild)
4. 更新数据字典的内存对象
5. 分配row_log对象记录增量(仅rebuild类型需要)
6. ⽣成新的临时ibd⽂件(仅rebuild类型需要)
ddl执⾏阶段 :
1. 降级EXCLUSIVE-MDL锁，允许读写
2. 扫描old_table的聚集索引每⼀条记录rec
3. 遍历新表的聚集索引和⼆级索引，逐⼀处理
4. 根据rec构造对应的索引项
5. 将构造索引项插⼊sort_buffer块排序
6. 将sort_buffer块更新到新的索引上
7. 记录ddl执⾏过程中产⽣的增量(仅rebuild类型需要)
8. 重放row_log中的操作到新索引上(no-rebuild数据是在原表上更新的)
9. 重放row_log间产⽣dml操作append到row_log最后⼀个Block
commit阶段 :
1. 当前Block为row_log最后⼀个时，禁⽌读写，升级到EXCLUSIVE-MDL锁
2. 重做row_log中最后⼀部分增量
3. 更新innodb的数据字典表
4. 提交事务(刷事务的redo⽇志)
5. 修改统计信息
6. rename临时idb⽂件，frm⽂件
7. 变更完成

⼏个疑问点

如何实现数据完整性
row_log记录了ddl变更过程中新产⽣的dml操作，并在ddl执⾏的最后将其应⽤到新的表中，保证数据完整性。
online与数据⼀致性如何兼得
online ddl在prepare阶段和commit阶段都会持有MDL-Exclusive锁，禁⽌读写。由于prepare和commit阶段相对于ddl执⾏阶段时间特别短，因此基本可以认为是全程online的。Prepare阶段和commit阶段的禁⽌读写，主要是为了保证数据⼀致性。Prepare阶段需要⽣成row_log对象和修改内存的字典；Commit阶段，禁⽌读写后，重做最后⼀部分增量，然后提交，保证数据⼀致。
如何实现server层和innodb层⼀致性
在prepare阶段，server层会⽣成⼀个临时的frm⽂件，⾥⾯包含了新表的格式；innodb层⽣成了临时的ibd⽂件(rebuild⽅式)；在ddl执⾏阶段，将数据从原表拷⻉到临时ibd⽂件，并且将row_log增量应⽤到临时ibd⽂件；在commit阶段，innodb层修改表的数据字典，然后提交；最后innodb层和mysql层⾯分别重命名frm和idb⽂件。
主从模式下的延时
在主从环境下，主库执⾏alter命令在完成之前是不会进⼊binlog记录事件，如果允许dml操作则不影响记录时间，所以期间不会导致延迟。然⽽，由于从库是单个SQL Thread按顺序应⽤relay log，轮到ALTER语句时直到执⾏完才能下⼀条，所以从库会在master ddl完成后开始产⽣延迟。如果主库ddl使⽤了1⼩时，从库将产⽣1⼩时的主从延时。（pt-osc可以控制延迟时间，所以这种场景下它更合适）

pt-online-schema-change

online DDL还是有⼀段锁表时间，这段时间的查询依然会引起meta data lock问题。pt-osc解决了这个问题，有在线修改表不锁表的能⼒。percona公司

pt-osc⼯作过程

创建⼀个和要执⾏ alter 操作的表⼀样的新的空表结构(是alter之前的结构)
在新表执⾏alter table 语句（速度应该很快）

在原表中创建触发器3个触发器分别对应insert,update,delete操作

1
2
3

6165 Query CREATE TRIGGER `pt_osc_confluence_sbtest3_del` AFTER DELETE ON `confluence`.`sbtest3` FOR EACH ROW DELETE IGNORE FROM `confluence`.`_sbtest3_new` WHERE `confluence`.`_sbtest3_new`.`id` <=> OLD.`id`
6165 Query CREATE TRIGGER `pt_osc_confluence_sbtest3_upd` AFTER UPDATE ON `confluence`.`sbtest3` FOR EACH ROW REPLACE INTO `confluence`.`_sbtest3_new`(`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`)
6165 Query CREATE TRIGGER `pt_osc_confluence_sbtest3_ins` AFTER INSERT ON `confluence`.`sbtest3` FOR EACH ROW REPLACE INTO `confluence`.`_sbtest3_new`(`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`)

replace into :
a. 如果发现表中已经有此⾏数据（根据主键或者唯⼀索引判断）则先删除此⾏数据，然后插⼊新的数据。
b. 否则，直接插⼊新数据。

以⼀定块⼤⼩从原表拷⻉数据到临时表，拷⻉过程对数据⾏持有S锁。拷⻉过程中通过原表上的触发器在原表进⾏的写操作都会更新到新建的临时表。

1
2
3

6165 Query INSERT LOW_PRIORITY IGNORE INTO `confluence`.`_sbtest3_new`(`id`, `k`, `c`, `pad`) SELECT `id`, `k`, `c`, `pad` FROM `confluence`.`sbtest3` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '4692805')) AND ((`id` <= '4718680')) LOCK IN SHARE MODE /*pt-online-schema-change 46459 copy nibble*/
INSERT IGNORE 与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在 的数据，如果数据库没有数据，就插⼊新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插⼊数据的⽬的。
如果数据修改的时候，还没有拷⻉到新表，修改后再拷⻉，虽然重复覆盖，但是数据也没有出错；如果是数据已经拷⻉，原表发⽣修改，这时触发器同步修改数据，两种情况下都保证了数据的⼀致性；

Rename 原表到old表中，在把临时表Rename为原表
如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理
alter-foreign-keys-method配置：
- rebuild_constraints ，优先采⽤这种⽅式
  - 它先通过 alter table t2 drop fk1,add _fk1 重建外键参考，指向新表
  - 再 rename t1 t1_old, _t1_new t1 ，交换表名，不影响客⼾端删除旧表 t1_old
  - 但如果字表t2太⼤，以致alter操作可能耗时过⻓，有可能会强制选择 drop_swap。
  - 涉及的主要⽅法在 pt-online-schema-change ⽂件的determine_alter_fk_method, rebuild_constraints, swap_tables三个函数中。
- drop_swap ，
  - 禁⽤t2表外键约束检查 FOREIGN_KEY_CHECKS=0
  - 然后 drop t1 原表
  - 再 rename _t1_new t1
  - 这种⽅式速度更快，也不会阻塞请求。但有⻛险，第⼀，drop表的瞬间到rename过程，原表t1是不存在的，遇到请求会报错；第⼆，如果因为bug或某种原因，旧表已删，新表rename失败，那就太晚了，但这种情况很少⻅。
  - 我们的开发规范决定，即使表间存在外键参考关系，也不通过表定义强制约束。
默认最后将旧原表删除
从库通过binlog执⾏相同操作

与原⽣online ddl⽐较

online ddl在必须copy table时成本较⾼，不宜采⽤
pt-osc⼯具在表存在触发器时，不适⽤（⼀个表上不能同时有2个相同类型的触发器）
修改索引、外键、列名时，优先采⽤online ddl，并指定 ALGORITHM=INPLACE
其它情况使⽤pt-osc，虽然存在copy data
pt-osc⽐online ddl要慢⼀倍左右，因为它是根据负载调整的
⽆论哪种⽅式都选择的业务低峰期执⾏
特殊情况需要利⽤主从特性，先alter从库，主备切换，再改原主库

gh-ost

why gh-ost

基于触发器的online ddl⽅案的不⾜

Triggers, overhead: 触发器是⽤存储过程的实现的，就⽆法避免存储过程本⾝需要的开销。
Triggers, locks: 增⼤了同⼀个事务的执⾏步骤，更多的锁争抢。
Trigger based migration, no pause: 整个过程⽆法暂停，假如发现影响主库性能，停⽌ OnlineDDL，那么下次就需要从头来过。
Triggers, multiple migrations: 多个并⾏的操作是不安全的。
Trigger based migration, no reliable production test: ⽆法在⽣产环境做测试。
Trigger based migration, bound to server: 触发器和源操作还是在同⼀个事务空间。

gh-ost优点：

⽆触发器
gh-ost 希望⼆进制⽂件使⽤基于⾏的⽇志格式。也可以使⽤从库，将基于语句的⽇志格式转化成基于⾏的⽇志格式。
轻量级
因为不需要使⽤触发器，gh-ost 把修改表定义的负载和正常的业务负载解耦开了。它不需要考虑被修改的表上的并发操作和竞争等，这些在⼆进制⽇志中都被序列化了，gh-ost 只操作临时表，完全与原始表不相⼲。事实上，gh-ost 也把⾏拷⻉的写操作与⼆进制⽇志的写操作序列化了，这样，对主库来说只是有⼀条连接在顺序的向临时表中不断写⼊数据，这样的⾏为与常⻅的 ETL 相当不同。
并⾏操作
对于 gh-ost 来说就是多个对主库的连接来进⾏写操作。
可暂停
因为所有写操作都是 gh-ost ⽣成的，⽽读取⼆进制⽂件本⾝就是⼀个异步操作，所以在暂停时，gh-ost 是完全可以把所有对主库的写操作全都暂停的。
动态可控
gh-ost 通过监听 TCP 或者 unix socket ⽂件来获取命令。即使有正在进⾏中的修改⼯作，⽤⼾也可以向 gh-ost 发出命令修改配置
可审计
和动态可控⼀样，可以通过命令查看任务进度、参数、实例情况等。
可测试
可靠
综合以上⼏个特性。它是可靠的。

原理

gh-ost 作为⼀个伪装的备库，可以从主库/备库上拉取 binlog，过滤之后重新应⽤到主库上去，相当于主库上的增量操作通过 binlog ⼜应⽤回主库本⾝，不过是应⽤在幽灵表上。
image-004.png

gh-ost ⾸先连接到主库上，根据 alter 语句创建幽灵表
然后作为⼀个”备库“连接到其中⼀个真正的备库上，⼀边在主库上拷⻉已有的数据到幽灵表，⼀边从备库上拉取增量数据的 binlog，然后不断的把 binlog 应⽤回主库。
cut-over 是最后⼀步，锁住主库的源表，等待 binlog 应⽤完毕，然后替换 gh-ost 表为源表。gh-ost 在执⾏中，会在原本的 binlog event ⾥⾯增加以下 hint 和⼼跳包，⽤来控制整个流程的进度，检测状态等。

⼏种模式

image-005.png
作者并没有对着三种模式有明确的使⽤倾向

模式⼀、连上从库，在主库上修改

这是 gh-ost 默认的⼯作模式，它会查看从库情况，找到集群的主库并且连接上去。修改操作的具体步骤是：

在主库上读写⾏数据；
在从库上读取⼆进制⽇志事件，将变更应⽤到主库上；
在从库上查看表格式、字段、主键、总⾏数等；
在从库上读取 gh-ost 内部事件⽇志（⽐如⼼跳）；
在主库上完成表切换；

如果主库的⼆进制⽇志格式是 Statement，就可以使⽤这种模式。但从库就必须配成启⽤⼆进制⽇志（log_bin, log_slave_updates），还要设成 Row 格式（binlog_format=ROW），实际上 gh-ost 会在从库上帮你做这些设置。
事实上，即使把从库改成 Row 格式，这仍然是对主库侵⼊最少的⼯作模式。

模式⼆、直接在主库上修改

如果没有从库，或者不想在从库上操作，那直接⽤主库也是可以的。gh-ost 就会在主库上直接做所有的操作。仍然可以在上⾯查看主从复制延迟。

主库必须产⽣ Row 格式的⼆进制⽇志；
启动 gh-ost 时必须⽤–allow-on-master 选项来开启这种模式；

模式三、在从库上修改和测试

这种模式会在从库上做修改。gh-ost 仍然会连上主库，但所有操作都是在从库上做的，不会对主库产⽣任何影响。在操作过程中，gh-ost 也会不时地暂停，以便从库的数据可以保持最新。

–migrate-on-replica 选项让 gh-ost 直接在从库上修改表。最终的切换过程也是在从库正常复制的状态下完成的。
–test-on-replica 表明操作只是为了测试⽬的。在进⾏最终的切换操作之前，复制会被停⽌。原始表和临时表会相互切换，再切换回来，最终相当于原始表没被动过。主从复制暂停的状态下，你可以检查和对⽐这两张表中的数据。

时序问题分析

binlog执⾏时间和copy old row时序不会影响最终结果。分析如下：
执⾏器对binlog语句实际执⾏时动作的替换

源类型	目标类型
insert	replace
update	update
delete	delete

对与insert和update是没有问题的，因为⽆论copy old row和apply binlog的先后顺序，如果applybinlog在后，会覆盖掉copy old row，如果apply binlog在前⾯，copy old row因为使⽤insert ignore，因此会被ignore掉；

对与delete数据，abc三个操作，可能存在三种情况（b肯定在a的后⾯）：
a.delete old row
b.delete binlog apply
c.copy old row

cab，c会将数据copy到ghost表，最后b会把ghost表中的数据delete掉；
acb，c空操作，b也是空操作；
abc，b空操作，c也是空操作；

参考⽂档

ONLINE DDL VS PT-ONLINE-SCHEMA-CHANGE
gh-ost triggerless design
sbr vs rbr

长沙雅美个人工具网站

Mysql online ddl⼯具gh-ost

背景