索引并不是時時都會生效的，比如以下幾種情況，將導(dǎo)致索引失效 

最左前綴法則

如果使用了聯(lián)合索引，要遵守最左前綴法則。最左前綴法則指的是查詢從索引的最左列開始， 并且不跳過索引中的列。如果跳躍某一列，索引將會部分失效( 后面的字段索引失效 ) 。查看tb_user 表所創(chuàng)建的索引 。 這個聯(lián)合索引涉及到三個字段，順序分別為：profession，age，status。

show index from tb_user;

對于最左前綴法則指的是，查詢時，最左變的列，也就是profession必須存在，否則索引全部失效。

 explain select * from tb_user where profession = '軟件工程' and age = 31 and status = '0';

SQL 查詢時，存在 profession 字段，最左邊的列是存在的，索引滿足最左前綴法則的基本條 件。但是查詢時，跳過了 age 這個列，所以后面的列索引是不會使用的，也就是索引部分生效，所以索引的長度就是47 。

explain select * from tb_user where profession = '軟件工程' and status = '0';

思考 

當(dāng)執(zhí)行 SQL 語句 : explain select * from tb_user where age = 31 and status = '0' and profession = '軟件工程 ' ； 時，是否滿足最左前綴法則，走不走聯(lián)合索引，

可以看到，是完全滿足最左前綴法則的，索引長度 54 ，聯(lián)合索引是生效的。注意 ： 最左前綴法則中指的最左邊的列，是指在查詢時，聯(lián)合索引的最左邊的字段( 即是第一個字段) 必須存在，與我們編寫 SQL 時，條件編寫的先后順序無關(guān)。

范圍查詢

聯(lián)合索引中，出現(xiàn)范圍查詢 (>,<) ，范圍查詢右側(cè)的列索引失效。

explain select * from tb_user where profession = '軟件工程' and age > 30 and status = '0' ;

當(dāng)范圍查詢使用 > 或 < 時，走聯(lián)合索引了，但是索引的長度為 49 ，就說明范圍查詢右邊的 status 字 段是沒有走索引的。

explain select * from tb_user where profession = '軟件工程' and age >= 30 and status = '0';

當(dāng)范圍查詢使用 >= 或 <= 時，走聯(lián)合索引了，但是索引的長度為 54，就說明所有的字段都是走索引的。 所以，在業(yè)務(wù)允許的情況下，盡可能的使用類似于 >= 或 <= 這類的范圍查詢，而避免使用 > 或 < 。

索引列運算

不要在索引列上進行運算操作， 索引將失效。在tb_user表中，除了前面介紹的聯(lián)合索引之外，還有一個索引，是phone字段的單列索引。

當(dāng)根據(jù) phone 字段進行等值匹配查詢時 , 索引生效。

explain select * from tb_user where phone = '17799990015';

當(dāng)根據(jù)phone字段進行函數(shù)運算操作之后，索引失效。

explain select * from tb_user where substring(phone,10,2) = '15';

字符串不加引號

字符串類型字段使用時，不加引號，索引將失效。 字符串類型的字段，加單引號

 explain select * from tb_user where profession = '軟件工程' and age = 31 and status = '0';

 字符串類型的字段，不加單引號

 explain select * from tb_user where profession = '軟件工程' and age = 31 and status = '0';

我們會明顯的發(fā)現(xiàn)，如果字符串不加單引號，對于查詢結(jié)果，沒什么影響， 但是數(shù)據(jù)庫存在隱式類型轉(zhuǎn)換，索引將失效。 模糊查詢 如果僅僅是尾部模糊匹配，索引不會失效。如果是頭部模糊匹配，索引失效。 模糊查詢時， % 加在關(guān)鍵字之后

explain select * from tb_user where profession like '軟件%';

模糊查詢時， % 加在關(guān)鍵字之前

explain select * from tb_user where profession like '%工程';

我們發(fā)現(xiàn)，在 like 模糊查詢中，在關(guān)鍵字后面加 % ，索引可以生效。而如果在關(guān)鍵字 前面加了 % ，索引將會失效。

or連接條件

用 or 分割開的條件， 如果 or 前的條件中的列有索引，而后面的列中沒有索引，那么涉及的索引都不會被用到。

explain select * from tb_user where profession like '%工程';

由于age沒有索引，所以即使id、phone有索引，索引也會失效。所以需要針對于age也要建立索引。

create index idx_user_age on tb_user(age);

再次執(zhí)行上述的SQL語句

 當(dāng)or連接的條件，左右兩側(cè)字段都有索引時，索引才會生效。

數(shù)據(jù)分布影響

如果 MySQL 評估使用索引比全表更慢，則不使用索引。

explain select * from tb_user where phone >= '17799990005';
explain select * from tb_user where phone >= '17799990015';

