我们正在运行MariaDB v 10.1.30,测试一个脚本来运行数据库维护脚本,以使用OPTIMIZE TABLE命令使用设置innodb_defragment = 1的新10.1.1补丁对表进行碎片整理和重建索引。
我已经使用 Alogorithm = INPLACE 测试了 Alter 表,工作正常,但我正在尝试利用 innodb_defragment 并使用优化来避免在重建表时创建临时文件,就像通过 Alter table INPLACE 算法所做的那样。
在使用优化时,没有创建临时表,但是表被锁定不允许并发连接,而使用 Alogorithm = INPLACE 的更改表则不是这种情况,但是文档提到优化是使用 INPLACE 算法完成的。
https://mariadb.org/defragmenting-unused-space-on-innodb-tablespace/
这是一个错误还是我在这里遗漏了什么,请告知。
速度的好处几乎为零。
-
"点查询"(您拥有键并可以直接转到行)取决于 BTree 的深度。 对于一百万行,深度约为 3。 对于一万亿行,大约 6. 优化表格不太可能缩小深度。
-
"范围扫描"(
BETWEEN
、>
等)穿过一个街区,查看每一行。 然后它跳到下一个块(通过链接),直到找到所需的所有行。 当然,您将在未优化的表中触摸更多块,但大部分工作是在访问每一行上。
对空间的好处是有限的。
-
INSERT
可以添加到非完整块中,也可以将完整块拆分为两个半完整块。 稍后,两个相邻的、有点空的块将合并在一起。 因此,BTree 自然倾向于平均块已满 69% 的状态。 也就是说,OPTIMIZE TABLE
对空间的好处是有限的。 -
换个说法,
OPTIMIZE
可能会将表的磁盘占用量缩小到原来的 69%,但后续操作只会再次增加表。 -
如果您使用的是
innodb_file_per_table=OFF
,则OPTIMIZE
无法将可用块返回到操作系统。 这样的块可以重用于将来的INSERTs
。
OPTIMIZE TABLE
是侵入性的。
-
它会复制表,并在此过程中将其锁定。 这对于需要 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。