面試官:談談你對mysql聯合索引的認識?

引言

本文預計分為兩個部分:

(1)聯合索引部分的基礎知識在這個部分,我們溫習一下聯合索引的基礎(2)聯合索引部分的實戰題在這個部分,列舉幾個我認為算是實戰中的代表題,挑出來說說。

正文

基礎

講聯合索引,一定要扯最左匹配!放心,我不扯有的沒的,幾句話懂個大概就行!最左匹配所謂最左原則指的就是如果你的 SQL 語句中用到了聯合索引中的最左邊的索引,那麼這條 SQL 語句就可以利用這個聯合索引去進行匹配,值得注意的是,當遇到範圍查詢(>、

<code>a = 1
a = 1 and b = 2
/<code>

是可以匹配索引的。但是要注意的是~你執行

<code>b= 2 and a =1
/<code>

也是能匹配到索引的,因為Mysql有優化器會自動調整a,b的順序與索引順序一致。相反的,你執行

<code>b = 2
/<code>

就匹配不到索引了。而你對(a,b,c,d)建立索引,where後條件為

<code>a = 1 and b = 2 and c > 3 and d = 4 
/<code>

那麼,a,b,c三個字段能用到索引,而d就匹配不到。因為遇到了範圍查詢!

最左匹配的原理?假設,我們對(a,b)字段建立索引,那麼如下圖所示

面試官:談談你對mysql聯合索引的認識?

如圖所示他們是按照a來進行排序,在a相等的情況下,才按b來排序。

因此,我們可以看到a是有序的1,1,2,2,3,3。而b是一種全局無序,局部相對有序狀態!什麼意思呢?從全局來看,b的值為1,2,1,4,1,2,是無序的,因此直接執行b = 2這種查詢條件沒有辦法利用索引。

從局部來看,當a的值確定的時候,b是有序的。例如a = 1時,b值為1,2是有序的狀態。當a=2時候,b的值為1,4也是有序狀態。因此,你執行a = 1 and b = 2是a,b字段能用到索引的。而你執行a > 1 and b = 2時,a字段能用到索引,b字段用不到索引。因為a的值此時是一個範圍,不是固定的,在這個範圍內b值不是有序的,因此b字段用不上索引。

綜上所示,最左匹配原則,在遇到範圍查詢的時候,就會停止匹配。

實戰

OK,懂上面的基礎,我們就可以開始扯了~我舉了經典的五大題型,看完基本就懂!

題型一

如果sql為

<code>SELECT * FROM table WHERE a = 1 and b = 2 and c = 3;  

/<code>

如何建立索引?如果此題回答為對(a,b,c)建立索引,那都可以回去等通知了。此題正確答法是,(a,b,c)或者(c,b,a)或者(b,a,c)都可以,重點要的是將區分度高的字段放在前面,區分度低的字段放後面。像性別、狀態這種字段區分度就很低,我們一般放後面。

例如假設區分度由大到小為b,a,c。那麼我們就對(b,a,c)建立索引。在執行sql的時候,優化器會 幫我們調整where後a,b,c的順序,讓我們用上索引。

題型二

如果sql為

<code>SELECT * FROM table WHERE a > 1 and b = 2; 
/<code>

如何建立索引?如果此題回答為對(a,b)建立索引,那都可以回去等通知了。此題正確答法是,對(b,a)建立索引。如果你建立的是(a,b)索引,那麼只有a字段能用得上索引,畢竟最左匹配原則遇到範圍查詢就停止匹配。如果對(b,a)建立索引那麼兩個字段都能用上,優化器會幫我們調整where後a,b的順序,讓我們用上索引。

題型三

如果sql為

<code>SELECT * FROM `table` WHERE a > 1 and b = 2 and c > 3; 
/<code>

如何建立索引?此題回答也是不一定,(b,a)或者(b,c)都可以,要結合具體情況具體分析。

拓展一下

<code>SELECT * FROM `table` WHERE a = 1 and b = 2 and c > 3; 
/<code>

怎麼建索引?嗯,大家一定都懂了!

題型四

<code>SELECT * FROM `table` WHERE a = 1 ORDER BY b;
/<code>

如何建立索引?這還需要想?一看就是對(a,b)建索引,當a = 1的時候,b相對有序,可以避免再次排序!那麼

<code>SELECT * FROM `table` WHERE a > 1 ORDER BY b; 
/<code>

如何建立索引?對(a)建立索引,因為a的值是一個範圍,這個範圍內b值是無序的,沒有必要對(a,b)建立索引。

拓展一下

<code>SELECT * FROM `table` WHERE a = 1 AND b = 2 AND c > 3 ORDER BY c;
/<code>

怎麼建索引?

題型五

<code>SELECT * FROM `table` WHERE a IN (1,2,3) and b > 1; /<code>

如何建立索引?還是對(a,b)建立索引,因為IN在這裡可以視為等值引用,不會中止索引匹配,所以還是(a,b)!

拓展一下

<code>SELECT * FROM `table` WHERE a = 1 AND b IN (1,2,3) AND c > 3 ORDER BY c;/<code>

如何建立索引?此時c排序是用不到索引的。

總結

希望大家在面試的時候遇到聯合索引的題目,能夠答出來吧


分享到:


相關文章: