當(dāng)前位置：首頁 > 公眾號精選 > 小林coding

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

時間：2021-07-25 14:10:37
關(guān)鍵字： SQL    最左匹配原則    索引   
手機(jī)看文章

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

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

大家好，我是小林。昨晚有位讀者問了我這個問題：
他創(chuàng)建了一張數(shù)據(jù)庫表，表里的字段只有主鍵索引（id）和聯(lián)合索引（a，b，c），然后他執(zhí)行的 select * from t where c = 0;這條語句發(fā)現(xiàn)走的是索引，他就感覺很困惑，困惑在于兩點(diǎn)：
  • 第一點(diǎn)， where c 這個條件并不符合聯(lián)合索引的最左匹配原則，怎么就查詢的時候走了索引呢？
  • 第二點(diǎn)，在這個數(shù)據(jù)表加了非索引字段，執(zhí)行同樣的查詢語句后，怎么變成走的是全表掃描呢？
我先跟大家解釋下，什么是最左匹配原則？
這個數(shù)據(jù)庫表創(chuàng)建了（a，b，c）這個聯(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 時，就無法使用到聯(lián)合索引，比如以下這種情況，就是不符合最左匹配規(guī)則：
  • where b = 0;
  • where c = 0;
  • where b = 0 and c =0;
  • where c = 0 and b = 0;
知道了聯(lián)合索引的最左匹配原則后，再來看看第一個問題。
為什么 select * from t where c = 0;這條不符合聯(lián)合索引的最左匹配原則的查詢語句走了索引查詢呢？
剛開始看到這個問題的時候，我一時也想不到原因，只能大概猜測這條語句可以覆蓋索引，所以就走了索引查詢。
今早我就發(fā)了個朋友圈，因?yàn)槲遗笥讶τ胁畈欢?1W 人，覺得朋友圈肯定有大佬能解答這個問題。
果然朋友圈大佬真的多，一個上午就有 50多個人留言解答了這個問題，我看完后思路也清晰了。
我也把解答思路整理了下，這里貼出來。
首先，這張表的字段沒有「非索引」字段，所以 select *相當(dāng)于 select id,a,b,c，然后這個查詢的內(nèi)容和條件 都在聯(lián)合索引樹里，因?yàn)槁?lián)合索引樹的葉子節(jié)點(diǎn)包含「索引列 主鍵」，所以查聯(lián)合索引樹就能查到全部結(jié)果了，這個就是覆蓋索引。
但是執(zhí)行計劃里的 type 是 index，這代表著是通過全掃描聯(lián)合索引樹的方式查詢到數(shù)據(jù)的，這是因?yàn)?where c并不符合聯(lián)合索引最左匹配原則。
那么，如果寫了個符合最左原則的 select 語句，那么 type 就是 ref，這個效率就比 index 全掃描要高一些。
那為什么選擇全掃描聯(lián)合索引樹，而不掃描全表（聚集索引樹）呢？
因?yàn)槁?lián)合索引樹的記錄比要小的多，而且這個 select * 不用執(zhí)行回表操作，所以直接遍歷聯(lián)合索引樹要比遍歷聚集索引樹要小的多，因此 MySQL 選擇了全掃描聯(lián)合索引樹。
再來回答第二個問題。
為什么這個數(shù)據(jù)表加了非索引字段，執(zhí)行同樣的查詢語句后，怎么變成走的是全表掃描呢？
因?yàn)榧恿似渌侄魏螅?/span>select * from t where c = 0;查詢的內(nèi)容就不能在聯(lián)合索引樹里找到了，而且條件也不符合最左匹配原則，這樣既不能覆蓋索引也不能執(zhí)行回表操作，所以這時只能通過掃描全表來查詢到所有的數(shù)據(jù)。
好了，問題就說完了，不知道大家 get 到了嗎？
這篇說的比較粗略，沒有詳細(xì)介紹一些索引的概念，比如聚集索引、聯(lián)合表索引、覆蓋索引、回表操作這些東西。
可能沒有點(diǎn)索引基礎(chǔ)的同學(xué)看的有點(diǎn)懵逼，小林后面在出一篇更詳細(xì)的。



