?SQL 優化的核心 —— 什么時候能“走索引”，什么時候會“失效”。整理一個索引生效/失效規則 + 核心原理的全景圖，幫助徹底理解。

---

🔑 MySQL 索引使用的核心原理

MySQL 使用 B+Tree 索引（最常見），特點是：

• 數據在磁盤上是 有序存儲 的。

• 查詢時會從根節點到葉子節點 二分查找。

• 只有滿足 有序性 的條件才能利用索引，否則會退化成全表掃描。

所以，判斷 SQL 是否能走索引的關鍵就是：WHERE 條件是否能利用索引的有序性。

---

? 典型場景：會走索引

1. 精確匹配（==, IN）

SELECT * FROM user WHERE id = 100;   -- 主鍵索引
SELECT * FROM user WHERE email IN ('a@xx.com','b@xx.com'); -- 普通索引

👉 等值查詢最友好，100%用到索引。

---

2. 范圍查詢（BETWEEN, >, <, >=, <=）

SELECT * FROM orders WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31';

👉 范圍條件能利用索引的有序性。

---

3. 前綴匹配（LIKE 'abc%'）

SELECT * FROM article WHERE title LIKE '優化%';

👉 因為能從 abc 開頭定位，所以可走索引。

---

4. 復合索引（最左前綴原則）

CREATE INDEX idx_user_name_age ON user(name, age);-- 能走索引：
SELECT * FROM user WHERE name = 'Tom';
SELECT * FROM user WHERE name = 'Tom' AND age = 20;

👉 使用復合索引時，必須從 最左字段開始連續使用。

---

5. 覆蓋索引 (Covering Index)

-- 索引: (user_id, create_time)
SELECT user_id, create_time FROM orders WHERE user_id = 10;

👉 查詢列全部在索引里，不需要回表，效率更高。

---

? 索引失效場景：不會走索引

1. LIKE '%abc' / %abc% （左模糊）

SELECT * FROM article WHERE title LIKE '%優化';
SELECT * FROM article WHERE title LIKE '%優化%';

👉 無法確定開頭位置，B+Tree 的有序性失效。

---

2. 索引列上做函數 / 運算

-- 函數
SELECT * FROM user WHERE YEAR(create_time) = 2024;-- 運算
SELECT * FROM user WHERE id + 1 = 10;

👉 MySQL 不能用索引里的有序值，只能逐行計算 → 全表掃描。

---

3. 隱式類型轉換

-- id 是 int，但傳了字符串
SELECT * FROM user WHERE id = '100';

👉 會觸發類型轉換，導致索引失效。

---

4. 使用 OR（混合索引列 vs 非索引列）

-- status 有索引，age 沒有
SELECT * FROM user WHERE status = 1 OR age = 20;

👉 部分條件沒索引 → 可能導致索引失效。

（解決辦法：把 OR 改成 UNION ALL）

---

5. 復合索引未遵循“最左前綴”

CREATE INDEX idx_name_age ON user(name, age);-- ? age 單獨用不了索引
SELECT * FROM user WHERE age = 20;

---

6. 使用 !=、<>、NOT IN、NOT LIKE

SELECT * FROM user WHERE status != 1;
SELECT * FROM user WHERE email NOT LIKE 'a%';

👉 索引失效，大概率全表掃描。

---

7. 在索引字段上用 IS NULL / IS NOT NULL

• IS NULL 可以走索引（但要看情況）。

• IS NOT NULL 基本走不了索引。

---

📊 總結表：索引能不能走

SQL 場景	是否走索引	說明
= / IN 精確匹配	?	最高效
范圍查詢（BETWEEN, <, >）	?	能利用索引有序性
LIKE 'abc%' 前綴匹配	?	從開頭開始匹配
LIKE '%abc' / %abc%	?	破壞有序性
復合索引（遵循最左前綴）	?	必須從最左列開始
索引列上函數 / 運算	?	索引失效
隱式類型轉換	?	字符串 vs 數字要注意
!=、<>、NOT IN	?	基本全表掃描
IS NULL	?(可能)	能用，效果視情況
IS NOT NULL	?	通常不走索引
覆蓋索引	?	查詢字段全在索引里

---

🌟 核心記憶法

👉 索引利用的關鍵：有序性

• 能從“開頭”精準定位 → ? 走索引

• 破壞有序性（函數、運算、模糊、NOT） → ? 索引失效

---