本文轉(zhuǎn)載自微信公眾號「IT界農(nóng)民工」，作者萊烏。轉(zhuǎn)載本文請聯(lián)系IT界農(nóng)民工公眾號。

前段時間有讀者提議講講索引下推，這期就把這事兒安排上。多余的前言就不贅述了，我們直接開始。

列位坐好!

 

- 思維導(dǎo)圖 -

回表操作

對于數(shù)據(jù)庫來說，只要涉及到索引，必然繞不過去回表操作。當然這也是我們今天所講內(nèi)容的前調(diào)基礎(chǔ)。

說到回表，我們需要從索引開始說起。別擔心，不會長篇大論，這里只是簡單講下主鍵索引與普通索引，目的是讓大家對回表操作有個認識。如果你對回表操作很熟悉了，那么可以跳過這一段。

這里我們只以 Innodb 存儲引擎作為講解對象。

主鍵索引

主鍵索引在底層的數(shù)據(jù)存儲是通過 B+ 樹來實現(xiàn)的。簡單來說，就是除葉子節(jié)之外的其他節(jié)點都存儲的是主鍵值。而葉子節(jié)點上存儲的是整行的數(shù)據(jù)。

大體結(jié)構(gòu)如下圖所示。

 

非主鍵索引

除了主鍵索引外，其它的索引都被稱為非主鍵索引。與主鍵索引不同的是，非主鍵索引的葉子節(jié)點上存儲的是主鍵的值。

 

那讓我們再回到開始的問題，什么是回表操作?

當我們在非主鍵索引上查找一行數(shù)據(jù)的時候，此時的查找方式是先搜索非主鍵索引樹，拿到對應(yīng)的主鍵值，再到主鍵索引樹上查找對應(yīng)的行數(shù)據(jù)。

這種操作就叫作回表操作。

好了，這里你應(yīng)該了解了什么是回表操作了。簡單來講，就是在非主鍵索引樹上拿到對應(yīng)的主鍵值，然后回到主鍵索引上找到對應(yīng)的行數(shù)據(jù)。

這樣做的前提條件是，所要查找的字段不存在于非主鍵索引樹上。

低版本操作

講完了回表操作，讓我們繼續(xù)回到這篇文章的主題——索引下推。

其實在 Mysql 5.6 版本之前是沒有索引下推這個功能的，從 5.6 版本后才加上了這個優(yōu)化項。所以在引出索引下推前還是先回顧下沒有這個功能時是怎樣一種處理方式。

我們以一個真實例子來進行講解。

在這里有張用戶表 user，記錄著用戶的姓名，性別，身高，年齡等信息。表中 id 是自增主鍵，(name,sex) 是聯(lián)合索引。在這里用 1 表示男，2 表示女。現(xiàn)在需要查找所有姓王的男性信息。

SQL 實現(xiàn)起來很簡單：

 

但是它的實現(xiàn)原理是什么呢?

根據(jù)聯(lián)合索引最左前綴原則，我們在非主鍵索引樹上找到第一個滿足條件的值時，通過葉子節(jié)點記錄的主鍵值再回到主鍵索引樹上查找到對應(yīng)的行數(shù)據(jù)，再對比是否為當前所要查找的性別。

整個原理可以用下邊的圖進行表示。

 

 

看到了吧，低版本中需要每條數(shù)據(jù)都進行回表，增加了樹的搜索次數(shù)。如果遇到所要查找的數(shù)據(jù)量很大的話，性能必然有所缺失。

高版本操作

講完了低版本操作，讓我們繼續(xù)回到這篇文章的主題——索引下推。

知道了痛點，那么怎么解決。很簡單，只有符合條件了再進行回表。結(jié)合我們的例子來說就是當滿足了性別 sex = 1 了，再回表查找。這樣原本可能需要進行回表查找 4 次，現(xiàn)在可能只需要 2 次就可以了。

 

所以本質(zhì)來說，索引下推就是只有符合條件再進行回表，對索引中包含的字段先進行判斷，不符合條件的跳過。減少了不必要的回表操作。

總結(jié)

回表操作

當所要查找的字段不在非主鍵索引樹上時，需要通過葉子節(jié)點的主鍵值去主鍵索引上獲取對應(yīng)的行數(shù)據(jù)，這個過程稱為回表操作。

索引下推

索引下推主要是減少了不必要的回表操作。對于查找出來的數(shù)據(jù)，先過濾掉不符合條件的，其余的再去主鍵索引樹上查找。