MySQL索引失效原因及最左前綴匹配原則示例詳解

更新時(shí)間：2025年08月01日 08:49:32 作者：佛祖讓我來巡山

這篇文章主要介紹了MySQL索引失效原因及最左前綴匹配原則示例,通過理解最左前綴原則和這些典型的索引失效場(chǎng)景,你可以更有效地設(shè)計(jì)索引、編寫高效的SQL查詢,并診斷性能問題,需要的朋友可以參考下

一、最左前綴匹配原則 (Leftmost Prefix Rule)

核心概念： 當(dāng)你在數(shù)據(jù)庫表上創(chuàng)建了一個(gè)復(fù)合索引（也叫聯(lián)合索引，包含多個(gè)列）時(shí)，這個(gè)索引可以被用來加速那些查詢條件只使用了該索引最左邊一個(gè)或連續(xù)多個(gè)列的查詢。它并不要求查詢條件必須包含索引中的所有列，但必須從最左邊的列開始，并且是連續(xù)的（不能跳過中間的列）。
類比理解： 想象一本電話簿，它首先按姓氏排序，姓氏相同再按名字排序。
- 查找“姓張的人”（只用最左列姓氏） - ? 索引有效（快速定位到所有張姓區(qū)域）
- 查找“姓張且名三的人”（用了連續(xù)的姓氏+名字） - ? 索引有效（在張姓區(qū)域內(nèi)快速找到張三）
- 查找“名叫三的人”（只用名字，跳過了姓氏） - ? 索引無效（必須掃描整本書，因?yàn)槊值呐判蛑辉谕障掠行В?/li>
- 查找“姓張且出生日期是某天的人”（用了姓氏，跳過了名字，用了出生日期） - ? 索引無效（在張姓區(qū)域內(nèi)，出生日期不是按索引排序的，除非索引包含了出生日期且名字條件用IS NULL或范圍覆蓋了所有可能名字，但這很特殊且通常低效）。
數(shù)據(jù)庫底層原理 (B+樹)： 復(fù)合索引在B+樹中存儲(chǔ)時(shí)，數(shù)據(jù)首先按索引定義的第一列排序，在第一列值相同的情況下，按第二列排序，以此類推。查詢時(shí)，數(shù)據(jù)庫只能有效地利用索引進(jìn)行查找，如果它能提供一個(gè)或多個(gè)索引列的值，并且這些值是從索引定義的最左邊開始的連續(xù)列。
關(guān)鍵點(diǎn)總結(jié)：
1. 必須從最左列開始。
2. 不能跳過中間的列。 （除非跳過的列在查詢條件中是IS NULL或使用了覆蓋索引等特定情況，但通常視為失效或效率降低）
3. 可以只使用最左邊連續(xù)的若干列。
4. 范圍查詢后的列無法使用索引排序或精確匹配。

二、典型的索引失效情況與示例

假設(shè)我們有一個(gè)用戶表 users，并在其上創(chuàng)建了一些索引：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    age INT,
    country VARCHAR(50),
    city VARCHAR(50),
    created_at DATETIME NOT NULL,
    INDEX idx_username (username), -- 單列索引
    INDEX idx_country_city (country, city), -- 復(fù)合索引
    INDEX idx_age_created (age, created_at) -- 復(fù)合索引
);

典型索引失效情況

未遵循最左前綴原則 (跳過了最左列):

原因: 復(fù)合索引 (col1, col2, col3) 的排序依賴于 col1。跳過 col1 直接查詢 col2 或 col3，數(shù)據(jù)庫無法利用索引的有序性進(jìn)行快速定位。

示例:

SELECT * FROM users WHERE city = 'New York'; -- 索引 idx_country_city 失效，因?yàn)樘^了最左列 `country`
SELECT * FROM users WHERE created_at > '2023-01-01'; -- 索引 idx_age_created 失效，因?yàn)樘^了最左列 `age`

在索引列上使用函數(shù)或表達(dá)式:

原因: 索引存儲(chǔ)的是列的原始值。對(duì)列應(yīng)用函數(shù)或表達(dá)式后，數(shù)據(jù)庫無法直接使用索引值進(jìn)行匹配，需要計(jì)算每一行的函數(shù)結(jié)果后再比較。

