MySQL 索引分類詳解：主鍵索引與輔助索引的區別與效能對比

一、主鍵索引

• 簡稱 PK，PRIMARY KEY 約束
• 由一或多列組成，唯一標識每行記錄
• 一張表最多一個主鍵，值不可為 NULL

二、InnoDB 主鍵設計三大原則

1. 無業務含義的自增列：BIGINT UNSIGNED AUTO_INCREMENT
2. 主鍵值不可更新：僅允許 INSERT/DELETE，禁止 UPDATE
3. 避免動態類型：如 TIMESTAMP、UUID() 等

收益：順序寫入減少頁分裂、主鍵不變避免行遷移與碎片。

三、輔助索引

• 即二級索引，SECONDARY KEY
• 分類：唯一索引（UNIQUE KEY）與 普通索引（INDEX/KEY）
• 唯一索引本質是唯一性約束，允許單個 NULL（多列唯一索引允許部分 NULL）

四、MyISAM vs InnoDB 索引差異

五、查詢效能實測對比（100 萬行整數列隨機點查）

原因分析：

• InnoDB 主鍵查詢：單次 B+ 樹查找直達葉子節點（完整行）
• InnoDB 唯一索引：二級索引查找 → 回表主鍵索引（兩次 B+ 樹）
• InnoDB 普通索引：二級索引查找 → 回表主鍵索引 → 可能需繼續掃描下一條確認唯一性

六、實戰選型指南

1. **業務主鍵 ≠ 物理主鍵**：業務唯一鍵（如身份證、手機號）建立 UNIQUE KEY，物理主鍵用自增 ID
2. **覆蓋索引優化**：高頻查詢將所需列包含在二級索引中，避免回表
3. **外鍵關聯**：被引用表必須有索引（主鍵或唯一索引），引用表建普通索引
4. **複合索引順序**：最左前綴原則，區分度高的列靠前

七、常見誤區澄清

八、總結

InnoDB 中主鍵索引即聚集索引，擁有天然效能優勢。輔助索引（唯一/普通）葉子節點存主鍵值，查詢需回表。設計表時：**物理主鍵用自增無業務含義列，業務唯一性用 UNIQUE KEY 保證，高頻查詢用覆蓋索引優化**，是索引設計的鐵律。