MySQL 索引優化實戰：分類、適用場景與失效陷阱全解析

MySQL 索引優化實戰：分類、適用場景與失效陷阱全解析

索引分類

索引在存儲引擎層實現，不同引擎支援不同。MySQL 主流索引類型：

• B-Tree 索引：最常見，大多數引擎支援。
• Full-text 索引：全文搜尋。
• Hash 索引：僅 Memory 引擎支援，適合等值查詢，不支援範圍查詢。
• R-Tree 索引：空間索引，MyISAM 專屬，較少用。

衍生索引：主鍵、唯一索引、複合索引、前綴索引等。

索引生效的典型場景

1. 全值匹配

對複合索引所有列指定具體值（等值條件）。

-- 索引 (rental_date, inventory_id, customer_id)
EXPLAIN SELECT * FROM rental 
WHERE rental_date='2005-05-25 17:22:10' 
  AND inventory_id=373 
  AND customer_id=343;

2. 範圍查詢

對索引列進行範圍查找。

-- 索引 idx_fk_customer_id(customer_id)
EXPLAIN SELECT * FROM rental 
WHERE customer_id >= 373 AND customer_id < 400;

3. 最左前綴匹配

僅使用複合索引最左列（或最左 N 列）查詢。

-- 索引 idx_payment_date(payment_date, amount, last_update)
EXPLAIN SELECT * FROM payment 
WHERE payment_date='2006-02-14 15:16:03' 
  AND last_update = '2006-02-15 22:12:32';

可用到索引，但非最左列 amount 缺失，僅用到部分索引（rows=182）。

4. 覆蓋索引

查詢列全在索引中，Extra 顯示 Using index，無需回表。

EXPLAIN SELECT last_update FROM payment 
WHERE payment_date='2005-08-19 21:21:47' AND amount=4.99;

5. 列前綴匹配

對字串列前綴建索引並查詢。

-- 索引 idx_title_desc_part(title(10), description(20))
EXPLAIN SELECT title FROM film_text WHERE title LIKE 'AFRICAN%';

6. 部分等值 + 部分範圍

EXPLAIN SELECT inventory_id FROM rental 
WHERE rental_date='2006-02-14 15:16:03' 
  AND customer_id >= 300 AND customer_id <= 400;

7. 索引列 IS NULL

EXPLAIN SELECT * FROM payment WHERE rental_id IS NULL;

索引存在但失效的典型陷阱

1. LIKE '%...%' 以萬用字元開頭

EXPLAIN SELECT * FROM actor WHERE last_name LIKE '%NI%';

優化：用子查詢先取主鍵再回表。

2. 隱式類型轉換

EXPLAIN SELECT * FROM actor WHERE last_name=1;  -- 不用索引
EXPLAIN SELECT * FROM actor WHERE last_name='1'; -- 用索引

3. 複合索引違反最左原則

EXPLAIN SELECT * FROM payment 
WHERE amount=3.98 AND last_update='2006-02-15 22:12:32';

4. 優化器判定全表掃描更快

如 title LIKE 'S%' 回傳比例過大，優化器放棄索引。

5. OR 條件任一列無索引

EXPLAIN SELECT * FROM payment 
WHERE customer_id=203 OR amount=3.96;  -- amount 無索引導致全表掃描