likes
comments
collection
share

InnoDB and Online DDL

作者站长头像
站长
· 阅读数 14

简介

online DDL功能支持INSTANT和in-place表变更和并发DML。此功能好处包括：

  • 在繁忙的生产环境中提升响应性和可用性，在这种情况下，使表在几分钟或几个小时不可用是不切实际的。
  • in-place操作，可以使用LOCK子句在DDL操作期间调整性能和并发之间的平衡。The LOCK clause
  • 比table-copy方法更少的磁盘空间使用和I/O开销。

注意

ALGORITHM=INSTANT ADD COLUM和其他操作在Mysql 8.0.12中支持

通常，无需做任何操作开启online DDL。默认情况下，Mysql在运行的情况下instant和in-place执行操作，并尽可能减少使用锁。

可以使用ALTER TABLE语句和ALGORITHM和LOCK子语句控制DDL操作的各个方面。例如：

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

注意

INSTANT只支持默认的LOCK=DEFAULT。ALGORITHM在遇到任何问题时会立即停止并回滚。

INSTANT、INPLACE和COPY的原理

对Online DDL有了大致的了解后，可以发现ALGORITHM子语句比较关键，在使用的过程中需要注意，因此我们来看看INSTANT、INPLACE和COPY的原理。

INSTANT

为了进一步改进online DDL（列添加），MySQL 8.0推出了INSTANT算法（TENCENT的补丁）。为繁忙的生产环境中提升了响应性和可用性，允许并发DML。

INSTANT算法只执行数据字典中的元数据更改。在修改schema期间它不需要任何元数据锁，因为它不会触发表的数据文件。

MySQL8版本增加了两个新的视图，即I_S.innodb_tables和I_S.innodb_colums。

例如，在使用INSANT添加一个列之前的表结构：

mysql> show create table sbtest.sbtest7\G
*************************** 1. row ***************************
       Table: sbtest7
