索引最佳化 (SQL Index Optimization)

索引 (Index) 可以大幅提升查詢效能，但建立過多或不當的索引反而會降低效能。這篇文章介紹索引使用的最佳實踐。

什麼時候該建立索引？

1. 經常出現在 WHERE 條件的欄位
2. 經常用於 JOIN 連接的欄位
3. 經常用於 ORDER BY 排序的欄位
4. 經常用於 GROUP BY 分組的欄位
5. 高選擇性的欄位（不同值很多，如 email、身分證字號）

什麼時候不該建立索引？

1. 資料量很小的表（全表掃描可能更快）
2. 經常大量更新的欄位（每次更新都要維護索引）
3. 低選擇性的欄位（如性別、布林值，只有少數幾種值）
4. 很少用於查詢條件的欄位

索引使用的最佳實踐

1. 使用複合索引時注意欄位順序

複合索引遵循「最左前綴原則」，只有從最左邊的欄位開始連續使用才會生效：

-- 建立複合索引
CREATE INDEX idx_name_city ON customers(name, city, age);

2. 避免在索引欄位上使用函數

-- ✗ 不好：索引失效
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- ✓ 好：索引可以使用
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

3. 避免在索引欄位上做運算

-- ✗ 不好：索引失效
SELECT * FROM products WHERE price * 1.1 > 1000;

-- ✓ 好：索引可以使用
SELECT * FROM products WHERE price > 1000 / 1.1;

4. 注意 LIKE 的使用方式

-- ✓ 索引可以使用（前綴匹配）
SELECT * FROM customers WHERE name LIKE '張%';

-- ✗ 索引失效（萬用字元在前）
SELECT * FROM customers WHERE name LIKE '%張';
SELECT * FROM customers WHERE name LIKE '%張%';

5. 使用覆蓋索引 (Covering Index)

如果索引包含了查詢所需的所有欄位，資料庫就不需要再去讀取資料表，這稱為覆蓋索引：

-- 建立索引
CREATE INDEX idx_covering ON orders(customer_id, order_date, total);

-- 這個查詢只需要讀取索引，不需要讀取資料表
SELECT customer_id, order_date, total FROM orders WHERE customer_id = 1;

6. 定期維護索引

索引隨著資料的新增、刪除會產生碎片化，定期重建可以維持效能：

-- SQL Server
ALTER INDEX idx_name ON table_name REBUILD;

-- MySQL
ALTER TABLE table_name ENGINE=InnoDB;  -- 重建整個表
OPTIMIZE TABLE table_name;

-- PostgreSQL
REINDEX INDEX idx_name;

查看索引使用情況

查看執行計劃

-- MySQL
EXPLAIN SELECT * FROM customers WHERE name = '張一';

-- SQL Server
SET SHOWPLAN_TEXT ON;
SELECT * FROM customers WHERE name = '張一';

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM customers WHERE name = '張一';

執行計劃會顯示查詢是否使用了索引，以及使用了哪個索引。

索引類型選擇

索引設計原則總結

1. 只為經常查詢的欄位建立索引
2. 優先考慮複合索引，減少索引數量
3. 將選擇性高的欄位放在複合索引前面
4. 避免過度索引，每個索引都會增加寫入成本
5. 定期檢視和調整索引策略