Mysql在线DDL

1.  Mysql各版本DDL方式

1.1 MysqlDDL演进

　　当mysql某个业务表上有未提交的活动事务的时候，你去执行在线DDL，这相当危险，直接会被卡住，show processlist里面会显示这个DDL遇到了MDL锁等待，即“waiting for table metadata lock"，此时如果你去喝咖啡去了。。。杯具就发生了，因为此时这个业务表连select都会被阻塞。
　　mysql在5.6官方文档里面说自己可以支持大部分在线DDL了，包括常见的加字段、加索引、改字段等等。但是要注意：这里所谓的支持在线DDL，指的是针对死表（执行DDL前表上没有未提交的事务）的情况，即在mysql5.6里对死表执行DDL的时候，不会出现mysql5.5的”copy to tmp table"的情况，只是提示“altering table"，在DDL执行期间不会像mysql5.5一样阻塞这个表上的DML。
　　但是生产系统的繁忙业务表，肯定是活表（表上每时每刻都有未提交的事务），所以在上面搞在线DDL则是另外的话题了，这里不详细解释。
　　但是有一点，mysql没有oracle做得好，mysql一些危险的sql，在执行的时候，会直接卡住，没有任何提示
　　5.6 online ddl推出以前，执行ddl主要有两种方式copy方式和inplace方式，inplace方式又称为(fast index creation)。相对于copy方式，inplace方式不拷贝数据，因此较快。但是这种方式仅支持添加、删除索引两种方式，而且与copy方式一样需要全程锁表，实用性不是很强。
　　在Mysql5.6之后 online方式实质也包含了copy和inplace方式，对于不支持online的ddl操作采用copy方式，比如修改列类型，删除主键，修改字符集等，这些操作都会导致记录格式发生变化，无法通过简单的全量+增量的方式实现online；对于inplace方式，mysql内部以“是否修改记录格式”为基准也分为两类，一类需要重建表(重新组织记录)，比如optimize table、添加索引、添加/删除列、修改列NULL/NOT NULL属性等；另外一类是只需要修改表的元数据，比如删除索引、修改列名、修改列默认值、修改列自增值等。Mysql将这两类方式分别称为rebuild方式和no-rebuild方式。更多关于哪些DDL是否可以inplace的内容可以参考官方文档：http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html。online ddl主要包括3个阶段，prepare阶段，ddl执行阶段，commit阶段，rebuild方式比no-rebuild方式实质多了一个ddl执行阶段。
　　在早期的mysql版本中进行ddl操作，首先需要创建一张和原始表结构一样的临时表，然后将基表数据全部拷贝到临时表，最后再重命名表。但是整个创建过程中基表是不可写的，会阻塞所有的dml操作，并且会消耗一倍的存储空间。mysql5.6版本之后开始支持onlineddl操作，在执行创建或者删除操作的同时，将dml操作日志写入到缓存中，待完成后再将缓存操作应用到表上，以此达到数据的一致性。
　　Mysql 5.6 虽然引入了Online DDL，但是并不是修改表结构的时候，一定不会导致锁表，在一些场景下还是会锁表的，比如

　　　　①某个慢SQL或者比较大的结果集的SQL在运行，执行ALTER TABLE时将会导致锁表发生；

　　　　②存在一个事务在操作表的时候，执行ALTER TABLE也会导致修改等待； 

具体在线DDL操作是否会有锁表操作，参考以下官网链接：
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html#online-ddl-primary-key-operations

 

1.2 MySQL各版本，对于DDL的处理方式是不同的，主要有三种：

　　①：Copy Table方式： 这是InnoDB最早支持的方式。顾名思义,通过临时表拷贝的方式实现的。新建一个带有新结构的临时表，将原表数据全部拷贝到临时表，然后Rename，完成创建操作。这个方式过程中，原表是可读的，不可写。但是会消耗一倍的存储空间。

　　②：Inplace方式：这是原生MySQL 5.5，以及innodb_plugin中提供的方式。所谓Inplace，也就是在原表上直接进行，不会拷贝临时表。但是这种方式仅支持添加、删除索引两种方式。相对于Copy Table方式，这比较高效率。原表同样可读的，但是不可写。

