前言

MySQL中提高性能的一個(gè)最有效的方式是對(duì)數(shù)據(jù)表設(shè)計(jì)合理的索引。索引提供了高效訪問(wèn)數(shù)據(jù)的方法，并且加快查詢的速度，因此索引對(duì)查詢的速度有著至關(guān)重要的影響。

• 使用索引可以快速地定位表中的某條記錄，從而提高數(shù)據(jù)庫(kù)查詢的速度，提高數(shù)據(jù)庫(kù)的性能。
• 如果查詢時(shí)沒(méi)有使用索引，查詢語(yǔ)句就會(huì)掃描表中的所有記錄。在數(shù)據(jù)量大的情況下，這樣查詢的速度回很慢。

大多數(shù)情況下都（默認(rèn)）采用B+樹來(lái)構(gòu)建索引。只是空間列類型的索引使用R-樹，并且MEMORY表還支持hash索引。

其實(shí)，用不用索引，最終都是優(yōu)化器說(shuō)了算。優(yōu)化器是基于什么的考慮？基于cost開銷（CostBaseOptimizer），它不是基于規(guī)則（Rule-BasedOptimizer），也不是基于語(yǔ)義，只是依據(jù)數(shù)值大小。另外，SQL語(yǔ)句是否使用索引，跟數(shù)據(jù)庫(kù)版本、數(shù)據(jù)量、數(shù)據(jù)選擇度都有關(guān)系。

本文我們嘗試總結(jié)索引失效的一些場(chǎng)景。我們會(huì)準(zhǔn)備class和student兩個(gè)表，class插入一萬(wàn)條數(shù)據(jù)，student插入50萬(wàn)條數(shù)據(jù)。環(huán)境是MySQL8.0，InnoDB。

【1】全值匹配我最愛(ài)

系統(tǒng)中經(jīng)常出現(xiàn)的SQL語(yǔ)句如下：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';

建立索引前執(zhí)行：

[SQL]SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
受影響的行: 0
時(shí)間: 0.308s

建立索引(age)：

CREATE INDEX idx_age ON student(age);

建立索引后執(zhí)行：

[SQL]SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
受影響的行: 0
時(shí)間: 0.113s

繼續(xù)創(chuàng)建索引(age,classId)：

CREATE INDEX idx_age_classid ON student(age,classId);

建立索引后執(zhí)行：

[SQL]SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
受影響的行: 0
時(shí)間: 0.007s

繼續(xù)創(chuàng)建索引(age,classId,NAME)：

CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);

建立索引后執(zhí)行：

[SQL]SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
受影響的行: 0
時(shí)間: 0.000s  # 其實(shí)必然不是0，只是更小了

從執(zhí)行計(jì)劃可以看到，MySQL會(huì)幫我們選擇最多包含查詢列的聯(lián)合索引。

【2】最佳左前綴法則

在MySQL建立聯(lián)合索引時(shí)會(huì)遵守最佳左前綴匹配原則，即最左優(yōu)先，在檢索數(shù)據(jù)時(shí)從聯(lián)合索引的最左邊開始匹配。

舉例：age、name可以用到索引。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd' ;

雖然可以正常使用，但是只有部分被使用到了。而且MySQL優(yōu)化器考慮的索引是idx_age，而非idx_age_classid_name。

舉例2：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.name = 'abcd';

可以看到，沒(méi)有age開頭 ，完全沒(méi)有用到索引。

舉例3：索引idx_age_classid_name還能否正常使用？

# MySQL會(huì)進(jìn)行優(yōu)化，形成age,classid,name以符合聯(lián)合索引idx_age_classid_name
EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE classid=4 AND student.age=30 AND student.name = 'abcd'; 

如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過(guò)索引中的列。

我們刪掉索引idx_age 、idx_age_classid 再次執(zhí)行查詢age and name，沒(méi)有中間的classid。

DROP INDEX idx_age ON student;
DROP INDEX idx_age_classid ON student;

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.name = 'abcd'; 

這里key_len=5，說(shuō)明只用到了聯(lián)合索引的一部分–age用到了索引。因?yàn)槠渲虚g環(huán)節(jié) classid不存在， 故而不能完全使用聯(lián)合索引。

結(jié)論 ： MySQL可以為多個(gè)字段創(chuàng)建索引，一個(gè)索引可以包括16個(gè)字段。對(duì)于多列索引，過(guò)濾條件要使用索引必須按照索引建立時(shí)的順序，依次滿足，一旦跳過(guò)某個(gè)字段，索引后面的字段都無(wú)法被使用。如果查詢條件中沒(méi)有使用這些字段中第一個(gè)字段時(shí)，多列（或聯(lián)合）索引不會(huì)被使用。

對(duì)于=值查詢，如果where中條件查詢沒(méi)有按照聯(lián)合索引字段順序編寫，MySQL優(yōu)化器會(huì)進(jìn)行調(diào)優(yōu)以使其滿足聯(lián)合索引字段順序。

