1、背景

2、實(shí)驗(yàn)

3、單表數(shù)量限制

4、表空間

5、頁(yè)的數(shù)據(jù)結(jié)構(gòu)

6、索引的數(shù)據(jù)結(jié)構(gòu)

7、單表建議值

8、總結(jié)

9、參考

1、背景

作為在后端圈開(kāi)車(chē)的多年老司機(jī)，是不是經(jīng)常聽(tīng)到過(guò)，“mysql 單表最好不要超過(guò) 2000w”,“單表超過(guò) 2000w 就要考慮數(shù)據(jù)遷移了”，“你這個(gè)表數(shù)據(jù)都馬上要到 2000w 了，難怪查詢(xún)速度慢”

這些名言民語(yǔ)就和 “群里只討論技術(shù)，不開(kāi)車(chē)，開(kāi)車(chē)速度不要超過(guò) 120 碼，否則自動(dòng)踢群”，只聽(tīng)過(guò)，沒(méi)試過(guò)，哈哈。

下面我們就把車(chē)速踩到底，干到 180 碼試試…….

基于 Spring Boot + MyBatis Plus + Vue & Element 實(shí)現(xiàn)的后臺(tái)管理系統(tǒng) + 用戶(hù)小程序，支持 RBAC 動(dòng)態(tài)權(quán)限、多租戶(hù)、數(shù)據(jù)權(quán)限、工作流、三方登錄、支付、短信、商城等功能

項(xiàng)目地址：https://github.com/YunaiV/ruoyi-vue-pro

視頻教程：https://doc.iocoder.cn/video/

2、實(shí)驗(yàn)

實(shí)驗(yàn)一把看看…建一張表

CREATETABLEperson(
idintNOTNULLAUTO_INCREMENTPRIMARYKEYcomment'主鍵',
person_idtinyintnotnullcomment'用戶(hù)id',
person_nameVARCHAR(200)comment'用戶(hù)名稱(chēng)',
gmt_createdatetimecomment'創(chuàng)建時(shí)間',
gmt_modifieddatetimecomment'修改時(shí)間'
)comment'人員信息表';

插入一條數(shù)據(jù)

insertintopersonvalues(1,1,'user_1',NOW(),now());

利用 mysql 偽列 rownum 設(shè)置偽列起始點(diǎn)為 1

select(@i:=@i+1)asrownum,person_namefromperson,(select@i:=100)asinit;
set@i=1;

運(yùn)行下面的 sql，連續(xù)執(zhí)行 20 次，就是 2 的 20 次方約等于 100w 的數(shù)據(jù)；執(zhí)行 23 次就是 2 的 23 次方約等于 800w , 如此下去即可實(shí)現(xiàn)千萬(wàn)測(cè)試數(shù)據(jù)的插入，如果不想翻倍翻倍的增加數(shù)據(jù)，而是想少量，少量的增加，有個(gè)技巧，就是在 SQL 的后面增加 where 條件，如 id > 某一個(gè)值去控制增加的數(shù)據(jù)量即可。

insertintoperson(id,person_id,person_name,gmt_create,gmt_modified)
select@i:=@i+1,
left(rand()*10,10)asperson_id,
concat('user_',@i%2048),
date_add(gmt_create,interval+@i*cast(rand()*100assigned)SECOND),
date_add(date_add(gmt_modified,interval+@i*cast(rand()*100assigned)SECOND),interval+cast(rand()*1000000assigned)SECOND)
fromperson;

此處需要注意的是，也許你在執(zhí)行到近 800w 或者 1000w 數(shù)據(jù)的時(shí)候，會(huì)報(bào)錯(cuò)：The total number of locks exceeds the lock table size，這是由于你的臨時(shí)表內(nèi)存設(shè)置的不夠大，只需要擴(kuò)大一下設(shè)置參數(shù)即可。

SETGLOBALtmp_table_size=512*1024*1024;（512M）
SETglobalinnodb_buffer_pool_size=1*1024*1024*1024(1G);

先來(lái)看一組測(cè)試數(shù)據(jù)，這組數(shù)據(jù)是在 mysql8.0 的版本，并且是在我本機(jī)上，由于本機(jī)還跑著 idea , 瀏覽器等各種工具，所以并不是機(jī)器配置就是用于數(shù)據(jù)庫(kù)配置，所以測(cè)試數(shù)據(jù)只限于參考。

