MySQL單表存多大的數(shù)據(jù)量比較合適
前言
經(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+樹(shù),只有葉子節(jié)點(diǎn)會(huì)存儲(chǔ)記錄的數(shù)據(jù),非葉子節(jié)點(diǎn)只存索引。
數(shù)據(jù)量計(jì)算
通常來(lái)說(shuō),三層B+樹(shù)的索引結(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)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySql中sql語(yǔ)句執(zhí)行過(guò)程詳細(xì)講解
天天和數(shù)據(jù)庫(kù)打交道,一天能寫上幾十條 SQL 語(yǔ)句,但你知道我們的系統(tǒng)是如何和數(shù)據(jù)庫(kù)交互的嗎?下面這篇文章主要給大家介紹了關(guān)于MySql中sql語(yǔ)句執(zhí)行過(guò)程詳細(xì)講解的相關(guān)資料,需要的朋友可以參考下2023-02-02mysql 內(nèi)存緩沖池innodb_buffer_pool_sizes大小調(diào)整實(shí)現(xiàn)
innodb_buffer_pool_size是MySQL中InnoDB存儲(chǔ)引擎的一個(gè)重要參數(shù),本文主要介紹了mysql 內(nèi)存緩沖池innodb_buffer_pool_sizes大小調(diào)整實(shí)現(xiàn),具有一定的參考價(jià)值,感興趣的可以了解一下2024-05-05MySQL中Stmt 預(yù)處理提高效率問(wèn)題的小研究
在oracle數(shù)據(jù)庫(kù)中,有一個(gè)變量綁定的用法,很多人都比較熟悉,可以調(diào)高數(shù)據(jù)庫(kù)效率,應(yīng)對(duì)高并發(fā)等,好吧,這其中并不包括我,當(dāng)同事問(wèn)我MySQL中有沒(méi)有類似的寫法時(shí),我是很茫然的,于是就上網(wǎng)查,找到了如下一種寫法2011-08-08mysql 某字段插入隨機(jī)數(shù)(插入隨機(jī)數(shù)到MySQL數(shù)據(jù)庫(kù))
這篇文章主要介紹了mysql 某字段插入隨機(jī)數(shù)(插入隨機(jī)數(shù)到MySQL數(shù)據(jù)庫(kù)),需要的朋友可以參考下2016-09-09MySQL下200GB大表備份的操作(利用傳輸表空間解決停服發(fā)版表備份問(wèn)題)
這篇文章主要介紹了MySQL下200GB大表備份的操作(利用傳輸表空間解決停服發(fā)版表備份問(wèn)題),本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2025-04-04mysql中數(shù)據(jù)庫(kù)覆蓋導(dǎo)入的幾種方式總結(jié)
這篇文章主要介紹了mysql中數(shù)據(jù)庫(kù)覆蓋導(dǎo)入的幾種方式總結(jié),具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2022-03-03