MySQL SQL 查詢優化實戰指南：索引、EXPLAIN 與配置調校三步走

本文系統闡述 MySQL 查詢優化的標準化三步驟：建立有效索引、EXPLAIN 分析執行計畫、調整關鍵配置參數，並以雙表關聯查詢為例，展示從全表掃描到索引覆蓋的劇效提升。

步驟 1：索引 — 查詢加速的基石

MySQL 支援 B-Tree 索引 (預設)、全文索引、空間索引。每表最多 64 個索引 (InnoDB)，單一索引最多 16 欄位 (MySQL 5.7+)。

建立索引語法

CREATE INDEX idx_username ON users(username);
-- 複合索引 (最左前綴原則)
CREATE INDEX idx_user_status ON users(username, status);

索引選擇性原則

• 高選擇性 (基數大，如 UUID、Email、身分證) → 索引效果最好。
• 低選擇性 (性別、狀態、布林) → 單獨索引效果差，可納入複合索引次序靠後。
• 最左前綴：複合索引 (a,b,c) 可支援 a、a,b、a,b,c 查詢，不可跳過 a 直接用 b,c。

步驟 2：EXPLAIN — 透視執行計畫

實戰案例：查詢印度城市

EXPLAIN SELECT city.name, city.district
FROM city JOIN country ON city.countrycode = country.code
WHERE country.code = 'IND';

優化前 (city.countrycode 無索引)

| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
|  1 | SIMPLE      | country | const| PRIMARY       | PRIMARY| 3     | const|    1 | Using index |
|  1 | SIMPLE      | city    | ALL  | NULL          | NULL | NULL    | NULL | 4079 | Using where |

建立索引

CREATE INDEX idx_countrycode ON city(countrycode);

優化後

| id | select_type | table   | type | possible_keys | key        | key_len | ref  | rows | Extra       |
|  1 | SIMPLE      | country | const| PRIMARY       | PRIMARY    | 3       | const|    1 | Using index |
|  1 | SIMPLE      | city    | ref  | idx_countrycode| idx_countrycode| 3   | const|  333 | Using where |

掃描行數從 4079 降至 333，**減少 91.8%**。

進階：覆蓋索引

若查詢僅需索引欄位，Extra 顯示 Using index，完全避免回表：

CREATE INDEX idx_ccode_name ON city(countrycode, name, district);
-- 查詢
SELECT name, district FROM city WHERE countrycode = 'IND';
-- Extra: Using index

步驟 3：關鍵配置參數調校

優化檢查清單

1. ☐ 開啟慢查詢日誌 (slow_query_log=1, long_query_time=1)
2. ☐ 定期分析 Top 10 慢查詢 (pt-query-digest)
3. ☐ 對每條慢查詢執行 EXPLAIN
4. ☐ 檢查 type 是否為 ALL/index (需優化)
5. ☐ 檢查 rows 是否合理 (應遠小於總行數)
6. ☐ 檢查 Extra 是否有 Using temporary/Using filesort
7. ☐ 補齊索引、重寫 SQL、調整參數
8. ☐ 壓測驗證 QPS、延遲、資源使用率

小結

「索引是矛，EXPLAIN 是盾，參數是內功」。系統化執行「慢查詢收集 → EXPLAIN 診斷 → 索引/SQL/參數 三位一體優化 → 壓測驗證」，是 MySQL 效能調優的黃金法則。切勿盲目加索引或調參數，必須以執行計畫與監控指標為依據。