面試回答：MySQL每張表最好不超過(guò)2000萬(wàn)數(shù)據(jù)，對(duì)不對(duì)？

MySQL中每張表到底能存多少數(shù)據(jù)？ 實(shí)際情況下，每張表由于自身的字段不同、字段所占用的空間不同等原因，它們?cè)谧罴研阅芟驴梢源娣诺臄?shù)據(jù)量也就不同，需要手動(dòng)計(jì)算才行。

事情是這樣的

下面是我朋友的面試記錄：

面試官：講一下你實(shí)習(xí)做了什么。

朋友：我在實(shí)習(xí)期間做了一個(gè)存儲(chǔ)用戶(hù)操作記錄的功能，主要是從MQ獲取上游服務(wù)發(fā)送過(guò)來(lái)的用戶(hù)操作信息，然后把這些信息存到MySQL里面，提供給數(shù)倉(cāng)的同事使用。

朋友：由于數(shù)據(jù)量比較大，每天大概有四五千多萬(wàn)條，所以我還給它做了分表的操作。每天定時(shí)生成3張表，然后將數(shù)據(jù)取模分別存到這三張表里，防止表內(nèi)數(shù)據(jù)過(guò)多導(dǎo)致查詢(xún)速度降低。

這表述，好像沒(méi)什么問(wèn)題是吧，別急，接著看：

面試官：那你為什么要分三張表呢，兩張表不行嗎？四張表不行嗎？

朋友：因?yàn)镸ySQL每張表最好不超過(guò)2000萬(wàn)條數(shù)據(jù)，否則會(huì)導(dǎo)致查詢(xún)速度降低，影響性能。我們每天的數(shù)據(jù)大概是在五千萬(wàn)條左右，所以分成三張表比較穩(wěn)妥。

面試官：還有嗎？

朋友： 沒(méi)有了……你干嘛，哎呦

面試官：那你先回去等通知吧。

講完了，看出什么了嗎，你們覺(jué)得我這位朋友回答的有什么問(wèn)題嗎？

前言

很多人說(shuō)，MySQL每張表最好不要超過(guò)2000萬(wàn)條數(shù)據(jù)，否則就會(huì)導(dǎo)致性能下降。阿里的Java開(kāi)發(fā)手冊(cè)上也提出：?jiǎn)伪硇袛?shù)超過(guò) 500 萬(wàn)行或者單表容量超過(guò) 2GB，才推薦進(jìn)行分庫(kù)分表。

但實(shí)際上，這個(gè)2000萬(wàn)或者500萬(wàn)都只是一個(gè)大概的數(shù)字，并不適用于所有場(chǎng)景，如果盲目的以為表數(shù)據(jù)只要不超過(guò)2000萬(wàn)條就沒(méi)問(wèn)題了，很可能會(huì)導(dǎo)致系統(tǒng)的性能大幅下降。

實(shí)際情況下，每張表由于自身的字段不同、字段所占用的空間不同等原因，它們?cè)谧罴研阅芟驴梢源娣诺臄?shù)據(jù)量也就不同。

那么，該如何計(jì)算出每張表適合的數(shù)據(jù)量呢？別急，慢慢往下看。

本文適合的讀者

閱讀本文你需要有一定的MySQL基礎(chǔ)，最好對(duì)InnoDB和B+樹(shù)都有一定的了解，可能需要有一年以上的MySQL學(xué)習(xí)經(jīng)驗(yàn)（大概一年？），知道 “InnoDB中B+樹(shù)的高度一般保持在三層以?xún)?nèi)會(huì)比較好” 這條理論知識(shí)。

本文主要是針對(duì) “InnoDB中高度為3的B+樹(shù)最多可以存多少數(shù)據(jù)” 這一話(huà)題進(jìn)行講解的。且本文對(duì)數(shù)據(jù)的計(jì)算比較嚴(yán)格（至少比網(wǎng)上95%以上的相關(guān)博文都要嚴(yán)格），如果你比較在意這些細(xì)節(jié)并且目前不太清楚的話(huà)，請(qǐng)繼續(xù)往下閱讀。

