淺談mysql哪些情況會導致索引失效

Posted on by

下面有一些培訓教學機構的口訣和我個人的一些總結：
為瞭講解以下索引內容，我們先建立一個臨時的表 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哪些情況會導致索引失效的文章就介紹到這瞭,更多相關mysql 索引失效內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：