大家好，我是小林。
昨天發了一篇關於索引失效的文章：誰還沒碰過索引失效呢
我在文末留了一個有點意思的思考題：
圖片
這個思考題其實是出自於，我之前這篇文章「一條 SQL 語句引發的思考」中留言區一位讀者朋友出的問題。
很多讀者都在留言區說了自己的想法，也有不少讀者私聊我答案到底是什麼？
所以，我今晚就跟大家聊聊這個思考題。
題目一
題目一很簡單，相信大家都能分析出答案，我昨天分享的索引失效文章裡也提及過。
「題目 1 」的資料庫表如下，id 是主鍵索引，name 是二級索引，其他欄位都是非索引欄位。
圖片
這四條模糊匹配的查詢語句，第一條和第二條都會走索引掃描，而且都是選擇掃描二級索引（index_name），我貼個第二條查詢語句的執行計劃結果圖：
圖片
而第三和第四條會發生索引失效，執行計劃的結果 type= ALL，代表了全表掃描。
圖片
題目二
題目 2 的資料庫表特別之處在於，只有兩個欄位，一個是主鍵索引 id，另外一個是二級索引 name。
圖片
針對題目 2 的資料表，第一條和第二條模糊查詢語句也是一樣可以走索引掃描，第二條查詢語句的執行計劃如下，Extra 裡的 Using index 說明用上了覆蓋索引：
圖片
我們來看一下第三條查詢語句的執行計劃（第四條也是一樣的結果）：
圖片
從執行計劃的結果中，可以看到 key=index_name，也就是說用上了二級索引，而且從 Extra 裡的 Using index 說明用上了覆蓋索引。
這是為什麼呢？
首先，這張表的欄位沒有「非索引」欄位，所以 select * 相當於 select id,name，然後這個查詢的資料都在*二級索引的 B+ 樹*，因為二級索引的 B+ 樹的葉子節點包含「索引值+主鍵值」，所以查*二級索引的 B+ 樹*就能查到全部結果了，這個就是覆蓋索引。
但是執行計劃裡的 type 是 index，這代表著是通過全掃描二級索引的 B+ 樹的方式查詢到資料的，也就是遍歷了整顆索引樹。
而第一和第二條查詢語句的執行計劃中 type 是 range，表示對索引列進行範圍查詢，也就是利用了索引樹的有序性的特點，通過查詢比較的方式，快速定位到了資料行。
所以，type=range 的查詢效率會比 type=index 的高一些。
為什麼選擇全掃描二級索引樹，而不掃描全表（聚簇索引）呢？
因為二級索引樹的記錄東西很少，就只有「索引列+主鍵值」，而聚簇索引記錄的東西會更多，比如聚簇索引中的葉子節點則記錄了主鍵值、事務 id、用於事務和 MVCC 的迴流指標以及所有的剩餘列。
再加上，這個 select * 不用執行回表操作。
所以， MySQL 優化器認為直接遍歷二級索引樹要比遍歷聚簇索引樹的成本要小的多，因此 MySQL 選擇了「全掃描二級索引樹」的方式查詢資料。
為什麼這個資料表加了非索引欄位，執行同樣的查詢語句後，怎麼變成走的是全表掃描呢？
加了其他欄位後，select * from t_user where name like "%xx"; 要查詢的資料就不能只在二級索引樹裡找了，得需要回表操作才能完成查詢的工作，再加上是左模糊匹配，無法利用索引樹的有序性來快速定位資料，所以得在二級索引樹逐一遍歷，獲取主鍵值後，再到聚簇索引樹檢索到對應的資料行，這樣實在太累了。
所以，優化器認為上面這樣的查詢過程的成本實在太高了，所以直接選擇全表掃描的方式來查詢資料。
從這個思考題我們知道了，使用左模糊匹配（like "%xx"）並不一定會走全表掃描，關鍵還是看資料表中的欄位。
如果資料庫表中的欄位只有主鍵+二級索引，那麼即使使用了左模糊匹配，也不會走全表掃描（type=all），而是走全掃描二級索引樹(type=index)。
再說一個相似，我們都知道聯合索引要遵循最左匹配才能走索引，但是如果資料庫表中的欄位都是索引的話，即使查詢過程中，沒有遵循最左匹配原則，也是走索引掃描的，而且 type 也是為 index，比如下圖：
圖片
就說到這了，下次見啦