如果你曾經等得太久才等到資料庫查詢結束，你就知道性能緩慢的痛苦。
好消息是？索引可以來拯救你！
PostgreSQL 功能強大，但如果沒有 適當的索引，你的資料庫就像是在馬鈴薯上運行。
索引是 超快速查詢 的秘訣，但它們也有取捨。
什麼是索引？
PostgreSQL 中的索引 像書中的索引一樣——而不是掃描每一頁（每一行），你可以直接跳到相關的部分。
沒有索引的話，PostgreSQL 會執行 順序掃描，這意味著需要查看 每一行 來找到匹配項，對性能來說並不理想。
索引特別適用於：
- 加速帶有
WHERE子句的查詢。 - 提升聯接性能。
有了索引，查詢的執行速度將是 對數級別快，而不是線性。
想像一下從 O(n) → O(log n) 的複雜度。
但是... 索引並不是萬能的。它們有取捨。
它們會消耗儲存空間，並可能減慢寫入速度。
因此，你需要聰明地決定何時何地使用它們。
索引是如何工作的：B-樹 的魔法
PostgreSQL 的預設索引類型是 B-樹 （平衡樹）。
把它想像成一個樹狀結構，保持數據排序以便快速搜尋。它的工作原理如下：
- 根節點：所有搜尋的起點。
- 分支節點：引導搜尋到正確的葉節點。
- 葉節點：儲存實際數據的指針。
例如，若你要在一個表中搜尋名稱「Mac」，B-樹將會：
- 從根節點開始。
- 將「Mac」與當前節點進行比較。
- 根據比較結果向左或向右遍歷。
- 重複這個過程直到找到精確的匹配。
這個過程減少了所需的比較次數，使搜尋變得 對數級別快。
何時使用索引
當索引能夠 顯著 提高讀取性能時使用索引。常見情況包括：
| 情況 | 索引如何有助於？ |
|---|---|
按唯一字段搜尋（例如 id、email） |
更快的查詢，避免全表掃描 |
使用 WHERE 子句進行過濾 |
快速找到匹配行 |
排序（ORDER BY） |
改善排序性能 |
| 聯接大型表 | 避免掃描整個數據集 |
| 全文搜尋 | 高效查找文本中的關鍵字 |
| 外鍵 | 確保快速的關係檢查 |
何時 不 使用索引
索引並不是免費的。
每次你 插入、更新 或 刪除 數據時，索引都需要更新。
這可能會減慢寫入密集的工作負載。如果：
- 你的表格 小（PostgreSQL 可以快速掃描它）。
- 你的查詢 很少根據 被索引的列過濾。
- 你的表格有 頻繁的寫入，而且讀取速度並不關鍵。
- 你的資料庫是 高度事務化的，並且需要快速的插入/更新。
PostgreSQL 的 MVCC 機制可能會導致「僅堆積行」的更新，造成死行並增加 I/O。
測量索引性能
在盲目添加索引之前，測試它們是否實際有幫助。
PostgreSQL 提供了 EXPLAIN ANALYZE 來分析查詢執行時間。試試這個：
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';查找 Seq Scan （不好）與 Index Scan （好）。
如果添加索引不減少查詢時間，那就不值得。
想刪除無用的索引？使用：
DROP INDEX index_name;
PostgreSQL 中的索引類型
並非所有索引都是一樣的。PostgreSQL 提供了幾種類型：
B-樹 索引（預設）
幾乎所有資料庫都會有一些 B-樹索引。
B-樹試圖保持平衡，樹的每個分支中的數據量大致相同。
因此，必須遍歷的層數始終在相同的範圍內。
B-樹索引可以有效用於等值和範圍查詢。
它們可以操作所有數據類型，並且也可以用於檢索 NULL 值。
B-樹非常適合緩存，即使僅部分緩存時也能很好的運作。
- 適合： 等值和範圍查詢（
=、<、<=、>、>=）。 - 範例：
CREATE INDEX idx_users_email ON users(email);
哈希索引（在 PostgreSQL 10+ 中改進）
在 PostgreSQL 10 之前僅對等值比較有用，但因為它們不具有事務安全性，崩潰後需要手動重建，而且不會複製到從屬節點，因此幾乎不建議使用。
在 PostgreSQL 10 及以上版本中，哈希索引現在支援寫前日誌並可複製到從屬節點。
- 優化用於 等值比較（
=）。 - 不適合 範圍查詢（
>、<）。 - 範例：
CREATE INDEX idx_users_hash_email ON users USING hash(email);
GIN（廣義倒排索引）
當一個索引必須將多個值映射到一行時，GIN 非常有用，而 B-樹索引則是優化為當一行只有一個鍵值時使用。
GIN 很適合用於索引數組值以及實現全文搜尋。
- 用於 全文搜尋 和 JSONB 欄位。
- 範例：
CREATE INDEX idx_users_bio ON users USING gin(to_tsvector('english', bio));
GiST（廣義搜尋樹）
GiST 索引允許你建立一般的平衡樹結構，並可以用於超越等值和範圍比較的操作。
它們用於索引幾何數據類型以及全文搜尋。
- 優化用於 幾何和範圍查詢。
- 用於 PostGIS （空間數據索引）。
- 範例：
CREATE INDEX idx_locations ON places USING gist(location);
BRIN（區塊範圍索引）
對於 大型、序列化存儲的數據（如時間序列數據）來說非常有效。
- 使用的 儲存空間 少於 B-樹。
- 範例：
CREATE INDEX idx_logs_timestamp ON logs USING brin(timestamp);
進階索引策略
組合索引
當多個欄位常常一起查詢時，索引多個欄位。
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);只有 B-樹、GiST、GIN 和 BRIN 索引類型支持多鍵欄位索引。
是否有多個鍵欄位與是否能將 INCLUDE 欄位添加到索引是獨立的。
索引最多可以有 32 列，包括 INCLUDE 欄位。
部分索引
僅索引一部分數據以節省空間。
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;部分索引是一種基於表格子集建立的索引；子集由條件表達式定義。
索引僅包含滿足條件的表行的條目。
覆蓋索引
儲存附加欄位以避免訪問主表。
CREATE INDEX idx_orders_covering ON orders(user_id, order_date) INCLUDE (total_price);在 PostgreSQL 中，所有索引都是二級索引，這意味著每個索引與表的主數據區域分開儲存。
唯一索引
有效地確保欄位唯一性。
CREATE UNIQUE INDEX idx_unique_email ON users(email);索引也可以用來強制每個欄位值的唯一性，或多個欄位組合值的唯一性。
只有 B-樹索引可以聲明為唯一的。
取捨：讀取性能與寫入性能
| 動作 | 索引的影響 |
|---|---|
| 讀取 | ✅ 查詢更快 |
| 寫入（INSERT/UPDATE/DELETE） | ❌ 由於索引維護而變慢 |
| 儲存 | ❌ 需要更多磁碟空間 |
| 清理 | ❌ 死元組需要清理 |
如果你的應用是 讀取密集型，索引是理所當然的選擇。如果它是 寫入密集型，則需要謹慎選擇。
總結
索引是 PostgreSQL 中 性能增強的最大利器之一。明智地使用它們：
- ✅ 對於過濾、排序和聯接使用索引。
- ❌ 避免在經常更新的表上使用索引。
- 🛠 在添加索引之前使用 EXPLAIN ANALYZE 測試。
- 🎯 根據查詢模式選擇正確的索引類型。
深入閱讀 📚
我正在開發一個非常方便的工具 LiveAPI。
LiveAPI 幫助你在幾分鐘內記錄所有後端 API。
使用 LiveAPI，你可以快速生成互動式 API 文件，讓用戶可以直接從瀏覽器執行 API。
如果你厭倦了手動創建 API 文件，這個工具可能會讓你的生活更輕鬆。
原文出處：https://dev.to/lovestaco/speed-up-db-queries-like-a-pro-2d95
1) --- 會變成分隔線（上一行必須是空白）
2) # 會變成一級標題
3) ## 會變成二級標題
4) ### 會變成三級標題
5) **粗體文字**會顯示粗體文字
6) ```當第一行與最後一行會顯示程式碼
7) 請搜尋 Markdown 語法，了解各種格式