[转帖]mysql8 ALGORITHM=INSTANT 亿级数据秒速增加字段

在线DDL之快速增加列（秒级别的），并不会造成业务抖动。该功能自 MySQL 8.0.12 版本引入，是由腾讯游戏DBA团队贡献，此功能只适
用于 InnoDB 表。实际上MySQL 5.7就已支持 Online DDL，虽说大部分 DDL 不影响对表DML操作，但是依然会消耗非常多的时间，且占用
额外的磁盘空间，并会造成主从延迟，或者影响表的查询速度。有了这个ALGORITHM=INSTANT 就可应对瞬息万变的需求了。

MySQL在大型表上的 DDL 会带来耗时较久、负载较高、额外空间占用、MDL、主从同步延时等情况。需要特别引起重视，而MySQL的DDL有很
多种方法。
MySQL本身自带三种方法，分别是：copy、inplace、instant。copy算法为最古老的算法，在MySQL5.5及以下为默认算法。
从MySQL5.6开始，引入了inplace算法并且默认使用。inplace算法还包含两种类型：rebuild-table 和 not-rebuild-table。
MySQL使用inplace算法时，会自动判断，能使用not-rebuild-table的情况下会尽量使用，不能的时候才会使用rebuild-table。
当DDL涉及到主键和全文索引相关的操作时，无法使用 not-rebuild-table，必须使用 rebuild-table。其他情况下都会使用 not-rebuild-table。
从MySQL8.0.12开始，引入了instant算法并且默认使用。目前instant算法只支持增加列等少量DDL类型的操作，其他类型仍然会默认使用inplace。

有一些第三方工具也可以实现DDL操作，最常见的是percona的pt-online-schema-change工具(简称为 pt-osc)，和github的gh-ost工具，
均支持 MySQL 5.5 以上的版本。

》》各类工具的对比

一般情况下的建议：如果使用的是 MySQL 5.5 或者 MySQL 5.6，推荐使用 gh-ost
如果使用的是 MySQL 5.7，索引等不涉及修改数据的操作，建议使用默认的 inplace 算法。如果涉及到修改数据(例如增加列)，不关心主
从同步延时的情况下使用默认的 inplace 算法，关心主从同步延时的情况下使用 gh-ost
如果使用的是 MySQL 8.0，推荐使用 MySQL 默认的算法设置，在语句不支持 instant 算法并且在意主从同步延时的情况下使用gh-ost

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

INSTANT是MySQL8.0.12和INPLACE之后的默认算法。

当 INSTANT算法用于添加列时，以下限制适用：
1、添加列不能与ALTER TABLE不支持的其他操作在同一语句中组合ALGORITHM=INSTANT。
2、在MySQL 8.0.29之前，一列只能作为表的最后一列添加。不支持将列添加到其他列中的任何其他位置。从MySQL 8.0.29开始，可以将即时
   添加的列添加到表中的任何位置。
3、不能将列添加到使用ROW_FORMAT=COMPRESSED的表、具有FULLTEXT索引的表、驻留在数据字典表空间中的表或临时表。临时表仅支持 
ALGORITHM=COPY.
4、在MySQL 8.0.29之前，添加列时不会评估行大小限制。但是，在插入和更新表中的行的DML操作期间会检查行大小限制。从8.0.29开始，
   添加列时会检查行大小限制。如果超出限制，则会报告以下错误消息：
"ERROR 4092 (HY000): Column can't be added with ALGORITHM=INSTANT as after this max possible row size crosses max permissible row size. 
   Try ALGORITHM=INPLACE/COPY."

可以在同一 ALTER TABLE语句中添加多个列。例如：
ALTER TABLE t1 ADD COLUMN c2 INT, ADD COLUMN c3 INT, ALGORITHM=INSTANT;

ALTER TABLE ... 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';
        
当通过表重建ALTER TABLE 或OPTIMIZE TABLE 操作重建具有即时添加或删除列的表时，该TOTAL_ROW_VERSIONS值重置为 0。允许的最大
行版本数为 64，因为每个行版本都需要额外的空间用于表元数据。当达到行版本限制时，ADD COLUMN or DROP COLUMN
using ALGORITHM=INSTANT被拒绝，并显示一条错误消息，建议使用 COPY or INPLACE 算法重建表。
'ERROR 4080 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE.'

ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INSTANT;
当 INSTANT算法用于删除列时，以下限制适用：
1、删除列不能与ALTER TABLE不支持的其他操作在同一语句中组合ALGORITHM=INSTANT。
2、不能从使用 ROW_FORMAT=COMPRESSED的表、具有 FULLTEXT索引的表、驻留在数据字典表空间中的表或临时表中删除列。临时表仅支持
   ALGORITHM=COPY.

