MySQL導致索引失效的幾種情況

2022-06-23 18:02:02

1.優先使用聯合索引
2.最左匹配原則
3.範圍條件右邊的列索引失效
4.計算、函數導致索引失效
5.型別轉換導致索引失效
6.不等於(!= 或者<>)索引失效
7.is null可以使用索引，is not null無法使用索引
8.like以%開頭，索引失效
9.OR前後存在非索引的列，索引失效
10.字元集不統一

三、建議

一、準備工作

首先準備兩張表用於演示：

CREATE TABLE `student_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `course_id` int NOT NULL,
  `class_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;

CREATE TABLE `course` (
  `id` int NOT NULL AUTO_INCREMENT,
  `course_id` int NOT NULL,
  `course_name` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;

#準備資料
select count(*) from student_info;#1000000
select count(*) from course;      #100

二、索引失效規則

1.優先使用聯合索引

如下一條sql語句是沒有索引的情況：

#平均耗時291毫秒
select * from student_info where name='123' and course_id=1 and class_id=1;

我們通過建立索引來優化它的查詢效率，有如下幾種方案：

①建立普通索引：

#建立普通索引
create index idx_name on student_info(name);
#平均耗時25毫秒，檢視explain執行計劃，使用到的是idx_name索引查詢
select * from student_info where name='MOKiKb' and course_id=1 and class_id=1;

②在普通索引的基礎上，再增加聯合索引：

#name，course_id組成的聯合索引
create index idx_name_courseId on student_info(name,course_id);
#該查詢語句一般使用的是聯合索引，而不是普通索引，具體看優化器決策
#平均耗時20ms
select * from student_info where name='zhangsan' and course_id=1 and class_id=1;

可以看到，在多個索引都可以使用時，系統一般優先使用更長的聯合索引，因為聯合索引相比來說更快，這點應該也很好理解，前提是要遵守聯合索引的最左匹配原則。

如果再建立一個name,course_id,class_id組成的聯合索引，那麼上述sql語句不出意外會使用這個key_len更長的聯合索引(意外是優化器可能會選擇其他更優的方案，如果它更快的話)。

聯合索引速度不一定優於普通索引，比如第一個條件就過濾了所有記錄，那麼就沒必要用後序的索引了。

2.最左匹配原則

#刪除前例建立的索引，新建立三個欄位的聯合索引，name-course_id-cass_id
create index idx_name_cou_cls on student_info(name,course_id,class_id);

①聯合索引全部匹配的情況：

#關聯欄位的索引比較完整
explain select * from student_info where name='11111' and course_id=10068 and class_id=10154;

該sql語句符合最左字首原則，每個欄位條件中的欄位恰好和聯合索引吻合。這種情況是最優的，因為依靠一個聯合索引就可以快速查詢，不需要額外的查詢。

②聯合索引最右邊缺失的情況：

explain select * from student_info where name='11111' and course_id=10068;

該sql語句條件中，並不含有聯合索引的全部條件，而是抹去了右半部分，該語句使用的索引依舊是該關聯查詢，只不過只用到了一部分，通過檢視key_len可以知道少了5位元組，這5位元組對應的是class_id，證明class_id並未生效而已(where中沒有，當然用不到啦)。

同理，抹掉where中的course_id欄位，聯合索引依舊會生效，只是key_len會減小。

③聯合索引中間缺失的情況：

#聯合索引中間的欄位未使用，而左邊和右邊的都存在
explain select * from student_info where name='11111' and class_id=10154;;

如上sql語句依舊使用的是聯合索引，但是它的key_len變小了，只有name欄位使用到了索引，而class_id欄位雖然在聯合索引中，但是因為不符合最左匹配原則而GG了。

整個sql語句的執行流程為：先在聯合索引的B樹中找到所有name為11111的記錄，然後全文過濾掉這些記錄中class_id不是10154的記錄。多了一個全文搜尋的步驟，相比於①和②情況效能會更差。

④聯合索引最左邊缺失的情況：

explain select * from student_info where class_id=10154 and course_id=10068;

該情況是上一個情況的特例，聯合索引中最左邊的欄位未找到，所以雖然有其他部分，但是統統都失效了，走的是全文查詢。

結論：最左匹配原則指的是查詢從索引的最左列開始，並且不能跳過索引中的列，如果跳過了某一列，索引將部分失效(後面的欄位索引全部失效)。

注意：建立聯合索引時，欄位的順序就定格了，最左匹配就是根據該順序比較的；但是在查詢語句中，where條件中欄位的順序是可變的，意味著不需要按照關聯索引欄位的順序，只要where條件中有就行了。

3.範圍條件右邊的列索引失效

承接上面的聯合索引，使用如下sql查詢：

#key_len=> name:63,course_id:5,class_id:5
explain select * from student_info where name='11111' and course_id>1 and class_id=1;

key_len只有68，代表關聯索引中class_id未使用到，雖然符合最左匹配原則，但因為>符號讓關聯索引中該條件欄位右邊的索引失效了。

但如果使用>=號的話：

#不是>、<,而是>=、<=
explain select * from student_info where name='11111' and course_id>=20 and course_id<=40 and class_id=1;

右邊的索引並未失效，key_len為73，所有欄位的索引都使用到了。

結論：為了充分利用索引，我們有時候可以將>、<等價轉為>=、<=的形式，或者將可能會有<、>的條件的欄位儘量放在關聯索引靠後的位置。

4.計算、函數導致索引失效

#刪除前面的索引，新建立name欄位的索引，方便演示
create index idx_name on student_info(name);

現有一個需求，找出name為li開頭的學生資訊：

#使用到了索引
explain select * from student_info where name like 'li%';
#未使用索引，花費時間更久
explain select * from student_info where LEFT(name,2)='li';

上面的兩條sql語句都可以滿足需求，然而第一條語句用了索引，第二條沒有，一點點的改變真是天差地別。

結論：欄位使用函數會讓優化器無從下手，B樹中的值和函數的結果可能不搭邊，所以不會使用索引，即索引失效。欄位能不用就不用函數。

類似：

#也不會使用索引
explain select * from student_info where name+''='lisi';

類似的對欄位的運算也會導致索引失效。

5.型別轉換導致索引失效

#不會使用name的索引
explain select * from student_info where name=123;
#使用到索引
explain select * from student_info where name='123';

如上，name欄位是VARCAHR型別的，但是比較的值是INT型別的，name的值會被隱式的轉換為INT型別再比較，中間相當於有一個將字串轉為INT型別的函數。

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

#建立索引
create index idx_name on student_info(name);
#索引失效
explain select * from student_info where name<>'zhangsan';
explain select * from student_info where name!='zhangsan';

不等於的情況是不會使用索引的。因為!=代表著要進行全文的查詢，用不上索引。

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

#可以使用索引
explain select * from student_info where name is null;
#索引失效
explain select * from student_info where name is not null;

和前一個規則類似的，!=null。同理not like也無法使用索引。

最好在設計表時設定NOT NULL約束，比如將INT型別的預設值設為0，將字串預設值設為''。

8.like以%開頭，索引失效

#使用到了索引
explain select * from student_info where name like 'li%';
#索引失效
explain select * from student_info where name like '%li';

只要以%開頭就無法使用索引，因為如果以%開頭，在B樹排序的資料中並不好找。

9.OR前後存在非索引的列，索引失效

#建立好索引
create index idx_name on student_info(name);
create index idx_courseId on student_info(course_id);

如果or前後都是索引：

#使用索引
explain select * from student_info where name like 'li%' or course_id=200;

如果其中一個沒有索引：

explain select * from student_info where name like 'li%' or class_id=1;

那麼索引就失效了，假設還是使用索引，那就變成了先通過索引查，然後再根據沒有的索引的欄位進行全表查詢，這種方式還不如直接全表查詢來的快。

10.字元集不統一

字元集如果不同，會存在隱式的轉換，索引也會失效，所有應該使用相同的字元集，防止這種情況發生。

三、建議

對於單列索引，儘量選擇針對當前query過濾性更好的索引
在選擇組合索引時，query過濾性最好的欄位應該越靠前越好
在選擇組合索引時，儘量選擇能包含當前query中where子句中更多欄位的索引
在選擇組合索引時，如果某個欄位可能出現範圍查詢，儘量將它往後放

到此這篇關於MySQL導致索引失效的幾種情況的文章就介紹到這了,更多相關MySQL 索引失效內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！