online-ddl

数据库版本：5.7.20

Online DDL选项

• ALGORITHM={COPY|INPLACE}
  指定DDL执行时对表的操作方式。首选是INPLACE，但并非所有的语句都支持INPLACE，需要根据DDL语句类型决定。

• LOCK={NONE|SHARED|DEFAULT|EXCLUSIVE}

  • NONE：允许并发查询和DML操作；
  • SHARED：允许并发查询，但不允许DML操作；
  • DEFAULT：允许尽可能多的并发查询或DML操作(或两者都允许)，没指定LOCK选项默认就为DEFAULT；
  • EXCLUSIVE：不允许并发查询和DML操作。
• 指定DDL执行时对表锁定方式。默认情况下MySQL在表执行DDL时会尽量使用最少的锁定，LOCK选项可以为DDL语句指定执行更为严格的锁定方式，一旦指定的锁级别低于DDL语句执行所需的锁级别，则DDL语句会执行失败。

首先准备实验数据集pt_osc_1表，并插入100多万条数据进行测试，接下来按照官网上的描述来一步步测试

索引操作

Operation	In Place （原表操作）	Rebuilds Table （重建表操作）	Permits Concurrent DML （允许并发DML）	only Modifies Metadata (仅修改元数据：指仅修改数据字典中的元数据，所以无需加排它锁)
Creating or adding a secondary index( 创建或添加一个二级索引)	Yes	No	Yes	No（在DDL的初始准备和最后结束两个阶段时通常需要加排他MDL锁（metadata lock，元数据锁））
Dropping an index（删除索引）	Yes	No	Yes	Yes
Renaming an index（重命名索引）	Yes	No	Yes	Yes
Adding a FULLTEXT index（添加全文索引）	Yes*	No*	No	No
Adding a SPATIAL index（创建空间索引）	Yes	No	No	No
Changing the index type（修改索引类别）	Yes	No	Yes	Yes

对于索引的操作：

执行添加索引操作：
alter table pt_osc_1 add index idx_name(name);
在另一个线程执行DML操作：
insert into pt_osc_1(name,age) values("zhangsan",83);（发现没有堵塞）
查看此时线程：
altering table | alter table pt_osc_1 add index idx_name(name)

可以看到对于索引的增加并没有产生临时表，所以采用的是inplace方式操作。这时候并没有阻塞DML操作。

接下来强制采用copy方式进行DDL操作：

alter table pt_osc_1 add index idx_age(age),algorithm=copy;
在另一个线程进行DML操作：
insert into pt_osc_1(name,age) values("zhangsan",83);

我们查看此时的线程：

可以看到 此时alter语句正在将原表数据拷贝到临时表中，而我们的DML操作也阻塞了。而且因为拷贝操作所带来的I/O，也降低了alter操作的效率，从这里可以看出inplace要比copy方式好很多，同时也知道了普通二级索引的增加和删除默认采用inplace方式。

接下来我们看看关于索引的操作都有哪些是默认采用inplace方式，哪些是采用alter方式

先来看看全文索引：

alter table pt_osc_1 add fulltext index ful_idx_name(name);

通过查看线程也可以看到DML操作被阻塞

加字段的操作也会堵塞DML: alter table pt_osc_1 add column sex enum('man','women') default 'man';

再添加全文索引

可以看到这一次并没有警告，这就是全文索引的特别的地方，它在这张表添加第一个全文索引时（没有用户定义的FTS_DOC_ID列）采用的是copy方式，之后采用的是In Place方式

重命名索引：alter table pt_osc_1 rename index ful_idx_name to up_ful_idx_name;

创建空间索引：

1.CREATE TABLE geom (g GEOMETRY NOT NULL);

2.ALTER TABLE geom ADD SPATIAL INDEX(g), ALGORITHM=INPLACE, LOCK=SHARED;

更改索引类型 (USING {BTREE | HASH})：

ALTER TABLE tbl_name DROP INDEX i1, ADD INDEX i1(key_part,...) USING BTREE, ALGORITHM=INPLACE;

主键操作

Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a primary key	Yes*	Yes*	Yes	No
Dropping a primary key	No	Yes	No	No
Dropping a primary key and adding another	Yes	Yes	Yes	No

