online DDL

生产中遇到添加、删除字段，添加索引语句，需要谨慎，因为可能会造成全表被锁。需要在非业务繁忙时执行。

从MySQL 5.6开始就支持部分DDL Online操作了，但并不是全部。当执行alter table 添加、删除字段、添加索引语句的时候，要评估表的数据量。

ALTER TABLE时ALGORITHM可以指定的三种方式

COPY ：

是指DDL时，会生成（临时）新表，此时加metadata lock，然后将原表数据逐行拷贝到新表中，此过程不允许写入，可以读。拷贝结束后，把临时表rename成原表，在执行所有以上过程期间都会阻塞DML，会消耗一倍的存储空间
 
INPLACE （原地）：

无需拷贝全表数据到新表，但可能还是需要IN-PLACE方式（原地，无需生成新的临时表）重建整表。这种情况下，在DDL的初始准备和最后结束两个阶段时通常需要加排他MDL锁（metadata lock，元数据锁），除此外，DDL期间不会阻塞DML

INSTANT （立即）：

只需修改数据字典中的元数据，无需拷贝数据也无需重建整表，同样，也无需加排他MDL锁，原表数据也不受影响。整个DDL过程几乎是瞬间完成的，也不会阻塞DML。

执行DDL操作时，ALGORITHM选项可以不指定，这时候MySQL按照INSTANT、INPLACE、COPY的顺序自动选择合适的模式。也可以指定ALGORITHM=DEFAULT，也是同样的效果。
如果指定了ALGORITHM选项，但不支持的话，会直接报错。

常见DDL执行方式

Online DDL

- Instant：此变更可以"立刻"完成；
- In Place：此变更由InnoDB引擎独立完成，不需要使用Redo log等，可以节省开销，否则使用copy；
- Rebuild Table：此变更会重建聚簇索引，一般情况下，涉及到数据变更时才需要重建聚簇索引；
- Permits Concurrent DML：此变更进行时，是否允许其他DML变更同一张表。此特性关系到变更是否会长时间阻塞业务；
- Only Modifies：此变更是否只变更元信息，不涉及数据变更

操作	Instant	InPlace	copy table	重建表 Rebuild Table	可并行 DML	只修改元数据	说明
新增辅助索引	否	是	否	否	是	否	当表上有FULLTEXT索引除外，需要锁表，阻塞写
删除辅助索引	否	是	否	否	是	是	操作元数据，不涉及表数据。所以很快，可以放心操作
修改索引名	否	是	是	否	是	是
新增主键	否	是	是	是	是	否	昂贵的操作（已验证）
删除主键	否	否	是	是	否	否	不允许并发DML，要拷贝表，而且如果没有在同一ATLER TABLE 语句里同时添加主键则会收到限制
删除并同时新增主键	否	是	是	是	是	否	在同一个 ALTER TABLE 语句删除就主键、添加新主键时，才允许inplace；数据大幅重组,所以它仍然是一项昂贵的操作
新增字段	是（追加式）	是	是	否	是	否	1、添加auto_increment列或者修改当前列为自增列都要锁表，阻塞写;2、虽采用online方式，但是表数据需要重新组织，所以增加列依然是昂贵的操作
删除字段	否	是	是	是	是	否	同add column，重新组织表数据，，昂贵的操作
修改字段数据类型	否	否	是	是	否	否	创建临时表，复制表数据，昂贵的操作（已验证）
扩展VARCHAR 列长度	否	是		否	是	是
新增STORED虚拟列	否	否		是	否	否
新增VIRTUAL虚拟列	是	是		否	是	是
转换表字符集	否	否	是	是	否	否	如果新字符集不同，需要重建表，昂贵的操作
opitmize table	否	是	是	是	是	否	当带有fulltext index的表用copy table方式并且阻塞写
修改表名	是	是		否	是	是
Make column `NOT NULL`		是	是	是	是	否	尽管允许ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作

执行DDL时的建议

1、一般DDL操作最好都采用pt-osc或gh-ost这样的工具来实施，并且实施之前务必要先检查当前目标表上是否有事务或大查询未结束，避免严重的MDL锁等待

2、除了8.0以上版本，除了追加式新增列、表改名、新增虚拟列这三种支持INSTANT的操作可以直接跑DDL，其余的都统统采用pt-osc/gh-osc工具，相对更不容易出问题

3、执行ALTER TABLE DDL时，不要指定ALGORITHM=?, LOCK=?选项，因为MySQL会自行判断该采用哪种方式。本来可以INPLACE的，可能不小心给指定成COPY就悲剧了

inplace的执行过程

准备阶段：

1、对表加元数据共享升级锁，并升级为排他锁；（此时DML不能并行）

