一條 SQL 語(yǔ)句引發(fā)的思考

時(shí)間：2021-07-25 14:10:37

關(guān)鍵字： SQL 最左匹配原則索引

手機(jī)看文章

掃描二維碼
隨時(shí)隨地手機(jī)看文章

[導(dǎo)讀]為什么 select * from t where c = 0;這條不符合聯(lián)合索引的最左匹配原則的查詢語(yǔ)句走了索引查詢呢？

大家好，我是小林。昨晚有位讀者問了我這個(gè)問題：

他創(chuàng)建了一張數(shù)據(jù)庫(kù)表，表里的字段只有主鍵索引（

id）和聯(lián)合索引（a，b，c），然后他執(zhí)行的 select * from t where c = 0;這條語(yǔ)句發(fā)現(xiàn)走的是索引，他就感覺很困惑，困惑在于兩點(diǎn)：

 

	
		第一點(diǎn)， where c 這個(gè)條件并不符合聯(lián)合索引的最左匹配原則，怎么就查詢的時(shí)候走了索引呢？

 
	
	
		第二點(diǎn)，在這個(gè)數(shù)據(jù)表加了非索引字段，執(zhí)行同樣的查詢語(yǔ)句后，怎么變成走的是全表掃描呢？

 
	

我先跟大家解釋下，什么是最左匹配原則？

這個(gè)數(shù)據(jù)庫(kù)表創(chuàng)建了（a，b，c）這個(gè)聯(lián)合索引，要能使其生效必須保證 where 條件里最左邊是 a 字段，比如以下這幾種情況：

 

	
		where a = 0;

 
	
	
		where a = 0 and b = 0;

 
	
	
		where a = 0 and c = 0;

 
	
	
		where a = 0 and b = 0 and c = 0;

 
	
	
		where a = 0 and c = 0 and b = 0;

 
	

而如果 where 條件里最左邊的字段不是 a 時(shí)，就無(wú)法使用到聯(lián)合索引，比如以下這種情況，就是不符合最左匹配規(guī)則：

 

	
		where b = 0;

 
	
	
		where c = 0;

 
	
	
		where b = 0 and c =0;

 
	
	
		where c = 0 and b = 0;

 
	

知道了聯(lián)合索引的最左匹配原則后，再來看看第一個(gè)問題。

為什么 select * from t where c = 0;這條不符合聯(lián)合索引的最左匹配原則的查詢語(yǔ)句走了索引查詢呢？

剛開始看到這個(gè)問題的時(shí)候，我一時(shí)也想不到原因，只能大概猜測(cè)這條語(yǔ)句可以覆蓋索引，所以就走了索引查詢。

今早我就發(fā)了個(gè)朋友圈，因?yàn)槲遗笥讶τ胁畈欢?1W 人，覺得朋友圈肯定有大佬能解答這個(gè)問題。

 

果然朋友圈大佬真的多，一個(gè)上午就有 50多個(gè)人留言解答了這個(gè)問題，我看完后思路也清晰了。

我也把解答思路整理了下，這里貼出來。

首先，這張表的字段沒有「非索引」字段，所以 select *相當(dāng)于 select id,a,b,c，然后這個(gè)查詢的內(nèi)容和條件
都在聯(lián)合索引樹里，因?yàn)槁?lián)合索引樹的葉子節(jié)點(diǎn)包含「索引列 主鍵」，所以查聯(lián)合索引樹就能查到全部結(jié)果了，這個(gè)就是覆蓋索引。

但是執(zhí)行計(jì)劃里的 type 是 index，這代表著是通過全掃描聯(lián)合索引樹的方式查詢到數(shù)據(jù)的，這是因?yàn)?where c并不符合聯(lián)合索引最左匹配原則。

那么，如果寫了個(gè)符合最左原則的 select 語(yǔ)句，那么 type 就是 ref，這個(gè)效率就比 index 全掃描要高一些。

那為什么選擇全掃描聯(lián)合索引樹，而不掃描全表（聚集索引樹）呢？

