MySQL 索引策略核心原則：四大注意事項與引擎差異解析

索引策略在數據庫性能中的地位

影響性能的五大核心策略：鎖策略、緩存策略、索引策略、存儲策略、執行計劃優化策略。索引策略決定數據定位效率，存儲策略決定持久化效率。

四大核心注意事項

1. 索引不存儲 NULL 值

• 單列索引：完全不存儲 NULL
• 複合索引：不存儲全為 NULL 的行
• 後果：WHERE col IS NULL 無法利用索引，觸發全表掃描

原理：B+Tree 建樹過程需比較鍵值，NULL 參與運算結果多為 NULL，無法建立有序關係，故不入葉子節點。

對策：業務上能用 NOT NULL DEFAULT '' 或 0 替代 NULL；必須查 NULL 時考慮位圖索引或分區裁剪。

2. 不適合低基數列（重複值多）

案例：type 列只有 5 個值，1 萬行數據，WHERE type=1 需訪問 2000 行 + 索引塊 > 200 塊；全表掃描 10 行/塊僅 1000 塊，優化器會選擇全表掃描。

經驗值：選擇性 Count(DISTINCT col)/Count(*) < 0.2 時通常不走索引。

例外：覆蓋索引、排序需求、外鍵關聯仍可能用到。

3. 前導模糊查詢無法利用索引

• LIKE '%abc'、LIKE '%abc%'：全索引/全表掃描
• LIKE 'abc%'：可用索引範圍掃描，遇到非 'a' 開頭即停止

原理：B+Tree 按字典序存儲，前導通配符破壞前綴有序性。

替代方案：全文索引、反轉列存儲、搜索引擎。

4. 兩大主流索引類型：B-Tree vs Hash

存儲引擎索引實現差異

實戰建議

1. 主鍵選擇：InnoDB 必須有主鍵，建議自增整型，避免 UUID 導致頁分裂
2. 覆蓋索引：SELECT a,b FROM t WHERE c=1 建立 idx(c,a,b) 避免回表
3. 最左前綴：聯合索引 (a,b,c) 支援 a/a,b/a,b,c，不支援 b/c/b,c
4. 索引下推 (ICP, 5.6+)：WHERE a=1 AND b LIKE 'xy%' 在存儲引擎層過濾 b，減少回表
5. 定期 ANALYZE TABLE 更新統計信息，防止優化器誤判