索引是資料庫查詢加速的核心杠桿，但**建立 ≠ 生效**。本文從 B+ 樹儲存機制出發，系統講解叢集/非叢集/複合/唯一索引特性，列舉 4 大類導致索引失效的 SQL 寫法，並以 EXPLAIN 實戰演示如何驗證與修正索引使用情況。

一、B+ 樹與索引儲存機制速覽

• 根節點 → 分支節點 → 葉子節點：所有查詢路徑長度相同 (平衡樹)，複雜度 **O(log N)**
• 葉子節點雙向鏈表：支援範圍掃描 (BETWEEN, >, <) 與有序輸出 (ORDER BY)
• 非叢集索引葉子 = 索引鍵 + Rowid/主鍵值 → 需 **回表 (Key Lookup)** 取得非索引欄位
• 叢集索引葉子 = 完整資料行 → 無回表，但插入需維持物理順序，易產生頁面分裂

二、四大核心索引類型與建立語法

類型	語法 (MySQL/PostgreSQL/Oracle 通用)	核心特性
單列索引	CREATE INDEX idx_col ON tbl(col);	單一欄位查詢加速
複合索引	CREATE INDEX idx_a_b ON tbl(a, b);	**最左前綴原則**：查詢必須包含前導欄位 a 才能用到
唯一索引	CREATE UNIQUE INDEX idx_uk ON tbl(uk_col);	等同 UNIQUE CONSTRAINT，禁止重複值，含 NULL 視為重複 (Oracle 除外)
叢集索引	主鍵自動建立 / CREATE CLUSTERED INDEX ... (SQL Server)	資料物理順序 = 索引順序，每表僅一個

三、四大類索引失效陷阱 (含驗證與修正)

1. LIKE 模糊查詢非前綴匹配

-- ❌ 失效：前導萬用字元
SELECT * FROM emp WHERE ename LIKE '%SMITH';
SELECT * FROM emp WHERE ename LIKE '%MI%';

-- ✅ 生效：前綴匹配
SELECT * FROM emp WHERE ename LIKE 'SMI%';

-- 💡 對策：全文檢索 (Oracle Text / PostgreSQL GIN / MySQL FULLTEXT) 或反轉索引 (Oracle REVERSE KEY)

2. 否定條件與 NULL 判斷

-- ❌ 失效：否定、不等於、IS [NOT] NULL
SELECT * FROM emp WHERE deptno <> 10;
SELECT * FROM emp WHERE comm IS NOT NULL;

-- ✅ 改寫：
SELECT * FROM emp WHERE deptno IN (20,30);  -- 已知值集合
SELECT * FROM emp WHERE comm > 0;           -- 業務語義允許時

-- 💡 部分索引 (PostgreSQL/Oracle 12c+)：
CREATE INDEX idx_comm_notnull ON emp(comm) WHERE comm IS NOT NULL;

3. 索引列參與運算/函數/隱含轉換

-- ❌ 失效：函數、運算、隱含轉換
SELECT * FROM emp WHERE YEAR(hiredate) = 1981;
SELECT * FROM emp WHERE sal + comm > 5000;
SELECT * FROM emp WHERE empno = '7369';  -- 字串比較數字 → 隱含 TO_NUMBER(empno)

-- ✅ 改寫：將運算移至常數側
SELECT * FROM emp WHERE hiredate >= '1981-01-01' AND hiredate < '1982-01-01';
SELECT * FROM emp WHERE sal > 5000 - comm;
SELECT * FROM emp WHERE empno = 7369;

-- 💡 Function-Based Index (Oracle/PostgreSQL/MySQL 8.0+)：
CREATE INDEX idx_hiredate_year ON emp(EXTRACT(YEAR FROM hiredate));
-- 或虛擬欄位 + 索引

4. 複合索引最左前綴斷裂 / OR 條件

CREATE INDEX idx_dept_job ON emp(deptno, job);

-- ✅ 用到索引
SELECT * FROM emp WHERE deptno = 10;
SELECT * FROM emp WHERE deptno = 10 AND job = 'CLERK';

-- ❌ 失效：缺少前導欄位
SELECT * FROM emp WHERE job = 'CLERK';

-- ❌ 失效：OR 條件跨欄位 (Index Merge 極罕見)
SELECT * FROM emp WHERE deptno = 10 OR job = 'CLERK';

-- 💡 對策：建立單獨索引 idx_job，或改寫 UNION ALL
SELECT * FROM emp WHERE deptno = 10
UNION ALL
SELECT * FROM emp WHERE job = 'CLERK' AND deptno <> 10;

四、EXPLAIN 實戰驗證流程

1. 執行計畫獲取：

   -- MySQL / PostgreSQL
   EXPLAIN SELECT * FROM emp WHERE deptno = 10;
   
   -- Oracle
   EXPLAIN PLAN FOR SELECT * FROM emp WHERE deptno = 10;
   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

2. 關鍵欄位解讀：

   欄位	關注重點
   type (MySQL) / Operation (Oracle)	ref/range/eq_ref = 索引生效；ALL/index = 全表/全索引掃描 ❌
   key / Object Name	實際使用的索引名稱
   rows / Cardinality	預估掃描行數，**越小越好**
   Extra / Filter Predicates	Using where = 索引後再過濾；Using index = 覆蓋索引 ✅；Using filesort = 需排序 ⚠️

3. 對比驗證**：建立索引前後對比 rows、cost、Execution Time，量化收益

五、開發階段索引檢查清單

1. ☐ 所有 JOIN 條件欄位 (FK) 是否有索引？
2. ☐ 高頻 WHERE/ORDER BY/GROUP BY 欄位是否有索引 (單列/複合)？
3. ☐ 複合索引欄位順序是否符合 **最左前綴** 且 **區分度高者前置**？
4. ☐ 是否存在 **隱含類型轉換** (應用層參數類型與欄位類型一致)？
5. ☐ 是否對 **函數/運算列** 建立 Function-Based Index？
6. ☐ 執行 EXPLAIN 確認核心 SQL 走預期索引？
7. ☐ 部署後啟用 MONITORING USAGE 追蹤實際使用情況？

索引使用不是「建好就對」，而是 **「SQL 寫法匹配索引結構 → 優化器識別 → 執行計畫驗證 → 持續監控」** 的閉環工程。養成 **寫 SQL 即跑 EXPLAIN** 的肌肉記憶，是高效開發者的分水嶺。