下面有一些培訓(xùn)教學機構(gòu)的口訣和我個人的一些總結(jié)：
為了講解以下索引內(nèi)容，我們先建立一個臨時的表 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.最佳左前綴法則（重要）
全值匹配意思就是聯(lián)立的復(fù)合索引的順序和個數(shù)要和檢索的條件順序和個數(shù)相同。
最佳左前綴法則是指，如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列
下面我們給這個表建立一個復(fù)合索引

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';

我們通過在檢索語句前面加關(guān)鍵字 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';

通過展示的結(jié)果我們可以知道，第一個復(fù)合索引的三個字段我們都用了，第二個復(fù)合索引我們只用到兩個字段，第三個復(fù)合索引我們只用到一個字段。三個語句我們都用到索引，顯然第一種是最優(yōu)的。

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

（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';

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

3.不在索引列上做任何操作（計算、函數(shù)（自動或手動）類型轉(zhuǎn)換），會使索引失效轉(zhuǎn)為全表掃描

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

第七種情況失效是因為索引列做了計算或者函數(shù)的操作，導(dǎo)致了全表掃描。

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

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

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

結(jié)果顯示索引失效導(dǎo)致了全表掃描

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

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

8.like以通配符開頭（'%abc…'）mysql索引會失效變成全表掃描的操作，（%寫右邊則可以避免索引失效，如果業(yè)務(wù)實在需要'%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 '%明%'

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

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

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

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

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

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

到此這篇關(guān)于淺談mysql哪些情況會導(dǎo)致索引失效的文章就介紹到這了,更多相關(guān)mysql 索引失效內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論