一條SQL語句引發(fā)的思考……
時間：2021-08-19 16:25:07
手機看文章
掃描二維碼
隨時隨地手機看文章
[導(dǎo)讀]大家好，我是小林。昨晚有位讀者問了我這個問題：他創(chuàng)建了一張數(shù)據(jù)庫表，表里的字段只有主鍵索引（id）和聯(lián)合索引（a，b，c），然后他執(zhí)行的?select*fromtwherec=0;?這條語句發(fā)現(xiàn)走的是索引，他就感覺很困惑，困惑在于兩點：第一點，?wherec這個條件并不符合聯(lián)合索...
大家好，我是小林。昨晚有位讀者問了我這個問題：
他創(chuàng)建了一張數(shù)據(jù)庫表，表里的字段只有主鍵索引（果然朋友圈大佬真的多，一個上午就有?
他創(chuàng)建了一張數(shù)據(jù)庫表，表里的字段只有主鍵索引（id）和聯(lián)合索引（a，b，c），然后他執(zhí)行的?select * from t where c = 0;?這條語句發(fā)現(xiàn)走的是索引，他就感覺很困惑，困惑在于兩點：- 第一點，?where c 這個條件并不符合聯(lián)合索引的最左匹配原則，怎么就查詢的時候走了索引呢？
- 第二點，在這個數(shù)據(jù)表加了非索引字段，執(zhí)行同樣的查詢語句后，怎么變成走的是全表掃描呢？
- 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 b = 0;
- where c = 0;
- where b = 0 and c =0;
- where c = 0 and b = 0;
select * from t where c = 0;?這條不符合聯(lián)合索引的最左匹配原則的查詢語句走了索引查詢呢？剛開始看到這個問題的時候，我一時也想不到原因，只能大概猜測這條語句可以覆蓋索引，所以就走了索引查詢。今早我就發(fā)了個朋友圈，因為我朋友圈有差不多 1W 人，覺得朋友圈肯定有大佬能解答這個問題。果然朋友圈大佬真的多，一個上午就有?50?多個人留言解答了這個問題，我看完后思路也清晰了。我也把解答思路整理了下，這里貼出來。首先，這張表的字段沒有「非索引」字段，所以?select *?相當(dāng)于?select id,a,b,c，然后這個查詢的內(nèi)容和條件 都在聯(lián)合索引樹里，因為聯(lián)合索引樹的葉子節(jié)點包含「索引列 主鍵」，所以查聯(lián)合索引樹就能查到全部結(jié)果了，這個就是覆蓋索引。但是執(zhí)行計劃里的 type 是?index，這代表著是通過全掃描聯(lián)合索引樹的方式查詢到數(shù)據(jù)的，這是因為?where c?并不符合聯(lián)合索引最左匹配原則。那么，如果寫了個符合最左原則的 select 語句，那么 type 就是?ref，這個效率就比 index 全掃描要高一些。那為什么選擇全掃描聯(lián)合索引樹，而不掃描全表（聚集索引樹）呢？因為聯(lián)合索引樹的記錄比要小的多，而且這個 select * 不用執(zhí)行回表操作，所以直接遍歷聯(lián)合索引樹要比遍歷聚集索引樹要小的多，因此 MySQL 選擇了全掃描聯(lián)合索引樹。再來回答第二個問題。為什么這個數(shù)據(jù)表加了非索引字段，執(zhí)行同樣的查詢語句后，怎么變成走的是全表掃描呢？因為加了其他字段后，select * from t where c = 0;?查詢的內(nèi)容就不能在聯(lián)合索引樹里找到了，而且條件也不符合最左匹配原則，這樣既不能覆蓋索引也不能執(zhí)行回表操作，所以這時只能通過掃描全表來查詢到所有的數(shù)據(jù)。好了，問題就說完了，不知道大家 get 到了嗎？這篇說的比較粗略，沒有詳細介紹一些索引的概念，比如聚集索引、聯(lián)合表索引、覆蓋索引、回表操作這些東西。可能沒有點索引基礎(chǔ)的同學(xué)看的有點懵逼，小林后面在出一篇更詳細的。來源：小林coding版權(quán)歸原作者所有，如有侵權(quán)，請聯(lián)系刪除。
下載文檔
