MySQL Online DDL 黄金准则

共计 7630 个字符，预计需要花费 20 分钟才能阅读完成。

作为一名 DBA，对数据库进行 DDL 操作非常多，如添加索引，添加字段等等。对于 MySQL 数据库，DDL 支持的并不是很好，一不留心就导致了全表被锁，经常搞得刚入门小伙伴很郁闷又无辜，不是说 MySQL 支持 Online DDL 么，不是说不会锁表的么？是的，令人高兴的是从 MySQL5.6 开始就支持部分 DDL Online 操作了，但并不是全部喔，今天这里就对我们常用的 DDL 进行总结和说明，让操作 DDL 的小伙伴从此做到心中有数，得心应手，让老板们再也不用担心我们做 DDL 咯。

我自己遵守的一条黄金准则：DDL 永远不要在业务高峰期间执行。

环境说明：本次的测试服务器配置如下

CPU:32 cores
      MEM：128G
      DISK: SSD（固态硬盘）MySQL 版本：5.6.27 以上

一、MySQL 执行 DDL 原理

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 来说，这是一个非常棒的改进。

二、常用 DDL 执行方式总结

操作	支持方式	Allow R/W	说明
add/create index	online	允许读写	`FULLTEXT 索引除外，需要锁表，阻塞写`
`add fulltext index`	in-place	仅支持读，阻塞写	创建表上第一个 fulltext index 用 copy table 方式，除非表上 `有FTS_DOC_ID`列。之后创建 fulltext index 用 in-place 方式，经过测试验证，第一次时 5.6 innodb 会隐含自动添加 `FTS_DOC_ID` 列，也就是 5.6 都是 in-place 方式
`drop index`	online	允许读写	操作元数据，不涉及表数据。所以很快，可以放心操作
optimize table	in-place or copy table(fulltext)	允许读写 or 阻塞写(fulltext)	5.6.17 版本以上才支持 inplace，之前版本是 copy-table；当带有 fulltext index 的表用 copy table 方式并且阻塞写
alter table…engine=innodb	in-place or copy table(fulltext)	允许读写 or 阻塞写(fulltext)	5.6.17 版本以上才支持 inplace，之前是 copy-table，当带有 fulltext index 的表用copy table 方式并且阻塞写
add column	in-place	允许读写，(增加自增列除外)	1、添加 auto_increment 列要锁表，阻塞写;2、虽采用 in-place 方式，但是表数据需要重新组织，所以增加列依然是昂贵的操作，小伙伴尤其注意啦（测试已验证）
drop column	in-place	允许读写(增加自增列除外)	同 add column，重新组织表数据，，昂贵的操作
Rename a column	online	允许读写	操作元数据; 不能改列的类型，否则就锁表（已验证）
Reorder columns	in-place	允许读写	重新组织表数据，昂贵的操作
Make column `NOT NULL`	in-place	允许读写	重新组织表数据，昂贵的操作
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	in-place	允许读写	昂贵的操作（已验证）
Convert character set	copy table	仅支持读，阻塞写	如果新字符集不同，需要重建表，昂贵的操作

【注】：红色部分都需要注意的操作，会影响线上数据库性能

二、测试常用 DDL 执行方式

测试用表：表大小 70M, 行数 13659
初始表结构：

CREATE TABLE `t_mysql` (
  `checksum` bigint(20) unsigned NOT NULL,
  `sample` text NOT NULL，
  `content` text，
  `content1` text，
  `content2` text，
) ENGINE=InnoDB DEFAULT CHARSET=utf8

测试机器开启 profiling：

root:test> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

1、add fulltext index

1) 用例 1：该语句执行期间是否锁表？

开两个 session。session 1：创建 fulltext index

dbadmin:test> alter table t_mysql add fulltext index idx_1(sample);
执行中.......

session 2: 进行 insert 数据，会一直等待中，阻塞写了

MySQL Online DDL 黄金准则

【结论 1】：创建全文索引时，仅支持读，阻塞写；dba 小伙伴加索引时要注意啦，而且执行时间超级超级长，在执行 ddl 时，尽量不要手动 kill，可能会导致异常，这里有个知识点。

2) 用例 2：创建表上第一个 fulltext index 用 copy table 方式，除非表上 有FTS_DOC_ID列。之后创建 fulltext index 用 in-place 方式？

创建第一个全文索引：

root:test> alter table t_mysql add fulltext index idx_1(sample);
Query OK, 0 rows affected, 1 warning (15.21 sec)
Records: 0  Duplicates: 0  Warnings: 1

这个时候发现0 rows affected，也就是说没有用 copy table 方式。这是为什么，官方文档上说第一个全文索引采用 copy table 方式的，难道官方文档上错误？再看下执行过程：

root:test> show profile for query 10;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000378 |
| checking permissions           |  0.000038 |
| checking permissions           |  0.000035 |
| init                           |  0.000032 |
| Opening tables                 |  0.000101 |
| setup                          |  0.000079 |
| creating table                 |  0.001043 |
| After create                   |  0.000217 |
| System lock                    |  0.000031 |
| preparing for alter table      |  0.023248 |
| altering table                 | 15.164399 |
| committing alter table to stor |  0.016108 |
| end                            |  0.000043 |
| query end                      |  0.000327 |
| closing tables                 |  0.000021 |
| freeing items                  |  0.000081 |
| logging slow query             |  0.000121 |
| cleaning up                    |  0.000060 |
+--------------------------------+-----------+
18 rows in set, 1 warning (0.00 sec)

