1、背景

2、實驗

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

4、表空間

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

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

7、單表建議值

8、總結(jié)

9、參考

1、背景

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

這些名言民語就和 “群里只討論技術(shù)，不開車，開車速度不要超過 120 碼，否則自動踢群”，只聽過，沒試過，哈哈。

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

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

項目地址：https://github.com/YunaiV/ruoyi-vue-pro

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

2、實驗

實驗一把看看…建一張表

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

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

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

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

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

運行下面的 sql，連續(xù)執(zhí)行 20 次，就是 2 的 20 次方約等于 100w 的數(shù)據(jù)；執(zhí)行 23 次就是 2 的 23 次方約等于 800w , 如此下去即可實現(xiàn)千萬測試數(shù)據(jù)的插入，如果不想翻倍翻倍的增加數(shù)據(jù)，而是想少量，少量的增加，有個技巧，就是在 SQL 的后面增加 where 條件，如 id > 某一個值去控制增加的數(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ù)的時候，會報錯：The total number of locks exceeds the lock table size，這是由于你的臨時表內(nèi)存設(shè)置的不夠大，只需要擴大一下設(shè)置參數(shù)即可。

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

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

看到這組數(shù)據(jù)似乎好像真的和標題對應，當數(shù)據(jù)達到 2000w 以后，查詢時長急劇上升；難道這就是鐵律嗎？

那下面我們就來看看這個建議值 2kw 是怎么來的？

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

項目地址：https://github.com/YunaiV/yudao-cloud

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

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

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

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

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

4、表空間

下面我們再來看看索引的結(jié)構(gòu)，對了，我們下面講內(nèi)容都是基于 Innodb 引擎的，大家都知道 Innodb 的索引內(nèi)部用的是 B+ 樹

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

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

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

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

在頁的 7 個組成部分中，我們自己存儲的記錄會按照我們指定的行格式存儲到 User Records 部分。

但是在一開始生成頁的時候，其實并沒有 User Records 這個部分，每當我們插入一條記錄，都會從 Free Space 部分，也就是尚未使用的存儲空間中申請一個記錄大小的空間劃分到 User Records 部分，當 Free Space 部分的空間全部被 User Records 部分替代掉之后，也就意味著這個頁使用完了，如果還有新的記錄插入的話，就需要去申請新的頁了。這個過程的圖示如下。

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

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

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

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

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

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

7、單表建議值

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

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

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

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

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

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

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

如果是非葉子節(jié)點的話，那么就會繼續(xù)指向新的頁

假設(shè)

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

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

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

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

X =？

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

Y=？

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

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

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

到這里難道就完了？

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

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

8、總結(jié)

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

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

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

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