??導讀

作為一個合格的 DBA，在遇到線上單表數(shù)據(jù)量超過千萬級別的時候，往往會建議用戶通過分表來縮減單表數(shù)據(jù)量，當用戶問為什么單表數(shù)據(jù)量不能超過千萬時，DBA 往往會說：單表數(shù)據(jù)量超過千萬，會影響查詢性能。知其然而不知所以然，學習技術不能停留在表面，而是要進一步去深入挖掘其中的原理，這樣才能不斷進步和成長?；氐竭@個問題：為什么單表數(shù)據(jù)量不能超過兩千萬，其中的依據(jù)是什么？歡迎閱讀。

??目錄

1 自增主鍵角度

2 數(shù)據(jù)頁角度

3 思考

事情是這樣的：

小王最近參加了騰訊的技術面試，面試官向他提了一個經(jīng)典的面試問題：聊聊你日常項目里的分庫分表實踐？

于是小王以過往項目里的某個 case 為例做了回答：

我負責的項目里涉及到存儲用戶操作記錄的功能，因為每天的數(shù)據(jù)量比較大，差不多超過 5000 萬條，所以我另外又做了分庫分表的操作。系統(tǒng)會自動定時生成 3 張表，數(shù)據(jù)分別存儲其中，防止都放在一個表里面導致查詢性能降低。

面試官又問：這里為什么要做一個分庫分表的操作呢？如果放在同一張表里面，為什么會導致查詢性能降低？

小王內(nèi)心 OS：為什么1+1=2？但他還是語氣平常地回答說：

MySQL 單表不要超過 2000 萬行基本上是一個行業(yè)共識，只有當單表行數(shù)超過 500 萬行或者單表容量超過 2GB，我們一般才推薦進行分庫分表。

面試官點了點頭表示認可，卻也沒有在這個問題上繼續(xù)深究，繼而問起了別的問題，不久后就結(jié)束了面試。小王回過神來以后復盤這次面試過程，覺得自己在 MySQL 分庫分表問題上沒有回答得特別到位，于是他開始進一步地深究起來這個“1+1=2”的問題。

01

自增主鍵角度

我們先來看看單表數(shù)據(jù)量理論上最大值是多少？

假設我們建表，ID 是自增主鍵，也就是說主鍵的大小可以限制表的上限。如果主鍵聲明為 int 類型，那么 int 類型最大為2的32次方 – 1 ，也就是21億左右；

如果主鍵聲明為 bigint 類型，那么 bigint 類型最大為2的64次方 – 1，這個數(shù)字實在太大了，一般還沒到這個限制，磁盤就撐不住了；

如果主鍵聲明為tinyint類型，那么 tinyint 類型最大為2的8次方 – 1，也就是255，所以如果我插入一條 ID=256 的數(shù)據(jù)，就會報錯；

上面是從自增主鍵的角度來講述單表最大數(shù)據(jù)量理論上能達到多少，那么接下來從另一個角度“數(shù)據(jù)頁”來闡述一下，單表數(shù)據(jù)量最大能達到多少，依據(jù)是什么？

02

數(shù)據(jù)頁角度

假設我們有一張 user 表，其中 ID 是自增主鍵，那么該表在硬盤文件上是 user.ibd（innodb 數(shù)據(jù)文件，又叫表空間文件）。這個數(shù)據(jù)文件被劃分成很多的數(shù)據(jù)頁，每個數(shù)據(jù)頁大小是16K。

?? 一個數(shù)據(jù)頁16K，表的數(shù)據(jù)量很多，一個數(shù)據(jù)頁可能放不下那么多數(shù)據(jù)，所以數(shù)據(jù)被分成好多份，存放在不同的數(shù)據(jù)頁，為了標識具體是哪一個數(shù)據(jù)頁，所以需要有頁號來標識；

?? 同時為了把這些存放數(shù)據(jù)的數(shù)據(jù)頁關聯(lián)起來，又引入了前后指針，用于指向前后的頁；

