MySQL索引是提升數(shù)據(jù)庫(kù)性能的關(guān)鍵因素，正確使用索引可以將查詢效率提高幾十倍甚至上百倍。

然而，在實(shí)際開發(fā)中，即使創(chuàng)建了索引，卻經(jīng)常出現(xiàn)索引不生效的情況，

本文將分享MySQL索引失效的12種典型場(chǎng)景，通過(guò)示例幫助開發(fā)者理解索引失效的原理，并掌握相應(yīng)的優(yōu)化方法。

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

這是最常見的索引失效場(chǎng)景之一，當(dāng)我們?cè)赪HERE子句中對(duì)索引列使用函數(shù)時(shí)，MySQL無(wú)法利用索引進(jìn)行查詢優(yōu)化。

問題示例

-- 創(chuàng)建索引
CREATE INDEX idx_create_time ON orders(create_time);

-- 以下查詢無(wú)法使用索引
SELECT * FROM orders WHERE YEAR(create_time) = 2023;

原理解釋

當(dāng)對(duì)索引列應(yīng)用函數(shù)時(shí)，MySQL必須對(duì)表中的每一行都應(yīng)用該函數(shù)，然后再與條件比較，這就導(dǎo)致了全表掃描。索引的B+樹結(jié)構(gòu)是基于列的原始值構(gòu)建的，而不是函數(shù)計(jì)算后的值。

解決方案

將函數(shù)應(yīng)用于條件值而不是列：

-- 優(yōu)化后的查詢，可以使用索引
SELECT * FROM orders 
WHERE create_time >= '2023-01-01 00:00:00' 
  AND create_time < '2024-01-01 00:00:00';

二、使用類型隱式轉(zhuǎn)換

當(dāng)條件中的值與索引列的類型不匹配時(shí)，MySQL會(huì)進(jìn)行隱式類型轉(zhuǎn)換，導(dǎo)致索引失效。

問題示例

-- 創(chuàng)建表和索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    phone VARCHAR(20),
    INDEX idx_phone (phone)
);

-- 以下查詢可能無(wú)法使用索引
SELECT * FROM users WHERE phone = 13800138000;

原理解釋

在上面的例子中，phone是VARCHAR類型，而條件值13800138000是整數(shù)。MySQL會(huì)將字符串類型的phone隱式轉(zhuǎn)換為數(shù)字類型進(jìn)行比較，導(dǎo)致無(wú)法使用索引。

解決方案

確保條件值與索引列類型一致：

-- 正確的查詢方式，可以使用索引
SELECT * FROM users WHERE phone = '13800138000';

三、使用不等于或不包含操作符

使用!=、<>、NOT IN、NOT LIKE等否定條件時(shí)，通常會(huì)導(dǎo)致索引失效。

問題示例

-- 創(chuàng)建索引
CREATE INDEX idx_status ON orders(status);

-- 以下查詢可能無(wú)法有效利用索引
SELECT * FROM orders WHERE status != 'completed';
SELECT * FROM orders WHERE status NOT IN ('completed', 'shipped');

原理解釋

MySQL的索引是為了快速查找滿足條件的記錄，而否定條件通常意味著要查找的范圍太大。MySQL優(yōu)化器可能判斷使用索引的代價(jià)大于全表掃描，因此選擇不使用索引。

解決方案

盡量使用肯定條件替代否定條件：

-- 優(yōu)化后的查詢
SELECT * FROM orders 
WHERE status IN ('pending', 'processing', 'cancelled');

如果必須使用否定條件，可以考慮重新設(shè)計(jì)索引或添加適當(dāng)?shù)慕y(tǒng)計(jì)信息幫助優(yōu)化器做出更好的決策。

四、使用OR操作符連接不同的索引列

當(dāng)使用OR連接多個(gè)條件，且這些條件分別在不同的索引上時(shí)，可能導(dǎo)致索引失效。

問題示例

-- 創(chuàng)建單列索引
CREATE INDEX idx_name ON customers(name);
CREATE INDEX idx_email ON customers(email);

-- 以下查詢可能無(wú)法充分利用索引
SELECT * FROM customers 
WHERE name = 'John' OR email = 'john@example.com';

原理解釋

MySQL在處理OR條件時(shí)，需要分別獲取滿足每個(gè)條件的記錄，然后合并結(jié)果。在某些情況下，優(yōu)化器會(huì)認(rèn)為這種操作的成本高于全表掃描，從而選擇不使用索引。