　　③：Online方式：这是MySQL 5.6以上版本中提供的方式，也是今天我们重点说明的方式。无论是Copy Table方式，还是Inplace方式，原表只能允许读取， 不可写。对应用有较大的限制，因此MySQL最新版本中，InnoDB支持了所谓的Online方式DDL。与以上两种方式相比，online方式支持DDL时不仅可 以读，还可以写，对于dba来说，这是一个非常棒的改进。

 

1.3 MySQL5.7中online ddl：（algorithm=inplace）

　　ALGORITHM=INPLACE，可以避免重建表带来的IO和CPU消耗，保证ddl期间依然有良好的性能和并发。 

更优秀的解决方案，在当前表加索引，步骤：
1.创建索引(二级索引)数据字典
2.加共享表锁，禁止DML，允许查询
3.读取聚簇索引，构造新的索引项，排序并插
入新索引
4.等待打开当前表的所有只读事务提交
5.创建索引结束

 

　　ALGORITHM=COPY，需要拷贝原始表，所以不允许并发DML写操作，可读。这种copy方式的效率还是不如 inplace ，因为前者需要记录undo和redo log，而且因为临时占用buffer pool引起短时间内性能受影响。

通过临时表创建索引，需要多一倍存储，还有更多的IO，步骤：
1.新建带索引（主键索引）的临时表
2.锁原表，禁止DML，允许查询
3.将原表数据拷贝到临时表
4.禁止读写,进行rename，升级字典锁
5.完成创建索引操作

 

　　Lock参数设置：

LOCK=DEFAULT：默认方式，MySQL自行判断使用哪种LOCK模式，尽量不锁表
LOCK=NONE：无锁：允许Online DDL期间进行并发读写操作。如果Online DDL操
作不支持对表的继续写入，则DDL操作失败，对表修改无效
LOCK=SHARED：共享锁：Online DDL操作期间堵塞写入，不影响读取
LOCK=EXCLUSIVE：排它锁：Online DDL操作期间不允许对锁表进行任何操作

 

　　其它参数

①：In-Place为Yes是优选项，说明该操作支持INPLACE 

②：Copies Table为No是优选项，因为为Yes需要重建表。大部分情况与In-Place是相反的

③：Allows Concurrent DML?为Yes是优选项，说明ddl期间表依然可读写，可以指定 LOCK=NONE（如果操作允许的话mysql自动就是NONE）

④：Allows Concurrent Query?默认所有DDL操作期间都允许查询请求，放在这只是便于参考

 

1.4 Mysql DDL参数

　　因为结合在线DDL对磁盘要求。那么就有以下一些文件和在线DDL相关。    

• 临时日志文件的空间

联机DDL操作创建索引或更改表时，临时日志文件记录并发DML。临时日志文件将根据需要扩展， innodb_sort_buffer_size最大值由指定 innodb_online_alter_log_max_size。如果临时日志文件超出大小限制，则联机DDL操作将失败，并且未提交的并发DML操作将回滚。较大的 innodb_online_alter_log_max_size 设置允许在联机DDL操作期间使用更多DML，但是当表被锁定以应用记录的DML时，它也会延长DDL操作结束时的时间。

如果该操作花费很长时间，并且并行DML修改了表，以至于临时日志文件的大小超过的值 innodb_online_alter_log_max_size，则联机DDL操作将失败并显示 DB_ONLINE_LOG_TOO_BIG错误。

• 临时排序文件的空间

重建表的在线DDL操作将临时排序文件写入MySQL临时目录（$TMPDIR在Unix，%TEMP% Windows或Windows Server 2003指定的目录中） --tmpdir）。临时排序文件不在包含原始表的目录中创建。每个临时排序文件都足够大，可以容纳一列数据，并且每个排序文件的数据合并到最终表或索引中时都将被删除。涉及临时排序文件的操作可能需要的临时空间等于表中的数据量加上索引。如果联机DDL操作使用了数据目录所在的文件系统上的所有可用磁盘空间，则会报告错误。

