在關(guān)系型數(shù)據(jù)庫里，索引是加速查詢的核心武器。索引失效不是“索引壞了”，而是優(yōu)化器沒有或不能利用索引的有序性來快速定位數(shù)據(jù)，最終選擇了全表掃描或更慢的執(zhí)行計(jì)劃。

理解索引失效的底層原理與常見場景，并掌握對(duì)應(yīng)的修復(fù)方法，能顯著提升 SQL 性能。

一、索引失效的底層邏輯（本質(zhì)與常見觸發(fā)原因）

在 B+ 樹索引結(jié)構(gòu)中，索引之所以能加速查詢，是因?yàn)樗饕I值在葉子節(jié)點(diǎn)中是有序排列的。優(yōu)化器之所以“走索引”，本質(zhì)是利用這種有序性做快速定位或范圍掃描。一旦查詢條件破壞了索引的順序性或匹配能力，索引就無法發(fā)揮作用。

主要失效原因包括：

排序鏈斷裂

• 當(dāng)查詢條件無法沿索引順序匹配時(shí)，B+ 樹無法快速定位數(shù)據(jù)區(qū)間。
• 例如聯(lián)合索引 (city, age, username)，查詢只用 age 或 username，起始節(jié)點(diǎn)無法定位。

值域跳躍

• 范圍查詢或函數(shù)運(yùn)算改變索引列的值域，索引無法連續(xù)掃描。
• 例如 YEAR(create_time) = 2025，索引存儲(chǔ)的是完整時(shí)間戳，無法匹配計(jì)算后的年份。

二次計(jì)算

• 查詢使用函數(shù)或表達(dá)式處理索引列，如 CAST()、LOWER()、+1 等，會(huì)破壞索引原始值匹配。

成本誤判

• 優(yōu)化器基于統(tǒng)計(jì)信息估算成本，當(dāng)索引選擇性低或數(shù)據(jù)分布變化時(shí)，可能放棄索引。
• 例如性別字段索引，男女比例 50:50，優(yōu)化器可能認(rèn)為全表掃描更快。

二、20 種典型索引失效場景

1. 違反最左前綴原則

業(yè)務(wù)表：user (id, city, age, username)

聯(lián)合索引：(city, age, username)

-- ? 錯(cuò)誤示例：缺失最左前綴 city，索引失效
SELECT * FROM user WHERE age = 25 AND username = 'Tom';

-- ? 正確示例：使用最左前綴 city
SELECT * FROM user WHERE city = 'Beijing' AND age = 25;

原因分析：

B+ 樹索引按 (city → age → username) 順序存儲(chǔ)。缺失最左列 city 時(shí)，無法找到索引起始節(jié)點(diǎn)，優(yōu)化器只能全表掃描。

2. 索引列參與運(yùn)算

業(yè)務(wù)表：orders (id, user_id, amount, create_time)

索引：create_time

-- ? 錯(cuò)誤示例
SELECT * FROM orders WHERE YEAR(create_time) = 2025;

-- ? 正確示例
SELECT * FROM orders WHERE create_time BETWEEN '2025-01-01' AND '2025-12-31';

原因分析：

B+ 樹存儲(chǔ)的是原始時(shí)間戳，計(jì)算后的年份無法匹配原始索引。范圍查詢能直接利用索引區(qū)間掃描，性能高。

3. 隱式類型轉(zhuǎn)換

業(yè)務(wù)表：product (id, price VARCHAR)

-- ? 錯(cuò)誤示例
SELECT * FROM product WHERE price = 100;

-- ? 正確示例
SELECT * FROM product WHERE price = '100';

原因分析：

類型不匹配觸發(fā)隱式轉(zhuǎn)換，相當(dāng)于函數(shù)運(yùn)算，索引失效。正確做法保證查詢值與列類型一致。

4. OR 連接非索引列

業(yè)務(wù)表：orders (user_id, status)

索引：user_id

-- ? 錯(cuò)誤示例
SELECT * FROM orders WHERE user_id = 10 OR status = 'PENDING';

-- ? 正確示例
SELECT * FROM orders WHERE user_id = 10
UNION
SELECT * FROM orders WHERE status = 'PENDING';

原因分析：

OR 條件中包含非索引列時(shí)，優(yōu)化器需掃描整個(gè)表驗(yàn)證條件，索引無法生效。拆分查詢或增加聯(lián)合索引可解決。

5. 范圍查詢阻斷索引

