數據庫索引設計全攻略：優缺點、適用場景、創建語法與失效規避

數據庫索引設計全攻略：優缺點、適用場景、創建語法與失效規避

索引是關係型數據庫性能的基石，但「索引越多越好」是危險誤區。本文系統梳理索引優缺點、何時創建/不創建、MySQL 創建語法、五大失效場景、監控指標與最佳實踐清單。

索引優點

1. 大幅加速數據檢索 (WHERE、JOIN、ORDER BY、GROUP BY)。
2. 唯一索引/主鍵保證行級唯一性。
3. 加速表連接 (JOIN ON 列)。
4. 覆蓋索引避免回表，極致性能。

索引缺點

1. 佔用物理空間 (約表大小 10%-30%，多索引累加)。
2. 寫入 (INSERT/UPDATE/DELETE) 需同步維護 B+ 樹，降低寫性能。
3. 優化器統計信息不準可能選錯索引，導致計劃劣化。

應該創建索引的 6 大場景

1. 經常出現在 WHERE 條件列。
2. 主鍵列 (自動建立聚簇索引)。
3. 經常用於 JOIN ON 的外鍵列。
4. 經常做範圍查詢 (>, <, BETWEEN) 的列，索引天然有序。
5. 經常用於 ORDER BY/GROUP BY 的列，避免 filesort。
6. 高選擇性列 (基數/行數 > 0.1)，低選擇性 (如性別、狀態) 單列索引收益極低。

不應該創建索引的 4 大特徵

1. 查詢中極少使用或從不使用的列。
2. 取值極少 (基數極低) 的列，如 is_deleted、sex、status (除非組合索引前綴)。
3. TEXT、BLOB、LONGTEXT 等大字段 (可建前綴索引 col(20)，但 5.7+ 支援全文索引更合適)。
4. 寫多讀少、且寫性能極其敏感的表 (如日誌表、流水表)。

MySQL 創建索引語法速查

-- 普通索引
CREATE INDEX idx_name ON tbl(col);
ALTER TABLE tbl ADD INDEX idx_name (col);

-- 唯一索引
CREATE UNIQUE INDEX uk_name ON tbl(col);
ALTER TABLE tbl ADD UNIQUE INDEX uk_name (col);

-- 主鍵 (建表時)
CREATE TABLE tbl (id INT PRIMARY KEY, ...);
ALTER TABLE tbl ADD PRIMARY KEY (id);

-- 全文索引 (5.6+ InnoDB)
CREATE FULLTEXT INDEX ft_name ON tbl(col);
ALTER TABLE tbl ADD FULLTEXT INDEX ft_name (col);

-- 多列/聯合索引 (遵循最左前綴)
CREATE INDEX idx_a_b_c ON tbl(a, b, c);

五大索引失效場景 (必背)

1. OR 條件未全覆蓋索引：WHERE a=1 OR b=2，若 a 有索引 b 無，全表掃描。解決：a,b 都建索引或改 UNION ALL。
2. 聯合索引非最左前綴：INDEX(a,b,c)，查詢 WHERE b=1 或 WHERE c=1 不走索引。
3. LIKE 以 % 開頭：WHERE name LIKE '%abc%' 不走索引；LIKE 'abc%' 可走。
4. 字符列隱式轉換：WHERE phone = 13800138000 (phone 是 VARCHAR)，MySQL 會轉換列值導致索引失效。解決：WHERE phone = '13800138000'。
5. 優化器判斷全掃更快：回表代價過大 (如需讀 > 20% 行)，優化器主動放棄索引。解決：覆蓋索引、減少 SELECT *、調整 optimizer_switch。

監控索引使用情況

SHOW STATUS LIKE 'Handler_read%';
-- Handler_read_key: 索引讀取次數 (越高越好)
-- Handler_read_rnd_next: 全表掃描次數 (越低越好)

最佳實踐清單

1. 聯合索引列順序：WHERE 等值條件列 → ORDER BY 列 → SELECT 列 (覆蓋)。
2. 單表索引數 ≤ 5 個 (非極端場景)，定期 DROP 未用索引 (sys.schema_unused_indexes 視圖)。
3. 主鍵盡量用自增整型 (8 字節 BIGINT 或 4 字節 INT)，避免 UUID/字串導致二級索引膨脹。
4. 覆蓋索引優化：INDEX(a,b,c) 覆蓋 SELECT a,b,c FROM t WHERE a=1，Extra 顯示 Using index。
5. 外鍵列必建索引 (InnoDB 自動建)，避免鎖表掃描子表。
6. 定期 ANALYZE TABLE 更新統計信息，防止優化器誤判。

索引設計本質是「空間換時間」與「寫換讀」的權衡。沒有完美索引，只有適合當前查詢負載的索引。建立基線 → 慢查詢分析 → 針對性加索引 → 驗證回歸 → 定期清理，形成閉環。