示例:

SELECT * FROM users WHERE YEAR(created_at) = 2023; -- 在 created_at 上使用了 YEAR() 函數(shù)，任何包含 created_at 的索引都失效
SELECT * FROM users WHERE age + 1 > 30; -- 在 age 上進(jìn)行了計(jì)算，索引 idx_age_created 失效
SELECT * FROM users WHERE UPPER(username) = 'JOHNDOE'; -- 在 username 上使用了 UPPER() 函數(shù)，索引 idx_username 失效

在索引列上進(jìn)行運(yùn)算:
- 原因: 同函數(shù)一樣，運(yùn)算改變了列的原始值。
- 示例:
```
SELECT * FROM users WHERE id * 2 = 100; -- 在 id (主鍵索引) 上進(jìn)行了乘法運(yùn)算，索引失效
```

使用 OR 連接非索引列條件:

原因: 如果 OR 連接的多個(gè)條件中，并非所有涉及的列都單獨(dú)建立了索引，數(shù)據(jù)庫通常無法有效合并索引掃描結(jié)果（除非優(yōu)化器選擇 Index Merge 策略，但這并非總是可行或高效），最終可能退化為全表掃描。

示例:

SELECT * FROM users WHERE username = 'john' OR email = 'john@example.com';
-- 情況1: 只有 idx_username 存在，email 無索引 -> 索引 idx_username 對(duì) OR 條件整體失效（需全表掃）。
-- 情況2: 如果同時(shí)存在 idx_username 和 idx_email -> 優(yōu)化器 *可能* 使用 Index Merge 策略（如 union），此時(shí)兩個(gè)索引都可能有效。但這取決于數(shù)據(jù)庫優(yōu)化器選擇和版本。

隱式類型轉(zhuǎn)換:

原因: 當(dāng)查詢條件中的值類型與索引列定義的類型不匹配時(shí)，數(shù)據(jù)庫需要執(zhí)行隱式類型轉(zhuǎn)換。這相當(dāng)于在列上應(yīng)用了一個(gè)轉(zhuǎn)換函數(shù)，導(dǎo)致索引失效。

示例:

-- 假設(shè) phone 字段是 VARCHAR(20) 且有索引
SELECT * FROM users WHERE phone = 13800138000; -- 數(shù)字 13800138000 被隱式轉(zhuǎn)換為字符串，導(dǎo)致 phone 索引失效
-- 假設(shè) age 是 INT 且有索引
SELECT * FROM users WHERE age = '30'; -- 字符串 '30' 被隱式轉(zhuǎn)換為整數(shù)，通常 *可能不會(huì)* 導(dǎo)致索引失效（因?yàn)檗D(zhuǎn)換是確定性的且發(fā)生在常量端），但寫法不推薦且依賴數(shù)據(jù)庫實(shí)現(xiàn)。

