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

我用MySQLTuner指出一些表被分割。 我用了

mysqlcheck –optimize -A

优化所有表格。 它修复了一些表,但MySQLTuner仍然发现19个表分割。 我怎么能看到哪些表需要碎片整理? 也许OPTIMIZE TABLE将工作在mysqlcheck没有? 还有什么我应该尝试?

简短的回答:

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;