幾乎所有的小伙伴都可以隨口說幾句關(guān)于創(chuàng)建索引的優(yōu)缺點(diǎn)，也知道什么時(shí)候創(chuàng)建索引能夠提高我們的查詢性能，什么時(shí)候索引會(huì)更新，但是你有沒有注意到，即使你設(shè)置了索引，有些時(shí)候索引他是不會(huì)生效的！這不僅考察了大家對(duì)索引的了解程度，還要讓大家在使用的時(shí)候能夠正確的使用。以下介紹了一些可能會(huì)造成索引失效的特殊情況，希望大家在平時(shí)開發(fā)和面試的時(shí)候能夠注意到！

一、如何判斷數(shù)據(jù)庫索引是否生效

首先在接著探討之前，我們先說一下，如何判斷數(shù)據(jù)庫的索引是否生效！相信大家應(yīng)該猜到了，就是explain！explain顯示了MySQL如何使用索引來處理select語句以及連接表。他可以幫助選擇更好的索引和寫出更優(yōu)化的查詢語句。

例如我們有一張表user，為name列創(chuàng)建索引name_index，如下所示：

使用explain分析語句如下：

可以看到，使用explain顯示了很多列，各個(gè)關(guān)鍵字的含義如下：

• table：顧名思義，顯示這一行的數(shù)據(jù)是關(guān)于哪張表的；
• type：這是重要的列，顯示連接使用了何種類型。從***到最差的連接類型為：const、eq_reg、ref、range、indexhe和ALL；
• possible_keys：顯示可能應(yīng)用在這張表中的索引。如果為空，沒有可能的索引?？梢詾橄嚓P(guān)的域從where語句中選擇一個(gè)合適的語句；
• key： 實(shí)際使用的索引。如果為NULL，則沒有使用索引。很少的情況下，MySQL會(huì)選擇優(yōu)化不足的索引。這種情況下，可以在Select語句中使用USE INDEX（indexname）來強(qiáng)制使用一個(gè)索引或者用IGNORE INDEX（indexname）來強(qiáng)制MySQL忽略索引；
• key_len：使用的索引的長(zhǎng)度。在不損失精確性的情況下，長(zhǎng)度越短越好；
• ref：顯示索引的哪一列被使用了，如果可能的話，是一個(gè)常數(shù)；
• rows：MySQL認(rèn)為必須檢查的用來返回請(qǐng)求數(shù)據(jù)的行數(shù)；
• Extra：關(guān)于MySQL如何解析查詢的額外信息。

具體的各個(gè)列所能表示的值以及含義可以參考MySQL官方文檔介紹，地址：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

二、哪些場(chǎng)景會(huì)造成索引生效

1、應(yīng)盡量避免在 where 子句中使用 != 或 <> 操作符，否則引擎將放棄使用索引而進(jìn)行全表掃描；

2、盡量避免在 where 子句中使用 or 來連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，即使其中有條件帶索引也不會(huì)使用，這也是為什么盡量少用 or 的原因；

3、對(duì)于多列索引，不是使用的***部分，則不會(huì)使用索引；

4、如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號(hào)引用起來，否則不會(huì)使用索引；

5、like的模糊查詢以 % 開頭，索引失效；

6、應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；

7、應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；

8、不要在 where 子句中的 “=” 左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無法正確使用索引；

9、如果MySQL估計(jì)使用全表掃描要比使用索引快，則不使用索引；

10、不適合鍵值較少的列（重復(fù)數(shù)據(jù)較多的列）