INDEX 索引

什麼是索引？

比喻：索引就像書本的目錄 📑

一本 500 頁的書，你要找「第三章」—— 沒有目錄 → 一頁一頁翻（Full Table Scan） 有目錄 → 直接翻到第 87 頁（Index Scan）

索引是資料庫用來加速查詢的資料結構。

建立索引

-- 在 Students 的 Email 欄位建立索引
CREATE INDEX idx_students_email    -- ← 索引名稱（慣例：idx_表_欄位）
ON Students (Email);               -- ← 在哪個表的哪個欄位

-- 在 Orders 表建立複合索引
CREATE INDEX idx_orders_customer_date
ON Orders (CustomerId, OrderDate); -- ← 多欄位組合索引

逐行解析：

CREATE INDEX idx_students_email    -- 建立索引，取名 idx_students_email
ON Students (Email)                -- 在 Students 表的 Email 欄位上
                                   -- 之後 WHERE Email = '...' 會變快

索引的效果

-- 沒有索引：掃描全表（100 萬筆逐筆比對）
SELECT * FROM Students WHERE Email = 'test@gmail.com';
-- 可能需要 500ms

-- 建立索引後：直接定位
CREATE INDEX idx_students_email ON Students (Email);
SELECT * FROM Students WHERE Email = 'test@gmail.com';
-- 只需要 1ms

索引類型

B-Tree 索引（預設）

-- 適合：等值查詢、範圍查詢、排序
CREATE INDEX idx_age ON Students (Age);

-- 這些查詢都能用到 B-Tree 索引：
WHERE Age = 20          -- 等值
WHERE Age > 18          -- 範圍
WHERE Age BETWEEN 20 AND 25  -- 範圍
ORDER BY Age            -- 排序

唯一索引

-- 確保值不重複（自動作為約束）
CREATE UNIQUE INDEX idx_email_unique
ON Students (Email);

-- UNIQUE 約束其實就是建立唯一索引
ALTER TABLE Students ADD CONSTRAINT uq_email UNIQUE (Email);

部分索引（Partial Index）

-- 只對「有效」的資料建索引
CREATE INDEX idx_active_students
ON Students (Name)
WHERE IsActive = true;     -- ← 只索引 IsActive=true 的列

-- 適合：大部分查詢都只找 IsActive=true 的資料
-- 索引更小、更快

複合索引

CREATE INDEX idx_dept_score
ON Students (Department, Score);

-- ✅ 這些查詢能用到：
WHERE Department = '資工系'                    -- 左前綴匹配
WHERE Department = '資工系' AND Score > 80     -- 兩欄都用到
ORDER BY Department, Score                     -- 排序

-- ❌ 這個用不到：
WHERE Score > 80                               -- 沒有用到最左邊的欄位

💡 最左前綴原則：複合索引 (A, B, C)，只有從 A 開始的查詢才能用到。

何時該建索引？

✅ 應該建索引

-- 1. WHERE 經常查詢的欄位
WHERE Email = '...'          -- 頻繁查詢 → 建索引

-- 2. JOIN 的連接欄位
ON Orders.CustomerId = Customers.Id  -- 外鍵欄位 → 建索引

-- 3. ORDER BY 的排序欄位
ORDER BY CreatedAt DESC      -- 頻繁排序 → 建索引

-- 4. UNIQUE 約束的欄位
-- 自動有索引

❌ 不應該建索引

-- 1. 很少查詢的欄位
-- 2. 經常大量 INSERT/UPDATE 的表（索引會拖慢寫入）
-- 3. 值很少變化的欄位（如 Gender 只有男/女，索引效果差）
-- 4. 資料量很小的表（直接全表掃描就很快了）

查看和管理索引

-- 查看表的所有索引（PostgreSQL）
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'students';

-- 刪除索引
DROP INDEX idx_students_email;

-- 查看查詢是否有用到索引
EXPLAIN ANALYZE
SELECT * FROM Students WHERE Email = 'test@gmail.com';
-- 結果會顯示 Index Scan 或 Seq Scan（全表掃描）

小結