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