索引為什麼快 —— 也為什麼會失效

進入第三幕:引擎與效能。第一個要懂的就是索引——為什麼加了它查詢快幾百倍,又為什麼有時加了卻好像沒用。這兩個問題的答案,都藏在它的資料結構裡:B-tree。

沒索引 vs 有索引:全掃 vs 直達

沒有索引時,WHERE id = 500 這種查詢只能做 Seq Scan(全表掃描)——從第一列開始一列一列比對,直到找到。有索引時,資料庫多維護了一棵排好序的 B-tree,查找變成從樹根往下走幾步就到:

索引就像一本排好序的目錄:不用一頁頁翻,直接用二分逼近。而且因為葉子是有序的,範圍查詢(>、BETWEEN)、ORDER BY、前綴 LIKE 'abc%' 也都吃得到索引——找到起點後順著葉子走就好

一句話記住:沒索引是「逐列找」,有索引是「用排序好的結構逼近」。 這也是為什麼索引不只加速 =,連範圍、排序都受惠——因為 B-tree 的葉子本身就是排好序的。

索引不是免費的

既然這麼快,為什麼不每一欄都加?因為索引是「空間換時間」,而且會拖慢寫入:每一筆 INSERT / UPDATE / DELETE,都得同步維護每一個相關索引(把新值插進那棵排序樹的正確位置)。索引越多,寫入越慢、佔的空間也越大。所以索引要加在刀口上——常被拿來 WHERE 過濾、JOIN、ORDER BY 的欄位,而不是無腦每欄都建。讀多寫少的表值得多加,寫很兇的表要克制。

複合索引:最左前綴

一個索引可以蓋多個欄位(複合索引),但它有個一定要懂的規矩:最左前綴——只能從最左欄開始、連續使用:

複合索引像照「姓→名」排的電話簿:知道姓能翻、知道姓+名更精準;但只給「名」,整本都得翻——索引用不到。所以建複合索引時,欄位順序決定它能服務哪些查詢

索引什麼時候會悄悄失效

索引最讓人踩坑的,是它常常「加了、但沒被用到」,而且不會報錯。幾個最常見的失效情境:

把欄位包進函式或運算:WHERE DATE(created_at) = '2026-07-11' 或 WHERE amount * 2 > 100 —— 索引存的是欄位的原值,不是 DATE(...) 或 amount*2 的值,所以用不到。改寫成讓欄位單獨站在一邊(WHERE created_at >= '2026-07-11' AND created_at < '2026-07-12')。
前導萬用字元:LIKE '%abc' 用不到索引(不知道從哪個字母開頭找),但 LIKE 'abc%' 可以。
型別不符的隱式轉換:欄位是字串卻 WHERE phone = 0912345678(數字),資料庫可能被迫轉型而放棄索引。
選擇性太低:像「性別」只有兩個值,索引篩不掉多少列,優化器可能寧願直接全表掃——索引只對「能大幅縮小範圍」的欄位才划算。

還有個進階但實用的觀念:covering index(涵蓋索引)——如果查詢要的欄位剛好都在索引裡,資料庫連原始表都不用回去讀(index-only scan),更快。

那要怎麼確認索引到底有沒有被用到?看 EXPLAIN——這正是跟 Spark 那篇執行計畫對應的、下一篇的主題:計畫裡是 Index Scan 還是 Seq Scan,一翻兩瞪眼。

反思

索引是「空間換時間」,沒有白吃的午餐

我看過不少人一遇到查詢慢就反射性加索引,加到後來寫入慢得要命還不知道為什麼。索引的加速是有代價的——佔空間、拖慢每一次寫入。所以我現在看到「查詢慢 → 加索引」時,一定會先多問一句:這張表是讀多還是寫多?這個查詢是不是真的夠頻繁、值得為它養一棵樹?加索引不是免費的優化,是一筆要算的投資——這跟 Type 2 留不留歷史、跟任何工程取捨一樣,關鍵是看清你在拿什麼換什麼。

「別把欄位包在函式裡」是一條跨工具的通則

WHERE func(col) 讓索引失效,原因很單純:索引存的是 col 的原值,不是 func(col) 的值。有趣的是,這跟 Spark 的 filter 下推被函式擋掉,是同一件事——把欄位包進運算,最佳化器就沒辦法用「欄位的原始樣子」去加速。所以我養成一個習慣:寫過濾條件時,盡量讓欄位單獨站在一邊(col >= x 而不是 func(col) = y),把運算挪到常數那側。這個小習慣,讓索引、下推這些底層最佳化有機會生效——你怎麼寫條件,直接決定引擎幫不幫得了你。

最左前綴逼你先想「查詢的形狀」

複合索引的最左前綴規矩,表面是個限制,實際上是逼你想清楚一件更重要的事:我到底都怎麼查這張表? 索引不是為「表」建的,是為「查詢模式」建的——欄位放哪個順序,取決於你最常一起用哪些欄過濾、哪個欄選擇性最高。這讓我建索引前一定先盤點實際的查詢,而不是憑感覺把幾個欄湊成一個索引。先懂你怎麼查,才知道怎麼建——這句話用在索引上最貼切,其實用在所有為了效能做的設計上都成立。