?? 數(shù)據(jù)頁需要讀寫，寫入到一半的過程中可能會發(fā)生了意外斷電等情況，所以為了保證數(shù)據(jù)頁的準確性，還引入了校驗碼；

?? 同時為了在數(shù)據(jù)頁搜索數(shù)據(jù)提高效率，數(shù)據(jù)頁內(nèi)部還生成了頁目錄；

?? 除了上述所說的，數(shù)據(jù)頁內(nèi)剩下的空間就用來存放實際的數(shù)據(jù)；

即數(shù)據(jù)頁的結(jié)構如下：

數(shù)據(jù)是以數(shù)據(jù)頁的形式進行存儲，數(shù)據(jù)頁和數(shù)據(jù)頁之間是以B+樹的形式進行關聯(lián)，例如：

其中，葉子節(jié)點的數(shù)據(jù)頁存放的是實際存儲的數(shù)據(jù)，非葉子節(jié)點存放的是索引內(nèi)容。B+樹的每一層代表一次磁盤 IO。舉個例子，如果我要尋找 ID=5 的記錄，從頂部非葉子節(jié)點開始查找，由于 ID=5 大于1并且小于7，故應該往左邊尋找，來到頁號為6的數(shù)據(jù)頁，由于5大于4，故應該往右邊尋找，來到頁號為105的數(shù)據(jù)頁，找到 ID=5 的記錄，完成查詢。這個過程中查詢了三個數(shù)據(jù)頁，如果這三個數(shù)據(jù)頁都沒有加載到內(nèi)存，那么就需要經(jīng)歷三次磁盤 IO 查詢。

了解完 B+樹是如何存儲數(shù)據(jù)的，我們就可以開始進行數(shù)據(jù)的估算。

假設：非葉子節(jié)點內(nèi)指向其他數(shù)據(jù)頁的指針數(shù)量為 X（即非葉子節(jié)點的最大子節(jié)點數(shù)為 X）；每個葉子節(jié)點可以存儲的行記錄數(shù)為 Y；B+樹的高度為 N（即 B+樹的層數(shù)）；

對于一個高度為 N 的 B+樹，頂層（根節(jié)點）有一個非葉子節(jié)點，那么第二層就有X個節(jié)點，第三層就有 X 的2次方個節(jié)點，第四層就有 X 的三次方個節(jié)點，以此類推，第 N 層（即葉子節(jié)點所在的第 N 層）就有 X 的 N-1 次方個節(jié)點；
在 B+ 樹中，所有的記錄都存儲在葉子節(jié)點中，假設每個葉子節(jié)點都可以存儲的行記錄數(shù)為 Y；
那么 B+ 樹可以存儲的數(shù)據(jù)總量為葉子節(jié)點總數(shù)乘以每個葉子節(jié)點存儲的記錄數(shù)，即：M=（X 的 N-1 次方）乘以 Y；

代入計算：

一個數(shù)據(jù)頁大小16K，扣除頁號、前后指針、頁目錄，校驗碼等信息，實際可以存儲數(shù)據(jù)的大約為15K，假設主鍵ID為bigint型，那么主鍵 ID 占用8個 byte，頁號占用4個 byte，則 X=15*1024/(8 + 4) 等于1280；
一個數(shù)據(jù)頁實際可以存儲數(shù)據(jù)的空間大小，大約為15K，假設一條行記錄占用的空間大小為1K，那么一個數(shù)據(jù)頁就可以存儲15條行記錄，即 Y=15；
假設 B+樹是兩層的：則 N=2，即 M=1280的（2-1）次方 * 15 ≈ 2w ；
假設 B+樹是三層的：則 N=3，即 M=1280的2次方 * 15 ≈ 2.5 kw；
假設 B+樹是四層的：則 N=4，即 M=1280的3次方 * 15 ≈ 300億；