業(yè)務(wù)表：user (city, age, username)

聯(lián)合索引：(city, age, username)

-- ? 錯(cuò)誤示例
SELECT * FROM user WHERE city = 'Beijing' AND age > 25 AND username = 'Tom';

-- ? 正確示例
SELECT * FROM user WHERE city = 'Beijing' AND username = 'Tom' AND age > 25;

原因分析：

范圍查詢會(huì)阻斷索引后續(xù)列掃描，索引只能掃描 city → username 部分，age > 25 無法直接索引。

6. 不等于查詢

-- ? 錯(cuò)誤示例
SELECT * FROM user WHERE status != 'ACTIVE';

-- ? 正確示例
SELECT * FROM user WHERE status = 'INACTIVE';

原因分析：

不等于查詢涉及大部分?jǐn)?shù)據(jù)，優(yōu)化器選擇全表掃描，索引不再被使用。

7. LIKE 左模糊匹配

-- ? 錯(cuò)誤示例
SELECT * FROM product WHERE name LIKE '%Phone';

-- ? 正確示例
SELECT * FROM product WHERE name LIKE 'iPhone%';

原因分析：

B+ 樹只能從字符串前綴開始匹配，左模糊破壞順序，索引失效。

8. 索引選擇性過低

-- ? 錯(cuò)誤示例
SELECT * FROM user WHERE gender = 'M';

-- ? 正確示例
SELECT * FROM user WHERE id = 1001;

原因分析：

性別列重復(fù)值多，選擇性低，優(yōu)化器估算全表掃描成本更低。

9. ORDER BY 排序方向混亂

-- ? 錯(cuò)誤示例
SELECT * FROM user ORDER BY city ASC, age DESC;

-- ? 正確示例
SELECT * FROM user ORDER BY city ASC, age ASC;

原因分析：

B+ 樹索引順序?yàn)?city ASC, age ASC，與查詢排序方向不一致，索引無法直接排序。

10. 使用 NOT IN

-- ? 錯(cuò)誤示例
SELECT * FROM orders WHERE id NOT IN (1,2,3);

-- ? 正確示例
SELECT o.* FROM orders o
LEFT JOIN (SELECT 1 AS id UNION ALL SELECT 2 UNION ALL SELECT 3) t
ON o.id = t.id
WHERE t.id IS NULL;

原因分析：

NOT IN 等價(jià)于多個(gè)不等式，需要全表掃描。通過 LEFT JOIN + IS NULL 可利用索引。

11. 多表 JOIN 字符集不一致

-- ? 錯(cuò)誤示例
SELECT u.id, o.id FROM user u JOIN orders o ON u.name = o.username;

-- ? 正確示例
ALTER TABLE orders CONVERT TO CHARACTER SET utf8;

原因分析：

字符集不同觸發(fā)轉(zhuǎn)換，索引無法匹配，查詢效率低。

12. 使用函數(shù)處理索引列

-- ? 錯(cuò)誤示例
SELECT * FROM user WHERE LOWER(username) = 'alice';

-- ? 正確示例
SELECT * FROM user WHERE username = 'Alice';

原因分析：

函數(shù)會(huì)破壞索引原始值匹配，導(dǎo)致全表掃描。

13. 使用變量表達(dá)式類型不一致

SET @uid := '1001';
-- ? 錯(cuò)誤示例
SELECT * FROM orders WHERE user_id = @uid;

-- ? 正確示例
SET @uid := 1001;
SELECT * FROM orders WHERE user_id = @uid;

原因分析：

變量類型與列類型不一致，觸發(fā)隱式類型轉(zhuǎn)換，索引失效。

14. 索引列存在 NULL

-- ? 錯(cuò)誤示例
SELECT * FROM user WHERE city IS NULL;

-- ? 正確示例
SELECT * FROM user WHERE city = 'Beijing';

原因分析：

NULL 值在索引中存儲(chǔ)特殊，可能不走索引。

15. 分頁深度過大

-- ? 錯(cuò)誤示例
SELECT * FROM orders ORDER BY create_time LIMIT 100000, 10;

-- ? 正確示例
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

原因分析：

深度分頁 OFFSET 大時(shí)，優(yōu)化器可能放棄索引，掃描大量無效行。

16. MATCH AGAINST 全文索引與普通索引混用

-- ? 錯(cuò)誤示例
SELECT * FROM product WHERE MATCH(name) AGAINST('Phone') AND category='Electronics';

