結(jié)論

具體案例下文有詳盡描述

不適合建立索引的場(chǎng)景：

• 數(shù)據(jù)量比較小的表不建議建立索引
• 有大量重復(fù)數(shù)據(jù)的字段上不建議建立索引（類似：性別字段）
• 需要進(jìn)行頻繁更新的表不建議建立索引
• where、group by、order by后面的沒有使用到的字段不建立索引
• 不要定義冗余索引

索引失效的場(chǎng)景：

• 過濾條件使用不等于(!=、<>)
• 過濾條件使用is not null
• 在索引字段上使用函數(shù)或進(jìn)行計(jì)算
• 在使用聯(lián)合索引的時(shí)候，需要滿足“最佳左前綴法則”，否則失效
• 當(dāng)使用了類型轉(zhuǎn)換也會(huì)導(dǎo)致索引失效
• 在使用范圍查詢的時(shí)候，聯(lián)合索引的部分字段失效(where age >18)
• 在like字段中，如果是以%開頭，索引失效(where name like ‘%abc’)
• 在使用or進(jìn)行查詢的時(shí)候，or前后出現(xiàn)非索引字段，索引失效
• 表和庫的字符集不一致，回導(dǎo)致索引失效

知識(shí)點(diǎn)：

• 每張表的索引不建議超過6個(gè)（占用空間、降低表更新速度）
• 最終到底是否使用索引還是優(yōu)化器進(jìn)行決定的
• 優(yōu)化器會(huì)根據(jù)數(shù)據(jù)量、數(shù)據(jù)庫版本、數(shù)據(jù)選擇讀進(jìn)行查詢代價(jià)的比較，從而決定是否使用索引
• 建立索引的時(shí)候?qū)⑿枰秶ヅ涞淖侄谓⒃谒饕奈膊?，避免失?/li>
• 在建立表的時(shí)候?qū)⒆侄卧O(shè)置為not null同時(shí)設(shè)置默認(rèn)值，當(dāng)需要查找沒有值的記錄的時(shí)候就可以使用where xxx = 默認(rèn)值，放置使用is not null導(dǎo)致索引失效
• 頁面搜索的時(shí)候嚴(yán)謹(jǐn)左模糊或者全模糊(like ‘%abc’)
• 對(duì)于過濾性較好的字段建立在聯(lián)合索引的前面，這樣就可以優(yōu)先過濾比較多的數(shù)據(jù)

不建議建立索引的場(chǎng)景

場(chǎng)景一：數(shù)據(jù)少的表

當(dāng)數(shù)據(jù)比較少的時(shí)候，索引的優(yōu)勢(shì)就不明顯了，因?yàn)閿?shù)據(jù)庫的存儲(chǔ)引擎也是非常快的，相較于需要查詢索引在進(jìn)行回表操作，可能直接查詢的性能會(huì)更高一些，所以數(shù)據(jù)相對(duì)較少的表不建議建立索引

場(chǎng)景二：有大量重復(fù)數(shù)據(jù)的字段

類似于性別字段，只有“男”和“女”兩個(gè)不同的值，所以索引一半的數(shù)據(jù)是“男”一半的數(shù)據(jù)是“女”，那么建立索引并不能進(jìn)行快速的查詢等，所以不建議在有大量重復(fù)數(shù)據(jù)的列上建立索引

場(chǎng)景三：頻繁更新的表（update/delete/insert）

因?yàn)楸碇懈聰?shù)據(jù)的時(shí)候，索引也是需要進(jìn)行對(duì)應(yīng)的維護(hù)的，如果一個(gè)表近期需要頻繁的進(jìn)行增刪改操作，那么就需要耗費(fèi)大量的時(shí)間去維護(hù)索引，不建議建立索引，可以在需要進(jìn)行頻繁的更新操作的時(shí)候?qū)⑺饕齽h除，更新完畢之后重建索引

場(chǎng)景四：沒有使用的字段（where/group by/order by）

不是where/group by/order by后面的字段沒有必要建立索引，因?yàn)椴粫?huì)使用到該索引

