前幾天一個(gè)小伙伴說(shuō)面試可能掛了，他說(shuō)面試官問(wèn)他MySQL 索引失效的原因可能有哪些時(shí)，他腦袋一懵，竟然啥都沒(méi)說(shuō)出來(lái)，面試官的笑容給他留下了深刻的印象。

MySQL 索引在優(yōu)化查詢性能中扮演著關(guān)鍵角色，尤其是有聯(lián)查或者數(shù)據(jù)量大的情況，但是，一些操作或?qū)懛〞?huì)導(dǎo)致索引失效，索引失效，等于無(wú)用功了。

下面說(shuō)一下 MySQL 索引失效的幾種常見(jiàn)情況。

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

如果在查詢條件中對(duì)索引列使用了函數(shù)或表達(dá)式，MySQL 無(wú)法利用索引。

例如下面的語(yǔ)句，本來(lái)name列是有索引的

SELECT * FROM users WHERE LEFT(name, 3) = 'Tom';

但是，LEFT(name, 3) 對(duì) name 列進(jìn)行了函數(shù)操作，這就導(dǎo)致 MySQL 無(wú)法直接使用索引。

解決方法

第一種就是避免在索引列上使用函數(shù)，改為使用 like查詢，注意是后面加 %

SELECT * FROM users WHERE name LIKE 'Tom%';

第一種方式是針對(duì)普通索引來(lái)說(shuō)的，還有一種解決方法，那就是直接加前綴索引，例如

CREATE INDEX idx_name_prefix ON users (LEFT(name, 3));

前綴索引，一般用于長(zhǎng)文本列（用戶名、地址）、減少索引存儲(chǔ)空間、提高索引創(chuàng)建和查詢性能等場(chǎng)景。

查詢條件中使用隱式類型轉(zhuǎn)換

如果索引列和查詢條件的數(shù)據(jù)類型不一致，MySQL 會(huì)進(jìn)行隱式類型轉(zhuǎn)換，導(dǎo)致索引失效。

例如：

SELECT * FROM users WHERE phone_number = 1234567890;

如果 phone_number 列是 VARCHAR 類型，而查詢條件中的值是數(shù)字類型，MySQL 會(huì)將 phone_number 轉(zhuǎn)換為數(shù)字類型，導(dǎo)致索引失效。

解決方法 

確保查詢條件的數(shù)據(jù)類型與列類型一致：

SELECT * FROM users WHERE phone_number = '1234567890';

使用不等操作符

當(dāng)查詢條件中使用 <> 或 NOT IN，MySQL 無(wú)法高效利用索引。

例如

SELECT * FROM users WHERE age <> 30;

不等操作符會(huì)使查詢范圍不連續(xù)，MySQL 通常會(huì)選擇全表掃描。

解決方法

如果可以的話，建議嘗試調(diào)整查詢邏輯，例如

-- 優(yōu)化方案1：IN 列表
SELECT * FROM users WHERE status IN (2, 3, 4);

-- 優(yōu)化方案2：邏輯重構(gòu)
SELECT * FROM users WHERE status > 1 AND status < 5;

使用 OR 條件且未對(duì)所有列加索引

當(dāng) OR 條件連接的多個(gè)列中，并非所有列都有索引時(shí)，索引會(huì)失效。

例如下面語(yǔ)句，只要name和age列有至少一個(gè)沒(méi)有加索引，索引都不會(huì)被命中

SELECT * FROM users WHERE name = 'Tom' OR age = 30;

name 列有索引，而 age 列沒(méi)有索引，會(huì)導(dǎo)致全表掃描。

解決方法

確保 OR 條件的每一列都加上索引，或者改用 UNION或UNION ALL

SELECT * FROM users WHERE name = 'Tom'
UNION
SELECT * FROM users WHERE age = 30;

且 UNION和 UNION ALL也是有差別的，UNION去重，性能略低，UNION ALL不去重，性能更高

索引列在范圍查詢后再用其他條件篩選

在范圍查詢（如 <、>、BETWEEN、LIKE）后再對(duì)其他列篩選時(shí)，其他列的索引可能失效。