解決方案

1. 使用UNION替代OR：

-- 使用UNION優(yōu)化
SELECT * FROM customers WHERE name = 'John'
UNION
SELECT * FROM customers WHERE email = 'john@example.com';

2. 創(chuàng)建復(fù)合索引或使用索引合并：

-- 在MySQL 5.6及以上版本，可能會(huì)使用索引合并
-- 也可以創(chuàng)建覆蓋索引
CREATE INDEX idx_name_email ON customers(name, email);

五、使用LIKE操作符且以通配符開頭

當(dāng)使用LIKE操作符進(jìn)行模糊查詢，且模式以通配符（%）開頭時(shí)，索引通常會(huì)失效。

問題示例

-- 創(chuàng)建索引
CREATE INDEX idx_product_name ON products(product_name);

-- 以下查詢無(wú)法使用索引
SELECT * FROM products WHERE product_name LIKE '%phone%';

原理解釋

B+樹索引是按照索引列的值排序的，當(dāng)使用前綴通配符（如'%phone'）時(shí)，MySQL無(wú)法利用索引的有序性來(lái)定位數(shù)據(jù)，只能進(jìn)行全表掃描。

解決方案

1. 避免使用前綴通配符，改用后綴通配符：

-- 可以使用索引的查詢
SELECT * FROM products WHERE product_name LIKE 'phone%';

2. 對(duì)于必須使用前綴通配符的場(chǎng)景，考慮使用全文索引：

-- 創(chuàng)建全文索引
ALTER TABLE products ADD FULLTEXT INDEX ft_product_name(product_name);

-- 使用全文索引查詢
SELECT * FROM products 
WHERE MATCH(product_name) AGAINST('phone' IN BOOLEAN MODE);

3. 考慮使用專門的搜索引擎，如Elasticsearch。

六、對(duì)索引列進(jìn)行運(yùn)算

在WHERE子句中對(duì)索引列進(jìn)行算術(shù)運(yùn)算同樣會(huì)導(dǎo)致索引失效。

問題示例

-- 創(chuàng)建索引
CREATE INDEX idx_price ON products(price);

-- 以下查詢無(wú)法使用索引
SELECT * FROM products WHERE price + 100 > 500;

原理解釋

與函數(shù)使用類似，當(dāng)對(duì)索引列進(jìn)行運(yùn)算時(shí)，MySQL需要對(duì)表中的每一行數(shù)據(jù)都進(jìn)行計(jì)算，然后再與條件值比較，導(dǎo)致無(wú)法利用索引。

解決方案

將運(yùn)算應(yīng)用于條件值，而不是列：

-- 優(yōu)化后的查詢，可以使用索引
SELECT * FROM products WHERE price > 500 - 100;

七、查詢條件中的字段順序與復(fù)合索引的順序不一致

在使用復(fù)合索引（多列索引）時(shí)，如果查詢條件中的字段順序與索引創(chuàng)建時(shí)的順序不一致，可能導(dǎo)致索引無(wú)法充分利用。

問題示例

-- 創(chuàng)建復(fù)合索引
CREATE INDEX idx_name_age_city ON users(name, age, city);

-- 以下查詢無(wú)法充分利用索引
SELECT * FROM users WHERE age = 25 AND city = 'New York' AND name = 'John';

原理解釋

MySQL復(fù)合索引遵循"最左前綴"原則，即先按第一個(gè)索引列排序，值相同時(shí)再按第二個(gè)索引列排序，以此類推。當(dāng)查詢條件的順序與索引列順序不一致時(shí)，MySQL的查詢優(yōu)化器通常能夠重新排序這些條件，但在某些復(fù)雜場(chǎng)景下可能無(wú)法最優(yōu)化。

解決方案

在編寫查詢時(shí)，盡量保持條件順序與索引列順序一致：

-- 優(yōu)化后的查詢，更容易使用索引
SELECT * FROM users WHERE name = 'John' AND age = 25 AND city = 'New York';

另外，在設(shè)計(jì)復(fù)合索引時(shí)，應(yīng)將選擇性高（不重復(fù)值多）的列放在前面。

八、在WHERE子句中使用IS NULL或IS NOT NULL

在某些情況下，對(duì)索引列使用IS NULL或IS NOT NULL條件可能導(dǎo)致索引失效。

問題示例

-- 創(chuàng)建索引
CREATE INDEX idx_deleted_at ON users(deleted_at);