閱讀本文你大概需要花費(fèi)10-20分鐘的時(shí)間，如果你在閱讀的過(guò)程中對(duì)數(shù)據(jù)進(jìn)行驗(yàn)算的話(huà)，可能要花費(fèi)30分鐘左右。

本文思維導(dǎo)圖

千萬(wàn)級(jí)數(shù)據(jù)并發(fā)如何處理？進(jìn)入學(xué)習(xí)

基礎(chǔ)知識(shí)快速回顧

眾所周知，MySQL中InnoDB的存儲(chǔ)結(jié)構(gòu)是B+樹(shù)，B+樹(shù)大家都熟悉吧？特性大概有以下幾點(diǎn)，一起快速回顧一下吧！

注：下面這這些內(nèi)容都是精華，看不懂或者不理解的同學(xué)建議先收藏本文，之后有知識(shí)基礎(chǔ)了再回來(lái)看。??

• 一張數(shù)據(jù)表一般對(duì)應(yīng)一顆或多顆樹(shù)的存儲(chǔ)，樹(shù)的數(shù)量與建索引的數(shù)量有關(guān)，每個(gè)索引都會(huì)有一顆單獨(dú)的樹(shù)。

• 聚簇索引和非聚簇索引：

  主鍵索引也是聚簇索引，非主鍵索引都是非聚簇索引。除格式信息外，兩種索引的非葉子節(jié)點(diǎn)都是只存索引數(shù)據(jù)的，比如索引為id，那非葉子節(jié)點(diǎn)就是存的id數(shù)據(jù)。

  葉子節(jié)點(diǎn)的區(qū)別如下：

  • 聚簇索引的葉子節(jié)點(diǎn)一般情況下存的是這條數(shù)據(jù)的所有字段信息。所以我們 select * from table where id = 1 的時(shí)候，都是要去葉子節(jié)點(diǎn)拿數(shù)據(jù)的。
  • 非聚簇索引的葉子節(jié)點(diǎn)存的是這條數(shù)據(jù)所對(duì)應(yīng)的主鍵和索引列信息。比如這條非聚簇索引是username，然后表的主鍵是id，那該非聚簇索引的葉子節(jié)點(diǎn)存的就是 username 和 id，而不存其他字段。 相當(dāng)于是先從非聚簇索引查到主鍵的值，再根據(jù)主鍵索引去查數(shù)據(jù)內(nèi)容，一般情況下要查兩次（除非索引覆蓋），這也稱(chēng)之為 回表 ，就有點(diǎn)類(lèi)似于存了個(gè)指針，指向了數(shù)據(jù)存放的真實(shí)地址。

• B+樹(shù)的查詢(xún)是從上往下一層層查詢(xún)的，一般情況下我們認(rèn)為B+樹(shù)的高度保持在3層以?xún)?nèi)是比較好的，也就是上兩層是索引，最后一層存數(shù)據(jù)，這樣查表的時(shí)候只需要進(jìn)行3次磁盤(pán)IO就可以了(實(shí)際上會(huì)少一次，因?yàn)楦?jié)點(diǎn)會(huì)常駐內(nèi)存)，且能夠存放的數(shù)據(jù)量也比較可觀。

  如果數(shù)據(jù)量過(guò)大，導(dǎo)致B+數(shù)變成4層了，則每次查詢(xún)就需要進(jìn)行4次磁盤(pán)IO了，從而使性能下降。所以我們才會(huì)去計(jì)算InnoDB的3層B+樹(shù)最多可以存多少條數(shù)據(jù)。

• MySQL每個(gè)節(jié)點(diǎn)大小默認(rèn)為16KB，也就是每個(gè)節(jié)點(diǎn)最多存16KB的數(shù)據(jù)，可以修改，最大64KB，最小4KB。

  擴(kuò)展：那如果某一行的數(shù)據(jù)特別大，超過(guò)了節(jié)點(diǎn)的大小怎么辦？

  MySQL5.7文檔的解釋是：

  • 對(duì)于 4KB、8KB、16KB 和 32KB設(shè)置 ，最大行長(zhǎng)度略小于數(shù)據(jù)庫(kù)頁(yè)面的一半 。例如：對(duì)于默認(rèn)的 16KB頁(yè)大小，最大行長(zhǎng)度略小于 8KB ，默認(rèn)32KB的頁(yè)大小，則最大行長(zhǎng)度略小于16KB。

  • 而對(duì)于 64KB 頁(yè)面，最大行則長(zhǎng)度略小于 16KB。

  • 如果行超過(guò)最大行長(zhǎng)度， 則將可變長(zhǎng)度列用外部頁(yè)存儲(chǔ)，直到該行符合最大行長(zhǎng)度限制。就是說(shuō)把varchar、text這種長(zhǎng)度可變的存到外部頁(yè)中，來(lái)減小這一行的數(shù)據(jù)長(zhǎng)度。