因?yàn)槁?lián)合索引樹的記錄比要小的多，而且這個(gè) select * 不用執(zhí)行回表操作，所以直接遍歷聯(lián)合索引樹要比遍歷聚集索引樹要小的多，因此 MySQL 選擇了全掃描聯(lián)合索引樹。

再來回答第二個(gè)問題。

為什么這個(gè)數(shù)據(jù)表加了非索引字段，執(zhí)行同樣的查詢語(yǔ)句后，怎么變成走的是全表掃描呢？

因?yàn)榧恿似渌侄魏螅?/span>select * from t where c = 0;查詢的內(nèi)容就不能在聯(lián)合索引樹里找到了，而且條件也不符合最左匹配原則，這樣既不能覆蓋索引也不能執(zhí)行回表操作，所以這時(shí)只能通過掃描全表來查詢到所有的數(shù)據(jù)。

好了，問題就說完了，不知道大家 get 到了嗎？

這篇說的比較粗略，沒有詳細(xì)介紹一些索引的概念，比如聚集索引、聯(lián)合表索引、覆蓋索引、回表操作這些東西。

可能沒有點(diǎn)索引基礎(chǔ)的同學(xué)看的有點(diǎn)懵逼，小林后面在出一篇更詳細(xì)的。


                
            欲知詳情，請(qǐng)下載word文檔 下載文檔

一條 SQL 語(yǔ)句引發(fā)的思考

阿維塔、賽力斯已入股！華為引望可能成“中國(guó)博世”

Trianz與AWS達(dá)成戰(zhàn)略合作協(xié)議，徹底改變?cè)撇捎煤凸芾矸绞?/a>

人工智能驅(qū)動(dòng)工具SODA V將顛覆汽車市場(chǎng)，使汽車開發(fā)時(shí)間和成本降低90%

從容應(yīng)對(duì)未知風(fēng)險(xiǎn)----解密亞馬遜云科技的韌性之道

中國(guó)游戲市場(chǎng)開始復(fù)蘇！騰訊、網(wǎng)易等巨頭縮減在日本投資

獨(dú)立自主！華為董事：致力打造不依賴西方的技術(shù)

華為張平安：數(shù)字世界話語(yǔ)權(quán)最終由生態(tài)繁榮決定！

中國(guó)通信服務(wù)公布2024年中期業(yè)績(jī)

NVI技術(shù)創(chuàng)新聯(lián)盟成立！自主生態(tài)將帶動(dòng)產(chǎn)業(yè)鏈高速發(fā)展

軟通動(dòng)力與長(zhǎng)三角投資達(dá)成戰(zhàn)略合作共謀數(shù)字生態(tài)新發(fā)展

海南區(qū)6家凱悅系酒店與嵐圖達(dá)成戰(zhàn)略合作，共同推動(dòng)新能源出行體驗(yàn)

安嵐攜手妮可?巴菲特開啟療愈之旅在秋日紅葉的浪漫中療愈身心

不懼美國(guó)封鎖！華為：我們給大家提供系統(tǒng)、存儲(chǔ)等

尼爾森IQ深耕中國(guó)四十載，共繪未來新篇章

第二十二屆跨盈年度B2B營(yíng)銷高管峰會(huì)2025聚焦"營(yíng)銷競(jìng)?cè)?，打破市?chǎng)內(nèi)卷實(shí)現(xiàn)認(rèn)知進(jìn)化"

恒久動(dòng)力馳騁天地美孚1號(hào)攜手周冠宇邀您縱擎馳騁，勁享駕趣體驗(yàn)

美通社母公司Cision發(fā)布CisionOne平臺(tái)，進(jìn)軍亞太地區(qū)媒體監(jiān)測(cè)市場(chǎng)

移遠(yuǎn)通信推出大模型解決方案，重塑千行百業(yè)智能邊界

高途公布2024年第二季度未經(jīng)審計(jì)業(yè)績(jī)

華為發(fā)布AI百校計(jì)劃：培養(yǎng)AI人才每年獲最高100萬(wàn)支持