By 兰春

Updated:

Contents
  1. 1. 一、常见的几种方案
  2. 2. 二、方案剖析
    1. 2.1. 2.1 MySQL源生的IN-PLACE ONLINE DDL
  3. 3. 三、第三方工具
    1. 3.1. 3.0 第三方工具大致原理
    2. 3.2. 3.1 pt-online-shema-change
    3. 3.3. 3.2 gh-ost
  4. 4. 四、 slave 先ddl，后切换主从
  5. 5. 五、 ONLINE DDL 最佳方案选型

一、常见的几种方案

  • 1.1 MySQL源生的IN-PLACE ONLINE DDL
1
2
3
4
5.5，5.6 开始支持
5.7 支持的更好，有更多ddl操作支持online
8.0 支持快速加列功能
  • 1.2 第三方工具
1
2
1. pt-online-schema-change
2. gh-ost
  • 1.3 slave 先ddl，后切换主从

二、方案剖析

2.1 MySQL源生的IN-PLACE ONLINE DDL

  • 原理
1
2
3
4
5
6
7
8
9
原理比较复杂，不一一解读。但是中间有几个重要的过程：  
1. 加一会排它锁，开启战场，并释放排它锁    
2. 记录ddl期间产生的增量dml（大小由innodb_online_alter_log_max_size控制）  
3. 应用这些增量dml  
4. 再加一会排它锁，清理战场，释放排它锁  
这里关心的问题：
1. 如果再ddl期间，innodb_online_alter_log_max_size的大小被占满，会有怎样的后果？
2. 如果DDL期间，被强行终止了，会有怎么样的后果？
  • 优点
1
1. 官方出品，原生态，品质有保障
  • 缺点
1
2
3
4
1. 有所等待风险  
2. innodb_online_alter_log_max_size 是有限制的  
3. 有可能造成主从延迟  
4. 不是所有的ddl都是online的，对ddl类型有要求
  • 哪些DDL可以online (基于5.7的官方文档)

8.0 可以支持快速加列

类型 操作 是否需要copy数据，重新rebuild表 是否允许并发DML 是否只修改元数据 备注
索引相关 创建、添加二级索引 NO YES NO -
索引相关 删除索引 NO YES YES -
索引相关 重命名索引 NO YES YES -
索引相关 添加FULLTEXT索引 NO* NO NO -
索引相关 添加SPATIAL索引 NO NO NO -
索引相关 改变索引类型（USING {BTREE or HASH}） NO YES YES -
主键相关 添加主键 YES* YES NO -
主键相关 删除主键 YES NO NO -
主键相关 删除主键并且又添加主键 YES YES NO -
列操作相关 添加列 YES YES* NO -
列操作相关 删除列 YES YES NO -
列操作相关 重命名列 NO YES* YES -
列操作相关 重新排列列（use FIRST or AFTER） YES YES NO -
列操作相关 设置列的默认值 NO YES YES -
列操作相关 修改列的数据类型 YES NO NO -
列操作相关 扩展varchar列的长度 NO YES YES 0~255 , 256 ~ 256+ 这两个区间可以in-place
列操作相关 删除列的默认值 NO YES YES -
列操作相关 修改auto-increcement的值 NO YES NO* -
列操作相关 使某列修改成NULL YES* YES NO -
列操作相关 使某列修改成NOT NULL YES* YES NO -
列操作相关 修改列定义为ENUM、SET NO YES YES -
表相关操作 optimizing table YES YES NO -
表相关操作 Rebuilding with the FORCE option YES YES NO -
表相关操作 Renaming a table NO YES YES -

三、第三方工具

3.0 第三方工具大致原理

  1. 先创建一个临时表 old_table_tmp
  2. 给临时表变更结构 alter old_table_tmp …
  3. 然后呢就是关键了： 将增量数据 和 原表的数据 都拷贝到 临时表
  4. 当原表数据拷贝完毕后，对原表加锁，进行切换
  5. 打扫战场，结束

好了，这里pt-online-shema-change 是通过触发器的方式，来同步增量数据的 ， gh-ost 是通过模拟slave，监听binlog并应用binlog来完成增量数据同步的，这里是主要区别。

所以，不管哪种方式，这里需要解决一个时序的问题(因为rowcopy和row_apply是并行的，不知道哪个先哪个后)，我们暂且认为 拷贝原表数据叫： rowcopy ， 拷贝增量数据并应用为 row_apply

