MySQL online DDL原理
MySQL online DDL原理
MySQL online DDL(Data Definition Language)主要解决的问题是，满足在不中断数据库服务的情况下进行DDL操作。现有的方案有：
- 使用原生的MySQL online DDL
- 在从库上修改表结构，主从切换
- 使用第三方工具
在MySQL 5.5之前的版本其DDL的实现方式是：
- 按照原表的定义创建一个新的临时表
- 对原表加
写锁 - 对新的临时表进行修改
- 将原表中的数据逐行复制到新表中
- 释放原表的
写锁 - 将旧表删除，并将新的临时表重命名
此方案存在的问题：
- 复制数据的过程需要耗费额外的存储空间，并且执行过程时耗较长
- 复制数据的过程需要
写锁，无法持续对外提供服务
MySQL 5.7 online DDL
ALTER TABLE testdb.testtable ADD COLUMN _new_column
ALGORITHM = inplace,LOCK = default;ALGORITHM子句用来指定执行DDL所采用的方式，取值为{DEFAULT|INPLACE|COPY}
- ALGORITHM = COPY
- 执行DDL的过程中采用表拷贝的方式进行，过程中会阻塞所有的DML
- ALGORITHM = INPLACE
- 执行DDL的过程中不发生表拷贝，过程中允许并发执行DML
- ALGORITHM = DEFAULT
- 默认选项，MYSQL会自动选择最优的执行方式，原则是尽量保证DML的并发操作
LOCK子句描述持有的锁的类型来控制DML(Data Manipulation Language)的并发，取值{DEFAULT|NONE|SHARED|EXCLUSIVE}
- LOCK = EXCLUSIVE
- 持有排它锁，阻塞所有的请求
- LOCK = SHARED
- 允许SELECT，但是阻塞INSERT UPDATA DELETE
- LOCK = NONE
- 不对表加锁，允许所有请求
- LOCK = DEFAULT
- 根据DDL的类型，在保证最大并发的原则下来选择LOCK的取值
MySQL 5.7 online DDL的实现原理
依然存在的问题：
- 在原表中仍然存在排他锁，有锁等待的风险
- 增量日志大小是有限制的 (innodb_online_alter_log_max_size)
- 有可能造成较大的主备延迟 (Bug#73196)
- 无法暂停
PT-OLINE-SCHEMA-CHANGE
使用Percona-toolkit第三方工具。
- 支持并发DML操作
- 经过多年生产环境验证，较为可靠
原理：
- 创建一张新表，表结构与旧表相同
- alter新表
- 在原表上创建INSERT, UPDATE, DELETE三种类型的触发器
- 将旧表的数据拷贝到新表中，同时通过触发器将旧表中的操作映射到新表
- 如果原表有外键约束，处理外键
- 原表重命名为old表，new表重命名为原表，整个过程为原子操作
- 删除old表(默认)
GH-OST
优点：
- 无触发器设计
- 切换方案的设计
- 最大限度的减少对主机的影响
最佳实践
MYSQL5.6以下的版本，建议使用第三方工具进行DDLMYSQL5.6及以上的版本，除了修改索引、外键、列名时，优先采用online DDL，并指定ALGORITHM = INPLACE，其他的均采用PT-OSC