MySQL 索引原理與實戰大全：B+Tree、覆蓋索引、最左前綴、全文索引、優化禁忌與驗證方法

索引基礎

索引是對列值排序的數據結構，提供指向數據行的邏輯指針。作用等同圖書目錄：先查目錄定位頁碼，再翻頁獲取內容，避免全表掃描。

MySQL 索引類型概覽

• 所有列類型可被索引。
• 每表至少 16 個索引，總長度 ≥ 256 字節 (各引擎上限不同)。
• MyISAM/InnoDB 前綴長度 ≤ 1000 字節 (5.1+)，多字節字符集按字節計算。
• FULLTEXT：僅 MyISAM (5.6+ InnoDB 支持)，CHAR/VARCHAR/TEXT，整列索引，不支持前綴。
• 空間索引：僅 MyISAM，R-Tree。
• MEMORY 默認 Hash 索引，可選 B-Tree。

B-Tree vs Hash 索引適用場景

結論：InnoDB 默認 B+Tree，通用性最強；Hash 僅適合純等值查詢且不需排序/範圍。

索引創建與管理

1. 普通索引

CREATE INDEX idx_name ON t_student(stu_name(4));
ALTER TABLE t_student ADD INDEX idx_name(stu_name(4));
CREATE TABLE t (..., INDEX idx_name (name(5)));

• CHAR/VARCHAR 可指定前綴長度；BLOB/TEXT 必須 指定前綴。

2. 唯一索引

CREATE UNIQUE INDEX idx_uk ON t(col(length));
ALTER TABLE t ADD UNIQUE idx_uk (col(length));

• 值唯一，允許 NULL (多個 NULL 不衝突)。主鍵是特殊唯一索引 (NOT NULL + AUTO_INCREMENT 常見)。

3. 多列索引 (復合索引)

CREATE INDEX idx_age_sex ON t_student(stu_age, stu_sex);

最左前綴原則：索引 (a,b,c) 等價於擁有 (a)、(a,b)、(a,b,c) 三個索引。
可用查詢：WHERE a=1、WHERE a=1 AND b=2、WHERE a=1 AND b=2 AND c=3。
不可用：WHERE b=2、WHERE c=3、WHERE b=2 AND c=3 (未包含最左列 a)。

4. 全文索引

CREATE TABLE article (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  content TEXT,
  FULLTEXT (title, content)
) ENGINE=MyISAM;

-- 查詢
SELECT * FROM article WHERE MATCH(title, content) AGAINST('查詢字符串');

• MyISAM 專屬 (5.6+ InnoDB 支持)，CHAR/VARCHAR/TEXT。
• 搜索字符串必須是常量，不能是列名。
• 匹配度超 50% 視為無匹配 (自然語言模式)。

5. 驗證索引是否生效

EXPLAIN SELECT * FROM t_student WHERE stu_age = 12;
-- key=idx_age_sex 表示用到索引
-- type=ref/range/eq_ref 非 ALL 即生效

索引的代價 (不足之處)

1. 降低寫入速度 (INSERT/UPDATE/DELETE 需維護索引文件)。
2. 佔用磁盤空間 (大表多組合索引膨脹快)。

使用索引六大注意事項 (避坑指南)

1. NULL 值不入索引：列含 NULL 不進索引，復合索引任一列 NULL 導致該列對復合索引無效。設計時 NOT NULL + DEFAULT。
2. 短索引：CHAR(255) 列前 10-20 字符區分度高時，只索引前綴，節省空間加快查詢。
3. 索引列排序：MySQL 單表單查詢只用一個索引，WHERE 用了索引則 ORDER BY 列不再用索引。盡量避免多列排序，需排序則建復合索引覆蓋。
4. LIKE 優化：LIKE 'abc%' 可用索引；LIKE '%abc%' 全表掃描。
5. 列不做運算：WHERE YEAR(date) < 2007 → 全表掃描。改為 WHERE date < '2007-01-01'。
6. 不用 NOT IN / <>：導致全表掃描，改用 NOT EXISTS / LEFT JOIN ... IS NULL。

實戰驗證示例

建表 t_student，加索引 idx_age_sex (age, sex)，插入數據。
EXPLAIN SELECT * FROM t_student WHERE age=12 → key=idx_age_sex, type=ref (用到索引)，結果按 sex、age 排序。
刪除索引後同樣查詢 → key=NULL, type=ALL (全表掃描)，結果按 id 排序。