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

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

列位坐好!

- 思維導(dǎo)圖 -

回表操作

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

說(shuō)到回表，我們需要從索引開(kāi)始說(shuō)起。別擔(dān)心，不會(huì)長(zhǎng)篇大論，這里只是簡(jiǎn)單講下主鍵索引與普通索引，目的是讓大家對(duì)回表操作有個(gè)認(rèn)識(shí)。如果你對(duì)回表操作很熟悉了，那么可以跳過(guò)這一段。

這里我們只以 Innodb 存儲(chǔ)引擎作為講解對(duì)象。

主鍵索引

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

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

非主鍵索引

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

那讓我們?cè)倩氐介_(kāi)始的問(wèn)題，什么是回表操作?

當(dāng)我們?cè)诜侵麈I索引上查找一行數(shù)據(jù)的時(shí)候，此時(shí)的查找方式是先搜索非主鍵索引樹(shù)，拿到對(duì)應(yīng)的主鍵值，再到主鍵索引樹(shù)上查找對(duì)應(yīng)的行數(shù)據(jù)。

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

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

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

低版本操作

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

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

我們以一個(gè)真實(shí)例子來(lái)進(jìn)行講解。

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

SQL 實(shí)現(xiàn)起來(lái)很簡(jiǎn)單：

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

根據(jù)聯(lián)合索引最左前綴原則，我們?cè)诜侵麈I索引樹(shù)上找到第一個(gè)滿足條件的值時(shí)，通過(guò)葉子節(jié)點(diǎn)記錄的主鍵值再回到主鍵索引樹(shù)上查找到對(duì)應(yīng)的行數(shù)據(jù)，再對(duì)比是否為當(dāng)前所要查找的性別。

整個(gè)原理可以用下邊的圖進(jìn)行表示。

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

高版本操作

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

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

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

總結(jié)

回表操作

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

索引下推

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