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

2022-06-01 14:01:24

前言：
1.最佳左字首法則
3.計算、函數、型別轉換(自動或手動)導致索引失效
4.範圍條件右邊的列索引失效
5.不等於(!= 或者<>)導致索引失效
6.is null可以使用索引，is not null無法使用索引
7.like以萬用字元%開頭索引失效
8.OR 前後只要存在非索引的列，都會導致索引失效
9.資料庫和表的字元集統一使用utf8mb4
總結

前言：

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' ;