文檔地址：MySQL :: MySQL 5.7 Reference Manual :: 14.12.2 File Space Management

• MySQL查詢(xún)速度主要取決于磁盤(pán)的讀寫(xiě)速度，因?yàn)镸ySQL查詢(xún)的時(shí)候每次只讀取一個(gè)節(jié)點(diǎn)到內(nèi)存中，通過(guò)這個(gè)節(jié)點(diǎn)的數(shù)據(jù)找到下一個(gè)要讀取的節(jié)點(diǎn)位置，再讀取下一個(gè)節(jié)點(diǎn)的數(shù)據(jù)，直到查詢(xún)到需要的數(shù)據(jù)或者發(fā)現(xiàn)數(shù)據(jù)不存在。

  肯定有人要問(wèn)了，每個(gè)節(jié)點(diǎn)內(nèi)的數(shù)據(jù)難道不用查詢(xún)嗎？這里的耗時(shí)怎么不計(jì)算？

  這是因?yàn)樽x取完整個(gè)節(jié)點(diǎn)的數(shù)據(jù)后，會(huì)存到內(nèi)存當(dāng)中，在內(nèi)存中查詢(xún)節(jié)點(diǎn)數(shù)據(jù)的耗時(shí)其實(shí)是很短的，再配合MySQL的查詢(xún)方式，時(shí)間復(fù)雜度差不多為

  $O(log_2N)$

  O(log2N) ，相比磁盤(pán)IO來(lái)說(shuō)，可以忽略不計(jì)。

MySQL InnoDB 節(jié)點(diǎn)的儲(chǔ)存內(nèi)容

在Innodb的B+樹(shù)中，我們常說(shuō)的節(jié)點(diǎn)被稱(chēng)之為 頁(yè)(page)，每個(gè)頁(yè)當(dāng)中存儲(chǔ)了用戶(hù)數(shù)據(jù)，所有的頁(yè)合在一起組成了一顆B+樹(shù)（當(dāng)然實(shí)際會(huì)復(fù)雜很多，但我們只是要計(jì)算可以存多少條數(shù)據(jù)，所以姑且可以這么理解?）。

頁(yè) 是InnoDB存儲(chǔ)引擎管理數(shù)據(jù)庫(kù)的最小磁盤(pán)單位，我們常說(shuō)每個(gè)節(jié)點(diǎn)16KB，其實(shí)就是指每頁(yè)的大小為16KB。

這16KB的空間，里面需要存儲(chǔ) 頁(yè)格式 信息和 行格式 信息，其中行格式信息當(dāng)中又包含一些元數(shù)據(jù)和用戶(hù)數(shù)據(jù)。所以我們?cè)谟?jì)算的時(shí)候，要把這些數(shù)據(jù)的都計(jì)算在內(nèi)。

頁(yè)格式

每一頁(yè)的基本格式，也就是每一頁(yè)都會(huì)包含的一些信息，總結(jié)表格如下：

示意圖：

頁(yè)格式這塊的內(nèi)容，我在官網(wǎng)翻了好久，硬是沒(méi)找到?。。。。不知道是沒(méi)寫(xiě)還是我眼瞎，有找到的朋友希望可以在評(píng)論區(qū)幫我掛出來(lái)?。

所以上面頁(yè)格式的表格內(nèi)容主要是基于一些博客中學(xué)習(xí)總結(jié)的。

