MySQL索引失效

一血巍、索引失效

索引定義：索引的本質就是一種數(shù)據(jù)結構萧锉，通過某種數(shù)據(jù)結構能夠快速定位獲取到所需要的指定數(shù)據(jù)
索引失效：字面上其實已經解釋很清楚，磁盤上文件中的數(shù)據(jù)都是一條一條的述寡，當無法使用某種數(shù)據(jù)結構（索引）的時候柿隙，需要對文件中數(shù)據(jù)進行全部掃描才能得到所要求的的數(shù)據(jù)，因此鲫凶，索引失效不是我們所希望看到的禀崖，其會嚴重影響查詢性能

二、索引失效產生原因

原因分析：要知道索引失效產生原因螟炫，還需要從SQL語句來分析波附，這里就不追到MySQL底層來說，我們從執(zhí)行一條SQL語句來進行分析：

// 假設在activity表上建立了name字段的普通索引
select * from activity where name = 'jan';

表數(shù)據(jù)示例.png

這條SQL在執(zhí)行過程中會走索引昼钻，現(xiàn)在從以下幾點分析：
注：索引數(shù)據(jù)結構類型有Hash和B樹
索引類型有主鍵索引（Innodb存儲引擎默認）掸屡、普通索引、聯(lián)合索引然评、唯一索引和全文索引
其中SQL語句最后分號有否不影響

1仅财、從查詢條件分析：
（1）在建立普通索引前提下，查詢條件未使用任何索引字段碗淌，此時索引失效盏求，走全表掃描（但該情況似乎和本主題脫離，可忽略）
（2）在建立普通索引多條件前提下亿眠，若多條件下使用 or 運算碎罚，SQL語句后加 or device_id = 2，此時通過SQL執(zhí)行計劃可以看到該SQL未走索引

explain select * from activity where name = '234' or device_id = 2

執(zhí)行語句.png

其中纳像，type字段值為ALL魂莫，key字段值為空，說明索引失效
（3）在建立普通索引單條件前提下爹耗，SQL使用like（%開頭）字句

explain select * from activity where name like '%2';

image.png

可以發(fā)現(xiàn)未走索引耙考，索引失效
（4）在建立聯(lián)合索引（遵循最左原則）前提下，SQL查詢條件字段中未使用聯(lián)合索引第一個字段

// 創(chuàng)建了聯(lián)合索引：(name,device_id)潭兽，
//如果包含聯(lián)合索引第一字段且亂序倦始，SQL優(yōu)化器會進行重排成聯(lián)合索引順序
explain select * from activity where device_id = 2;

這種情況也會索引失效
（5）查詢條件對索引列運算（+、-山卦、*鞋邑、/、<>账蓉、or枚碗、in、exist）都會導致失效
（6）查詢條件中索引列使用 is not null

explain select * from activity where name is not null;

（7）查詢條件使用內部函數(shù)

explain select * from activity where LTRIM(name) = '234'

image.png

（8）還有一種可能因為粗心導致索引失效的情況铸本，查詢條件類型錯誤

explain select * from activity where name = 234

image.png

其中name字段類型為varchar肮雨，但賦值使用了數(shù)值類型

?著作權歸作者所有,轉載或內容合作請聯(lián)系作者