色哟哟视频在线观看-色哟哟视频在线-色哟哟欧美15最新在线-色哟哟免费在线观看-国产l精品国产亚洲区在线观看-国产l精品国产亚洲区久久

0
  • 聊天消息
  • 系統(tǒng)消息
  • 評論與回復
登錄后你可以
  • 下載海量資料
  • 學習在線課程
  • 觀看技術視頻
  • 寫文章/發(fā)帖/加入社區(qū)
會員中心
創(chuàng)作中心

完善資料讓更多小伙伴認識你,還能領取20積分哦,立即完善>

3天內不再提示

MySQL單表數(shù)據(jù)最大不要超過多少行?為什么?

jf_ro2CN3Fa ? 來源:小白debug ? 2023-07-06 09:46 ? 次閱讀

故事從好多年前說起。

想必大家也聽說過數(shù)據(jù)庫單表建議最大2kw 條數(shù)據(jù)這個說法。如果超過了,性能就會下降得比較厲害。

巧了。

我也聽說過。

但我不接受它的建議,硬是單表裝了1億條數(shù)據(jù)。

這時候,我們組里新來的實習生看到了之后,天真無邪的問我:"單表不是建議最大兩千萬嗎?為什么這個表都放了1個億還不分庫分表 "?

我能說我是因為懶 嗎?我當初設計時哪里想到這表竟然能漲這么快。。。

我不能。

說了等于承認自己是開發(fā)組里的毒瘤 ,雖然我確實是,但我不能承認 。

我如坐針氈,如芒刺背,如鯁在喉。

開始了一波騷操作。

"我這么做是有道理的"

"雖然這個表很大,但你有沒有發(fā)現(xiàn)它查詢其實還是很快"

"這個2kw是個建議值,我們要來看下這個2kw是怎么來的"

數(shù)據(jù)庫單表行數(shù)最大多大?

我們先看下單表行數(shù)理論最大值是多少。

建表的SQL是這么寫的,

CREATETABLE`user`(
`id`int(10)unsignedNOTNULLAUTO_INCREMENTCOMMENT'主鍵',
`name`varchar(100)NOTNULLDEFAULT''COMMENT'名字',
`age`int(11)NOTNULLDEFAULT'0'COMMENT'年齡',
PRIMARYKEY(`id`),
KEY`idx_age`(`age`)
)ENGINE=InnoDBAUTO_INCREMENT=100037DEFAULTCHARSET=utf8;

其中id就是主鍵。主鍵本身唯一,也就是說主鍵的大小可以限制表的上限。

如果主鍵聲明為int大小,也就是32位,那么能支持2^32-1,也就是21個億 左右。

如果是bigint,那就是2^64-1,但這個數(shù)字太大 ,一般還沒到這個限制之前,磁盤先受不了 。

搞離譜點。

如果我把主鍵聲明為 tinyint,一個字節(jié),8位,最大2^8-1,也就是255。

CREATETABLE`user`(
`id`tinyint(2)unsignedNOTNULLAUTO_INCREMENTCOMMENT'主鍵',
`name`varchar(100)NOTNULLDEFAULT''COMMENT'名字',
`age`int(11)NOTNULLDEFAULT'0'COMMENT'年齡',
PRIMARYKEY(`id`),
KEY`idx_age`(`age`)
)ENGINE=InnoDBAUTO_INCREMENT=0DEFAULTCHARSET=utf8;

如果我想插入一個id=256的數(shù)據(jù),那就會報錯 。

mysql>INSERTINTO`tmp`(`id`,`name`,`age`)VALUES(256,'',60);
ERROR1264(22003):Outofrangevalueforcolumn'id'atrow1

也就是說,tinyint主鍵限制表內最多255條數(shù)據(jù)。

那除了主鍵,還有哪些因素會影響行數(shù)?

索引的結構

索引內部是用的B+樹,這個也是八股文老股了,大家估計也背得很熟了。

為了不讓大家有過于強烈的審丑疲勞,今天我嘗試從另外一個角度給大家講講這玩意。

頁的結構

假設我們有這么一張user數(shù)據(jù)表。

aa87b092-1b9d-11ee-962d-dac502259ad0.pnguser表

其中id是唯一主鍵 。

這看起來的一行行數(shù)據(jù),為了方便,我們后面就叫它們record 吧。

這張表看起來就跟個excel表格一樣。excel的數(shù)據(jù)在硬盤上是一個xx.excel的文件。

而上面user表數(shù)據(jù),在硬盤上其實也是類似,放在了user.ibd 文件下。含義是user表的innodb data文件,專業(yè)點,又叫表空間