如果MySQL临时目录的大小不足以容纳排序文件，请设置tmpdir为其他目录。或者，使用定义一个单独的临时目录以进行联机DDL操作 innodb_tmpdir。MySQL 5.7.11中引入了此选项，以帮助避免因大型临时排序文件而导致的临时目录溢出。

• 中间表文件的空间

一些重建表的在线DDL操作会在与原始表相同的目录中创建一个临时中间表文件。中间表文件可能需要的空间等于原始表的大小。中间表文件名以#sql-ib前缀开头，并且仅在联机DDL操作期间短暂出现。

该innodb_tmpdir选项不适用于中间表文件。

 

2.  在线DDL的限制       

　　1）在alter table时，如果涉及到table copy操作，要确保datadir目录有足够的磁盘空间，能够放的下整张表，因为拷贝表的的操作是直接在数据目录下进行的。
　　2）添加索引无需table copy，但要确保tmpdir目录足够存下索引一列的数据（如果是组合索引，当前临时排序文件一合并到原表上就会删除）
　　3）在主从环境下，主库执行alter命令在完成之前是不会进入binlog记录事件，如果允许dml操作则不影响记录时间，所以期间不会导致延迟。然而，由于从库是单个SQL Thread按顺序应用relay log，轮到ALTER语句时直到执行完才能下一条，所以从库会在master ddl完成后开始产生延迟。（pt-osc可以控制延迟时间，所以这种场景下它更合适）
　　4）During each online DDL ALTER TABLE statement, regardless of the LOCK clause, there are brief periods at the beginning and end requiring an exclusive lock on the table (the same kind of lock specified by the LOCK=EXCLUSIVE clause). Thus, an online DDL operation might wait before starting if there is a long-running transaction performing inserts, updates, deletes, or SELECT … FOR UPDATE on that table; and an online DDL operation might wait before finishing if a similar long-running transaction was started while the ALTER TABLE was in progress.
　　5）在执行一个允许并发DML在线 ALTER TABLE时，结束之前这个线程会应用 online log 记录的增量修改，而这些修改是其它thread里产生的，所以有可能会遇到重复键值错误(ERROR 1062 (23000): Duplicate entry)。
　　6）涉及到table copy时，目前还没有机制限制暂停ddl，或者限制IO阀值
　　7）在MySQL 5.7.6开始能够通过 performance_schema 观察alter table的进度。一般来说，建议把多个alter语句合并在一起进行，避免多次table rebuild带来的消耗。但是也要注意分组，比如需要copy table和只需inplace就能完成的，应该分两个alter语句。
　　8）如果DDL执行时间很长，期间又产生了大量的dml操作，以至于超过了innodb_online_alter_log_max_size变量所指定的大小，会引起DB_ONLINE_LOG_TOO_BIG 错误。默认为 128M，特别对于需要拷贝大表的alter操作，考虑临时加大该值，以此获得更大的日志缓存空间
　　9）执行完 ALTER TABLE 之后，最好 ANALYZE TABLE tb1 去更新索引统计信息

 

3. Online DDL的实现过程

　　online ddl主要包括3个阶段，prepare阶段，ddl执行阶段，commit阶段，rebuild方式比no-rebuild方式实质多了一个ddl执行阶段，prepare阶段和commit阶段类似。

　　下面将主要介绍ddl执行过程中三个阶段的流程。

3.1 Prepare阶段: 

①：创建新的临时frm文件(与InnoDB无关)
②：持有EXCLUSIVE-MDL锁，禁止读写
③：根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)，假如是Add Index，则选择online-norebuild即INPLACE方式
④：更新数据字典的内存对象
⑤：分配row_log对象记录增量(仅rebuild类型需要)
⑥：生成新的临时ibd文件(仅rebuild类型需要)

 

 3.2 ddl执行阶段:

