MySQL 索引儲存架構差異：MyISAM vs InnoDB (Primary Key vs Secondary Key)

前言

MyISAM 與 InnoDB 對索引的底層儲存機制截然不同，直接影響查詢效能、鎖行為與備份策略。本文對比兩大引擎在 Primary Key (PK) 與 Secondary Key (SK) 上的儲存差異，並解析最左前綴在 InnoDB 中的實際運作。

MyISAM：非叢集索引 (Non-Clustered)

• PK 與 SK 結構相同：B-Tree 葉節點儲存 資料行指標 (Row Pointer / File Offset)。
• 查詢流程：索引樹搜尋 → 取得行指標 → 直接讀取資料檔。
• 主鍵與次要索引地位對等，無特殊綁定。

InnoDB：叢集索引 (Clustered Index)

Primary Key (叢集索引)

• 資料檔即是主鍵 B+Tree，葉節點包含 完整行資料 (所有欄位值) + 事務 ID + 回滾指標。
• 主鍵查詢 = 直接在叢集索引樹完成，極快 (Covering Index by nature)。
• 若無明確 PK，InnoDB 會選第一個 NOT NULL UNIQUE 鍵，否則內建 6-byte 隱藏 ROW_ID。

Secondary Key (非叢集索引)

• 葉節點儲存 主鍵值 (而非行指標)。
• 查詢流程：兩次 B+Tree 查找 (回表) → 1. 次要索引樹找 PK 值 → 2. 叢集索引樹找完整行。
• 優點：資料移動 (頁分裂/合併) 時，次要索引無需更新行指標，只需維護 PK 值 (PK 不變)。

最左前綴原則實證

CREATE TABLE test (
  id INT NOT NULL,
  last_name CHAR(30) NOT NULL,
  first_name CHAR(30) NOT NULL,
  PRIMARY KEY (id),
  INDEX name (last_name, first_name)
);

索引 name (last_name, first_name) 可用於：

• WHERE last_name='Widenius'
• WHERE last_name='Widenius' AND first_name='Michael'
• WHERE last_name='Widenius' AND first_name IN ('Michael','Monty')
• WHERE last_name='Widenius' AND first_name>='M' AND first_name<'N'

不可用：

• WHERE first_name='Michael' (缺最左欄)
• WHERE last_name='Widenius' OR first_name='Michael' (OR 導致無法用索引合併)

實務啟示

1. InnoDB 表務必設計有意義的主鍵 (自增整數最佳)，避免 UUID/長字串導致叢集索引膨脹、次要索引跟著變大。
2. 次要索引查詢若只需索引欄位 (Covering Index)，可避免回表，效能接近 PK 查詢。
3. 大量寫入場景：MyISAM 表鎖 vs InnoDB 行鎖 + MVCC，選型需綜合考量。

延伸閱讀

• MySQL 索引的核心作用與效能量化
• MySQL 索引缺點與注意事項
• ADO 連接 MySQL 實作指南
• MySQL 觸發器 NEW/OLD 介紹
• MySQL 觸發器建立語法