雖然在數(shù)據(jù)表里,它們看起來是挨在一起的。但實際上在user.ibd里他們被分成很多小份的數(shù)據(jù)頁 ,每份大小16k。

類似于下面這樣。

aa9c8166-1b9d-11ee-962d-dac502259ad0.pngibd文件內部有大量的頁

我們把視角聚焦一下,放到頁上面。

整個頁16k,不大,但record這么多,一頁肯定放不下,所以會分開放到很多頁里。并且這16k,也不可能全用來放record對吧。

因為record們被分成好多份,放到好多頁里了,為了唯一標識具體是哪一頁,那就需要引入頁號 (其實是一個表空間的地址偏移量)。同時為了把這些數(shù)據(jù)頁給關聯(lián)起來,于是引入了前后指針 ,用于指向前后的頁。這些都被加到了頁頭 里。

頁是需要讀寫的,16k說小也不小,寫一半電源線被拔了也是有可能發(fā)生的,所以為了保證數(shù)據(jù)頁的正確性,還引入了校驗碼。這個被加到了頁尾

那剩下的空間,才是用來放我們的record的。而record如果行數(shù)特別多的話,進入到頁內時挨個遍歷,效率也不太行,所以為這些數(shù)據(jù)生成了一個頁目錄 ,具體實現(xiàn)細節(jié)不重要。只需要知道,它可以通過二分查找 的方式將查找效率從O(n) 變成O(lgn) 。

aab2a4fa-1b9d-11ee-962d-dac502259ad0.png頁結構

從頁到索引

如果想查一條record,我們可以把表空間里每一頁都撈出來,再把里面的record撈出來挨個判斷是不是我們要找的。

行數(shù)量小的時候,這么操作也沒啥問題。

行數(shù)量大了,性能就慢了 ,于是為了加速搜索,我們可以在每個數(shù)據(jù)頁里選出主鍵id最小 的record,而且只需要它們的主鍵id和所在頁的頁號 。組成新的record ,放入到一個新生成的一個數(shù)據(jù)頁中,這個新數(shù)據(jù)頁跟之前的頁結構沒啥區(qū)別,而且大小還是16k。

但為了跟之前的數(shù)據(jù)頁進行區(qū)分。數(shù)據(jù)頁里加入了*頁層級(page level)** 的信息,從0開始往上算。于是頁與頁之間就有了*上下層級 的概念,就像下面這樣。

aad61a7a-1b9d-11ee-962d-dac502259ad0.png

兩層B+樹結構

突然頁跟頁之間看起來就像是一棵倒過來的樹了。也就是我們常說的B+樹 索引。

最下面那一層,page level 為0 ,也就是所謂的葉子結點 ,其余都叫非葉子結點 。

上面展示的是兩層 的樹,如果數(shù)據(jù)變多了,我們還可以再通過類似的方法,再往上構建一層。就成了三層 的樹。

aaef6bf6-1b9d-11ee-962d-dac502259ad0.png

三層B+樹結構

那現(xiàn)在我們就可以通過這樣一棵B+樹加速查詢。舉個例子。

比方說我們想要查找行數(shù)據(jù)5。會先從頂層頁的record們入手。record里包含了主鍵id和頁號(頁地址) 。看下圖黃色的箭頭,向左最小id是1,向右最小id是7。那id=5的數(shù)據(jù)如果存在,那必定在左邊箭頭。于是順著的record的頁地址就到了6號數(shù)據(jù)頁里,再判斷id=5>4,所以肯定在右邊的數(shù)據(jù)頁里,于是加載105號數(shù)據(jù)頁。在數(shù)據(jù)頁里找到id=5的數(shù)據(jù)行,完成查詢。

ab1d0b92-1b9d-11ee-962d-dac502259ad0.png

B+樹查詢過程

另外需要注意的是,上面的頁的頁號并不是連續(xù)的,它們在磁盤里也不一定是挨在一起的。

這個過程中查詢了三個頁,如果這三個頁都在磁盤中(沒有被提前加載到內存中),那么最多 需要經歷三次磁盤IO查詢 ,它們才能被加載到內存中。

B+樹承載的記錄數(shù)量

從上面的結構里可以看出B+樹的最末級葉子結點 里放了record數(shù)據(jù)。而非葉子結點 里則放了用來加速查詢的索引數(shù)據(jù)。

也就是說

同樣一個16k的頁,非葉子節(jié)點里每一條數(shù)據(jù)都指向一個新的頁,而新的頁有兩種可能。

如果是末級葉子節(jié)點的話,那么里面放的就是一行行record數(shù)據(jù)。

