| 导语本文是MySQL谬误集系列文章的第二篇，该系列旨在纠正一系列似是而非的说法。比如关于MySQL DDL操作，有很多同学认为会锁表，那是不是一定会锁表呢？是锁读还是锁写呢？锁多长时间？不同的DDL操作有差别吗？MySQL从5.5到8.0，对这个问题有什么改进呢？本文做了一个简单的总结。

1. 旧的copy临时表方式

我们旧有印像中的DDL变更导致长时间锁表，无法进行DML，使用的一般就是copy方式，以新建索引为例，它的操作步骤大概如下：

(1)新建带索引的临时表
(2)锁原表，禁止DML，允许查询
(3)将原表数据拷贝到临时表(无排序，一行一行拷贝)
(4)进行rename，升级字典锁，禁止读写
(5)完成创建索引操作

第3步时间复杂度是 O(n·m) ，其中n是记录行数，m是索引个数。

在引入Fast Index Creation和Online DDL之前，所有DDL都是采用copy方式。

2. Fast Index Creation

MySQL5.5或者MySQL5.1 with InnoDB Plugin开始支持Fast Index Creation。对于新建或者删除二级索引，使用inplace方式，其操作步骤如下：

(1)新建索引的数据字典
(2)锁表，禁止DML，允许查询
(3)读取聚集索引，构造新的索引项，排序并插入新索引
(4)等待打开当前表的所有只读事务提交
(5)创建索引结束

第3步相对于原来的拷备表方式，时间大为缩短。
利用这个特性，可以在导出/导入表的时候，先导数据（有主键），再建二级索引，可以大幅缩短时间。

CREATE TABLE T1(A INT PRIMARY KEY, B INT, C CHAR(1)) ENGINE=InnoDB;

INSERT INTO T1 VALUES (1,2,'a'), (2,3,'b'), (3,2,'c'), (4,3,'d'), (5,2,'e');
COMMIT;
ALTER TABLE T1 ADD INDEX (B), ADD UNIQUE INDEX (C);
复制

注意使用下面的方法效率比上面低，因为聚集索引要被扫描2次。

CREATE INDEX B ON T1 (B);
CREATE UNIQUE INDEX C ON T1 (C);
复制

但对于删除二级索引，上面2种命令速度差不多。因为删除二级索引只是更新了InnoDB系统表和数据字典表。

3. InnoDB Online DDL

在5.5的Fast Index Creation基础上，5.6进一步提供了Online DDL。在线DDL功能支持即时和原地的表更改以及并发的数据操作。这个功能的好处包括：

(1) 在繁忙的生产环境中提高响应速度和可用性，因为让一个表在几分钟或几小时内不可用是不切实际的。

(2) 对于原地操作，使用LOCK子句在DDL操作中调整性能和并发性之间的平衡。参见LOCK子句。

(3) 与表复制方法相比，磁盘空间使用和I/O开销更少。

这些特点使得在线DDL功能在数据库管理中非常有价值，特别是在需要高并发和高可用性的生产环境中。

通常，你不需要做任何特殊的操作来启用在线DDL。默认情况下，MySQL会尽可能地进行即时或原地的操作，并尽量减少锁定。

你也可以使用ALTER TABLE语句的ALGORITHM和LOCK子句来控制DDL操作的某些方面。这些子句放在语句的末尾，用逗号与表和列的规范分隔。例如：

alter table 

ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}

COPY: 这种操作是在原始表的一个副本上进行的，表数据会逐行从原始表复制到新表。在此过程中，不允许进行并发的数据修改操作。

INPLACE: 这种操作避免了复制表数据，但可能会就地重建表。在操作的准备和执行阶段，系统可能会短暂地对表进行独占的元数据锁定。通常，这种方式支持并发的数据修改操作。

INSTANT: 这种操作仅修改数据字典中的元数据。在操作的执行阶段，系统可能会短暂地对表进行独占的元数据锁定。表数据不受影响，使得操作瞬时完成。允许进行并发的数据修改操作。（这个功能在MySQL 8.0.12中引入）

当对主键的操作使用ALGORITHM=INPLACE时，尽管数据仍然被复制，但它比使用ALGORITHM=COPY更为高效，因为：

ALGORITHM=INPLACE不需要记录undo日志或相关的重做日志。这些操作为使用ALGORITHM=COPY的DDL语句增加了开销。

二级索引条目是预先排序的，因此可以按顺序加载。不使用变更缓冲区，因为二级索引中没有随机访问插入。

LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

LOCK = DEFAULT

根据给定的ALGORITHM子句（如果有）和ALTER TABLE操作，实现最大并发级别：如果支持，允许并发读写。如果不支持并发写，但支持并发读，则允许并发读。如果两者都不支持，强制执行独占访问。

