官方online ddl

时间：2021-10-21 21:46:44

一、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限制

官方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大表可能导致从库延迟严重

相关文章