MySQL單表數(shù)據(jù)最大不要超過多少行？為什么？

故事從好多年前說起。

想必大家也聽說過數(shù)據(jù)庫(kù)單表建議最大2kw 條數(shù)據(jù)這個(gè)說法。如果超過了，性能就會(huì)下降得比較厲害。

巧了。

我也聽說過。

但我不接受它的建議，硬是單表裝了1億條數(shù)據(jù)。

這時(shí)候，我們組里新來的實(shí)習(xí)生看到了之后，天真無邪的問我："單表不是建議最大兩千萬嗎？為什么這個(gè)表都放了1個(gè)億還不分庫(kù)分表 "？

我能說我是因?yàn)閼?/strong> 嗎？我當(dāng)初設(shè)計(jì)時(shí)哪里想到這表竟然能漲這么快。。。

CREATETABLE`user`(
`id`int(10)unsignedNOTNULLAUTO_INCREMENTCOMMENT'主鍵',
`name`varchar(100)NOTNULLDEFAULT''COMMENT'名字',
`age`int(11)NOTNULLDEFAULT'0'COMMENT'年齡',
PRIMARYKEY(`id`),
KEY`idx_age`(`age`)
)ENGINE=InnoDBAUTO_INCREMENT=100037DEFAULTCHARSET=utf8;

CREATETABLE`user`(
`id`tinyint(2)unsignedNOTNULLAUTO_INCREMENTCOMMENT'主鍵',
`name`varchar(100)NOTNULLDEFAULT''COMMENT'名字',
`age`int(11)NOTNULLDEFAULT'0'COMMENT'年齡',
PRIMARYKEY(`id`),
KEY`idx_age`(`age`)
)ENGINE=InnoDBAUTO_INCREMENT=0DEFAULTCHARSET=utf8;

mysql>INSERTINTO`tmp`(`id`,`name`,`age`)VALUES(256,'',60);
ERROR1264(22003):Outofrangevalueforcolumn'id'atrow1

比方說我們想要查找行數(shù)據(jù)5。會(huì)先從頂層頁的record們?nèi)胧帧?strong>record里包含了主鍵id和頁號(hào)（頁地址） ?？聪聢D黃色的箭頭，向左最小id是1，向右最小id是7。那id=5的數(shù)據(jù)如果存在，那必定在左邊箭頭。于是順著的record的頁地址就到了6號(hào)數(shù)據(jù)頁里，再判斷id=5>4，所以肯定在右邊的數(shù)據(jù)頁里，于是加載105號(hào)數(shù)據(jù)頁。在數(shù)據(jù)頁里找到id=5的數(shù)據(jù)行，完成查詢。

B+樹查詢過程

另外需要注意的是，上面的頁的頁號(hào)并不是連續(xù)的，它們?cè)诖疟P里也不一定是挨在一起的。

這個(gè)過程中查詢了三個(gè)頁，如果這三個(gè)頁都在磁盤中（沒有被提前加載到內(nèi)存中），那么最多 需要經(jīng)歷三次磁盤IO查詢 ，它們才能被加載到內(nèi)存中。

B+樹承載的記錄數(shù)量

從上面的結(jié)構(gòu)里可以看出B+樹的最末級(jí)葉子結(jié)點(diǎn) 里放了record數(shù)據(jù)。而非葉子結(jié)點(diǎn) 里則放了用來加速查詢的索引數(shù)據(jù)。

也就是說

同樣一個(gè)16k的頁，非葉子節(jié)點(diǎn)里每一條數(shù)據(jù)都指向一個(gè)新的頁，而新的頁有兩種可能。

如果是末級(jí)葉子節(jié)點(diǎn)的話，那么里面放的就是一行行record數(shù)據(jù)。

如果是非葉子節(jié)點(diǎn)，那么就會(huì)循環(huán)繼續(xù)指向新的數(shù)據(jù)頁。

假設(shè)

非葉子結(jié)點(diǎn)內(nèi)指向其他內(nèi)存頁的指針數(shù)量為x

葉子節(jié)點(diǎn)內(nèi)能容納的record數(shù)量為y

B+樹的層數(shù)為z

總行數(shù)的計(jì)算方法

那這棵B+樹放的行數(shù)據(jù)總量 等于 (x ^ (z-1)) * y。

x怎么算

我們回去看數(shù)據(jù)頁的結(jié)構(gòu)。

頁結(jié)構(gòu)

非葉子節(jié)點(diǎn)里主要放索引查詢相關(guān)的數(shù)據(jù)，放的是主鍵和指向頁號(hào)。

主鍵假設(shè)是bigint（8Byte），而頁號(hào)在源碼里叫FIL_PAGE_OFFSET（4Byte），那么非葉子節(jié)點(diǎn)里的一條數(shù)據(jù)是12Byte左右。

整個(gè)數(shù)據(jù)頁16k， 頁頭頁尾那部分?jǐn)?shù)據(jù)全加起來大概128Byte，加上頁目錄毛估占1k吧。那剩下的15k 除以12Byte，等于1280，也就是可以指向x=1280頁 。

