好的，我們來詳細(xì)解釋一下最左前綴匹配原則，并盡可能全面地列出典型的索引失效情況，每個情況都配上示例。

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

核心概念： 當(dāng)你在數(shù)據(jù)庫表上創(chuàng)建了一個復(fù)合索引（也叫聯(lián)合索引，包含多個列）時，這個索引可以被用來加速那些查詢條件只使用了該索引最左邊一個或連續(xù)多個列的查詢。它并不要求查詢條件必須包含索引中的所有列，但必須從最左邊的列開始，并且是連續(xù)的（不能跳過中間的列）。

類比理解： 想象一本電話簿，它首先按姓氏排序，姓氏相同再按名字排序。

• 查找“姓張的人”（只用最左列姓氏） - ? 索引有效（快速定位到所有張姓區(qū)域）
• 查找“姓張且名三的人”（用了連續(xù)的姓氏+名字） - ? 索引有效（在張姓區(qū)域內(nèi)快速找到張三）
• 查找“名叫三的人”（只用名字，跳過了姓氏） - ? 索引無效（必須掃描整本書，因為名字的排序只在同姓下有效）
• 查找“姓張且出生日期是某天的人”（用了姓氏，跳過了名字，用了出生日期） - ? 索引無效（在張姓區(qū)域內(nèi)，出生日期不是按索引排序的，除非索引包含了出生日期且名字條件用IS NULL或范圍覆蓋了所有可能名字，但這很特殊且通常低效）。

數(shù)據(jù)庫底層原理 (B+樹)： 復(fù)合索引在B+樹中存儲時，數(shù)據(jù)首先按索引定義的第一列排序，在第一列值相同的情況下，按第二列排序，以此類推。查詢時，數(shù)據(jù)庫只能有效地利用索引進(jìn)行查找，如果它能提供一個或多個索引列的值，并且這些值是從索引定義的最左邊開始的連續(xù)列。

關(guān)鍵點總結(jié)：

• 必須從最左列開始。
• 不能跳過中間的列。 （除非跳過的列在查詢條件中是IS NULL或使用了覆蓋索引等特定情況，但通常視為失效或效率降低）
• 可以只使用最左邊連續(xù)的若干列。
• 范圍查詢后的列無法使用索引排序或精確匹配。

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

假設(shè)我們有一個用戶表 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 失效，因為跳過了最左列 `country`
SELECT * FROM users WHERE created_at > '2023-01-01'; -- 索引 idx_age_created 失效，因為跳過了最左列 `age`

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

原因: 索引存儲的是列的原始值。對列應(yīng)用函數(shù)或表達(dá)式后，數(shù)據(jù)庫無法直接使用索引值進(jìn)行匹配，需要計算每一行的函數(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)行了計算，索引 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 連接的多個條件中，并非所有涉及的列都單獨建立了索引，數(shù)據(jù)庫通常無法有效合并索引掃描結(jié)果（除非優(yōu)化器選擇 Index Merge 策略，但這并非總是可行或高效），最終可能退化為全表掃描。

示例:

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

隱式類型轉(zhuǎn)換

原因: 當(dāng)查詢條件中的值類型與索引列定義的類型不匹配時，數(shù)據(jù)庫需要執(zhí)行隱式類型轉(zhuǎn)換。這相當(dāng)于在列上應(yīng)用了一個轉(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ù)，通常 *可能不會* 導(dǎo)致索引失效（因為轉(zhuǎn)換是確定性的且發(fā)生在常量端），但寫法不推薦且依賴數(shù)據(jù)庫實現(xiàn)。

使用 != 或 <> (不等于)

原因: 不等于操作符需要查找所有不等于特定值的行。對于非唯一索引或非主鍵索引，數(shù)據(jù)庫通常認(rèn)為掃描整個索引或全表掃描比利用索引定位再過濾掉大量數(shù)據(jù)更高效（除非不等于的值匹配了極少數(shù)行，且優(yōu)化器能識別）。

示例:

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 (對非稀疏索引)

原因: 標(biāo)準(zhǔn)B+樹索引通常不存儲 NULL 值（或?qū)⑵湟暈樘厥庵担?。查?IS NULL 時，如果索引不包含 NULL 記錄，則無法使用索引。查詢 IS NOT NULL 時，需要排除 NULL，這通常相當(dāng)于掃描所有非 NULL 值，優(yōu)化器可能認(rèn)為全表掃描更快。

