MySQL 索引深度解析：主鍵索引與輔助索引的區別、設計原則與效能差異

索引是 MySQL 效能的靈魂。在 InnoDB 與 MyISAM 兩大引擎中，主鍵索引與輔助索引的存儲結構、查詢路徑、效能特徵存在本質差異。本文系統梳理兩者定義、InnoDB/MyISAM 下的異同、實測效能數據，並給出主鍵設計黃金法則。

一、核心定義

• 主鍵索引： PRIMARY KEY，由一或多欄位組成，唯一標識每行資料。一張表最多一個主鍵，值不可為 NULL。
• 輔助索引： 又稱二級索引（SECONDARY KEY），包含 唯一索引（UNIQUE KEY，允許單個 NULL）與 普通索引（INDEX，允許重複與 NULL）。

二、InnoDB 與 MyISAM 的本質差異

MyISAM（堆組織表，Heap Organized Table）

• 資料檔與索引檔分離（.MYD / .MYI）。
• 主鍵索引與「不允許 NULL 的唯一索引」在存儲結構上無本質區別，皆為非聚集索引，葉子節點存儲資料行指針（物理偏移量）。
• 查詢效能：主鍵 ≈ 不允許 NULL 的唯一索引 > 普通索引（約慢 30%+）。

InnoDB（索引組織表，Index Organized Table - IOT）

• 聚集索引 = 資料本身。主鍵索引葉子節點直接存儲完整行資料（含 ROW_ID、rollback pointer、trx_id 等隱藏列）。
• 輔助索引葉子節點存儲 主鍵值，而非物理指針。查詢需「雙重查找」：先走輔助索引找主鍵，再回主鍵索引取完整行（回表）。
• 聚集索引選擇規則：
  1. 顯式定義的 PRIMARY KEY；
  2. 首個 NOT NULL UNIQUE 索引；
  3. 內建 6 字節 ROW_ID（單調遞增）。

三、主鍵設計黃金法則（InnoDB 專用）

1. 使用無業務含義的自增整數（BIGINT/UNSIGNED）：保證主鍵單調遞增，新插入行總是追加在 B+ 樹最右邊，避免頁分裂與碎片。
2. 主鍵值不可更新：更新主鍵等同於 DELETE + INSERT，導致頁分裂、碎片、鎖競爭。
3. 避免動態類型（如 TIMESTAMP、UUID、MD5）：隨機分布導致大量隨機 I/O、頁分裂、碎片爆增。

為什麼自增主鍵高效？

• 插入時 B+ 樹右側擴展，頁分裂概率極低；
• 範圍查詢（WHERE id BETWEEN ...）可順序讀取，極大發揮預讀優勢；
• 輔助索引存儲的主鍵值短小（8 字節），節省空間，減少回表 I/O。

四、實測效能對比（100 萬行隨機點查）

結論： InnoDB 下主鍵查詢最快；唯一索引需一次回表（輔助索引→主鍵索引）；普通索引可能需多次回表（掃描滿足條件的所有主鍵值）。

五、輔助索引分類與應用場景

六、避坑指南

• 不要用 UUID/雪花 ID 做主鍵：若業務需分散式 ID，可用「自增主鍵 + 唯一索引（業務 ID）」雙欄設計。
• 輔助索引盡量覆蓋查詢欄位：實現「索引覆蓋」，避免回表。
• 定期 ANALYZE TABLE 更新統計資訊：防止優化器誤判導致全表掃描。
• 監控 Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests：命中率低於 99% 需擴容 Buffer Pool 或優化索引。

七、總結

InnoDB 中主鍵索引即聚集索引，資料與索引合一；輔助索引通過主鍵值回表。主鍵設計應遵循「自增、不變、無業務含義」三原則。理解兩者存儲結構與查詢路徑差異，是編寫高效 SQL、設計高效能資料庫架構的基石。