另外，當(dāng)新記錄插入到 InnoDB 聚集索引中時(shí)，InnoDB 會(huì)嘗試留出 1/16 的頁(yè)面空閑以供將來(lái)插入和更新索引記錄。如果按順序（升序或降序）插入索引記錄，則生成的頁(yè)大約可用 15/16 的空間。如果以隨機(jī)順序插入記錄，則頁(yè)大約可用 1/2 到 15/16 的空間。參考文檔：MySQL :: MySQL 5.7 Reference Manual :: 14.6.2.2 The Physical Structure of an InnoDB Index

除了 User Records和Free Space 以外所占用的內(nèi)存是

38+56+26+8=128 字節(jié)，每一頁(yè)留給用戶(hù)數(shù)據(jù)的空間就還剩

16×1615×1024?128=15232 字節(jié)（保留了1/16）。

當(dāng)然，這是最小值，因?yàn)槲覀儧](méi)有考慮頁(yè)目錄。頁(yè)目錄留在后面根據(jù)再去考慮，這個(gè)得根據(jù)表字段來(lái)計(jì)算。

行格式

首先，我覺(jué)得有必要提一嘴，MySQL5.6的默認(rèn)行格式為COMPACT(緊湊)，5.7及以后的默認(rèn)行格式為DYNAMIC(動(dòng)態(tài))，不同的行格式存儲(chǔ)的方式也是有區(qū)別的，還有其他的兩種行格式，本文后續(xù)的內(nèi)容主要是基于DYNAMIC(動(dòng)態(tài))進(jìn)行講解的。

官方文檔鏈接：MySQL :: MySQL 5.7 參考手冊(cè) :: 14.11 InnoDB 行格式（包括下面的行格式內(nèi)容大都可以在里面找到）

每行記錄都包含以下這些信息，其中大都是可以從官方文檔當(dāng)中找到的。我這里寫(xiě)的不是特別詳細(xì)，僅寫(xiě)了一些能夠我們計(jì)算空間的知識(shí)，更詳細(xì)內(nèi)容可以去網(wǎng)上搜索 “MySQL 行格式”。

示意圖：

另外還有幾點(diǎn)需要注意：

溢出頁(yè)（外部頁(yè)）的存儲(chǔ)

注意：這一點(diǎn)是DYNAMIC的特性。

當(dāng)使用 DYNAMIC 創(chuàng)建表時(shí)，InnoDB 會(huì)將較長(zhǎng)的可變長(zhǎng)度列（比如 VARCHAR、VARBINARY、BLOB 和 TEXT 類(lèi)型）的值剝離出來(lái)，存儲(chǔ)到一個(gè)溢出頁(yè)上，只在該列上保留一個(gè) 20 字節(jié)的指針指向溢出頁(yè)。

而 COMPACT 行格式（MySQL5.6默認(rèn)格式）則是將前 768 個(gè)字節(jié)和 20 字節(jié)的指針存儲(chǔ)在 B+ 樹(shù)節(jié)點(diǎn)的記錄中，其余部分存儲(chǔ)在溢出頁(yè)上。

列是否存儲(chǔ)在頁(yè)外取決于頁(yè)大小和行的總大小。當(dāng)一行太長(zhǎng)時(shí)，選擇最長(zhǎng)的列進(jìn)行頁(yè)外存儲(chǔ)，直到聚集索引記錄適合 B+ 樹(shù)頁(yè)（文檔里沒(méi)說(shuō)具體是多少?）。小于或等于 40 字節(jié)的 TEXT 和 BLOB 直接存儲(chǔ)在行內(nèi)，不會(huì)分頁(yè)。

優(yōu)點(diǎn)

DYNAMIC 行格式避免了用大量數(shù)據(jù)填充 B+ 樹(shù)節(jié)點(diǎn)從而導(dǎo)致長(zhǎng)列的問(wèn)題。

DYNAMIC 行格式的想法是，如果長(zhǎng)數(shù)據(jù)值的一部分存儲(chǔ)在頁(yè)外，則通常將整個(gè)值存儲(chǔ)在頁(yè)外是最有效的。

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

字符編碼不同情況下的存儲(chǔ)

char 、varchar、text 等需要設(shè)置字符編碼的類(lèi)型，在計(jì)算所占用空間時(shí)，需要考慮不同編碼所占用的空間。

