MySQL 5.7.32 Online ddl使用注意事项

前言:

        MySQL从5.6开始有了Online ddl功能，有效的解决了DDL操作的痛点，特别是针对一些大表的DDL操作，大幅度的减少ddl操作对应用的影响，但 Online ddl不是一个完美的功能，它仍然会对数据库产生影响，所以我们需要了解使用Onlineddl的注意事项。

Online ddl:

       从MySQL 5.6开始，Online ddl使用了inplace算法并且默认使用，inplace表示在innodb层进行操作，不涉及server层，算法包括no-rebuild-table 以及rebuild-table,在使用inplace算法的时候，会自动优先使用 not-rebuild-table，如果场景不允许，才会使用 rebuild-table，rebuild-table是一个重建表的操作，对空间，IO会有消耗。

       从MySQL8.0.12开始，引入了instant算法并且默认使用，instant算法只需修改数据字典信息，从而大大提高了DDL操作的速度，但算法目前支持的DDL操作类型较少，对于instant不支持的DDL操作类型，则会退化成inplace算法进行。

       此外，还有copy算法，但该算法较为落后，是以前老版本MySQL5.5之前的默认算法，该算法会在server层进行拷贝表迁移数据，效率就低，并且不允许DML操作。

注意事项:

1 确认DDL类型是否支持Online ddl

        Online ddl支持大部分的操作类型，但还是存在不支持的场景，例如:添加fulltext全文索引, SPATIAL空间索引，删除主键，修改列类型，添加虚拟列等都不允许DML操作。对于不支持Online ddl操作的，可以考虑第三方的工具进行在线迁移，如gt-osc,gh-ost

 

 

更多Online ddl支持场景，可以通过MySQL官方文档去获取

https://dev.mysql.com/doc/refman/5.7/en/innodb-Online-ddl.html

 

2 Online ddl操作会产生主从延时，要评估应用是否有影响

        MySQL Online ddl操作大部分场景到会引发主从延时，因为ddl操作需要在主库执行完成之后，才写入binlog同步到从库进行应用，所以假设一个添加列的操作在主库执行了5分钟，则从库需要等待主库执行完之后，再同步到从库执行5分钟，导致主从产生5分钟的延时，所以主从环境，需要考虑DDL引发的从库延时是否会对应用有影响。

 

3 Online ddl 的MDL锁潜在影响

        Online ddl在阶段2:Execution执行的一开始以及阶段3:Commit Table Definition 提交表定义会存在获取一个独占mdl锁的情况，这个锁获取虽然很快，但如果ddl涉及的表存在大事务，长事务，慢查询将可能引发会话堵塞

        接下来我们模拟可能引发的会话堵塞

        在session 1 ，执行一个长查询

select id,sleep(600) from sbtest1 limit 1;

        在session 2 ,  执行ddl操作，添加一个新列,会发现操作被堵塞

 

alter table sbtest1 add d bigint null ;

       

         通过show processlist查看，可以发现ddl语句正在等待mdl锁 waiting for table metadata lock

 

         session 3执行一条查询语句，发现也被堵塞，这是因为DDL锁获取的MDL锁，优先级更高，这会导致后面新涉及表的查询，dml操作被DDL所在的会话堵塞，引发数据库一连串的堵塞

select count(*) from sbtest1;

         所以，在执行Online ddl的时候要检查数据库是否有长时间未提交的事务，长查询，以避免ddl操作可能引发潜在的堵塞，强烈建议Online ddl操作在应用空闲窗口执行。

 

4 Online ddl操作期间增量数据的影响

          在Online ddl执行操作期间，后台进程会将增量数据记录到临时日志里面，但这个日志是有大小限制的，初始大小等于参数innodb_sort_buffer_size，最大大小由参数innodb_Online_alter_log_max_size进行控制，默认为128M，当临时日志超过128M时，ddl操作会失败终止。

 

        接下来我们模拟临时日志大小超出的情况

        我们先将参数大小调小为1M，这样可以更容易的复现

set global innodb_sort_buffer_size=1*1024*1024;

set global innodb_Online_alter_log_max_size=1*1024*1024;      

  

        session 1 执行添加列的ddl操作

alter table sbtest1 add e bigint not null default '1111';

        session 2 开启批量数据插入

mysqlslap -uroot -p123456 -P3306 -S /opt/mysql/data/mysql.sock  --concurrency=10 --number-of-queries=10000 --create-schema=sbtest   --query="insert into sbtest.sbtest1(k,c,pad,d) values('11111','11111','11111','111111')" --delimiter=";"

        在session 1 很快就出现空间超过innodb_Online_alter_log_max_size大小的报错

        所以，在执行ddl的过程，还有注意增量数据的大小，在大部分情况下默认128M是够用的，但如果对于dml操作频繁的表，需要评估一下是否增大临时日志大小。

 

5 磁盘空间，IO，CPU资源的影响

        在执行Online ddl的时候要需要注意服务器磁盘空间，io，cpu资源的使用情况，特别是存在rebuild-table操作的时候，由于需要重建表，至少需要表一倍的磁盘空间，并且由于操作的数据量大，将会占用一定的IO,CPU资源

       例如：我们执行重建主键的操作，该操作是需要inplace+rebuild table的，所以我们可以发现在重建表期间，数据目录下面会产生一个#sql开头的临时表，并且将原表的整个数据拷贝到临时表，该操作至少需要原表一倍的磁盘空间以及产生一定的IO，CPU资源消耗

      

alter table sbtest1 drop primary key, add primary key(id);

         所以，对于Online ddl,特别是涉及到rebuild-table的操作，需要注意服务器磁盘空间，io，cpu资源的使用情况。