如果是非葉子節(jié)點,那么就會循環(huán)繼續(xù)指向新的數(shù)據(jù)頁。

假設

非葉子結點內指向其他內存頁的指針數(shù)量為x

葉子節(jié)點內能容納的record數(shù)量為y

B+樹的層數(shù)為z

ab4a68e4-1b9d-11ee-962d-dac502259ad0.png

總行數(shù)的計算方法

那這棵B+樹放的行數(shù)據(jù)總量 等于 (x ^ (z-1)) * y。

x怎么算

我們回去看數(shù)據(jù)頁的結構。

aab2a4fa-1b9d-11ee-962d-dac502259ad0.png

頁結構

非葉子節(jié)點里主要放索引查詢相關的數(shù)據(jù),放的是主鍵和指向頁號。

主鍵假設是bigint(8Byte),而頁號在源碼里叫FIL_PAGE_OFFSET(4Byte),那么非葉子節(jié)點里的一條數(shù)據(jù)是12Byte左右。

整個數(shù)據(jù)頁16k, 頁頭頁尾那部分數(shù)據(jù)全加起來大概128Byte,加上頁目錄毛估占1k吧。那剩下的15k 除以12Byte,等于1280,也就是可以指向x=1280頁

我們常說的二叉樹指的是一個結點可以發(fā)散出兩個新的結點。m叉樹一個節(jié)點能指向m個新的結點。這個指向新節(jié)點的操作就叫扇出(fanout) 。

而上面的B+樹,它能指向1280個新的節(jié)點,恐怖如斯,可以說扇出非常高 了。

y的計算

葉子節(jié)點和非葉子節(jié)點的數(shù)據(jù)結構是一樣的,所以也假設剩下15kb可以發(fā)揮。

葉子節(jié)點里放的是真正的行數(shù)據(jù)。假設一條行數(shù)據(jù)1kb,所以一頁里能放y=15行 。

行總數(shù)計算

回到 (x ^ (z-1)) * y 這個公式。

已知x=1280,y=15。

假設B+樹是兩層 ,那z=2。則是(1280 ^ (2-1)) * 15 ≈ 2w

假設B+樹是三層 ,那z=3。則是(1280 ^ (3-1)) * 15 ≈ 2.5kw

這個2.5kw,就是我們常說的單表建議最大行數(shù)2kw的由來。 畢竟再加一層,數(shù)據(jù)就大得有點離譜了。三層數(shù)據(jù)頁對應最多三次磁盤IO,也比較合理。

行數(shù)超一億就慢了嗎?

上面假設單行數(shù)據(jù)用了1kb,所以一個數(shù)據(jù)頁能放個15行數(shù)據(jù)。

如果我單行數(shù)據(jù)用不了這么多,比如只用了250byte。那么單個數(shù)據(jù)頁能放60行數(shù)據(jù)。

那同樣是三層B+樹,單表支持的行數(shù)就是 (1280 ^ (3-1)) * 60 ≈ 1個億。

你看我一個億的數(shù)據(jù),其實也就三層B+樹,在這個B+樹里要查到某行數(shù)據(jù),最多也是三次磁盤IO。所以并不慢。

這就很好的解釋了文章開頭,為什么我單表1個億,但查詢性能沒啥大毛病。

B樹承載的記錄數(shù)量

既然都聊到這里了,我們就順著這個話題多聊一些吧。

我們都知道,現(xiàn)在mysql的索引都是B+樹,而有一種樹,跟B+樹很像,叫B樹,也叫B-樹 。

它跟B+樹最大的區(qū)別在于,B+樹只在末級葉子結點處放數(shù)據(jù)表行數(shù)據(jù),而B樹則會在葉子和非葉子結點上都放。

于是,B樹的結構就類似這樣

ab7ddbc0-1b9d-11ee-962d-dac502259ad0.png

B樹結構

B樹將行數(shù)據(jù)都存在非葉子節(jié)點上,假設每個數(shù)據(jù)頁還是16kb,掐頭去尾每頁剩15kb,并且一條數(shù)據(jù)表行數(shù)據(jù)還是占1kb,就算不考慮各種頁指針的情況下,也只能放個15條數(shù)據(jù)。數(shù)據(jù)頁扇出明顯變少了。

計算可承載的總行數(shù)的公式也變成了一個等比數(shù)列 。

15+15^2+15^3+...+15^z

其中z還是層數(shù) 的意思。

