第四章：查詢執行與最佳化器 — 資料庫的大腦

1. 定義

查詢最佳化器 (Query Optimizer) 是資料庫核心中最複雜的部分。它的任務是將用戶的聲明式 SQL（告訴資料庫要什麼）轉換為物理執行計畫（告訴資料庫怎麼做）。

現代資料庫大多使用 CBO (Cost-Based Optimization)，即計算所有可能的執行路徑（索引掃描、全表掃描、巢狀迴圈連接等）的「成本」，並選擇成本最低的一條。成本通常以 I/O 次數和 CPU 指令數為單位。

2. 技術深度：選擇性與基數

最佳化器如何知道哪個索引更快？

• 選擇性 (Selectivity)：欄位中唯一值佔總行數的比例。選擇性越高（唯一值越多），索引效率通常越高。
• 基數 (Cardinality)：欄位中不重複值的估算數量。

如果 status 欄位只有 ‘active’ 和 ‘inactive’ 兩個值（選擇性低 / 基數=2），在千萬級表中，使用索引查找 ‘active’ 行可能需要回表 500 萬次。此時，CBO 會判斷全表掃描 (Sequential Scan) 比索引掃描更有效率。

3. 可視化：SQL 執行管道

flowchart TD
    SQL[SQL 語句] --> Parser[解析器 (Parser)]
    Parser --> AST[抽象語法樹]
    AST --> Rewriter[重寫器 (視圖展開/常量折疊)]
    Rewriter --> Optimizer[最佳化器 (CBO)]
    
    Optimizer --"統計資訊 (Statistics)"--> PlanA[計畫 A: 索引掃描]
    Optimizer --"成本計算"--> PlanB[計畫 B: 全表掃描]
    
    Optimizer --> Executor[執行器]
    Executor --> Engine[存儲引擎 (InnoDB)]
    Engine --> Data[數據回傳]

4. 真實案例：GitLab 生產環境慢查詢事故

背景：GitLab.com 曾遭遇嚴重的資料庫效能降級，某些簡單的查詢耗時超過 30 秒。 原因：PostgreSQL 的統計資訊 (Statistics) 過時。

深度解析：

1. 某張大表透過 DELETE 刪除了大量數據，但 Auto-analyze（自動統計行程）尚未觸發。
2. Postgres 的統計直方圖仍然認為該表有數百萬行數據，且目標欄位的分佈非常均勻。
3. 最佳化器基於錯誤的統計資訊，錯誤地選擇了 Nested Loop Join 而不是 Hash Join。
4. 這導致了數億次的迴圈查詢，瞬間耗盡了 CPU 資源。

教訓：SQL 效能不只取決於 SQL 寫法，更取決於資料庫對數據的「認知」。定期 ANALYZE 是運維的生命線。

5. 深度最佳化與縱深防禦

A. 理解執行計畫 (EXPLAIN)

不要猜測，使用 EXPLAIN (ANALYZE) 查看真實的執行路徑。

• 存取類型 (Access Type)：關注 type 欄位。ALL (全表掃描) 最差，const / eq_ref 最好。
• 掃描行數 (Rows Examined)：掃描行數 vs 回傳行數。如果掃描 100 萬行只回傳 1 行，說明索引無效。

B. 強制索引 (Index Hint)

作為最後的手段（慎用）。

• 在 MySQL 中使用 FORCE INDEX (idx_name) 告訴最佳化器：「我相信這個索引更好」。
• 風險：數據分佈變化後，強制索引可能變成最差選擇。

C. 複合索引的最左前綴原則

最佳化器只能利用複合索引的最左邊部分。

• 索引 (a, b, c) 可以支援 a=? 和 a=? AND b=?。
• 但不支援 b=? 或 c=?。這就像電話簿：你不能在不知道姓氏的情況下直接查名字。

6. 參考資料

• PostgreSQL Documentation: Planner/Optimizer
• MySQL Explain Output Format
• GitLab Post-Mortem: Database Incident