MySQL 效能調優：從慢查詢到索引優化

在 Linux 伺服器上運行 MySQL 時，效能瓶頸往往不是硬體資源不足，而是 SQL 查詢語法或索引配置不當所導致。對於中階使用者而言，掌握如何診斷慢查詢並透過索引優化來解決問題，是提升資料庫響應速度的關鍵。本文將帶領您逐步完成從啟用慢查詢日誌到分析並優化索引的完整流程。

MySQL 內建了「慢查詢日誌」（Slow Query Log），用於記錄執行時間超過指定閾值的 SQL 語句。這是診斷效能問題的起點。

首先，我們需要確認當前 MySQL 的慢查詢設定。登入 MySQL 後執行：

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

通常預設情況下，慢查詢日誌是關閉的，且閾值可能設定為 10 秒，這對於現代應用來說太長了。建議將閾值調整為 1 秒或更短，以便捕捉潛在問題。

在 Ubuntu 22.04 或 Debian 12 上，您可以編輯 MySQL 的設定檔 /etc/mysql/mysql.conf.d/mysqld.cnf（或 /etc/my.cnf），在 [mysqld] 區塊中加入以下參數：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

這裡的 log_queries_not_using_indexes = 1 非常重要，它能直接記錄那些沒有使用索引的查詢，這通常是效能問題的根源。

修改完設定檔後，重新啟動 MySQL 服務以套用變更：

sudo systemctl restart mysql

當系統運行一段時間後，慢查詢日誌 /var/log/mysql/mysql-slow.log 中會累積大量資料。直接閱讀原始日誌並不容易理解，我們可以使用官方工具 mysqldumpslow 進行彙整分析。

例如，找出執行時間最長的前 10 筆查詢：

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

參數說明：

如果您希望看到更詳細的查詢內容，可以使用 pt-query-digest（來自 Percona Toolkit），它比 mysqldumpslow 提供更深入的統計資訊，但需要先安裝：

sudo apt install percona-toolkit
pt-query-digest /var/log/mysql/mysql-slow.log

假設分析結果顯示以下查詢頻繁出現且執行時間過長：

SELECT * FROM users WHERE email = 'user@example.com';

如果 users 表很大，且 email 欄位沒有索引，MySQL 將執行全表掃描（Full Table Scan），導致效能急劇下降。

首先，確認該表目前的索引結構：

SHOW INDEX FROM users;

如果輸出中沒有 email 欄位的索引，這就是問題所在。

針對經常用於 WHERE 條件的欄位建立索引。使用 EXPLAIN 來驗證查詢計劃是否會使用到索引：

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

觀察輸出中的 key 欄位。如果為 NULL，表示未使用索引。建立索引後：

ALTER TABLE users ADD INDEX idx_email (email);

再次執行 EXPLAIN，您應該會看到 key 欄位顯示為 idx_email，且 type 變為 ref 或 const，這代表查詢效率大幅提升。

1. 索引過多反而降低效能？ 是的，每個索引都會增加 INSERT、UPDATE 和 DELETE 的開銷，因為資料庫必須同步更新索引樹。因此，索引應僅針對頻繁查詢且數據量大的欄位。一般建議單一表的索引數量不要超過 5-6 個。

2. 為什麼加了索引還是很慢？ 可能原因包括：

數據類型不匹配：例如欄位是 VARCHAR，但查詢時傳入的是數字，導致隱式轉換失效。
函數操作：在 WHERE 子句中使用函數（如 WHERE YEAR(create_date) = 2023）會使索引失效。應改為範圍查詢：WHERE create_date >= '2023-01-01' AND create_date < '2024-01-01'。
索引選擇性低：如果欄位只有極少幾個值（如 gender），索引效果有限，資料庫可能認為全表掃描更快而忽略索引。

MySQL 效能調優是一個持續的過程。透過啟用慢查詢日誌，您可以精確定位瓶頸；利用 EXPLAIN 和索引工具，您可以針對性地優化查詢。記住，沒有萬能的索引，只有最適合業務場景的索引設計。定期檢視慢查詢日誌，並根據實際流量調整索引策略，是維持資料庫高效運行的最佳實踐。