為了能放2kw左右的數(shù)據(jù),需要z>=6。也就是樹需要有6層,查一次要訪問6個頁。假設這6個頁并不連續(xù),為了查詢其中一條數(shù)據(jù),最壞情況需要進行6次磁盤IO

而B+樹同樣情況下放2kw數(shù)據(jù)左右,查一次最多是3次磁盤IO。

磁盤IO越多則越慢,這兩者在性能上差距略大。

為此,B+樹比B樹更適合成為mysql的索引。

總結

B+樹葉子和非葉子結點的數(shù)據(jù)頁都是16k,且數(shù)據(jù)結構一致,區(qū)別在于葉子節(jié)點放的是真實的行數(shù)據(jù),而非葉子結點放的是主鍵和下一個頁的地址。

B+樹一般有兩到三層,由于其高扇出,三層就能支持2kw以上的數(shù)據(jù),且一次查詢最多1~3次磁盤IO,性能也還行。

存儲同樣量級的數(shù)據(jù),B樹比B+樹層級更高,因此磁盤IO也更多,所以B+樹更適合成為mysql索引。

索引結構不會影響單表最大行數(shù),2kw也只是推薦值,超過了這個值可能會導致B+樹層級更高,影響查詢性能。

單表最大值還受主鍵大小和磁盤大小限制。







審核編輯:劉清

聲明:本文內容及配圖由入駐作者撰寫或者入駐合作網站授權轉載。文章觀點僅代表作者本人,不代表電子發(fā)燒友網立場。文章及其配圖僅供工程師學習之用,如有內容侵權或者其他違規(guī)問題,請聯(lián)系本站處理。 舉報投訴
  • SQL
    SQL
    +關注

    關注

    1

    文章

    762

    瀏覽量

    44117
  • MySQL
    +關注

    關注

    1

    文章

    804

    瀏覽量

    26542

原文標題:阿里:MySQL 單表數(shù)據(jù)最大不要超過多少行?為什么?

文章出處:【微信號:芋道源碼,微信公眾號:芋道源碼】歡迎添加關注!文章轉載請注明出處。

