MySQL 索引使用機制深度分析：單列 vs 組合、最左前綴、覆蓋索引、LIKE 邊界、不足與代價

通過 testIndex / myIndex 兩張示例表，演示索引如何將「掃描 1000 行」降為「精準定位 1 行」，並對比單列索引、組合索引、最左前綴、覆蓋索引、LIKE 使用邊界、索引代價，建立直觀的索引效能模型。

一、 基礎驗證：索引加速 100 倍

CREATE TABLE testIndex (i_testID INT, vc_Name VARCHAR(16));
-- 插入 1000 行，其中一行 vc_Name='erquan'
SELECT * FROM testIndex WHERE vc_Name='erquan';
-- 無索引：掃描 1000 行
-- 有索引：直接定位，速度提升 100 倍

二、 單列索引 vs 組合索引實測

CREATE TABLE myIndex (
  i_testID INT PRIMARY KEY AUTO_INCREMENT,
  vc_Name VARCHAR(50), vc_City VARCHAR(50), i_Age INT, i_SchoolID INT
);
-- 5 條 vc_Name='erquan'，city/age/school 組合各異
-- 查詢：WHERE vc_Name='erquan' AND vc_City='鄭州' AND i_Age=25

• 單列索引 (vc_Name)：鎖定 5 行 → 回表 → 過濾 city/age → 1 行。仍需回表 5 次。
• 組合索引 (vc_Name(10), vc_City, i_Age)：直接定位唯一行，零回表、零排序。
• 三個單列索引：優化器只能選一個（通常 vc_Name），效率遠低於組合索引。

三、 組合索引 = 隱含多個前綴索引

INDEX idx_name_city_age (vc_Name(10), vc_City, i_Age)
-- 等價於同時擁有：
-- (vc_Name, vc_City, i_Age)
-- (vc_Name, vc_City)
-- (vc_Name)
-- 但 沒有 (vc_City, i_Age)、(vc_City)、(i_Age)

最左前綴 = B+Tree 字典序前綴有序。

四、 使用索引的條件邊界

五、 索引的代價（必讀）

1. 寫入放大：每個索引都是獨立 B+Tree，INSERT/UPDATE/DELETE 需修改所有樹。
2. 空間膨脹：大表多組合索引易超過數據本體大小。
3. 維護開銷：統計信息收集、優化器評估、DDL 鎖定時間隨索引數增長。

結論：按查詢模式建最小必要索引集，單表 ≤ 6 個，定期 ANALYZE TABLE 更新統計。

1. 上一篇文章： MySQL filesort 優化方案
2. 下一篇文章： MySQL 聯合索引 vs 單列索引實測：為什麼有時單列更有效、索引列選擇決定成敗

MySQL 綜合教學

徹底解決 Windows 命令列 MySQL 中文亂碼問題

徹底解決 Windows 命令列 MySQL 中文亂碼問題

MySQL EXPLAIN 執行計劃詳解：核心字段含義、類型排序、優化判讀實戰

MySQL EXPLAIN 執行計劃詳解：核心字段含義、類型

MySQL 表索引損壞修復實戰：myisamchk 與 InnoDB 故障恢復全流程

MySQL 表索引損壞急救指南：MyISAM myisamc

MySQL 自訂隨機字串函式實作：MD5、UUID 與字元集三種方法

MySQL 自訂隨機字串函式實作：MD5、UUID 與字元集