-- ? 正確示例
SELECT * FROM product WHERE category='Electronics' AND id IN (
    SELECT id FROM product WHERE MATCH(name) AGAINST('Phone')
);

原因分析：

全文索引使用不同算法，與 B+ 樹索引不兼容，需要分步查詢。

17. 強(qiáng)制類型轉(zhuǎn)換

-- ? 錯(cuò)誤示例
SELECT * FROM orders WHERE CAST(id AS CHAR) = '1001';

-- ? 正確示例
SELECT * FROM orders WHERE id = 1001;

原因分析：

顯式轉(zhuǎn)換破壞索引原始存儲(chǔ)順序，優(yōu)化器無法利用索引。

18. 統(tǒng)計(jì)信息不準(zhǔn)確

-- ? 錯(cuò)誤示例
-- 數(shù)據(jù)量變化大，但未更新統(tǒng)計(jì)信息
SELECT * FROM orders WHERE user_id = 1001;

-- ? 正確示例
ANALYZE TABLE orders;
SELECT * FROM orders WHERE user_id = 1001;

原因分析：

優(yōu)化器基于統(tǒng)計(jì)信息判斷成本，過時(shí)統(tǒng)計(jì)信息可能導(dǎo)致索引被放棄。

19. 使用派生表

-- ? 錯(cuò)誤示例
SELECT * FROM (SELECT * FROM orders) t WHERE user_id = 1001;

-- ? 正確示例
SELECT * FROM orders WHERE user_id = 1001;

原因分析：

派生表會(huì)生成臨時(shí)表，索引下推失效。

20. 索引合并效率低下

-- ? 錯(cuò)誤示例
SELECT * FROM orders WHERE user_id = 1001 AND status = 'PENDING';

-- ? 正確示例
-- 建立聯(lián)合索引 (user_id, status)
CREATE INDEX idx_user_status ON orders(user_id, status);
SELECT * FROM orders WHERE user_id = 1001 AND status = 'PENDING';

原因分析：

MySQL 對(duì)多個(gè)單列索引進(jìn)行合并可能效率低于全表掃描，聯(lián)合索引可提高效率。

三、索引優(yōu)化黃金策略

索引優(yōu)化不僅是避免失效，還要保證查詢的可維護(hù)性和執(zhí)行效率。提出 5C 原則，并加入 實(shí)際操作技巧，讓優(yōu)化更可執(zhí)行。

1. Complete Coverage（完整覆蓋）

目標(biāo)：保證查詢條件包含索引最左前綴。

說明：

• 聯(lián)合索引 (a,b,c)，必須從 a 開始使用索引，否則 B+ 樹無法快速定位起始節(jié)點(diǎn)。
• 對(duì)于業(yè)務(wù)查詢 WHERE b=2 AND c=3，索引完全失效。

優(yōu)化方法：

• 改寫查詢：WHERE a=1 AND b=2 AND c=3
• 對(duì)高頻查詢列設(shè)計(jì)最左前綴列

示例：

-- 聯(lián)合索引 city, age, username
SELECT * FROM user WHERE city='Beijing' AND age=25;

2. Clean Calculation（避免計(jì)算）

目標(biāo)：索引列不使用運(yùn)算或函數(shù)。

說明：

• B+ 樹存儲(chǔ)的是原始值，YEAR(create_time) 或 amount+10 會(huì)破壞索引匹配。

優(yōu)化方法：

• 使用范圍查詢替代函數(shù)
• 預(yù)計(jì)算字段或生成列（generated column）

示例：

-- 錯(cuò)誤
SELECT * FROM orders WHERE YEAR(create_time)=2023;

-- 正確
SELECT * FROM orders WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

3. Consistent Type（類型一致）

目標(biāo)：保證查詢值類型與列類型一致。

說明：

• 隱式類型轉(zhuǎn)換會(huì)觸發(fā)函數(shù)運(yùn)算，導(dǎo)致索引失效。
• 常見場景：VARCHAR 字段用數(shù)字查詢，或整數(shù)列用字符串查詢。

優(yōu)化方法：

• 查詢值與列類型嚴(yán)格一致
• 對(duì)動(dòng)態(tài)變量進(jìn)行類型檢查

示例：

-- 錯(cuò)誤
SELECT * FROM product WHERE price=100; -- price VARCHAR

-- 正確
SELECT * FROM product WHERE price='100';

4. Controlled Range（控制范圍）

