什么是 MySQL 的“回表”？
點擊藍色“程序員黃小斜”關(guān)注我喲
加個“星標”，每天和你一起多進步一點點！
小伙伴們在面試的時候，有一個特別常見的問題，那就是數(shù)據(jù)庫的回表。什么是回表？為什么需要回表？
今天就來和大家聊一聊這個話題。
1. 索引結(jié)構(gòu)
要搞明白這個問題，需要大家首先明白 MySQL 中索引存儲的數(shù)據(jù)結(jié)構(gòu)。這個其實很多小伙伴可能也都聽說過，B+Tree 嘛！
B+Tree 是什么？那你得先明白什么是 B-Tree，來看如下一張圖：
前面是 B-Tree，后面是 B+Tree，兩者的區(qū)別在于：
B-Tree 中，所有節(jié)點都會帶有指向具體記錄的指針；B+Tree 中只有葉子結(jié)點會帶有指向具體記錄的指針。
B-Tree 中不同的葉子之間沒有連在一起；B+Tree 中所有的葉子結(jié)點通過指針連接在一起。
B-Tree 中可能在非葉子結(jié)點就拿到了指向具體記錄的指針，搜索效率不穩(wěn)定；B+Tree 中，一定要到葉子結(jié)點中才可以獲取到具體記錄的指針，搜索效率穩(wěn)定。
基于上面兩點分析，我們可以得出如下結(jié)論：
B+Tree 中，由于非葉子結(jié)點不帶有指向具體記錄的指針，所以非葉子結(jié)點中可以存儲更多的索引項，這樣就可以有效降低樹的高度，進而提高搜索的效率。
B+Tree 中，葉子結(jié)點通過指針連接在一起，這樣如果有范圍掃描的需求，那么實現(xiàn)起來將非常容易，而對于 B-Tree，范圍掃描則需要不停的在葉子結(jié)點和非葉子結(jié)點之間移動。
對于第一點，一個 B+Tree 可以存多少條數(shù)據(jù)呢？以主鍵索引的 B+Tree 為例（二級索引存儲數(shù)據(jù)量的計算原理類似，但是葉子節(jié)點和非葉子節(jié)點上存儲的數(shù)據(jù)格式略有差異），我們可以簡單算一下。
計算機在存儲數(shù)據(jù)的時候，最小存儲單元是扇區(qū)，一個扇區(qū)的大小是 512 字節(jié)，而文件系統(tǒng)（例如 XFS/EXT4）最小單元是塊，一個塊的大小是 4KB。InnoDB 引擎存儲數(shù)據(jù)的時候，是以頁為單位的，每個數(shù)據(jù)頁的大小默認是 16KB，即四個塊。
基于這樣的知識儲備，我們可以大致算一下一個 B+Tree 能存多少數(shù)據(jù)。
假設(shè)數(shù)據(jù)庫中一條記錄是 1KB，那么一個頁就可以存 16 條數(shù)據(jù)（葉子結(jié)點）；對于非葉子結(jié)點存儲的則是主鍵值+指針，在 InnoDB 中，一個指針的大小是 6 個字節(jié)，假設(shè)我們的主鍵是 bigint ，那么主鍵占 8 個字節(jié)，當然還有其他一些頭信息也會占用字節(jié)我們這里就不考慮了，我們大概算一下，小伙伴們心里有數(shù)即可：
16*1024/(8+6)=1170
即一個非葉子結(jié)點可以指向 1170 個頁，那么一個三層的 B+Tree 可以存儲的數(shù)據(jù)量為：
1170*1170*16=21902400
可以存儲 2100萬 條數(shù)據(jù)。
在 InnoDB 存儲引擎中，B+Tree 的高度一般為 2-4 層，這就可以滿足千萬級的數(shù)據(jù)的存儲，查找數(shù)據(jù)的時候，一次頁的查找代表一次 IO，那我們通過主鍵索引查詢的時候，其實最多只需要 2-4 次 IO 操作就可以了。
大家先搞明白這個 B+Tree。
2. 兩類索引
大家知道，MySQL 中的索引有很多中不同的分類方式，可以按照數(shù)據(jù)結(jié)構(gòu)分，可以按照邏輯角度分，也可以按照物理存儲分，其中，按照物理存儲方式，可以分為聚簇索引和非聚簇索引。
我們?nèi)粘Ｋf的主鍵索引，其實就是聚簇索引（Clustered Index）;主鍵索引之外，其他的都稱之為非主鍵索引，非主鍵索引也被稱為二級索引（Secondary Index），或者叫作輔助索引。
對于主鍵索引和非主鍵索引，使用的數(shù)據(jù)結(jié)構(gòu)都是 B+Tree，唯一的區(qū)別在于葉子結(jié)點中存儲的內(nèi)容不同：
主鍵索引的葉子結(jié)點存儲的是一行完整的數(shù)據(jù)。
非主鍵索引的葉子結(jié)點存儲的則是主鍵值。
這就是兩者最大的區(qū)別。
所以，當我們需要查詢的時候：
如果是通過主鍵索引來查詢數(shù)據(jù)，例如 select * from user where id=100，那么此時只需要搜索主鍵索引的 B+Tree 就可以找到數(shù)據(jù)。
如果是通過非主鍵索引來查詢數(shù)據(jù)，例如 select * from user where username='javaboy'，那么此時需要先搜索 username 這一列索引的 B+Tree，搜索完成后得到主鍵的值，然后再去搜索主鍵索引的 B+Tree，就可以獲取到一行完整的數(shù)據(jù)。
對于第二種查詢方式而言，一共搜索了兩棵 B+Tree，第一次搜索 B+Tree 拿到主鍵值后再去搜索主鍵索引的 B+Tree，這個過程就是所謂的回表。
從上面的分析中我們也能看出，通過非主鍵索引查詢要掃描兩棵 B+Tree，而通過主鍵索引查詢只需要掃描一棵 B+Tree，所以如果條件允許，還是建議在查詢中優(yōu)先選擇通過主鍵索引進行搜索。
3. 一定會回表嗎？
那么不用主鍵索引就一定需要回表嗎？
不一定！
如果查詢的列本身就存在于索引中，那么即使使用二級索引，一樣也是不需要回表的。
舉個例子，我有如下一張表：
uname 和 address 字段組成了一個復合索引，那么此時，雖然這是一個二級索引，但是索引樹的葉子節(jié)點中除了保存主鍵值，也保存了 address 的值。
我們來看如下分析：
可以看到，此時使用到了 uname 索引，但是最后的 Extra 的值為?Using index，這就表示用到了索引覆蓋掃描（覆蓋索引），此時直接從索引中過濾不需要的記錄并返回命中的結(jié)果，這一步是在 MySQL 服務(wù)器層完成的，并且不需要回表。
4. 擴展
基于第一、二小節(jié)的分析，我們再來捋一捋為什么在數(shù)據(jù)庫中建議使用自增主鍵。
自增主鍵往往占用空間比較小，int 占 4 個字節(jié)，bigint 占 8 個字節(jié)。由于二級索引的葉子節(jié)點存儲的就是主鍵，所以如果主鍵占用空間小，意味著二級索引的葉子節(jié)點將來占用的空間小（間接降低 B+Tree 的高度，提高搜索效率）。
自增主鍵插入的時候比較快，直接插入即可，不會涉及到葉子節(jié)點分裂等問題（不需要挪動其他記錄）；而其他非自增主鍵插入的時候，可能要插入到兩個已有的數(shù)據(jù)中間，就有可能導致葉子節(jié)點分裂等問題，插入效率低（要挪動其他記錄）。
當然，這個是基于技術(shù)層面的討論，如果業(yè)務(wù)上無法使用自增主鍵或者有其他要求導致無法使用自增主鍵，那沒辦法，在滿足新要求的情況下重新選擇一個最佳實踐吧。
好啦，今天的主題是回表，現(xiàn)在大家明白什么是回表了吧？
看完微軟大神寫的求平均值代碼，我意識到自己還是 too young了
7 種提升 Spring Boot 吞吐量神技！
在央企當程序員是一種怎樣的體驗?
—?【 THE END 】— 公眾號[程序員黃小斜]全部博文已整理成一個目錄，請在公眾號里回復「m」獲取！ 最近面試BAT，整理一份面試資料《Java面試BATJ通關(guān)手冊》，覆蓋了Java核心技術(shù)、JVM、Java并發(fā)、SSM、微服務(wù)、數(shù)據(jù)庫、數(shù)據(jù)結(jié)構(gòu)等等。
獲取方式：點“在看”，關(guān)注公眾號并回復 PDF?領(lǐng)取，更多內(nèi)容陸續(xù)奉上。
文章有幫助的話，在看，轉(zhuǎn)發(fā)吧。
謝謝支持喲 (*^__^*）