讀懂 EXPLAIN:優化器到底怎麼跑你的 query

· tech

#sql#performance

上一篇留了一個問題:索引到底有沒有被用到?答案就在 EXPLAIN 裡。這篇是我之前寫的 Spark 執行計畫那篇的 SQL 版姊妹作——同一套「讀計畫、找瓶頸」的思維,換一個引擎。學會讀 EXPLAIN,你就從「猜為什麼慢」升級成「打開來看」。

EXPLAIN:把優化器的計畫攤開

EXPLAIN <query> 會印出優化器打算怎麼跑這個查詢——不用真的執行,是靜態估計。EXPLAIN ANALYZE 則會真的跑一次,附上每一步的實際時間與實際列數。讀法跟 Spark 那篇一樣:縮排最深的先執行(由內而外),一層層往上到最終結果。

計畫裡先認「掃描方式」——這正好接上一篇的索引:

  • Seq Scan:全表掃描(沒用索引)。
  • Index Scan:走索引找到位置、再回表拿資料。
  • Index Only Scan:要的欄位都在索引裡,連表都不用回(covering index)。
  • Bitmap Heap Scan:命中的列不多不少時,先用索引收集一批再一次讀。

三種 JOIN 演算法

計畫裡最值得看懂的,是 JOIN 用了哪種演算法。這也補完了第二篇講 join 記憶體時埋的伏筆——優化器會依表大小、有沒有排序、有沒有索引,在三種裡挑一種:

Nested Loop 外 外 外 內表(有索引更快) 外表每列 → 查內表一次 適合:小表 / 內表有索引 Hash Join 小表 Hash 表(記憶體) 大表 小表建 hash,大表 probe 適合:大表、等值 join Merge Join 1 3 5 2 4 6 兩側排序後,像拉鍊合併 適合:已排序 / 有索引
三種 JOIN 沒有絕對優劣,只有適不適合:Nested Loop 小表(或內表有索引)快、Hash Join 對付大表等值 join、Merge Join 在資料已排序時省事。這跟 Spark 的 broadcast vs sort-merge 是同一組概念

讀一個真實的計畫

把上面兜起來,看一個 orders JOIN customers 的計畫該怎麼讀:

Hash Join (cost=… rows=500) 用 Hash Join(小表建表、大表 probe) -> Seq Scan on orders orders 全表掃 —— 該不該建索引? -> Hash -> Index Scan on customers customers 走了索引 ✓ 讀法:縮排最深的先執行;cost 是優化器的「估計」,EXPLAIN ANALYZE 才給真實 actual time / rows
由內而外讀:先掃 customers(走索引)建 hash、掃 orders 去 probe,最後 Hash Join 合起來。看到大表 Seq Scan 就該問一句「這裡是不是缺索引」——這就是把效能問題變成螢幕上指得出來的一行

讀計畫時最該抓的三件事:① 該用索引的地方卻是 Seq Scan?(缺索引,或被函式/型別搞失效了)② JOIN 演算法選得對嗎?(小表 join 卻用了 Nested Loop 掃大表就慘了)③ 估計的 rows 跟 EXPLAIN ANALYZE 的實際差幾個數量級嗎?——差很多代表統計過時,優化器拿著錯的估計就會選錯計畫,跑一次 ANALYZE 更新統計往往就好了。

反思

讀計畫,是把「猜」變成「看」

這篇的收穫跟 Spark 那篇幾乎一字不差:在你會讀計畫之前,效能是玄學;會讀之後,它是螢幕上一行行看得懂的東西。 我以前 debug 慢查詢,是靠經驗猜、靠改改看;現在第一步一定是 EXPLAIN ANALYZE,讓計畫直接告訴我瓶頸在哪一步、掃了多少列、join 怎麼做。這套「先讀計畫再動手」的紀律,SQL 和 Spark 完全共用——跨引擎的底層思維是相通的,學會一個,另一個就通了一半。

cost 是估計,ANALYZE 才是事實

EXPLAIN 的 rows、cost 都是優化器根據統計猜的,不是真的。我吃過的虧是盯著漂亮的 cost 以為沒問題,結果實際跑起來慢到爆——因為統計過時,優化器把一個其實有一百萬列的步驟估成一千列,計畫整個選歪。所以我現在只信 EXPLAIN ANALYZE 的 actual。這件事有個更大的道理:優化器再聰明,也只跟它手上的統計一樣準。 估計與現實脫節,是很多「莫名其妙變慢」的根源——而修法常常樸素到好笑:更新統計而已。

JOIN 演算法沒有最好,只有最適合

Nested Loop、Hash、Merge 各有主場,優化器多數時候選得對。但「多數時候」不是「總是」——當它的估計歪了,就可能對著大表用 Nested Loop、慢上千倍。你要有能力看懂「它為什麼選這個、本來該是哪個」,才判斷得出優化器有沒有選錯。這需要你真的懂三種演算法的脾氣,而這份知識,跟第二篇的 join 記憶體、跟 Spark 的 broadcast vs sort-merge 是同一組——講到底,「資料怎麼被配對」這件事,在單機和分散式是同一套物理。把它學透一次,到哪個引擎都用得上。