64次更改极限！MySQL DBA如何巧妙规避即时DDL操作的陷阱？

用户1278550

发布于 2024-04-10 11:04:07

1880

发布于 2024-04-10 11:04:07

文章被收录于专栏：idba

原文来自oralcle 官方blog ，参考阅读原文。前言

我们在MySQL 8.0.12版本中引入了一种新的 DDL 算法，当更改表定义时不会阻塞表。第一个即时操作是由腾讯游戏团队贡献的--在表的末尾添加列。

然后在 MySQL 8.0.29 中，我们增加了在表的任何位置添加（或删除）列的可能性。

有关更多信息，请查看Mayank Prasad的文章：[1]，[2]。

在本文中，我想重点讨论使用INSTANT DDL 时可能出现的一些风险。

默认算法

从 MySQL 8.0.12开始，对于任何支持的DDL，默认算法是 INSTANT。这意味着ALTER语句将只修改数据字典中的表元数据。在 DDL 操作的准备和执行阶段不会对表获取独占元数据锁，表数据不受影响，使操作瞬间完成。

另外两种算法是 COPY 和 INPLACE，有关在线DDL操作的详细信息，请参考官方手册。

然而，INSTANT DDL也有一个限制：一个表支持64次即时更改。如果超过64次INSTANT 变更该后的 DDL 需要“重建”表。

如果在ALTER语句（DDL操作）期间没有指定算法（DDL操作），则会默默选择适当的算法。当然，如果没有预期到这一点，在生产环境中可能会导致噩梦般的局面。

始终指定ALGORITHM

因此，第一个建议是在执行 DDL时始终指定算法，即使它是默认的。当指定算法时，如果MySQL无法使用它，它会抛出错误，而不是使用另一种算法执行操作：

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.

监控即时更改

第二个建议也是对表执行的即时更改的数量进行监控。

MySQL在Information_Schema中保留行版本：

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操作，但在那之后，MySQL将无法执行另一个操作。

作为DBA，监控所有表并决定何时需要重建表（以重置计数器）是一个好习惯。

这是一个添加到您的监控工具中的推荐查询的示例：

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 |
+--------------------------+--------------------+------------------------+--------+

要重置计数器并重建表，您可以使用

OPTIMIZE TABLE <table>或
ALTER TABLE <table> ENGINE=InnoDB。

结论

总之，MySQL 8.0引入的INSTANT算法通过避免阻塞更改，彻底改变了模式更改。然而，由于有64次即时更改的限制，在需要重建表之前，明确指定ALTER语句中的算法以避免意外行为至关重要。

通过Information_Schema监控即时更改的数量也值得推荐，以避免在不知不觉中达到即时更改限制，并仔细规划表的重建。