MySQL DDL发展史

DDL发展

• DDL
• online DDL
• 工具化时代

1、DDL（锁表阶段）

• ALGORITHM=COPY
• ALGORITHM=inplace

ALTER TABLE xxxx ADD xxx, ALGORITHM=INPLACE, LOCK=SHARED

• 关键字：ALGORITHM
  • online ddl前：不copy原表数据，只重新做索引，所以会比copy要快，过程还是锁表
  • online ddl：区分是否需要rebuild表，如果需要，还是要涉及表的数据copy，不过这个过程是允许并发dml的
  • copy：server层的数据拷贝，必锁
  • inplace：
  • 注意：这里有个误区，ALGORITHM=INPLACE就是不copy数据，这是不对的，还要看是否会rebuid的，是否在数据拷贝过程中支持并发DML， ALGORITHM=copy则一定不支持online ddl
• 关键字：LOCK
  • LOCK=NONE DDL期间允许dml并发
  • LOCK=SHARED 写操作加锁
  • LOCK=DEFAULT mysql自己去判断是否加锁，原则是是少加锁
  • LOCK=EXCLUSIVE 读写加锁
• 注意：8.0 ALGORITHM新增INSTANT，这里LOCK需要等于DEFAULT，这里可以建议一下研发同学，如果不太了解这些关键字，只写alter语句就可以了，有时写错了反而会起到反作用

DDL的流程：

1. 新建带索引的临时表
2. 锁原表，禁止DML，允许查询
3. copy：将原表数据拷贝到临时表(无排序，一行一行拷贝) inplace:读取聚集索引，构造新的索引项，排序并插入新索引
4. copy:进行rename，升级字典锁，禁止读写 inplace:等待打开当前表的所有只读事务提交
5. 完成创建索引操作

- 注意： 在online ddl前，inplace的方法主要在第三步大大缩短了时间，只重构了索引，没有重新copy所有数据

2、online DDL

• 5.6支持online ddl
• 5.7新增online ddl的场景支持
• 8.0增加了快速加列的功能

online DDL流程大致分为三个步骤

• prepare
  1. 创建新的临时frm文件
  2. 持有EXCLUSIVE-MDL锁，禁止读写
  3. 根据alter类型，确定执行方式
  4. 更新数据字典的内存对象
  5. 分配row_log对象记录增量
  6. 生成新的临时ibd文件
• 执行阶段
  1. 降级EXCLUSIVE-MDL锁，允许读写
  2. 扫描old_table的聚集索引每一条记录rec
  3. 遍历新表的聚集索引和二级索引，逐一处理
  4. 根据rec构造对应的索引项
  5. 将构造索引项插入sort_buffer块
  6. 将sort_buffer块插入新的索引
  7. 处理ddl执行过程中产生的增量(仅rebuild类型需要)
• commit
  1. 升级到EXCLUSIVE-MDL锁，禁止读写
  2. 重做最后row_log中最后一部分增量
  3. 更新innodb的数据字典表
  4. 提交事务(刷事务的redo日志)
  5. 修改统计信息
  6. rename临时idb文件，frm文件
  7. 变更完成

注意：

1. online DDL在开头和结尾也是有两个锁的，所以在执行online DDL前需要确认当前是否有正在执行的关于这个表的大事务，防止阻塞开头的锁获取
2. row_log就是存放增量的地方，
   • innodb_tmpdir参数（5.6.29后），指定这个增量存放的目录，防止默认tmpdir的空间不足
   • innodb_online_alter_log_max_size参数，如果增量大小超过这个参数会报错，DB_ONLINE_LOG_TOO_BIG ，默认128M
   • 如何查看进度：
   • 在MySQL 5.7需要先开启，然后才能查看

UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/alter%';
UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%';
SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;

8.0快速加列

之前online DDL存在的问题：

• 大表变更时间过长
• 需要额外磁盘空间
• 消耗大量的IO\CPU资源
• 导致备机延迟，级联架构延迟加倍

8.0快速加列的原理：

ALGORITHM=INSTANT，LOCK=DEFAULT

8.0的metadata中新增了instant列的默认值和instant列的数量，数据的物理记录中增加了info_bit，用flag标记这条记录是否是instant 当使用instant时，直接修改metadata中的列信息就好，操作数据时，就可以结合metadata来组成最新数据