ALTER TABLE同一语句中可以删除多个列；例如：
ALTER TABLE t1 DROP COLUMN c4, DROP COLUMN c5, ALGORITHM=INSTANT;
每次使用 添加或删除列时ALGORITHM=INSTANT，都会创建一个新的行版本。该INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS
列跟踪表的行版本数。每次立即添加或删除列时，该值都会增加。初始值为 0。

ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INSTANT, LOCK=NONE;
MySQL8.0.28中添加了ALGORITHM=INSTANT对重命名列的支持。早期的MySQL服务器版本仅支持ALGORITHM=INPLACE or ALGORITHM=COPY命名列。
要允许并发 DML，请保持相同的数据类型并仅更改列名。
当您保持相同的数据类型和[NOT] NULL属性时，只需更改列名，就可以始终在线执行操作。
仅允许重命名从另一个表引用的列ALGORITHM=INPLACE。如果您使用ALGORITHM=INSTANTor ALGORITHM=COPY或其他操作使用这些算法的条件
  ，则 ALTER TABLE语句将失败。
ALGORITHM=INSTANT支持重命名虚拟列；ALGORITHM=INPLACE不支持。
ALGORITHM=INSTANT和ALGORITHM=INPLACE不支持在同一语句中添加或删除虚拟列时重命名列。在这种情况下仅ALGORITHM=COPY支持。

ALTER TABLE tbl_name CHANGE c1 c1 BIGINT, ALGORITHM=COPY;
仅支持更改列数据类型 ALGORITHM=COPY。

ALTER TABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;
列所需的长度字节数VARCHAR必须保持不变。对于VARCHAR大小为0到255字节的列，需要一个长度字节来对值进行编码。对于VARCHAR大小为
256 字节或更大的列，需要两个长度字节。因此，INPLACE ALTER TABLE仅支持将VARCHAR列大小从0字节增加到255字节，或从256字节增
加到更大的大小。INPLACE ALTER TABLE不支持增加VARCHAR列从小于256字节到大小等于或大于256字节。在这种情况下，所需的长度字节
数从1变为2，仅表拷贝 ( ALGORITHM=COPY) 支持。例如，尝试VARCHAR使用就地将单字节字符集的列大小从VARCHAR(255)更改为
VARCHAR(256) 会ALTER TABLE返回此错误：
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.

不支持VARCHAR使用就地减小大小。ALTER TABLE减小VARCHAR大小需要使用ALGORITHM=COPY

ALTER TABLE tbl_name ALTER COLUMN col SET DEFAULT literal, ALGORITHM=INSTANT;
仅修改表元数据。默认列值存储在数据字典中。
1.
2.

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

ALTER TABLE table AUTO_INCREMENT=next_value, ALGORITHM=INPLACE, LOCK=NONE;
修改存储在内存中的值，而不是数据文件。
在使用复制或分片的分布式系统中，您有时会将表的自动增量计数器重置为特定值。插入到表中的下一行使用其自动增量列的指定值。您也可
以在数据仓库环境中使用此技术，在该环境中，您会定期清空所有表并重新加载它们，然后从 1 重新启动自动递增序列。

ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NULL, ALGORITHM=INPLACE, LOCK=NONE;
就地重建表。数据被大量重组，使其成为一项昂贵的操作。
1.
2.

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 值，则操作失败。服务
器禁止对可能导致失去参照完整性的外键列进行更改。
1.
2.
3.

CREATE TABLE t1 (c1 ENUM('a', 'b', 'c'));
ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd'), ALGORITHM=INSTANT;
只要数据类型的存储大小不变，就可以立即或就地执行通过在有效成员值列表的末尾添加新枚举或集合成员来 修改ENUM或列的定义。
例如，将成员添加到具有8个成员的列会将每个值所需的存储空间从1个字节更改为2个字节；这需要一个表格副本。在列表中间添加成员会导致
对现有成员重新编号，这需要一个表拷贝。