MySQL索引優化之不適合構建索引及索引失效的幾種情況詳解

Posted on by

結論

具體案例下文有詳盡描述

不適合建立索引的場景：

  • 數據量比較小的表不建議建立索引
  • 有大量重復數據的字段上不建議建立索引（類似：性別字段）
  • 需要進行頻繁更新的表不建議建立索引
  • where、group by、order by後面的沒有使用到的字段不建立索引
  • 不要定義冗餘索引

索引失效的場景：

  • 過濾條件使用不等於(!=、<>)
  • 過濾條件使用is not null
  • 在索引字段上使用函數或進行計算
  • 在使用聯合索引的時候，需要滿足“最佳左前綴法則”，否則失效
  • 當使用瞭類型轉換也會導致索引失效
  • 在使用范圍查詢的時候，聯合索引的部分字段失效(where age >18)
  • 在like字段中，如果是以%開頭，索引失效(where name like ‘%abc’)
  • 在使用or進行查詢的時候，or前後出現非索引字段，索引失效
  • 表和庫的字符集不一致，回導致索引失效

知識點：

  • 每張表的索引不建議超過6個（占用空間、降低表更新速度）
  • 最終到底是否使用索引還是優化器進行決定的
  • 優化器會根據數據量、數據庫版本、數據選擇讀進行查詢代價的比較，從而決定是否使用索引
  • 建立索引的時候將需要范圍匹配的字段建立在索引的尾部，避免失效
  • 在建立表的時候將字段設置為not null同時設置默認值，當需要查找沒有值的記錄的時候就可以使用where xxx = 默認值，放置使用is not null導致索引失效
  • 頁面搜索的時候嚴謹左模糊或者全模糊(like ‘%abc’)
  • 對於過濾性較好的字段建立在聯合索引的前面，這樣就可以優先過濾比較多的數據

不建議建立索引的場景

場景一：數據少的表

當數據比較少的時候，索引的優勢就不明顯瞭，因為數據庫的存儲引擎也是非常快的，相較於需要查詢索引在進行回表操作，可能直接查詢的性能會更高一些，所以數據相對較少的表不建議建立索引

場景二：有大量重復數據的字段

類似於性別字段，隻有“男”和“女”兩個不同的值，所以索引一半的數據是“男”一半的數據是“女”，那麼建立索引並不能進行快速的查詢等，所以不建議在有大量重復數據的列上建立索引

場景三：頻繁更新的表（update/delete/insert）

因為表中更新數據的時候，索引也是需要進行對應的維護的，如果一個表近期需要頻繁的進行增刪改操作，那麼就需要耗費大量的時間去維護索引，不建議建立索引，可以在需要進行頻繁的更新操作的時候將索引刪除，更新完畢之後重建索引

場景四：沒有使用的字段（where/group by/order by）

不是where/group by/order by後面的字段沒有必要建立索引，因為不會使用到該索引

場景五：不要定義冗餘索引

create index username_password_address on xiao(username,password,address);
-- 如果建立瞭第一個索引，那麼就沒有必要建立第二個索引
create index username on xiao (username);
--第二個索引就是冗餘索引，因為第一個已經是先根據username排序的索引
--也就是第二個索引的功能完全可以由第一個索引實現

這裡因為username作為第一個聯合索引的第一個字段，所以索引就是按照username進行排序，在username相同的情況下按照password、address排序，所以也就是實現瞭單獨拿username列作為索引的功能，即第二個索引就是多餘的

索引失效的場景

場景一：在建立索引的字段上進行運算(函數等)，導致索引失效

這裡首先是給age創建瞭索引，在第一次查詢過程中使用瞭age索引，但是第二次key值為null（索引失效），導致索引失效的原因在於第二次查詢的時候where後面對age進行瞭計算，計算機並不知道執行的是什麼計算所以會將age+1計算後與1比較，索引失效

類似於在字段上使用函數concat()等都會導致索引失效

場景二：使用不等於(where age != 18)

當使用等值運算，那麼是可以在索引中進行查找的，但是如果是不等於，那麼則需要遍歷所有數據，所以所失效

explain select * from xiaoyuanhao where age = 18;
explain select * from xiaoyuanhao where age != 18;
--這裡是在age字段上建立瞭普通索引，第二個查詢時候索引失效

場景三：使用is not null索引失效

與不等於一樣，如果使用的是is not null，那麼就需要進行全部數據的遍歷操作，索引失效，但是如果使用的是is null那麼依舊是可以使用索引的

--這裡是在age字段上建立瞭普通索引，第二個查詢時候索引失效
explain select * from xiaoyuanhao where age is null;
--可以正常使用索引
explain select * from xiaoyuanhao where age is not null;
--索引失效

