迹忆客 专注技术分享

本教程是关于如何在 MySQL 中优化表和数据库的详尽指南。我们将使用两个操作系统，Windows 和 Linux (Ubuntu 20.04)。

它还介绍了 MySQL 中优化的重要性。

何时以及为何在 MySQL 中优化表

如果我们的应用程序在数据库上执行许多 DELETE 和 UPDATE 操作，MySQL 中的数据文件碎片化的可能性更高。它会导致未使用的空间，这也会影响性能。

我们非常需要持续对 MySQL 表进行碎片整理并回收未使用的空间。这就是我们需要在 MySQL 中进行表优化的地方，它支持在专用存储服务器中重新排序数据，最终提高数据输入和输出的性能和速度。

现在，我们如何知道应该优化哪个表？我们应该优化信息（数据）不断更新的表；例如，事务数据库是表优化的完美候选者。

但是，优化查询可能会消耗更多时间，具体取决于表和数据库的大小。因此，事务系统将表锁定多个小时是不好的。

相反，我们可以在 INNODB 引擎表中尝试一些技巧。下面列出了一些技巧：

优化 MySQL 中的表

首先，我们应该分析我们要优化的表。我们必须使用以下命令连接到我们的数据库。

示例代码：

-- Syntax: Use your_database_name;
mysql> USE test;

与所需数据库连接后，使用以下查询获取表的状态。

示例代码：

-- Syntax: SHOW TABLE STATUS LIKE "your_table_name" \G
mysql> SHOW TABLE STATUS LIKE "test_table" \G

输出：

我们有两个重要的属性可以知道我们是否应该优化这个表。

此信息指导我们确定需要优化的表以及之后我们将回收的空间量。

我们可以使用以下查询为特定数据库中的所有表获取这两个数字（Data_length 和 Data_free）。目前，我们在 test 数据库中只有一个名为 test_table 的表。

示例代码：

mysql> SELECT TABLE_NAME, data_length, data_free
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

输出：

上面的查询打印表的名称、总空间（以字节为单位）和分配的未使用空间（以字节为单位）。如果你习惯以 MB 为单位工作，则可以使用以下查询以 MB 为单位获取输出。

示例代码：

mysql> SELECT TABLE_NAME,
    -> round(data_length/1024/1024) AS Data_Length_in_MBs,
    -> round(data_free/1024/1024) AS Data_Free_in_MBs
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

尽管给定的示例表没有严重碎片化，但我们可以使用 OPTIMIZE TABLE 命令回收空间。

我们将学习使用 MySQL shell 和终端优化单个/多个表或数据库。

在 Windows/Ubuntu 20.04 操作系统中使用 MySQL Shell 优化单表或多表

示例代码：

mysql> OPTIMIZE TABLE test_table;

输出：

要优化多个表，请使用以下查询。

示例代码：

mysql> OPTIMIZE TABLE tableName1, tableName2;

现在，使用以下命令确认所需的表已优化。

示例代码：

mysql> SELECT TABLE_NAME, data_length, data_free
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

输出：

使用 Windows 终端优化表格

示例代码：

-- Syntax mysqlcheck -o <schema> <table> -u <username> -p <password>
mysqlcheck -o test test_table -u root -p

一旦我们编写了上面给出的命令并点击 Enter，我们将被要求输入 MySQL root 密码。输入那个。

我们必须在 bin 文件夹中才能执行查询（参见以下示例）。

示例代码：

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqlcheck -o test test_table -u root -p
Enter password: *****

使用 Ubuntu 20.04 终端优化表格

如果我们使用 sudo su 命令以超级用户身份登录，则执行下面给出的查询。它只会询问 MySQL root 密码。

示例代码：

mysqlcheck -o test test_table -u root -p

如果我们没有以超级用户身份登录，我们将执行 mysqlcheck 命令。在这里，我们将被要求输入系统的 root 密码和 MySQL root 密码。

示例代码：

sudo mysqlcheck -o test test_table -u root -p

优化一个 MySQL 数据库中的所有表

使用 Windows 命令行优化所有表：

-- Syntax: mysqlcheck -o your_database_name -u username -pPassword
mysqlcheck -o test -u root -p

使用 Ubuntu 终端优化所有表：

-- if you are signed in as a superuser
mysqlcheck -o test -u root -p

-- if you are not signed in as a superuser
sudo mysqlcheck -o test -u root -p

优化 MySQL 中的所有数据库

使用 Windows 命令行优化所有数据库：

-- Syntax: mysqlcheck -o --all-databases -u username -pPassword
mysqlcheck -o --all-databases -u root -p

使用 Ubuntu 终端优化所有数据库：

-- if you are signed in as a superuser
mysqlcheck -o --all-databases -u root -p

-- if you are not signed in as a superuser
sudo mysqlcheck -o --all-databases -u root -p