MySQL 索引創建決策指南：何時建、何時不建、四大核心判斷準則

索引的代價與收益

索引提升查詢效率、降低 I/O、降低排序成本（CPU/記憶體），但寫入時需維護索引結構（B+ 樹分裂合併），佔用儲存空間。決策核心：查詢收益 > 維護成本。

四大核心判斷準則

1. 高頻 WHERE / JOIN / ORDER BY / GROUP BY 條件欄位 → 建

• 分析所有核心 SQL，統計欄位出現頻率
• 出現在 80%+ 核心查詢的欄位，優先建立組合索引
• 覆蓋索引：INDEX (a,b,c) 可服務 WHERE a=1 AND b=2、ORDER BY a,b、SELECT a,b,c

2. 低基數欄位（性別、狀態、類型、布爾值） → 不建

• 基數 = 不同值數量 / 總行數。Cardinality / Rows < 0.01 視為低基數
• 優化器評估：掃描索引 + 回表成本 > 全表掃描，直接選擇全表掃描
• 例外：組合索引前綴 為高基數時，低基數欄位可作為第二/三列（如 INDEX (user_id, status)）

3. 高頻更新欄位 → 謹慎建 / 不建

• 每次 UPDATE 都要修改索引：B+ 樹定位、分裂/合併、Undo Log 記錄
• 經驗值：讀寫比 < 10:1 時重新評估必要性
• 如 last_login_time、view_count、balance 高頻更新，通常不建單列索引

4. 永不出現在 WHERE/JOIN/ORDER BY/GROUP BY → 不建

• 純展示欄位（description、remark、avatar_url）
• 大文本/Blob 欄位（前綴索引除外）

進階決策矩陣

驗證索引有效性

EXPLAIN SELECT * FROM orders WHERE user_id=123 AND status=1;
-- key: idx_user_status, type: ref, rows: 10 → 索引生效
-- type: ALL, rows: 1000000 → 全表掃描，索引失效

SHOW INDEX FROM orders;
-- Cardinality 接近 Rows 為高選擇性

維護與清理

• 定期 ANALYZE TABLE 更新統計資訊（InnoDB 自動統計通常足夠）
• pt-duplicate-key-checker 檢測重複/冗餘索引
• pt-index-usage 分析慢查詢日誌找出未用索引
• 刪除未用索引：DROP INDEX idx_x ON tbl

小結

索引不是越多越好。核心四問：高頻查詢條件？高基數？低頻更新？確實會被用到？ 全部是 → 建；有一個否 → 重新評估。組合索引遵循最左前綴、區分度高的前置、範圍列最後。定期清理無用索引，讓寫入輕裝上陣。