官方online ddl
一、5.6版本online DDL
| 操作 | In Place | Rebuilds Table | Concurrent DML | Only Modifies Metadata | 说明 |
|---|---|---|---|---|---|
| add/create secondary index | yes | No | yes* | no | 当表上有FULLTEXT索引除外，需要锁表，阻塞写 |
| drop index | yes | no | yes | yes | - |
| add fulltext index | yes | no | no | no | - |
| add primary key | yes* | yes | yes | no | 即使in-place，但需要copy data，不过效率比copy方式高 |
| drop primary key | no | yes | no | no | 即使in-place，但需要copy data，不过效率比copy方式高 |
| Dropping a primary key and adding another | Yes | Yes | Yes | No | - |
| add column | yes | yes | yes* | no | 自增列阻塞写 |
| drop column | yes | yes | yes | no | - |
| Rename a column | yes | no | yes* | yes | 只改变列名不改变类型才支持写 |
| Reorder columns | yes | yes | yes | no | - |
| Set default value for a column | yes | no | yes | yes | - |
| Change data type of column | no | yes | no | no | - |
| 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 | - |
| Adding a foreign key constraint | yes* | no | yes | yes | INPLACE只有在foreign_key_checks=off |
| Dropping a foreign key constraint | yes | no | yes | yes | - |
| Changing the ROW_FORMAT | yes | yes | yes | no | — |
| Changing the KEY_BLOCK_SIZE | yes | yes | yes | no | — |
| Convert character set | no | yes | no | no | - |
| optimize table | yes* | yes | yes | no | 从5.6.17支持in-place，但当带有fulltext index的表用copy table方式并且阻塞写 |
| alter table...engine=innodb | yes* | yes | yes | no | 从5.6.17支持in-place，当带有fulltext index的表用copy table方式并且阻塞写 |
| Renaming a table | yes | no | yes | yes | - |
二、5.7版本online DDL（仅突出与5.6不同的地方，列出如下（未列出的同5.6））
| 操作 | In Place | Rebuilds Table | Concurrent DML | Only Modifies Metadata | 说明 |
|---|---|---|---|---|---|
| Renaming an index | yes | no | yes | yes | 5.7新增 |
| Adding a SPATIAL index | Yes | No | No | No | 5.7新增 |
| Extending VARCHAR column size | Yes | No | Yes | Yes | 5.7新增，只能在[0-255],[256-~]字节区间扩大 |
1、扩展varchar长度测试
1.1）varchar从大变小
| 操作 | In Place | Rebuilds Table | Concurrent DML | Only Modifies Metadata | 说明 |
|---|---|---|---|---|---|
| varchar从大变小 | no | yes | no | no | 阻塞DML |
1.2) varchar从小变大
对于大小为0到255字节的VARCHAR列，需要一个长度字节来编码该值。对于大小为256或更多的字节的VARCHAR列，需要两个长度字节。
因此，in-place算法更改表只支持将VARCHAR列的大小从0字节增加到255字节，或者从256字节增加到更大的值。
in-place算法不支持将VARCHAR列的大小从小于256字节增加到等于或大于256字节的大小，因为在这种情况下，所需长度字节的数量从1变化到2，只能通过copy方式实现。
比如将VARARAR（255字节）更改为VARCHAR（256字节），只能copy算法并且阻塞写
-------------------测试---------------------
说明：采用utf8，如果存中文字符，一个字符需要3个字节，因此255字节对应最大字符数是varchar(85),也就是[0-255]对应varchar(0-85),[256-~]对应varchar(86-~)
表结构：
create table varchar_test(
c1 varchar(1) not null default '0'
);
1）采用online方式，扩大到85字符---支持
alter table varchar_test change c1 c1 varchar(85) not null default '0',ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
2）采用online方式，扩大到86字符---不支持
alter table varchar_test change c1 c1 varchar(86) not null default '0',ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
3）采用copy方式
alter table varchar_test change c1 c1 varchar(86) not null default '0',ALGORITHM=copy;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
4）采用online方式，从86扩大到259字符---支持
alter table varchar_test change c1 c1 varchar(259) not null default '0',ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
三、8.0版本online DDL(仅列出与5.7不同的地方)
| Operation | Instant | In Place | Rebuilds Table | Permits Concurrent DML | Only Modifies Metadata | desc |
|---|---|---|---|---|---|---|
| add column | yes* | yes | yes | yes* | no | 自增列阻塞写 |
| modify index type | yes | yes | no | yes | yes | - |
3、实现原理
目标表T1上MDL（SHARED_NO_WRITE）,阻塞所有写操作
判断是否符合在线加字段的条件，如符合，执行步骤3，否则按照官方方式来新增字段。
创建跟目标表T1同构的临时表frm文件S1.frm（含新增字段）
目标表T1的字典锁升级为排它锁，所有关于T1表的所有读写操作被阻塞
修改T1表InnoDB内部数据字典信息，增加新字段。
将S1.frm重命名为T1.frm
释放表T1的字典锁
2、支持instant的ddl
Change index option
Rename table (in ALTER way)
SET/DROP DEFAULT
MODIFY COLUMN
Add/drop virtual columns
Add columns– We call this instant ADD COLUMN
4、使用最新算法instant条件：
1）不能合并写到其他不支持instant算法的alter 语句后面；
alter table sbtest1 add index idx_2(k),add c5 varchar(10) not null default '0' ,ALGORITHM=instant;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
2) 不支持before和after关键字，只能默认的加到最后一列；
alter table sbtest1 add c2 varchar(10) not null default '0';
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
alter table sbtest1 add c4 varchar(10) not null default '0' after id;
Query OK, 0 rows affected (26.42 sec)
Records: 0 Duplicates: 0 Warnings: 0
alter table sbtest1 add c5 varchar(10) not null default '0' after id,ALGORITHM=instant;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
3）不支持ROW_FORMAT=COMPRESSED类型的表；
root:sbtest> show table status like 'sbtest1'\G
*************************** 1. row ***************************
Name: sbtest1
Engine: InnoDB
Version: 10
Row_format: Compressed
Rows: 9906340
Avg_row_length: 73
Data_length: 724033536
Max_data_length: 0
Index_length: 101171200
Data_free: 3145728
Auto_increment: 20000020
Create_time: 2018-10-21 15:48:22
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: max_rows=100000000 row_format=COMPRESSED
Comment:
1 row in set (0.00 sec)
root:sbtest>
root:sbtest> alter table sbtest1 add c5 varchar(10) not null default '0',ALGORITHM=instant;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
root:sbtest>
4）表上有FULLTEXT index，不支持instant算法；
5）不支持临时表加字段；
6）如果表上存在大事务，instant也会被阻塞
六、官方online ddl限制
1、大事务可能引起MDL锁（即使是8.0.12 instant方式也是需要获取MDL锁的）
session 1：
alter table sbtest1 ALGORITHM=INPLACE,drop column c2 ;
session2：写入事务未提交
set autocommit=0;
begin;
insert into sbtest1(c) values("session2"); --当执行后，session 1将被阻塞，状态有alter table -->Waiting for table metadata lock
session3:
insert into sbtest1(c) values("session2"); --被阻塞
结论发现：
dbadmin:sbtest> show processlist;
+----------+---------+-----------+--------+---------+------+---------------------------------+------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+---------+-----------+--------+---------+------+---------------------------------+------------------------------------------------------+
| 78213439 | dbadmin | localhost | sbtest | Query | 0 | starting | show processlist |
| 78213440 | root | localhost | sbtest | Query | 1763 | Waiting for table metadata lock | alter table sbtest1 ALGORITHM=INPLACE,drop column c2 |
| 78213441 | dbadmin | localhost | sbtest | Query | 1373 | Waiting for table metadata lock | insert into sbtest1(c) values("darren") |
经过漫长的时间，发现session2 插入语句被回滚了（因为客户端连接超过30分钟断开导致未提交的事务自动回滚），session 1和session 3执行成功。
2、online ddl无法暂停和进度监控
3、online ddl大表可能导致从库延迟严重
