MySQL 索引原理與添加原則：B+Tree 結構、最左前綴、覆蓋索引

MySQL 索引原理與添加原則：B+Tree 結構、最左前綴、覆蓋索引

理解索引底層結構是設計高效索引的前提。InnoDB 使用 B+Tree（B-Tree 變種），葉子節點鏈表掃描支援範圍查詢，非葉子僅存鍵值導航。

索引底層存儲

• **聚簇索引** = 主鍵索引，葉子節點存完整行數據。
• **二級索引** 葉子節點存主鍵值，查詢需「回表」查聚簇索引獲取非索引列。
• **覆蓋索引**：查詢列全在二級索引中 → 免回表，Extra: Using index。

添加索引三大原則

1. 只為 WHERE、JOIN、ORDER BY、GROUP BY 涉及的列建索引，避免無效寫入開銷與佔用空間。
2. 聯合索引遵循最左前綴：INDEX(a,b,c) 服務 a/a,b/a,b,c；列順序按**區分度降序**（基數大→小）或**查詢頻率/範圍列靠右**。
3. 適度原則：單表索引建議 ≤ 5 個；寫入密集表更少。定期 ANALYZE TABLE 更新統計信息，配合 pt-duplicate-key-checker 清理重複/冗餘索引。

索引自動維護

• INSERT/DELETE/UPDATE 觸發 B+Tree 分裂/合併/旋轉，InnoDB 透明處理。
• 大批量導入建議：DROP INDEX → LOAD DATA → CREATE INDEX 或 SET UNIQUE_CHECKS=0; SET FOREIGN_KEY_CHECKS=0; 導入後重建。

常見誤區

• 「索引越多越好」→ 寫入放大、優化器選擇困難、統計信息偏差。
• 「字符串列整列建索引」→ 體積大、比較慢；用**前綴索引** INDEX(name(10)) 或哈希索引 INDEX(crc32_col)。
• 「外鍵自動建索引足夠」→ 外鍵索引僅服務約束檢查，業務查詢需單獨建立覆蓋索引。

驗證清單

EXPLAIN SELECT ...  -- type=ref/range, key=idx_xxx, key_len 合理, rows 小, Extra=Using index
SHOW INDEX FROM tbl;  -- Cardinality 基數越大越好
ANALYZE TABLE tbl;    -- 更新統計信息