🚀 資料庫效能優化：一秒學會 Index 索引

在專案剛上線時，你的資料庫可能只有 100 名使用者、1000 筆訂單。這時候，隨便寫一條 SQL SELECT * FROM orders WHERE user_id = 5，速度都是飛快的 (通常 < 1 毫秒)。

但當你的 SaaS 產品一炮而紅，資料庫湧入了一百萬筆訂單後，有一天你突然發現：使用者的儀表板載入要 5 秒鐘！ 你嚇得趕快去檢查伺服器 CPU，卻發現根本沒滿載。問題到底出在哪？

答案是：你的資料庫正在做「全表掃描 (Sequential Scan)」！ 本章將為你介紹資料庫最重要的武器：Index (索引)。只要加對索引，百萬筆資料的查詢也能在 1 毫秒內完成！

1. 什麼是全表掃描？為什麼它很慢？

想像一下，你手上有一本厚達 1000 頁的英文字典，但裡面的單字是「完全隨機亂排」的。這時候我請你找出 Apple 這個單字。你怎麼找？你只能從第 1 頁看起，一頁一頁往下翻，直到翻到可能在第 680 頁的 Apple 為止。這就叫做 Sequential Scan (全表掃描)。

在 PostgreSQL 中，如果你的資料表沒有建立索引，當你執行：

SELECT * FROM users WHERE email = 'test@example.com';

資料庫就必須把硬碟裡幾百萬筆使用者的資料，一筆一筆拿出來比對，直到找到那個人為止。這極度消耗硬碟 I/O，也是讓網站變慢的元凶。

2. Index (索引) 的運作原理

如果那本字典裡的單字是「按照 A~Z 字母順序排列」的，找 Apple 就像喝水一樣簡單，你翻開 A 區，一下子就找到了。

在資料庫中，Index 就是這本字典的目錄。當我們為 email 欄位建立索引時，PostgreSQL 會在背後偷偷建立一棵叫做 B-Tree (平衡樹) 的資料結構。這棵樹把所有的 email 排序好，並且記住了每一筆 email 實際在硬碟中的位置 (指標)。

下次你要找 email = 'test@example.com' 時，它只要沿著 B-Tree 的樹枝搜尋 (這叫 Binary Search)，只需要走幾步路 (時間複雜度 O(log N)) 就能找到指標，然後直接去硬碟把那筆資料抓出來。

速度從 O(N) 變成 O(log N)，這在百萬筆資料時，就是 幾千倍的效能差距！

3. 如何建立索引 (Create Index)

在 PostgreSQL 建立索引非常簡單。假設我們有一張 orders 表，我們常常需要根據 user_id 來撈出他的所有訂單。

-- 建立一張簡單的訂單表
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID,
  amount DECIMAL(10,2),
  created_at TIMESTAMP
);

-- ❌ 如果沒加索引，當資料變多時下面這句會超慢
-- SELECT * FROM orders WHERE user_id = 'xxx-xxx-xxx';

-- ✅ 為 user_id 建立索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

就這樣！一句 CREATE INDEX，你的網站就復活了！

常見的幾種索引情境：

1. 唯一索引 (Unique Index) 如果你要確保某個欄位絕對不能重複 (例如使用者的 email)，請加上 UNIQUE：

CREATE UNIQUE INDEX idx_users_email ON users(email);

這不僅能加速搜尋，還能防止在併發註冊時產生兩個相同的 email 帳號。

2. 複合索引 (Composite Index) 如果你常常「同時」用兩個條件來過濾，例如：搜尋某個使用者在「某段時間內」的訂單：

SELECT * FROM orders WHERE user_id = 'xxx' AND created_at > '2023-01-01';

這時候建立複合索引最有效：

CREATE INDEX idx_orders_user_time ON orders(user_id, created_at);

⚠️ 複合索引地雷：複合索引有「最左前綴原則」。上面的索引對於單獨查 user_id 有用，但對於單獨查 created_at 完全沒用！你必須把最常拿來過濾的欄位放在括號裡的第一個。

4. 如何檢查索引有沒有發揮作用？(EXPLAIN)

你加了索引，但你怎麼知道 PostgreSQL 真的有去用它？有時候 SQL 寫太爛，資料庫寧願全表掃描也不用你的索引！

這時候你要學會 DBA (資料庫管理員) 的終極招式：EXPLAIN。

只要在你的 SQL 查詢最前面加上 EXPLAIN ANALYZE，執行下去後，PostgreSQL 不會回傳資料，而是會回傳「它是怎麼跑這句 SQL 的報告」。

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = '123e4567-e89b-12d3-a456-426614174000';

解讀報告：

❌ 如果看到 Seq Scan (全表掃描)：

Seq Scan on orders  (cost=0.00..15243.00 rows=12 width=144) (actual time=1.234..45.678)
  Filter: (user_id = '123e4567...'::uuid)

代表你的索引沒建對，或是資料庫覺得全表掃描比較快。

✅ 如果看到 Index Scan 或 Bitmap Index Scan：

Index Scan using idx_orders_user_id on orders  (cost=0.29..8.30 rows=12 width=144) (actual time=0.012..0.025)
  Index Cond: (user_id = '123e4567...'::uuid)

恭喜你！索引完美命中！注意看 actual time 只要 0.025 毫秒！

5. 索引是萬靈丹嗎？(The Cost of Indexes)

既然索引這麼棒，那我們是不是要把所有欄位都加上索引？ 絕對不行！這會引發大災難！

索引的代價 (Trade-offs)：

吃硬碟空間：B-Tree 索引本身也是資料。如果表很大，索引可能會比表本身還要大！
拖慢寫入速度 (INSERT / UPDATE)：這最嚴重。當你每次新增一筆訂單時，資料庫不僅要把資料寫進硬碟，它還必須去更新「所有的 B-Tree 索引目錄」。如果你一張表有 10 個索引，代表一次寫入動作背後要執行 11 次操作。你的寫入效能會崩潰！

建立索引的最佳實踐 (Best Practices)：

唯獨不改的歷史資料：可以大膽加索引。
寫入頻繁的表 (例如 log, 追蹤數據)：盡量減少索引，不然寫入會卡死。
Primary Key (主鍵) 和 Foreign Key (外鍵)：這兩個幾乎一定要加索引！尤其是做 JOIN 時，外鍵沒加索引會直接拖垮整台資料庫。
定期刪除沒在用的索引：不要怕，這不會刪到資料。

掌握了 Index 與 EXPLAIN，你就擺脫了「只會寫 CRUD 的菜鳥」稱號，正式踏入了高級後端工程師的殿堂！