mysql索引失效的常見九種原因圖文詳解

Posted on by

前言：

MySQL中提高性能的一個最有效的方式是對數據表設計合理的索引。索引提供瞭高效訪問數據的方法，並且加快查詢的速度， 因此索引對查詢的速度有著至關重要的影響。

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

大多數情況下都（默認）采用B+ 樹來構建索引。隻是空間列類型的索引使R- 樹，並且MEMORY 表還支持hash 索引。其實，用不用索引最終都是優化器說瞭算。

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

1.最佳左前綴法則

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

2.主鍵插入順序

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

可這個數據頁已經滿瞭，再插進來咋辦呢？我們需要把當前 頁面分裂 成兩個頁面，把本頁中的一些記錄移動到新創建的這個頁中。頁面分裂和記錄移位意味著什麼？意味著： 性能損耗 ！所以如果我們想盡量 避免這樣無謂的性能損耗，最好讓插入的記錄的 主鍵值依次遞增 ，這樣就不會發生這樣的性能損耗瞭。         

所以我們建議：讓主鍵具有 AUTO_INCREMENT ，讓存儲引擎自己為表生成主鍵， 在插入記錄時存儲引擎會自動為我們填入自增的主鍵值。這樣的主鍵占用空間小，順序寫入，減少頁分裂。

3.計算、函數、類型轉換(自動或手動)導致索引失效

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

例子：

#創建一個聯合索引， 註意字段的順序
create index idx_age_classid_name on student(age,classid,name);
#執行計劃
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student .age = 30 AND student .classId > 20 AND student .name = 'abc' ;  

#再創建一個聯合索引，與上面的索引對比字段順序變瞭
create index idx_age_name_classid on student(age,name,classid); 

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

 看到兩個執行計劃雖然都用到瞭索引，但是：

  • 第一個沒用全，隻用到瞭聯合索引“idx_age_classid_name” 的age和classid。
  • 第二個把聯合索引“idx_age_name_classid”的age，name和classid都用上瞭。

5.不等於(!= 或者<>)導致索引失效

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

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

拓展： Alibaba 《Java 開發手冊》 【強制】頁面搜索嚴禁左模糊或者全模糊，如果需要請走搜索引擎來解決。

8.OR 前後隻要存在非索引的列，都會導致索引失效 

9.數據庫和表的字符集統一使用utf8mb4         

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

總結

到此這篇關於mysql索引失效的常見九種原因的文章就介紹到這瞭,更多相關mysql索引失效原因內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：