如同書末的「索引頁」讓你秒速定位頁碼,資料庫索引將特定列的值**排序存儲**,並保存指向原始行的指針。查詢時先查索引 → 獲取指針 → 回表取數據 (或直接覆蓋輸出)。
| 類型 | 定義 | 物理順序 | 數量限制 | 適用場景 |
|---|---|---|---|---|
| 叢集索引 (Clustered Index) |
資料行按索引鍵**物理順序**存放,葉層即資料頁 | 邏輯 = 物理順序 | 每表**僅 1 個** | 範圍查詢、排序、頻繁按此列存取、作為 PK 最佳 |
| 非叢集索引 (Nonclustered Index) |
獨立 B+ 樹,葉層存 **鍵值 + 行定位器** (RID 或 PK) | 邏輯 ≠ 物理順序 | SQL Server: 999 MySQL: 無硬限制 |
點查詢、覆蓋索引、輔助查詢路徑 |
| 堆表 (Heap) |
無叢集索引,資料無序堆放,IAM 管理頁面鏈 | 無序 (插入位置由 PFS 決定) | 無索引或僅非叢集 | 純追加寫入、暫存表、ETL 階段表 |
CREATE UNIQUE INDEX UX_Users_Email ON Users(email) WHERE email IS NOT NULL; (Filtered 解決多 NULL)CREATE INDEX IX_Orders_Cust_Status ON Orders (customer_id, status)
INCLUDE (order_date, total_amount, shipping_address);
-- 查詢:SELECT order_date, total_amount FROM Orders WHERE customer_id=? AND status=?
-- 執行計畫:Index Seek (Covered) → 無 Key Lookup,極速
-- 僅索引未刪除、啟用、特定狀態的行
CREATE INDEX IX_Users_Active_Email ON Users (email)
WHERE is_deleted = 0 AND status = 'ACTIVE';
-- 體積小、維護快、統計資料更精準
-- SQL Server: 計算欄 + 索引
ALTER TABLE Users ADD email_lower AS LOWER(email);
CREATE INDEX IX_Users_EmailLower ON Users (email_lower);
-- PostgreSQL / Oracle / MySQL 8.0+: 直接表達式索引
CREATE INDEX IX_Users_EmailLower ON Users (LOWER(email));
-- 1. 慢查詢日誌 / Query Store / pg_stat_statements
-- 2. 應用 ORM 生成的 SQL 模式
-- 3. 報告/BI 常用維度
Selectivity = COUNT(DISTINCT column) / COUNT(*)
-- 經驗值:
-- < 1% : 極佳,點查詢首選
-- 1-5% : 良好,範圍查詢友好
-- 5-20% : 邊界,視查詢佔比決定
-- > 20% : 通常不建索引 (性別、狀態、類型碼)
-- 重疊索引檢測:前綴列相同
-- IX_A (a,b) 和 IX_B (a) → IX_B 可刪除 (被 IX_A 覆蓋)
-- IX_C (a,b,c) 和 IX_D (a,b) → 視查詢模式保留其一
| 反模式 | 危害 | 修正 |
|---|---|---|
| 「保險起見」給每列建單列索引 | 寫入放大、優化器困惑、空間浪費 | 合併為複合索引,按查詢模式設計 |
| 叢集索寬/不穩/非遞增 (UUID, 複合鍵) | 頁面分裂狂魔、非叢集索引膨脹 (存 PK) | 叢集鍵 = Identity/Sequence/日期+流水號 |
| 複合索引列序錯誤 (範圍列在前) | 範圍列後的列無法利用索引順序 | 等值列前置,範圍列中間,排序列最後 |
| 忽略 INCLUDE 列,導致 Key Lookup | 書籤查詢隨機 I/O,百萬行查詢變秒級 | 分析 SELECT 列表,高頻投影列加 INCLUDE |
| 對大字段 (VARCHAR(MAX), BLOB) 建標準索引 | 無效/報錯,浪費維護窗口 | Full-Text / JSON / XML / 向量 專用索引 |
| 概念 | SQL Server | MySQL (InnoDB) | PostgreSQL | Oracle |
|---|---|---|---|---|
| 叢集索引 | Clustered Index | Primary Key (聚簇) | — (Heap) | Index Organized Table (IOT) |
| 非叢集索引 | Nonclustered Index | Secondary Index | Index (B-tree) | B-tree Index |
| 行定位器 | RID / Clustered Key | Primary Key Value | CTID (TID) | ROWID / PK |
| 覆蓋索引 | INCLUDE Columns | 隱含 PK (不支援自定) | INCLUDE (11+) | — |
| 填充因子 | FILLFACTOR | — (頁面留白由 PCTFREE 類似) | FILLFACTOR | PCTFREE |
| 碎片整理 | REORGANIZE / REBUILD | OPTIMIZE TABLE / ALTER TABLE ... ENGINE=InnoDB | REINDEX / pg_repack | ALTER INDEX REBUILD |
索引是資料庫效能的槓桿,用得好撬動地球,用不好壓垮自己。建立「索引變更審批流程:業務驗證 → 執行計畫對比 → 壓測驗證 → 上線觀察 → 歸檔文檔」,讓每一個索引都經得起時間考驗。