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