使用 != 或 <> (不等于):
- 原因: 不等于操作符需要查找所有不等于特定值的行。對(duì)于非唯一索引或非主鍵索引，數(shù)據(jù)庫通常認(rèn)為掃描整個(gè)索引或全表掃描比利用索引定位再過濾掉大量數(shù)據(jù)更高效（除非不等于的值匹配了極少數(shù)行，且優(yōu)化器能識(shí)別）。
- 示例:
```
SELECT * FROM users WHERE username <> 'admin'; -- 索引 idx_username 很可能失效（除非 'admin' 是絕大多數(shù)行）
```
使用 NOT IN 或 NOT EXISTS:
- 原因: 類似于 !=，需要排除大量數(shù)據(jù)，優(yōu)化器傾向于全表掃描。
- 示例:
```
SELECT * FROM users WHERE country NOT IN ('US', 'UK'); -- 索引 idx_country_city 失效
```
使用 IS NULL 或 IS NOT NULL (對(duì)非稀疏索引):
- 原因: 標(biāo)準(zhǔn)B+樹索引通常不存儲(chǔ) NULL 值（或?qū)⑵湟暈樘厥庵担?。查?IS NULL 時(shí)，如果索引不包含 NULL 記錄，則無法使用索引。查詢 IS NOT NULL 時(shí)，需要排除 NULL，這通常相當(dāng)于掃描所有非 NULL 值，優(yōu)化器可能認(rèn)為全表掃描更快。注意： 有些數(shù)據(jù)庫（如 MySQL InnoDB）的二級(jí)索引是包含 NULL 值的，理論上 IS NULL 在特定條件下可能使用索引（如果 NULL 值很少），但 IS NOT NULL 通常仍會(huì)導(dǎo)致索引失效。實(shí)踐中，通常認(rèn)為兩者都可能導(dǎo)致索引失效。
- 示例:
```
SELECT * FROM users WHERE email IS NULL; -- 索引 (如果有在 email 上) 可能有效也可能無效，取決于數(shù)據(jù)庫和 NULL 比例
SELECT * FROM users WHERE email IS NOT NULL; -- 索引 (如果有在 email 上) 很可能失效
```
使用前導(dǎo)通配符的 LIKE (%xxx):
- 原因: 索引是按列值的完整內(nèi)容排序的。以通配符 % 開頭意味著模式的前綴是未知的，數(shù)據(jù)庫無法利用索引的有序性進(jìn)行快速定位（就像電話簿里找名字以 "son" 結(jié)尾的人一樣困難）。
- 示例:
```
SELECT * FROM users WHERE username LIKE '%doe'; -- 索引 idx_username 失效
SELECT * FROM users WHERE username LIKE '%john%'; -- 索引 idx_username 失效 (兩個(gè) %)
```
- 例外: LIKE 'xxx%' (后綴通配符) 通常可以有效利用索引，因?yàn)槟Ｊ降拈_頭是固定的。

復(fù)合索引中，第一列使用范圍查詢后，后續(xù)列索引失效:

原因: 復(fù)合索引 (col1, col2)。當(dāng) col1 使用范圍查詢（>, <, BETWEEN）時(shí)，數(shù)據(jù)庫可以快速定位到 col1 滿足范圍的索引片段。但是，在這個(gè)片段內(nèi)部，col2 的值是無序的（索引只在 col1 相同的情況下才按 col2 排序）。因此，對(duì)于 col2 的條件，數(shù)據(jù)庫無法利用索引進(jìn)行排序或高效的精確匹配/范圍掃描，通常需要在 col1 的范圍結(jié)果內(nèi)逐行掃描過濾 col2。

示例:

SELECT * FROM users WHERE country = 'US' AND city = 'New York'; -- ? 索引 (country, city) 有效 (等值+等值)
SELECT * FROM users WHERE country = 'US' AND city LIKE 'N%'; -- ? 索引有效 (等值+后綴通配符)
SELECT * FROM users WHERE country IN ('US', 'CA') AND city = 'Seattle'; -- ? 對(duì)于 IN 內(nèi)的每個(gè) country，city 條件有效。但整體效率取決于 IN 列表大小和優(yōu)化器。通常認(rèn)為部分有效。
SELECT * FROM users WHERE country > 'C' AND city = 'London'; -- ? `country` 是范圍查詢，`city` 條件無法利用索引排序和高效過濾。索引對(duì) `city` 條件失效。
SELECT * FROM users WHERE age BETWEEN 20 AND 30 AND created_at = '2023-10-01'; -- ? `age` 是范圍查詢，`created_at` 條件無法利用索引。索引 idx_age_created 對(duì) `created_at` 失效。

