欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

總結三道MySQL聯(lián)合索引面試題

 更新時間:2022年08月16日 10:17:00   作者:一燈架構???????  
這篇文章主要介紹了總結三道MySQL聯(lián)合索引面試題,眾所周知MySQL聯(lián)合索引遵循最左前綴匹配原則,在少數(shù)情況下也會不遵循,創(chuàng)建聯(lián)合索引的時候,建議優(yōu)先把區(qū)分度高的字段放在第一列

前言:

眾所周知MySQL聯(lián)合索引遵循最左前綴匹配原則,在少數(shù)情況下也會不遵循(有興趣,可以翻一下上篇文章

創(chuàng)建聯(lián)合索引的時候,建議優(yōu)先把區(qū)分度高的字段放在第一列。

至于怎么統(tǒng)計區(qū)分度,可以按照下面這種方式。

創(chuàng)建一張測試表,用來測試:

CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `a` int NOT NULL,
  `b` int NOT NULL,
  `c` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='測試表';

統(tǒng)計每個字段的區(qū)分度:

select 
    count(distinct a)/count(*), 
    count(distinct b)/count(*),
    count(distinct c)/count(*)
from test;

值越大,區(qū)分度越高,優(yōu)先放在第一列。

很多人不知道聯(lián)合索引在B+樹中是怎么存儲的?我簡單畫一下。

比如在(a,b)字段上面創(chuàng)建聯(lián)合索引,存儲結構類似下面這樣:

image.png

葉子節(jié)點存儲全部數(shù)據(jù),用雙鏈表指針相連,數(shù)據(jù)都是先按a字段排序,a字段的值相等時再按b字段排序。

a字段的值是全局有序的,分別有1,1,1,2,2,2。

b字段的值是全局無序的,分別有1,3,5,1,3,5,只有在a字段的值相等時才呈現(xiàn)出局部有序。

所以在進行SQL查詢的時候,如果where條件中沒有a字段,只有b字段,是無法用到索引的,像下面這樣:

select * from test where b=1;

像有些文章上面說的,在(a,b)兩個字段上創(chuàng)建聯(lián)合索引,就會創(chuàng)建兩個索引,分別是(a)和(a,b),這其實是一種不恰當?shù)谋硎?,雖然結果是對的。

下面做幾道聯(lián)合索引的經(jīng)典面試題,試一下大家掌握的怎么樣?

第一題:

下面這條SQL,該怎么創(chuàng)建聯(lián)合索引?

SELECT * FROM test WHERE a = 1 and b = 1 and c = 1;

你以為的答案是(a,b,c),其實答案是6個,abc三個的排列組合,(a,b,c)、(a,c,b)、(b,a,c)、(b,c,a)、(c,a,b)、(c,b,a)。

MySQL優(yōu)化器為了適應索引,會調(diào)整條件的順序。

再給面試官補充一句,區(qū)分度高的字段放在最前面,大大加分。

第二題:

下面這條SQL,該怎么創(chuàng)建聯(lián)合索引?

SELECT * FROM test WHERE a = 1 and b > 1 and c = 1;

考察的知識點是: 聯(lián)合索引遇到范圍匹配會停止,不會再匹配后面的索引字段。

所以答案應該是:(a,c,b)和 (c,a,b)。

當創(chuàng)建(a,c,b)和 (c,a,b)索引的時候,查詢會用到3個字段的索引,效率更高。

怎么判斷是用到了3個字段的索引,而不是只用到前兩個字段的索引呢?

有個非常簡單的方法,看執(zhí)行計劃的索引長度。

由于int類型的字段占4個字節(jié),3個字段長度剛好是12個字節(jié)。

第三題:

下面這條SQL,該怎么創(chuàng)建聯(lián)合索引

SELECT * FROM test WHERE a in (1,2,3) and b > 1;

答案是(a,b)。in條件查詢會被轉(zhuǎn)換成等值查詢,可以驗證一下:

可以看到用到了兩個字段的索引。

所以我們在平時做開發(fā),盡量想辦法把范圍查詢轉(zhuǎn)換成in條件查詢,效率更高。

到此這篇關于總結三道MySQL聯(lián)合索引面試題的文章就介紹到這了,更多相關MySQL聯(lián)合索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!

相關文章

最新評論