你寫的 SQL 只說「要什麼」，不說「怎麼拿」。Query optimizer 的工作是把這份宣告式的邏輯計畫，翻成一份它估計最便宜的實體執行計畫——而它估得準不準，全押在統計資訊上。
🧠 Intuition
TIP
Optimizer 不是在找「最佳計畫」，而是在「猜成本」。它沒真的跑你的查詢，只是拿 column 統計去估每個計畫要碰多少列、花多少 I/O，挑估計最便宜的那個。垃圾統計進、垃圾計畫出。
- 邏輯計畫 → 實體計畫：parser 先把 SQL 轉成關聯代數（join、filter、projection 的樹），這是邏輯計畫；optimizer 再為每個邏輯運算子挑具體演算法（hash join 還是 nested loop？index scan 還是 seq scan？），產出實體計畫。
- Cost-based optimizer (CBO)：用統計資訊（每欄的 cardinality、distinct 值數、histogram、null 比例）估算 cardinality（每步輸出列數），再換算成 cost（I/O + CPU 的加權），在計畫空間裡搜最低成本者。
- 搜尋空間爆炸：join 順序的可能性隨表數階乘成長，optimizer 不可能窮舉，靠 dynamic programming（System R 風格）加啟發式剪枝，必要時超時就收斂到「夠好」的計畫。
- 估計誤差會放大：cardinality 估計的誤差會沿著計畫樹逐層相乘，第一個 join 估錯，後面整棵樹的成本判斷全歪——這是壞計畫最常見的根因。
⚖️ Tradeoffs
Cardinality Estimation — 一切成本的源頭
- Optimizer 靠 histogram 與 distinct count 估「這個 filter / join 會留下幾列」。
- 獨立性假設（assumes predicates are independent）在現實常被打破：
WHERE city='台北' AND zip='100'兩欄高度相關，optimizer 卻把選擇度相乘，嚴重低估列數。 - 低估 → 以為輸出很小 → 選 nested loop / 不配記憶體；高估 → 過度保守。修正手段：multi-column statistics、extended statistics（PG）、filtered statistics。
Join 順序與演算法選擇 — 計畫品質的主戰場
- 順序：先 join 出小結果集，能讓後續步驟碰更少列。錯誤的順序會在中間產生巨大的暫存結果。
- 演算法：nested loop 適合小外表 + 內表有索引；hash join 適合大表等值 join 且無有用索引；merge join 適合兩邊已排序。
- 演算法選擇高度依賴 cardinality 估計——估錯列數，就會把該用 hash join 的場景挑成 nested loop（或反之），效能差數量級。
統計過期 (Stale Statistics) — 壞計畫的慢性病
- 統計是定期或按修改量門檻取樣更新的，不是即時。大量 INSERT/DELETE 後統計還停在舊分佈。
- 典型災難：新資料剛灌進來，optimizer 以為某區間「沒有列」（統計裡還是空的），對著實際上百萬列的範圍選了 nested loop。
- 解法：手動
UPDATE STATISTICS/ANALYZE、調低 auto-update 門檻、對傾斜欄位建 histogram。遞增載入的大表尤其要盯。
Plan Cache 與 Parameter Sniffing — 重用的雙面刃
- 為省下每次重編譯，DB 把編好的計畫快取起來，相同的參數化查詢重用。
- Parameter sniffing：第一次編譯時「嗅」到的參數值決定了計畫，之後所有執行都沿用。若首次參數是罕見值（高選擇度）卻沿用到常見值（低選擇度），計畫就完全不適配。
- 緩解：
OPTIMIZE FOR、RECOMPILEhint、強制參數化 vs 局部變數、SQL Server 的 Query Store 能抓到並強制（force）較佳計畫。權衡的是「重用省編譯」對上「一份計畫服務不了所有參數」。
🔑 Takeaways
- Optimizer 把宣告式 SQL（邏輯計畫）翻成實體計畫，靠統計估 cardinality → 估 cost → 挑最低成本，它從沒真的跑過你的查詢。
- 估計誤差沿計畫樹相乘，join 順序與演算法選擇是計畫品質的主戰場，估錯列數就選錯 join。
- 統計過期是壞計畫最常見的慢性病，遞增載入的大表要主動更新統計。
- Plan cache 省下重編成本，但 parameter sniffing 會讓一份計畫服務不了所有參數；對症下藥（RECOMPILE / OPTIMIZE FOR / Query Store），別全面重編。
2026-07-01
- Q: 既然 parameter sniffing 會出問題，為什麼不乾脆每次都重新編譯計畫？
- A: 因為編譯本身不便宜。對 OLTP 那種一秒跑幾千次的小查詢，每次重編的 CPU 成本會直接壓垮系統——plan cache 的存在就是為了攤掉這筆。Sniffing 的問題不是「重用」本身，而是「用一份計畫服務分佈差異極大的參數」。所以正解不是全面 RECOMPILE，而是針對性處理：只對那些已知參數分佈傾斜、且重編成本可接受的查詢加
RECOMPILE；或用OPTIMIZE FOR (@p UNKNOWN)讓它用平均選擇度編一份「中庸」計畫；真正棘手的就靠 Query Store 抓出退化、強制釘住好計畫。重點是分清「這查詢值不值得每次重編」。