☰

如何查找和修复碎片化的 MySQL 表

tag-icon • tag-icon mysql
如何查找和修复碎片化的 MySQL 表

我使用了 MySQLTuner，它指出一些表存在碎片。我使用了

mysqlcheck——优化-A

优化所有表。它修复了一些表，但 MySQLTuner 仍然发现 19 个表存在碎片。我如何才能看到哪些表需要碎片整理？也许 OPTIMIZE TABLE 可以在 mysqlcheck 不起作用的地方起作用？或者我还应该尝试什么？

答案1

简短的回答：

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 表会产生碎片，因此这是正常情况。当创建一个表时，比如说使用包含数据的转储导入，所有行都会无碎片地存储在许多固定大小的页面中。当您更新可变长度的行时，包含此行的页面会分为两个或更多页面来存储更改，并且这两个（或更多）新页面包含填充未使用空间的空白空间。

这不会影响性能，除非碎片过多。什么是碎片过多？让我们看看您要查找的查询：

  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 的表上应用优化表需要花费大量时间，并且会阻塞表几分钟，这在生产中是不切实际的。

因此，如果您考虑在性能方面获得的利益以及优化表所浪费的时间，我建议您不要优化。

如果你认为存储更好，请查看你的比率，看看优化后可以节省多少空间。通常不会太多，所以我更喜欢不优化。

如果你进行优化，下一次更新将通过将页面拆分成两个或更多页面来创建空白区域。但更新碎片表比更新非碎片表要快，因为如果表碎片化，对某一行的更新不一定会拆分页面。

我希望这可以帮助你。

答案2

只是为了补充答案费利佩-罗哈斯您可以将碎片率计算为查询的一部分：

select ENGINE,
  concat(TABLE_SCHEMA, '.', TABLE_NAME) as table_name,
  round(DATA_LENGTH/1024/1024, 2) as data_length,
  round(INDEX_LENGTH/1024/1024, 2) as index_length,
  round(DATA_FREE/1024/1024, 2) 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;

如果一个表的碎片比例很小（小于 5%？），那么您可能可以不管它。

对于任何较大的数据，您都需要根据数据库使用情况、锁定表等来评估对表进行碎片整理的重要性。

答案3

优化表格确实会解决您遇到的问题。

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

如果您有很多数据库，那么另一种方法可能更可取。

我使用 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();

答案4

使用 MySQL 的 InnoDB 引擎的表基本上不需要OPTIMIZEd。

或Data_free的值通常不为零，即使您认为已经完成了对表进行碎片整理的所有工作。此外，该指标只是information_schema.tablesSHOW TABLE STATUS一些碎片化是可能发生的，而且确实会发生。（此外，还有块、撤消列表、索引 BTree 与数据 BTree 等中的浪费空间等。）

并使innodb_file_per_table的使用变得复杂Data_free。如果表位于 中ibdata1，则Data_free指整个表空间；.ibd这是一个相当无用的数字。如果表位于其自己的文件中，则它可能是几 MB 或表大小的几个百分点，以较大者为准。

仅当您已删除很多行数和不打算再填满桌子，可能值得一试OPTIMIZE TABLE。

PARTITIONs也显示出令人不安的数量Data_free，因为每个分区通常显示 4-7MB“可用”。并且这个空间不会消失。

为什么要进行碎片整理？

  • 要将空间归还给操作系统？那么，你可能如果有的话，可以快速实现这一点innodb_file_per_table=1。但是随着行的增加，您将从操作系统中将其取回。
  • 为了加快访问速度？算了吧。磁盘上的块布局相对随机，而且过去几十年一直如此。半个世纪前，重新排列块还是很重要的。
  • 重新平衡 BTree？那么？它们很快就会再次变得不平衡。随机插入的 BTree 的稳定状态是 69%。这甚至还没有被计入Data_free。
  • MySQLTuner 说什么？该产品需要冷却。

历史记录。当我帮助 DBA 处理大部分 MyISAM 表时，我发现大约每 1000 个表中就有 2 个表得到了每月 OPTIMIZE从那时起，我已经处理了数千个 InnoDB 表，但还没有发现可以通过 解决的性能问题OPTIMIZE。（当然，也存在磁盘空间问题，这OPTIMIZE可能会有所帮助，但这很棘手 —— 通常 DBA 没有足够的磁盘空间来运行OPTIMIZE！）

相关内容