针对不同的操作，8.0如何操作instant列的呢

• select： 读取一行数据的物理记录时，会根据 flag 来判断是否需要去 metadata 中获取 instant 列的信息；如果需要，则根据 column_num 来读取实际的物理数据，再从 metadata 中补全缺少的 instant 列数据。
• insert： 额外记录语句执行时的 flag 和 column_num。
• delete： 与以前的版本保持一致
• update： 如果表的 instant column 数量发生了变化，对旧数据的 update 会在内部转换成 delete 和 insert 操作

8.0支持哪些操作

• change index
• alter table rename
• set/drop default
• add column（支持instant）等

8.0快速加列的限制

• 不能和其它非instant操作放到一个语句中，会报错
• 只支持在最后加列
• 不支持压缩
• 不支持全文索引
• 不支持临时表

注意： 1. 当对包含 instant 列的表进行 rebuild 时，所有的数据在 rebuild 的过程中重新以旧的数据格式（包含所有列的内容） 2. 可以用SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%';查到有多少快速加的列

3、工具化

• pt-online-schema-change
• gh-ost

pt-online-schema-change

• 工作原理：
  1. 创建一个要修改的表结构相同的空表new
  2. 然后对空表进行alter操作
  3. 在原表上创建触发器分表对应insert，delete，update等dml操作
  4. 然后从原表copy数据到新表，过程中如果有对应的dml操作都会通过触发器写到新表中
  5. rename原表到old，rename新表new到原表
  6. 如果有外键需要根据alter-foreign-keys-method参数的值检测外键相关的表做对应处理，引用要修改的表的外键必须同步进行处理，确保可以继续引用正确的表
  7. 默认是删除旧表

示例及简单参数介绍：

pt-online-schema-change  --check-interval=2 --alter="ENGINE=InnoDB" --no-check-replication-filters --max-load="Threads_running=200" --critical-load="Threads_running=500" --recurse=0 --ask-pass-dry-run --print h=xxx,P=xxx,u=xxx,D=xxx,t=xxx,A=utf8

• --ask-pass：手动输入密码
• --dry-run 创建和修改新表，不会创建触发器
• –max-load：执行完一个chunk时会自动检查status的值，超过会先暂停
• --check-interval：检查间隔
• --[no]check-replication-filters 如果检查到有复制过滤则拒绝改表
• --critical-load：执行完一个chunk时会自动检查status的值，超过会终止
• --max-lag copy完一次chunk后会查看复制延迟的情况，延迟大于这个值时暂停复制数据
• --chunk_time 默认0.5s，copy数据行时，尽量保证0.5s一个chunk，来大致确认一个chunk_size有多大
• --chunk_size 默认1000行，比chunk_time优先生效
• --set-vars 可以在后面接一些命令，修改部分参数变量，针对这次ddl修改–set-vars “wait_timeout=10000,innodb_lock_wait_timeout=1“
• --no-version-check：云上执行时版本不对时忽略版本

pt-online-schema-change注意事项：

• 需要有主键或者唯一键（触发器同步是通过replace into的方法同步数据，原表同步是通过insert ignore的方法，如果没有主键或者唯一键，很容易出现数据错乱）
• 不能通过直接drop再add重命名列，pt不会同步这个列的数据，会导致数据丢失，使用change语法修改列格式
• pt会比原生的online ddl慢一些，同时会产生大量的redo和binlog
• 中途失败不会自动清理触发器，需要手动清理，原表不能有触发器
• 最好指定utf8的字符集，否则会导致表改表后comment乱码
• 表变更结束最后rename替换表期间是会有锁的，不过时间很短

分享一个pt-online-schema-change的死锁场景

• 背景： 1.事务A：insert into a xxxx，触发器是replace into _a_new xxx(原表新写入) 2.事务B：insert ignore _a_new(xxx) select xxx from a （原表往_a_new表做数据同步）
• 死锁场景：
  1. 当事务A执行完insert a表触发器还没开始执行数据时，持有 a表记录的x锁
  2. 事务B开始数据同步，持有_a_new表的自增锁，等待a表的s锁
  3. 此时事务A触发器开始执行，等待_a_new表的自增锁，从而形成死锁