建表语句：
CREATE TABLE `pt_osc_2` (
  `id` int(10) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `idd` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

添加主键：alter table pt_osc_2 add primary key(id),algorithm=inplace,lock=None;某些情况下会出现无法使用INPLACE方式

删除主键：alter table pt_osc_2 drop primary key,algorithm=copy;仅ALGORITHM = COPY支持删除主键而不在同一ALTER TABLE语句中添加新的主键。

删除并添加主键:alter table pt_osc_2 drop primary key,add primary key(id),algorithm=inplace,lock=None;(数据大量重组，操作成本很高)

字段操作

Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a column	Yes	Yes	Yes*	No
Dropping a column	Yes	Yes	Yes	No
Renaming a column	Yes	No	Yes*	Yes
Reordering columns（重排序字段）	Yes	Yes	Yes	No
Setting a column default value	Yes	No	Yes	Yes
Changing the column data type	No	Yes	No	No
Extending VARCHAR column size	Yes	No	Yes	Yes
Dropping the column default value	Yes	No	Yes	Yes
Changing the auto-increment value	Yes	No	Yes	No*
Making a column NULL	Yes	Yes*	Yes	No
Making a column NOT NULL	Yes*	Yes*	Yes	No
Modifying the definition of an ENUM or SET column （修改枚举类型的定义值）	Yes	No	Yes	Yes

添加字段：

添加字段：
ALTER TABLE pt_osc_1 ADD COLUMN title varchar(20), ALGORITHM=INPLACE, LOCK=NONE;
报错：
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY.
删除一个全文索引尝试：
alter table pt_osc_1 drop index up_ful_idx_name;
看线程发现被堵塞
个人认为原因：添加字段的sql报错之后并没有释放锁
删除索引后继续添加字段依旧报错
删除第二个全文索引：
 alter table pt_osc_1 drop index ful_idx_info;
继续添加字段：ALTER TABLE pt_osc_1 ADD COLUMN title varchar(20), ALGORITHM=INPLACE, LOCK=NONE;
发现此时可以运行成功，且DML不堵塞

接下来实验加自增字段：
 alter table pt_osc_2 add column idd int(10) auto_increment, add index idx_idd(idd),ALGORITHM=INPLACE, LOCK=NONE;
报错：ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Adding an auto-increment column requires a lock. Try LOCK=SHARED.

添加自动增量列时不允许并发DML。 数据被大量重组，使其操作成本很高。 至少需要ALGORITHM = INPLACE，LOCK = SHARED。有全文索引的时候仅支持COPY方式

删除字段：alter table pt_osc_2 drop column age, ALGORITHM=INPLACE, LOCK=NONE;

重命名字段：alter table pt_osc_2 change age age_new int(10),ALGORITHM=INPLACE, LOCK=NONE;

要允许并发DML，请保持相同的数据类型，仅更改列名。重命名属于外键约束的列。 外键定义会自动更新为使用新的列名。 重命名参与外键的列仅适用于ALGORITHM = INPLACE。

重排序字段： alter table pt_osc_2 modify column age_new int(10) first,ALGORITHM=INPLACE, LOCK=NONE;

修改字段类型： alter table pt_osc_2 change age_new age_new bigint,ALGORITHM=COPY；只能用ALGORITHM=COPY；

扩展varchar字段大小：

1.alter table pt_osc_2 change column name name varchar(85),algorithm=INPLACE,lock=None;

2.alter table pt_osc_2 change column name name varchar(86),algorithm=COPY,lock=SHARED;

（注：这里官方给的长度分界线为255,0-255占一个字节，所以在这个范围内可以用INPLACE方式，跨越字节的时候则需要用COPY方式，但是这个和字符集有关，例如我的数据库采用的是UTF8，255的长度需要三个字节存储，那么255/3=85，即85为字节的分界线，减小VARCHAR长度的时候只能用COPY）

字段指定默认值：alter table pt_osc_2 alter column name set default 'cc',algorithm=INPLACE,lock=none;（仅修改表元数据。 默认列值存储在表的.frm文件中）

删除字段默认值：alter table pt_osc_2 alter column name drop default,algorithm=INPLACE,lock=none;

修改自增列值：alter table pt_osc_2 auto_increment=10,algorithm=inplace,lock=none;

指定字段为null：alter table pt_osc_2 modify column name varchar(83) null,algorithm=inplace,lock=none;

指定字段为not null：alter table pt_osc_2 modify column name varchar(83) not null,algorithm=inplace,lock=none;

修改枚举类型的定义值：alter table pt_osc_2 modify column sex enum('man','women','man-women') default 'man',algorithm=inplace,lock=none;

组合字段操作（组合字段：定义一个字段，值是其他字段值的组合）

Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a STORED column	No	Yes	No	No
Modifying STORED column order	No	Yes	No	No
Dropping a STORED column	Yes	Yes	Yes	No
Adding a VIRTUAL column	Yes	No	Yes	Yes
Modifying VIRTUAL column order	No	Yes	No	No
Dropping a VIRTUAL column	Yes	No	Yes	Yes

添加存储组合字段：alter table pt_osc_2 add column (age_2 int(10) generated always as (age+1) stored),algorithm=copy;

修改存储组合字段顺序：alter table pt_osc_2 modify column age_2 int(10) generated always as (age+1) stored first,algorithm=copy;

删除存储组合字段顺序：alter table pt_osc_2 drop column age_2,algorithm=inplace,lock=none;

添加虚拟存储组合字段：alter table pt_osc_2 add column (age_2 int(10) generated always as (age+1) virtual),algorithm=inplace,lock=none;

（注：添加虚拟字段不能与其他alter table操作结合使用，且对于分区表不能采用inplace方式）

修改虚拟存储组合字段顺序：alter table pt_osc_2 modify column age_2 int(10) generated always as (age+1) virtual first,algorithm=copy;

删除虚拟存储组合字段： alter table pt_osc_2 drop column age_2,algorithm=inplace,lock=none;（注：和添加虚拟组合字段一样）

外键操作（外键不常用，这里就不详细研究了）

Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a foreign key constraint	Yes*	No	Yes	Yes
Dropping a foreign key constraint	Yes	No	Yes	Yes

添加外键：alter table pt_osc_2 add constraint fk_id foreign key(id_fk) references pt_osc_2_fk(id_fk),algorithm=inplace;

（报错：ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY.

注：通过报错信息可以看到，当foreign_key_checks=OFF时可以用INPLACE，反之则只能用COPY）

删除外键：alter table pt_osc_2 drop foreign key fk_id;

表操作

Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Changing the ROW_FORMAT（修改行格式）	Yes	Yes	Yes	No
Changing the KEY_BLOCK_SIZE（ 修改索引键块大小）	Yes	Yes	Yes	No
Setting persistent table statistics（ 设计永久表统计信息选项）	Yes	No	Yes	Yes
Specifying a character set（指定字符集）	Yes	Yes*	No	No
Converting a character set（转换字符集）	No	Yes*	No	No
Optimizing a table	Yes*	Yes	Yes	No
Rebuilding with the FORCE option（  使用FORCE重建表）	Yes*	Yes	Yes	No
Performing a null rebuild（使用null重建表）	Yes*	Yes	Yes	No
Renaming a table	Yes	No	Yes	Yes

 修改行格式：ALTER TABLE tbl_name ROW_FORMAT = row_format, ALGORITHM=INPLACE, LOCK=NONE;

修改索引键块大小：ALTER TABLE tbl_name KEY_BLOCK_SIZE = value, ALGORITHM=INPLACE, LOCK=NONE;

设计永久表统计信息选项：ALTER TABLE tbl_name STATS_PERSISTENT=0, STATS_SAMPLE_PAGES=20, STATS_AUTO_RECALC=1, ALGORITHM=INPLACE, LOCK=NONE;

（注：仅修改表元数据。）

指定字符集：alter table pt_osc_2 CHARACTER SET = latin1, ALGORITHM=INPLACE, LOCK=NONE;

（注：如果新的字符编码不同，则重建表。）

转换字符集：alter table pt_osc_2 convert to CHARACTER SET utf8, ALGORITHM=INPLACE, LOCK=NONE;

（注：如果新的字符编码不同，则重建表。）

 优化表：OPTIMIZE TABLE pt_osc_3;

（注：具有FULLTEXT索引的表不支持就地操作。 该操作使用INPLACE算法，但不允许使用ALGORITHM和LOCK语法。）

 使用FORCE重建表：alter table pt_osc_2 force,algorithm=inplace,lock=none;

（注：从MySQL 5.6.17开始使用ALGORITHM = INPLACE。 具有FULLTEXT索引的表不支持ALGORITHM = INPLACE。）

使用null重建表（不做改动重建表，innodb引擎常用的表碎片优化方式）：

alter table pt_osc_2 engine=innodb,algorithm=inplace,lock=none;

(注：从MySQL 5.6.17开始使用ALGORITHM = INPLACE。 具有FULLTEXT索引的表不支持ALGORITHM = INPLACE。)

重命名表：alter table a rename to b,algorithm=inplace,lock=exclusive;

（注：官方给的是lock=none,但是我用5.7.20测试的是lock=EXCLUSIVE，这里感觉很奇怪）

 

 表空间操作

Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Enabling or disabling file-per-table tablespace encryption（ 开启或禁止独立表空间加密）	No	Yes	No	No

主要涉及独立表空间加密的Online DDL操作：ALTER TABLE tbl_name ENCRYPTION='Y', ALGORITHM=COPY; 

表分区操作

分区子句(Partitioning Clause)	原表操作(In Place)	允许DML操作(Permits DML)	说明(Notes)
PARTITION BY	NO	NO	只允许algorithm=copy, LOCK={DEFAULT|SHARED|EXCLUSIVE}
ADD PARTITION	NO	NO	仅允许ALGORITHM = DEFAULT，LOCK = DEFAULT。 不复制按RANGE或LIST分区的表的现有数据。 允许对由HASH或LIST分区的表进行并发查询。 MySQL在持有共享锁的同时复制数据。
DROP PARTITION	NO	NO	仅允许ALGORITHM = DEFAULT，LOCK = DEFAULT。 不复制按RANGE或LIST分区的表的现有数据。
DISCARD PARTITION	NO	NO	仅允许ALGORITHM = DEFAULT，LOCK = DEFAULT
IMPORT PARTITION	NO	NO	仅允许ALGORITHM = DEFAULT，LOCK = DEFAULT
TRUNCATE PARTITION	YES	YES	不复制现有数据。 它只是删除行； 它不会更改表本身或其任何分区的定义。
COALESCE PARTITION	NO	NO	仅允许ALGORITHM = DEFAULT，LOCK = DEFAULT。 允许对由HASH或LIST分区的表进行并发查询，因为MySQL在持有共享锁的同时会复制数据。
REORGANIZE PARTITION	NO	NO	仅允许ALGORITHM = DEFAULT，LOCK = DEFAULT。 允许对由LINEAR HASH或LIST分区的表进行并发查询。 MySQL持有共享的元数据锁时从受影响的分区复制数据。
EXCHANGE PARTITION	YES	YES
ANALYZE PARTITION	YES	YES
CHECK PARTITION	YES	YES
OPTIMIZE PARTITION	NO	NO	ALGORITHM和LOCK子句被忽略。 重建整个表。
REBUILD PARTITION	NO	NO	仅允许ALGORITHM = DEFAULT，LOCK = DEFAULT。 允许对由LINEAR HASH或LIST分区的表进行并发查询。 MySQL持有共享的元数据锁时从受影响的分区复制数据。
REPAIR PARTITION	YES	YES
REMOVE PARTITIONING	NO	NO	允许ALGORITHM = COPY，LOCK = {DEFAULT | SHARED | EXCLUSIVE}

Online DDL过程

Online DDL主要有PREPARE(准备)、EXECUTE(执行)和COMMIT(提交)三个阶段，如下：

• PREPARE：
  • 创建新的临时frm文件；
  • 持有EXCLUSIVE-MDL锁，禁止读写操作；
  • 根据ALTER类型，确定执行方式(copy,Online-Rebuilds,Online-No-Rebuilds)；
  • 更新数据字典的内存对象；
  • 分配row_log对象记录增量(Rebuilds需要)；
  • 生成新的临时ibd文件(Rebuilds需要)。
• EXECUTE：
  • 降级EXCLUSIVE-MDL锁，允许读写；
  • 记录执行期间产生的DML增量到row_log中(Rebuilds需要)；
  • 扫描old_table的聚集索引中每一条记录record；
  • 遍历新表的聚集索引和二级索引，逐一处理；
  • 根据record构造对应的索引项；
  • 将构造的索引项插入sort_buffer块中；
  • 将sort_buffer块插入到新的索引中；
  • 将row_log中的记录应用到新临时表中，应用到最后一个block；
• COMMIT：
  • 升级到EXECLUSIVE-MDL锁，禁止读写；
  • 重做row_log中最后一部分的增量；
  • 更新InnoDB的数据字典表；
  • 提交事务，写InnoDB redo日志；
  • 修改统计信息；
  • RENAME临时的ibd和frm文件；
  • 执行变更完成。

row_log记录了DDL执行期间产生的DML操作，这保证了变更期间表的并发性，通过以上过程可以看出在EXECUTE(执行)阶段表允许读写操作，操作记录在row_log中，在最后阶段应用到新表当中，保证了数据的完整性。

Online DDL涉及参数

• old_alter_table

属性(Property)	值(Value)
命令行格式(Command-Line Format)	--old-alter-table
系统变量格式(System Variable)	old_alter_table
作用范围(Scope)	全局、会话
动态参数(Dynamic)	是
类型(Type)	布尔型
默认值(Default Value)	OFF

指定是否使用早期版本的DDL方式，默认为OFF，为动态参数，可以全局和会话级别修改。指定表DDL的执行过程当中采用COPY方式生成临时表复制数据。

• innodb_online_alter_log_max_size

属性(Property)	值(Value)
命令行格式(Command-Line Format)	--innodb-online-alter-log-max-size=#
系统变量格式(System Variable)	innodb_online_alter_log_max_size
作用范围(Scope)	全局
动态参数(Dynamic)	是
类型(Type)	数值型
默认值(Default Value)	134217728
最小值(Minimum Value)	65536
最大值(Maximum Value)	2**64-1

指定Online DDL执行期间产生临时日志文件的最大大小，单位字节，默认大小为128MB。日志文件记录的是表在DDL期间的数据插入、更新和删除信息(DML操作)，一旦日志文件超过该参数指定值时，DDL执行就会失败并回滚所有未提交的当前DML操作，所以，当执行DDL期间有大量DML操作时可以提高该参数值，但同时也会增加DDL执行完成时应用日志时锁定表的时间。

Online DDL注意事项

对于线上环境的MySQL来说，任何类型的DDL都要十分谨慎，最好在语句执行之前可以分析下语句所使用的方式以及预估判断下影响的时长，尽量选择在业务访问的低峰期进行操作，主要有以下几点需要注意：

• 空间需求
  • 临时日志文件大小(innodb_online_alter_log_max_size)
    当DDL执行过程当中允许并发执行DML操作时的日志大小需求。
  • 临时排序文件大小(tmpdir)
    当DDL执行过程中表需要rebuild时临时排序文件是放在tmpdir指定的路径下的，需要保证该路径下的磁盘空间充足。临时排序文件都足够容纳所有二级索引以及聚簇索引的主键列，最终合并到新表或索引后，临时排序文件会被删除。在MySQL5.7.11及之后版本当中新增系统参数innodb_tmpdir专门用来指定Online DDL产生排序文件的路径。
  • 临时中间表文件大小
    当有些DDL执行过程中表需要rebuild时会在当前表所在路径下产生临时中间表文件，临时中间表文件大小可能需要与原表大小一致，在DDL执行过程当中产生。

• 合并拆分同表的DDL操作
  早期不支持Online DDL时通常将同一张表中的多个DDL合并一起执行，以便减少多次rebuild表带来的性能消耗；
  现在Online DDL特性出现之后，可以通过COPY方式和INPLACE方式来进行分类并合并分组。其中INPLACE方式又可以根据是否rebuild表来进行分组合并，尽量减少DDL对系统的CPU、I/O资源的影响。

• 对于一些大表进行Online DDL并需要重建表的操作
  • 现在还没有机制可以做到暂停Online DDL的操作或者限制Online DDL对服务器CPU、I/O资源的使用；
  • 如果Online DDL执行失败，则回滚有可能会是一项昂贵的操作；
  • 执行时间过长的Online DDL可能会导致主从复制的延迟。因为主库在执行DDL时可能允许DML并发操作，而在从库只能在执行完DDL语句之后再进行应用DML语句操作。

借鉴文章：https://www.cnblogs.com/lonelyxmas/p/10384605.html

官网文章位置：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html