劇情介紹
? 在瘋狂創(chuàng )客圈 的社群面試交流中，有很多小伙伴在面大廠(chǎng)， 經(jīng)常遇到下面的問(wèn)題：
問(wèn)題1：在實(shí)際生產(chǎn)環(huán)境中，InnoDB 中一棵 B+ 樹(shù)索引一般有多少層？
問(wèn)題2：在實(shí)際生產(chǎn)環(huán)境中，InnoDB一棵B+樹(shù)可以存放多少行數據？
問(wèn)題3：MySQL 對于千萬(wàn)級的大表，為啥要優(yōu)化？
問(wèn)題4：mysql 單表最好不要超過(guò)2000w？
問(wèn)題5：?jiǎn)伪沓^(guò)2000w 就要考慮數據遷移了，這個(gè)是為啥？
問(wèn)題6：你這個(gè)表數據都馬上要到2000w 了，難怪查詢(xún)速度慢，為什么？
問(wèn)題N： ... 第100個(gè)變種
前段時(shí)間，有一個(gè)小伙伴在面美團里的時(shí)候，又遇到了此問(wèn)題。
其實(shí)，這些問(wèn)題，都是在考察大家對 B+樹(shù)底層原理的理解
尼恩在這里，給大家做一個(gè)系統化、起底式、全方位的梳理。
按照下面的套路去答，基本可以拿到120分。
大家收藏起來(lái)，多度幾遍，一定能讓面試官刮目相看。
現在把這個(gè) 題目以及參考答案，收入咱們的 《尼恩Java面試寶典》，
供后面的小伙伴參考，提升大家的 3高 架構、設計、開(kāi)發(fā)水平。
注：本文以 PDF 持續更新，最新Java 架構筆記、面試題 的PDF文件，請后臺私信【筆記】即可獲??！
要回答這些問(wèn)題，在回答的時(shí)候，首先從 InnoDB 索引數據結構、數據組織方式說(shuō)起。
磁盤(pán)扇區、文件系統、InnoDB 存儲引擎都有各自的最小存儲單元。
來(lái)看看三個(gè)重要的最小單元磁盤(pán)上，存儲數據最小單元是扇區，一個(gè)扇區的大小是 512 字節，文件系統（例如EXT4）,最小單元是塊 （block），一個(gè)block 塊的大小是 4k，InnoDB 存儲引擎 的最小儲存單元——頁(yè)（Page），一個(gè)頁(yè)的大小是 16K。
來(lái)一個(gè)圖，更清楚：
以上三個(gè)數據，非常重要，一定要背下來(lái)， 并且在面試的時(shí)候， 找個(gè)機會(huì )背出來(lái)。
面試官一聽(tīng)，感覺(jué)你的計算機底層知識是多么的扎實(shí)，好感慢慢。這是來(lái)自 老架構師的 衷心提示。
由于文件系統（例如EXT4）的最小單元是塊 （block），一個(gè)block 塊的大小是 4k，
所以，假設一個(gè)文件大小只有1個(gè)字節，那么，這個(gè)文件在磁盤(pán)上，還是不得不占4KB的空間。
具體如下圖：
要知道，Innodb 的所有數據文件（后綴為 ibd 的文件），也是存儲在磁盤(pán)的，當然也是由block組成，
所以，Innodb 的所有數據文件，全部都是 16384（16k）的整數倍。
具體如下圖：
InnoDB 存儲引擎 的最小儲存單元——頁(yè)（Page），一個(gè)頁(yè)的大小是 16K，
在 MySQL 中我們的InnoDB 頁(yè)的大小當然也可以通過(guò)參數設置的，具體如下圖：
通過(guò)上圖，可以看到，在 MySQL 中我們的 InnoDB 頁(yè)的大小默認是 16k
數據表中的數據都是存儲在頁(yè)中的，所以一個(gè)頁(yè)中能存儲多少行數據呢？
先做一個(gè)假設：
假設一行數據的大小是1k，
那么一個(gè)16K頁(yè)，可以存放16行這樣的數據。
如果數據庫只按這樣的方式存儲，那么如何查找數據就成為一個(gè)問(wèn)題
因為我們不知道要查找的數據存在哪個(gè)頁(yè)中，也不可能把所有的頁(yè)遍歷一遍，那樣太慢了。
所以人們想了一個(gè)辦法，用B+樹(shù)的方式組織這些數據。B+ 樹(shù)的葉子節點(diǎn)存儲真正的記錄，對應的文件系統 page頁(yè)面，可以叫做 數據頁(yè)B+ 樹(shù)的非葉子節點(diǎn)存放的是鍵值 + 指針，其對應的文件系統 page頁(yè)面，可以叫做 索引頁(yè)
這里用指針來(lái)描其實(shí)述不是太準確，準確來(lái)說(shuō)是頁(yè)的偏移量，不過(guò)借用指針一詞，更好理解
如圖所示：
InnoDB中主鍵索引B+樹(shù)是如何組織數據、查詢(xún)數據的？
我們總結一下：
1、在B+樹(shù)中葉子節點(diǎn)存放數據，非葉子節點(diǎn)存放鍵值+指針。
InnoDB存儲引擎的最小存儲單元是頁(yè)，頁(yè)可以用于存放數據也可以用于存放鍵值+指針，
2、頁(yè)內的記錄是有序的，所以可以使用二分查找在頁(yè)內到下一層的目標頁(yè)面的指針從根頁(yè)開(kāi)始，首先通過(guò)非葉子節點(diǎn)的二分查找法，確定數據在下一層哪個(gè)頁(yè)之后，一層一層往下找，一直到非葉子節點(diǎn)，進(jìn)而在非葉子節（數據頁(yè)）中查找到需要的數據；
那么回到我們開(kāi)始的問(wèn)題，通常一棵B+樹(shù)可以存放多少行數據？
首先，需要計算出非葉子節點(diǎn)能存放多少指針？
頁(yè)作為 InnoDB 磁盤(pán)管理的最小單位，不僅可以用來(lái)存放具體的行數據，還可以存放鍵值和指針。
回到文題，我們先從簡(jiǎn)單的入手，
這里我們先假設B+樹(shù)高為2，即存在一個(gè)根節點(diǎn)和若干個(gè)葉子節點(diǎn)，那么 B+ 樹(shù)只有兩層。
如下圖：
那么對于這棵 B+ 樹(shù)能夠存放多少行數據，其實(shí)問(wèn)的就是這棵 B+ 樹(shù)的非葉子節點(diǎn)中存放的數據量，
那么，這棵簡(jiǎn)單的 B+ 樹(shù)能夠存放多少行數據，其實(shí)問(wèn)的就是這棵 B+ 樹(shù)的非葉子節點(diǎn)中存放的數據量，
這棵B+樹(shù)的存放總記錄數為, 是一個(gè)簡(jiǎn)單的公式：
每個(gè)葉子節點(diǎn)存放的行記錄數就是每頁(yè)存放的記錄數，由于各個(gè)數據表中的字段數量都不一樣，
這里我們就不深究葉子節點(diǎn)的存儲結構了，
簡(jiǎn)單按照一行記錄的數據大小為 1k 來(lái)算的話(huà)（實(shí)際上現在很多互聯(lián)網(wǎng)業(yè)務(wù)數據記錄大小通常就是 1K 左右），
一頁(yè)（16K）或者說(shuō)一個(gè)葉子節點(diǎn)可以存放 16 行這樣的數據。
那么 ，這顆B+ 樹(shù) 的非葉子節點(diǎn)（ 唯一的）能夠存儲多少數據呢？
非葉子節點(diǎn)里面存的是主鍵值 + 指針
為了方便分析，這里我們把一個(gè)主鍵值 + 一個(gè)指針?lè )Q為一個(gè)單元，我們假設主鍵的類(lèi)型是 BigInt，長(cháng)度為 8 字節，而指針大小在 InnoDB 中設置為 6 字節，
這樣一個(gè)單元，一共 14 字節。
這樣的話(huà)，一頁(yè)或者說(shuō)一個(gè)非葉子節點(diǎn)能夠存放 16384 / 14=1170 個(gè)這樣的單元。
也就是說(shuō)一個(gè)非葉子節點(diǎn)中能夠存放 1170 個(gè)指針，即對應 1170 個(gè)葉子節點(diǎn)，
所以對于這樣一棵高度為 2 的 B+ 樹(shù)，能存放 1170（一個(gè)非葉子節點(diǎn)中的指針數） * 16（一個(gè)葉子節點(diǎn)中的行數）= 18720 行數據。
當然，這樣分析其實(shí)不是很?chē)乐?，InnoDB 數據頁(yè)結構，不全是 主鍵值 + 一個(gè)指針，還有其他的一些 元數據。
按照 《MySQL 技術(shù)內幕：InnoDB 存儲引擎》中的定義，InnoDB 數據頁(yè)結構包含如下幾個(gè)部分：
分析完高度為 2 的 B+ 樹(shù)，同樣的道理，我們來(lái)看高度為 3 的：
根頁(yè)（page10）可以存放 1170 個(gè)指針，
然后第二層的每個(gè)頁(yè)（page:11,12,13）也都分別可以存放1170個(gè)指針。
這樣一共可以存放 1170 * 1170 個(gè)指針，
即對應的有 1170 * 1170 個(gè)非葉子節點(diǎn)，
所以，高為3的B+樹(shù)一共可以存放 1170 * 1170 * 16 = 21902400 行記錄。
回到問(wèn)題，InnoDB 一棵 B+ 樹(shù)可以存放多少行數據？
這個(gè)問(wèn)題的簡(jiǎn)單回答是：約 2 千萬(wàn)。
在InnoDB 引擎中，實(shí)際的情況如何呢？
在InnoDB的表空間文件中，約定page number為3的代表主鍵索引的根頁(yè)，而在根頁(yè)偏移量為64的地方存放了該B+樹(shù)的page level。
如果page level為1，樹(shù)高為2，page level為2，則樹(shù)高為3。
即B+樹(shù)的高度=page level+1；
下面我們將從實(shí)際環(huán)境中嘗試找到這個(gè)page level。
實(shí)驗環(huán)境中，這三張表的數據量如下：
從圖中可以看到，一個(gè)表600W，一個(gè)表 15W，一個(gè)表5行數據。
在實(shí)際操作之前，你可以通過(guò)InnoDB元數據表確認主鍵索引根頁(yè)的page number為3，你也可以從《InnoDB存儲引擎》這本書(shū)中得到確認。
說(shuō)明：
information_schema是mysql自帶的一個(gè)信息數據庫，其保存著(zhù)關(guān)于mysql服務(wù)器所維護的所有其他數據庫的信息，如數據庫名，數據庫的表，表欄的數據類(lèi)型與訪(fǎng)問(wèn)權限等。innodb_sys_indexes：innodb表的索引的相關(guān)信息innodb_sys_tables：表格的格式和存儲特性，包括行格式，壓縮頁(yè)面大小位級別的信息
執行結果：
可以看出數據庫dbt3下的customer表、lineitem表主鍵索引根頁(yè)的page number均為3，而其他的二級索引page number為4。
在進(jìn)行深入分析之前，首先回顧一下基礎知識
數據表的主鍵列使用的就是主鍵索引。一張數據表有只能有一個(gè)主鍵，并且主鍵不能為 null，不能重復。
在 MySQL 的 InnoDB 的表中，當沒(méi)有顯示的指定表的主鍵時(shí)，InnoDB 會(huì )自動(dòng)先檢查表中是否有唯一索引的字段，如果有，則選擇該字段為默認的主鍵，否則 InnoDB 將會(huì )自動(dòng)創(chuàng )建一個(gè) 6Byte 的自增主鍵。
二級索引又稱(chēng)為輔助索引，是因為二級索引的葉子節點(diǎn)存儲的數據是主鍵。也就是說(shuō)，通過(guò)二級索引，可以定位主鍵的位置。常用的二級索引包括：唯一索引(Unique Key) ：唯一索引也是一種約束。唯一索引的屬性列不能出現重復的數據，但是允許數據為 NULL，一張表允許創(chuàng )建多個(gè)唯一索引。 建立唯一索引的目的大部分時(shí)候都是為了該屬性列的數據的唯一性，而不是為了查詢(xún)效率。普通索引(Index) ：普通索引的唯一作用就是為了快速查詢(xún)數據，一張表允許創(chuàng )建多個(gè)普通索引，并允許數據重復和 NULL。前綴索引(Prefix) ：前綴索引只適用于字符串類(lèi)型的數據。前綴索引是對文本的前幾個(gè)字符創(chuàng )建索引，相比普通索引建立的數據更小， 因為只取前幾個(gè)字符。
從物理意義上來(lái)講，InnoDB表由共享表空間文件（ibdata1）、獨占表空間文件（ibd）、表結構文件（.frm）、以及日志文件（redo文件等）組成。
在MYSQL中建立任何一張數據表，在其數據目錄對應的數據庫目錄下都有對應表的.frm文件
.frm文件是用來(lái)保存每個(gè)數據表的元數據(meta)信息，包括表結構的定義等，
.frm文件跟數據庫存儲引擎無(wú)關(guān)，也就是任何存儲引擎的數據表都必須有.frm文件，
命名方式為數據表名.frm，如user.frm. ， .frm文件可以用來(lái)在數據庫崩潰時(shí)恢復表結構。
?。?）表空間結構分析
以下為InnoDB的表空間結構圖：
數據段即B+樹(shù)的葉子節點(diǎn)，索引段即為B+樹(shù)的非葉子節點(diǎn)InnoDB存儲引擎的管理是由引擎本身完成的，
表空間（Tablespace）是由分散的段(Segment)組成。一個(gè)段(Segment)包含多個(gè)區（Extent）。
區（Extent）由64個(gè)連續的頁(yè)（Page）組成，每個(gè)頁(yè)大小為16K，即每個(gè)區大小為1MB，創(chuàng )建新表時(shí)，先使用32頁(yè)大小的碎片頁(yè)存放數據，使用完后才是區的申請（InnoDB最多每次申請4個(gè)區，保證數據的順序性能）頁(yè)類(lèi)型有：數據頁(yè)、Undo頁(yè)、系統頁(yè)、事務(wù)數據頁(yè)、插入緩沖位圖頁(yè)、以及插入緩沖空閑列表頁(yè)。
?。?）獨占表空間文件
若將innodb_file_per_table設置為on，則系統將為每一個(gè)表單獨的生成一個(gè)table_name.ibd的文件，
在此文件中，存儲與該表相關(guān)的數據、索引、表的內部數據字典信息。
?。?）共享表空間文件
在InnoDB存儲引擎中，默認表空間文件是ibdata1（主要存儲的是共享表空間數據），初始化為10M，且可以擴展，如下圖所示：
實(shí)際上，InnoDB的表空間文件是可以修改的，使用以下語(yǔ)句就可以修改：
使用共享表空間存儲方式時(shí)，Innodb的所有數據保存在一個(gè)單獨的表空間里面，而這個(gè)表空間可以由很多個(gè)文件組成，一個(gè)表可以跨多個(gè)文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。
從Innodb的官方文檔中可以看到，其表空間的最大限制為64TB，也就是說(shuō)，Innodb的單表限制基本上也在64TB左右了，當然這個(gè)大小是包括這個(gè)表的所有索引等其他相關(guān)數據。
而在使用單獨表空間存儲方式時(shí)，每個(gè)表的數據以一個(gè)單獨的文件來(lái)存放，這個(gè)時(shí)候的單表限制，又變成文件系統的大小限制了。
了解了這些基礎知識之后，下面我們對數據庫表空間文件做相關(guān)的解析， 主要是針對獨占獨占表空間文件（ibd）
因為主鍵索引B+樹(shù)的根頁(yè)，在整個(gè)表空間文件中的第3個(gè)頁(yè)開(kāi)始，所以可以算出它在文件中的偏移量：
另外根據《InnoDB存儲引擎》中描述在根頁(yè)的64偏移量位置前2個(gè)字節，保存了page level的值，
因此我們想要的page level的值在整個(gè)文件中的偏移量為：16384*3+64=49152+64=49216，前2個(gè)字節中。
接下來(lái)我們用hexdump工具，查看表空間文件指定偏移量上的數據：
linetem表的page level為2，B+樹(shù)高度為page level+1=3；
region表的page level為0，B+樹(shù)高度為page level+1=1；
customer表的page level為2，B+樹(shù)高度為page level+1=3；
總結，通過(guò)分析可以看到，實(shí)驗環(huán)境的三個(gè)表：lineitem表的數據行數為600多萬(wàn)，B+樹(shù)高度為3，customer表數據行數只有15萬(wàn)，B+樹(shù)高度也為3。
可以看出盡管數據量差異較大，這兩個(gè)表樹(shù)的高度都是3，換句話(huà)說(shuō)這兩個(gè)表通過(guò)索引查詢(xún)效率并沒(méi)有太大差異，因為都只需要做3次IO。
所以在InnoDB中B+樹(shù)高度一般為1-3層，它就能滿(mǎn)足千萬(wàn)級的數據存儲。
在查找數據時(shí)一次頁(yè)的查找代表一次IO，所以通過(guò)主鍵索引查詢(xún)通常只需要1-3次IO操作即可查找到數據。
前面分析了，假設主鍵ID為bigint類(lèi)型，長(cháng)度為8字節，
而指針大小在InnoDB源碼中設置為6字節，這樣一共14字節。
那么一個(gè)頁(yè)中能存放多少這樣的組合，就代表有多少指針，即 16384 / 14 = 1170。
那么可以算出一棵高度為2 的B+樹(shù)，能存放 1170 * 16 = 18720 條這樣的數據記錄。
同理：
高度為3的B+樹(shù)可以存放的行數 = 1170 * 1170 * 16 = 21902400
所以，千萬(wàn)級的數據存儲只需要約3層B+樹(shù)，所以說(shuō)，根據主鍵id索引查詢(xún)約3次IO便可以找到目標結果。
注意：查詢(xún)數據時(shí)，每加載一頁(yè)（page）代表一次IO，
當然 B+樹(shù)的根節點(diǎn)是常駐內存的，這里少了一次IO。
但是，我們?yōu)槔阌诜治?，在分析的時(shí)候，暫時(shí)不管吧，先看一般情況。
對于一些復雜的查詢(xún)，可能需要走二級索引，那么通過(guò)二級索引查找記錄最多需要花費多少次IO呢？
首先，從二級索引B+樹(shù)中，根據name 找到對應的主鍵id
然后，再根據主鍵id 從 聚簇索引查找到對應的記錄。
如上圖所示，二級索引有3層，聚簇索引有3層，那么最多花費的IO次數是：3+3 = 6 （這里，忽略根節點(diǎn)常駐內存這件事）
聚簇索引默認是主鍵，如果表中沒(méi)有定義主鍵，InnoDB 會(huì )選擇一個(gè)唯一的非空索引代替。
如果連這樣的索引沒(méi)有，InnoDB 會(huì )隱式定義一個(gè)主鍵來(lái)作為聚簇索引。
這也是為什么InnoDB表必須有主鍵，并且推薦使用整型的自增主鍵?。?！InnoDB使用的是聚簇索引，將主鍵組織到一棵B+樹(shù)中，而行數據就儲存在葉子節點(diǎn)上
為啥磁盤(pán)IO的性能低？ 不多說(shuō)啦，具體請參考 的《Java葵花寶典：Java 高性能超底層原理》 視頻和講義，需要的請后臺私信【筆記】即可獲??！
通過(guò)上面的分析可以看出， 如果是 走 非聚集索引查詢(xún)， 需要6次IO，
走 聚焦索引查詢(xún)，需要3次磁盤(pán)IO
當然，以上分析流程，忽略了一些性能的優(yōu)化措施，比如 B+樹(shù)根節點(diǎn) 常駐內存，還有可能命中 查詢(xún)緩存等等。
所以，innodb 單表推薦2kw 記錄，超過(guò)了這個(gè)值可能會(huì )導致B+樹(shù)層級更高，影響查詢(xún)性能。
當然，凡事看場(chǎng)景，上面只是一般的分析。
索引結構不會(huì )影響單表最大行數，2kw也只是推薦值，最終的單表記錄數最大值，受到硬件條件，和各種優(yōu)化措施的影響。
只要按照上面的 流程去作答， 你的答案不是 100分，而是 120分
面試官一定是 心滿(mǎn)意足， 五體投地。。。
問(wèn)題1：在實(shí)際生產(chǎn)環(huán)境中，InnoDB 中一棵 B+ 樹(shù)索引一般有多少層？
問(wèn)題2：在實(shí)際生產(chǎn)環(huán)境中，InnoDB一棵B+樹(shù)可以存放多少行數據？
問(wèn)題3：MySQL 對于千萬(wàn)級的大表，為啥要優(yōu)化？
問(wèn)題4：mysql 單表最好不要超過(guò)2000w？
問(wèn)題5：?jiǎn)伪沓^(guò)2000w 就要考慮數據遷移了，這個(gè)是為啥？
問(wèn)題6：你這個(gè)表數據都馬上要到2000w 了，難怪查詢(xún)速度慢，為什么？
問(wèn)題N： ... 第100個(gè)變種
前段時(shí)間，有一個(gè)小伙伴在面美團里的時(shí)候，又遇到了此問(wèn)題。
其實(shí)，這些問(wèn)題，都是在考察大家對 B+樹(shù)底層原理的理解
尼恩在這里，給大家做一個(gè)系統化、起底式、全方位的梳理。
按照下面的套路去答，基本可以拿到120分。
大家收藏起來(lái)，多度幾遍，一定能讓面試官刮目相看。
現在把這個(gè) 題目以及參考答案，收入咱們的 《尼恩Java面試寶典》，
供后面的小伙伴參考，提升大家的 3高 架構、設計、開(kāi)發(fā)水平。
注：本文以 PDF 持續更新，最新Java 架構筆記、面試題 的PDF文件，請后臺私信【筆記】即可獲??！
要回答這些問(wèn)題，在回答的時(shí)候，首先從 InnoDB 索引數據結構、數據組織方式說(shuō)起。
磁盤(pán)扇區、文件系統、InnoDB 存儲引擎都有各自的最小存儲單元。
來(lái)看看三個(gè)重要的最小單元磁盤(pán)上，存儲數據最小單元是扇區，一個(gè)扇區的大小是 512 字節，文件系統（例如EXT4）,最小單元是塊 （block），一個(gè)block 塊的大小是 4k，InnoDB 存儲引擎 的最小儲存單元——頁(yè)（Page），一個(gè)頁(yè)的大小是 16K。
來(lái)一個(gè)圖，更清楚：
以上三個(gè)數據，非常重要，一定要背下來(lái)， 并且在面試的時(shí)候， 找個(gè)機會(huì )背出來(lái)。
面試官一聽(tīng)，感覺(jué)你的計算機底層知識是多么的扎實(shí)，好感慢慢。這是來(lái)自 老架構師的 衷心提示。
由于文件系統（例如EXT4）的最小單元是塊 （block），一個(gè)block 塊的大小是 4k，
所以，假設一個(gè)文件大小只有1個(gè)字節，那么，這個(gè)文件在磁盤(pán)上，還是不得不占4KB的空間。
具體如下圖：
要知道，Innodb 的所有數據文件（后綴為 ibd 的文件），也是存儲在磁盤(pán)的，當然也是由block組成，
所以，Innodb 的所有數據文件，全部都是 16384（16k）的整數倍。
具體如下圖：
InnoDB 存儲引擎 的最小儲存單元——頁(yè)（Page），一個(gè)頁(yè)的大小是 16K，
在 MySQL 中我們的InnoDB 頁(yè)的大小當然也可以通過(guò)參數設置的，具體如下圖：
通過(guò)上圖，可以看到，在 MySQL 中我們的 InnoDB 頁(yè)的大小默認是 16k
數據表中的數據都是存儲在頁(yè)中的，所以一個(gè)頁(yè)中能存儲多少行數據呢？
先做一個(gè)假設：
假設一行數據的大小是1k，
那么一個(gè)16K頁(yè)，可以存放16行這樣的數據。
如果數據庫只按這樣的方式存儲，那么如何查找數據就成為一個(gè)問(wèn)題
因為我們不知道要查找的數據存在哪個(gè)頁(yè)中，也不可能把所有的頁(yè)遍歷一遍，那樣太慢了。
所以人們想了一個(gè)辦法，用B+樹(shù)的方式組織這些數據。B+ 樹(shù)的葉子節點(diǎn)存儲真正的記錄，對應的文件系統 page頁(yè)面，可以叫做 數據頁(yè)B+ 樹(shù)的非葉子節點(diǎn)存放的是鍵值 + 指針，其對應的文件系統 page頁(yè)面，可以叫做 索引頁(yè)
這里用指針來(lái)描其實(shí)述不是太準確，準確來(lái)說(shuō)是頁(yè)的偏移量，不過(guò)借用指針一詞，更好理解
如圖所示：
InnoDB中主鍵索引B+樹(shù)是如何組織數據、查詢(xún)數據的？
我們總結一下：
1、在B+樹(shù)中葉子節點(diǎn)存放數據，非葉子節點(diǎn)存放鍵值+指針。
InnoDB存儲引擎的最小存儲單元是頁(yè)，頁(yè)可以用于存放數據也可以用于存放鍵值+指針，
2、頁(yè)內的記錄是有序的，所以可以使用二分查找在頁(yè)內到下一層的目標頁(yè)面的指針從根頁(yè)開(kāi)始，首先通過(guò)非葉子節點(diǎn)的二分查找法，確定數據在下一層哪個(gè)頁(yè)之后，一層一層往下找，一直到非葉子節點(diǎn)，進(jìn)而在非葉子節（數據頁(yè)）中查找到需要的數據；
那么回到我們開(kāi)始的問(wèn)題，通常一棵B+樹(shù)可以存放多少行數據？
首先，需要計算出非葉子節點(diǎn)能存放多少指針？
頁(yè)作為 InnoDB 磁盤(pán)管理的最小單位，不僅可以用來(lái)存放具體的行數據，還可以存放鍵值和指針。
回到文題，我們先從簡(jiǎn)單的入手，
這里我們先假設B+樹(shù)高為2，即存在一個(gè)根節點(diǎn)和若干個(gè)葉子節點(diǎn)，那么 B+ 樹(shù)只有兩層。
如下圖：
那么對于這棵 B+ 樹(shù)能夠存放多少行數據，其實(shí)問(wèn)的就是這棵 B+ 樹(shù)的非葉子節點(diǎn)中存放的數據量，
那么，這棵簡(jiǎn)單的 B+ 樹(shù)能夠存放多少行數據，其實(shí)問(wèn)的就是這棵 B+ 樹(shù)的非葉子節點(diǎn)中存放的數據量，
這棵B+樹(shù)的存放總記錄數為, 是一個(gè)簡(jiǎn)單的公式：
每個(gè)葉子節點(diǎn)存放的行記錄數就是每頁(yè)存放的記錄數，由于各個(gè)數據表中的字段數量都不一樣，
這里我們就不深究葉子節點(diǎn)的存儲結構了，
簡(jiǎn)單按照一行記錄的數據大小為 1k 來(lái)算的話(huà)（實(shí)際上現在很多互聯(lián)網(wǎng)業(yè)務(wù)數據記錄大小通常就是 1K 左右），
一頁(yè)（16K）或者說(shuō)一個(gè)葉子節點(diǎn)可以存放 16 行這樣的數據。
那么 ，這顆B+ 樹(shù) 的非葉子節點(diǎn)（ 唯一的）能夠存儲多少數據呢？
非葉子節點(diǎn)里面存的是主鍵值 + 指針
為了方便分析，這里我們把一個(gè)主鍵值 + 一個(gè)指針?lè )Q為一個(gè)單元，我們假設主鍵的類(lèi)型是 BigInt，長(cháng)度為 8 字節，而指針大小在 InnoDB 中設置為 6 字節，
這樣一個(gè)單元，一共 14 字節。
這樣的話(huà)，一頁(yè)或者說(shuō)一個(gè)非葉子節點(diǎn)能夠存放 16384 / 14=1170 個(gè)這樣的單元。
也就是說(shuō)一個(gè)非葉子節點(diǎn)中能夠存放 1170 個(gè)指針，即對應 1170 個(gè)葉子節點(diǎn)，
所以對于這樣一棵高度為 2 的 B+ 樹(shù)，能存放 1170（一個(gè)非葉子節點(diǎn)中的指針數） * 16（一個(gè)葉子節點(diǎn)中的行數）= 18720 行數據。
當然，這樣分析其實(shí)不是很?chē)乐?，InnoDB 數據頁(yè)結構，不全是 主鍵值 + 一個(gè)指針，還有其他的一些 元數據。
按照 《MySQL 技術(shù)內幕：InnoDB 存儲引擎》中的定義，InnoDB 數據頁(yè)結構包含如下幾個(gè)部分：
分析完高度為 2 的 B+ 樹(shù)，同樣的道理，我們來(lái)看高度為 3 的：
根頁(yè)（page10）可以存放 1170 個(gè)指針，
然后第二層的每個(gè)頁(yè)（page:11,12,13）也都分別可以存放1170個(gè)指針。
這樣一共可以存放 1170 * 1170 個(gè)指針，
即對應的有 1170 * 1170 個(gè)非葉子節點(diǎn)，
所以，高為3的B+樹(shù)一共可以存放 1170 * 1170 * 16 = 21902400 行記錄。
回到問(wèn)題，InnoDB 一棵 B+ 樹(shù)可以存放多少行數據？
這個(gè)問(wèn)題的簡(jiǎn)單回答是：約 2 千萬(wàn)。
在InnoDB 引擎中，實(shí)際的情況如何呢？
在InnoDB的表空間文件中，約定page number為3的代表主鍵索引的根頁(yè)，而在根頁(yè)偏移量為64的地方存放了該B+樹(shù)的page level。
如果page level為1，樹(shù)高為2，page level為2，則樹(shù)高為3。
即B+樹(shù)的高度=page level+1；
下面我們將從實(shí)際環(huán)境中嘗試找到這個(gè)page level。
實(shí)驗環(huán)境中，這三張表的數據量如下：
從圖中可以看到，一個(gè)表600W，一個(gè)表 15W，一個(gè)表5行數據。
在實(shí)際操作之前，你可以通過(guò)InnoDB元數據表確認主鍵索引根頁(yè)的page number為3，你也可以從《InnoDB存儲引擎》這本書(shū)中得到確認。
說(shuō)明：
information_schema是mysql自帶的一個(gè)信息數據庫，其保存著(zhù)關(guān)于mysql服務(wù)器所維護的所有其他數據庫的信息，如數據庫名，數據庫的表，表欄的數據類(lèi)型與訪(fǎng)問(wèn)權限等。innodb_sys_indexes：innodb表的索引的相關(guān)信息innodb_sys_tables：表格的格式和存儲特性，包括行格式，壓縮頁(yè)面大小位級別的信息
執行結果：
可以看出數據庫dbt3下的customer表、lineitem表主鍵索引根頁(yè)的page number均為3，而其他的二級索引page number為4。
在進(jìn)行深入分析之前，首先回顧一下基礎知識
數據表的主鍵列使用的就是主鍵索引。一張數據表有只能有一個(gè)主鍵，并且主鍵不能為 null，不能重復。
在 MySQL 的 InnoDB 的表中，當沒(méi)有顯示的指定表的主鍵時(shí)，InnoDB 會(huì )自動(dòng)先檢查表中是否有唯一索引的字段，如果有，則選擇該字段為默認的主鍵，否則 InnoDB 將會(huì )自動(dòng)創(chuàng )建一個(gè) 6Byte 的自增主鍵。
二級索引又稱(chēng)為輔助索引，是因為二級索引的葉子節點(diǎn)存儲的數據是主鍵。也就是說(shuō)，通過(guò)二級索引，可以定位主鍵的位置。常用的二級索引包括：唯一索引(Unique Key) ：唯一索引也是一種約束。唯一索引的屬性列不能出現重復的數據，但是允許數據為 NULL，一張表允許創(chuàng )建多個(gè)唯一索引。 建立唯一索引的目的大部分時(shí)候都是為了該屬性列的數據的唯一性，而不是為了查詢(xún)效率。普通索引(Index) ：普通索引的唯一作用就是為了快速查詢(xún)數據，一張表允許創(chuàng )建多個(gè)普通索引，并允許數據重復和 NULL。前綴索引(Prefix) ：前綴索引只適用于字符串類(lèi)型的數據。前綴索引是對文本的前幾個(gè)字符創(chuàng )建索引，相比普通索引建立的數據更小， 因為只取前幾個(gè)字符。
從物理意義上來(lái)講，InnoDB表由共享表空間文件（ibdata1）、獨占表空間文件（ibd）、表結構文件（.frm）、以及日志文件（redo文件等）組成。
在MYSQL中建立任何一張數據表，在其數據目錄對應的數據庫目錄下都有對應表的.frm文件
.frm文件是用來(lái)保存每個(gè)數據表的元數據(meta)信息，包括表結構的定義等，
.frm文件跟數據庫存儲引擎無(wú)關(guān)，也就是任何存儲引擎的數據表都必須有.frm文件，
命名方式為數據表名.frm，如user.frm. ， .frm文件可以用來(lái)在數據庫崩潰時(shí)恢復表結構。
?。?）表空間結構分析
以下為InnoDB的表空間結構圖：
數據段即B+樹(shù)的葉子節點(diǎn)，索引段即為B+樹(shù)的非葉子節點(diǎn)InnoDB存儲引擎的管理是由引擎本身完成的，
表空間（Tablespace）是由分散的段(Segment)組成。一個(gè)段(Segment)包含多個(gè)區（Extent）。
區（Extent）由64個(gè)連續的頁(yè)（Page）組成，每個(gè)頁(yè)大小為16K，即每個(gè)區大小為1MB，創(chuàng )建新表時(shí)，先使用32頁(yè)大小的碎片頁(yè)存放數據，使用完后才是區的申請（InnoDB最多每次申請4個(gè)區，保證數據的順序性能）頁(yè)類(lèi)型有：數據頁(yè)、Undo頁(yè)、系統頁(yè)、事務(wù)數據頁(yè)、插入緩沖位圖頁(yè)、以及插入緩沖空閑列表頁(yè)。
?。?）獨占表空間文件
若將innodb_file_per_table設置為on，則系統將為每一個(gè)表單獨的生成一個(gè)table_name.ibd的文件，
在此文件中，存儲與該表相關(guān)的數據、索引、表的內部數據字典信息。
?。?）共享表空間文件
在InnoDB存儲引擎中，默認表空間文件是ibdata1（主要存儲的是共享表空間數據），初始化為10M，且可以擴展，如下圖所示：
實(shí)際上，InnoDB的表空間文件是可以修改的，使用以下語(yǔ)句就可以修改：
使用共享表空間存儲方式時(shí)，Innodb的所有數據保存在一個(gè)單獨的表空間里面，而這個(gè)表空間可以由很多個(gè)文件組成，一個(gè)表可以跨多個(gè)文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。
從Innodb的官方文檔中可以看到，其表空間的最大限制為64TB，也就是說(shuō)，Innodb的單表限制基本上也在64TB左右了，當然這個(gè)大小是包括這個(gè)表的所有索引等其他相關(guān)數據。
而在使用單獨表空間存儲方式時(shí)，每個(gè)表的數據以一個(gè)單獨的文件來(lái)存放，這個(gè)時(shí)候的單表限制，又變成文件系統的大小限制了。
了解了這些基礎知識之后，下面我們對數據庫表空間文件做相關(guān)的解析， 主要是針對獨占獨占表空間文件（ibd）
因為主鍵索引B+樹(shù)的根頁(yè)，在整個(gè)表空間文件中的第3個(gè)頁(yè)開(kāi)始，所以可以算出它在文件中的偏移量：
另外根據《InnoDB存儲引擎》中描述在根頁(yè)的64偏移量位置前2個(gè)字節，保存了page level的值，
因此我們想要的page level的值在整個(gè)文件中的偏移量為：16384*3+64=49152+64=49216，前2個(gè)字節中。
接下來(lái)我們用hexdump工具，查看表空間文件指定偏移量上的數據：
linetem表的page level為2，B+樹(shù)高度為page level+1=3；
region表的page level為0，B+樹(shù)高度為page level+1=1；
customer表的page level為2，B+樹(shù)高度為page level+1=3；
總結，通過(guò)分析可以看到，實(shí)驗環(huán)境的三個(gè)表：lineitem表的數據行數為600多萬(wàn)，B+樹(shù)高度為3，customer表數據行數只有15萬(wàn)，B+樹(shù)高度也為3。
可以看出盡管數據量差異較大，這兩個(gè)表樹(shù)的高度都是3，換句話(huà)說(shuō)這兩個(gè)表通過(guò)索引查詢(xún)效率并沒(méi)有太大差異，因為都只需要做3次IO。
所以在InnoDB中B+樹(shù)高度一般為1-3層，它就能滿(mǎn)足千萬(wàn)級的數據存儲。
在查找數據時(shí)一次頁(yè)的查找代表一次IO，所以通過(guò)主鍵索引查詢(xún)通常只需要1-3次IO操作即可查找到數據。
前面分析了，假設主鍵ID為bigint類(lèi)型，長(cháng)度為8字節，
而指針大小在InnoDB源碼中設置為6字節，這樣一共14字節。
那么一個(gè)頁(yè)中能存放多少這樣的組合，就代表有多少指針，即 16384 / 14 = 1170。
那么可以算出一棵高度為2 的B+樹(shù)，能存放 1170 * 16 = 18720 條這樣的數據記錄。
同理：
高度為3的B+樹(shù)可以存放的行數 = 1170 * 1170 * 16 = 21902400
所以，千萬(wàn)級的數據存儲只需要約3層B+樹(shù)，所以說(shuō)，根據主鍵id索引查詢(xún)約3次IO便可以找到目標結果。
注意：查詢(xún)數據時(shí)，每加載一頁(yè)（page）代表一次IO，
當然 B+樹(shù)的根節點(diǎn)是常駐內存的，這里少了一次IO。
但是，我們?yōu)槔阌诜治?，在分析的時(shí)候，暫時(shí)不管吧，先看一般情況。
對于一些復雜的查詢(xún)，可能需要走二級索引，那么通過(guò)二級索引查找記錄最多需要花費多少次IO呢？
首先，從二級索引B+樹(shù)中，根據name 找到對應的主鍵id
然后，再根據主鍵id 從 聚簇索引查找到對應的記錄。
如上圖所示，二級索引有3層，聚簇索引有3層，那么最多花費的IO次數是：3+3 = 6 （這里，忽略根節點(diǎn)常駐內存這件事）
聚簇索引默認是主鍵，如果表中沒(méi)有定義主鍵，InnoDB 會(huì )選擇一個(gè)唯一的非空索引代替。
如果連這樣的索引沒(méi)有，InnoDB 會(huì )隱式定義一個(gè)主鍵來(lái)作為聚簇索引。
這也是為什么InnoDB表必須有主鍵，并且推薦使用整型的自增主鍵?。?！InnoDB使用的是聚簇索引，將主鍵組織到一棵B+樹(shù)中，而行數據就儲存在葉子節點(diǎn)上
為啥磁盤(pán)IO的性能低？ 不多說(shuō)啦，具體請參考 的《Java葵花寶典：Java 高性能超底層原理》 視頻和講義，需要的請后臺私信【筆記】即可獲??！
通過(guò)上面的分析可以看出， 如果是 走 非聚集索引查詢(xún)， 需要6次IO，
走 聚焦索引查詢(xún)，需要3次磁盤(pán)IO
當然，以上分析流程，忽略了一些性能的優(yōu)化措施，比如 B+樹(shù)根節點(diǎn) 常駐內存，還有可能命中 查詢(xún)緩存等等。
所以，innodb 單表推薦2kw 記錄，超過(guò)了這個(gè)值可能會(huì )導致B+樹(shù)層級更高，影響查詢(xún)性能。
當然，凡事看場(chǎng)景，上面只是一般的分析。
索引結構不會(huì )影響單表最大行數，2kw也只是推薦值，最終的單表記錄數最大值，受到硬件條件，和各種優(yōu)化措施的影響。
只要按照上面的 流程去作答， 你的答案不是 100分，而是 120分
面試官一定是 心滿(mǎn)意足， 五體投地。。。
猜你喜歡
常見(jiàn)問(wèn)題
1.請問(wèn)哪個(gè)平臺可以在線(xiàn)觀(guān)看國產(chǎn)劇《瘋狂創(chuàng )客》？
神馬影院網(wǎng)友：瘋狂創(chuàng )客高清無(wú)廣告在線(xiàn)播放地址http://www.seseaise.com/ndzvod/14954.html
2.國產(chǎn)劇《瘋狂創(chuàng )客》上映時(shí)間？
3.國產(chǎn)劇《瘋狂創(chuàng )客》演員列表？
4.網(wǎng)友怎么評價(jià)《瘋狂創(chuàng )客》？
Mtime網(wǎng)友：這個(gè)題材，做成這樣，真的很不容易了。尤其是，影片在影像上非常有特點(diǎn)，大量的特寫(xiě)鏡頭、跟拍、手持，以及碎片式剪輯，營(yíng)造出強烈的恐慌與不安感。這種情緒，其實(shí)就是角色在片中的情緒，它以影像的方式，最直接延伸給觀(guān)眾。
丟豆網(wǎng)網(wǎng)友：一個(gè)好的導演，是會(huì )調教演員的，知道演員的優(yōu)勢在哪里。 《瘋狂創(chuàng )客》像極了一道來(lái)自異域的夏日甜品，雖然不是主菜的配置，但卻也有著(zhù)勾人的獨特風(fēng)情。坦白說(shuō)，最初的確是沖著(zhù)未知導演的名號看的，畢竟人家講故事的功力擺在那里，但一開(kāi)始就入了坑也是我怎么都沒(méi)料到的。
豆瓣網(wǎng)網(wǎng)友：放在豆瓣語(yǔ)境下，是部時(shí)至今日終于拍出來(lái)的中國大陸 “高分國產(chǎn)劇”。拿現實(shí)題材拍商業(yè)類(lèi)型片，社會(huì )意義擺在那，群戲也處理得相當不錯。對我們國家而言，這樣的電影多一部是一部，走一步是一步。
常見(jiàn)問(wèn)題
國產(chǎn)劇周榜單
Copyright ? 2024 酷客影院-免費電影在線(xiàn)觀(guān)看_熱播電視劇免費追劇_高清不卡電影網(wǎng)ntdhdz.com.保留所有權利
來(lái)自【網(wǎng)友】的網(wǎng)友評論
2018-03-05 18:56:31
來(lái)自【網(wǎng)友】的網(wǎng)友評論
2018-09-14 11:56:41
來(lái)自【網(wǎng)友】的網(wǎng)友評論
2019-01-08 19:55:51
來(lái)自【第九電影】的網(wǎng)友評論
2019-03-05 12:22:22
來(lái)自【網(wǎng)友】的網(wǎng)友評論
2019-05-08 23:56:37
來(lái)自【網(wǎng)友】的網(wǎng)友評論
2020-02-07 10:16:01