MySQL SQL 優化核心原則：索引、類型、改寫與實測驗證

核心優化原則速覽

1. 善用索引加速遍歷：叢集索引適合範圍/排序/分組；複合索引覆蓋高頻查詢；索引非越多越好，維護成本隨寫入線性增長。
2. 海量查詢避免格式轉換：應用層處理格式，資料庫專注核心運算。
3. ORDER BY/GROUP BY 受索引加速：索引順序滿足排序分組可避免 Filesort。
4. 索引欄不可操作：函數、運算、類型轉換作用於索引欄 → 全表掃描；移至條件右側或建立函數索引 (8.0)。
5. IN/OR 謹慎使用：易產生臨時表導致索引失效；值集小時改寫為 UNION ALL。
6. 選擇最小足夠資料類型：MEDIUMINT 代替 INT、CHAR 定長優於 VARCHAR、ENUM 代替少量固定值字串。
7. 欄位預設 NOT NULL：NULL 需額外位圖判斷，複雜化優化器統計。
8. 慎用 VARCHAR/TEXT/BLOB：溢出頁存儲、回表開銷大；大文本拆分副表。
9. 少量固定值用 ENUM：內部儲存為整數，比較極快、空間極省。

實測驗證：索引對 LIKE 查詢的影響

測試環境

• 表 users，37,365 行，username VARCHAR(50)
• 工具：MySQL-Front 執行 8 次取平均

無索引狀態

建立前綴索引 CREATE INDEX username_idx ON users(username(6))

實測結論

• 前綴索引對 LIKE 'prefix%' **效果最顯著**（8 倍提升），因 B+Tree 可直接範圍掃描。
• 前後萬用字元 LIKE '%text%' **索引幫助有限**（仍需掃描大部分索引頁），全文索引或搜尋引擎更適合。
• 索引長度 6 字元平衡了選擇性與空間，過長增加 I/O、過短選擇性不足。

多表查詢優化策略

1. 索引建在 JOIN ON + WHERE 條件欄位：驅動表 WHERE 過濾欄位、被驅動表 JOIN ON 欄位。
2. 小表驅動大表：EXPLAIN 觀察 rows 最小的表作驅動表（通常是過濾後結果集最小者）。
3. 覆蓋索引避免回表：SELECT 欄位全包含於複合索引中，Using index 出現於 Extra。
4. 避免子查詢，改寫 JOIN：MySQL 5.6+ 已優化部分子查詢，但 JOIN 穩定性更佳。

索引建立進階原則

前綴索引長度選擇

-- 計算前綴選擇性
SELECT 
  COUNT(DISTINCT LEFT(username, 4)) / COUNT(*) AS sel_4,
  COUNT(DISTINCT LEFT(username, 6)) / COUNT(*) AS sel_6,
  COUNT(DISTINCT LEFT(username, 8)) / COUNT(*) AS sel_8
FROM users;
-- 選擇選擇性接近 1.0 且長度最短的值

MySQL 支援最長 255 字元（實際受限於 max_key_length 3072 bytes，utf8mb4 每字元 4 bytes → 768 字元）。

複合索引列序決策

1. 最左列：最高頻 WHERE 條件欄位（=、IN、範圍）。
2. 次左列：第二高頻 WHERE 或 JOIN ON 欄位。
3. 後續列：ORDER BY/GROUP BY 欄位、覆蓋索引補充 SELECT 列表欄位。
4. 範圍條件列（>、<、>=、<=、LIKE 'prefix%'）之後的列**無法使用**索引查找。

IN/OR 索引失效實例與改寫

失效案例

-- 單列索引 idx_a(a), idx_b(b)
SELECT * FROM t WHERE a=1 OR b=2;  -- 無法合併使用兩個索引，全表掃描

-- 改寫
SELECT * FROM t WHERE a=1
UNION ALL
SELECT * FROM t WHERE b=2 AND a!=1;  -- 避免重複，各分支用各自索引

IN 列表過大優化

-- IN 值超過幾百個，優化器可能放棄索引改全表掃描
SELECT * FROM t WHERE id IN (1,2,3,...,10000);

-- 改寫：臨時表/値表 JOIN
CREATE TEMPORARY TABLE tmp_ids (id INT PRIMARY KEY);
INSERT INTO tmp_ids VALUES (1),(2),...;
SELECT t.* FROM t JOIN tmp_ids USING(id);

EXPLAIN 實戰調優工作流

1. EXPLAIN SELECT ... 觀察 type（目標：ref/range/eq_ref，避免 ALL/index）、key（實際用索引）、rows（預估掃描行）、Extra（Using index/Using where/Using filesort/Using temporary）。
2. EXPLAIN FORMAT=JSON (5.6+) 獲取成本估算 query_cost、read_cost、eval_cost。
3. OPTIMIZER_TRACE 追蹤優化器決策路徑（為何捨棄某索引）。
4. 對比優化前後 FLUSH STATUS → 執行 → SHOW SESSION STATUS LIKE 'Handler%' 量化 Handler_read_key / Handler_read_rnd_next 變化。

總結

MySQL 優化核心是**索引設計匹配查詢模式**。實測證明前綴索引對前綴匹配查詢提升 8 倍，但對任意位置模糊查詢效果有限。掌握 EXPLAIN 深度解讀、複合索引列序決策、IN/OR 改寫技巧、資料類型最小化原則，配合 Performance Schema 量化驗證，才能從經驗主義進入數據驅動優化。記住：**過早優化是萬惡之源，無監控優化是盲人騎瞎馬。**