MySQL索引相關(guān)面試演練

0.準(zhǔn)備

#1.創(chuàng)建test表（測(cè)試表）。

drop?table?if?exists?test;
create?table?test(
id?int?primary?key?auto_increment,
c1?varchar(10),
c2?varchar(10),
c3?varchar(10),
c4?varchar(10),
c5?varchar(10)
)?ENGINE=INNODB?default?CHARSET=utf8;

insert?into?test(c1,c2,c3,c4,c5)?values('a1','a2','a3','a4','a5');
insert?into?test(c1,c2,c3,c4,c5)?values('b1','b2','b3','b4','b5');
insert?into?test(c1,c2,c3,c4,c5)?values('c1','c2','c3','c4','c5');
insert?into?test(c1,c2,c3,c4,c5)?values('d1','d2','d3','d4','d5');
insert?into?test(c1,c2,c3,c4,c5)?values('e1','e2','e3','e4','e5');

#2.創(chuàng)建索引。

1.根據(jù)以下Case分析索引的使用情況

Case 1：

分析：

①創(chuàng)建復(fù)合索引的順序?yàn)閏1,c2,c3,c4。

②上述四組explain執(zhí)行的結(jié)果都一樣：type=ref，key_len=132，ref=const,const,const,const。

結(jié)論：在執(zhí)行常量等值查詢時(shí)，改變索引列的順序并不會(huì)更改explain的執(zhí)行結(jié)果，因?yàn)閙ysql底層優(yōu)化器會(huì)進(jìn)行優(yōu)化，但是推薦按照索引順序列編寫(xiě)sql語(yǔ)句。

Case 2：

分析：

當(dāng)出現(xiàn)范圍的時(shí)候，type=range，key_len=99，比不用范圍key_len=66增加了，說(shuō)明使用上了索引，但對(duì)比Case1中執(zhí)行結(jié)果，說(shuō)明c4上索引失效。

結(jié)論：范圍右邊索引列失效，但是范圍當(dāng)前位置（c3）的索引是有效的，從key_len=99可證明。

Case 2.1：

分析：

與上面explain執(zhí)行結(jié)果對(duì)比，key_len=132說(shuō)明索引用到了4個(gè)，因?yàn)閷?duì)此sql語(yǔ)句mysql底層優(yōu)化器會(huì)進(jìn)行優(yōu)化：范圍右邊索引列失效（c4右邊已經(jīng)沒(méi)有索引列了），注意索引的順序（c1,c2,c3,c4），所以c4右邊不會(huì)出現(xiàn)失效的索引列，因此4個(gè)索引全部用上。

結(jié)論：范圍右邊索引列失效，是有順序的：c1,c2,c3,c4，如果c3有范圍，則c4失效；如果c4有范圍，則沒(méi)有失效的索引列，從而會(huì)使用全部索引。

Case 2.2：

分析：

如果在c1處使用范圍，則type=ALL，key=Null，索引失效，全表掃描，這里違背了最佳左前綴法則，帶頭大哥已死，因?yàn)閏1主要用于范圍，而不是查詢。

解決方式使用覆蓋索引。

結(jié)論：在最佳左前綴法則中，如果最左前列（帶頭大哥）的索引失效，則后面的索引都失效。

Case 3：

分析：

利用最佳左前綴法則：中間兄弟不能斷，因此用到了c1和c2索引（查找），從key_len=66，ref=const,const，c3索引列用在排序過(guò)程中。

Case 3.1：

分析：

從explain的執(zhí)行結(jié)果來(lái)看：key_len=66，ref=const,const，從而查找只用到c1和c2索引，c3索引用于排序。

Case 3.2：

分析：

從explain的執(zhí)行結(jié)果來(lái)看：key_len=66，ref=const,const，查詢使用了c1和c2索引，由于用了c4進(jìn)行排序，跳過(guò)了c3，出現(xiàn)了Using filesort。

Case 4：

分析：

查找只用到索引c1，c2和c3用于排序，無(wú)Using filesort。

Case 4.1：

分析：

和Case 4中explain的執(zhí)行結(jié)果一樣，但是出現(xiàn)了Using filesort，因?yàn)樗饕膭?chuàng)建順序?yàn)閏1,c2,c3,c4，但是排序的時(shí)候c2和c3顛倒位置了。

Case 4.2：

分析：

在查詢時(shí)增加了c5，但是explain的執(zhí)行結(jié)果一樣，因?yàn)閏5并未創(chuàng)建索引。

Case 4.3：

分析：

與Case 4.1對(duì)比，在Extra中并未出現(xiàn)Using filesort，因?yàn)閏2為常量，在排序中被優(yōu)化，所以索引未顛倒，不會(huì)出現(xiàn)Using filesort。

Case 5：

分析：

只用到c1上的索引，因?yàn)閏4中間間斷了，根據(jù)最佳左前綴法則，所以key_len=33，ref=const，表示只用到一個(gè)索引。

Case 5.1：

分析：

對(duì)比Case 5，在group by時(shí)交換了c2和c3的位置，結(jié)果出現(xiàn)Using temporary和Using filesort，極度惡劣。原因：c3和c2與索引創(chuàng)建順序相反。

總結(jié)：

通過(guò)以上Case的分析，進(jìn)行如下總結(jié)：

①最佳左前綴法則。

1. 在等值查詢時(shí)，更改索引列順序，并不會(huì)影響explain的執(zhí)行結(jié)果，因?yàn)閙ysql底層會(huì)進(jìn)行優(yōu)化。

2. 在使用order by時(shí)，注意索引順序、常量，以及可能會(huì)導(dǎo)致Using filesort的情況。

②group by容易產(chǎn)生Using temporary。

③通俗理解口訣：

全值匹配我最愛(ài)，最左前綴要遵守；

帶頭大哥不能死，中間兄弟不能斷；

索引列上少計(jì)算，范圍之后全失效；

LIKE百分寫(xiě)最右，覆蓋索引不寫(xiě)星；

不等空值還有or，索引失效要少用。