如何优化 MySQL 表和碎片整理以恢复空间

如果您的应用程序正在对 MySQL 数据库执行大量删除和更新，那么您的 MySQL 数据文件很可能是碎片化的。

这将导致大量未使用的空间，并且还可能影响性能。

因此，强烈建议您持续对 MySQL 表进行碎片整理。

本教程解释了如何优化 MySQL 以对表进行碎片整理并回收未使用的空间。

1. 确定优化表

第一步是确定您的 MySQL 数据库是否有碎片。

连接到您的 MySQL 数据库，并执行以下查询，这将显示每个表中有多少未使用的空间可用。

mysql> use tiamo;

mysql> select table_name,
round(data_length/1024/1024) as data_length_mb, 
round(data_free/1024/1024) as data_free_mb 
 from information_schema.tables 
 where round(data_free/1024/1024) > 500 
 order by data_free_mb;

+------------+----------------+--------------+
| table_name | data_length_mb | data_free_mb |
+------------+----------------+--------------+
| BENEFITS   |           7743 |         4775 |
| DEPARTMENT |          14295 |        13315 |
| EMPLOYEE   |          21633 |        19834 |
+------------+----------------+--------------+

在上面的输出中：

• 这将显示具有最少 500MB 未使用空间的所有表的列表。正如我们在上面看到的，在这个例子中，有 3 个表的未使用空间超过 500MB。
• data_length_mb 列显示总表大小（以 MB 为单位）。例如，EMPLOYEE 表大小约为 21GB。
• data_free_mb 列显示该特定表中未使用的总空间。例如，EMPLOYEE 表中有大约 19GB 的未使用空间。
• 所有这三个表（EMPLOYEE、DEPARTMENT 和 BENEFITS）都非常碎片化，需要对其进行优化以回收未使用的空间。

从文件系统级别，您可以看到各个表文件的大小，如下所示。

文件大小将与您在上述输出中的“data_length_mb”列下看到的相同。

# ls -lh /var/lib/mysql/tiamo/
..
-rw-rw----. 1 mysql mysql  7.6G Apr 23 10:55 BENEFITS.MYD
-rw-rw----. 1 mysql mysql   14G Apr 23 12:53 DEPARTMENT.MYD
-rw-rw----. 1 mysql mysql   22G Apr 23 12:03 EMPLOYEE.MYD
..

在此示例中，EMPLOYEE.MYD 文件在文件系统级别占用了大约 22GB，但其中有很多未使用的空间。如果我们优化这个表，这个文件的大小应该会急剧下降。

2. 使用 OPTIMIZE TABLE 命令进行碎片整理

有两种方法可以优化表。

第一种方法是使用优化表命令，如下所示。

以下示例将优化 EMPLOYEE 表。

mysql> use tiamo;

mysql> OPTIMIZE TABLE EMPLOYEE;

您还可以在单​​个命令中优化多个表，如下所示。

mysql> OPTIMIZE TABLE EMPLOYEE, DEPARTMENT, BENEFITS

关于优化表要记住的几点：

• 可以为 InnoDB 引擎、MyISAM 引擎或 ARCHIVE 表执行优化表。
• 对于 MyISAM 表，它会分析表，对相应的 MySQL 数据文件进行碎片整理，并回收未使用的空间。
• 对于 InnoDB 表，优化表将简单地执行一个更改表来回收空间。
• 如果您有索引，它还会重新搜索索引页面，并更新统计信息。

优化时MySQL会为该表创建一个临时表，优化后删除原表，并将该临时表重命名为原表。

上述优化中，EMPLOYEE表为MyISAM表。

对于此示例，在优化之前，您将看到表的以下 .MYD 文件。

# ls -lh /var/lib/mysql/tiamo/EMPLOYEE.*
-rw-rw----。1 mysql mysql 22G Apr 23 12:03 EMPLOYEE.MYD

当“OPTIMIZE TABLE”命令运行时，你可以看到它已经为这个表创建了一个扩展名为.TMD的临时文件。这个临时文件的大小将不断增长，直到优化表运行。

# ls -lh /var/lib/mysql/tiamo/EMPLOYEE.*
-rw-rw----。1 mysql mysql 22G Apr 23 12:03 EMPLOYEE.MYD
-rw-rw----。1 mysql mysql 500M Apr 23 14:10 EMPLOYEE.TMD

优化表命令完成后，您将看不到临时表。相反，您将看到经过优化且文件大小减小的原始 EMPLOYEE.MYD 文件。

# ls -lh /var/lib/mysql/tiamo/EMPLOYEE.*
-rw-rw----. 1 mysql mysql    2G Apr 23 14:20 EMPLOYEE.MYD

3. 使用 mysqlcheck 命令进行碎片整理

优化表的第二种方法是使用 mysqlcheck 命令，如下所示。

以下示例将优化 DEPARTMENT 表。您将从 Linux 提示符（而不是 MySQL 提示符）执行此命令。

# mysqlcheck -o tiamo DEPARTMENT -u root -pMySQLSecretPwd99
thegeekstuff.DEPARTMENT  OK

注意：mysqlcheck 命令在内部使用“OPTIMIZE TABLE”命令。

在上面的例子中：

• mysqlcheck 是从 Linux 提示符执行的命令。
• -o 选项是指示 mysqlcheck 应该执行“优化表”操作。
• thegeekstuff 是数据库
• DEPARTMENT是geekstuff数据库里面应该优化的表
• -u root 表示mysqlcheck命令应该使用“root”作为mysql用户连接
• -p 表示mysql的root账户密码。请注意 -p 选项和密码之间没有空格。

除了优化之外，您还可以使用mysqlcheck 命令来检查、分析和修复 mysql 数据库中的表。

4.对所有表或所有数据库进行碎片整理

如果要优化特定 MySQL 数据库中的所有表，请使用以下命令。

以下命令将优化位于 geekstuff 数据库中的所有表。

mysqlcheck -o tiamo -u root -pMySQLSecretPwd99

如果您的系统上运行多个数据库，则可以使用以下命令优化系统上所有数据库下的所有表。

以下将优化您系统上的所有数据库。

mysqlcheck -o --all-databases -u root -pMySQLSecretPwd99

5.优化后

优化后，使用以下查询，检查我们在此示例中优化的三个表的总大小和未使用空间大小。

mysql> use tiamo;

mysql> select table_name,
round(data_length/1024/1024) as data_length_mb, 
round(data_free/1024/1024) as data_free_mb 
 from information_schema.tables 
 where table_name in 
 ( 'EMPLOYEE', 'DEPARTMENT', 'BENEFITS' );
 
+------------+----------------+--------------+
| table_name | data_length_mb | data_free_mb |
+------------+----------------+--------------+
| BENEFITS   |           2968 |            0 |
| DEPARTMENT |            980 |            0 |
| EMPLOYEE   |           1799 |            0 |
+------------+----------------+--------------+

正如我们从上面的输出中看到的，优化后这些表的 data_length_mb 急剧减少。此外，data_free_mb 现在为 0，因为不再有碎片。

与原始大小相比，这些表的文件大小现在要小得多。我们现在已经为这些表在文件系统级别回收了大量未使用的空间。

# ls -lh /var/lib/mysql/tiamo/
..
-rw-rw----. 1 mysql mysql    3G Apr 23 14:23 BENEFITS.MYD
-rw-rw----. 1 mysql mysql  980M Apr 23 14:30 DEPARTMENT.MYD
-rw-rw----. 1 mysql mysql    2G Apr 23 14:45 EMPLOYEE.MYD
..

在这个例子中，我们在优化这三个表后回收了大约 37GB 的未使用空间。