目標(biāo)：范圍查詢放在聯(lián)合索引最右側(cè)。

說明：

• 范圍查詢?nèi)?>、<、BETWEEN 會(huì)阻斷索引后續(xù)列掃描。
• 例如 (city, age, username) 索引，條件 city='Beijing' AND age>25 AND username='Tom'，age>25 阻斷 username 索引。

優(yōu)化方法：

• 將范圍查詢放在聯(lián)合索引最右
• 對(duì)于復(fù)雜查詢，可拆分查詢或添加額外索引

示例：

SELECT * FROM user WHERE city='Beijing' AND username='Tom' AND age>25;

5. Cost Consideration（成本考量）

目標(biāo)：在選擇索引時(shí)考慮選擇性和全表掃描成本。

說明：

低選擇性索引可能導(dǎo)致優(yōu)化器選擇全表掃描。

判斷標(biāo)準(zhǔn)：COUNT(DISTINCT col)/COUNT(*) < 0.2，慎用索引

什么是低選擇性列

• 選擇性（Selectivity） = 不同值的數(shù)量 / 總行數(shù)
• 低選擇性列：不同值少，比如性別字段 gender（只有 男/女 兩種），啟用狀態(tài)字段 is_enabled（通常 0/1）
• 高選擇性列：不同值多，比如身份證號(hào)、手機(jī)號(hào)、訂單號(hào)

為什么低選擇性列建索引意義不大

• 假設(shè)表 user 有 1000 萬條記錄：

gender 字段索引：

• 男 = 500 萬條，女 = 500 萬條 * 查詢 WHERE gender='男' 時(shí)，索引只幫助定位到 500
  萬條記錄 * 實(shí)際掃描行數(shù)幾乎與全表掃描相同

優(yōu)化器會(huì)判斷：使用索引的成本 > 全表掃描成本

• 結(jié)果是可能不會(huì)走索引 * 建索引浪費(fèi)空間和維護(hù)成本（插入/更新時(shí)索引需要維護(hù)）

6. 擴(kuò)展優(yōu)化策略

1. 覆蓋索引：查詢字段全部包含在索引中，可避免訪問表數(shù)據(jù)。

2. 分區(qū)索引：大表可以通過分區(qū)減少全表掃描范圍。

3. 深度分頁優(yōu)化：使用 id > last_id 替代大 OFFSET。

4. 索引 NULL 處理：盡量避免索引列包含大量 NULL 值，或者為 NULL 建專門索引。

四、實(shí)戰(zhàn)檢測工具

1. EXPLAIN 分析

EXPLAIN SELECT * FROM orders WHERE user_id=1001;

type 列：

• ref/range：有效索引
• ALL：全表掃描，索引失效

key 列：顯示使用的索引名稱

rows 列：掃描行數(shù)，可判斷優(yōu)化效果

示例：

id | select_type | table  | type | key       | rows
1  | SIMPLE      | orders | ref  | idx_user  | 10

說明索引 idx_user 有效，掃描 10 行。

2. 優(yōu)化器追蹤

SET optimizer_trace="enabled=on";
SELECT * FROM orders WHERE user_id=1001;
SELECT * FROM information_schema.optimizer_trace;

• 可查看優(yōu)化器決策過程
• 追蹤索引使用、JOIN 順序、條件下推等細(xì)節(jié)
• 有助于排查索引失效根因

3. 索引使用分析

SHOW INDEX FROM orders;

• 查看表索引結(jié)構(gòu)、列順序、唯一性
• 確認(rèn)是否存在聯(lián)合索引和覆蓋索引

示例：

4. 性能對(duì)比驗(yàn)證

• 建立測試表，執(zhí)行錯(cuò)誤和優(yōu)化查詢
• 使用 SELECT SQL_NO_CACHE ... 避免緩存影響
• 記錄查詢耗時(shí)、掃描行數(shù)

示例：

-- 錯(cuò)誤查詢
SELECT * FROM orders WHERE YEAR(create_time)=2025;  -- 掃描 100000 行，耗時(shí) 500ms

-- 優(yōu)化查詢
SELECT * FROM orders WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';  -- 掃描 500 行，耗時(shí) 2ms

5. 定期維護(hù)

1. ANALYZE TABLE：更新統(tǒng)計(jì)信息
2. OPTIMIZE TABLE：清理碎片，提高索引掃描效率
3. 監(jiān)控慢查詢?nèi)罩?/strong>：發(fā)現(xiàn)索引失效和全表掃描