前言：

MySQL中提高性能的一個(gè)最有效的方式是對(duì)數(shù)據(jù)表設(shè)計(jì)合理的索引。索引提供了高效訪問數(shù)據(jù)的方法，并且加快查詢的速度， 因此索引對(duì)查詢的速度有著至關(guān)重要的影響。

• 使用索引可以快速地定位表中的某條記錄，從而提高數(shù)據(jù)庫查詢的速度，提高數(shù)據(jù)庫的性能。
• 如果查詢時(shí)沒有使用索引，查詢語句就會(huì)掃描表中的所有記錄。在數(shù)據(jù)量大的情況下，這樣查詢的速度會(huì)很慢。

大多數(shù)情況下都（默認(rèn)）采用B+ 樹來構(gòu)建索引。只是空間列類型的索引使R- 樹，并且MEMORY 表還支持hash 索引。其實(shí)，用不用索引最終都是優(yōu)化器說了算。

優(yōu)化器是基于什么的優(yōu)化器？ 基于cost開銷(CostBaseOptimizer) ，它不是基于規(guī)則（ Rule-BasedOptimizer），也不是基于語義。怎么樣開銷小就怎么來。另外， SQL 語句是否使用索引，跟數(shù)據(jù)庫版本、數(shù)據(jù)量、數(shù)據(jù)選擇度都有關(guān)系。

1.最佳左前綴法則

拓展： Alibaba 《 Java 開發(fā)手冊(cè)》 索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引。

2.主鍵插入順序

 如果此時(shí)再插入一條主鍵值為 9 的記錄，那它插入的位置就如下圖：

可這個(gè)數(shù)據(jù)頁已經(jīng)滿了，再插進(jìn)來咋辦呢？我們需要把當(dāng)前 頁面分裂 成兩個(gè)頁面，把本頁中的一些記錄移動(dòng)到新創(chuàng)建的這個(gè)頁中。頁面分裂和記錄移位意味著什么？意味著： 性能損耗 ！所以如果我們想盡量 避免這樣無謂的性能損耗，最好讓插入的記錄的 主鍵值依次遞增 ，這樣就不會(huì)發(fā)生這樣的性能損耗了。         

所以我們建議：讓主鍵具有 AUTO_INCREMENT ，讓存儲(chǔ)引擎自己為表生成主鍵， 在插入記錄時(shí)存儲(chǔ)引擎會(huì)自動(dòng)為我們填入自增的主鍵值。這樣的主鍵占用空間小，順序?qū)懭?，減少頁分裂。

3.計(jì)算、函數(shù)、類型轉(zhuǎn)換(自動(dòng)或手動(dòng))導(dǎo)致索引失效

4.范圍條件右邊的列索引失效

例子：

#創(chuàng)建一個(gè)聯(lián)合索引， 注意字段的順序
create index idx_age_classid_name on student(age,classid,name);
#執(zhí)行計(jì)劃
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student .age = 30 AND student .classId > 20 AND student .name = 'abc' ;  

#再創(chuàng)建一個(gè)聯(lián)合索引，與上面的索引對(duì)比字段順序變了
create index idx_age_name_classid on student(age,name,classid); 

#再執(zhí)行一模一樣的執(zhí)行計(jì)劃
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student .age = 30 AND student .classId > 20 AND student .name = 'abc' ;

 看到兩個(gè)執(zhí)行計(jì)劃雖然都用到了索引，但是：

• 第一個(gè)沒用全，只用到了聯(lián)合索引“idx_age_classid_name” 的age和classid。
• 第二個(gè)把聯(lián)合索引“idx_age_name_classid”的age，name和classid都用上了。

5.不等于(!= 或者<>)導(dǎo)致索引失效

6.is null可以使用索引，is not null無法使用索引

7.like以通配符%開頭索引失效

拓展： Alibaba 《Java 開發(fā)手冊(cè)》 【強(qiáng)制】頁面搜索嚴(yán)禁左模糊或者全模糊，如果需要請(qǐng)走搜索引擎來解決。

8.OR 前后只要存在非索引的列，都會(huì)導(dǎo)致索引失效 

9.數(shù)據(jù)庫和表的字符集統(tǒng)一使用utf8mb4         

統(tǒng)一使用utf8mb4( 5.5.3 版本以上支持 ) 兼容性更好，統(tǒng)一字符集可以避免由于字符集轉(zhuǎn)換產(chǎn)生的亂碼。不同的 字符集 進(jìn)行比較前需要進(jìn)行 轉(zhuǎn)換 會(huì)造成索引失效。

總結(jié)

到此這篇關(guān)于mysql索引失效的常見九種原因的文章就介紹到這了,更多相關(guān)mysql索引失效原因內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論