【3】主鍵插入順序

對(duì)于一個(gè)使用InnoDB存儲(chǔ)引擎的表來(lái)說(shuō)，在我們沒(méi)有顯示的創(chuàng)建索引時(shí)，表中的數(shù)據(jù)實(shí)際上都是存儲(chǔ)在聚簇索引的葉子節(jié)點(diǎn)的。而記錄又是存儲(chǔ)在數(shù)據(jù)頁(yè)中的，數(shù)據(jù)頁(yè)和記錄又是按照記錄主鍵值從小到大的順序進(jìn)行排序。所以如果我們插入的記錄的主鍵值是依次增大的話，那我們每插滿一個(gè)數(shù)據(jù)頁(yè)就換到下一個(gè)數(shù)據(jù)頁(yè)繼續(xù)插。

而如果我們插入的主鍵值忽大忽小的話，就比較麻煩了。假設(shè)某個(gè)數(shù)據(jù)頁(yè)存儲(chǔ)的記錄已經(jīng)滿了，它存儲(chǔ)的主鍵值在1~100之間：

如果此時(shí)再插入一條主鍵值為9的記錄，那它插入的位置就如下圖：

可這個(gè)數(shù)據(jù)頁(yè)已經(jīng)滿了，再插進(jìn)來(lái)咋辦呢？我們需要把當(dāng)前頁(yè)面分裂成兩個(gè)頁(yè)面，把本頁(yè)中的一些記錄移動(dòng)到新創(chuàng)建的這個(gè)頁(yè)中。頁(yè)面分裂和記錄移位意味著什么？意味著性能損耗！ 所以如果我們想進(jìn)來(lái)避免這樣無(wú)謂的性能損耗，最好讓插入的記錄的主鍵值依次遞增，這樣就不會(huì)發(fā)生這樣的性能損耗了。

所以我們建議：讓主鍵具有AUTO_INCREMENT，讓存儲(chǔ)引擎自己為表生成主鍵，而不是我們手動(dòng)插入，比如person_info表：

create table person_info(
	id int unsigned not null auto_increment,
	name varchar(100) not null,
	birthday date not null,
	phone_numnber char(11) not null,
	country varchar(100) not null,
	primary key (id),
	key idx_name_bd_ph_num(name(10),birthday,phone_number)
)

我們自定義的主鍵列id擁有AUTO_INCREMENT屬性，在插入記錄時(shí)存儲(chǔ)引擎會(huì)自動(dòng)為我們填入自增的主鍵值。這樣的主鍵占用空間小，順序?qū)懭?，減少頁(yè)分裂。

【4】計(jì)算、函數(shù)、類型轉(zhuǎn)換(自動(dòng)或手動(dòng))導(dǎo)致索引失效

如下兩條SQL，哪個(gè)更好呢？其實(shí)是第一條，能夠使用到索引，第二條有了函數(shù)計(jì)算。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc'; 

我們創(chuàng)建索引(NAME)：

CREATE INDEX idx_name ON student(NAME);

查看第一條SQL的執(zhí)行計(jì)劃：

查看第二條SQL的執(zhí)行計(jì)劃：

對(duì)比執(zhí)行計(jì)劃可以看到，第一條SQL使用到了索引，第二條SQL的type=all表示全表掃描。說(shuō)明函數(shù)計(jì)算或?qū)е滤饕А?/p>

我們?cè)倏匆幌聰?shù)學(xué)計(jì)算：

CREATE INDEX idx_sno ON student(stuno);

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

如上圖所示，SQL中有數(shù)學(xué)計(jì)算，執(zhí)行計(jì)劃中 type=all表示沒(méi)有使用索引進(jìn)行了全表掃描。我們?cè)倏聪旅孢@個(gè)SQL，很顯然其會(huì)使用到索引。這就說(shuō)明數(shù)學(xué)計(jì)算會(huì)導(dǎo)致索引失效。

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

最后我們?cè)倏匆幌骂愋娃D(zhuǎn)換

字符串類型一定不要忘記單引號(hào)，否則索引失效。

# 會(huì)進(jìn)行隱式類型轉(zhuǎn)換 ，索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123; 

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = '123';

對(duì)比二者的執(zhí)行計(jì)劃可知，類型轉(zhuǎn)換會(huì)導(dǎo)致索引失效。

【5】范圍條件右邊的列索引失效

首先刪除表student的索引：

alter table student drop index idx_name;
alter table student drop index idx_age;
alter table student drop index idx_age_classid;

查看當(dāng)前索引：show index from student;

對(duì)于如下SQL，索引idx_age_classid_name還能夠正常使用嗎？

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.classId > 20 AND student.name = 'abc' ; 

執(zhí)行計(jì)劃如下所示，key_len=10，說(shuō)明只有age和classid用到了索引。

這時(shí)候即使交換次序，也是沒(méi)有意義的，如下所示：

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.name = 'abc' AND student.classId>20; 

