mysql索引失效的常見原因有哪些

本篇內容介紹了“mysql索引失效的常見原因有哪些”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

前言：

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索引失效的常見原因有哪些”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！