-- 以下查詢可能無(wú)法使用索引
SELECT * FROM users WHERE deleted_at IS NULL;

原理解釋

MySQL對(duì)NULL值的處理比較特殊。在早期版本中，MySQL對(duì)含有NULL值的列索引效果不佳，尤其是在使用IS NULL或IS NOT NULL條件時(shí)。不過(guò)，在MySQL 5.6及以后的版本中，這種情況有所改善。

解決方案

1. 在設(shè)計(jì)表時(shí)，盡量避免使用NULL值，可以使用空字符串或默認(rèn)值代替：

-- 創(chuàng)建表時(shí)使用NOT NULL約束和默認(rèn)值
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    status TINYINT NOT NULL DEFAULT 1
);

2. 如果必須查詢NULL值，檢查執(zhí)行計(jì)劃確保索引被正確使用：

EXPLAIN SELECT * FROM users WHERE deleted_at IS NULL;

九、查詢的數(shù)據(jù)占表中數(shù)據(jù)的比例較大

當(dāng)查詢條件返回的結(jié)果集占表總數(shù)據(jù)量的比例較大時(shí)，MySQL優(yōu)化器可能會(huì)選擇不使用索引，而是直接全表掃描。

問題示例

-- 創(chuàng)建索引
CREATE INDEX idx_gender ON users(gender);

-- 假設(shè)性別比例接近1:1，以下查詢可能不使用索引
SELECT * FROM users WHERE gender = 'male';

原理解釋

使用索引查詢涉及兩個(gè)步驟：先通過(guò)索引找到滿足條件的記錄ID，再通過(guò)這些ID獲取完整記錄（回表操作）。當(dāng)結(jié)果集較大時(shí)，這種"隨機(jī)IO"的成本可能高于順序讀取全表的成本，因此優(yōu)化器會(huì)選擇全表掃描。

解決方案

1. 增加更多的過(guò)濾條件，減小結(jié)果集：

-- 添加更多條件縮小結(jié)果集
SELECT * FROM users 
WHERE gender = 'male' AND age BETWEEN 25 AND 35;

2. 使用覆蓋索引避免回表：

-- 創(chuàng)建覆蓋索引
CREATE INDEX idx_gender_age_name ON users(gender, age, name);

-- 查詢僅需要索引中包含的列
SELECT gender, age, name FROM users WHERE gender = 'male';

十、索引字段的數(shù)據(jù)重復(fù)度過(guò)高

當(dāng)索引列的基數(shù)（不同值的數(shù)量）很低時(shí)，例如狀態(tài)字段只有幾個(gè)不同的值，MySQL可能會(huì)認(rèn)為使用索引效率低而選擇全表掃描。

問題示例

-- 創(chuàng)建索引
CREATE INDEX idx_status ON orders(status);

-- 假設(shè)status只有3個(gè)值：'pending', 'processing', 'completed'
-- 以下查詢可能不使用索引
SELECT * FROM orders WHERE status = 'completed';

原理解釋

索引的選擇性是指不同索引值與表中記錄總數(shù)的比值，選擇性越高（接近1），索引效率越高。對(duì)于低選擇性的列，使用索引可能需要訪問大量的索引頁(yè)和數(shù)據(jù)頁(yè)，效率不如全表掃描。

解決方案

1. 將低選擇性的列放在復(fù)合索引的后面：

-- 創(chuàng)建復(fù)合索引，將高選擇性的user_id放在前面
CREATE INDEX idx_user_status ON orders(user_id, status);
-- 查詢同時(shí)使用user_id和status
SELECT * FROM orders 
WHERE user_id = 10001 AND status = 'completed';

2. 考慮使用索引下推（Index Condition Pushdown，ICP）特性（MySQL 5.6及以上版本支持）。

十一、使用不等值范圍查詢

當(dāng)對(duì)索引列進(jìn)行范圍查詢（如>、<、BETWEEN）時(shí)，會(huì)部分影響索引的使用效率，尤其是在復(fù)合索引中。

問題示例

-- 創(chuàng)建復(fù)合索引
CREATE INDEX idx_age_salary ON employees(age, salary);

-- 以下查詢只能使用索引的age部分，salary部分無(wú)法使用
SELECT * FROM employees WHERE age > 30 AND salary > 50000;

原理解釋

在復(fù)合索引中，如果對(duì)前面的列使用了范圍條件，那么后面的列就無(wú)法使用索引了。這是因?yàn)锽+樹索引在范圍查詢后，無(wú)法再保證后續(xù)列的有序性。

