關(guān)于單表能存多少數(shù)據(jù)，阿里 JAVA 開發(fā)手冊提出，建議最大 2000 萬。

然后也看過一篇文章，可以往單表塞 1 億。

當(dāng)然，以上其實(shí)都有一些理論支撐，但是都不全面，也沒有結(jié)合具體實(shí)際的場景。

這篇文章，結(jié)合之前學(xué)習(xí)的知識，進(jìn)行一個(gè)整體匯總，并貼合實(shí)際場景展開。

不 BB，上文章目錄。

01 理論知識

B+ 樹

MySQL 的底層結(jié)構(gòu)用 B+ 樹存儲，這個(gè)估計(jì)地球人都知道。

為了便于后續(xù)講解，先普及幾個(gè)概念：

• 對于非聚集索引，B+ 樹的葉子節(jié)點(diǎn)和非葉子節(jié)點(diǎn)存儲的都是索引指針；
• 對于聚集索引，B+ 樹的非葉子節(jié)點(diǎn)存儲的是索引指針，葉子節(jié)點(diǎn)存儲的是數(shù)據(jù)，順序排列；
• InnoDB 中的 B+ 樹的高度一般會保持在 3 層以內(nèi)，我們就以 3 層來定。

下圖是聚集索引，3 層 B+ 樹的結(jié)構(gòu)：

虛線部分，可以找到對應(yīng)頁碼的數(shù)據(jù)，這里很基礎(chǔ)，不去過多解讀。

頁存儲

B+ 樹節(jié)點(diǎn)的存儲結(jié)構(gòu)是 “頁”，一頁的大小 16 KB。

下面是頁結(jié)構(gòu)示意圖：

再看看對頁結(jié)構(gòu)的解讀：

那一頁能留多少存儲空間呢？

除了 User Records 和 Free Space 以外所占用的存儲是 38 + 56 + 26 + 8 = 128。

當(dāng)新記錄插入到 InnoDB 聚集索引中時(shí)，InnoDB 會嘗試留出 1/16 的頁面空閑以供將來插入和更新索引記錄，所以就只剩下 15/16。

可存儲空間 = 15/16 * 1024 - 128 = 15232 字節(jié)。

行存儲

MySQL 的數(shù)據(jù)是行存儲，MySQL 5.6 默認(rèn)行格式為 COMPACT(緊湊)，5.7 及以后的默認(rèn)行為 DYNAMIC(動態(tài))。

下面是行結(jié)構(gòu)示意圖：

再看看對行結(jié)構(gòu)的解讀：

02 葉子節(jié)點(diǎn)計(jì)算

3 層 B+ 樹最大數(shù)據(jù)量

前面說了，我們的 B+ 樹是 3 層，第一層就一個(gè)根節(jié)點(diǎn)，能存放 X 個(gè)指針。

第二層的每個(gè)節(jié)點(diǎn)，也能存放 X 個(gè)指針，指向第三層 X 個(gè)節(jié)點(diǎn)。

第三層的每個(gè)節(jié)點(diǎn)，存放 Y 個(gè)數(shù)據(jù)。

3 層 B+ 樹最大數(shù)據(jù)量 = x ^ 2 * y。

葉子節(jié)點(diǎn)總數(shù) x ^ 2 計(jì)算

我們先看一頁能存儲多少個(gè)指針?biāo)饕?/span>

每一條索引記錄當(dāng)中都包含了當(dāng)前索引的值 、一個(gè) 6 字節(jié)的指針信息 、一個(gè) 5 字節(jié)的行標(biāo)頭，用來指向下一層數(shù)據(jù)頁的指針。

索引記錄當(dāng)中的指針占用空間我沒在官方文檔里找到，這個(gè) 6 字節(jié)是我參考其他博文，他們說源碼里寫的是 6 字節(jié)。

假設(shè)我們的主鍵 id 為 bigint 型，也就是 8 字節(jié)。

索引指針大小：8 + 6 + 5 = 19 字節(jié)。

前面已經(jīng)算出，每頁可存儲空間 15232 字節(jié)。

單頁可存儲索引指針：15232 / 19 ≈ 801 條。

那算上頁目錄的話，按每個(gè)槽平均 6 條數(shù)據(jù)計(jì)算的話，至少有 801 / 6 ≈ 134 個(gè)槽，需要占用 268 字節(jié)的空間。

把存數(shù)據(jù)的空間分一點(diǎn)給槽的話，我算出來大約可以存 787 條索引數(shù)據(jù)。

單頁數(shù)據(jù)存儲索引指針：

• 最終單頁可存儲 bigint 型索引指針：（15232 - 268）/ 19 ≈ 787 條；
• 最終單頁可存儲 int 型索引指針 993 條。

葉子節(jié)點(diǎn)總數(shù)：

• 主鍵為 bigint 的表可以存放 787 ^ 2 = 619369 個(gè)葉子節(jié)點(diǎn)；
• 主鍵為 int 的表可以存放 993 ^ 2 = 986049 個(gè)葉子節(jié)點(diǎn)。

說明：以上的數(shù)據(jù)計(jì)算，僅供參考，因?yàn)橛械奈恼抡f，在主鍵為 bigint 的情況下，可存放 160 萬葉子節(jié)點(diǎn)，整整多出 65 萬。

03 總記錄數(shù)計(jì)算

溢出頁

前面提到過，MySQL 行存儲格式包括 COMPACT 和 DYNAMIC，我們這里只看 DYNAMIC。

DYNAMIC 怎么理解？

