优化 MySQL 中的表和数据库
- 何时以及为何在 MySQL 中优化表
- 优化 MySQL 中的表
- 在 Windows/Ubuntu 20.04 操作系统中使用 MySQL Shell 优化单表或多表
- 使用 Windows 终端优化表格
- 使用 Ubuntu 20.04 终端优化表格
- 优化一个 MySQL 数据库中的所有表
- 优化 MySQL 中的所有数据库
本教程是关于如何在 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告诉数据库表中已分配但未使用的字节。
此信息指导我们确定需要优化的表以及之后我们将回收的空间量。
我们可以使用以下查询为特定数据库中的所有表获取这两个数字（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;
输出：
相同的查询将在 Linux 操作系统 (Ubuntu 20.04) 中的 MySQL shell 上运行。
使用 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
