MySQL最大建議行數2000W？老司機做了個實驗……

一、背景作為在後端圈開車的多年老司機，是不是經常聽到過，“mysql 單表最好不要超過 2000w”，“單表超過 2000w 就要考慮數據遷移了”，“你這個表數據都馬上要到 2000w 了，難怪查詢速度慢”。這些名言民語就和“群里只討論技術，不開車，開車速度不要超過 120 碼，否則自動踢群”， ...

一、背景

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

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

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

二、實驗

實驗一把看看……建一張表：

CREATE TABLE person(
id int NOT NULL AUTO_INCREMENT PRIMARY KEY comment '主鍵',
person_id tinyint not null comment '用戶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 > 某一個值去控制增加的數據量即可。

insert into 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()*100 as signed) SECOND),

date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND)

from person;

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

SET GLOBAL tmp_table_size =512*1024*1024; （512M）
SET global innodb_buffer_pool_size= 1*1024*1024*1024 (1G);

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

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

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

三、單表數量限制

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

CREATE TABLE person(
id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY comment '主鍵',
person_id tinyint not null comment '用戶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，從而導致性能下降，所以增加硬體配置（比如把記憶體當磁碟使），可能會帶來立竿見影的性能提升哈。

八、總結

Mysql 的表數據是以頁的形式存放的，頁在磁碟中不一定是連續的。

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

在 B+ 樹中，葉子節點和非葉子節點的數據結構是一樣的，區別在於，葉子節點存放的是實際的行數據，而非葉子節點存放的是主鍵和頁號。

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

>>>>

參考資料

https://www.jianshu.com/p/cf5d381ef637
https://www.modb.pro/db/139052
《MYSQL 內核：INNODB 存儲引擎捲 1》

作者丨京東雲開發者

本文來自博客園，作者：古道輕風，轉載請註明原文鏈接：https://www.cnblogs.com/88223100/p/Is-the-maximum-recommended-number-of-MySQL-rows-is-2000W.html