前幾天一個小伙伴說面試可能掛了，他說面試官問他MySQL 索引失效的原因可能有哪些時，他腦袋一懵，竟然啥都沒說出來，面試官的笑容給他留下了深刻的印象。

MySQL 索引在優化查詢性能中扮演著關鍵角色，尤其是有聯查或者數據量大的情況，但是，一些操作或寫法會導致索引失效，索引失效，等于無用功了。

下面說一下 MySQL 索引失效的幾種常見情況。

使用函數或表達式操作索引列

如果在查詢條件中對索引列使用了函數或表達式，MySQL 無法利用索引。

例如下面的語句，本來name列是有索引的

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

但是，LEFT(name, 3) 對 name 列進行了函數操作，這就導致 MySQL 無法直接使用索引。

解決方法

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

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

第一種方式是針對普通索引來說的，還有一種解決方法，那就是直接加前綴索引，例如

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

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

查詢條件中使用隱式類型轉換

如果索引列和查詢條件的數據類型不一致，MySQL 會進行隱式類型轉換，導致索引失效。

例如：

SELECT * FROM users WHERE phone_number = 1234567890;

如果 phone_number 列是 VARCHAR 類型，而查詢條件中的值是數字類型，MySQL 會將 phone_number 轉換為數字類型，導致索引失效。

解決方法 

確保查詢條件的數據類型與列類型一致：

SELECT * FROM users WHERE phone_number = '1234567890';

使用不等操作符

當查詢條件中使用 <> 或 NOT IN，MySQL 無法高效利用索引。

例如

SELECT * FROM users WHERE age <> 30;

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

解決方法

如果可以的話，建議嘗試調整查詢邏輯，例如

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

-- 優化方案2：邏輯重構
SELECT * FROM users WHERE status > 1 AND status < 5;

使用 OR 條件且未對所有列加索引

當 OR 條件連接的多個列中，并非所有列都有索引時，索引會失效。

例如下面語句，只要name和age列有至少一個沒有加索引，索引都不會被命中

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

name 列有索引，而 age 列沒有索引，會導致全表掃描。

解決方法

確保 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）后再對其他列篩選時，其他列的索引可能失效。

例如：

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

MySQL 使用聯合索引時，范圍查詢會中斷索引的使用，后續的 name = 'Tom' 條件無法利用索引。

解決方法 調整索引順序或邏輯，確保查詢優化。

設置聯合索引時，建議將區分度最高的列放在最左側，使用下列方法可以評估各個列的區分度大小

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

索引列的前綴未完全匹配

對于多列聯合索引，必須遵循最左前綴匹配規則，否則索引會部分或完全失效。

例如：

下面的語句，沒有遵守最左前綴原則，導致索引失效

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

查詢條件未包含索引的第一列 name，無法觸發聯合索引。

解決方法

確保遵循最左前綴匹配規則：

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

LIKE 模式中通配符使用不當

前面也提到了這種情況，如果 LIKE 查詢以 % 開頭，索引會失效。

例如：

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

以 % 開頭無法通過索引定位記錄。

解決方法

盡量避免以 % 開頭，改為：

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

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

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

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

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

查詢結果集太小，優化器選擇全表掃描

MySQL 的查詢優化器會根據查詢成本決定是否使用索引。如果查詢結果集較小，MySQL 可能選擇全表掃描。

例如：

SELECT * FROM users WHERE is_active = 1;

如果 is_active = 1 的記錄占比非常高，MySQL 會認為全表掃描更高效。

解決方法

這種情況下，索引可能無法優化查詢性能。

索引統計信息不準確

MySQL 會根據統計信息決定索引使用與否。如果統計信息不準確，可能導致索引失效。

索引的統計信息過期，影響查詢優化器的決策。

解決方法

定期執行 ANALYZE TABLE，調整 innodb_stats_persistent，以保持統計信息準確。 手動更新統計信息：

ANALYZE TABLE users;

IS NULL 和 IS NOT NULL 操作

在某些 MySQL 版本中，IS NULL 和 IS NOT NULL 條件會導致索引失效。

例如：

SELECT * FROM users WHERE age IS NOT NULL;

MySQL 無法直接通過索引處理 NULL 值。

解決方法

盡量避免大量 NULL 值，或改用其他標識。

你學廢了了嗎？