本站聲明： 本文章由作者或相關(guān)機(jī)構(gòu)授權(quán)發(fā)布，目的在于傳遞更多信息，并不代表本站贊同其觀點(diǎn)，本站亦不保證或承諾內(nèi)容真實(shí)性等。需要轉(zhuǎn)載請聯(lián)系該專欄作者，如若文章內(nèi)容侵犯您的權(quán)益，請及時聯(lián)系本站刪除。
換一批
延伸閱讀

9月2日消息，不造車的華為或?qū)⒋呱龈蟮莫?dú)角獸公司，隨著阿維塔和賽力斯的入局，華為引望愈發(fā)顯得引人矚目。

關(guān)鍵字： 阿維塔 塞力斯 華為

加利福尼亞州圣克拉拉縣2024年8月30日 /美通社/ -- 數(shù)字化轉(zhuǎn)型技術(shù)解決方案公司Trianz今天宣布，該公司與Amazon Web Services （AWS）簽訂了...

關(guān)鍵字： AWS AN BSP 數(shù)字化

倫敦2024年8月29日 /美通社/ -- 英國汽車技術(shù)公司SODA.Auto推出其旗艦產(chǎn)品SODA V，這是全球首款涵蓋汽車工程師從創(chuàng)意到認(rèn)證的所有需求的工具，可用于創(chuàng)建軟件定義汽車。 SODA V工具的開發(fā)耗時1.5...

關(guān)鍵字： 汽車 人工智能 智能驅(qū)動 BSP

北京2024年8月28日 /美通社/ -- 越來越多用戶希望企業(yè)業(yè)務(wù)能7×24不間斷運(yùn)行，同時企業(yè)卻面臨越來越多業(yè)務(wù)中斷的風(fēng)險，如企業(yè)系統(tǒng)復(fù)雜性的增加，頻繁的功能更新和發(fā)布等。如何確保業(yè)務(wù)連續(xù)性，提升韌性，成...

關(guān)鍵字： 亞馬遜 解密 控制平面 BSP

8月30日消息，據(jù)媒體報道，騰訊和網(wǎng)易近期正在縮減他們對日本游戲市場的投資。

關(guān)鍵字： 騰訊 編碼器 CPU

8月28日消息，今天上午，2024中國國際大數(shù)據(jù)產(chǎn)業(yè)博覽會開幕式在貴陽舉行，華為董事、質(zhì)量流程IT總裁陶景文發(fā)表了演講。

關(guān)鍵字： 華為 12nm EDA 半導(dǎo)體

8月28日消息，在2024中國國際大數(shù)據(jù)產(chǎn)業(yè)博覽會上，華為常務(wù)董事、華為云CEO張平安發(fā)表演講稱，數(shù)字世界的話語權(quán)最終是由生態(tài)的繁榮決定的。

關(guān)鍵字： 華為 12nm 手機(jī) 衛(wèi)星通信

要點(diǎn)： 有效應(yīng)對環(huán)境變化，經(jīng)營業(yè)績穩(wěn)中有升 落實(shí)提質(zhì)增效舉措，毛利潤率延續(xù)升勢 戰(zhàn)略布局成效顯著，戰(zhàn)新業(yè)務(wù)引領(lǐng)增長 以科技創(chuàng)新為引領(lǐng)，提升企業(yè)核心競爭力 堅持高質(zhì)量發(fā)展策略，塑強(qiáng)核心競爭優(yōu)勢...

關(guān)鍵字： 通信 BSP 電信運(yùn)營商 數(shù)字經(jīng)濟(jì)

北京2024年8月27日 /美通社/ -- 8月21日，由中央廣播電視總臺與中國電影電視技術(shù)學(xué)會聯(lián)合牽頭組建的NVI技術(shù)創(chuàng)新聯(lián)盟在BIRTV2024超高清全產(chǎn)業(yè)鏈發(fā)展研討會上宣布正式成立。 活動現(xiàn)場 NVI技術(shù)創(chuàng)新聯(lián)...

關(guān)鍵字： VI 傳輸協(xié)議 音頻 BSP

北京2024年8月27日 /美通社/ -- 在8月23日舉辦的2024年長三角生態(tài)綠色一體化發(fā)展示范區(qū)聯(lián)合招商會上，軟通動力信息技術(shù)（集團(tuán)）股份有限公司（以下簡稱"軟通動力"）與長三角投資（上海）有限...

關(guān)鍵字： BSP 信息技術(shù)
關(guān)閉
關(guān)閉