看到這組數(shù)據(jù)似乎好像真的和標(biāo)題對(duì)應(yīng)，當(dāng)數(shù)據(jù)達(dá)到 2000w 以后，查詢(xún)時(shí)長(zhǎng)急劇上升；難道這就是鐵律嗎？

那下面我們就來(lái)看看這個(gè)建議值 2kw 是怎么來(lái)的？

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 實(shí)現(xiàn)的后臺(tái)管理系統(tǒng) + 用戶(hù)小程序，支持 RBAC 動(dòng)態(tài)權(quán)限、多租戶(hù)、數(shù)據(jù)權(quán)限、工作流、三方登錄、支付、短信、商城等功能

項(xiàng)目地址：https://github.com/YunaiV/yudao-cloud

視頻教程：https://doc.iocoder.cn/video/

3、單表數(shù)量限制

首先我們先想想數(shù)據(jù)庫(kù)單表行數(shù)最大多大？

CREATETABLEperson(
idint(10)NOTNULLAUTO_INCREMENTPRIMARYKEYcomment'主鍵',
person_idtinyintnotnullcomment'用戶(hù)id',
person_nameVARCHAR(200)comment'用戶(hù)名稱(chēng)',
gmt_createdatetimecomment'創(chuàng)建時(shí)間',
gmt_modifieddatetimecomment'修改時(shí)間'
)comment'人員信息表';

看看上面的建表 sql，id 是主鍵，本身就是唯一的，也就是說(shuō)主鍵的大小可以限制表的上限，如果主鍵聲明 int 大小，也就是 32 位，那么支持 2^32-1 ~~21 億；如果是 bigint，那就是 2^62-1 ？（36893488147419103232），難以想象這個(gè)的多大了，一般還沒(méi)有到這個(gè)限制之前，可能數(shù)據(jù)庫(kù)已經(jīng)爆滿(mǎn)了！！有人統(tǒng)計(jì)過(guò)，如果建表的時(shí)候，自增字段選擇無(wú)符號(hào)的 bigint , 那么自增長(zhǎng)最大值是 18446744073709551615，按照一秒新增一條記錄的速度，大約什么時(shí)候能用完？

4、表空間

下面我們?cè)賮?lái)看看索引的結(jié)構(gòu)，對(duì)了，我們下面講內(nèi)容都是基于 Innodb 引擎的，大家都知道 Innodb 的索引內(nèi)部用的是 B+ 樹(shù)

這張表數(shù)據(jù)，在硬盤(pán)上存儲(chǔ)也是類(lèi)似如此的，它實(shí)際是放在一個(gè)叫 person.ibd （innodb data）的文件中，也叫做表空間；雖然數(shù)據(jù)表中，他們看起來(lái)是一條連著一條，但是實(shí)際上在文件中它被分成很多小份的數(shù)據(jù)頁(yè)，而且每一份都是 16K。大概就像下面這樣，當(dāng)然這只是我們抽象出來(lái)的，在表空間中還有段、區(qū)、組等很多概念，但是我們需要跳出來(lái)看。

5、頁(yè)的數(shù)據(jù)結(jié)構(gòu)

因?yàn)槊總€(gè)頁(yè)只有 16K 的大小，但是如果數(shù)據(jù)很多，那一頁(yè)肯定就放不下這些數(shù)據(jù)，那數(shù)據(jù)肯定就會(huì)被分到其他的頁(yè)中，所以為了把這些頁(yè)關(guān)聯(lián)起來(lái)，肯定就會(huì)有記錄前后頁(yè)地址，方便找到對(duì)應(yīng)頁(yè)；同時(shí)每頁(yè)都是唯一的，那就會(huì)需要有一個(gè)唯一標(biāo)志來(lái)標(biāo)記頁(yè)，就是頁(yè)號(hào)；頁(yè)中會(huì)記錄數(shù)據(jù)所以會(huì)存在讀寫(xiě)操作，讀寫(xiě)操作會(huì)存在中斷或者其他異常導(dǎo)致數(shù)據(jù)不全等，那就會(huì)需要有校驗(yàn)機(jī)制，所以里面還有會(huì)校驗(yàn)碼，而讀操作最重要的就是效率問(wèn)題，如果按照記錄一個(gè)個(gè)進(jìn)行遍歷，那肯定是很費(fèi)勁的，所以這里面還會(huì)為數(shù)據(jù)生成對(duì)應(yīng)的頁(yè)目錄（Page Directory）; 所以實(shí)際頁(yè)的內(nèi)部結(jié)構(gòu)像是下面這樣的。