場(chǎng)景五：不要定義冗余索引

create index username_password_address on xiao(username,password,address);
-- 如果建立了第一個(gè)索引，那么就沒有必要建立第二個(gè)索引
create index username on xiao (username);
--第二個(gè)索引就是冗余索引，因?yàn)榈谝粋€(gè)已經(jīng)是先根據(jù)username排序的索引
--也就是第二個(gè)索引的功能完全可以由第一個(gè)索引實(shí)現(xiàn)

這里因?yàn)閡sername作為第一個(gè)聯(lián)合索引的第一個(gè)字段，所以索引就是按照username進(jìn)行排序，在username相同的情況下按照password、address排序，所以也就是實(shí)現(xiàn)了單獨(dú)拿username列作為索引的功能，即第二個(gè)索引就是多余的

索引失效的場(chǎng)景

場(chǎng)景一：在建立索引的字段上進(jìn)行運(yùn)算(函數(shù)等)，導(dǎo)致索引失效

這里首先是給age創(chuàng)建了索引，在第一次查詢過程中使用了age索引，但是第二次key值為null（索引失效），導(dǎo)致索引失效的原因在于第二次查詢的時(shí)候where后面對(duì)age進(jìn)行了計(jì)算，計(jì)算機(jī)并不知道執(zhí)行的是什么計(jì)算所以會(huì)將age+1計(jì)算后與1比較，索引失效

類似于在字段上使用函數(shù)concat()等都會(huì)導(dǎo)致索引失效

場(chǎng)景二：使用不等于(where age != 18)

當(dāng)使用等值運(yùn)算，那么是可以在索引中進(jìn)行查找的，但是如果是不等于，那么則需要遍歷所有數(shù)據(jù)，所以所失效

explain select * from xiaoyuanhao where age = 18;
explain select * from xiaoyuanhao where age != 18;
--這里是在age字段上建立了普通索引，第二個(gè)查詢時(shí)候索引失效

場(chǎng)景三：使用is not null索引失效

與不等于一樣，如果使用的是is not null，那么就需要進(jìn)行全部數(shù)據(jù)的遍歷操作，索引失效，但是如果使用的是is null那么依舊是可以使用索引的

--這里是在age字段上建立了普通索引，第二個(gè)查詢時(shí)候索引失效
explain select * from xiaoyuanhao where age is null;
--可以正常使用索引
explain select * from xiaoyuanhao where age is not null;
--索引失效

場(chǎng)景四：在使用聯(lián)合索引的時(shí)候沒有遵循最佳左前綴法則

CREATE INDEX age_classid_name ON student(age,classId,NAME);
EXPLAIN SELECT * FROM student WHERE classId = 30 AND NAME = 'xiaoyuanhao';
-- 因?yàn)闆]有使用age字段，所以沒有準(zhǔn)許最佳左前綴原則，索引失效

從這里可以看出是沒有使用索引的(key = null)，因?yàn)閯?chuàng)建的索引是先按照age進(jìn)行排序，在age相同的情況下按照classId和name排序，如果在查詢的時(shí)候需要直接按照classId進(jìn)行排序查找，那么就無法使用該索引，即索引失效。

如果需要使用使用索引，那么就一定需要到聯(lián)合索引的第一個(gè)字段age，案例如下

EXPLAIN SELECT * FROM student WHERE age = 10 AND NAME = 'xiaoyuanhao';
EXPLAIN SELECT * FROM student WHERE age = 10 AND classId = 33 AND NAME = 'xiaoyuanhao';
--兩者都是使用age字段索引，所以索引有效

場(chǎng)景五：類型轉(zhuǎn)換導(dǎo)致索引失效

CREATE INDEX NAME ON student(NAME);
-- 這里的name字段是varchar類型
EXPLAIN SELECT * FROM student WHERE NAME = 'xiao';
-- 本次查詢是可以使用索引的，因?yàn)轭愋投际且恢碌?，都是字符?
EXPLAIN SELECT * FROM student WHERE NAME = 123;
-- 本次查詢則無法使用索引，因?yàn)槭菍?shù)字類型123轉(zhuǎn)換為字符類型

