mysql中什么情況會導致索引失效

這篇文章主要為大家展示了“mysql中什么情況會導致索引失效”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“mysql中什么情況會導致索引失效”這篇文章吧。

為了講解以下索引內容，我們先建立一個臨時的表 test02

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.最佳左前綴法則（重要）
全值匹配意思就是聯立的復合索引的順序和個數要和檢索的條件順序和個數相同。
最佳左前綴法則是指，如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列
下面我們給這個表建立一個復合索引

ALTER TABLE sys_user ADD INDEX idx_sys_user_nameAgePos(name,age,pos);

以下是我們的檢索語句：

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字段的索引失效了。

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在使用不等于(!= 或者<>)的時候無法使用索引會導致全表掃描
(!= 或者<>)通常會匹配到大量數據，當使用索引的花銷大于全表掃描時，mysql則會放棄使用索引而選擇全表掃描

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

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

7.is null，is not null 也無法使用索引
is null，is not null 通常會匹配到大量數據，當使用索引的花銷大于全表掃描時，mysql則會放棄使用索引而選擇全表掃描

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

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

（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 '%明%'

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

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

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

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

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

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

以上是“mysql中什么情況會導致索引失效”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！