①：降级EXCLUSIVE-MDL锁，允许读写
②：扫描old_table的聚集索引每一条记录rec
③：遍历新表的聚集索引和二级索引，逐一处理
④：根据rec构造对应的索引项
⑤：将构造索引项插入sort_buffer块排序
⑥：将sort_buffer块更新到新的索引上
⑦：记录ddl执行过程中产生的增量(仅rebuild类型需要)
⑧：重放row_log中的操作到新索引上(no-rebuild数据是在原表上更新的)
⑨：重放row_log间产生dml操作append到row_log最后一个Block

 

3.3 commit阶段：

①：当前Block为row_log最后一个时，禁止读写，升级到EXCLUSIVE-MDL锁
②：重做row_log中最后一部分增量
③：更新innodb的数据字典表
④：提交事务(刷事务的redo日志)
⑤：修改统计信息
⑥：rename临时idb文件，frm文件
⑦：变更完成

 

可以看出，在线DDL 并非整个过程都是online,在prepare阶段和commit阶段都会持有MDL-Exclusive锁，禁止读写；
执行阶段，予许读写。由于prepare和commit阶段相对于ddl执行阶段时间特别短，因此基本认为是全程online的。
prepare和commit阶段禁止读写，主要是为了保证数据一致性。Prepare阶段需要生成row_log对象和修改内存字典；commit禁止读写后，重做最后一部分增量，然后提交，保证数据一致

 

3.4 Mysql官方说明

15.13.1 Online DDL Overview
The online DDL feature enhances many DDL operations that formerly required a table copy or blocked
DML operations on the table, or both. Table 15.10, “Online Status for DDL Operations” shows how the
online DDL feature applies to each DDL statement.
With the exception of ALTER TABLE partitioning clauses, online DDL operations for partitioned
InnoDB tables follow the same rules that apply to regular InnoDB tables. For more information, see
Section 15.13.7, “Online DDL for Partitioned Tables”.
Some factors affect the performance, space usage, and semantics of online DDL operations. For more
information, see Section 15.13.8, “Online DDL Limitations”.
The “In-Place?” column shows which operations permit the ALGORITHM=INPLACE clause.
The “Rebuilds Table?” column shows which operations rebuild the table. For operations that use
the INPLACE algorithm, the table is rebuilt in place. For operations that do not support the INPLACE
algorithm, the table copy method is used to rebuild the table.
The “Permits Concurrent DML?” column shows which operations are performed fully online. You can
specify LOCK=NONE to assert that concurrent DML is permitted during the DDL operation. MySQL
automatically permits concurrent DML when possible.
Concurrent queries are permitted during all online DDL operations. You can specify LOCK=SHARED
to assert that concurrent queries are permitted during a DDL operation. MySQL automatically permits
concurrent queries when possible.
The “Notes” column provides additional information and explains exceptions and dependencies related
to the “Yes/No” values of other columns. An asterisk indicates an exception or dependency

 

4. 验证

1.实验环境是MySQL5.7.18

[mysql@localhost ~]$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

2.创建测试tet_emp，并插入数据

MySQL [(none)]> create database test;
Query OK, 1 row affected (0.07 sec)
MySQL [(none)]> use test
Database changed
MySQL [test]> create table test_emp( id int(10) unsigned NOT NULL AUTO_INCREMENT, c1 int(10) NOT NULL DEFAULT '0',
    ->  c2 int(10) unsigned DEFAULT NULL, c5 int(10) unsigned NOT NULL DEFAULT '0', c3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> c4 varchar(200) NOT NULL DEFAULT '', PRIMARY KEY(id), KEY idx_c1(c1), KEY idx_c2(c2) )ENGINE=InnoDB ;
Query OK, 0 rows affected (0.11 sec)   ----创建测试表：test_emp
MySQL [test]> delimiter //
MySQL [test]> create procedure insert_test_emp(in row_num int )
    -> begin
    ->  declare i int  default 0;
    ->  while i < row_num do
    -> insert into test_emp(c1, c2, c5,c3, c4) values( floor(rand()*row_num),floor(rand()*row_num),floor(rand()*row_num),now(),repeat('su', floor(rand()*20)));
    -> set i = i+1;
    ->  END while;
    -> end
    -> //