2、在原表所在的路径下创建.frm和.ibd临时中转文件；（no-rebuild除创建二级索引外只创建.frm文件，其中添加二级索引操作最为特殊，该操作属于no-rebuild不会生成.ibd，但实际上对.ibd文件却做了修改，该操作会在参数tmpdir指定路径下生成临时文件，用于存储索引排序结果，然后再合并到.ibd文件中）

3、申请row log空间，用于存放DDL执行阶段产生的DML操作。（norebuild不需要）

执行阶段:

1、释放排他锁，保留元数据共享升级锁；（此时DML可以并行）

2、扫描原表主键以及二级索引的所有数据页，生成 B+ 树，存储到临时文件中；

3、将所有对原表的DML操作记录在日志文件row log中。

提交阶段：

1、升级元数据共享升级锁，产生排他锁锁表；（此时DML不能并行）

2、重做row log中的内容；（no-rebuild不需要）

3、重命名原表文件，将临时文件改名为原表文件名，删除原表文件；

4、提交事务，变更完成。

在DDL期间产生的数据，会按照正常操作一样，写入原表，记redolog、undolog、binlog，并同步到从库去执行，只是额外会记录在row log中，并且写入row log的操作本身也会记录redolog，而在提交阶段才进行row log重做，此阶段会锁表，此时主库（新表空间+row log）和从库（表空间）数据是一致的，在主库DDL操作执行完成并提交，这个DDL才会写入binlog传到从库执行，在从库执行该DDL时，这个DDL对于从库本地来讲仍然是online的，也就是在从库本地直接写入数据是不会阻塞的，也会像主库一样产生row log。

但是对于主库同步过来DML，此时会被阻塞，是offline的，DDL是排他锁的在复制线程中也是一样，所以不只会阻塞该表，而是后续所有从主库同步过来的操作（主要是在复制线程并行时会排他，同一时间只有他自己在执行）。所以大表的DDL操作，会造成同步延迟。

copy的执行过程

1、锁表，期间DML不可并行执行

2、生成临时表以及临时表文件（.frm .ibd）

3、拷贝原表数据到临时表

4、重命令临时表及文件

5、删除原表及文件

6、提交事务，释放锁

Online DDL空间要求

row log空间：：

row log空间每次申请的大小由 innodb_sort_buffer_size决定，最大值innodb_online_alter_log_max_size，该值默认为128M，支持动态修改。对于更新频繁的表来讲，如果预计在DDL期间对表的更新操作存储可能超过128M时，需要为本次操作增大该值。当然如果不涉及rebuild操作时，不需要考虑该值。如果提示DB_ONLINE_LOG_TOO_BIG错误，则是由innodb_online_alter_log_max_size空间不足造成的。

索引排序空间：

如果DDL操作涉及二级索引的创建，会在MySQL临时目录产生临时排序文件，将中间的排序结果写入文件，最终将内容合并到最终表或索引中，然后自动删除临时排序文件。这个路径默认为mysql全局参数tmpdir指定（默认值为/tmp，如果手动指定了innodb_tmpdir参数的路径，则tmpdir会被覆盖），且不会在原始表的目录中创建临时排序文件。tmpdir需要保证能够容纳要创建的二级索引，临时排序文件最大可能需要的空间等于表中的数据量加上索引，否则执行将报错。

中间表空间：

如果DDL操作涉及rebuild表，则会在原表所在目录创建临时表空间文件（以#sql开头），临时表空间大小需要等于原表大小，重建完成后会自动重命名临时表空间，删除原表空间。所以执行rebuild操作时需要保证原表所在路径下有足够空间

在instant(8.0.12)添加列后元数据发生更改后，如何解析页面上的物理记录？

额外的信息与数据字典中的一些元数据一起保留在物理记录中。我们认为，将元数据存储在适当的数据字典表中并使其在事务上保持一致将使其更健壮且更自然。此新的元数据存储在物理记录中。这个新的元数据包括一个存储在info_bits中的标志。info_bits中的此新信息用于跟踪是否在第一个即时ADD COLUMN之后创建记录。我们还使用info_bits跟踪物理记录中的字段/列数。当表经历第一个即时ADD COLUMN时的列数以及新添加的列的所有默认值都存储在数据字典中。这两条信息存储在数据字典表的se_private_data列中。

有了这些额外的信息，现在就可以立即执行ADD COLUMN操作，而无需修改表中的任何行。如果没有即时的ADD COLUMN，则表中的所有行将采用与以前相同的格式。即时发出ADD COLUMN后，对该表的任何更新都将以新格式写入行。从数据字典中查找默认值（如果有）。

