InnoDB Online DDL

[toc]

Online DDL流程

MySQL 5.6后引入的Online DDL，重建表的流程为：

• 其中，alter语句在启动时候需要获取MDL写锁，但是这个写锁在真正拷贝数据之前就退化成了读锁，所以实现了Online DDL；而降级为读锁的目的是禁止其他线程对这个表同时做DDL

• Online DDL 最耗时的过程是拷贝数据到临时表，这个步骤执行期间可以接受DML操作。所以，相对于整个DDL过程，锁的时间非常短。对业务来说可以认为是Online。

DDL与表结构

既然DDL的作用是改变表结构，逻辑上，InnoDB表中的数据可以理解成按照主键(聚簇索引)顺序存放的，每一行的数据依次排列 (物理上，InnoDB表中的数据按照InnoDB的数据结构B+树进行排列)。

当需要对表增加一列时，会涉及到每一行数据排列的变动，需要重建整张表的数据，可想而知这种变动的成本是高昂的。

DDL类型

• Instant：此变更可以"立刻"完成
• In Place：此变更由InnoDB引擎独立完成，不需要使用Redo log等，可以节省开销
• Rebuild Table：此变更会重建聚簇索引，一般情况下，涉及到数据变更时才需要重建聚簇索引
• Permits Concurrent DML：此变更进行时，是否允许其他DML变更同一张表。此特性关系到变更是否会长时间阻塞业务。
• Only Modifies Metadata：此变更是否只变更元信息，不涉及数据变更。

索引在线DDL操作

列在线DDL操作

• 列操作的在线 DDL 支持
• 支持在线加索引，注意加唯一性约束时，不能有重复列
  

添加列

• 添加一般的列时，会重建表，但不会复制表，允许并发DML，即增删改查；
• 添加自动增量列时，或者设置某列为自增长时，是通过copy表实现的，不允许并发DML,即只能查询，无法增删改，DML会提示 waiting for table metadata lock。数据被大量重组，使其成为一项昂贵的操作。
• 无法瞬间完成，消耗时长与表大小有关。

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INPLACE, LOCK=NONE;

删除一列

• 删除普通列时，会重建表，但不会复制表，允许并发DML，即增删改查；
• 删除列和我们之前提到的增加列情况类似，由于需要改动数据行，MySQL在InnoDB引擎内部需要重建聚簇索引 (按照聚簇索引生成临时表,再取而代之)。同时，为了支持并发的DML操作，还需要维护DDL期间的数据变更日志。可见当数据量较大时，这是一种非常高成本的操作。
• 无法瞬间完成，消耗时长与表大小有关。

ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INPLACE, LOCK=NONE;

重命名列

• 要允许并发DML，请保持相同的数据类型并仅更改列名。
• 当保持相同的数据类型和[NOT] NULL属性时，只需更改列名，就可以始终在线执行操作。
• 可以瞬间完成
• ALGORITHM=INPLACE不支持重命名生成的列。

ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INPLACE, LOCK=NONE;

变更Varchar列长度

• 字符串的字段是以字节为单位存储的，utf8字符集的表一个字符需要三个字节，utf8mb4字符集的表 一个字符需要4个字节。对于小于等于255字节以内的长度可以使用一个byte 存储。大于255个字节的长度则需要使用2个byte存储。
• online ddl in-place 模式(不锁表)只支持字段的字节长度从0到255之间 ，或者256到更大值之间变化，瞬间实现不阻塞读写，不锁表
• 如果修改字段的长度，导致字段的字节长度无法使用 1 byte表示，得使用2个byte才能表示，比如从 240 修改为 256 ，如果在默认字符集为utf8mb4的情况下，varchar(60) 修改为 varchar(64)，则DDL需要以copy模式，也即会锁表，阻塞写操作。
  • utf8字符集如果表字段varchar长度从1到85之间变化的话，改字段的存储字节最大也就是255，一个byte足够存储，是不会锁表的，且瞬间完成。但是varchar的长度从小于等于85直接修改到大于85的话，此时他的字节数以及大于255了，存储到磁盘需要至少2个byte.此时DDL需要以copy模式，即会锁表，阻塞写操作。同理从86到以上变化，因为都是2byte存储，不会锁表。
  • utf8mb4字符集如果表字段varchar长度从1到63之间变化的话，改字段的存储字节最大也就是255，一个byte足够存储，是不会锁表的，且瞬间完成。但是varchar的长度从小于等于63直接修改到大于63的话，此时他的字节数以及大于255了，存储到磁盘需要至少2个byte.此时DDL需要以copy模式，即会锁表，阻塞写操作。
