MySQL 索引背後的數據結構與高性能策略：最左前綴、覆蓋索引、前綴索引與 InnoDB 主鍵選擇

示例環境

採用 MySQL 官方 employees 測試庫（ER 圖見文中），數據量百萬級，適合驗證索引策略。

最左前綴原理實戰

表 titles 主鍵 (emp_no, title, from_date)，輔助索引 emp_no 已刪除，專注分析主鍵行為。

「隔離列」填坑技巧

當中間列基數極小（如 title 僅 7 種值）時，可用 IN 列舉所有值形成最左前綴：

WHERE emp_no=... 
  AND title IN ('Senior Engineer','Staff',...)
  AND from_date=...

實測 key_len 從 4 升至 59，性能微幅提升；若中間列基數大則需建輔助索引。

索引選擇性與前綴索引

選擇性 = Cardinality / 總行數，越接近 1 越好。

• title 選擇性 0.000016 → 不建單列索引；
• employees 表 first_name 選擇性 0.0042，(first_name, last_name) 0.9313 但長度 30；
• 折中：(first_name, left(last_name,4)) 選擇性 0.9007、長度 18，建立前綴索引後查詢快 120 倍。

前綴索引限制：不可用於 ORDER BY/GROUP BY，無法實現覆蓋索引。

InnoDB 主鍵選擇鐵律

業務無關的自增整數。

• 自增主鍵：順序寫入葉子頁，頁分裂少，無碎片，插入極快；
• 非自增（身份證、學號）：隨機插入導致頁分裂、數據移動、碎片嚴重，需定期 OPTIMIZE TABLE。

圖示對比見文中「圖13 緊湊結構」vs「圖14 碎片結構」。