收藏 人收藏

    評論

    相關推薦

    誰說MySQL行數(shù)不要超過2000W?

    網上看了一篇文章《為什么說MySQL行數(shù)不要超過2000w》,親自實踐了一下,跟原作者有不同的結論。原文的結論是2000W左右性能會成指
    的頭像 發(fā)表于 12-15 10:02 ?1052次閱讀
    誰說<b class='flag-5'>MySQL</b><b class='flag-5'>單</b><b class='flag-5'>表</b>行數(shù)<b class='flag-5'>不要</b><b class='flag-5'>超過</b>2000W?

    TAS5717的MCLK如果是12.288MHZ,這個頻率的上下誤差最大不能超過多少呢?

    TAS5717的MCLK如果是12.288MHZ,這個頻率的上下誤差最大不能超過多少?
    發(fā)表于 11-05 08:23

    mysql中文參考手冊chm

    數(shù)據(jù)類型 10 從 MySQL 得到最大的性能 10.1 優(yōu)化概述 10.2 系統(tǒng)/編譯時和啟動參數(shù)的調節(jié) 10.2.1 編譯和鏈接如何影響 M
    發(fā)表于 12-26 13:32

    變壓器的大小有效電流最大不超過1A,這樣的話功率不是達不到嗎?

    輸入220v交流經整壓濾波穩(wěn)流后輸出為24v5A,現(xiàn)在比較奇怪的是。在變壓器部分降壓之后的電壓最大不超過40v,看變壓器的大小有效電流最大不超過1A,這樣的話功率不是達不到嗎???
    發(fā)表于 11-10 20:38

    MySQL root密碼忘記怎么辦?

    MySQL實例1. 跳過授權登錄mysqld_safe --skip-grant-table --user=mysql &2. 更改密碼mysq
    發(fā)表于 06-22 17:54

    MySQL分區(qū)類型及介紹

    分區(qū)是將一個數(shù)據(jù)按照一定規(guī)則水平劃分成不同的邏輯塊,并分別進行物理存儲,這個規(guī)則就叫做分區(qū)函數(shù),可以有不同的分區(qū)規(guī)則。通過show plugins語句查看當前MySQL是否支持
    發(fā)表于 06-29 16:31

    請問TAS5717的MCLK是12.288MHZ那頻率的上下誤差最大不能超過多少?

    TAS5717的MCLK如果是12.288MHZ,這個頻率的上下誤差最大不能超過多少?
    發(fā)表于 08-06 10:49

    如何利用labview獲取MySQL數(shù)據(jù)中某一列的最大

    如題,想獲取MySQL數(shù)據(jù)中的data7那一列的最大值,下面是程序框圖一直報語法錯誤,但是該語句在mysql command line
    發(fā)表于 12-06 21:37

    mysql轉列如何操作

    mysql 轉列操作
    發(fā)表于 04-28 11:27

    mysql數(shù)據(jù)導出golang實現(xiàn)

    mysql數(shù)據(jù)導出為excel文件,golang實現(xiàn):首先下載依賴到的三方庫:Simple install the package to your $GOPATH
    發(fā)表于 10-21 15:14

    關于MySQL的基礎知識簡析

    隨機分配一個root密碼,記住密碼,安裝完畢用root登錄,但密碼是過期狀態(tài),mysql默認情況下密碼有效期是360天,需要重新改下,基礎的概念數(shù)據(jù)庫(database): 保存有組織的數(shù)據(jù)庫。
    發(fā)表于 11-03 11:50

    B+樹索引如何對Mysql數(shù)據(jù)量造成影響

    我們說 Mysql 適合存儲的最大數(shù)據(jù)量,自然不是說能夠存儲的最大數(shù)據(jù)量,如果是說能夠存儲的最大
    的頭像 發(fā)表于 04-16 08:08 ?1602次閱讀
    B+樹索引如何對<b class='flag-5'>Mysql</b><b class='flag-5'>單</b><b class='flag-5'>表</b><b class='flag-5'>數(shù)據(jù)</b>量造成影響

    MySQL數(shù)據(jù)最大不要超過多

    最好不要超過 2000w”,“超過 200
    的頭像 發(fā)表于 06-02 15:30 ?616次閱讀
    <b class='flag-5'>MySQL</b><b class='flag-5'>單</b><b class='flag-5'>表</b><b class='flag-5'>數(shù)據(jù)</b><b class='flag-5'>最大不要</b><b class='flag-5'>超過多</b>少<b class='flag-5'>行</b>

    為什么 MySQL 不能超過 2000 萬?

    ,因為數(shù)據(jù)量超大(5000 萬條左右),需要每天定時生成 3 張,然后將數(shù)據(jù)取模分別存到這三張表里。 接下來是兩人的對話: 面試后續(xù)暫且不論,不過,互聯(lián)網江湖上的確流傳著一個說法:
    的頭像 發(fā)表于 06-29 16:48 ?706次閱讀
    為什么 <b class='flag-5'>MySQL</b> <b class='flag-5'>單</b><b class='flag-5'>表</b>不能<b class='flag-5'>超過</b> 2000 萬<b class='flag-5'>行</b>?

    MySQL數(shù)據(jù)量限制:為何2000萬成為瓶頸?

    很多人認為:數(shù)據(jù)超過500萬或2000萬時,引起B(yǎng)+tree的高度增加,延長了索引的搜索路徑,進而導致了性能下降。事實果真如此嗎?
    的頭像 發(fā)表于 02-27 10:38 ?6150次閱讀
    <b class='flag-5'>MySQL</b><b class='flag-5'>單</b><b class='flag-5'>表</b><b class='flag-5'>數(shù)據(jù)</b>量限制:為何2000萬<b class='flag-5'>行</b>成為瓶頸?
    主站蜘蛛池模板: 免费精品国偷自产在线| 60岁老年熟妇在线无码| 国产精品99re6热在线播放| 欧美末成年videos丨| 自拍黄色片| 久久AV国产麻豆HD真实乱| 亚洲国产日韩欧美高清片a| 国产AV高清怡春院| 日韩精品久久日日躁夜夜躁影视| 99精品视频在线观看re| 毛茸茸womansex| 在线视频av大全色久久| 久久久久久天天夜夜天天| 亚洲乱码国产一区三区| 果冻传媒2021一二三区| 亚洲国产精品无码AV久久久| 国产一浮力影院| 亚洲国产在线99视频| 国产主播AV福利精品一区| 亚洲成人在线免费| 国模精品一区二区三区视频| 亚洲VA欧美VA天堂V国产综合| 国产乱国产乱老熟300部视频| 午夜伦理电影在线观免费| 国产精品一区二区激情| 校园女教师之禁区| 国产亚洲免费观看| 亚洲免费在线视频观看| 精品免费久久久久久影院| 亚洲在线国产日韩欧美| 久久看片网| 67194con免费福和视频| 男人国产AV天堂WWW麻豆| a圾片目录大全| 人人干人人看| 二级片免费看| 午夜国产在线观看| 韩国女主播内部vip自带氏巾| 一区二区三区无码高清视频| 久久亚洲一级α片| 99久久全国免费久久爱|