前言

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

MySQL存儲(chǔ)方式

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

數(shù)據(jù)頁(yè)

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

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ù)，還包含頁(yè)頭和頁(yè)尾的開(kāi)銷（大約200字節(jié)）。因此，一個(gè)數(shù)據(jù)頁(yè)中的有效數(shù)據(jù)空間大概為16184字節(jié)。

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

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

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

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

根節(jié)點(diǎn)計(jì)算

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

第二層節(jié)點(diǎn)計(jì)算

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

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

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

總結(jié)

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

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

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

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

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

最新評(píng)論