例如：

SELECT * FROM users WHERE age > 30 AND name = 'Tom';

MySQL 使用聯(lián)合索引時(shí)，范圍查詢會(huì)中斷索引的使用，后續(xù)的 name = 'Tom' 條件無(wú)法利用索引。

解決方法 調(diào)整索引順序或邏輯，確保查詢優(yōu)化。

設(shè)置聯(lián)合索引時(shí)，建議將區(qū)分度最高的列放在最左側(cè)，使用下列方法可以評(píng)估各個(gè)列的區(qū)分度大小

SELECT 
    COUNT(DISTINCT username) / COUNT(*) AS username_selectivity,
    COUNT(DISTINCT age) / COUNT(*) AS age_selectivity 
FROM users;

索引列的前綴未完全匹配

對(duì)于多列聯(lián)合索引，必須遵循最左前綴匹配規(guī)則，否則索引會(huì)部分或完全失效。

例如：

下面的語(yǔ)句，沒(méi)有遵守最左前綴原則，導(dǎo)致索引失效

CREATE INDEX idx_name_age ON users (name, age);
SELECT * FROM users WHERE age = 30; -- 索引失效

查詢條件未包含索引的第一列 name，無(wú)法觸發(fā)聯(lián)合索引。

解決方法

確保遵循最左前綴匹配規(guī)則：

SELECT * FROM users WHERE name = 'Tom' AND age = 30;

LIKE 模式中通配符使用不當(dāng)

前面也提到了這種情況，如果 LIKE 查詢以 % 開(kāi)頭，索引會(huì)失效。

例如：

SELECT * FROM users WHERE name LIKE '%Tom';

以 % 開(kāi)頭無(wú)法通過(guò)索引定位記錄。

解決方法

盡量避免以 % 開(kāi)頭，改為：

SELECT * FROM users WHERE name LIKE 'Tom%';

但，模糊查詢本身性能就不高，所以，如果有需要like查詢的情況，可以分析一下是否需要全文索引，也就是 FULLTEXT。

我之前修改過(guò)一個(gè)老系統(tǒng)中特別慢的接口，數(shù)據(jù)量夠大，而且在很多字段用了 like查詢，改用全文索引后，性能提升非常明顯，10倍以上。

-- 創(chuàng)建全文索引
CREATE FULLTEXT INDEX idx_fulltext ON users (username);

-- 全文搜索
SELECT * FROM users 
WHERE MATCH(username) AGAINST('張*' IN BOOLEAN MODE);

查詢結(jié)果集太小，優(yōu)化器選擇全表掃描

MySQL 的查詢優(yōu)化器會(huì)根據(jù)查詢成本決定是否使用索引。如果查詢結(jié)果集較小，MySQL 可能選擇全表掃描。

例如：

SELECT * FROM users WHERE is_active = 1;

如果 is_active = 1 的記錄占比非常高，MySQL 會(huì)認(rèn)為全表掃描更高效。

解決方法

這種情況下，索引可能無(wú)法優(yōu)化查詢性能。

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

MySQL 會(huì)根據(jù)統(tǒng)計(jì)信息決定索引使用與否。如果統(tǒng)計(jì)信息不準(zhǔn)確，可能導(dǎo)致索引失效。

索引的統(tǒng)計(jì)信息過(guò)期，影響查詢優(yōu)化器的決策。

解決方法

定期執(zhí)行 ANALYZE TABLE，調(diào)整 innodb_stats_persistent，以保持統(tǒng)計(jì)信息準(zhǔn)確。 手動(dòng)更新統(tǒng)計(jì)信息：

ANALYZE TABLE users;

IS NULL 和 IS NOT NULL 操作

在某些 MySQL 版本中，IS NULL 和 IS NOT NULL 條件會(huì)導(dǎo)致索引失效。

例如：

SELECT * FROM users WHERE age IS NOT NULL;

MySQL 無(wú)法直接通過(guò)索引處理 NULL 值。

解決方法

盡量避免大量 NULL 值，或改用其他標(biāo)識(shí)。

你學(xué)廢了了嗎？