MySQL最左匹配原則詳細(xì)分析
闡述
通常我們在建立聯(lián)合索引的時候,相信建立過索引的同學(xué)們會發(fā)現(xiàn),無論是Oracle 還是 MySQL 都會讓我們選擇索引的順序,比如我們想在 a,b,c
三個字段上建立一個聯(lián)合索引,我們可以選擇自己想要的優(yōu)先級,(a、b、c)
,或是 (b、a、c)
或者是 (c、a、b)
等順序。
為什么數(shù)據(jù)庫會讓我們選擇字段的順序呢?
不都是三個字段的聯(lián)合索引么?
這里就引出了數(shù)據(jù)庫索引的最重要的原則之一,最左匹配原則。
在我們開發(fā)中經(jīng)常會遇到這種問題,明明這個字段建了聯(lián)合索引,但是SQL查詢該字段時卻不會使用這個索引。難道這索引是假的?
比如索引 abc_index:(a,b,c)
是 a,b,c
三個字段的聯(lián)合索引,下列 sql 執(zhí)行時都無法命中索引 abc_index
;
select * from table where c = '1'; select * from table where b ='1' and c ='2';
以下三種情況卻會走索引:
select * from table where a = '1'; select * from table where a = '1' and b = '2'; select * from table where a = '1' and b = '2' and c='3';
從上面兩個例子大家有木有看出點(diǎn)眉目呢?
是的,索引 abc_index:(a,b,c)
,只會在 where 條件中帶有 (a)、(a,b)、(a,b,c)
的三種類型的查詢中使用。
其實(shí)這里說的有一點(diǎn)歧義,其實(shí)當(dāng) where 條件只有 (a,c)
時也會走,但是只走a字段索引,不會走 c 字段。
那么這都是為什么呢?
我們一起來看看其原理吧。
一、最左匹配原則的原理
MySQL 建立多列索引(聯(lián)合索引)有最左匹配的原則,即最左優(yōu)先:
如果有一個 2 列的索引 (a, b),則已經(jīng)對 (a)、(a, b) 上建立了索引; 如果有一個 3 列索引 (a, b, c),則已經(jīng)對 (a)、(a, b)、(a, b, c) 上建立了索引;
假設(shè)數(shù)據(jù)表 LOL (id,sex,price,name)
的物理位置(表中的無序數(shù)據(jù))如下:
(注:下面數(shù)據(jù)是測試少量數(shù)據(jù)選用的,只為了方便大家看清楚。實(shí)際操作中,應(yīng)按照使用頻率、數(shù)據(jù)區(qū)分度來綜合設(shè)定索引順序喔~)
主鍵id sex(a) price(b) name(c)
(1) 1 1350 AAA安妮
(2) 2 6300 MMM盲僧
(3) 1 3150 NNN奈德麗
(4) 2 6300 CCC錘石
(5) 1 6300 LLL龍女
(6) 2 3150 EEE伊澤瑞爾
(7) 2 6300 III艾克
(8) 1 6300 BBB暴走蘿莉
(9) 1 4800 FFF發(fā)條魔靈
(10) 2 3150 KKK卡牌大師
(11) 1 450 HHH寒冰射手
(12) 2 450 GGG蓋倫
(13) 2 3150 OOO小提莫
(14) 2 3150 DDD刀鋒之影
(15) 2 6300 JJJ疾風(fēng)劍豪
(16) 2 450 JJJ劍圣
當(dāng)你在 LOL 表創(chuàng)建一個聯(lián)合索引 abc_index:(sex,price,name)
時,生成的 索引文件邏輯上等同于下表內(nèi)容(分級排序):
sex(a) price(b) name(c) 主鍵id
1 450 HHH寒冰射手 (11)
1 1350 AAA安妮 (1)
1 3150 NNN奈德麗 (3)
1 4800 FFF發(fā)條魔靈 (9)
1 6300 BBB暴走蘿莉 (8)
1 6300 LLL龍女 (5)
2 450 GGG蓋倫 (12)
2 450 JJJ劍圣 (16)
2 3150 DDD刀鋒之影 (14)
2 3150 EEE伊澤瑞爾 (6)
2 3150 KKK卡牌大師 (10)
2 3150 OOO小提莫 (13)
2 6300 CCC錘石 (4)
2 6300 III艾克 (7)
2 6300 JJJ疾風(fēng)劍豪 (15)
2 6300 MMM盲僧 (2)
小伙伴兒們有沒有發(fā)現(xiàn)B+樹聯(lián)合索引的規(guī)律?
感覺還有點(diǎn)模糊的話,那咱們再來看一張索引存儲數(shù)據(jù)的結(jié)構(gòu)圖,或許更明了一些。
B+樹中的聯(lián)合索引,每級索引都是排好序的。
聯(lián)合索引 bcd_index:(b,c,d) , 在索引樹中的樣子如圖 , 在比較的過程中 ,先判斷 b 再判斷 c 然后是 d 。
由上圖可以看出,B+ 樹的數(shù)據(jù)項(xiàng)是復(fù)合的數(shù)據(jù)結(jié)構(gòu),同樣,對于我們這張表的聯(lián)合索引 (sex,price,name)來說 ,B+ 樹也是按照從左到右的順序來建立搜索樹的,當(dāng)SQL如下時:
select sex,price,name from LOL where sex = 2 and price = 6300 and name = 'JJJ疾風(fēng)劍豪';
B+ 樹會優(yōu)先比較 sex 來確定下一步的指針?biāo)逊较颍绻?sex 相同再依次比較 price 和 name,最后得到檢索的數(shù)據(jù);
二、違背最左原則導(dǎo)致索引失效的情況
(下面以聯(lián)合索引 abc_index:(a,b,c) 來進(jìn)行講解,便于理解)
1、查詢條件中,缺失優(yōu)先級最高的索引 “a”
當(dāng) where b = 6300 and c = ‘JJJ疾風(fēng)劍豪’ 這種沒有以 a 為條件來檢索時;B+樹就不知道第一步該查哪個節(jié)點(diǎn),從而需要去全表掃描了(即不走索引)。
因?yàn)榻⑺阉鳂涞臅r候 a 就是第一個比較因子,必須要先根據(jù) a 來搜索,進(jìn)而才能往后繼續(xù)查詢b 和 c,這點(diǎn)我們通過上面的存儲結(jié)構(gòu)圖可以看明白。
2、查詢條件中,缺失優(yōu)先級居中的索引 “b”
當(dāng) where a =1 and c =“JJJ疾風(fēng)劍豪” 這樣的數(shù)據(jù)來檢索時;B+ 樹可以用 a 來指定第一步搜索方向,但由于下一個字段 b 的缺失,所以只能把 a = 1 的數(shù)據(jù)主鍵ID都找到,通過查到的主鍵ID回表查詢相關(guān)行,再去匹配 c = ‘JJJ疾風(fēng)劍豪’ 的數(shù)據(jù)了,當(dāng)然,這至少把 a = 1 的數(shù)據(jù)篩選出來了,總比直接全表掃描好多了。
這就是MySQL非常重要的原則,即索引的最左匹配原則。
三、查詢優(yōu)化器偷偷干了哪些事兒
當(dāng)對索引中所有列通過 “=” 或 “IN” 進(jìn)行精確匹配時,索引都可以被用到。
1、如果建的索引順序是 (a, b)。而查詢的語句是 where b = 1 AND a = ‘aaa’; 為什么還能利用到索引?
理論上索引對順序是敏感的,但是由于 MySQL 的查詢優(yōu)化器會自動調(diào)整 where 子句的條件順序以使用適合的索引,所以 MySQL 不存在 where 子句的順序問題而造成索引失效。當(dāng)然了,SQL書寫的好習(xí)慣要保持,這也能讓其他同事更好地理解你的SQL。
2、還有一個特殊情況說明下,下面這種類型的SQL, a 與 b 會走索引,c不會走。
select * from LOL where a = 2 and b > 1000 and c='JJJ疾風(fēng)劍豪';
對于上面這種類型的sql語句;
mysql會一直向右匹配直到遇到范圍查詢 (>、<、between、like)
就停止匹配(包括like '陳%'這種)。
在a、b走完索引后,c已經(jīng)是無序了,所以c就沒法走索引,優(yōu)化器會認(rèn)為還不如全表掃描c字段來的快。所以只使用了(a,b)兩個索引,影響了執(zhí)行效率。
其實(shí),這種場景可以通過修改索引順序?yàn)?abc_index:(a,c,b),就可以使三個索引字段都用到索引,建議小伙伴們不要有問題就想著新增索引哦,浪費(fèi)資源還增加服務(wù)器壓力。
綜上,如果通過調(diào)整順序,就可以解決問題或少維護(hù)一個索引,那么這個順序往往就是我們DBA人員需要優(yōu)先考慮采用的。
四、知識點(diǎn)
1、如何通過有序索引排序,避免冗余執(zhí)行 order by
order by 用在 select 語句中,具備排序功能。如:
SELECT sex, price, name FROM LOL ORDER BY sex;
是將表 LOL 中的數(shù)據(jù)按 “sex” 一列排序。
而只有當(dāng) order by 與 where 語句同時出現(xiàn),order by 的排序功能無效。
換句話說,order by 中的字段在執(zhí)行計劃中利用了索引時,不用排序操作。如下SQL時,不會按 sex 一列排序,因?yàn)?sex 本身已經(jīng)是有序的了。
SELECT sex, price, name FROM LOL where sex = 1 ORDER BY sex ;
所以,只有 order by 字段出現(xiàn)在 where 條件中時,才會利用該字段的索引而避免排序。
對于上面的語句,數(shù)據(jù)庫的處理順序是:
第一步:根據(jù) where 條件和統(tǒng)計信息生成執(zhí)行計劃,得到數(shù)據(jù)。
第二步:將得到的數(shù)據(jù)排序。當(dāng)執(zhí)行處理數(shù)據(jù)(order by)時,數(shù)據(jù)庫會先查看第一步的執(zhí)行計劃,看 order by 的字段是否在執(zhí)行計劃中利用了索引。如果是,則可以利用索引順序而直接取得已經(jīng)排好序的數(shù)據(jù)。如果不是,則排序操作。
第三步:返回排序后的數(shù)據(jù)。
2、like 語句的索引問題
如果通配符 % 不出現(xiàn)在開頭,則可以用到索引,但根據(jù)具體情況不同可能只會用其中一個前綴,在 like “value%” 可以使用索引,但是 like “%value%” 違背了最左匹配原則,不會使用索引,走的是全表掃描。
3、不要在列上進(jìn)行運(yùn)算
如果查詢條件中含有函數(shù)或表達(dá)式,將導(dǎo)致索引失效而進(jìn)行全表掃描
例如 :
select * from user where YEAR(birthday) < 1990
可以改造成:
select * from users where birthday <'1990-01-01′
4、索引不會包含有 NULL 值的列
只要列中包含有 NULL 值都將不會被包含在索引中,復(fù)合索引中只要有一列含有 NULL 值,那么這一列對于此復(fù)合索引就是無效的。所以在數(shù)據(jù)庫設(shè)計時不要讓字段的默認(rèn)值為 NULL
5、盡量選擇區(qū)分度高的列作為索引
區(qū)分度的公式是count(distinct col)/count(*),表示字段不重復(fù)的比例,比例越大我們掃描的記錄數(shù)越少,唯一鍵的區(qū)分度是 1,而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是 0。一般需要 join 的字段都要求區(qū)分度 0.1 以上,即平均 1 條掃描 10 條記錄
6、覆蓋索引的好處
如果一個索引包含所有需要的查詢的字段的值,我們稱之為覆蓋索引。覆蓋索引是非常有用的工具,能夠極大的提高性能。因?yàn)?,只需要讀取索引,而無需讀表,極大減少數(shù)據(jù)訪問量,這也是不建議使用Select * 的原因。
到此這篇關(guān)于MySQL最左匹配原則詳細(xì)分析的文章就介紹到這了,更多相關(guān)MySQL最左匹配內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
mysql ERROR 1045 (28000)問題的解決方法
這篇文章主要介紹了mysql ERROR 1045 (28000)問題的解決方法,文中步驟介紹的非常詳細(xì),具有一定的參考價值,感興趣的小伙伴們可以參考一下2019-10-10MySQL與PHP的基礎(chǔ)與應(yīng)用專題之?dāng)?shù)據(jù)控制
MySQL是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng),由瑞典MySQL?AB?公司開發(fā),屬于?Oracle?旗下產(chǎn)品。MySQL?是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一,本系列將帶你掌握php與mysql的基礎(chǔ)應(yīng)用,本篇帶你了解數(shù)據(jù)控制2022-02-02mysql8.0?lower_case_table_names?大小寫敏感設(shè)置問題解決
在默認(rèn)情況下,這個變量是設(shè)置為0的,以保持向前兼容性,如果將該變量設(shè)置為1,則表名和數(shù)據(jù)庫名將被區(qū)分大小寫,本文主要介紹了mysql8.0?lower_case_table_names?大小寫敏感設(shè)置問題解決,感興趣的可以了解一下2023-09-09Mysql 常用的時間日期及轉(zhuǎn)換函數(shù)小結(jié)
本文是腳本之家小編給大家總結(jié)的一些常用的mysql時間日期以及轉(zhuǎn)換函數(shù),非常不錯,具有一定的參考借鑒價值,需要的朋友參考下吧2018-05-05詳解MySQL執(zhí)行原理、邏輯分層、更改數(shù)據(jù)庫處理引擎
在本文里我們給大家總結(jié)了關(guān)于MySQL執(zhí)行原理、邏輯分層、更改數(shù)據(jù)庫處理引擎的相關(guān)知識點(diǎn),需要的讀者們一起學(xué)習(xí)下。2019-02-02win11設(shè)置mysql開機(jī)自啟的實(shí)現(xiàn)方法
本文主要介紹了win11設(shè)置mysql開機(jī)自啟的實(shí)現(xiàn)方法,要通過命令行方式設(shè)置,具有一定的參考價值,感興趣的可以了解一下2024-03-03