对所有碎片化的表进行Mysql OPTIMIZE TABLE优化

Question

我希望能够优化所有当前碎片化的表。这些表应该具有information_schema.DATA_FREE > 0。

在SQL中是否可以使用一个命令来优化所有具有此属性的表，还是我需要编写外部代码来完成这个任务？

- Wienczny

Answer 1

你可以像这样做:

SELECT concat("OPTIMIZE TABLE ", table_schema,".",table_name,";")
FROM tables
WHERE DATA_FREE > 0
INTO OUTFILE '/tmp/optimize.sql';
SOURCE '/tmp/optimize.sql';

如果第一个方法失败了，可以尝试以下方法：

SELECT concat("OPTIMIZE TABLE ", table_schema,".",table_name,";")
FROM information_schema.tables
WHERE DATA_FREE > 0
INTO OUTFILE '/tmp/optimize.sql';
SOURCE /tmp/optimize.sql;

- Max Bube

---

对我来说需要做一些微小的改动，但方法很好。从information_schema.tables中选择DATA_FREE大于0的表，并使用concat函数连接"OPTIMIZE TABLE "、table_schema和table_name，生成一个语句并输出到'/tmp/optimize.sql'文件中。然后执行该文件中的语句，以优化这些表。 - El Yobo

你需要在 table_schema 和 table_name 周围加上一些反引号，否则我欠你一瓶啤酒，非常感谢。 - Question Mark

Answer 2

好的，这是一个旧帖子，但这是需要的。

Phil Dufault http://www.dufault.info/blog/a-script-to-optimize-fragmented-tables-in-mysql/ 编写了一个非常棒的脚本，在 Github 上可用： https://github.com/pdufault/mysqlfragfinder/blob/master/mysqlfragfinder.sh

vim mysqlfragfinder.sh
#copy paste from github
chmod +x ./mysqlfragfinder.sh
./mysqlfragfinder.sh --user root_username --password root_password

就是这样。我已经在多台服务器上使用它一段时间了。

- jacob karapatakis

Answer 3

我知道这里有一个答案。但是MySQL对于INNODB数据库有以下建议：

如果您定期执行“null” ALTER TABLE操作，MySQL会重建表格，从而加速索引扫描：

ALTER TABLE tbl_name ENGINE=INNODB

在此处找到 http://dev.mysql.com/doc/refman/5.0/en/innodb-file-defragmenting.html

- Richard Dev