MySQL導(dǎo)致索引失效的情況有哪些

本篇內(nèi)容主要講解“MySQL導(dǎo)致索引失效的情況有哪些”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“MySQL導(dǎo)致索引失效的情況有哪些”吧!

一、準(zhǔn)備工作

首先準(zhǔn)備兩張表用于演示：

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;

#準(zhǔn)備數(shù)據(jù)
select count(*) from student_info;#1000000
select count(*) from course;      #100

二、索引失效規(guī)則

1.優(yōu)先使用聯(lián)合索引

如下一條sql語(yǔ)句是沒(méi)有索引的情況：

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

我們通過(guò)建立索引來(lái)優(yōu)化它的查詢效率，有如下幾種方案：

①建立普通索引：

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

②在普通索引的基礎(chǔ)上，再增加聯(lián)合索引：

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

 可以看到，在多個(gè)索引都可以使用時(shí)，系統(tǒng)一般優(yōu)先使用更長(zhǎng)的聯(lián)合索引，因?yàn)槁?lián)合索引相比來(lái)說(shuō)更快，這點(diǎn)應(yīng)該也很好理解，前提是要遵守聯(lián)合索引的最左匹配原則。

如果再創(chuàng)建一個(gè)name,course_id,class_id組成的聯(lián)合索引，那么上述sql語(yǔ)句不出意外會(huì)使用這個(gè)key_len更長(zhǎng)的聯(lián)合索引(意外是優(yōu)化器可能會(huì)選擇其他更優(yōu)的方案，如果它更快的話)。

聯(lián)合索引速度不一定優(yōu)于普通索引，比如第一個(gè)條件就過(guò)濾了所有記錄，那么就沒(méi)必要用后序的索引了。

2.最左匹配原則

#刪除前例創(chuàng)建的索引，新創(chuàng)建三個(gè)字段的聯(lián)合索引，name-course_id-cass_id
create index idx_name_cou_cls on student_info(name,course_id,class_id);

①聯(lián)合索引全部匹配的情況：

#關(guān)聯(lián)字段的索引比較完整
explain select * from student_info where name='11111' and course_id=10068 and class_id=10154;

 該sql語(yǔ)句符合最左前綴原則，每個(gè)字段條件中的字段恰好和聯(lián)合索引吻合。這種情況是最優(yōu)的，因?yàn)橐揽恳粋€(gè)聯(lián)合索引就可以快速查找，不需要額外的查詢。

②聯(lián)合索引最右邊缺失的情況：

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

 該sql語(yǔ)句條件中，并不含有聯(lián)合索引的全部條件，而是抹去了右半部分，該語(yǔ)句使用的索引依舊是該關(guān)聯(lián)查詢，只不過(guò)只用到了一部分，通過(guò)查看key_len可以知道少了5字節(jié)，這5字節(jié)對(duì)應(yīng)的是class_id，證明class_id并未生效而已(where中沒(méi)有，當(dāng)然用不到啦)。

同理，抹掉where中的course_id字段，聯(lián)合索引依舊會(huì)生效，只是key_len會(huì)減小。

③聯(lián)合索引中間缺失的情況：

#聯(lián)合索引中間的字段未使用，而左邊和右邊的都存在
explain select * from student_info where name='11111' and class_id=10154;;

如上sql語(yǔ)句依舊使用的是聯(lián)合索引，但是它的key_len變小了，只有name字段使用到了索引，而class_id字段雖然在聯(lián)合索引中，但是因?yàn)椴环献钭笃ヅ湓瓌t而GG了。

整個(gè)sql語(yǔ)句的執(zhí)行流程為：先在聯(lián)合索引的B樹(shù)中找到所有name為11111的記錄，然后全文過(guò)濾掉這些記錄中class_id不是10154的記錄。多了一個(gè)全文搜索的步驟，相比于①和②情況性能會(huì)更差。

④聯(lián)合索引最左邊缺失的情況：

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

 該情況是上一個(gè)情況的特例，聯(lián)合索引中最左邊的字段未找到，所以雖然有其他部分，但是統(tǒng)統(tǒng)都失效了，走的是全文查找。

