MySQL索引：數據庫的「超級目錄」

想象你有一本1000頁的百科全書，要快速找到某個知識點（如“光合作用”）：

• ? 無索引：逐頁翻找 → 全表掃描（慢！）
• ? 有索引：直接查目錄 → 精準定位（快！）

索引的本質：預先對數據排序+存儲位置信息，加速檢索的特殊數據結構。

---

一、索引類型及原理

1. 數據結構

索引類型	數據結構	適用場景	特點
B+樹索引	多叉平衡樹	默認索引（InnoDB）	范圍查詢快，適合磁盤存儲
哈希索引	哈希表	精確匹配（Memory引擎）	等值查詢極快，不支持范圍查詢
全文索引	倒排索引	文本搜索（MATCH AGAINST）	解決LIKE '%word%'低效問題

📌 B+樹為什么快？

• 葉子節點形成鏈表 → 范圍查詢高效（如WHERE id > 100）
• 非葉子節點只存索引 → 單節點存儲更多key
• 所有數據在葉子節點 → 查詢路徑長度一致

2. 邏輯分類

索引類型	描述	示例
主鍵索引	唯一標識，不允許NULL	PRIMARY KEY (id)
唯一索引	保證列值唯一，允許NULL	UNIQUE KEY (email)
普通索引	加速查詢，允許重復值	INDEX (name)
聯合索引	多列組合索引	INDEX (city, age)

---

二、索引生效與失效場景

? 生效場景

-- 1. 全值匹配  
SELECT * FROM users WHERE name = 'Alice';  -- 2. 最左前綴原則（聯合索引）  
INDEX (a, b, c)  -- 生效: WHERE a=? / WHERE a=? AND b=? / WHERE a=? AND b=? AND c=?  -- 3. 范圍查詢（部分生效）  
SELECT * FROM orders WHERE amount > 100 AND status = 1;  -- (amount)索引生效  -- 4. 覆蓋索引（直接從索引拿數據）  
SELECT id FROM products WHERE price > 50;  -- 索引包含(id,price)  

? 失效場景

-- 1. 違反最左前綴  
INDEX (a, b, c)  
WHERE b = 2;     -- ? 索引失效  -- 2. 對索引列運算  
WHERE YEAR(create_time) = 2023;  -- ? 改用: create_time BETWEEN '2023-01-01' AND '2023-12-31'  -- 3. 隱式類型轉換  
WHERE phone = 13800138000;  -- ? phone是varchar類型  -- 4. LIKE左模糊  
WHERE name LIKE '%Lee';     -- ? 全表掃描  -- 5. OR條件未全覆蓋  
WHERE age = 18 OR name = 'Bob';  -- 若name無索引 → 全表掃描  

---

三、索引優化策略

1. EXPLAIN診斷工具

EXPLAIN SELECT * FROM employees WHERE department_id = 3;  

關鍵指標：

• type：system > const > ref > range > index > ALL（性能從優到差）
• key：實際使用的索引
• rows：掃描行數（越小越好）

2. 設計原則

策略	說明
只為高頻查詢建索引	避免維護成本（增刪改變慢）
短字段優先	整型索引比字符串快，考慮用city_code代替city_name
避免冗余索引	INDEX(a,b) 和 INDEX(a) 同時存在 → 后者冗余
前綴索引	長文本可截取前N字符：ALTER TABLE t ADD INDEX (text_col(10))

3. 慢查詢優化示例

問題SQL：

SELECT * FROM logs WHERE user_id = 1001 AND DATE(create_time) = '2023-10-01';  

優化步驟：

1. 避免對create_time計算 → 改用范圍查詢
2. 建立聯合索引(user_id, create_time)

-- 優化后  
SELECT * FROM logs  
WHERE user_id = 1001  AND create_time >= '2023-10-01 00:00:00'  AND create_time < '2023-10-02 00:00:00';  

---

四、索引的代價

• 空間代價：索引占用額外存儲（特別是B+樹的非葉子節點）
• 時間代價：
  • INSERT：需更新索引 → 性能下降約10%
  • UPDATE：若修改索引列 → 觸發索引重組
  • DELETE：標記刪除 → 產生索引碎片

💡 黃金法則：
不要為小表建索引（全表掃描更快）
中大型表重點優化WHERE和JOIN列

---

五、高級技巧

1. 索引下推（ICP）

MySQL 5.6+

INDEX (age, city)  
SELECT * FROM users WHERE age > 20 AND city = 'Beijing';  

• 舊版本：先按age>20回表查數據 → 再過濾city
• 開啟ICP：在索引層直接過濾city → 減少回表次數

2. 覆蓋索引優化

-- 原始查詢  
SELECT id, name FROM products WHERE category = 'Electronics';  -- 優化方案：  
ALTER TABLE products ADD INDEX (category, id, name);  -- 覆蓋索引  

數據直接從索引返回 → 避免回表查主鍵

---

總結：索引使用指南

1. 建索引前問3個問題：

   • 數據量是否足夠大？
   • 查詢頻率是否高？
   • 該字段過濾性是否好？（如性別字段不適合單獨建索引）

2. 優先考慮：

   • WHERE條件列、JOIN關聯列、ORDER BY排序列

3. 定期維護：

   ANALYZE TABLE users;       -- 更新索引統計信息  
   OPTIMIZE TABLE orders;     -- 重建表+索引（解決碎片問題）  
   

🚨 最后警告：
索引不是越多越好！

• 表數據量<1萬 → 通常不需要索引
• 每增加一個索引 → INSERT速度降低約10%