由于rowcopy从时序上来说，都是老数据，所以它的优先级是最低的，所以将rowcopy的动作转换为inset ignore，意味着，row apply是可以覆盖rowcopy数据的，这样理解没问题吧

好了，上面的问题解决了，其他的基本就不是问题了

3.1 pt-online-shema-change

  • 优点
1
2
3
4
5
1. percona 出品，必属金品
2. 经过多年的生产环境验证，质量可靠
3. 支持并发DML操作
  • 缺点
1
2
3
4
5
6
7
8
9
10
11
1. 原表不能有触发器
3. 由于触发器的原因，对master的性能消耗比较大
4. 处理外键有一定的风险，需要特殊处理  
5. 原表中至少要有主键或者唯一键
	检查是否具有主键或者唯一索引，如果都没有，这一步会报错  
	提示The new table `xx`.`_xx_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.
6. ddl不能有添加唯一索引的操作
	如果对表增加唯一索引的话，会存在丢数据的风险。  
	具体原因是因为pt-osc在copy已有的数据时会使用insert ignore将老表中的数据插入到新表中，因为新表已经增加了unique index，所以重复的数据会被ignore掉
	--check-unique-key-change 可以避免  , 默认yes
  • 原理
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
1. 创建一张新表  
2. alter新表
3. 原表创建insert，update，delete三种触发器
4. 原表开始拷贝数据到新表，且触发器也开始映射到新表
5. 处理外键（如果没有忽略）
6. 重命名新表和原表  
7. 清理战场  
重要：
insert触发器 =SQL转换=> replace into
update触发器 
	=SQL转换=> delete ignore + replace into (大于3.0.2版本)
	=SQL转换=> replace into（低于3.0.2版本，所以这个版本会有问题，如果这时候对老的主键修改，那么修改之前的值不会去掉，从而多了一些异常数据）  
delete触发器 =SQL转换=> delete ignore
copy rows    =SQL转换=> insert ignore into
  • 最佳实践
1
2
1. innodb_autoinc_lock_mode 设置成 2 ， 否则会经常死锁，autoinc锁  
2. 如果中途ddl失败，需要先删除触发器，再删除新的临时表

3.2 gh-ost

  • 优点
1
2
3
4
1. 无触发器设计
2. out-over方案设计
3. 对主机性能级别无影响
4. 可以暂停
  • 缺点
1
2
3
4
5
1. 原表不能有外键
2. 原表不能有触发器
3. 强制要求binlog为row格式
4. 原表不能有字母大小不同的同名表
5. 当并发写入多的时候，在应用binlog阶段由于是单线程，所以会非常慢,影响ddl性能和进度
  • 原理
1
原理基本都一样，这里主要的区别就是row apply这里，pt-osc是触发器，这里是监听master binlog并应用日志，其余的差别不大，这里不再赘述

四、 slave 先ddl，后切换主从

如果其余方式都不行，只能祭出大招slave先ddl，然后主从切换了

  • 优点
1
1. slave操作，不影响master
  • 缺点
1
2
3
4
1. 需要主从切换，主从切换越平滑，此方案就越好  
2. 有几点需要考虑和处理下：
	2.1 add column after|before ， 这样的操作slave先做是否有影响  
	2.2 slave先新增字段，可能会导致主从同步停掉，需要设置某些参数

五、 ONLINE DDL 最佳方案选型

    1. 如果是创建索引、修改默认值这样的，online ddl 快速且无影响的操作，尽量优先选择online ddl
    1. 如果当前服务器写入量不高，负载不高，且原表没有触发器，没有外键，且此表有主键，尽量优先选择pt-online-schema-change
    1. 其余情况，选择主从切换
Contents
  1. 1. 一、常见的几种方案
  2. 2. 二、方案剖析
    1. 2.1. 2.1 MySQL源生的IN-PLACE ONLINE DDL
  3. 3. 三、第三方工具
    1. 3.1. 3.0 第三方工具大致原理
    2. 3.2. 3.1 pt-online-shema-change
    3. 3.3. 3.2 gh-ost
  4. 4. 四、 slave 先ddl，后切换主从
  5. 5. 五、 ONLINE DDL 最佳方案选型

幸福,不在于得到的多

而在于计较的少