LOCK = NONE

如果支持，允许并发读写。否则，会出现错误。

LOCK = SHARED

如果支持，允许并发读，但阻止写操作。即使存储引擎支持给定的ALGORITHM子句（如果有）和ALTER TABLE操作的并发写，也会阻止写操作。如果不支持并发读，会出现错误。

LOCK = EXCLUSIVE

强制执行独占访问。即使存储引擎支持给定的ALGORITHM子句（如果有）和ALTER TABLE操作的并发读/写，也会这样做。

这些选项允许数据库管理员在执行表结构更改时，根据需要选择适当的并发级别，以平衡性能和数据完整性。

为了避免因Copy表的ALTER TABLE操作而占用服务器资源和阻塞DML，应使用ALGORITHM=INSTANT或ALGORITHM=INPLACE。如果无法使用指定的算法，语句会立即停止。

3.1 Online DDL的3个阶段

Prepare阶段：

1. 创建新的临时frm文件

2. 持有EXCLUSIVE-MDL锁，禁止读写

3. 根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)

4. 更新数据字典的内存对象

5. 分配row_log对象记录增量

6. 生成新的临时ibd文件

ddl执行阶段：

1. 降级EXCLUSIVE-MDL锁，允许读写

2. 扫描old_table的聚集索引每一条记录rec

3. 遍历新表的聚集索引和二级索引，逐一处理

4. 根据rec构造对应的索引项

5. 将构造索引项插入sort_buffer块

6. 将sort_buffer块插入新的索引

7. 处理ddl执行过程中产生的增量(仅rebuild类型需要)

commit阶段：

1. 升级到EXCLUSIVE-MDL锁，禁止读写

2. 重做最后row_log中最后一部分增量

3. 更新innodb的数据字典表

4. 提交事务(刷事务的redo日志)

5. 修改统计信息

6. rename临时idb文件，frm文件

7. 变更完成  

这里实现数据完整性的关键点是row_log。row_log记录了DDL变更过程中新产生的DML操作，并在DDL执行最后将其应用到新的表中，保证数据完整性。实际上，online DDL并非整个过程都是online，在prepare阶段和commit阶段都会持有MDL-Exclusive锁，禁止读写；而在整个DDL执行阶段，允许读写。由于prepare和commit阶段相对于DDL执行阶段时间特别短，因此可以认为是online的。Prepare阶段和commit阶段的禁止读写，主要是为了保证数据一致性。Prepare阶段需要生成row_log对象和修改内存的字典；Commit阶段，禁止读写后，重做最后一部分增量，然后提交，保证数据一致。

4. Instant DDL

MySQL8.0.12中DDL新增instant语法，只修改元数据，不需要锁表，这里最重要的改进是add column instant（即刻加列），在8.0之前版本，Online DDL下，加字段可以使用inplace方式，虽然不会长时间阻塞写入，但其实还是需要rebuild重构表，这有几个负作用：

1. 对于大表，变更时间长
2. 需要额外的磁盘空间
3. 会消耗大量的CPU，内存和IO资源
4. 会导致备机延迟

特别是第4点，线上业务一般不能接受大于10秒的复制延迟，而MySQL8.0支持instant add column，彻底解决了这个问题。

这里要思考一个问题，为什么加字段之前不能通过修改数据字典的方式完成呢？主要原因是在数据记录中元数据信息不足。

InnoDB有2种主要的行格式，redundant和compact，dynamic类似compact。compact为了节省空间，移除了一些元数据，为了实现即刻加列，需要添加一些元数据，而redundant不需要，因为记录中已经包含了列的个数。

这些额外的信息分别存在记录和数据字典中，存在记录中的新元数据包括info_bits中的标识位，来标识记录是否在第一次即刻加列后插入，info_bits也记录了当前记录列的个数。第一次即刻加列的字段数和新加字段的默认值放在数据字典表中的se_private_data列。而记录默认值的主要原因是默认值如果发生修改，需要有地方查到默认值修改前的记录的值。第一次加字段的时候，需要当前字段个数，以及默认值，第二次加字段的时候，只需要记录默认值就行了。

假设第一次即刻加列的字段数为x，表当前字段数为y，那么x <= y总是成立的。字段的解析为：

1.如果x == y或非没有即刻加列过，按原来的方式解析

2.如果x < y，那么记录必定是在加字段前就生成，那么最后y - x个字段必为NULL或默认值。

2.1 如果该字段是允许为NULL，则为NULL。

2.2 如果该字段不允许为NULL，则为默认值。

如何查询是否有即刻加列呢？请看以下示例：

