mysql索引失效的情況（索引失效的情況和解決）

2023-08-23 07:46:52

面試的時候被問到了：導致索引失效的原因有哪些？頓時啞口無言，平時不多注意，而支支吾吾的回答了幾點，雖然問題不大，但是表達還是欠缺。今天在這裡做一個總結，給自己長點記性。也可以當做是面試後總結的經驗與筆記吧，保證下次不再犯錯，同樣在開發過程中能注意到這些問題。希望也能幫助到大家！

下面結合一些示例來給大家講解

首先先新建臨時表,這個表有四個欄位主鍵、名字、年齡、職位

CREATE TABLE `sys_user` ( `id` varchar(64) NOT NULL COMMENT '主鍵', `name` varchar(64) DEFAULT NULL COMMENT '名字', `age` int(64) DEFAULT NULL COMMENT '年齡', `pos` varchar(64) DEFAULT NULL COMMENT '職位', PRIMARY KEY (`id`), KEY `idx_sys_user_nameAgePos` (`name`,`age`,`pos`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='使用者表';

1.全值匹配

全值匹配意思就是聯立的複合索引的順序和個數要和檢索的條件順序和個數相同。

2.最佳左字首法則（重要）
最佳左字首法則是指，如果索引了多列，要遵守最左字首法則。指的是查詢從索引的最左前列開始並且不跳過索引中的列

下面我們給這個表建立一個複合索引

SELECT * FROM sys_user WHERE name='小明' AND age = 22 AND pos ='java';

以下是我們的檢索語句：

SELECT * FROM sys_user WHERE name='小明' AND age = 22 AND pos ='java';

我們通過在檢索語句前面加關鍵字 EXLAIN，可以知道是否使用的索引

（1）EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age = 22 AND pos ='java'; （2）EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age = 22 ; （3）EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND pos ='java';

通過上面的結果我們可以知道，第一個複合索引的三個欄位我們都用了，第二個複合索引我們只用到兩個欄位，第三個複合索引我們只用到一個欄位。三個語句我們都用到索引，顯然第一種是最優的。

我們再看看哪種情況會失效：

（4）EXPLAIN SELECT * FROM sys_user WHERE age = 22; （5）EXPLAIN SELECT * FROM sys_user WHERE pos ='java'; （6）EXPLAIN SELECT * FROM sys_user WHERE age = 22 AND pos ='java';

以上三種情況都變成了全表掃描，原因是違反了最左左字首原則，因為複合索引最左邊的是name，當檢索條件name沒在前面索引將失效，第一種情況滿足了全值匹配，第二種滿足了兩個欄位name和age，第三種因為只滿足了name，所以索引只用到name。

3.不在索引列上做任何操作（計算、函式（自動或手動）型別轉換），會使索引失效轉為全表掃描

（7）EXPLAIN SELECT * FROM sys_user WHERE LEFT(name,1)='小明';

第七種情況失效是因為索引列做了計算或者函式的操作，導致了全表掃描。

4.儲存引擎不能使用索引中範圍條件右邊的列
可能大家關看上面的文字不知道是什麼意思，下面我們執行一下查詢語句就清楚了

（8）EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age < 22 AND pos ='java';

從上圖我們可以知道type變成了範圍級別，也就是說age<22之後的pos欄位的索引失效了。< p="">

5.儘量使用覆蓋索引（只訪問索引的查詢（索引列和查詢列一致），減少select * 的使用
查詢具體的欄位比查詢*效率更高，下面我們做一下對比

（9）EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age =22 AND pos ='java'; （10）EXPLAIN SELECT name,age,pos FROM sys_user WHERE name='小明' AND age =22 AND pos ='java';

6.mysql在使用不等於(!= 或者<>)的時候無法使用索引會導致全表掃描

（11）EXPLAIN SELECT * FROM sys_user WHERE name !='小明'

結果顯示索引失效導致了全表掃描

7.is null，is not null 也無法使用索引

（12）EXPLAIN SELECT * FROM sys_user WHERE name is not null

8.like以萬用字元開頭（’�c…’）mysql索引會失效變成全表掃描的操作，（%寫右邊則可以避免索引失效，如果業務實在需要’�c…%'則可以用覆蓋索引避免索引失效）

（13）EXPLAIN SELECT * FROM sys_user WHERE name like '%明%' （14）EXPLAIN SELECT * FROM sys_user WHERE name like '明%' （15）EXPLAIN SELECT name,age,pos FROM sys_user WHERE name like '%明%'

從上面的結果，第一種索引失效，第二種只寫右邊的%則可以避免索引失效，第三種如果業務實在需要‘�c…%’這種sql，則可以用覆蓋索引解決索引失效的問題

9.字串不加單引號索引會失效

（16）EXPLAIN SELECT * FROM sys_user WHERE name=222;

因為檢索字串是必須加單引號，上面用用了222是int型別，mysql在檢索的時候會判斷name是varchar的型別會將222轉換為’222’進行檢索，索引列發生了型別轉換，故索引失效。

10.少用or,用它連線時會索引失效

（17）EXPLAIN SELECT * FROM sys_user WHERE name='小明' or age = 22;

以上是本文的全部內容，希望對大家的學習有幫助，也希望大家多多支援php自學中心