mysql資料庫單表最大儲存依據詳解

2022-07-18 18:02:33

引言

故事從好多年前說起。

想必大家也聽說過資料庫單表建議最大2kw條資料這個說法。如果超過了，效能就會下降得比較厲害。

巧了。

我也聽說過。

但我不接受它的建議，硬是單表裝了1億條資料。

這時候，我們組裡新來的實習生看到了之後，天真無邪的問我："單表不是建議最大兩千萬嗎？為什麼這個表都放了1個億還不分庫分表"？

我能說我是因為懶嗎？我當初設計時哪裡想到這表竟然能漲這麼快。。。

我不能。

說了等於承認自己是開發組裡的毒瘤，雖然我確實是，但我不能承認。

我如坐鍼氈，如芒刺背，如鯁在喉。

開始了一波騷操作。

"我這麼做是有道理的"

"雖然這個表很大，但你有沒有發現它查詢其實還是很快"

"這個2kw是個建議值，我們要來看下這個2kw是怎麼來的"

資料庫單錶行數最大多大？

我們先看下單錶行數理論最大值是多少。

建表的SQL是這麼寫的，

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '名字',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年齡',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=100037 DEFAULT CHARSET=utf8;

其中id就是主鍵。主鍵本身唯一，也就是說主鍵的大小可以限制表的上限。

如果主鍵宣告為int大小，也就是32位元，那麼能支援2^32-1，也就是21個億左右。

如果是bigint，那就是2^64-1，但這個數位太大，一般還沒到這個限制之前，磁碟先受不了。

搞離譜點。

如果我把主鍵宣告為 tinyint，一個位元組，8位元，最大2^8-1，也就是255。

