mysql - 使用 OPTIMIZE 对 Mariadb 表进行碎片整理

Question

我们正在运行 MariaDB v 10.1.30，通过使用设置 innodb_defragment = 1 的新 10.1.1 补丁，测试运行数据库维护脚本以使用 OPTIMIZE TABLE 命令对表进行碎片整理和重建索引的脚本。

我已经使用 Alogorithm = INPLACE 测试了 Alter Table，工作正常，但我正在尝试使用 innodb_defragment 并使用优化来避免在按照 Alter table INPLACE 算法重建表时创建临时文件。

在使用 Optimize 时，没有创建临时表，但是该表被锁定，不允许并发连接，而​​使用 Alogorithm = INPLACE 的 Alter Table 则不是这种情况，但是文档提到优化是使用 INPLACE 算法完成的。

https://mariadb.org/defragmenting-unused-space-on-innodb-tablespace/

这是一个错误还是我在这里遗漏了什么，请指教。

Answer 1

速度的好处几乎为零。

• “点查询”(您拥有键并可以直接转到行)取决于 BTree 的深度。对于一百万行，深度约为 3。对于 万亿 行，深度约为 6。优化表不太可能缩小深度。

• “范围扫描”(BETWEEN、> 等)遍历一个 block ，查看每一行。然后它跳(通过链接)到下一个 block ，直到找到所有需要的行。当然，您会接触到未优化表中的更多 block ，但大部分工作都在访问每一行上。

空间的好处是有限的。

• INSERT 可以添加到一个非完整的 block ，也可以将一个完整的 block 拆分为两个半完整的 block 。稍后，两个相邻的、有点空的 block 将合并在一起。因此，BTree 自然会倾向于 average block 已满 69% 的状态。也就是说，OPTIMIZE TABLE 对 space 的好处是有限的。

• 换句话说，OPTIMIZE 可能会将表的磁盘占用空间缩小到原来的 69%，但后续操作只会再次增大表。

  <

• 如果您正在使用 innodb_file_per_table=OFF，则 OPTIMIZE 无法将空闲 block 返回给操作系统。此类 block 可重复用于将来的INSERT。

OPTIMIZE TABLE 具有侵入性。

• 它复制表格，并在此过程中锁定它。这对于需要 100% 正常运行时间的网站来说是 Not Acceptable 。

• 如果您正在使用复制，后续写入可能会堆积在 OPTIMIZE 之后，从而使从服务器无法保持最新​​。

大删除

• 删除大量行后，OPTIMIZE 可能会有好处，但请检查 69% 的估计值。

• 如果大删除很常见，也许您还应该做其他事情。参见 http://mysql.rjweb.org/doc.php/deletebig

历史和内部结构

• 旧版本以一种直接的方式OPTIMIZE:创建一个新表(相同的模式)；将行复制到其中:重命名表；降低。不允许写入。
• ALGORITHM=INPLACE 可能 锁定几个 block ，将它们组合起来填满一个 block ，然后向前滑动。这需要一定程度的锁定。根据问题，听起来它只是锁定了整个表。
• 请注意，每个 BTree(PK+Data，或二级索引)可以独立“优化”。但是没有命令允许仅对主 BTree(PK+数据)执行此操作。可以通过 DROP INDEX + ADD INDEX 优化单个 secondary 索引，但这会丢失索引。相反，请考虑执行 NOCOPY ADD INDEX，然后 INSTANT DROP INDEX。注意:这可能会影响 USE_INDEX 或 FORCE INDEX(如果您使用此类)。

(注意:此答案适用于 InnoDB，不适用于 MyISAM。)