Query OK, 0 rows affected (0.01 sec)
MySQL [test]> 
MySQL [test]> call insert_test_emp(100000);   ----向测试表test_emp插入数据
Query OK, 1 row affected (8 min 24.34 sec)
MySQL [test]> desc test_emp;
+-------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type             | Null | Key | Default           | Extra                       |
+-------+------------------+------+-----+-------------------+-----------------------------+
| id    | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| c1    | int(10)          | NO   | MUL | 0                 |                             |
| c2    | int(10) unsigned | YES  | MUL | NULL              |                             |
| c5    | int(10) unsigned | NO   |     | 0                 |                             |
| c3    | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| c4    | varchar(200)     | NO   |     |                   |                             |
+-------+------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)
MySQL [test]>

 

3.在线修改字段：

MySQL [test]> alter table test_emp add c6 varchar(60) not null default '';
Query OK, 0 rows affected (2.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
MySQL [test]> select count(*) from test_emp;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.02 sec)
MySQL [test]> ---使用ALGORITHM=INPLACE选项在线修改
MySQL [test]> alter table test_emp ALGORITHM=INPLACE,modify c6 varchar(80) not null default '';
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0
MySQL [test]>

#可以看到 执行时间为0.09秒，执行速度很快

　　不过，ALGORITHM用法只对varcahr类型有效哦，比如我们对c1列int型进行变更：

MySQL [test]> alter table test_emp ALGORITHM=INPLACE,modify c1 int(11) unsigned not null;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

 

　　注意：

　　①：只变更int的位数，是可以的，不过这没什么意义，因为无论你int多少，最多都只能存10位，这也就是为什么我们生产库开发规范要定义所有的int都用int(10)。

　　②：如果字段属性大于并等于varchar(256)（这里的256是指字节（UTF8占用3字节）或者把varchar(80)减少到varchar(70)或者更少)，则仍需要拷贝数据且锁全表。 

mysql> alter table test_emp ALGORITHM=INPLACE,modify c6 varchar(84) not null default '';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
  
mysql> alter table test_emp ALGORITHM=INPLACE,modify c6 varchar(85) not null default '';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
  
mysql> alter table test_emp ALGORITHM=INPLACE,modify c6 varchar(86) not null default '';
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
  
mysql> alter table test_emp ALGORITHM=INPLACE,modify c6 varchar(40) not null default '';
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
  
mysql> alter table test_emp ALGORITHM=INPLACE,modify c6 varchar(70) not null default '';
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

#注意：添加字段alter table时，对该表的增删改查均不会锁表。而在这之前，当该表被访问时，需要等其执行完毕后才可以执行alter table。

　　总结：

　　在varchar变更字段长度方面，5.7的新特性ALGORITHM参数可以快速调整varchar类型的字段长度。5.7同5.6一样，增加，删除字段或索引不锁全表，删除主键锁全表。因此，在上线时，一定要执行show processlist命令并观察，此刻是否有某个慢SQL对该表进行操作，以免alter table表时出现锁表现象。

 

5.常用命令

1、在线添加索引：
alter table test_emp add index idx_id (c1),ALGORITHM=INPLACE;

2、在线添加字段：
alter table test_emp add name varchar(100) not null default '',ALGORITHM=INPLACE;

3、在线修改字段属性：
alter table test_emp ALGORITHM=INPLACE,modify c6 varchar(85) not null default '';

 
语法：
1.PRIMARY  KEY（主键索引）
mysql>ALTER  TABLE  `table_name`  ADD  PRIMARY  KEY (  `column`  ) ,ALGORITHM=INPLACE;

2.UNIQUE(唯一索引)
 mysql>ALTER  TABLE  `table_name`  ADD  UNIQUE (`column` ) ,ALGORITHM=INPLACE;

3.INDEX(普通索引)
mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column`  ),ALGORITHM=INPLACE;

4.FULLTEXT(全文索引)
mysql>ALTER  TABLE  `table_name`  ADD  FULLTEXT ( `column` ),ALGORITHM=INPLACE;

5.多列索引
mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column1`,  `column2`,  `column3`  ),ALGORITHM=INPLACE;