MySQL 索引深度實戰：從 B+ 樹原理到 EXPLAIN 執行計劃分析

前言

索引是 MySQL 效能調優的核心。本文系統覆蓋索引類型、儲存引擎差異、優缺點、建立與刪除語法、複合索引最左前綴原則、全文與空間索引、EXPLAIN 執行計劃深度解讀，並提供大量實戰案例。

一、索引基礎理論

1.1 儲存引擎與索引支援矩陣

1.2 InnoDB 叢集索引 vs 二級索引

• 叢集索引：主鍵索引，葉子節點包含完整行資料，表資料按主鍵順序儲存
• 二級索引：非主鍵索引，葉子節點存儲 索引列值 + 主鍵值，查詢需「回表」查主鍵索引取完整行
• 覆蓋索引：查詢列完全包含在二級索引中，避免回表，極大提升效能

1.3 索引優缺點權衡

二、索引分類與建立語法

2.1 統一建立語法

CREATE TABLE table_name (
  col_name data_type,
  [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [(length)]) [ASC | DESC]
);

• INDEX 與 KEY 同義
• length 僅適用字串類型 (CHAR/VARCHAR/TEXT/BLOB)，指定前綴長度
• ASC/DESC MySQL 8.0+ 真正支援降序索引，舊版忽略但語法兼容

2.2 普通索引

CREATE TABLE book (
  bookid INT NOT NULL,
  bookname VARCHAR(255) NOT NULL,
  year_publication YEAR NOT NULL,
  INDEX idx_year (year_publication)
);

2.3 唯一索引

CREATE TABLE t1 (
  id INT NOT NULL,
  name CHAR(30) NOT NULL,
  UNIQUE INDEX uniq_idx (id)
);

主鍵 = 唯一索引 + NOT NULL。

2.4 複合索引（聯合索引）

CREATE TABLE t3 (
  id INT NOT NULL,
  name CHAR(30) NOT NULL,
  age INT NOT NULL,
  INDEX multi_idx (id, name, age)
);

最左前綴原則：查詢必須從索引最左列開始，才能使用索引。

2.5 全文索引

• 類型：FULLTEXT
• 支援引擎：MyISAM (全版本)、InnoDB (5.6+)
• 適用欄位：CHAR、VARCHAR、TEXT
• 語法：FULLTEXT INDEX ft_idx (content)
• 查詢：MATCH(content) AGAINST('keyword' IN NATURAL LANGUAGE MODE)

2.6 空間索引

• 類型：SPATIAL
• 支援引擎：MyISAM、InnoDB (5.7+)
• 欄位類型：GEOMETRY、POINT、LINESTRING、POLYGON
• 強制 NOT NULL
• 語法：SPATIAL INDEX spat_idx (geo_col)

三、現有表上建立/刪除索引

3.1 ALTER TABLE

-- 普通索引
ALTER TABLE t1 ADD INDEX idx_name (name);

-- 唯一索引
ALTER TABLE t1 ADD UNIQUE INDEX uniq_email (email);

-- 複合索引
ALTER TABLE t3 ADD INDEX idx_age_info (age, info);

-- 全文索引 (需 MyISAM)
ALTER TABLE t6 ADD FULLTEXT INDEX ft_info (info);

-- 空間索引 (需 NOT NULL + MyISAM/InnoDB 5.7+)
ALTER TABLE t7 ADD SPATIAL INDEX spat_g (g);

3.2 CREATE INDEX

CREATE INDEX idx_bookname ON book (bookname);
CREATE UNIQUE INDEX uniq_bookid ON book (bookid);
CREATE INDEX idx_auth_info ON book (authors(20), info(50));
CREATE FULLTEXT INDEX ft_info ON t6 (info);
CREATE SPATIAL INDEX spat_g ON t7 (g);

3.3 刪除索引

ALTER TABLE book DROP INDEX uniq_bookid;
DROP INDEX idx_auth_info ON book;

注意：AUTO_INCREMENT 欄位的主鍵/唯一索引不可刪除。

3.4 刪除欄位對索引的影響

• 刪除的欄位若為索引組成部分，該欄位從索引中移除
• 若索引所有欄位均被刪除，整個索引自動刪除

四、EXPLAIN 執行計劃深度解讀

4.1 核心欄位含義

4.2 type 存取類型詳細解析

system/const    -- 系統表/主鍵唯一查找，極快
eq_ref          -- 唯一索引/主鍵 JOIN，每行最多一匹配
ref             -- 非唯一索引查找/關聯，可能多行
range           -- 索引範圍掃描 (>、<、>=、<=、BETWEEN、IN)
index           -- 全索引掃描 (覆蓋索引時常見)
ALL             -- 全表掃描 (最差，需優化)

4.3 實戰案例分析

EXPLAIN SELECT * FROM t3 WHERE id=1 AND name='小芳';
-- 結果：
-- type=ref, possible_keys=multi_idx, key=multi_idx, key_len=94, ref=const,const, rows=1, Extra=Using where
-- ✅ 使用複合索引，最左匹配 id、name，預估 1 行

EXPLAIN SELECT * FROM t3 WHERE name='小芳';
-- 結果：
-- type=ALL, possible_keys=NULL, key=NULL, rows=2, Extra=Using where
-- ❌ 未用索引，因 name 非最左列

五、索引優化實戰清單

1. 慢查詢優先：開啟 slow_query_log，pt-query-digest 分析 Top 10
2. EXPLAIN 驗證：關注 type、key、rows、Extra
3. 覆蓋索引：SELECT id, name FROM t WHERE id=1 → INDEX(id, name) 避免回表
4. 最左前綴：複合索引列序 = 查詢條件列序 = 排序列序
5. 避免索引失效：
   • 隱式轉換：WHERE varchar_col = 123 → WHERE varchar_col = '123'
   • 函數運算：WHERE DATE(created_at) = '2024-01-01' → WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
   • LIKE 導數：LIKE '%keyword%' → 全文索引或 LIKE 'keyword%'
   • OR 條件：WHERE a=1 OR b=2 → UNION ALL 或複合索引
6. 定期維護：ANALYZE TABLE 更新統計資訊、OPTIMIZE TABLE 整理碎片

總結

• InnoDB 叢集索引決定資料物理順序，**主鍵應單調遞增 (自增 ID/雪花 ID)**，避免頁分裂
• 二級索引葉子存主鍵值** → 覆蓋索引是避免回表的關鍵
• 複合索引遵循最左前綴** → 列序設計 = 業務查詢模式
• EXPLAIN 是放大鏡** → 讀懂 type/key/rows/Extra = 讀懂執行計劃
• 索引非越多越好** → 寫入代價、空間佔用、優化器負擔，定期審視 sys.schema_unused_indexes