• 简单来说，锁持有情况如下：
  1. A：持有a表记录的x锁，等待_a_new的自增锁
  2. B：持有_a_new的自增锁，等待a表的x锁

gh-ost

• 原理
  1. 在 Master 中创建镜像表_tablename_gho和心跳表_tablename_ghc;
  2. 向心跳表中写入 Online DDL 的进度以及时间（关键步骤）
  3. 在镜像表上执行 ALTER操作
  4. 伪装成 Slave 连接到 Master 的 Slave 上获取 Binlog 的信息（默认设置，也可以连 Master）
  5. 在 Master 中完成镜像表的数据同步
  6. 从源表中拷贝数据到镜像表
  7. 依据 Binlog 信息完成增量数据的变更
  8. 在源表上加锁
  9. 确认心跳表中的时间，确保数据是完全同步的
  10. 用镜像表替换源表
  11. Online DDL 完成

image.png

go-ost相对比pt来说的优势：

• 不依赖触发器，依赖binlog，对原表写入无压力
• 轻量级，单独起一个连接异步操作，而pt的触发器写入都是在同一个事务
• 可暂停，可以通过某些指标来停止主库除去追踪表的其他所有写入，和pt的--max-load类似
• 动态控制，可以通过TCP或者socket文件来获取命令，echo throttle | socat - /tmp/gh-ost.sock：限流，可以动态修改运行参数
• 可测试，--test-on-replica参数允许在从库进行修改表结构，修改完后暂停主从复制，切换表再切换回来，对比同一时刻的两个表数据是否一致（此备库不提供服务）

go-ost使用以及简单参数介绍

gh-ost --alter "add index (add_time)" --database="test" --table="test" --allow-master-master --allow-on-master --host="xxx"  --user="xxx" --password="xxx" --assume-rbr --execute

• --allow-master-master：允许双主
• --assume-rbr：用户没有super权限时加入这个参数，不会再去变更binlog格式
• --allow-on-master：允许直接在master库上使用
• --chunk-size：每次循环处理的数据行
• --cut-over：自动执行rename操作
• --panic-flag-file：这个文件创建后，操作立即终止对出
• --throttle-flag-file：此文件存在操作暂停，不存在继续执行
• --initially-drop-socket-file：如果还有上次gh-ost的socket文件则会执行删除老的
• --ok-to-drop-table：执行完后是否删除老表

go-ost有哪些限制呢：

• binlog需要为row，如果应用从库则从库binlog需要为row并且开启log_slave_updates
• 不支持外键，不论源表是主表还是子表，都无法使用
• 不支持触发器
• 压力太大的情况下，会一直优先应用binlog，但由于是单线程，可能一直完成不了

gh-ost的切换细节，如何保证切换时不丢数据：

image.png

1. 创建_del表是为了防止cut-over提前执行，导致表数据丢失
2. 同一个会话先执行LOCK TABLES ... WRITE之后还是可以执行DROP操作的
3. 无论RENAME TABLE和DML操作谁先执行，被阻塞后RENAME TABLE总是优先于DML被执行

gh-ost的bug（源自姜老师公众号，简单描述下）

go-ost执行流程如下：

1. addDMLEventsListener：添加对于二进制日志的过滤采集（指定表的二进制日志过滤）
2. ReadMigrationRangeValues：获取对应表唯一索引的max、min值
3. onBeforeRowCopy：将捕获的二进制日志应用到表 *_gho
4. iterateChunks：根据 min、max 值，批量插入数据到表 *_gho
5. rename & drop 新旧表

image.png

1. 在after_sync的情况下，
2. binlog=5的事务已经提交了，由于其他原因导致从库还未接收到=5的binlog，因此主库=5的事务未在redo层提交，
3. 因此无法通过select获取到，通过select只获取到min=1，max=4的数据，
4. 所以只能获取到5之后的binlog进行增量同步，相当于丢失了=5这个事务，真正丢数据可能不止1条，而是一个事务 - 注意：任何影响二阶段提交情况，binlog已经提交，但是redo还未commit，从而导致读到的数据和binlog已提交数据不符

最后，本篇文章更多是总结一些DDL的使用，更偏向于一些介绍，汇总，可以帮助开发同学来了解下DDL的发展以及工具使用，如果有哪些地方描述不准确辛苦私下沟通