varchar、text等類(lèi)型會(huì)有長(zhǎng)度字段列表來(lái)記錄他們所占用的長(zhǎng)度，但char是固定長(zhǎng)度的類(lèi)型，情況比較特殊，假設(shè)字段 name 的類(lèi)型為 char(10) ，則有以下情況：

• 對(duì)于長(zhǎng)度固定的字符編碼（比如ASCII碼），字段 name 將以固定長(zhǎng)度格式存儲(chǔ)，ASCII碼每個(gè)字符占一個(gè)字節(jié)，那 name 就是占用 10 個(gè)字節(jié)。

• 對(duì)于長(zhǎng)度不固定的字符編碼（比如utf8mb4），至少將為 name 保留 10 個(gè)字節(jié)。如果可以，InnoDB會(huì)通過(guò)修剪尾部空格空間的方式來(lái)將其存到 10 個(gè)字節(jié)中。

  如果空格剪完了還存不下，則將尾隨空格修剪為 列值字節(jié)長(zhǎng)度的最小值（一般是 1 字節(jié)）。

  列的最大長(zhǎng)度為：

  $字符編碼的最大字符長(zhǎng)度 times N$

  字符編碼的最大字符長(zhǎng)度×N，比如 name 字段的編碼為 utf8mb4，那就是

  $4 times 10$

  4×10。

• 大于或等于 768 字節(jié)的 char 列會(huì)被看成是可變長(zhǎng)度字段（就像varchar一樣），可以跨頁(yè)存儲(chǔ)。例如，utf8mb4 字符集的最大字節(jié)長(zhǎng)度為 4，則 char(255) 列將可能會(huì)超過(guò) 768 個(gè)字節(jié)，進(jìn)行跨頁(yè)存儲(chǔ)。

說(shuō)實(shí)話(huà)對(duì)char的這個(gè)設(shè)計(jì)我是不太理解的，盡管看了很久，包括官方文檔和一些博客?，希望懂的同學(xué)可以在評(píng)論區(qū)解惑：

對(duì)于長(zhǎng)度不固定的字符編碼這塊，char是不是有點(diǎn)像是一個(gè)長(zhǎng)度可變的類(lèi)型了？我們常用的 utf8mb4，占用為 1 ~ 4 字節(jié)，那么 char(10) 所占用的空間就是 10 ~ 40 字節(jié)，這個(gè)變化還是挺大的啊，但是它并沒(méi)有留足夠的空間給它，也沒(méi)有使用可變長(zhǎng)度字段列表去記錄char字段的空間占用情況，就很特殊？

開(kāi)始計(jì)算

好了，我們已經(jīng)知道每一頁(yè)當(dāng)中具體存儲(chǔ)的東西了，現(xiàn)在我們已經(jīng)具備計(jì)算能力了。

由于頁(yè)的剩余空間我已經(jīng)在上面頁(yè)格式的地方計(jì)算過(guò)了，每頁(yè)會(huì)剩余 15232 字節(jié)可用，下面我們直接計(jì)算行。

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

單個(gè)節(jié)點(diǎn)計(jì)算

索引頁(yè)就是存索引的節(jié)點(diǎn)，也就是非葉子節(jié)點(diǎn)。

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

索引記錄當(dāng)中的指針占用空間我沒(méi)在官方文檔里找到?，這個(gè) 6 字節(jié)是我參考其他博文的，他們說(shuō)源碼里寫(xiě)的是6字節(jié)，但具體在哪一段源碼我也不知道?。

希望知道的同學(xué)可以在評(píng)論區(qū)解惑。

假設(shè)我們的主鍵id為 bigint 型，也就是8個(gè)字節(jié)，那索引頁(yè)中每行數(shù)據(jù)占用的空間就等于

8+6+5=19 字節(jié)。每頁(yè)可以存

15232÷19≈801 條索引數(shù)據(jù)。

那算上頁(yè)目錄的話(huà)，按每個(gè)槽平均6條數(shù)據(jù)計(jì)算的話(huà)，至少有

801÷6≈134 個(gè)槽，需要占用 268 字節(jié)的空間。

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

如果是主鍵是 int 型的話(huà)，那可以存