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

最左前綴法則

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

思考 

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

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

范圍查詢

聯合索引中，出現范圍查詢 (>,<) ，范圍查詢右側的列索引失效。

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

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

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

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

索引列運算

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

當根據 phone 字段進行等值匹配查詢時 , 索引生效。

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

當根據phone字段進行函數運算操作之后，索引失效。

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ā)現，如果字符串不加單引號，對于查詢結果，沒什么影響， 但是數據庫存在隱式類型轉換，索引將失效。 模糊查詢 如果僅僅是尾部模糊匹配，索引不會失效。如果是頭部模糊匹配，索引失效。 模糊查詢時， % 加在關鍵字之后

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

模糊查詢時， % 加在關鍵字之前

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

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

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語句

 當or連接的條件，左右兩側字段都有索引時，索引才會生效。

數據分布影響

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

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

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

 SQL提示

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

use index

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

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 * 。 那么什么是覆蓋索引呢？ 覆蓋索引是指 查詢使用了索引，并 且需要返回的列，在該索引中已經全部能夠找到 。

查詢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 表中有一個聯合索引 idx_user_pro_age_sta ，該索引關聯了三個字段profession、 age 、 status ，而這個索引也是一個二級索引，所以葉子節(jié)點下面掛的是這一行的主鍵id 。 所以當我們查詢返回的數據在 id 、 profession 、 age 、 status 之中，則直接走二級索引 直接返回數據了。 如果超出這個范圍，就需要拿到主鍵 id，再去掃描聚集索引，再獲取額外的數據了，這個過程就是回表。 而我們如果一直使用select * 查詢返回所有字段值，很容易就會造成回表查詢（除非是根據主鍵查詢，此時只會掃描聚集索引）

前綴索引

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

語法

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

前綴長度

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

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

創(chuàng)建前綴索引

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

單列索引與聯合索引

• 單列索引：即一個索引只包含單個列。
• 聯合索引：即一個索引包含了多個列。

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

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

總結

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

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

最新評論