從圖中可以看出，一個(gè) InnoDB 數(shù)據(jù)頁(yè)的存儲(chǔ)空間大致被劃分成了 7 個(gè)部分，有的部分占用的字節(jié)數(shù)是確定的，有的部分占用的字節(jié)數(shù)是不確定的。

在頁(yè)的 7 個(gè)組成部分中，我們自己存儲(chǔ)的記錄會(huì)按照我們指定的行格式存儲(chǔ)到 User Records 部分。

但是在一開(kāi)始生成頁(yè)的時(shí)候，其實(shí)并沒(méi)有 User Records 這個(gè)部分，每當(dāng)我們插入一條記錄，都會(huì)從 Free Space 部分，也就是尚未使用的存儲(chǔ)空間中申請(qǐng)一個(gè)記錄大小的空間劃分到 User Records 部分，當(dāng) Free Space 部分的空間全部被 User Records 部分替代掉之后，也就意味著這個(gè)頁(yè)使用完了，如果還有新的記錄插入的話(huà)，就需要去申請(qǐng)新的頁(yè)了。這個(gè)過(guò)程的圖示如下。

剛剛上面說(shuō)到了數(shù)據(jù)的新增的過(guò)程。

那下面就來(lái)說(shuō)說(shuō)，數(shù)據(jù)的查找過(guò)程，假如我們需要查找一條記錄，我們可以把表空間中的每一頁(yè)都加載到內(nèi)存中，然后對(duì)記錄挨個(gè)判斷是不是我們想要的，在數(shù)據(jù)量小的時(shí)候，沒(méi)啥問(wèn)題，內(nèi)存也可以撐；但是現(xiàn)實(shí)就是這么殘酷，不會(huì)給你這個(gè)局面；為了解決這問(wèn)題，mysql 中就有了索引的概念；大家都知道索引能夠加快數(shù)據(jù)的查詢(xún)，那到底是怎么個(gè)回事呢？下面我就來(lái)看看。

6、索引的數(shù)據(jù)結(jié)構(gòu)

在 mysql 中索引的數(shù)據(jù)結(jié)構(gòu)和剛剛描述的頁(yè)幾乎是一模一樣的，而且大小也是 16K, 但是在索引頁(yè)中記錄的是頁(yè) (數(shù)據(jù)頁(yè)，索引頁(yè)) 的最小主鍵 id 和頁(yè)號(hào)，以及在索引頁(yè)中增加了層級(jí)的信息，從 0 開(kāi)始往上算，所以頁(yè)與頁(yè)之間就有了上下層級(jí)的概念。

看到這個(gè)圖之后，是不是有點(diǎn)似曾相似的感覺(jué)，是不是像一棵二叉樹(shù)啊，對(duì)，沒(méi)錯(cuò)！它就是一棵樹(shù)，只不過(guò)我們?cè)谶@里只是簡(jiǎn)單畫(huà)了三個(gè)節(jié)點(diǎn)，2 層結(jié)構(gòu)的而已，如果數(shù)據(jù)多了，可能就會(huì)擴(kuò)展到 3 層的樹(shù)，這個(gè)就是我們常說(shuō)的 B+ 樹(shù)，最下面那一層的 page level =0, 也就是葉子節(jié)點(diǎn)，其余都是非葉子節(jié)點(diǎn)。

看上圖中，我們是單拿一個(gè)節(jié)點(diǎn)來(lái)看，首先它是一個(gè)非葉子節(jié)點(diǎn)（索引頁(yè)），在它的內(nèi)容區(qū)中有 id 和 頁(yè)號(hào)地址兩部分，這個(gè) id 是對(duì)應(yīng)頁(yè)中記錄的最小記錄 id 值，頁(yè)號(hào)地址是指向?qū)?yīng)頁(yè)的指針；而數(shù)據(jù)頁(yè)與此幾乎大同小異，區(qū)別在于數(shù)據(jù)頁(yè)記錄的是真實(shí)的行數(shù)據(jù)而不是頁(yè)地址，而且 id 的也是順序的。

7、單表建議值

下面我們就以 3 層，2 分叉（實(shí)際中是 M 分叉）的圖例來(lái)說(shuō)明一下查找一個(gè)行數(shù)據(jù)的過(guò)程。