mysql> CREATE TABLE t1 (a INT, b INT);
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name    | instant_cols |
+----------+---------+--------------+
|     1065 | test/t1 |            0 |
+----------+---------+--------------+
1 row in set (0.22 sec)

mysql> SELECT table_id, name, has_default, default_value FROM information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+---------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+---------------+
|     1065 | a    |           0 | NULL          |
|     1065 | b    |           0 | NULL          |
+----------+------+-------------+---------------+
2 rows in set (0.38 sec)

mysql> ALTER TABLE t1 ADD COLUMN c INT, ADD COLUMN d INT DEFAULT 1000, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name    | instant_cols |
+----------+---------+--------------+
|     1065 | test/t1 |            2 |
+----------+---------+--------------+
1 row in set (0.03 sec)

mysql> SELECT table_id, name, has_default, default_value FROM information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+---------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+---------------+
|     1065 | a    |           0 | NULL          |
|     1065 | b    |           0 | NULL          |
|     1065 | c    |           1 | NULL          |
|     1065 | d    |           1 | 800003e8      |
+----------+------+-------------+---------------+
4 rows in set (0.36 sec)
复制

 即刻加列的限制：

1. 不能和其它非instant操作放到一个语句中
2. 只支持在最后加列
3. 不支持COMPRESSED格式
4. 不支持有全文索引的表
5. 不支持在DD表空间的
6. 不支持临时表

值得一提的是即刻加列的patch主要是腾讯互娱DBA团队的贡献，MariaDB10.3.2版本和MySQL社区版8.0.12版本都吸收进去了，为他们点赞。

5. DDL操作分类测试

那么哪些DDL支持online(instat/inplace)方式呢，可以参考官网说明，以8.0的索引操作为例（所有DDL对online的支持情况请见附录）：

说明：

(1) “Permits Concurrent DML?” 列显示哪些操作是完全online的。如果Permits Concurrent DML=NO，说明不支持Online DDL，需要使用老的COPY临时表的方式。

(2) DDL 过程如果是 Online 的，就一定是 inplace或者instant 的

(3) 反过来未必，也就是说 inplace 的 DDL，有可能不是 Online 的。添加全文索引（FULLTEXT index）和空间索引 (SPATIAL index) 就属于这种情况。

(4) “Rebuilds Table?” 列显示是否要重构表，如果支持inplace语法，则原地重构，否则采用copy表的方式重构。

(5) 如何区分是否使用老的COPY表的方式？很简单，看影响行数，如果影响行数不是0，说明有COPY表。

mysql> alter table t drop primary key;
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table t add  primary key(id);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
复制

下面按照并发性最好到最坏，分析常用DDL语句的4种情况：

5.1 最理想的情况，只修改元数据

不需要重构表，对读写基本没有影响，支持以下操作：

1.删除/重命名索引，改索引类型

2.加/减列，改列名

3.改VARCHAR列长度

4.更改 ENUM or SET 列定义

5.设置/删除列默认值

6.加/减VIRTUAL 列

7.增加/删除外键

8.改表名

测试，先用sysbench生成一张100万行的表。

复制

sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=root --mysql-password=xxx  --mysql-socket=/data/zftMysqlData/mysql.sock --db-driver=mysql  prepare
复制

复制

 然后更改字段名称，只用了2ms，注意影响行数为0。

test_5.7_master>show create table sbtest\G;
*************************** 1. row ***************************
       Table: sbtest
Create Table: CREATE TABLE `sbtest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad1` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

test_5.7_master>select current_timestamp(3);alter table sbtest change pad pad1 char(60) NOT NULL DEFAULT '';select current_timestamp(3);
+-------------------------+
| current_timestamp(3)    |
+-------------------------+
| 2018-05-19 13:20:01.034 |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

+-------------------------+
| current_timestamp(3)    |
+-------------------------+
| 2018-05-19 13:20:01.036 |
+-------------------------+
1 row in set (0.00 sec)
复制

 补充说明，如果是varchar类字段改长度，只有小于256是可以只修改元数据的

test_5.7_master>alter table sbtest.sbtest modify `c` varchar(150);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
复制

5.2 inplace但不需要rebuild  table

支持以下操作：

1. 添加二级索引

2. 更改自增列的当前值

测试二级索引添加，用了2.65秒：

复制

test_5.7_master>select current_timestamp(3);alter table sbtest add index idx_pad1(pad1);select current_timestamp(3);
+-------------------------+
| current_timestamp(3)    |
+-------------------------+
| 2018-05-19 13:26:23.757 |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (2.65 sec)
Records: 0  Duplicates: 0  Warnings: 0

+-------------------------+
| current_timestamp(3)    |
+-------------------------+
| 2018-05-19 13:26:26.413 |
+-------------------------+
1 row in set (0.00 sec)
复制