解決方案

1. 調(diào)整索引列順序，將范圍查詢的列放在最后：

-- 調(diào)整索引順序
CREATE INDEX idx_salary_age ON employees(salary, age);

-- 如果條件中salary是等值查詢，age是范圍查詢
SELECT * FROM employees WHERE salary = 50000 AND age > 30;

2. 對(duì)于復(fù)雜條件，考慮創(chuàng)建多個(gè)索引：

-- 為不同的查詢模式創(chuàng)建不同的索引
CREATE INDEX idx_age ON employees(age);
CREATE INDEX idx_salary ON employees(salary);

十二、ORDER BY或GROUP BY子句的使用不當(dāng)

當(dāng)ORDER BY或GROUP BY的列與WHERE條件中使用的索引列不一致時(shí)，可能導(dǎo)致額外的排序操作，影響性能。

問題示例

-- 創(chuàng)建索引
CREATE INDEX idx_name ON users(name);

-- 以下查詢無(wú)法使用索引排序，會(huì)產(chǎn)生filesort
SELECT * FROM users WHERE name = 'John' ORDER BY create_time;

原理解釋

B+樹索引本身是有序的，如果排序或分組的列與索引列一致，MySQL可以直接利用索引的有序性。但如果不一致，MySQL需要在檢索出結(jié)果后再進(jìn)行排序（filesort），這是一個(gè)成本較高的操作。

解決方案

1. 創(chuàng)建包含排序/分組列的復(fù)合索引：

-- 創(chuàng)建包含排序列的復(fù)合索引
CREATE INDEX idx_name_create_time ON users(name, create_time);

-- 現(xiàn)在可以使用索引排序
SELECT * FROM users WHERE name = 'John' ORDER BY create_time;

2. 如果排序方向不一致，考慮使用降序索引（MySQL 8.0+支持）：

-- 創(chuàng)建包含混合排序方向的索引
CREATE INDEX idx_name_time_score ON users(name ASC, create_time DESC, score ASC);

-- 可以高效使用索引
SELECT * FROM users 
WHERE name = 'John' 
ORDER BY create_time DESC, score ASC;

如何診斷索引失效問題

發(fā)現(xiàn)并解決索引失效問題，需要掌握一些實(shí)用的診斷工具和方法：

1. 使用EXPLAIN分析查詢計(jì)劃

EXPLAIN是診斷索引使用情況的主要工具：

EXPLAIN SELECT * FROM orders WHERE customer_id = 1001 AND status = 'completed';

重點(diǎn)關(guān)注以下字段：

• • type: 從好到差依次是：system > const > eq_ref > ref > range > index > ALL
• • key: 實(shí)際使用的索引，如果為NULL則表示未使用索引
• • rows: 預(yù)計(jì)掃描的行數(shù)，數(shù)值越小越好
• • Extra: 額外信息，如"Using filesort"表示需要額外排序

2. 使用慢查詢?nèi)罩景l(fā)現(xiàn)問題SQL

配置并啟用MySQL慢查詢?nèi)罩荆?/p>

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 設(shè)置慢查詢閾值為1秒

3. 使用MySQL性能模式(Performance Schema)

MySQL 5.6及以上版本提供了更強(qiáng)大的性能監(jiān)控工具：

-- 查看查詢性能統(tǒng)計(jì)
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

4. 使用SHOW PROFILE分析查詢執(zhí)行情況

SET profiling = 1;
SELECT * FROM users WHERE email LIKE '%@example.com';
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

總結(jié)

索引優(yōu)化是一個(gè)持續(xù)的過(guò)程，需要結(jié)合具體的業(yè)務(wù)場(chǎng)景和數(shù)據(jù)特點(diǎn)。通過(guò)了解這些索引失效的場(chǎng)景和原理，你可以更有針對(duì)性地設(shè)計(jì)索引策略，顯著提升數(shù)據(jù)庫(kù)性能。

沒有一勞永逸的索引方案，隨著數(shù)據(jù)量的增長(zhǎng)和業(yè)務(wù)的變化，索引策略也需要不斷調(diào)整和優(yōu)化。

持續(xù)監(jiān)控、分析和優(yōu)化是保持高性能數(shù)據(jù)庫(kù)的關(guān)鍵。

到此這篇關(guān)于MySQL中索引失效的12種場(chǎng)景及對(duì)應(yīng)解決方案的文章就介紹到這了,更多相關(guān)MySQL索引失效解決內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！