前言

經(jīng)常使用MySQL數(shù)據(jù)庫的小伙伴都知道，當單表數(shù)據(jù)量達到一定的規(guī)模以后，查詢性能就會顯著降低。因此，當單表數(shù)據(jù)量過大時，我們往往要考慮進行分庫分表。那么如何計算單表存儲多大的數(shù)據(jù)量合適？當單表數(shù)據(jù)達到多大的規(guī)模時，我們才要進行分庫分表呢？

MySQL存儲方式

首先我們要先了解一下MySQL存儲數(shù)據(jù)的方式，以下都是針對InnoDB引擎來講解的。

數(shù)據(jù)頁

為了提高數(shù)據(jù)查詢效率，MySQL采用了數(shù)據(jù)頁的方式進行數(shù)據(jù)存儲，一個數(shù)據(jù)頁的大小是16KB，可以通過以下語句查詢。

mysql> SHOW GLOBAL STATUS LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

根據(jù)結(jié)構(gòu)示意圖，我們可以看到，在這16KB的數(shù)據(jù)里，除了包括我們要記錄的數(shù)據(jù)，還包含頁頭和頁尾的開銷（大約200字節(jié)）。因此，一個數(shù)據(jù)頁中的有效數(shù)據(jù)空間大概為16184字節(jié)。

索引結(jié)構(gòu)

InnoDB引擎的索引結(jié)構(gòu)是B+樹，只有葉子節(jié)點會存儲記錄的數(shù)據(jù)，非葉子節(jié)點只存索引。

數(shù)據(jù)量計算

通常來說，三層B+樹的索引結(jié)構(gòu)可以達到一個較好的檢索性能，只需三次磁盤IO即可完成數(shù)據(jù)查詢。因此，我們以此為例進行計算。

根節(jié)點計算

我們假設(shè)數(shù)據(jù)表的主鍵是一個bigint類型的字段，bigint類型的長度是8Byte。而根節(jié)點除了要儲存主鍵字段數(shù)據(jù)，還有存儲下一層索引數(shù)據(jù)頁的地址，大小為6Byte。
可以算出一條數(shù)據(jù)的索引所占空間為8+6=14Byte，進而可以算出根節(jié)點可以存儲16184/14=1156個指針。

第二層節(jié)點計算

第二層的每個節(jié)點的指針數(shù)量和根節(jié)點一樣，都是1156個指針，節(jié)點數(shù)量和根節(jié)點的指針數(shù)量一致。因此可以得出，第二層的指針數(shù)量為1156*1156=1336336。

葉子節(jié)點計算

我們假設(shè)一行數(shù)據(jù)有100個字節(jié)，那么一個葉子節(jié)點可以存儲16184/100≈161條數(shù)據(jù)。與第二層的指針數(shù)量相乘以后，可以得出總數(shù)據(jù)量為1336336*161=215150096條數(shù)據(jù)，大約2億多條。

總結(jié)

通過以上的分析，我們可以發(fā)現(xiàn)，關(guān)于單表的數(shù)據(jù)量條數(shù)限制并沒有一個統(tǒng)一的答案。單表可容納多少數(shù)據(jù)量，這與表的主鍵以及數(shù)據(jù)行長度息息相關(guān)，需要具體情況具體分析。
另外，在阿里的開發(fā)規(guī)范中，關(guān)于數(shù)據(jù)庫的建表規(guī)約，有一條這樣的建議：

【推薦】單表行數(shù)超過 500 萬行或者單表容量超過 2GB，才推薦進行分庫分表。
說明：如果預(yù)計三年后的數(shù)據(jù)量根本達不到這個級別，請不要在創(chuàng)建表時就分庫分表。

這個數(shù)據(jù)規(guī)模要比我們計算出來的小很多，可能由以下幾個方面的原因?qū)е拢?/p>

• 實際業(yè)務(wù)中的表字段長度一般不止100個字節(jié)，主鍵索引結(jié)構(gòu)也可能更加復(fù)雜，導致單個數(shù)據(jù)頁可以存儲的數(shù)據(jù)量大大降低；
• 磁盤IO性能的限制，當時機械硬盤還是主流，對數(shù)據(jù)量限制較為嚴格；
• 數(shù)據(jù)備份和恢復(fù)的難度，數(shù)據(jù)量過大會導致數(shù)據(jù)備份和恢復(fù)的難度大大提高。

到此這篇關(guān)于MySQL單表存多大的數(shù)據(jù)量比較合適的文章就介紹到這了,更多相關(guān)MySQL單表存儲內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！ 

最新評論