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