MySQL最大建議行數2000w, 靠譜嗎？

2024-05-08碼農

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

這些名言民語就和「群裏只討論技術，不開車，開車速度不要超過 120 碼，否則自動踢群」，只聽過，沒試過，哈哈。

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

實驗

實驗一把看看… 建一張表

CREATETABLE person( idintNOTNULL AUTO_INCREMENT PRIMARY KEYcomment'主鍵', person_id tinyintnotnullcomment'使用者id', person_name VARCHAR(200) comment'使用者名稱稱', gmt_create datetime comment'建立時間', gmt_modified datetime comment'修改時間' ) comment'人員資訊表';

插入一條數據

insert into person values(1,1,'user_1', NOW(), now());

利用 mysql 偽列 rownum 設定偽列起始點為 1

select (@i:=@i+1) as rownum, person_name from person, (select @i:=100) as init; set @i=1;

執行下面的 sql，連續執行 20 次，就是 2 的 20 次方約等於 100w 的數據；執行 23 次就是 2 的 23 次方約等於 800w , 如此下去即可實作千萬測試數據的插入，如果不想翻倍翻倍的增加數據，而是想少量，少量的增加，有個技巧，就是在 SQL 的後面增加 where 條件，如 id > 某一個值去控制增加的數據量即可。

insertinto person(id, person_id, person_name, gmt_create, gmt_modified) select @i:=@i+1, left(rand()*10,10) as person_id, concat('user_',@i%2048), date_add(gmt_create,interval + @i*cast(rand()*100as signed) SECOND), date_add(date_add(gmt_modified,interval +@i*cast(rand()*100as signed) SECOND), interval +cast(rand()*1000000as signed) SECOND) from person;

此處需要註意的是，也許你在執行到近 800w 或者 1000w 數據的時候，會報錯：The total number of locks exceeds the lock table size，這是由於你的臨時表記憶體設定的不夠大，只需要擴大一下設定參數即可。

SETGLOBAL tmp_table_size =512*1024*1024; （512M） SETglobal innodb_buffer_pool_size= 1*1024*1024*1024 (1G);

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

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

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

單表數量限制

首先我們先想想資料庫單表行數最大多大？

CREATETABLE person( idint(10) NOTNULL AUTO_INCREMENT PRIMARY KEYcomment'主鍵', person_id tinyintnotnullcomment'使用者id', person_name VARCHAR(200) comment'使用者名稱稱', gmt_create datetime comment'建立時間', gmt_modified datetime comment'修改時間' ) comment'人員資訊表';

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

表空間

下面我們再來看看索引的結構，對了，我們下面講內容都是基於 Innodb 引擎的，大家都知道 Innodb 的索引內部用的是 B+ 樹

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

頁的數據結構

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

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

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

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

剛剛上面說到了數據的新增的過程。

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

索引的數據結構

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

看到這個圖之後，是不是有點似曾相似的感覺，是不是像一棵二元樹啊，對，沒錯！它就是一棵樹，只不過我們在這裏只是簡單畫了三個節點，2 層結構的而已，如果數據多了，可能就會擴充套件到 3 層的樹，這個就是我們常說的 B+ 樹，最下面那一層的 page level =0, 也就是葉子節點，其余都是非葉子節點。

看上圖中，我們是單拿一個節點來看，首先它是一個非葉子節點（索引頁），在它的內容區中有 id 和頁號地址兩部份，這個 id 是對應頁中記錄的最小記錄 id 值，頁號地址是指向對應頁的指標；而數據頁與此幾乎大同小異，區別在於數據頁記錄的是真實的行數據而不是頁地址，而且 id 的也是順序的。

單表建議值

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

比如說我們需要尋找一個 id=6 的行數據，因為在非葉子節點中存放的是頁號和該頁最小的 id，所以我們從頂層開始對比，首先看頁號 10 中的目錄，有 [id=1, 頁號 = 20],[id=5, 頁號 = 30], 說明左側節點最小 id 為 1，右側節點最小 id 是 5；6>5, 那按照二分法尋找的規則，肯定就往右側節點繼續尋找，找到頁號 30 的節點後，發現這個節點還有子節點（非葉子節點），那就繼續比對，同理，6>5&&6<7, 所以找到了頁號 60，找到頁號 60 之後，發現此節點為葉子節點（數據節點），於是將此頁數據載入至記憶體進行一一對比，結果找到了 id=6 的數據行。

從上述的過程中發現，我們為了尋找 id=6 的數據，總共查詢了三個頁，如果三個頁都在磁盤中（未提前載入至記憶體），那麽最多需要經歷三次的磁盤 IO。需要註意的是，圖中的頁號只是個範例，實際情況下並不是連續的，在磁盤中儲存也不一定是順序的。

至此，我們大概已經了解了表的數據是怎麽個結構了，也大概知道查詢數據是個怎麽的過程了，這樣我們也就能大概估算這樣的結構能存放多少數據了。

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

所以，同樣一個 16K 的頁，非葉子節點裏的每條數據都指向新的頁，而新的頁有兩種可能

如果是葉子節點，那麽裏面就是一行行的數據

如果是非葉子節點的話，那麽就會繼續指向新的頁

假設

非葉子節點內指向其他頁的數量為 x

葉子節點內能容納的數據行數為 y

B+ 數的層數為 z

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

X =？

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

Y=？

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

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

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

到這裏難道就完了？

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

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