• 另外缩小字段长度不支持以in-place方式，是以copy表方式实现，只要缩短列长度，都会锁表。
• 对于varchar来说，最多能存放的字符个数为65532，varchar(N)，utf8 编码，计算方式如下：
  由于 utf8 的每个字符最多占用 3 个字节。而 MySQL定义行的长度不能超过65535，因此 N 的最大值计算方法为：(65535-1-2)/3。
  减去 1 的原因是实际存储从第二个字节开始； 减去 2 的原因是因为要在列表长度存储实际的字符长度； 除以 3 是因为 utf8 限制； 每个字符最多占用3个字节。

ALTER TABLE ecif.Inf_customer_contact_info MODIFY COLUMN Company_name VARCHAR(256),MODIFY COLUMN Company_name VARCHAR(256),MODIFY COLUMN Company_address_details VARCHAR(256);

特别注意：utf8字符集如果表字段varchar长度从从小于85到大于85的话，例如从varchar(16)到varchar(2048)

• Rds三节点数据库不会阻塞读写，in-place 方式实现，允许并发DML，即增删改查
• Rds高可用数据库不会阻塞读，会阻塞写，copy表实现的

变更列类型

• 变更数据列类型，按照文档描述这是一种无法Inplace的操作，即需要MySQL在server层完成一次表的复制，相比由InnoDB内部完成重建，这种操作需要记录Redo log，占用更多的buffer pool。不过由于在执行过程中，无法并发DML操作，不需要记录DDL期间的变更日志。即便如此，这仍然是一种高成本的操作

ALTER TABLE `t1` MODIFY COLUMN `c1` INTEGER;

表在线DDL操作

、

转换字符集

• 如果新的字符编码不同，则用copy表的方式重建表，可以读，但会阻塞写操作。

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name, ALGORITHM=COPY;

表碎片清理

• 大量增删改的表都可能存在数据页空洞，而把空洞去掉就能达到收缩表空间的目的，耗时慢，5.6之后支持OnlineDDL
• 从 MySQL 5.6.17 开始使用ALGORITHM=INPLACE。ALGORITHM=INPLACE不支持带有FULLTEXT 索引的表，会重建表，但不会copy表，允许并发DML，但整个事务提交时会阻塞整个+实例。

alter table t engine=InnoDB;

数据删除后空间回收机制

在InnoDB存储引擎中，数据都是用B+树的结构组织的。当MySQL触发Delete 操作后，对于InnoDB表数据的表现为：

• 如删除果包含了全表记录，那么所有的数据页都会被标记为可复用，但是磁盘空间大小不会变化
• 如果是记录被删除，同样也是将记录的位置标记为可复用，磁盘大小不发生变化
• Delete不能回收表空间。可以复用，而没被使用的空间，类似空洞

数据页空洞

不止删除数据，插入数据同样可以造成空洞

• 如果数据是随机插入（而不是按索引递增顺序插入），可能会造成索引的数据页分裂，页分离完成后，可能在页末尾留下空洞
• 更新数据同样可能造成空洞，更新索引上的值，可以理解为删除旧值再插入新值。

监控在线DDL的进度

开启PS统计：

mysql > use performance_schema;
mysql > select * from set_instrucments where name like 'stage/innodb/alter%';
mysql > select * from set_consumers where name like '%stages%';

mysql > update set_instrucments set enabled = 'YES' where name like 'stage/innodb/alter%';
mysql > update set_consumers set enabled = 'YES' where name like '%stages%';

查看 DDL 进度

select event_name,work_completed,work_estimated from events_stages_current;

# 当前进度
# 运行时间 = 当前时间 - 语句的开始时间
# 剩余时间估算 = 运行时间 * （1-当前进度）
# 关联键 = 进程号 + 父事件ID
select 
    stmt.SQL_TEXT as sql_text,
    concat(WORK_COMPLETED,'/',WORK_ESTIMATED) as progress
    (stage.TIMER_END - stmt.TIMER_START) / 1e12 as current_seconds,
    (stage.TIMER_END - stmt.TIMER_START) / 1e12 * (WORK_ESTIMATED-WORK_COMPLETED) / WORK_COMPLETED as remaining_seconds
    from events_stages_current stage, events_statements_current stmt where stage.THREAD_ID = stmt.THREAD_ID and stage.NESTING_EVENT_ID = stmt.EVENT_ID;

Optimize|Analyze|Alter区别

Optimize table、Analyze table、alter table在重建表的区别为

• alter table t engine=InnoDB ，即 recreate 表
• analyze table t ，即只对表的索引信息重新统计，不修改数据，过程加MDL读锁；不recreate表
• optimize table t，等同于 recreate + analyze 表