比如說(shuō)我們需要查找一個(gè) id=6 的行數(shù)據(jù)，因?yàn)樵诜侨~子節(jié)點(diǎn)中存放的是頁(yè)號(hào)和該頁(yè)最小的 id，所以我們從頂層開(kāi)始對(duì)比，首先看頁(yè)號(hào) 10 中的目錄，有 [id=1, 頁(yè)號(hào) = 20],[id=5, 頁(yè)號(hào) = 30], 說(shuō)明左側(cè)節(jié)點(diǎn)最小 id 為 1，右側(cè)節(jié)點(diǎn)最小 id 是 5；6>5, 那按照二分法查找的規(guī)則，肯定就往右側(cè)節(jié)點(diǎn)繼續(xù)查找，找到頁(yè)號(hào) 30 的節(jié)點(diǎn)后，發(fā)現(xiàn)這個(gè)節(jié)點(diǎn)還有子節(jié)點(diǎn)（非葉子節(jié)點(diǎn)），那就繼續(xù)比對(duì)，同理，6>5&&6<7, 所以找到了頁(yè)號(hào) 60，找到頁(yè)號(hào) 60 之后，發(fā)現(xiàn)此節(jié)點(diǎn)為葉子節(jié)點(diǎn)（數(shù)據(jù)節(jié)點(diǎn)），于是將此頁(yè)數(shù)據(jù)加載至內(nèi)存進(jìn)行一一對(duì)比，結(jié)果找到了 id=6 的數(shù)據(jù)行。

從上述的過(guò)程中發(fā)現(xiàn)，我們?yōu)榱瞬檎?id=6 的數(shù)據(jù)，總共查詢(xún)了三個(gè)頁(yè)，如果三個(gè)頁(yè)都在磁盤(pán)中（未提前加載至內(nèi)存），那么最多需要經(jīng)歷三次的磁盤(pán) IO。需要注意的是，圖中的頁(yè)號(hào)只是個(gè)示例，實(shí)際情況下并不是連續(xù)的，在磁盤(pán)中存儲(chǔ)也不一定是順序的。

至此，我們大概已經(jīng)了解了表的數(shù)據(jù)是怎么個(gè)結(jié)構(gòu)了，也大概知道查詢(xún)數(shù)據(jù)是個(gè)怎么的過(guò)程了，這樣我們也就能大概估算這樣的結(jié)構(gòu)能存放多少數(shù)據(jù)了。

從上面的圖解我們知道 B+ 數(shù)的葉子節(jié)點(diǎn)才是存在數(shù)據(jù)的，而非葉子節(jié)點(diǎn)是用來(lái)存放索引數(shù)據(jù)的。

所以，同樣一個(gè) 16K 的頁(yè)，非葉子節(jié)點(diǎn)里的每條數(shù)據(jù)都指向新的頁(yè)，而新的頁(yè)有兩種可能

如果是葉子節(jié)點(diǎn)，那么里面就是一行行的數(shù)據(jù)

如果是非葉子節(jié)點(diǎn)的話(huà)，那么就會(huì)繼續(xù)指向新的頁(yè)

假設(shè)

非葉子節(jié)點(diǎn)內(nèi)指向其他頁(yè)的數(shù)量為 x

葉子節(jié)點(diǎn)內(nèi)能容納的數(shù)據(jù)行數(shù)為 y

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

如下圖中所示Total =x^(z-1) *y 也就是說(shuō)總數(shù)會(huì)等于 x 的 z-1 次方 與 Y 的乘積。

X =？

在文章的開(kāi)頭已經(jīng)介紹了頁(yè)的結(jié)構(gòu)，索引也也不例外，都會(huì)有 File Header (38 byte)、Page Header (56 Byte)、Infimum + Supermum（26 byte）、File Trailer（8byte）, 再加上頁(yè)目錄，大概 1k 左右，我們就當(dāng)做它就是 1K, 那整個(gè)頁(yè)的大小是 16K, 剩下 15k 用于存數(shù)據(jù)，在索引頁(yè)中主要記錄的是主鍵與頁(yè)號(hào)，主鍵我們假設(shè)是 Bigint (8 byte), 而頁(yè)號(hào)也是固定的（4Byte）, 那么索引頁(yè)中的一條數(shù)據(jù)也就是 12byte; 所以 x=15*1024/12≈1280 行。

Y=？