复制

5.3 in place需要rebuild  table

支持以下操作：

1.OPTIMIZE TABLE

2.Reorder columns

3.Change ROW_FORMAT property

4.Change KEY_BLOCK_SIZE property

5.Make column NULL/Make column NOT NULL

6.Add primary key

7.Drop primary key and add another

8.Rebuild with FORCE option

9.“null” rebuild using ALTER TABLE ... ENGINE=INNODB

测试加主键：

复制

select current_timestamp(3);alter table sbtest1 add primary key(id);select current_timestamp(3);
+-------------------------+
| current_timestamp(3)    |
+-------------------------+
| 2023-08-19 16:30:14.639 |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (38.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

+-------------------------+
| current_timestamp(3)    |
+-------------------------+
| 2023-08-19 16:30:53.155 |
+-------------------------+
1 row in set (0.00 sec)
复制

 可以观察到读写性能都有下降，有时甚至接近掉底，好在持续时间不长：

5.4 不可以in place需要copy表

包括以下操作：

1.改字段类型

2.删除主键

3.Convert character set／Specify character set

测试改字段类型如下，影响时长6秒，读写都掉底，影响行数100万行。

复制

test_5.7_master>select current_timestamp(3);alter table sbtest modify `pad1` char(61) NOT NULL DEFAULT '';select current_timestamp(3);
+-------------------------+
| current_timestamp(3)    |
+-------------------------+
| 2018-05-19 17:04:44.405 |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 1000000 rows affected (6.38 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

+-------------------------+
| current_timestamp(3)    |
+-------------------------+
| 2018-05-19 17:04:50.792 |
+-------------------------+
1 row in set (0.00 sec)
复制

复制

注意，如果不支持inplace的DDL使用inplace语法，会报错！

test_5.7_master>alter table sbtest modify `pad` char(61) NOT NULL DEFAULT '' ,algorithm=inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
复制

5.5 例外：时间类型列

注意对TIME, DATETIME, 和TIMESTAMP类型的列，在MySQL 5.6.4之前的版本与MySQL 5.6.4及之后的版本中的格式不同。在MySQL 5.6.4中这些时间类型可以存储6位的小数位。比如MySQL 5.6.4后的DATETIME存储值可以为'2018-05-27 21:33:25.123456'。

当alter table加字段时指定ALGORITHM=INPLACE，若该表是从MySQL 5.5原地升级过来且包含有时间未处理的时间类型，该加字段行为会被拒绝。只能使用ALGORITHM=COPY，即非在线加字段了。

5.6 如何观察进度

5.7新增通过performance schema来查看改表的进度功能，示例如下：

一、打开功能
复制

mysql> UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 
'stage/innodb/alter%';
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7 Changed: 7 Warnings: 0

mysql> UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
复制

二、执行改表操作
复制

复制

mysql> ALTER TABLE employees.employees ADD COLUMN middle_name varchar(14) AFTER 
first_name; Query OK, 0 rows affected (9.27 sec) Records: 0 Duplicates: 0 Warnings: 0 
复制

复制

 三、查看改表进度


mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current; 
+------------------------------------------------------+----------------+----------------+ 
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED 
| +------------------------------------------------------+----------------+----------------+ 
| stage/innodb/alter table (read PK and internal sort) | 280 | 1245 
| +------------------------------------------------------+----------------+----------------+ 1 row in set (0.01 sec)
复制

复制

 6. 实战应用

在生产环境进行DDL的时候，是否使用Online DDL进行变更呢？实际上有3种情况：

1）对于只改元数据的操作，包括8.0.12后的加列操作，使用Online DDL

2)  对于数据量很小的表，能使用Online DDL而不是只能使用copy表方式，和业务沟通后也可以使用Online DDL

3）对于大表，又不支持只改元数据的操作，都不使用Online DDL，而是使用了Gh-ost工具改表，最大的原因就是即使不copy表而是rebuild表，也会导致下游备机复制延迟，一般业务无法接受，而Gh-ost可以控制速度，也就控制住了下游延迟。

公众号精华文章：

1.MySQL谬误集01：读不加锁
2.MySQL8.1来了：MySQL创新和长期支持（LTS）版本简介
3.Gh-ost改表P0级BUG：可能导致数据丢失
4.从一道数据库面试题彻谈MySQL加锁机制
5.MySQL字符编码指南--基础篇
6.从财政学专业到TOP金融数据库DBA--我的学习之路
7.大胆假设小心求证：MySQL双写+双向复制实战
8.MySQL字符集终极指南--进阶篇

公众号"数据库之巅"分享这十几年来我在数据库特别是互联网金融数据库运维走过的路和踩过的坑，欢迎大家关注。