Create Table: CREATE TABLE `sbtest7` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `f` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k_7` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

此时我们查询innodb_tables，instant_columns是0:

mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables where name like '%sbtest7%';
+----------+----------------+--------------+
| table_id | name           | instant_cols |
+----------+----------------+--------------+
|     1258 | sbtest/sbtest7 |            0 |
+----------+----------------+--------------+
1 row in set (0.00 sec)

使用INSTANT算法添加一列：

mysql> alter table sbtest7 add g varchar(100) not null default 'Mysql 8 is great', algorithm=instant;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

现在来看看innodb_tables的instant_columns的列变成了5：

mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables where name like '%sbtest7%';
+----------+----------------+--------------+
| table_id | name           | instant_cols |
+----------+----------------+--------------+
|     1258 | sbtest/sbtest7 |            5 |
+----------+----------------+--------------+
1 row in set (0.00 sec)

instant_cols记录在表sbtest7添加instant列之前存在的列行数。

INSTANT算法添加列的默认值是被存储在I_S.innodb_columns表。

mysql> SELECT table_id, name, has_default, default_value FROM information_schema.innodb_columns WHERE table_id = 1258;
+----------+------+-------------+----------------------------------+
| table_id | name | has_default | default_value                    |
+----------+------+-------------+----------------------------------+
|     1258 | id   | 0           | NULL                             |
|     1258 | k    | 0           | NULL                             |
|     1258 | c    | 0           | NULL                             |
|     1258 | pad  | 0           | NULL                             |
|     1258 | f    | 0           | NULL                             |
|     1258 | g    | 1           | 4d7973716c2038206973206772656174 |
+----------+------+-------------+----------------------------------+
6 rows in set (0.23 sec

列g has_deafult值是1、default_value使用hash格式存储。

缺点

  • 列只能添加到表的最后。添加到任意位置是不支持的。

在MySQL 8.0.29之后就支持了

  • 列不可添加到使用ROW_FORMAT=COMPRESSED。
  • 不可以添列到包含FULLTEXT索引的表中
  • 不支持向临时表中添加。临时表仅支持ALGORITHM=COPY。
  • 不能将列添加到位于数据字典表空间（共享表空间）中的表中。

INPLCAE

INPLACE算法就地对原表执行操作，并尽可能避免表复制和重建。

两个重要的参数：

  • innodb_tmpdir：使用临时目录来写排序文件，默认使用/tmp目录，如果定义的临时目录不够大，我们可以使用innodb_tmpdir系统变量进行定义。
  • innodb_online_alter_log_max_size：使用名为innodb_online_alter_log的临时日志文件跟踪记录在表DDL操作期间执行的像INSERT、UPDATE、DELETE的数据变化，文件的大小可以使用innodb_online_alter_log_max_size进行配置，默认128MB。

在alter期间传入的写入是被存储（大小受innodb_online_alter_log_max_size控制），在使用DDL操作结束后会被使用，使用时会锁几秒表进行补入。

内部流程图： InnoDB and Online DDL

注意：InnoDB需要额外的磁盘空间去执行alter，等于原始表在datadir中的空间或者更多（在一些情况下）。

缺点

  • 长时间运行的online DDL操作可能会导致复本滞后。
  • 如果innodb_online_alter_log的数较多会导致执行DDL操作后需要较多时间锁表补入。
  • 有时会导致高并发服务器上较大表的高IO使用率（在资源消耗方面具有侵略性）。

COPY

COPY算法使用创建一个新的已经更改好的临时表去修改现有表的schema，一旦数据迁移到临时表完成后，临时表会替代老表。

使用COPY算法是昂贵的，因为它会阻塞并发的DML操作，但是可以支持并发读（当LOCK语句使用SHARED的话，如果使用LOCK=EXCLUSIVE读写都会被阻塞）。

内部流程图：

InnoDB and Online DDL

缺点

  • 在操作期间没有暂停DDL的操作或者限制I/O或CPU使用的机制。
  • 回滚的操作可能是个昂贵的过程。
  • 在修改表期间不允许并发DML。
  • 造成复制延迟

INPLACE、COPY和INSTANT算法的比较

使用1M数据量的表进行测试比较。

INPLACE – 7.09 sec

mysql> alter table sbtest7 add g varchar(100) not null default 0, algorithm=inplace;
Query OK, 0 rows affected (7.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

COPY – 14.34 sec

mysql> alter table sbtest7 add g varchar(100) not null default 0, algorithm=copy;
Query OK, 1000000 rows affected (14.34 sec)
Records: 1000000  Duplicates: 0 Warnings: 0

INSTANT – 0.03 sec

mysql> alter table sbtest7 add g varchar(100) not null default 0, algorithm=instant;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

InnoDB and Online DDL

有了大概的了解下面来看看如何使用的以及使用过程需要注意的事情。

只涉及常用的

索引操作

operationInstantin placerebuilds tablepermits consurrent dmlonly modified metadata
创建或添加二级索引NOYESNOYESNO
删除索引NOYESNOYESYES
重命名索引NOYESNOYESYES
改变索引类型YESYESNOYESYES

创建或添加二级索引

CREATE INDEX name ON table (col_list);

ALTER TABLE tbl_name ADD INDEX name (col_list);

创建索引时，该表仍然用于读写操作。CREATE INDEX语句在所有访问该表的事务都完成后才结束，因此索引的初始状态反映了表的最新内容。

删除索引

DROP INDEX name ON table;

ALTER TABLE tbl_name DROP INDEX name;

重命名索引

ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name, ALGORITHM=INPLACE, LOCK=NONE;

改变索引类型（USING {BTREE|HASH}）

ALTER TABLE tbl_name DROP INDEX i1, ADD INDEX i1(key_part,... ) USING BTREE, ALGORITHM=INSTANT;

列操作

operationInstantin placerebuilds tablepermits consurrent dmlonly modified metadata
添加一列YES*YESNO*YES*YES
删除列YES*YESYESYESYES
重命名列YES*YESNOYES*YES
给列设置默认值YESYESNOYESYES
改变列数据类型NONOYESNONO
扩充varchar类型NOYESNOYESYES
删除列的默认值YESYESNOYESYES
改变自增值NOYESNOYESNO*
设置列可为NULLNOYESYES*YESNO
设置列不可为NULLNOYES*YES*YESNO

添加列

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INSTANT;

INSTANT是MySQL 8.0.12开始的默认算法，之前是INPLACE

INSTANT的使用限制

  • 语句不能将添加列与不支持INSYANT算法的其他ALTER TABLE操作结合使用
  • 可以使用INSTANT算法在表中的任何位置添加列。在MySQL 8.0.29之前，只能添加到末尾。
  • 不可以向使用ROW_FORMAT=COMPRESSED的表、具有FULLTEXT索引的表、驻留在数据字典表空间的表或临时表空间中添加列。临时表仅支持ALGOROTHM=COPY。
  • MySQL在INSTANT算法添加列时检查行大小，如果添加超过限制则抛出以下错误。
ERROR 4092 (HY000): Column can't be added with ALGORITHM=INSTANT as after this max possible row size crosses max permiMYssible row size. Try ALGORITHM=INPLACE/COPY.
  • 在使用INSTANT算法添加列之后，表的内部表示形式中的最大列数不能超过1022。错误信息是：
ERROR 4158 (HY000): Column can't be added to tbl_name with ALGORITHM=INSTANT anymore. Please try ALGORITHM=INPLACE/COPY
  • 使用ALGORITHM=INSTANT修改表会产生新的版本，比如添加一列或者多列，删除一列或者多列，或者添加和删除一列或多列。使用INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS进行跟踪，默认值为0。
mysql> SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES 
WHERE NAME LIKE 'test/t1'; 
+---------+--------------------+ 
| NAME | TOTAL_ROW_VERSIONS | 
+---------+--------------------+ 
| test/t1 | 0 |
+---------+--------------------+

允许最大版本号是64，因为每个行版本都需要额外的表元数据空间。当行版本限制达到继续alter table将被拒绝提示以下错误信息：

ERROR 4080 (HY000): Maximum row versions reached for table test/t1. 
No more columns can be added or dropped instantly. Please use COPY/INPLACE.

当对使用过INSTANT算法添加删除的表使用ALTER TABLE或者OPTIMIE TABLE表重建语句进行重建表后，TOTAL_ROW_VERSIONS将会被重置为0。

  • 添加自动递增时不允许并发DML。大量地重新组织数据，使其成为一项昂贵的操作。至少，ALGORITHM=INPLACE,LOCK=SHARED是必需的。

如果使用ALGORITHM=INPLACE添加列，则重新构建表。

删除列

ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INSTANT;

从MySQL 8.0.29开始INSTANT是默认算法，INPLACE是之前默认的。

限制

  • 不可以使用同一条ALTER TABLE语句和不支持ALGORITHM=INSTANT的操作一起执行删除列。
  • 不可以向使用ROW_FORMAT=COMPRESSED的表、具有FULLTEXT索引的表驻留在数据字段表空间的表或临时表中删除列。临时表仅支持ALOGIRITHM=COPY。

版本限制和添加列类似。

如果使用除ALGORITHM=INSTANT以外的算法，则会对数据进行实质性重组，使其成为一种昂贵的操作。

重命名列

ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INSTANT, LOCK=NONE;

从MySQL 8.0.28开始支持使用ALGORITHM=INSTANT重命名列。

允许同时DML,保持相同的数据类型只改变列名。

当保持相同的数据类型和[NOT]NULL属性，只更改列名，则操作始终可在线执行。

只允许使用ALGORITHM=INPLACE重命名从另一个表引用的列。如果你使用ALGORITHM=INSTANT,ALGORITHM=COPY，或者一些其他造成使用这些算法的条件，ALTER TABLE语句失败。

ALGORITHM=INSTANT支持重命名虚拟列，ALGORITHM=INPLACE不支持。

改变数据类型

ALTER TABLE tbl_name CHANGE c1 c1 BIGINT, ALGORITHM=COPY;

改变数据类型仅支持使用ALGORITHM=COPY。

扩充VARCHAR列大小

ALTER TABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;

但是VARCHAR列表示字符串长度字节必须保持不变，例如：

ALTER TABLE tbl_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256); ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

缩小只能使用ALGORITHM=COPY。

设置默认值

ALTER TABLE tbl_name ALTER COLUMN col SET DEFAULT literal, ALGORITHM=INSTANT;

仅修改表元数据。默认列值存储在数据字典中。

删除列默认值

ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT, ALGORITHM=INSTANT;

改变自增值

ALTER TABLE table AUTO_INCREMENT=next_value, ALGORITHM=INPLACE, LOCK=NONE;

修改存在内存中的值，而不是数据文件。

自增值不放在表的元信息中，这样还要修改，因此读取最大值比较合理。

设置列可为NULL

ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NULL, ALGORITHM=INPLACE, LOCK=NONE;

就地重建表。数据被整个重新组织，是一个昂贵的操作。

如果之前是NOT NULL现在弄成NULL应该不需要重建表，如果原来NOT NULL有默认值现在改成了NULL没有默认值默认为NULL那就需要重新组织表。

设置不可为NULL

ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;

就地重建表。想要操作成功需要STRICT_ALL_TABLES 或者 STRICT_TRANS_TABLES SQL_MODE。数据被整体重新组织，是一个昂贵的操作。如果列存在NULL值操作将失败。

参考

15.12 InnoDB and Online DDL

An Overview of DDL Algorithm’s in MySQL ( covers MySQL 8)