葉子節(jié)點(diǎn)和非葉子節(jié)點(diǎn)的結(jié)構(gòu)是一樣的，同理，能放數(shù)據(jù)的空間也是 15k；但是葉子節(jié)點(diǎn)中存放的是真正的行數(shù)據(jù)，這個(gè)影響的因素就會(huì)多很多，比如，字段的類(lèi)型，字段的數(shù)量；每行數(shù)據(jù)占用空間越大，頁(yè)中所放的行數(shù)量就會(huì)越少；這邊我們暫時(shí)按一條行數(shù)據(jù) 1k 來(lái)算，那一頁(yè)就能存下 15 條，Y≈15。

算到這邊了，是不是心里已經(jīng)有譜了啊根據(jù)上述的公式，Total =x^(z-1) y，已知 x=1280,y=15假設(shè) B+ 樹(shù)是兩層，那就是 Z =2， Total = （1280 ^1 ）15 = 19200假設(shè) B+ 樹(shù)是三層，那就是 Z =3， Total = （1280 ^2） *15 = 24576000 （約 2.45kw）

哎呀，媽呀！這不是正好就是文章開(kāi)頭說(shuō)的最大行數(shù)建議值 2000w 嘛！對(duì)的，一般 B+ 數(shù)的層級(jí)最多也就是 3 層，你試想一下，如果是 4 層，除了查詢(xún)的時(shí)候磁盤(pán) IO 次數(shù)會(huì)增加，而且這個(gè) Total 值會(huì)是多少，大概應(yīng)該是 3 百多億吧，也不太合理，所以，3 層應(yīng)該是比較合理的一個(gè)值。

到這里難道就完了？

不我們剛剛在說(shuō) Y 的值時(shí)候假設(shè)的是 1K ，那比如我實(shí)際當(dāng)行的數(shù)據(jù)占用空間不是 1K , 而是 5K, 那么單個(gè)數(shù)據(jù)頁(yè)最多只能放下 3 條數(shù)據(jù)同樣，還是按照 Z=3 的值來(lái)計(jì)算，那 Total = （1280 ^2） *3 = 4915200 （近 500w）

所以，在保持相同的層級(jí)（相似查詢(xún)性能）的情況下，在行數(shù)據(jù)大小不同的情況下，其實(shí)這個(gè)最大建議值也是不同的，而且影響查詢(xún)性能的還有很多其他因素，比如，數(shù)據(jù)庫(kù)版本，服務(wù)器配置，sql 的編寫(xiě)等等，MySQL 為了提高性能，會(huì)將表的索引裝載到內(nèi)存中。在 InnoDB buffer size 足夠的情況下，其能完成全加載進(jìn)內(nèi)存，查詢(xún)不會(huì)有問(wèn)題。但是，當(dāng)單表數(shù)據(jù)庫(kù)到達(dá)某個(gè)量級(jí)的上限時(shí)，導(dǎo)致內(nèi)存無(wú)法存儲(chǔ)其索引，使得之后的 SQL 查詢(xún)會(huì)產(chǎn)生磁盤(pán) IO，從而導(dǎo)致性能下降，所以增加硬件配置（比如把內(nèi)存當(dāng)磁盤(pán)使），可能會(huì)帶來(lái)立竿見(jiàn)影的性能提升哈。

8、總結(jié)

Mysql 的表數(shù)據(jù)是以頁(yè)的形式存放的，頁(yè)在磁盤(pán)中不一定是連續(xù)的。

頁(yè)的空間是 16K, 并不是所有的空間都是用來(lái)存放數(shù)據(jù)的，會(huì)有一些固定的信息，如，頁(yè)頭，頁(yè)尾，頁(yè)碼，校驗(yàn)碼等等。

在 B+ 樹(shù)中，葉子節(jié)點(diǎn)和非葉子節(jié)點(diǎn)的數(shù)據(jù)結(jié)構(gòu)是一樣的，區(qū)別在于，葉子節(jié)點(diǎn)存放的是實(shí)際的行數(shù)據(jù)，而非葉子節(jié)點(diǎn)存放的是主鍵和頁(yè)號(hào)。

索引結(jié)構(gòu)不會(huì)影響單表最大行數(shù)，2kw 也只是推薦值，超過(guò)了這個(gè)值可能會(huì)導(dǎo)致 B + 樹(shù)層級(jí)更高，影響查詢(xún)性能。
責(zé)任編輯：彭菁