CREATE TABLE `user` (
  `id` tinyint(2) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '名字',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年齡',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

如果我想插入一個id=256的資料，那就會報錯。

mysql> INSERT INTO `tmp` (`id`, `name`, `age`) VALUES (256, '', 60);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

也就是說，tinyint主鍵限制表內最多255條資料。

那除了主鍵，還有哪些因素會影響行數？

索引的結構

索引內部是用的B+樹，這個也是八股文老股了，大家估計也背得很熟了。

為了不讓大家有過於強烈的審醜疲勞，今天我嘗試從另外一個角度給大家講講這玩意。

頁的結構

假設我們有這麼一張user資料表。

其中id是唯一主鍵。

這看起來的一行行資料，為了方便，我們後面就叫它們record吧。

這張表看起來就跟個excel表格一樣。excel的資料在硬碟上是一個xx.excel的檔案。

而上面user表資料，在硬碟上其實也是類似，放在了user.ibd檔案下。含義是user表的innodb data檔案，專業點，又叫表空間。

雖然在資料表裡，它們看起來是挨在一起的。但實際上在user.ibd裡他們被分成很多小份的資料頁，每份大小16k。

類似於下面這樣。

我們把視角聚焦一下，放到頁上面。

整個頁16k，不大，但record這麼多，一頁肯定放不下，所以會分開放到很多頁裡。並且這16k，也不可能全用來放record對吧。

因為record們被分成好多份，放到好多頁裡了，為了唯一標識具體是哪一頁，那就需要引入頁號（其實是一個表空間的地址偏移量）。同時為了把這些資料頁給關聯起來，於是引入了前後指標，用於指向前後的頁。這些都被加到了頁頭裡。

頁是需要讀寫的，16k說小也不小，寫一半電源線被拔了也是有可能發生的，所以為了保證資料頁的正確性，還引入了校驗碼。這個被加到了頁尾。

那剩下的空間，才是用來放我們的record的。而record如果行數特別多的話，進入到頁內時挨個遍歷，效率也不太行，所以為這些資料生成了一個頁目錄，具體實現細節不重要。只需要知道，它可以通過二分查詢的方式將查詢效率從O(n) 變成O(lgn) 。

從頁到索引

如果想查一條record，我們可以把表空間裡每一頁都撈出來，再把裡面的record撈出來挨個判斷是不是我們要找的。

行數量小的時候，這麼操作也沒啥問題。

行數量大了，效能就慢了，於是為了加速搜尋，我們可以在每個資料頁裡選出主鍵id最小的record，而且只需要它們的主鍵id和所在頁的頁號。組成新的record，放入到一個新生成的一個資料頁中，這個新資料頁跟之前的頁結構沒啥區別，而且大小還是16k。

但為了跟之前的資料頁進行區分。資料頁里加入了頁層級（page level）的資訊，從0開始往上算。於是頁與頁之間就有了上下層級的概念，就像下面這樣。

突然頁跟頁之間看起來就像是一棵倒過來的樹了。也就是我們常說的B+樹索引。

最下面那一層，page level 為0，也就是所謂的葉子結點，其餘都叫非葉子結點。

上面展示的是兩層的樹，如果資料變多了，我們還可以再通過類似的方法，再往上構建一層。就成了三層的樹。

那現在我們就可以通過這樣一棵B+樹加速查詢。舉個例子。

比方說我們想要查詢行資料5。會先從頂層頁的record們入手。record裡包含了主鍵id和頁號（頁地址）。看下圖黃色的箭頭，向左最小id是1，向右最小id是7。那id=5的資料如果存在，那必定在左邊箭頭。於是順著的record的頁地址就到了6號資料頁裡，再判斷id=5>4，所以肯定在右邊的資料頁裡，於是載入105號資料頁。在資料頁裡找到id=5的資料行，完成查詢。

另外需要注意的是，上面的頁的頁號並不是連續的，它們在磁碟裡也不一定是挨在一起的。

這個過程中查詢了三個頁，如果這三個頁都在磁碟中（沒有被提前載入到記憶體中），那麼最多需要經歷三次磁碟IO查詢，它們才能被載入到記憶體中。

B+樹承載的記錄數量

從上面的結構裡可以看出B+樹的最末級葉子結點裡放了record資料。而非葉子結點裡則放了用來加速查詢的索引資料。

也就是說

同樣一個16k的頁，非葉子節點裡每一條資料都指向一個新的頁，而新的頁有兩種可能。

如果是末級葉子節點的話，那麼裡面放的就是一行行record資料。
如果是非葉子節點，那麼就會迴圈繼續指向新的資料頁。

假設

非葉子結點內指向其他記憶體頁的指標數量為x
葉子節點內能容納的record數量為y
B+樹的層數為z

那這棵B+樹放的行資料總量等於 (x ^ (z-1)) * y。

x怎麼算

我們回去看資料頁的結構。

非葉子節點裡主要放索引查詢相關的資料，放的是主鍵和指向頁號。

主鍵假設是bigint（8Byte），而頁號在原始碼裡叫FIL_PAGE_OFFSET（4Byte），那麼非葉子節點裡的一條資料是12Byte左右。

整個資料頁16k，頁頭頁尾那部分資料全加起來大概128Byte，加上頁目錄毛估佔1k吧。那剩下的15k除以12Byte，等於1280，也就是可以指向x=1280頁。

我們常說的二元樹指的是一個結點可以發散出兩個新的結點。m叉樹一個節點能指向m個新的結點。這個指向新節點的操作就叫扇出（fanout）。

而上面的B+樹，它能指向1280個新的節點，恐怖如斯，可以說扇出非常高了。

y的計算

葉子節點和非葉子節點的資料結構是一樣的，所以也假設剩下15kb可以發揮。

葉子節點裡放的是真正的行資料。假設一條行資料1kb，所以一頁裡能放y=15行。

行總數計算

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

已知x=1280，y=15。

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

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

這個2.5kw，就是我們常說的單表建議最大行數2kw的由來。畢竟再加一層，資料就大得有點離譜了。三層資料頁對應最多三次磁碟IO，也比較合理。

行數超一億就慢了嗎？

上面假設單行資料用了1kb，所以一個資料頁能放個15行資料。

如果我單行資料用不了這麼多，比如只用了250byte。那麼單個資料頁能放60行資料。

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

你看我一個億的資料，其實也就三層B+樹，在這個B+樹裡要查到某行資料，最多也是三次磁碟IO。所以並不慢。

這就很好的解釋了文章開頭，為什麼我單表1個億，但查詢效能沒啥大毛病。

B樹承載的記錄數量

既然都聊到這裡了，我們就順著這個話題多聊一些吧。

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

它跟B+樹最大的區別在於，B+樹只在末級葉子結點處放資料表行資料，而B樹則會在葉子和非葉子結點上都放。

於是，B樹的結構就類似這樣

B樹將行資料都存在非葉子節點上，假設每個資料頁還是16kb，掐頭去尾每頁剩15kb，並且一條資料表行資料還是佔1kb，就算不考慮各種頁指標的情況下，也只能放個15條資料。資料頁扇出明顯變少了。

計算可承載的總行數的公式也變成了一個等比數列。

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

其中z還是層數的意思。

為了能放2kw左右的資料，需要z>=6。也就是樹需要有6層，查一次要存取6個頁。假設這6個頁並不連續，為了查詢其中一條資料，最壞情況需要進行6次磁碟IO。

而B+樹同樣情況下放2kw資料左右，查一次最多是3次磁碟IO。

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

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

總結

B+樹葉子和非葉子結點的資料頁都是16k，且資料結構一致，區別在於葉子節點放的是真實的行資料，而非葉子結點放的是主鍵和下一個頁的地址。
B+樹一般有兩到三層，由於其高扇出，三層就能支援2kw以上的資料，且一次查詢最多1~3次磁碟IO，效能也還行。
儲存同樣量級的資料，B樹比B+樹層級更高，因此磁碟IO也更多，所以B+樹更適合成為mysql索引。
索引結構不會影響單表最大行數，2kw也只是推薦值，超過了這個值可能會導致B+樹層級更高，影響查詢效能。
單表最大值還受主鍵大小和磁碟大小限制。

參考資料

《MYSQL核心：INNODB儲存引擎卷1》

雖然我在單表裡塞了1億條資料，但這個操作的前提是，我很清楚這不會太影響效能。

這波解釋，毫無破綻，無懈可擊。

到這裡，連我自己都被自己說服了。想必實習生也是。

可惡，這該死的毒瘤竟然有些"知識淵博"。

更多關於mysql單表最大儲存的資料請關注it145.com其它相關文章！