來源：https://my.oschina.net/u/4090830/blog/5559454
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 總結
- Mysql 的表資料是以頁的形式存放的，頁在磁盤中不一定是連續的，
- 頁的空間是 16K, 并不是所有的空間都是用來存放資料的，會有一些固定的資訊，如，頁頭，頁尾，頁碼，校驗碼等等，
- 在 B+ 樹中，葉子節點和非葉子節點的資料結構是一樣的，區別在于，葉子節點存放的是實際的行資料，而非葉子節點存放的是主鍵和頁號，
- 索引結構不會影響單表最大行數，2kw 也只是推薦值，超過了這個值可能會導致 B + 樹層級更高，影響查詢性能，
參考資料：
- https://www.jianshu.com/p/cf5d381ef637
- https://www.modb.pro/db/139052
- 《MYSQL 內核：INNODB 存盤引擎 卷 1》
近期熱文推薦：
1.1,000+ 道 Java面試題及答案整理(2022最新版)
2.勁爆！Java 協程要來了，，，
3.Spring Boot 2.x 教程，太全了！
4.別再寫滿屏的爆爆爆炸類了，試試裝飾器模式，這才是優雅的方式！！
5.《Java開發手冊（嵩山版）》最新發布，速速下載！
覺得不錯，別忘了隨手點贊+轉發哦！
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/553690.html
標籤：Java
下一篇：返回列表