在每个即时ADD COLUMN中，都会分别跟踪新添加的列的默认值。这些列的默认值可以随时更改。因此，在重建或截断表之后，可以丢弃即时列数和默认值，此外，可以像以前一样将表中的行更改为旧格式。如果表是分区表，则不同的分区可能具有不同数量的即时列，并且需要不同数量的默认值。如果某些分区被重建，截断或重新创建，则分区中的行也可以像以前一样更改为旧格式。

来源：http://serverteam.mysql.com

当前，InnoDB支持用于这些操作的INSTANT算法：

1、更改索引选项

2、重命名表（以ALTER方式）

3、设置/删除默认值

4、修改栏

5、添加/删除虚拟列

6、添加列（非生成）–我们称此为“立即添加列”

以下是一些可以立即完成的操作的简单示例：

mysql> CREATE TABLE t1 (a INT, b INT, KEY(b));

Query OK, 0 rows affected (0.70 sec)

添加和删除索引

mysql> # Modify the index can be instant if it's a trivial(不重要) change

mysql> ALTER TABLE t1 DROP KEY b, ADD KEY b(b) USING BTREE, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0

修改表名

mysql> # Rename the table through ALTER TABLE can be instant

mysql> ALTER TABLE t1 RENAME TO t2, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.26 sec)

设置默认值

mysql> # SET DEFAULT to a column can be instant

mysql> ALTER TABLE t2 ALTER COLUMN b SET DEFAULT 100,ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

删除默认值

mysql> # DROP DEFAULT to a column can be instant

mysql> ALTER TABLE t2 ALTER COLUMN b DROP DEFAULT,ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

添加字段和修改字段值

mysql> # MODIFY COLUMN can be instant

mysql> ALTER TABLE t2 ADD COLUMN c ENUM('a', 'b', 'c'),ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.35 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE t2 MODIFY COLUMN c ENUM('a', 'b', 'c', 'd', 'e'), ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

添加和删除虚拟列

mysql> # ADD/DROP virtual column can be instant

mysql> ALTER TABLE t2 ADD COLUMN (d INT GENERATED ALWAYS AS (a + 1) VIRTUAL), ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE t2 DROP COLUMN d, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0

同一个语句中执行两个操作

mysql> # Do two operations instantly in the same statement

mysql> ALTER TABLE t2 ALTER COLUMN a SET DEFAULT 20,ALTER COLUMN b SET DEFAULT 200, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DROP TABLE t2;

Query OK, 0 rows affected (0.36 sec)

如何观察

用户可以通过information_schema中的视图观察即时添加列的结果。更具体地说，一些新字段将添加到I_S.innodb_tables和I_S.innodb_columns。请注意，对于可以立即完成的其他操作，无需提供新的观察状态。请参见下面的示例：用户可以通过information_schema中的视图观察即时添加列的结果。更具体地说，一些新字段将添加到I_S.innodb_tables和I_S.innodb_columns。请注意，对于可以立即完成的其他操作，无需提供新的观察状态。请参见下面的示例：

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)

如我们所见，在innodb_tables中引入了一个名为instant_cols的新列，该列代表即时列的数量，而在innodb_columns中引入了两个有关默认值的新列，分别名为'has_default'和'default_value'。

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)

请注意，table_id不变。它不再是表的重建！正如我们所看到的，'instant_cols'现在设置为2，这意味着在第一个即时ADD COLUMN发生时表中有a列和b列。在innodb_columns中记住c和d列的默认值。现在，如果has_default为1，则用户可以知道是否立即添加了列。此外，如果“ has_default”为1，则此列的默认值存储在“ default_value”字段中。d的default_value 设置为值1000的内部二进制格式。

mysql> ALTER TABLE t1 ADD COLUMN e VARCHAR(100) DEFAULT 'Hello MySQL!';
Query OK, 0 rows affected (0.06 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 |
| 1065 | e | 1 | 48656c6c6f204d7953514c21 |
+----------+------+-------------+--------------------------+
5 rows in set (0.36 sec)

在另一个即时添加列之后，table_id再次保持不变。'instant_cols'不会更改，并且还会记住列e的默认值

mysql> insert into t1 (a,b,c,e) values(10,15,220,"hello");                                                      

Query OK, 1 row affected (0.01 sec) 

mysql> select * from t1;
+------+------+------+------+-------+

| a    | b    | c    | d    | e     |
+------+------+------+------+-------+
|   10 |   15 |  220 | 1000 | hello |
+------+------+------+------+-------+
1 row in set (0.00 sec)

posted on 2020-05-31 22:46 hopeless-dream 阅读(205) 评论(0) 编辑收藏举报

刷新页面返回顶部

hopeless-dream

导航

公告