1背景
作為在後端圈開車的多年老司機，是不是經常聽到過，“mysql 單表最好不要超過 2000w”,“單表超過 2000w 就要考慮資料遷移了”，“你這個表資料都馬上要到 2000w 了，難怪查詢速度慢”。
這些名言民語就和 “群裡隻讨論技術，不開車，開車速度不要超過 120 碼，否則自動踢群”，隻聽過，沒試過，哈哈。
下面我們就把車速踩到底，幹到 180 碼試試…….
2實驗
實驗一把看看…
建一張表
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); 先來看一組測試資料，這組資料是在 mysql8.0 的版本，并且是在我本機上，由于本機還跑着 idea , 浏覽器等各種工具，是以并不是機器配置就是用于資料庫配置，是以測試資料隻限于參考。
看到這組資料似乎好像真的和标題對應，當資料達到 2000w 以後，查詢時長急劇上升；難道這就是鐵律嗎？
那下面我們就來看看這個建議值 2kw 是怎麼來的？
3單表數量限制
首先我們先想想資料庫單表行數最大多大？
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，按照一秒新增一條記錄的速度，大約什麼時候能用完？
4表空間
下面我們再來看看索引的結構，對了，我們下面講内容都是基于 Innodb 引擎的，大家都知道 Innodb 的索引内部用的是 B+ 樹
這張表資料，在硬碟上存儲也是類似如此的，它實際是放在一個叫 person.ibd （innodb data）的檔案中，也叫做表空間；雖然資料表中，他們看起來是一條連着一條，但是實際上在檔案中它被分成很多小份的資料頁，而且每一份都是 16K。
大概就像下面這樣，當然這隻是我們抽象出來的，在表空間中還有段、區、組等很多概念，但是我們需要跳出來看。
5頁的資料結構
因為每個頁隻有 16K 的大小，但是如果資料很多，那一頁肯定就放不下這些資料，那資料肯定就會被分到其他的頁中，是以為了把這些頁關聯起來，肯定就會有記錄前後頁位址，友善找到對應頁；同時每頁都是唯一的，那就會需要有一個唯一标志來标記頁，就是頁号；
頁中會記錄資料是以會存在讀寫操作，讀寫操作會存在中斷或者其他異常導緻資料不全等，那就會需要有校驗機制，是以裡面還有會校驗碼，而讀操作最重要的就是效率問題，如果按照記錄一個個進行周遊，那肯定是很費勁的，是以這裡面還會為資料生成對應的頁目錄（Page Directory）; 是以實際頁的内部結構像是下面這樣的。
從圖中可以看出，一個 InnoDB 資料頁的存儲空間大緻被劃分成了 7 個部分，有的部分占用的位元組數是确定的，有的部分占用的位元組數是不确定的。
在頁的 7 個組成部分中，我們自己存儲的記錄會按照我們指定的行格式存儲到 User Records 部分。
但是在一開始生成頁的時候，其實并沒有 User Records 這個部分，每當我們插入一條記錄，都會從 Free Space 部分，也就是尚未使用的存儲空間中申請一個記錄大小的空間劃分到 User Records 部分，當 Free Space 部分的空間全部被 User Records 部分替代掉之後，也就意味着這個頁使用完了，如果還有新的記錄插入的話，就需要去申請新的頁了。這個過程的圖示如下。
剛剛上面說到了資料的新增的過程。
那下面就來說說，資料的查找過程，假如我們需要查找一條記錄，我們可以把表空間中的每一頁都加載到記憶體中，然後對記錄挨個判斷是不是我們想要的，在資料量小的時候，沒啥問題，記憶體也可以撐；但是現實就是這麼殘酷，不會給你這個局面；為了解決這問題，mysql 中就有了索引的概念； 大家都知道索引能夠加快資料的查詢，那到底是怎麼個回事呢？下面我就來看看。
6索引的資料結構
在 mysql 中索引的資料結構和剛剛描述的頁幾乎是一模一樣的，而且大小也是 16K, 但是在索引頁中記錄的是頁 (資料頁，索引頁) 的最小主鍵 id 和頁号，以及在索引頁中增加了層級的資訊，從 0 開始往上算，是以頁與頁之間就有了上下層級的概念。
看到這個圖之後，是不是有點似曾相似的感覺，是不是像一棵二叉樹啊，對，沒錯！它就是一棵樹，隻不過我們在這裡隻是簡單畫了三個節點，2 層結構的而已，如果資料多了，可能就會擴充到 3 層的樹，這個就是我們常說的 B+ 樹，最下面那一層的 page level =0, 也就是葉子節點，其餘都是非葉子節點。
看上圖中，我們是單拿一個節點來看，首先它是一個非葉子節點（索引頁），在它的内容區中有 id 和 頁号位址兩部分，這個 id 是對應頁中記錄的最小記錄 id 值，頁号位址是指向對應頁的指針；而資料頁與此幾乎大同小異，差別在于資料頁記錄的是真實的行資料而不是頁位址，而且 id 的也是順序的。
7單表建議值
下面我們就以 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，進而導緻性能下降，是以增加硬體配置（比如把記憶體當磁盤使），可能會帶來立竿見影的性能提升哈。
8總結
1. Mysql 的表資料是以頁的形式存放的，頁在磁盤中不一定是連續的。
2. 頁的空間是 16K, 并不是所有的空間都是用來存放資料的，會有一些固定的資訊，如，頁頭，頁尾，頁碼，校驗碼等等。
3. 在 B+ 樹中，葉子節點和非葉子節點的資料結構是一樣的，差別在于，葉子節點存放的是實際的行資料，而非葉子節點存放的是主鍵和頁号。