場景四：在使用聯合索引的時候沒有遵循最佳左前綴法則

CREATE INDEX age_classid_name ON student(age,classId,NAME);
EXPLAIN SELECT * FROM student WHERE classId = 30 AND NAME = 'xiaoyuanhao';
-- 因為沒有使用age字段，所以沒有準許最佳左前綴原則，索引失效

從這裡可以看出是沒有使用索引的(key = null)，因為創建的索引是先按照age進行排序，在age相同的情況下按照classId和name排序，如果在查詢的時候需要直接按照classId進行排序查找，那麼就無法使用該索引，即索引失效。

如果需要使用使用索引，那麼就一定需要到聯合索引的第一個字段age，案例如下

EXPLAIN SELECT * FROM student WHERE age = 10 AND NAME = 'xiaoyuanhao';
EXPLAIN SELECT * FROM student WHERE age = 10 AND classId = 33 AND NAME = 'xiaoyuanhao';
--兩者都是使用age字段索引，所以索引有效

場景五：類型轉換導致索引失效

CREATE INDEX NAME ON student(NAME);
-- 這裡的name字段是varchar類型
EXPLAIN SELECT * FROM student WHERE NAME = 'xiao';
-- 本次查詢是可以使用索引的，因為類型都是一致的，都是字符串
EXPLAIN SELECT * FROM student WHERE NAME = 123;
-- 本次查詢則無法使用索引，因為是將數字類型123轉換為字符類型

沒有發生類型轉換，使用索引key = name

發生瞭類型轉換，無法使用索引kye = null，索引失效

使用索引的時候一定需要保證數據類型是一致的，否則系統就需要進行轉換，那麼就無法使用索引

場景六：使用范圍查詢導致聯合索引其他字段失效

create index age_classId_name on student (age,classId,name);
EXPLAIN SELECT * FROM student WHERE age = 10 AND classId > 20 AND NAME = 'xiaoyuanhao';
-- 這裡隻能使用age,classId，索引的前兩個字段
EXPLAIN SELECT * FROM student WHERE age = 10 AND classId = 20 AND NAME = 'xiaoyuanhao';
-- 這裡可以使用完整的索引，因為都是等值連接

在classId字段上使用范圍查詢，導致name字段失效，有效索引長度為63

使用的都是等值匹配，整個索引皆可用，有效索引長度為73

也就是在對於聯合索引來說，如果在使用的時候是等值匹配，那麼就可以重復的利用索引，如果不是等值匹配，那麼該字段也是可以使用索引的，但是該字段右邊的字段就將失效

建議在建立索引的時候將需要范圍匹配的字段建立在索引的最後面

場景七：在使用like的時候，如果以%開頭導致索引失效

EXPLAIN SELECT * FROM student WHERE NAME LIKE 'abc%';
-- 可以正常使用索引
EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';
-- 這裡在like中，%在前面無法使用索引

key = name，使用瞭該索引，索引有效

key = null，索引失效

因為建立的索引實際上是按照整個字符串的從第一個開始進行比較排序的，所以在使用like的時候，也隻能夠重現進行比較，如果使用的是’%abc’，那麼查詢的就是以abc結尾的數據，無法使用索引

場景八：or前後出現非索引字段，索引失效

-- 該表中隻有name字段上的索引
CREATE INDEX NAME ON student(NAME);
EXPLAIN SELECT * FROM student WHERE NAME = 'xiao';
-- 這裡是可以使用name索引的
EXPLAIN SELECT * FROM student WHERE NAME = 'xiao' OR classId = 1001;
-- 這個則無法使用索引，進行的是全表掃描

key = null，無法使用索引，or條件中出現非索引字段

因為如果name不等於’xiao’的時候那麼就會繼續判斷classId是否等於1001，那麼實際上還是會進行全表掃描，所以索引失效（也就是進行name判斷的時候可以使用索引，但是在判斷classId的時候又要全表掃描，那麼優化器就直接進行全表掃描），但是如果or前後的字段都有索引瞭，那麼就就會使用索引

小結

在建立索引的時候，盡量要避免出現以上的情況導致索引失效，但是就算建立的索引是正確的、有效的，但是在不同的數據量以及數據庫版本的情況下，執行的結果也是不一致的，如果想瞭解哪些情況下適合建立索引，可以從以下文章中進行交流MySQL索引優化之適合構建索引的幾種情況詳解

到此這篇關於MySQL索引優化之不適合構建索引及索引失效的幾種情況詳解的文章就介紹到這瞭,更多相關MySQL索引優化內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：