优化 MySQL 中的表和数据库

Mehvish Ashiq 2023年1月30日 2022年5月26日
  1. 何时以及为何在 MySQL 中优化表
  2. 优化 MySQL 中的表
  3. 在 Windows/Ubuntu 20.04 操作系统中使用 MySQL Shell 优化单表或多表
  4. 使用 Windows 终端优化表格
  5. 使用 Ubuntu 20.04 终端优化表格
  6. 优化一个 MySQL 数据库中的所有表
  7. 优化 MySQL 中的所有数据库
优化 MySQL 中的表和数据库

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

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

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

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

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

现在,我们如何知道应该优化哪个表?我们应该优化信息(数据)不断更新的表;例如,事务数据库是表优化的完美候选者。

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

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

  1. 有时,优化不正确的值会导致二级索引的碎片,因此分析如何从压缩特定值中获得更多收益非常重要。这意味着确定优化的正确值非常重要。
  2. 另一种方法是删除索引,优化表,然后重新添加索引。这种方式只适用于表可以在短时间内不使用索引的情况下。

优化 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

输出:

优化 mysql 中的表和数据库——表状态

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

  1. Data_length 表示数据库占用了多少空间。
  2. Data_free 告诉数据库表中已分配但未使用的字节。

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

我们可以使用以下查询为特定数据库中的所有表获取这两个数字(Data_lengthData_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;

输出:

优化 mysql 中的表和数据库 - 以字节为单位的表状态

上面的查询打印表的名称、总空间(以字节为单位)和分配的未使用空间(以字节为单位)。如果你习惯以 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 中的表和数据库 - 优化表

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

示例代码:

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;

输出:

优化 mysql 中的表和数据库——优化表

相同的查询将在 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
Mehvish Ashiq avatar Mehvish Ashiq avatar

Mehvish Ashiq is a former Java Programmer and a Data Science enthusiast who leverages her expertise to help others to learn and grow by creating interesting, useful, and reader-friendly content in Computer Programming, Data Science, and Technology.

LinkedIn GitHub Facebook

相关文章 - MySQL Table

相关文章 - MySQL Database