MySQL InnoDB Instant schema 变更注意点

在 MySQL 8.0.12 中为 DDL 引入了新的算法，在修改表的定义的时候不会阻塞。第一个 instant 操作是在表的最后增加一个列，这是腾讯游戏团队贡献的。在 8.0.29 中，官方增加在任意位置增加或删除列的 instant 操作。

本文要讲的是盲目使用该特性会带来的危害。

 

默认算法

从 MySQL 8.0.12 开始，对于任意的 DDL 操作，默认的算法就是 instant。这意味着 alter 语句只会修改表在数据字典中的元数据。在操作的准备和执行阶段，不会对表进行排他的元数据锁定，表数据也不会受到影响，从而使操作瞬时完成。

除了instant 算法之外，还有另外两种算法：copy、inplace。

然而，即使是在支持 instant ddl 的场景下，也会有一些限制：表最多支持64个 instant 变更。达到这个限制后，就需要重构表了。

如果在 alter 语句中没有指定 instant 算法，后台会自动选择合适的算法，当然，如果没有按照预期的方式，对于生产环境可能就是一场噩梦。

 

总是显式指定算法

给出的第一个建议就是，总是显式的指定ddl 算法，即使有默认算法。显式指定算法之后，如果mysql 不能使用指定的算法，就会报出错误信息，而不是换成其它的算法继续执行。

SQL > ALTER TABLE t1 DROP col1, ALGORITHM=INSTANT;
ERROR: 4092 (HY000): Maximum row versions reached for table test/t1.
No more columns can be added or dropped instantly. Please use COPY/INPLACE.

 

监控 instant 变更次数

第二个建议就是监控在表上执行 instant 便跟的次数。

mysql在information_schema中保存的行版本信息：

SQL > SELECT NAME, TOTAL_ROW_VERSIONS 
      FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'test/t1';
+---------+--------------------+
| NAME    | TOTAL_ROW_VERSIONS |
+---------+--------------------+
| test/t1 |                 63 |
+---------+--------------------+

在这里，dba还可以对表做最后一次 instant ddl 操作，之后就不可以了。

 

对于 dba，最好监控所有表的 instant ddl 的次数，从而知晓是否要对表做重构操作、或者重置该计数器。可以使用以下的脚本做监控检测：

SQL > SELECT NAME, TOTAL_ROW_VERSIONS, 64-TOTAL_ROW_VERSIONS AS 
             "REMAINING_INSTANT_DDLs", 
             ROUND(TOTAL_ROW_VERSIONS/64 * 100,2) AS "DDLs %"
      FROM INFORMATION_SCHEMA.INNODB_TABLES 
      WHERE TOTAL_ROW_VERSIONS > 0 ORDER BY 2 DESC;
+--------------------------+--------------------+------------------------+--------+
| NAME                     | TOTAL_ROW_VERSIONS | REMAINING_INSTANT_DDLs | DDLs % |
+--------------------------+--------------------+------------------------+--------+
| test/t1                  |                 63 |                      1 |  98.44 |
| test/t                   |                  4 |                     60 |   6.25 |
| test2/t1                 |                  3 |                     61 |   4.69 |
| sbtest/sbtest1           |                  2 |                     62 |   3.13 |
| test/deprecation_warning |                  1 |                     63 |   1.56 |
+--------------------------+--------------------+------------------------+--------+

如果想重置计算器，可以使用 optimze table、或 alter table <tablename> engine=innodb。

 

总之，MySQL 8.0 为 DDL 操作引入的 INSTANT 算法避免了阻塞性变更，从而彻底改变了schema变更方式。不过，由于有 64 次 INSTANT 变更的限制，在需要重建表之前，必须在 ALTER 语句中明确指定算法，以避免出现意想不到的行为。此外，还建议通过 Information_Schema 监控 INSTANT 变更的数量，以避免在不知情的情况下达到 INSTANT 变更限制而出现意外，并谨慎计划表的重建。