使用优化的Mariadb表碎片整理

Question

我们正在运行MariaDB v10.1.30，测试一个脚本来运行数据库维护脚本，以便对表进行碎片整理，并通过使用新的设置innodb_defragment =1的10.1.1修补程序，使用优化表命令重新生成索引。

我已经用algorithm = INPLACE算法测试了Alter，工作得很好，但是我尝试使用innodb_defragment并使用优化来避免像Alter算法那样重新构建表时创建临时文件。

在使用优化时，没有创建临时表，但是表被锁定不允许并发连接--这与Alter的Alogorithm = INPLACE不同，但是文档提到优化是使用内部算法完成的。

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

这是窃听器还是我在这里漏掉了什么，请告诉我。

Answer 1

的好处实际上是零。。

• “点查询”(其中有键并可以直接进入行)取决于BTree的深度。对于一百万行，深度大约是3。对于1万亿行，大约6。优化一个表不太可能缩小深度。
• 一个“范围扫描”(BETWEEN，>等)走过一个街区，查看每一行。然后(通过链接)跳到下一个块，直到找到所需的所有行。当然，您将在未优化的表中接触到更多的块，但是大部分工作是访问每一行。

的利益有限。

• 一个INSERT可以添加到一个非满块中，也可以将一个完整块分割成两个半满块。稍后，两个相邻的、多少为空的块将合并在一起。因此，BTree自然会被吸引到平均块已满69%的状态。也就是说，OPTIMIZE TABLE对空间的好处是有限的。
• 使用不同的措辞，OPTIMIZE可能会将表的磁盘占用空间缩小到原来的69%，但随后的操作只会使表再次增长。
• 如果您使用的是innodb_file_per_table=OFF，那么OPTIMIZE就不能将空闲块返回到操作系统。这样的块可以在将来的INSERTs中重用。

OPTIMIZE TABLE 是 invasive.

• 它复制表，在处理过程中锁定它。对于需要100%正常运行时间的站点来说，这是不可接受的。
• 如果您使用的是复制，那么后续的写操作可能会在OPTIMIZE后面堆叠起来，从而使从服务器无法达到每秒的水平。

大删除

• 删除大量行后，可能会对OPTIMIZE有好处，但请检查69%的估计值。
• 如果大的删除是常见的情况，也许还有其他的事情需要你去做。请参阅http://mysql.rjweb.org/doc.php/deletebig

历史和内部件

• 旧版本以一种简单的方式执行OPTIMIZE：创建一个新表(相同的模式)；将行复制到其中:重命名表；删除。无法允许写入。
• ALGORITHM=INPLACE可能会锁定几个块，将它们组合起来填充一个块，然后向前滑动。这需要一定程度的锁定。基于这个问题，听起来就像是把整个桌子都锁上了。
• 请注意，每个BTree ( PK+Data或辅助索引)都可以独立地进行“优化”。但是没有任何命令允许只对主BTree (PK+data)执行此操作。优化单个二级索引可以由DROP INDEX + ADD INDEX完成，但这会丢失索引。相反，考虑做一个NOCOPY ADD INDEX，然后是INSTANT DROP INDEX。警告:如果您正在使用USE_INDEX或FORCE INDEX，则可能会受到影响。

(请注意:这个答案适用于InnoDB，而不是MyISAM。)