MySQL 在查詢時，會評估使用索引的效率與走全表掃描的效率，如果走全表掃描更快，則放棄 索引，走全表掃描。 因為索引是用來索引少量數(shù)據(jù)的，如果通過索引查詢返回大批量的數(shù)據(jù)，則還不如走全表掃描來的快，此時索引就會失效。

 SQL提示

SQL 提示，是優(yōu)化數(shù)據(jù)庫的一個重要手段，簡單來說，就是在 SQL 語句中加入一些人為的提示來達到優(yōu)化操作的目的。

use index

建議 MySQL 使用哪一個索引完成此次查詢（僅僅是建議， mysql 內(nèi)部還會再次進行評估）

explain select * from tb_user use index(idx_user_pro) where profession = '軟件工程';

 ignore index

忽略指定的索引。

explain select * from tb_user ignore index(idx_user_pro) where profession = '軟件工程';

force index

強制使用索引。

explain select * from tb_user force index(idx_user_pro) where profession = '軟件工程';

覆蓋索引

盡量使用覆蓋索引，減少 select * 。 那么什么是覆蓋索引呢？ 覆蓋索引是指 查詢使用了索引，并 且需要返回的列，在該索引中已經(jīng)全部能夠找到 。

查詢id,profession,age, status字段

explain select id,profession,age, status from tb_user where profession = '軟件工程' and age = 31 and status = '0' ;

 查詢id,profession,age, status,name字段

explain select id,profession,age, status,name from tb_user where profession = '軟件工程' and age = 31 and status = '0' \G;

因為，在 tb_user 表中有一個聯(lián)合索引 idx_user_pro_age_sta ，該索引關(guān)聯(lián)了三個字段profession、 age 、 status ，而這個索引也是一個二級索引，所以葉子節(jié)點下面掛的是這一行的主鍵id 。 所以當(dāng)我們查詢返回的數(shù)據(jù)在 id 、 profession 、 age 、 status 之中，則直接走二級索引 直接返回數(shù)據(jù)了。 如果超出這個范圍，就需要拿到主鍵 id，再去掃描聚集索引，再獲取額外的數(shù)據(jù)了，這個過程就是回表。 而我們?nèi)绻恢笔褂胹elect * 查詢返回所有字段值，很容易就會造成回表查詢（除非是根據(jù)主鍵查詢，此時只會掃描聚集索引）

前綴索引

當(dāng)字段類型為字符串（ varchar ， text ， longtext 等）時，有時候需要索引很長的字符串，這會讓 索引變得很大，查詢時，浪費大量的磁盤 IO ， 影響查詢效率。此時可以只將字符串的一部分前綴，建立索引，這樣可以大大節(jié)約索引空間，從而提高索引效率。

語法

create index idx_xxxx on table_name(column(n)) ; 1

前綴長度

可以根據(jù)索引的選擇性來決定，而選擇性是指不重復(fù)的索引值（基數(shù)）和數(shù)據(jù)表的記錄總數(shù)的比值，索引選擇性越高則查詢效率越高， 唯一索引的選擇性是1 ，這是最好的索引選擇性，性能也是最好的。

select count(distinct substring(email,1,5)) / count(*) from tb_user ;

創(chuàng)建前綴索引

create index idx_email_5 on tb_user(email(5));

單列索引與聯(lián)合索引

• 單列索引：即一個索引只包含單個列。
• 聯(lián)合索引：即一個索引包含了多個列。

我們先來看看 tb_user 表中目前的索引情況， 在查詢出來的索引中，既有單列索引，又有聯(lián)合索引。

在業(yè)務(wù)場景中，如果存在多個查詢條件，考慮針對于查詢字段建立索引時，建議建立聯(lián)合索引， 而非單列索引。

總結(jié)

針對于數(shù)據(jù)量較大，且查詢比較頻繁的表建立索引。
針對于常作為查詢條件（where）、排序（order by）、分組（group by）操作的字段建立引。
盡量選擇區(qū)分度高的列作為索引，盡量建立唯一索引，區(qū)分度越高，使用索引的效率越高。
如果是字符串類型的字段，字段的長度較長，可以針對于字段的特點，建立前綴索引。
盡量使用聯(lián)合索引，減少單列索引，查詢時，聯(lián)合索引很多時候可以覆蓋索引，節(jié)省存儲空間， 避免回表，提高查詢效率。
要控制索引的數(shù)量，索引并不是多多益善，索引越多，維護索引結(jié)構(gòu)的代價也就越大，會影響增刪改的效率。
如果索引列不能存儲NULL值，請在創(chuàng)建表時使用NOT NULL約束它。當(dāng)優(yōu)化器知道每列是否包含NULL值時，它可以更好地確定哪個索引最有效地用于查詢。

到此這篇關(guān)于MYSQL的索引使用注意小結(jié)的文章就介紹到這了,更多相關(guān)mysql索引使用內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論