結(jié)論：最左匹配原則指的是查詢從索引的最左列開(kāi)始，并且不能跳過(guò)索引中的列，如果跳過(guò)了某一列，索引將部分失效(后面的字段索引全部失效)。

注意：創(chuàng)建聯(lián)合索引時(shí)，字段的順序就定格了，最左匹配就是根據(jù)該順序比較的；但是在查詢語(yǔ)句中，where條件中字段的順序是可變的，意味著不需要按照關(guān)聯(lián)索引字段的順序，只要where條件中有就行了。

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

承接上面的聯(lián)合索引，使用如下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，代表關(guān)聯(lián)索引中class_id未使用到，雖然符合最左匹配原則，但因?yàn)?strong>>符號(hào)讓關(guān)聯(lián)索引中該條件字段右邊的索引失效了。

但如果使用>=號(hào)的話：

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

 右邊的索引并未失效，key_len為73，所有字段的索引都使用到了。

結(jié)論：為了充分利用索引，我們有時(shí)候可以將>、<等價(jià)轉(zhuǎn)為>=、<=的形式，或者將可能會(huì)有<、>的條件的字段盡量放在關(guān)聯(lián)索引靠后的位置。

4.計(jì)算、函數(shù)導(dǎo)致索引失效

#刪除前面的索引，新創(chuàng)建name字段的索引，方便演示
create index idx_name on student_info(name);

現(xiàn)有一個(gè)需求，找出name為li開(kāi)頭的學(xué)生信息：

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

上面的兩條sql語(yǔ)句都可以滿足需求，然而第一條語(yǔ)句用了索引，第二條沒(méi)有，一點(diǎn)點(diǎn)的改變真是天差地別。

結(jié)論：字段使用函數(shù)會(huì)讓優(yōu)化器無(wú)從下手，B樹(shù)中的值和函數(shù)的結(jié)果可能不搭邊，所以不會(huì)使用索引，即索引失效。字段能不用就不用函數(shù)。

類似：

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

類似的對(duì)字段的運(yùn)算也會(huì)導(dǎo)致索引失效。

5.類型轉(zhuǎn)換導(dǎo)致索引失效

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

如上，name字段是VARCAHR類型的，但是比較的值是INT類型的，name的值會(huì)被隱式的轉(zhuǎn)換為INT類型再比較，中間相當(dāng)于有一個(gè)將字符串轉(zhuǎn)為INT類型的函數(shù)。

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

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

不等于的情況是不會(huì)使用索引的。因?yàn)?=代表著要進(jìn)行全文的查找，用不上索引。

7.is null可以使用索引，is not null無(wú)法使用索引

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

和前一個(gè)規(guī)則類似的，!=null。同理not like也無(wú)法使用索引。

最好在設(shè)計(jì)表時(shí)設(shè)置NOT NULL約束，比如將INT類型的默認(rèn)值設(shè)為0，將字符串默認(rèn)值設(shè)為''。

8.like以%開(kāi)頭，索引失效

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

只要以%開(kāi)頭就無(wú)法使用索引，因?yàn)槿绻?開(kāi)頭，在B樹(shù)排序的數(shù)據(jù)中并不好找。

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

#創(chuàng)建好索引
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;

 如果其中一個(gè)沒(méi)有索引：

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

那么索引就失效了，假設(shè)還是使用索引，那就變成了先通過(guò)索引查，然后再根據(jù)沒(méi)有的索引的字段進(jìn)行全表查詢，這種方式還不如直接全表查詢來(lái)的快。

10.字符集不統(tǒng)一

字符集如果不同，會(huì)存在隱式的轉(zhuǎn)換，索引也會(huì)失效，所有應(yīng)該使用相同的字符集，防止這種情況發(fā)生。

三、建議

• 對(duì)于單列索引，盡量選擇針對(duì)當(dāng)前query過(guò)濾性更好的索引

• 在選擇組合索引時(shí)，query過(guò)濾性最好的字段應(yīng)該越靠前越好

• 在選擇組合索引時(shí)，盡量選擇能包含當(dāng)前query中where子句中更多字段的索引

• 在選擇組合索引時(shí)，如果某個(gè)字段可能出現(xiàn)范圍查詢，盡量將它往后放

到此，相信大家對(duì)“MySQL導(dǎo)致索引失效的情況有哪些”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！