那么如何使其能夠使用到索引呢？如下所示創(chuàng)建索引(age,NAME,classId)。

CREATE INDEX idx_age_name_cid ON student(age,NAME,classId);

這時(shí)再執(zhí)行上面SQL，可以看到充分用到了聯(lián)合索引。

對(duì)于 下面這個(gè)SQL，執(zhí)行計(jì)劃是一樣的。查詢優(yōu)化器對(duì)于and條件會(huì)進(jìn)行順序的調(diào)整，以滿足聯(lián)合索引的順序。

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.classId > 20 AND student.name = 'abc' ; 

總結(jié)

• 范圍右邊的列不能使用索引。比如 < 、<=、 >、 >=、 between。
• 這個(gè)右邊指的是聯(lián)合索引字段的右邊，至于SQL where中的and條件，查詢優(yōu)化器是可以進(jìn)行調(diào)整的。
• 創(chuàng)建的聯(lián)合索引中，務(wù)必把范圍涉及到的字段寫在最后。

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

為name字段創(chuàng)建索引：

CREATE INDEX idx_name ON student(NAME);

進(jìn)行等值判斷，正常使用索引：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name = 'abc' ;

對(duì)于不等判斷，查看索引是否失效：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc' ;

可以看到，兩條SQL均為使用到索引。

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

is null可以觸發(fā)索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL; 

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME IS NULL; 

is not null無(wú)法使用索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL; 

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME IS NOT NULL; 

結(jié)論： 最好在設(shè)計(jì)數(shù)據(jù)表的時(shí)候就將字段設(shè)置為not null約束，比如你可以將int類型的字段，默認(rèn)值設(shè)置為0.將字符類型的默認(rèn)值設(shè)置為空字符('') 。同理，在查詢中使用 not like 也無(wú)法使用索引，導(dǎo)致全表掃描。

【8】like以通配符%開頭索引失效

在使用like關(guān)鍵字進(jìn)行查詢的查詢語(yǔ)句中，如果匹配字符串的第一個(gè)字符為“%”，索引就不會(huì)起作用。只有"%"不在第一個(gè)位置，索引才會(huì)起作用。

使用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%'; 

沒(méi)有用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';

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

在where子句中，如果在or前的條件列進(jìn)行了索引，而在or后的條件列沒(méi)有進(jìn)行索引，那么索引會(huì)失效。也就是說(shuō)，OR 前后的兩個(gè)條件中的列都是索引列時(shí)，查詢中才會(huì)使用到索引。

因?yàn)镺R的含義就是兩個(gè)只要滿足一個(gè)即可，因此只有一個(gè)條件列進(jìn)行了索引是沒(méi)有意義的。只要有條件列沒(méi)有進(jìn)行索引，就會(huì)進(jìn)行全表掃描，因此索引的條件列也會(huì)失效。

SHOW INDEX FROM student;

# 刪除索引
alter table student drop index idx_age_classid_name;
alter table student drop index idx_age_name_cid;
alter table student drop index idx_sno;
alter table student drop index idx_name;

#創(chuàng)建索引
CREATE INDEX idx_age ON student(age);

這時(shí)我們查詢語(yǔ)句使用OR關(guān)鍵字的情況（age有索引，classid沒(méi)有索引）

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

可以看到，是沒(méi)有使用到索引的。如果我們?yōu)閏lassid創(chuàng)建索引呢？

CREATE INDEX idx_cid ON student(classid);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

可以看到，其使用到了索引，type=index_merge。簡(jiǎn)單來(lái)說(shuō)，index_merge就是對(duì)age和classid分別進(jìn)行了掃描，然后將這兩個(gè)結(jié)果集進(jìn)行了合并。這樣做的好處就是避免了全表掃描。

【10】數(shù)據(jù)庫(kù)和表的字符集統(tǒng)一使用utf8mb4

統(tǒng)一使用utf8mb4(5.5.3版本以上支持)兼容性更好，統(tǒng)一字符集可以避免由于字符集轉(zhuǎn)換產(chǎn)生的亂碼。

不同的字符集進(jìn)行比較前需要進(jìn)行轉(zhuǎn)換會(huì)造成索引失效。

一般性建議：

• 對(duì)于單列索引，盡量選擇針對(duì)當(dāng)前query過(guò)濾性更好的索引；
• 在選擇組合索引的時(shí)候，當(dāng)前query中過(guò)濾性最好的字段在索引字段順序中，位置越靠前越好；
• 在選擇組合索引的時(shí)候，盡量選擇能夠包含當(dāng)前query中的where子句中更多字段的索引；
• 在選擇組合索引的時(shí)候，如果某個(gè)字段可能出現(xiàn)范圍查詢時(shí)，盡量把這個(gè)字段放在索引次序的最后面。

總結(jié)

到此這篇關(guān)于MySQL調(diào)優(yōu)之索引在什么情況下會(huì)失效的文章就介紹到這了,更多相關(guān)MySQL索引失效內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論