注意： 有些數(shù)據(jù)庫（如 MySQL InnoDB）的二級索引是包含 NULL 值的，理論上 IS NULL 在特定條件下可能使用索引（如果 NULL 值很少），但 IS NOT NULL 通常仍會導(dǎo)致索引失效。

實踐中，通常認(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 失效 (兩個 %)

例外: LIKE 'xxx%' (后綴通配符) 通常可以有效利用索引，因為模式的開頭是固定的。

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

原因: 復(fù)合索引 (col1, col2)。當(dāng) col1 使用范圍查詢（>, <, BETWEEN）時，數(shù)據(jù)庫可以快速定位到 col1 滿足范圍的索引片段。但是，在這個片段內(nèi)部，col2 的值是無序的（索引只在 col1 相同的情況下才按 col2 排序）。因此，對于 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'; -- ? 對于 IN 內(nèi)的每個 country，city 條件有效。但整體效率取決于 IN 列表大小和優(yōu)化器。通常認(rèn)為部分有效。
SELECT * FROM users WHERE country > 'C' AND city = 'London'; -- ? `country` 是范圍查詢，`city` 條件無法利用索引排序和高效過濾。索引對 `city` 條件失效。
SELECT * FROM users WHERE age BETWEEN 20 AND 30 AND created_at = '2023-10-01'; -- ? `age` 是范圍查詢，`created_at` 條件無法利用索引。索引 idx_age_created 對 `created_at` 失效。

查詢列未被索引覆蓋且需要回表，優(yōu)化器判斷全表掃描更快

原因: 如果 SELECT * 或者查詢了不在索引中的列，數(shù)據(jù)庫即使使用了索引定位行，也需要根據(jù)索引中的指針（通常是主鍵值）回到主鍵索引（聚簇索引）或數(shù)據(jù)文件中取出完整的行數(shù)據(jù)（稱為 回表）。如果篩選條件過濾掉的行很少（即滿足條件的行數(shù)非常多），優(yōu)化器可能認(rèn)為直接掃描整個表（尤其是如果表很小或大部分?jǐn)?shù)據(jù)在內(nèi)存中）比通過索引查找再大量回表更高效。

示例:

-- 假設(shè)表很大，但 country='XX' 是一個非常大的國家，占表中大部分?jǐn)?shù)據(jù)
SELECT * FROM users WHERE country = 'XX'; -- 雖然有索引 idx_country_city, 但優(yōu)化器可能選擇全表掃描，避免大量回表操作。

索引列選擇性過低 (數(shù)據(jù)重復(fù)度過高)

原因: 如果索引列的值幾乎都一樣（例如 gender 列只有 'M'/'F'），那么使用這個索引篩選出的行數(shù)仍然非常多，數(shù)據(jù)庫優(yōu)化器可能會認(rèn)為使用索引帶來的好處（減少IO）不足以抵消額外的索引查找和可能的回表開銷，從而選擇全表掃描。

示例:

-- 假設(shè) country 列 90% 的值都是 'US'
SELECT * FROM users WHERE country = 'US'; -- 雖然有索引 idx_country_city, 但優(yōu)化器很可能選擇全表掃描。

使用 ORDER BY 的列與索引排序方式不一致

原因: 索引默認(rèn)是升序 (ASC) 存儲的。如果 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)化這個查詢。
SELECT * FROM users ORDER BY country ASC, city DESC; -- 索引 (country ASC, city ASC) 無法直接用于此混合排序。

統(tǒng)計信息過時

原因: 數(shù)據(jù)庫優(yōu)化器依賴表和索引的統(tǒng)計信息（如行數(shù)、不同值數(shù)量、數(shù)據(jù)分布直方圖）來估算不同執(zhí)行計劃的成本。如果這些統(tǒng)計信息沒有及時更新（例如在大量插入、刪除、更新后），優(yōu)化器可能會錯誤地估算使用索引的成本，從而選擇次優(yōu)計劃（如本應(yīng)使用索引卻選擇了全表掃描，或相反）。

示例: 沒有特定查詢示例，這是一個維護(hù)問題。需要定期運(yùn)行數(shù)據(jù)庫的 ANALYZE TABLE 或類似命令更新統(tǒng)計信息。

三、關(guān)鍵建議

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

通過理解最左前綴原則和這些典型的索引失效場景，你可以更有效地設(shè)計索引、編寫高效的SQL查詢，并診斷性能問題。

到此這篇關(guān)于MySQL中索引失效場景,原因以及解決方法介紹的文章就介紹到這了,更多相關(guān)MySQL索引失效內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論