MySQL 查詢優化三板斧：索引設計、EXPLAIN 執行計劃、內部參數調優

MySQL 查詢優化核心方法論：索引、EXPLAIN、參數調優三位一體

查詢優化非單點技巧，而是「建立索引 → 分析執行計劃 → 調整內部參數」的系統工程。本文以實戰案例演示從全表掃描到索引覆蓋的完整優化路徑，並總結關鍵內部參數調優方向。

一、索引：查詢加速的基石

建立索引**：

CREATE INDEX idx_username ON users(username);

驗證索引**：

SHOW INDEX FROM users;
-- 關鍵欄位：Key_name, Column_name, Cardinality (基數), Index_type (BTREE)

索引設計鐵律**：

• 高選擇性優先：基數接近行數的欄位 (如 UUID、手機號) 索引效果最好
• 最左前綴原則：聯合索引 (a,b,c) 支援 a/a,b/a,b,c 查詢
• 覆蓋索引：SELECT a,b FROM t WHERE c=1 → 索引 (c,a,b) 避免回表
• 避免過度索引**：每索引增加寫入開銷 (維護 B+ 樹)、佔用空間，讀寫比低的表嚴控索引數
• 外鍵隱式索引**：InnoDB 自動為外鍵建立索引，無需手動重複

二、EXPLAIN：剖析執行計劃的 X 光

實戰案例：四表關聯優化**

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

階段 1：無索引 → 全表掃描災難

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

city 表 type=ALL，掃描 4079 行，Using where 表示 Server 層過濾。

階段 2：建立關聯欄位索引

CREATE INDEX idx_countrycode ON city(countrycode);

階段 3：再次 EXPLAIN → 索引生效

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

city 表 type=ref，key=idx_countrycode，rows=333，**掃描行數降低 92%**。

三、內部參數調優：引擎調校的旋鈕

優化閉環流程

1. 發現**：慢查詢日誌、SHOW PROCESSLIST、應用層 APM 告警
2. 分析**：EXPLAIN、SHOW INDEX、OPTIMIZE TABLE 更新統計
3. 優化**：建索引、改 SQL、調參數、分表分庫
4. 驗證**：負載測試、pt-query-digest 對比、生產灰度
5. 固化**：參數寫入 my.cnf、索引變更納入變更管理、文檔更新

進階技巧

• EXPLAIN FORMAT=JSON (5.6+)：輸出詳細成本估算、查詢改寫資訊
• EXPLAIN ANALYZE (8.0.18+)：**實際執行**並輸出真實行數、耗時、迴圈次數，**優化驗證神器**
• OPTIMIZER_TRACE：追蹤優化器決策過程，排查為何不走預期索引
• 直方圖統計 ANALYZE TABLE ... UPDATE HISTOGRAM (8.0+)：非索引欄位選擇性估算

掌握「索引建模 → EXPLAIN 診斷 → 參數調校」三板斧，並建立「慢查詢發現 → 分析優化 → 驗證固化」閉環，是 MySQL DBA 與後端工程師的核心硬技能。