沒有發(fā)生類型轉(zhuǎn)換，使用索引key = name

發(fā)生了類型轉(zhuǎn)換，無法使用索引kye = null，索引失效

使用索引的時(shí)候一定需要保證數(shù)據(jù)類型是一致的，否則系統(tǒng)就需要進(jìn)行轉(zhuǎn)換，那么就無法使用索引

場(chǎng)景六：使用范圍查詢導(dǎo)致聯(lián)合索引其他字段失效

create index age_classId_name on student (age,classId,name);
EXPLAIN SELECT * FROM student WHERE age = 10 AND classId > 20 AND NAME = 'xiaoyuanhao';
-- 這里只能使用age,classId，索引的前兩個(gè)字段
EXPLAIN SELECT * FROM student WHERE age = 10 AND classId = 20 AND NAME = 'xiaoyuanhao';
-- 這里可以使用完整的索引，因?yàn)槎际堑戎颠B接

在classId字段上使用范圍查詢，導(dǎo)致name字段失效，有效索引長(zhǎng)度為63

使用的都是等值匹配，整個(gè)索引皆可用，有效索引長(zhǎng)度為73

也就是在對(duì)于聯(lián)合索引來說，如果在使用的時(shí)候是等值匹配，那么就可以重復(fù)的利用索引，如果不是等值匹配，那么該字段也是可以使用索引的，但是該字段右邊的字段就將失效

建議在建立索引的時(shí)候?qū)⑿枰秶ヅ涞淖侄谓⒃谒饕淖詈竺?/p>

場(chǎng)景七：在使用like的時(shí)候，如果以%開頭導(dǎo)致索引失效

EXPLAIN SELECT * FROM student WHERE NAME LIKE 'abc%';
-- 可以正常使用索引
EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';
-- 這里在like中，%在前面無法使用索引

key = name，使用了該索引，索引有效

key = null，索引失效

因?yàn)榻⒌乃饕龑?shí)際上是按照整個(gè)字符串的從第一個(gè)開始進(jìn)行比較排序的，所以在使用like的時(shí)候，也只能夠重現(xiàn)進(jìn)行比較，如果使用的是’%abc’，那么查詢的就是以abc結(jié)尾的數(shù)據(jù)，無法使用索引

場(chǎng)景八：or前后出現(xiàn)非索引字段，索引失效

-- 該表中只有name字段上的索引
CREATE INDEX NAME ON student(NAME);
EXPLAIN SELECT * FROM student WHERE NAME = 'xiao';
-- 這里是可以使用name索引的
EXPLAIN SELECT * FROM student WHERE NAME = 'xiao' OR classId = 1001;
-- 這個(gè)則無法使用索引，進(jìn)行的是全表掃描

key = null，無法使用索引，or條件中出現(xiàn)非索引字段

因?yàn)槿绻鹡ame不等于’xiao’的時(shí)候那么就會(huì)繼續(xù)判斷classId是否等于1001，那么實(shí)際上還是會(huì)進(jìn)行全表掃描，所以索引失效（也就是進(jìn)行name判斷的時(shí)候可以使用索引，但是在判斷classId的時(shí)候又要全表掃描，那么優(yōu)化器就直接進(jìn)行全表掃描），但是如果or前后的字段都有索引了，那么就就會(huì)使用索引

小結(jié)

在建立索引的時(shí)候，盡量要避免出現(xiàn)以上的情況導(dǎo)致索引失效，但是就算建立的索引是正確的、有效的，但是在不同的數(shù)據(jù)量以及數(shù)據(jù)庫版本的情況下，執(zhí)行的結(jié)果也是不一致的，如果想了解哪些情況下適合建立索引，可以從以下文章中進(jìn)行交流MySQL索引優(yōu)化之適合構(gòu)建索引的幾種情況詳解

到此這篇關(guān)于MySQL索引優(yōu)化之不適合構(gòu)建索引及索引失效的幾種情況詳解的文章就介紹到這了,更多相關(guān)MySQL索引優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論