在这上面也没有发现 copy tmp table 字样，说明确实没有进行表 copy。等等，在上面执行建全文索引时，有一个 warning，看下这个 warning：

root:test> show warnings;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)

到这里就明白了，原来当我们建第一个全文索引时，5.6 以上版本 innodb 会默认的为我们自动添加 FTS_DOC_ID，这样就避免了 copy table 了，所以相对会快些。

【结论 2】：5.6 以上版本 innodb 会默认的为我们自动添加 FTS_DOC_ID，所以第一次创建全文索引时避免了 copy table。我们可以自此认为 5.6 以上版本创建全文索引都是 in-place 方式。

2、optimize table & alter table...engine=innodb

注：测试前清除上面创建的全文索引，恢复表为初始

1) 用例：该语句执行期间是否锁表

1.1)不存在全文索引：

session1 执行：

root:test> alter table t_mysql engine=innodb;
Query OK, 0 rows affected (1.38 sec) # 没有数据受影响
Records: 0  Duplicates: 0  Warnings: 0

session2 同时执行：

dbadmin:test> insert into t_mysql values(0113,'测试全文索引','darrenllllllllllllll');
Query OK, 1 row affected (0.14 sec)

当表上不存在全文索引时，optimize table 或者 alter table t_mysql engine=innodb 很快执行完成，并且不阻塞写；

1.2）存在全文索引时：

步骤一：添加全文索引

CREATE TABLE `t_mysql` (`checksum` bigint(20) unsigned NOT NULL,
  `sample` text NOT NULL,
  `content` text,
  FULLTEXT KEY `idx_1` (`sample`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

步骤二：session1：执行 optimize table 或者 alter table … engine=innodb

root:test> alter table t_mysql engine=innodb;

执行中.......

Query OK, 13661 rows affected (42.13 sec)   #说明进行 copy table 数据了
Records: 13661  Duplicates: 0  Warnings: 0

root:test> show profile for query 14;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000355 |
| checking permissions |  0.000071 |
| Opening tables       |  0.000151 |
| System lock          |  0.000188 |
| init                 |  0.000027 |
| Opening tables       |  0.000958 |
| setup                |  0.000062 |
| creating table       |  0.001235 |
| After create         |  0.000127 |
| System lock          |  0.045863 |
| copy to tmp table    | 43.937449 |
| rename result table  |  0.529001 |
| end                  |  0.000172 |
| Opening tables       |  0.000759 |
| System lock          |  0.002615 |
| query end            |  0.000402 |
| closing tables       |  0.000011 |
| freeing items        |  0.000022 |
| cleaning up          |  0.000033 |
+----------------------+-----------+

session 2：模拟插入数据：

dbadmin:test> insert into t_mysql values(0113,'测试全文索引','darrenllllllllllllll'); 

等待中.......

当表上存在全文索引时，我们执行 optimize table 或者 alter table t_mysql engine=innodb 采用 copy table 方式，而且锁全表，阻塞写；

【结论 1】：当表上不存在全文索引时，optimize table 或者 alter table t_mysql engine=innodb 采用 in-place 方式，并且不阻塞写；

当表上存在全文索引时，我们执行 optimize table 或者 alter table t_mysql engine=innodb 采用 copy table 方式，而且锁全表，阻塞写；

3、add column

1）用例 1：添加 auto_increment 列要锁表，阻塞写？

session 1：

root:test> alter table t_mysql add column id int not null primary key auto_increment;
Query OK, 0 rows affected (1.41 sec)

session 2：

dbadmin:test> insert into t_mysql(checksum,sample,content) values(0113,'测试全文索引','darrenllllllllllllll');

waitting......
......
......
......
......
......
Query OK, 1 row affected (0.97 sec)

当添加自增列时，会阻塞写。

2）用例 2：添加普通列，online？

session 1：

root:test> alter table t_mysql add column content1 text;
Query OK, 0 rows affected (1.36 sec)  #in-place 方式
Records: 0  Duplicates: 0  Warnings: 0

session 2：

dbadmin:test> insert into t_mysql(checksum,sample,content) values(0113,'测试全文索引','darrenllllllllllllll');
Query OK, 1 row affected (0.01 sec)

当添加一个普通列时，是 online 的，不阻塞写入。

4、change column type

session 1：

root:test> alter table t_mysql change content1 content1 longtext;  
Query OK, 13674 rows affected (1.37 sec)  # copy table
Records: 13674  Duplicates: 0  Warnings: 0

root:test> show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000302 |
| checking permissions | 0.000027 |
| checking permissions | 0.000045 |
| init                 | 0.000024 |
| Opening tables       | 0.000097 |
| setup                | 0.000067 |
| creating table       | 0.001379 |
| After create         | 0.000165 |
| System lock          | 0.004105 |
| copy to tmp table    | 1.327642 |  #copy table
| rename result table  | 0.034565 |
| end                  | 0.000473 |
| query end            | 0.001067 |
| closing tables       | 0.000263 |
| freeing items        | 0.000414 |
| logging slow query   | 0.000478 |
| cleaning up          | 0.001074 |
+----------------------+----------+

session 2：并发 DML

dbadmin:test> insert into t_mysql(checksum,sample,content1) values(0113,'测试全文索引','darrenllllllllllllll');

WAITTING.......
.......
.......
.......
.......
.......
Query OK, 1 row affected (0.95 sec)