在一行數(shù)據(jù)中，當(dāng)某列太長時(shí)，葉子節(jié)點(diǎn)無需將該數(shù)據(jù)直接存儲 ，而是存儲指向該數(shù)據(jù)的指針，真實(shí)數(shù)據(jù)全部存儲在溢出頁。

使用 DYNAMIC 格式，較短的列會盡可能保留在 B+ 樹節(jié)點(diǎn)中，從而最大限度地減少給定行所需的溢出頁數(shù)。

那 COMPACT 呢？

COMPACT 行格式則是將前 768 個(gè)字節(jié)和 20 字節(jié)的指針存儲在 B+ 樹節(jié)點(diǎn)的記錄中，其余部分存儲在溢出頁上。

這里我們只討論 DYNAMIC 情況。

最少總記錄數(shù)

前面我們提到，最大行長度略小于數(shù)據(jù)庫頁面的一半，之所以是略小于一半，是由于每個(gè)頁面還留了點(diǎn)空間給頁格式的其他內(nèi)容，所以我們可以認(rèn)為每個(gè)頁面最少能放兩條數(shù)據(jù)，每條數(shù)據(jù)略小于 8 KB。

如果某行的數(shù)據(jù)長度超過這個(gè)值，那 InnoDB 肯定會分一些數(shù)據(jù)到 溢出頁當(dāng)中去了，所以我們不考慮。

那每條數(shù)據(jù) 8  KB 的話，每個(gè)葉子節(jié)點(diǎn)就只能存放 2 條數(shù)據(jù)。

在主鍵為 int 的情況下，最少總記錄數(shù)：2 × 986049 ≈ 124 萬。

最多總記錄數(shù)

假設(shè)我們的表是這樣的：

CREATE TABLE`course_schedule` (
`id`intNOTNULL,
`teacher_id`intNOTNULL,
`course_id`intNOTNULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDBDEFAULTCHARSET=utf8;

先來分析一下這張表的行數(shù)據(jù)：無 null 值列表，無可變長字段列表，需要算上事務(wù) ID 和指針字段，需要算上行記錄頭。

每行數(shù)據(jù)占用空間：4 + 4 + 4 + 6 + 7 + 5 = 30。

每個(gè)葉子節(jié)點(diǎn)存放：15232 ÷  30 ≈ 507。

算上頁目錄槽位所占空間，每個(gè)葉子節(jié)點(diǎn)可存放 502 條。

在主鍵為 int 的情況下，最多總記錄數(shù)：502 × 986049 ≈ 5 億。

04 實(shí)際場景

上面的場景是兩個(gè)極端， 我們看一個(gè)具體的示例。

CREATE TABLE`blog` (
`id`bigintunsignedNOTNULL AUTO_INCREMENT COMMENT'博客id',
`author_id`bigintunsignedNOTNULLCOMMENT'作者id',
`title`varchar(50) CHARACTERSET utf8mb4 NOTNULLCOMMENT'標(biāo)題',
`description`varchar(250) CHARACTERSET utf8mb4 NOTNULLCOMMENT'描述',
`school_code`bigintunsignedDEFAULTNULLCOMMENT'院校代碼',
`cover_image`char(32) DEFAULTNULLCOMMENT'封面圖',
`create_time` datetime NOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'創(chuàng)建時(shí)間',
`release_time` datetime DEFAULTNULLCOMMENT'首次發(fā)表時(shí)間',
`modified_time` datetime NOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'修改時(shí)間',
`status`tinyintunsignedNOTNULLCOMMENT'發(fā)表狀態(tài)',
`is_delete`tinyintunsignedNOTNULLDEFAULT0,
  PRIMARY KEY (`id`),
KEY`author_id` (`author_id`),
KEY`school_code` (`school_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULTCHARSET=utf8 COLLATE=utf8_general_mysql500_ci ROW_FORMAT=DYNAMIC;

分析一下這張表的行記錄：

• 行記錄頭信息：肯定得有，占用 5 字節(jié)；
• 可變長度字段列表：表中 title 占用 1 字節(jié)，description 占用 2 字節(jié)，共 3 字節(jié)；
• null 值列表：表中僅 school_code、cover_image、release_time 3 個(gè)字段可為 null，故僅占用 1 字節(jié)；
• 事務(wù) ID 和指針字段：兩個(gè)都得有，占用 13 字節(jié)。

再看看字段內(nèi)容信息：

• id、author_id、school_code 均為 bigint 型，各占用 8 字節(jié)，共 24 字節(jié)；
• create_time、release_time、modified_time 均為 datetime 類型，各占 8 字節(jié)，共 24 字節(jié)；
• status、is_delete 為 tinyint 類型，各占用 1 字節(jié)，共 2 字節(jié)；
• cover_image 為char(32)，字符編碼為表默認(rèn)值 utf8，占用 32 字節(jié)；
• title、description 分別為 varchar(50)、varchar(250)，這兩個(gè)應(yīng)該都不會產(chǎn)生溢出頁（不太確定），字符編碼均為 utf8mb4，實(shí)際生產(chǎn)中 70% 以上都是存的中文( 3 字節(jié))，25% 為英文(1 字節(jié))，還有 5% 為 4 字節(jié)的表情，則存滿的情況下將占用 (50+250)×(0.7×3+0.25×1+0.05×4) = 765 字節(jié)。

統(tǒng)計(jì)上面的所有分析，共占用 869 字節(jié)，則每個(gè)葉子節(jié)點(diǎn)可以存放 15232 ÷ 869 ≈ 17 條，算上頁目錄，仍然能放 17 條。

主鍵為 bigint，最大總記錄數(shù)：17× 619369=10,529,273 ≈ 1053 萬。

閱讀原文：原文鏈接