如何find并修复碎片化的MySQL表

简短的回答：

select ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free from information_schema.tables where DATA_FREE > 0; 

“你一定知道”的答案

首先你必须明白当更新一行时Mysql表会被分割，所以这是正常情况。 当创build表时，可以说使用带有数据的转储导入，所有的行都存储在许多固定大小的页面中，没有碎片。 更新可变长度的行时，包含此行的页面被分成两个或多个页面以存储更改，这些新的两个（或多个）页面包含填充未使用空间的空格。

这不会影响性能，除非碎片增长太多。 什么是太多碎片，让我们看看你正在寻找的查询：

  select ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free from information_schema.tables where DATA_FREE > 0; 

DATA_LENGTH和INDEX_LENGTH是数据和索引正在使用的空间，DATA_FREE是所有表页面（碎片）中未使用的总字节数。

这是一个真正的生产表的例子

 | ENGINE | TABLE_NAME | data_length | index_length | data_free | | InnoDB | comments | 896 | 316 | 5 | 

在这种情况下，我们有一个使用（896 + 316）= 1212 MB的表，并有一个5 MB的可用空间的数据。 这意味着一个“碎片化的比例”：

 5/1212 = 0.0041 

…这是一个非常低的“碎片比例”。

我一直在使用比例接近0.2的表格（意思是占空间的20％），即使我优化了表格，性能也是一样。 但是在800MB的表上应用一个优化表需要花费很多时间，并在几分钟内阻塞表，这对生产是不切实际的。

所以，如果你考虑到你在performance上的成就和在优化表格方面所浪费的时间，我宁愿不优化。

如果您认为存储更好，请查看您的比例，并查看优化时可以节省多less空间。 通常不会太多，所以我不喜欢优化。

如果进行优化，下一次更新将通过分割两个或多个页面来创build空白空间。 但是，更新碎片表比不碎片碎片更快，因为如果表被碎片化，行上的更新不一定会分割页面。

我希望这可以帮助你。

只需添加来自Felipe-Rojas的答案，就可以计算出片段比例作为查询的一部分：

 select ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free, (data_free/(index_length+data_length)) as frag_ratio from information_schema.tables where DATA_FREE > 0 order by frag_ratio desc; 

如果一张桌子被分成一小部分（less于5％？），那么你可以放弃它。

任何更大的，你将需要评估根据你的数据库的使用情况，locking表等等，这是多么重要的碎片整理表。

Optimize Table将确实解决您遇到的问题。

如果你只有几个数据库，那么你可以使用PHPMyAdmin来浏览所有的数据库。 select开销表，然后select优化。

如果你有很多的数据库，那么另一种方法可能会更好。

我在cron中使用以下PHP脚本安装程序每小时运行一次。

 $DB = new mysqli ('localhost', 'DbUser', 'DbPassword'); $results = $DB->query('show databases'); $allDbs = array(); while ($row = $results->fetch_array(MYSQLI_NUM)) { $allDbs[] = $row[0]; } $results->close(); foreach ($allDbs as $dbName) { if ($dbName != 'information_schema' && $dbName != 'mysql') { $DB->select_db($dbName); $results = $DB->query('SHOW TABLE STATUS WHERE Data_free > 0'); if ($results->num_rows > 0) { while ($row = $results->fetch_assoc()) { $DB->query('optimize table ' . $row['Name']); } } $results->close(); } } $DB->close(); 

我遇到了这个页面，发现Felipe-Rojas和sysadmiral的询问非常有帮助。 但在我的情况下，我在WHM的phpMyAdmin中运行查询，只有TABLE_NAME没有那么有用，因为数据库没有列出，而且有几个数据库具有相同的表名。 所以，简单地添加TABLE_SCHEMA也会提供该列。

 select ENGINE, TABLE_SCHEMA, TABLE_NAME, Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free, (data_free/(index_length+data_length)) as frag_ratio from information_schema.tables where DATA_FREE > 0 order by frag_ratio desc 

显示数据库

 ENGINE | TABLE_SCHEMA | TABLE_NAME | data_length | index_length | data_free | frag_ratio InnoDB | db_name | db_table | 0 | 0 | 8 | 170.6667 

为了“修复”我使用phpMyAdmin中的碎片整理表链接为每个导致phpMyAdmin执行高“frag_ratio”的表：

 ALTER TABLE `table_name` ENGINE = InnoDB;