如何選擇合適的索引類型

我們之前已經 dive 過索引的理論，認識過各種索引類型，也學會怎麼建立和刪除索引，還有怎麼對像陣列和 JSONB 這種複雜資料型態做索引。現在該來聊聊怎麼挑到最適合你需求的索引了——畢竟選錯索引，後果可能很慘。

想像一下，你的資料庫就像一間圖書館，查詢就是來找書的訪客。如果書都亂丟在地上，找書就會變成無止盡的迷宮。索引就是那些有條理的書架和目錄，讓你可以很快找到想要的東西，不用每本都翻一遍。

但如果你放錯書架或目錄，比如你用 HASH 索引在需要範圍查詢的地方，就像圖書館員想用出版年份的目錄找書名——找半天找不到，大家都會抱怨。在資料庫裡，這就會變成查詢很慢、系統壓力大。

今天我們就來聊聊怎麼正確選索引，讓你的查詢飛快，資料庫也不會累到爆炸。選錯的話，結果會很慘：查詢慢、資源被吃光，圖書館員（PostgreSQL）都要憂鬱症了。

選擇索引的重點：checklist

選索引時，先問自己幾個問題：

1. 你這個欄位的資料型態是什麼？
   • 比如，數字 INTEGER、FLOAT 通常用 B-TREE，陣列就用 GIN，文字欄位就要看實際需求了。

2. 你最常執行什麼查詢？

   • WHERE field = value？直接查找？那大多數情況下 B-TREE 或 HASH 就夠了。
   • 查陣列或 JSONB？那就看 GIN。
   • 地理資料、範圍查詢？考慮 GiST。

3. 你的資料會發生什麼事？

   • 如果你的表常常 insert 或 update，避免太多索引，因為這樣會拖慢寫入。

4. 需不需要保證唯一性？

   • 這種情況就要用帶 UNIQUE 屬性的索引。

案例：實際選索引的例子

來看幾個真實場景。

1. 簡單等值查詢

你有個學生資料庫，要快速用 email 找學生：

SELECT * FROM students WHERE email = 'student@example.com';

重點是這裡是等值查詢。最適合的就是 B-TREE 索引，因為它查精確值超快。

CREATE INDEX idx_students_email ON students (email);

如果 email 需要唯一：

CREATE UNIQUE INDEX idx_students_email_unique ON students (email);

2. 範圍查詢

假設你要找年齡大於 18 歲的學生：

SELECT * FROM students WHERE age > 18;

範圍查詢 B-TREE 也很適合，因為它本來就是為排序查詢設計的。

CREATE INDEX idx_students_age ON students (age);

3. 陣列過濾

你有個 courses 表，其中一個欄位存著報名學生的 ID 陣列。你想找出所有有學生 ID 123 的課程。

SELECT * FROM courses WHERE student_ids @> ARRAY[123];

這種查詢 GIN 索引最適合，因為它就是為陣列優化的。

CREATE INDEX idx_courses_students_ids ON courses USING gin (student_ids);

4. 從 JSONB 取資料

假設你有個存 JSONB 資料的表，裡面有訂單資訊。你要找所有 city 是 "Moscow" 的訂單：

SELECT * FROM orders WHERE data->>'city' = 'Moscow';

這裡用 GIN 索引很有效，因為它能針對 JSONB 的 key/value 查找。

CREATE INDEX idx_orders_data ON orders USING gin (data);

5. 地理資料

如果你在處理地理資訊，比如找落在某個半徑內的點，用 GiST 索引。這種索引對幾何和範圍查詢超強。

CREATE INDEX idx_locations_geom ON locations USING gist (geom);

不同索引效能比較

來看個真實例子，用 email 查學生。表裡有 100 萬筆資料。我們用不同索引和沒索引來跑查詢：

結論：這種情況下用 B-TREE 索引，查詢速度提升超過 500 倍。

選索引時常見錯誤

最常見的錯誤——「以防萬一」就幫每個欄位都建索引。結果發現 insert 變超慢。記住，索引不是萬能的魔法棒。用對地方很強，用錯反而害你。

另一個常見錯誤——選錯索引類型。比如你用 HASH 索引做範圍查詢，查詢突然變超慢。因為 HASH 只適合精確查找。

選索引建議

• 如果你常做 等值查詢或排序，用 B-TREE。
• 要 精確比對，又想省記憶體，可以考慮 HASH。
• 處理陣列或 JSONB，首選 GIN。
• 要查 範圍或地理資料，用 GiST。

最後一個重點：一定要分析你的查詢！用 EXPLAIN 和 EXPLAIN ANALYZE 看 PostgreSQL 怎麼用索引，還有哪裡可以優化。

EXPLAIN ANALYZE
SELECT * FROM students WHERE email = 'student@example.com';

今天就到這啦！現在你已經有選索引的知識，像 Jedi 挑光劍一樣挑索引。記得別亂建索引，沒必要就不要建，隨時檢查它們對效能的影響。