MySQL Online DDL 梳理
数据库需要每天进行很多的DDL,例如添加索引、添加字段等。对于MySQL数据库DDL支持的并不是很友好，一不小心就全表锁。从MySQL 5.6开始支持部分DDL Online操作，不是全部DDL。
一、MySQL DDL 执行原理
不同版本的MySQL，对于DDL的处理方式是不同的，主要有三种：
1、Copy Table方式：这是innodb最早的支持方式。通过临时表拷贝的方式实现。
原理：新建一个相同表结构的临时表，将原来的数据全部拷贝到临时表，然后进行rename，完成创建操作。这个过程中，原表"可读"，"不可写"，消耗一倍的存储空间。
2、Online (INPLACE)方式：MySQL5.6以上版本中提供的方式，无论是copy table，还是inplace，原表都是"可读"，"不可写"，对应用有很大的限制。MySQL5.6开始innodb开始支持Online DDL，"可读"，"可写"
MySQL DDL 总结：
| 操作 | 支持方式 | Allow R/W | 说明 |
| add/create index | online | 允许读写 | 当表上有FULLTEXT索引除外，需要锁表，阻塞写 |
add fulltext index |
in-place（5.6以上版本） | 仅支持读，阻塞写 |
创建表上第一个fulltext index用copy table方式，除非表上 之后创建fulltext index用in-place方式，经过测试验证，第一次时5.6 innodb 会隐含自动添加 |
drop index |
online | 允许读写 | 操作元数据，不涉及表数据。所以很快，可以放心操作 |
| optimize table | online | 允许读写 |
当带有fulltext index的表用copy table方式并且阻塞写 |
| alter table...engine=innodb | online | 允许读写 |
当带有fulltext index的表用copy table方式并且阻塞写 |
| add column | online | 允许读写，(增加自增列除外) |
1、添加auto_increment列或者修改当前列为自增列都要锁表，阻塞写;2、虽采用online方式，但是表数据需要重新组织，所以增加列依然是昂贵的操作，小伙伴尤其注意啦 |
| drop column | online | 允许读写(增加自增列除外) | 同add column，重新组织表数据，，昂贵的操作 |
| Rename a column | online | 允许读写 | 操作元数据;不能改列的类型，否则就锁表（已验证） |
| Reorder columns | online | 允许读写 | 重新组织表数据，昂贵的操作 |
Make column NOT NULL |
online | 允许读写 | 重新组织表数据，昂贵的操作 |
| Change data type of column | copy table | 仅支持读，阻塞写 | 创建临时表，复制表数据，昂贵的操作（已验证） |
| Set default value for a column | online | 允许读写 |
操作元数据，因为default value存储在frm文件中，不涉及表数据。所以很快， 可以放心操作 |
| alter table xxx auto_increment=xx | online | 允许读写 | 操作元数据，不涉及表数据。所以很快，可以放心操作 |
| Add primary key | online | 允许读写 | 昂贵的操作（已验证） |
| Convert character set | copy table | 仅支持读，阻塞写 | 如果新字符集不同，需要重建表，昂贵的操作 |
DDL 测试：
创建表结构：
mysql> CREATE TABLE `hank` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20), `name1` varchar(20), `concent` text, PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
创造测试数据：
mysql> insert into hank(name,name1,concent)values('a','b','123aabbcc'); Query OK, 1 row affected (0.00 sec) ... ... mysql> insert into hank(name,name1,concent) select name,name1,concent from hank; Query OK, 1048576 rows affected (5.19 sec) Records: 1048576 Duplicates: 0 Warnings: 0 mysql> select count(*) from hank; +----------+ | count(*) | +----------+ | 2097152 | +----------+ 1 row in set (0.29 sec)
开启profiling ：
mysql> set profiling=1;
1、创建全文索引，add fulltext index
(1).用例1：该语句执行期间是否锁表？
session 1: mysql> alter table hank add fulltext index idx_concent(concent); ... session 2: mysql> insert into hank(name,name1,concent)values('a','b','123aabbcc'); ... session 1: mysql> show profiles; mysql> show profile for query 35; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000084 | | checking permissions | 0.000004 | | checking permissions | 0.000005 | | init | 0.000006 | | Opening tables | 0.000136 | | setup | 0.000046 | | creating table | 0.000780 | | After create | 0.000104 | | System lock | 0.000008 | | preparing for alter table | 0.022092 | | altering table | 2.760071 | | committing alter table to stor | 0.001088 | | end | 0.000036 | | query end | 0.000470 | | Waiting for semi-sync ACK from | 0.000006 | | query end | 0.000022 | | closing tables | 0.000023 | | freeing items | 0.000116 | | cleaning up | 0.000016 | +--------------------------------+----------+ 在创建全文索引时，支持"读"，阻塞"写" . 在创建第一个全文索引时，MySQL 5.6以上版本innodb会默认自动添加FTS_DOC_ID，这样就避免了COPY TABLE，5.6以上版本创建全文索引都是in-place方式。 mysql> alter table hank add fulltext index idx_concent(concent); Query OK, 0 rows affected, 1 warning (6.72 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> show warnings; +---------+------+--------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------+ | Warning | 124 | InnoDB rebuilding table to add column FTS_DOC_ID | +---------+------+--------------------------------------------------+ 1 row in set (0.00 sec)
(2).optimize table & alter table .. engine=innodb & alter table table_name force 执行期间是否锁表？
mysql> delete from hank where id<100000; Query OK, 65536 rows affected (0.73 sec) mysql> alter table hank drop index idx_concent; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 sesson 1: mysql> alter table hank engine=innodb; Query OK, 0 rows affected (2.33 sec) Records: 0 Duplicates: 0 Warnings: 0 sesson 2: mysql> insert into hank(name,name1,concent)values('a','b','123aabbcc'); Query OK, 1 row affected (0.00 sec) mysql> show profiles; +----------+------------+----------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+----------------------------------------------------------+ | 32 | 0.00013850 | alter table hank drop fulltext index idx_concent | | 33 | 0.00801450 | alter table hank drop index idx_concent | | 34 | 0.00017100 | alter table hank drop fulltext index idx_concent | | 35 | 2.78511000 | alter table hank add fulltext index idx_concent(concent) | | 36 | 0.00018150 | show warnigns | | 37 | 0.00025075 | show warnings | | 38 | 0.00018200 | alter table hank drop fulltext index idx_concent | | 39 | 0.00777425 | alter table hank drop index idx_concent | | 40 | 2.66718450 | alter table hank add fulltext index idx_concent(concent) | | 41 | 0.00019775 | show warnings | | 42 | 0.00029225 | select max(id) fro hank | | 43 | 0.00058700 | select max(id) from hank | | 44 | 0.73273450 | delete from hank where id<100000 | | 45 | 0.00801875 | alter table hank drop index idx_concent | | 46 | 2.32371300 | alter table hank engine=innodb | +----------+------------+----------------------------------------------------------+ 15 rows in set, 1 warning (0.00 sec) mysql> show profile for query 46; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000102 | | checking permissions | 0.000006 | | checking permissions | 0.000005 | | init | 0.000006 | | Opening tables | 0.000135 | | setup | 0.000038 | | creating table | 0.000843 | | After create | 0.000119 | | System lock | 0.000008 | | preparing for alter table | 0.002342 | | altering table | 2.291558 | | committing alter table to stor | 0.027953 | | end | 0.000028 | | query end | 0.000397 | | Waiting for semi-sync ACK from | 0.000003 | | query end | 0.000016 | | closing tables | 0.000010 | | freeing items | 0.000126 | | cleaning up | 0.000021 | +--------------------------------+----------+ 19 rows in set, 1 warning (0.00 sec) 在没有"全文索引"下，optimize table & alter table .. engine=innodb & alter table table_name force "不阻塞读写" .
(3). 在有全文索引下，optimize table & alter table .. engine=innodb & alter table table_name force 是否锁表？
mysql> alter table hank add fulltext index idx_concent(concent); Query OK, 0 rows affected, 1 warning (6.72 sec) Records: 0 Duplicates: 0 Warnings: 1 session 1: mysql> alter table hank engine=innodb; Query OK, 1966084 rows affected (20.93 sec) Records: 1966084 Duplicates: 0 Warnings: 0 session 2: mysql> insert into hank(name,name1,concent)values('a','b','123aabbcc'); Query OK, 1 row affected (20.37 sec) session 1: mysql> show profile; +--------------------------------+-----------+ | Status | Duration | +--------------------------------+-----------+ | starting | 0.000166 | | checking permissions | 0.000007 | | checking permissions | 0.000007 | | init | 0.000008 | | Opening tables | 0.000166 | | setup | 0.000069 | | creating table | 0.000913 | | After create | 0.000169 | | System lock | 0.020907 | | copy to tmp table | 20.888505 | | rename result table | 0.017792 | | end | 0.000046 | | query end | 0.000403 | | Waiting for semi-sync ACK from | 0.000004 | | query end | 0.000011 | | closing tables | 0.000022 | | freeing items | 0.000119 | | cleaning up | 0.000030 | +--------------------------------+-----------+ 18 rows in set, 1 warning (0.00 sec)
