MySQL 查詢優化三大核心技法：索引、EXPLAIN 與參數調校

前言：一條慢 SQL 的連鎖效應

高併發系統中，單條低效查詢可佔用過量 CPU、I/O 與鎖資源，導致整個應用叢集回應變慢甚至雪崩。優化查詢既是科學也是藝術，MySQL 內建工具提供強大觀測與干預能力。本文聚焦三大核心技法。

一、索引：精準定位的雙面刃

建立索引

CREATE INDEX idx_username ON users(username);
-- 或建表時
CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(64),
  INDEX idx_username (username)
);

驗證索引

SHOW INDEX FROM users;

索引決策原則

• WHERE、JOIN、ORDER BY、GROUP BY 高頻欄位優先建索引。
• 選擇性高（基數/總行數 > 0.1）的欄位效果最佳；低選擇性（如性別、狀態碼）單列索引幫助有限，考慮複合索引。
• 最左前綴原則：複合索引 (a,b,c) 可支援 a、a,b、a,b,c，不可跳過 a 直接用 b。
• 覆蓋索引：查詢僅需索引欄位即可滿足（Using index），避免回表。
• 克制過度索引：每個索引增加寫入開銷（INSERT/UPDATE/DELETE 需維護 B+ 樹），單表建議 ≤ 5~6 個，視讀寫比例調整。

二、EXPLAIN：窺探執行計畫的 X 光機

基本用法

EXPLAIN SELECT * FROM country JOIN city ON city.countrycode = country.code WHERE country.name = 'Taiwan';

關鍵欄位解讀

實戰案例：索引降低 90% 掃描行數

原查詢 EXPLAIN 顯示 city 表 type=ALL, rows=4019。建立索引 CREATE INDEX idx_ccode ON city(countrycode); 後，重新 EXPLAIN 顯示 type=ref, rows=333，掃描行數暴跌 92%，查詢延遲從秒級降至毫秒級。

三、內部參數調校：記憶體與 I/O 的平衡術

持續優化閉環

1. 基線建立：記錄核心業務 SQL 的 EXPLAIN、執行時間、Rows examined。
2. 慢查詢收集：pt-query-digest 分析 Top N 慢查詢。
3. 索引與改寫：加索引、改寫 SQL（避免 SELECT *、拆分大事務、消除隱式轉換）。
4. 參數微調：單一參數變更、觀測指標、回滾機制。
5. 壓測驗證：模擬生產流量驗證改善效果與無退化。

結語

索引讓查找「有路可走」，EXPLAIN 讓執行「可視可控」，參數調校讓資源「用在刀口上」。三者結合，構成 MySQL 查詢優化的鐵三角。