目錄

覆蓋索引

最左字首原則

索引下推

思考及解答

---

設計表結構或者是建立索引的時候，我們的目標就是在滿足當前需求的情況下，減少對資料庫的訪問，減少資源的消耗。

//表結構
USER_TABLE | CREATE TABLE `USER_TABLE` (
  `id` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `orderId` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `orderId` (`orderId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

//插入資料
insert into students (id, age, orderId) values (1,18,101),(2,19,102),(3,20,103),(4,21,104),(5,22,105);

//查詢sql
select * from USER_TABLE where orderId between 102 and 103;

下面來看一下上面的查詢sql語句執行的流程：

1、在普通orderId索引上找到orderId=102的記錄，獲取id的值2；
2、再到主鍵索引樹上查詢 id=2 對應的行記錄；
3、在普通orderId索引上找到orderId=103的記錄，獲取id的值3；
4、再到主鍵索引樹上查詢 id=3 對應的行記錄；
5、再到普通orderId索引上找到orderId=104的記錄，不滿足條件，迴圈結束；
    通過第一節，我們知道完成的行記錄資訊是儲存在主鍵索引上的，普通索引只是儲存了索引值和主鍵key的資料。1-3-5步驟是查詢了普通索引樹上的值，然後回表了2次主鍵索引才獲取到結果。那麼我們只要避免回表就可以提高查詢的速度。

覆蓋索引

   這裡就用到了覆蓋索引，如果執行的語句是：


select id from USER_TABLE where orderId between 102 and 103;

    這時候只需要查詢id的值，而id的值已經在普通索引orderId上了，可以直接查詢結果，而不需要回表。也就是說，普通索引orderId已經“覆蓋了”我們的查詢要求，所以我們成為覆蓋索引。
    由於覆蓋索引可以減少樹的搜尋次數，避免了回表查詢，顯著提升查詢效能，所以使用覆蓋索引是一個常用的效能優化手段。
需要注意的是：覆蓋索引在引擎內部使用覆蓋索引在索引orderId上其實讀了3個記錄，但是對於server層來說，它只拿到了2條記錄，所以認為掃描數是2條。   

   當我們需要建立冗餘索引來支援覆蓋索引時就需要權衡考慮其維護代價和查詢的效率了。
   例如我們一般很少對姓名“name”欄位來做索引，基本上都是對身份證id或者userId來建立索引，但是如果我們有通過userId查詢“name”的需求的時候，資料量還比較大，又不想對"name"單獨的建立索引，查詢需求少且增加了維護成本，這時候使用聯合索引(userId,name)就有意義了。在這個請求上不需要回表查整個記錄，減少語句的執行時間。

最左字首原則

      當某個查詢頻率不高，但是存在的請求，考慮到空間及維護成本，我們不希望為它單獨建立一個索引，但是又不希望它掃描全表，這時，我們就會想到最字首索引，在B+樹中，我們可以利用“最左字首”，來定位記錄。
      最左字首索引就是利用聯合索引的最左N個欄位，也可以是字串索引的最左個字元；
    這裡我們需要考慮一個問題：在建立聯合索引的時候，如何安排索引內的欄位順序？


• 原則一：如果通過調整可以少維護一個索引，那麼這個順序就是需要優先考慮的；
• 原則二：空間記憶體的佔用；

   比如，對於身份證id和userId，我們既需要聯合索引(id,userId)，又需要id和userId各自的索引，這時候就要考慮，看那個欄位更大，如果userId比id大，我們就需要建立(userid,id)和id的索引，這樣既可以滿足查詢需求，又可以節省空間。

索引下推

     前面提到最左字首索引，我們可以通過最左字首來定位索引，那麼那些不符合最左字首的部分呢？
     比如有一聯合索引是(name，age)。現在需要查出姓李的年齡是12歲的男同學。

select * from USER_TABLE where name like “李%” and age = 12 and is male = 1;

    這條語句在使用的時候只能用到”張”的索引，找到第一個滿足條件姓李的同學，然後再匹配剩下的條件。

     在mysql5.6之前，只能從找到的id將每條資料一個個開始回表，比對欄位值查詢，然後返回；
     但是mysql5.6以後引入索引下推優化【index condition pushdown 】簡稱icp，意思就是在索引遍歷的結果中，先通過右側索引包含的欄位直接過濾掉不滿足的條件記錄，減少回表的次數。這裡把條件判斷提前了，而不是訪問完磁碟查詢到結果放到結果集前才去判斷過濾。比如在索引下推的情況下，在查詢的結果中，會過濾掉姓張，年齡是12的女同學，這部分資料是不需要回表查詢的，減少了回表的次數，提高了訪問效率。這個優化我覺得還是比較合理的，不然多個聯合索引將造成右側索引的浪費，沒有物盡其用。

思考及解答

問題一：sql語句的執行流程，回表／覆蓋索引／聯合索引／最左字首／索引下推的概念及優缺點？

答：問中已經提到，這裡不字啊贅述。

問題二：主鍵索引一定比二級索引快嗎？
答：不一定，覆蓋索引不需要回表了，資料量大時，二級索引可能比主鍵索引快；

問題三：聯合索引的使用技巧有那些？
1、覆蓋索引：如果查詢條件是普通索引或者是聯合索引的最左側欄位，查詢結果是聯合索引的欄位或者是主鍵，此時不需要回表，直接返回結果，減少io磁碟的訪問，提高效率；
2、最左字首：聯合索引的最左N個欄位，也可以是字串索引的最左M個字元；
3、聯合索引：聯合索引的使用遵循最左字首原則，所以儘量將頻繁查詢的欄位靠左建立；
4、索引下推：例如”select * from USER_TABLE where name like “李%” and age = 12”，mysql5.6版本之前，會對所有匹配的資料進行回表操作，但是5.6之後的版本，會先過濾age!=12的資料，再進行回表，減少了訪問磁碟的次數，提升檢索效率；
Ps：使用聯合索引時，sql語句中的欄位不必和建立的聯合索引順序一致，因為優化器回幫你優化順序；

問題四：索引的建立原則
答：高頻查詢：可以建立聯合索引來使用覆蓋索引，不用回表；
      非高頻查詢：在已有的聯合索引基礎上，使用最左字首原則來快速查詢；
      mysql5.6: 引入索引下推，減少回表次數；

問題五：聯合索引在索引下推優化的情況下的執行過程？
聯合索引的使用方法：使用最左位元組做快速定位，然後由於有index condition pushdown 優化，接下來遍歷索引過程中就可以用右邊的欄位來過濾掉不需要的記錄，得到結果返回給server層，判斷是否繼續；

select  * from USER_TABLE where age > 10 and age < 12 and name =“張三”;

在icp作用下的執行流程：

• (1) server層將age>10 and name =“張三” 傳入引擎：
• (2) 引擎「快速定位」找到第一個 age > 10的行，如果發現name<>”zhangsan”，則找下一個，直到滿足name=“張三”;
• (3) 把找到的行返回給server層，server層根據age是否大於12來決定要不要取下一個；

總之：聯合索引因為有了icp優化，所以還是應該儘量將查詢的欄位放入聯合索引中。

學習筆記，內容簡單，用於複習，原內容2月有更新。
##參考資料，《MySql實戰詳解》