前言

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

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

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

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

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

【1】全值匹配我最愛

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

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
時間: 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
時間: 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
時間: 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
時間: 0.000s  # 其實必然不是0，只是更小了

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

【2】最佳左前綴法則

在MySQL建立聯(lián)合索引時會遵守最佳左前綴匹配原則，即最左優(yōu)先，在檢索數(shù)據(jù)時從聯(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';

可以看到，沒有age開頭 ，完全沒有用到索引。

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

# MySQL會進行優(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'; 

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

我們刪掉索引idx_age 、idx_age_classid 再次執(zhí)行查詢age and name，沒有中間的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，說明只用到了聯(lián)合索引的一部分–age用到了索引。因為其中間環(huán)節(jié) classid不存在， 故而不能完全使用聯(lián)合索引。

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

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

【3】主鍵插入順序

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

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

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

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

所以我們建議：讓主鍵具有AUTO_INCREMENT，讓存儲引擎自己為表生成主鍵，而不是我們手動插入，比如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屬性，在插入記錄時存儲引擎會自動為我們填入自增的主鍵值。這樣的主鍵占用空間小，順序?qū)懭?，減少頁分裂。

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

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

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í)行計劃：

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

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

我們再看一下數(shù)學計算：

CREATE INDEX idx_sno ON student(stuno);

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

如上圖所示，SQL中有數(shù)學計算，執(zhí)行計劃中 type=all表示沒有使用索引進行了全表掃描。我們再看下面這個SQL，很顯然其會使用到索引。這就說明數(shù)學計算會導致索引失效。

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

最后我們再看一下類型轉(zhuǎn)換

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

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

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

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

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

首先刪除表student的索引：

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

查看當前索引：show index from student;

對于如下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í)行計劃如下所示，key_len=10，說明只有age和classid用到了索引。

這時候即使交換次序，也是沒有意義的，如下所示：

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

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

對于 下面這個SQL，執(zhí)行計劃是一樣的。查詢優(yōu)化器對于and條件會進行順序的調(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。
• 這個右邊指的是聯(lián)合索引字段的右邊，至于SQL where中的and條件，查詢優(yōu)化器是可以進行調(diào)整的。
• 創(chuàng)建的聯(lián)合索引中，務必把范圍涉及到的字段寫在最后。

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

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

CREATE INDEX idx_name ON student(NAME);

進行等值判斷，正常使用索引：

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

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

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無法使用索引

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無法使用索引

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è)計數(shù)據(jù)表的時候就將字段設(shè)置為not null約束，比如你可以將int類型的字段，默認值設(shè)置為0.將字符類型的默認值設(shè)置為空字符('') 。同理，在查詢中使用 not like 也無法使用索引，導致全表掃描。

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

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

使用到索引

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

沒有用到索引

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

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

在where子句中，如果在or前的條件列進行了索引，而在or后的條件列沒有進行索引，那么索引會失效。也就是說，OR 前后的兩個條件中的列都是索引列時，查詢中才會使用到索引。

因為OR的含義就是兩個只要滿足一個即可，因此只有一個條件列進行了索引是沒有意義的。只要有條件列沒有進行索引，就會進行全表掃描，因此索引的條件列也會失效。

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

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

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

可以看到，是沒有使用到索引的。如果我們?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。簡單來說，index_merge就是對age和classid分別進行了掃描，然后將這兩個結(jié)果集進行了合并。這樣做的好處就是避免了全表掃描。

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

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

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

一般性建議：

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

總結(jié)

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

最新評論