我們常說的二叉樹指的是一個(gè)結(jié)點(diǎn)可以發(fā)散出兩個(gè)新的結(jié)點(diǎn)。m叉樹一個(gè)節(jié)點(diǎn)能指向m個(gè)新的結(jié)點(diǎn)。這個(gè)指向新節(jié)點(diǎn)的操作就叫扇出（fanout） 。

而上面的B+樹，它能指向1280個(gè)新的節(jié)點(diǎn)，恐怖如斯，可以說扇出非常高 了。

y的計(jì)算

葉子節(jié)點(diǎn)和非葉子節(jié)點(diǎn)的數(shù)據(jù)結(jié)構(gòu)是一樣的，所以也假設(shè)剩下15kb可以發(fā)揮。

葉子節(jié)點(diǎn)里放的是真正的行數(shù)據(jù)。假設(shè)一條行數(shù)據(jù)1kb，所以一頁里能放y=15行 。

行總數(shù)計(jì)算

回到 (x ^ (z-1)) * y 這個(gè)公式。

已知x=1280，y=15。

假設(shè)B+樹是兩層 ，那z=2。則是(1280 ^ (2-1)) * 15 ≈ 2w

假設(shè)B+樹是三層 ，那z=3。則是(1280 ^ (3-1)) * 15 ≈ 2.5kw

這個(gè)2.5kw，就是我們常說的單表建議最大行數(shù)2kw的由來。 畢竟再加一層，數(shù)據(jù)就大得有點(diǎn)離譜了。三層數(shù)據(jù)頁對(duì)應(yīng)最多三次磁盤IO，也比較合理。

行數(shù)超一億就慢了嗎？

上面假設(shè)單行數(shù)據(jù)用了1kb，所以一個(gè)數(shù)據(jù)頁能放個(gè)15行數(shù)據(jù)。

如果我單行數(shù)據(jù)用不了這么多，比如只用了250byte。那么單個(gè)數(shù)據(jù)頁能放60行數(shù)據(jù)。

那同樣是三層B+樹，單表支持的行數(shù)就是 (1280 ^ (3-1)) * 60 ≈ 1個(gè)億。

你看我一個(gè)億的數(shù)據(jù)，其實(shí)也就三層B+樹，在這個(gè)B+樹里要查到某行數(shù)據(jù)，最多也是三次磁盤IO。所以并不慢。

這就很好的解釋了文章開頭，為什么我單表1個(gè)億，但查詢性能沒啥大毛病。

B樹承載的記錄數(shù)量

既然都聊到這里了，我們就順著這個(gè)話題多聊一些吧。

我們都知道，現(xiàn)在mysql的索引都是B+樹，而有一種樹，跟B+樹很像，叫B樹，也叫B-樹 。

它跟B+樹最大的區(qū)別在于，B+樹只在末級(jí)葉子結(jié)點(diǎn)處放數(shù)據(jù)表行數(shù)據(jù)，而B樹則會(huì)在葉子和非葉子結(jié)點(diǎn)上都放。

于是，B樹的結(jié)構(gòu)就類似這樣

B樹結(jié)構(gòu)

B樹將行數(shù)據(jù)都存在非葉子節(jié)點(diǎn)上，假設(shè)每個(gè)數(shù)據(jù)頁還是16kb，掐頭去尾每頁剩15kb，并且一條數(shù)據(jù)表行數(shù)據(jù)還是占1kb，就算不考慮各種頁指針的情況下，也只能放個(gè)15條數(shù)據(jù)。數(shù)據(jù)頁扇出明顯變少了。

計(jì)算可承載的總行數(shù)的公式也變成了一個(gè)等比數(shù)列 。

15+15^2+15^3+...+15^z

其中z還是層數(shù) 的意思。

為了能放2kw左右的數(shù)據(jù)，需要z>=6。也就是樹需要有6層，查一次要訪問6個(gè)頁。假設(shè)這6個(gè)頁并不連續(xù)，為了查詢其中一條數(shù)據(jù)，最壞情況需要進(jìn)行6次磁盤IO 。

而B+樹同樣情況下放2kw數(shù)據(jù)左右，查一次最多是3次磁盤IO。

磁盤IO越多則越慢，這兩者在性能上差距略大。

為此，B+樹比B樹更適合成為mysql的索引。

總結(jié)

B+樹葉子和非葉子結(jié)點(diǎn)的數(shù)據(jù)頁都是16k，且數(shù)據(jù)結(jié)構(gòu)一致，區(qū)別在于葉子節(jié)點(diǎn)放的是真實(shí)的行數(shù)據(jù)，而非葉子結(jié)點(diǎn)放的是主鍵和下一個(gè)頁的地址。

B+樹一般有兩到三層，由于其高扇出，三層就能支持2kw以上的數(shù)據(jù)，且一次查詢最多1~3次磁盤IO，性能也還行。

存儲(chǔ)同樣量級(jí)的數(shù)據(jù)，B樹比B+樹層級(jí)更高，因此磁盤IO也更多，所以B+樹更適合成為mysql索引。

索引結(jié)構(gòu)不會(huì)影響單表最大行數(shù)，2kw也只是推薦值，超過了這個(gè)值可能會(huì)導(dǎo)致B+樹層級(jí)更高，影響查詢性能。

單表最大值還受主鍵大小和磁盤大小限制。

審核編輯：劉清