避免陷阱：MySQL表结构调整必知

在我们之前的文章中，我们已经探讨了修改数据库表结构时需要注意的事项，包括那些会导致表重建的操作，以及不同的表结构修改算法。本篇文章，我们将继续讨论instant算法。

在MySQL的 8.0.12 版本中，引入了instant的新算法。虽然这种算法有一定的使用限制，但它允许用户在仅修改元数据的前提下，实现字段的快速增加。

“

👻 当你需要增加字段时，你可能会在脚本中指定该字段的具体位置（例如使用 after 语句）。然而，DBA 可能会建议你不要这样做，而是将新字段直接增加到表的末尾

👻 另外，值得注意的是，在删除字段时，instant 算法是无法使用的。

MySQL 8.0.29 版本对instant算法进行了优化：

我们曾经提到过，增加字段时尽量明确指定instant（考虑到存在64个instant字段的限制）。

“

某一天，当你在增加字段时，发现操作未能立即完成并且耗时较长，很可能是因为表中已经有64个instant字段。为避免这种情况，建议在操作中明确指定算法。

当然，将表的instant字段数量纳入监控系统是一个明智的做法。一旦字段数量超过64，系统便可以触发告警，然后在业务低峰期，再对表进行重建操作（instant列归0）。

我们创建下表：

create table demo_20240518_001 
(
 id int auto_increment primary key ,
 uname varchar(60),
 address varchar(60),
 age mediumint
);

然后使用下面的SQL查看表的一些信息：

select NAME,
 N_COLS,
 INSTANT_COLS,
 TOTAL_ROW_VERSIONS
from
 information_schema.innodb_tables
where
 NAME = 'amias/demo_20240518_001';

✅ 结果：

此时，你可能会好奇，我们在建表时明明只有 4 个字段，为什么N_COLS是 7。

其实这个问题，在我们之前的分享中就已经提到过，另外三个是 InnoDB 的隐藏字段（db_row_id, db_trx_id, db_roll_ptr）。

⁉️ instant字段数量从哪里看？

字段TOTAL_ROW_VERSIONS 记录了row version，即instant的字段数量（准确点应该是instant操作次数），目前是0。

给表 demo_20240518_001 增加字段score。

alter table demo_20240518_001 
 add column score tinyint, algorithm = instant;

此时TOTAL_ROW_VERSIONS更新为1。

✅ 结果：

接着，我们修改下字段数据类型（修改字段数据类型会引发重建表）。

alter table demo_20240518_001 modify score int;

✅ 结果：

TOTAL_ROW_VERSIONS 已经重置成了0。

跟踪表空间文件的状态，也可以发现该表被重建。

“

即便在该操作中指定 algorithm = instant ，也会由于算法不支持而报错。

行记录中 version number 记录在哪里？

其实在记录头中info-bits中有1位之前未被使用，所以直接拿来用。

有人可能会质疑，仅仅64个字段的instant是否不足？实际上，这个数量通常已经足够使用。出于性能优化的考虑，我们在数据库表结构设计时通常不推荐创建过宽的表。

未来，MySQL可能会放宽这一限制，允许字段数量超过64个。

在8.0.29版本之后，MySQL 对instant功能进行了改进，但如果您想避免一些可能由instant引起的异常bug，我建议在升级数据库时直接跳至8.0.32版本。

原文始发于微信公众号（小新数据库）：避免陷阱：MySQL表结构调整必知

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/290307.html