查詢列未被索引覆蓋且需要回表，優(yōu)化器判斷全表掃描更快:
- 原因: 如果 SELECT * 或者查詢了不在索引中的列，數(shù)據(jù)庫即使使用了索引定位行，也需要根據(jù)索引中的指針（通常是主鍵值）回到主鍵索引（聚簇索引）或數(shù)據(jù)文件中取出完整的行數(shù)據(jù)（稱為回表）。如果篩選條件過濾掉的行很少（即滿足條件的行數(shù)非常多），優(yōu)化器可能認(rèn)為直接掃描整個(gè)表（尤其是如果表很小或大部分?jǐn)?shù)據(jù)在內(nèi)存中）比通過索引查找再大量回表更高效。
- 示例:
```
-- 假設(shè)表很大，但 country='XX' 是一個(gè)非常大的國家，占表中大部分?jǐn)?shù)據(jù)
SELECT * FROM users WHERE country = 'XX'; -- 雖然有索引 idx_country_city, 但優(yōu)化器可能選擇全表掃描，避免大量回表操作。
```
索引列選擇性過低 (數(shù)據(jù)重復(fù)度過高):
- 原因: 如果索引列的值幾乎都一樣（例如 gender 列只有 'M'/'F'），那么使用這個(gè)索引篩選出的行數(shù)仍然非常多，數(shù)據(jù)庫優(yōu)化器可能會(huì)認(rèn)為使用索引帶來的好處（減少IO）不足以抵消額外的索引查找和可能的回表開銷，從而選擇全表掃描。
- 示例:
```
-- 假設(shè) country 列 90% 的值都是 'US'
SELECT * FROM users WHERE country = 'US'; -- 雖然有索引 idx_country_city, 但優(yōu)化器很可能選擇全表掃描。
```
使用 ORDER BY 的列與索引排序方式不一致:
- 原因: 索引默認(rèn)是升序 (ASC) 存儲(chǔ)的。如果 ORDER BY 子句使用了索引列，但是排序方向是降序 (DESC)，或者混合了升序降序（且與索引定義不一致），數(shù)據(jù)庫可能無法直接利用索引的有序性來避免額外的排序操作（filesort）。
- 示例:
```
SELECT * FROM users WHERE country = 'US' ORDER BY city DESC; -- 索引 (country, city ASC) 可能用于 WHERE, 但 ORDER BY city DESC 需要額外排序。
-- 創(chuàng)建索引 (country, city DESC) 可以優(yōu)化這個(gè)查詢。
SELECT * FROM users ORDER BY country ASC, city DESC; -- 索引 (country ASC, city ASC) 無法直接用于此混合排序。
```
統(tǒng)計(jì)信息過時(shí):
- 原因: 數(shù)據(jù)庫優(yōu)化器依賴表和索引的統(tǒng)計(jì)信息（如行數(shù)、不同值數(shù)量、數(shù)據(jù)分布直方圖）來估算不同執(zhí)行計(jì)劃的成本。如果這些統(tǒng)計(jì)信息沒有及時(shí)更新（例如在大量插入、刪除、更新后），優(yōu)化器可能會(huì)錯(cuò)誤地估算使用索引的成本，從而選擇次優(yōu)計(jì)劃（如本應(yīng)使用索引卻選擇了全表掃描，或相反）。
- 示例: 沒有特定查詢示例，這是一個(gè)維護(hù)問題。需要定期運(yùn)行數(shù)據(jù)庫的 ANALYZE TABLE 或類似命令更新統(tǒng)計(jì)信息。

關(guān)鍵建議

善用 EXPLAIN: 這是診斷查詢執(zhí)行計(jì)劃和索引使用情況的最重要工具。在你寫的 SQL 語句前加上 EXPLAIN (或 EXPLAIN ANALYZE)，分析輸出結(jié)果中的 key (使用的索引)、type (訪問類型，如 ref, range, index, ALL 表示全表掃描)、Extra (額外信息，如 Using where, Using filesort, Using index) 等字段。
設(shè)計(jì)合適的索引: 根據(jù)最頻繁的查詢模式（WHERE, JOIN, ORDER BY, GROUP BY）來設(shè)計(jì)索引，優(yōu)先考慮高選擇性的列，并遵循最左前綴原則。
考慮覆蓋索引: 如果查詢只需要訪問索引中包含的列，就可以避免回表操作，顯著提升性能 (EXPLAIN 的 Extra 列會(huì)顯示 Using index)。
避免過度索引: 索引會(huì)占用空間，并在數(shù)據(jù)插入、更新、刪除時(shí)帶來維護(hù)開銷。只為必要的查詢創(chuàng)建索引。
保持統(tǒng)計(jì)信息準(zhǔn)確: 定期更新表統(tǒng)計(jì)信息，讓優(yōu)化器做出更明智的決定。
理解數(shù)據(jù)庫特性: 不同數(shù)據(jù)庫管理系統(tǒng)（MySQL, PostgreSQL, SQL Server, Oracle）在索引實(shí)現(xiàn)和優(yōu)化器行為上可能存在細(xì)微差異，查閱官方文檔了解細(xì)節(jié)。