MySQL 單表數(shù)據(jù)最大不要超過多少行？為什么？
點(diǎn)擊關(guān)注公眾號(hào)，Java干貨及時(shí)送達(dá)??
來源：https://my.oschina.net/u/4090830/blog/5559454
1 背景
作為在后端圈開車的多年老司機(jī)，是不是經(jīng)常聽到過，“mysql 單表最好不要超過 2000w”,“單表超過 2000w 就要考慮數(shù)據(jù)遷移了”，“你這個(gè)表數(shù)據(jù)都馬上要到 2000w 了，難怪查詢速度慢”
這些名言民語就和 “群里只討論技術(shù)，不開車，開車速度不要超過 120 碼，否則自動(dòng)踢群”，只聽過，沒試過，哈哈。
下面我們就把車速踩到底，干到 180 碼試試…….
2 實(shí)驗(yàn)
實(shí)驗(yàn)一把看看…
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 '創(chuàng)建時(shí)間',
gmt_modified datetime comment '修改時(shí)間'
) comment '人員信息表';
插入一條數(shù)據(jù)：
insert into person values(1,1,'user_1', NOW(), now());
利用 mysql 偽列 rownum 設(shè)置偽列起始點(diǎn)為 1
select (@i:=@i+1) as rownum, person_name
from person, (select @i:=100) as init;
set @i=1;
運(yùn)行下面的 sql，連續(xù)執(zhí)行 20 次，就是 2 的 20 次方約等于 100w 的數(shù)據(jù)；執(zhí)行 23 次就是 2 的 23 次方約等于 800w , 如此下去即可實(shí)現(xiàn)千萬測試數(shù)據(jù)的插入，如果不想翻倍翻倍的增加數(shù)據(jù)，而是想少量，少量的增加，有個(gè)技巧，就是在 SQL 的后面增加 where 條件，如 id > 某一個(gè)值去控制增加的數(shù)據(jù)量即可。
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;
此處需要注意的是，也許你在執(zhí)行到近 800w 或者 1000w 數(shù)據(jù)的時(shí)候，會(huì)報(bào)錯(cuò)：The total number of locks exceeds the lock table size，這是由于你的臨時(shí)表內(nèi)存設(shè)置的不夠大，只需要擴(kuò)大一下設(shè)置參數(shù)即可。
SET GLOBAL tmp_table_size =512*1024*1024; （512M）
SET global innodb_buffer_pool_size= 1*1024*1024*1024 (1G);
先來看一組測試數(shù)據(jù)，這組數(shù)據(jù)是在 mysql8.0 的版本，并且是在我本機(jī)上，由于本機(jī)還跑著 idea , 瀏覽器等各種工具，所以并不是機(jī)器配置就是用于數(shù)據(jù)庫配置，所以測試數(shù)據(jù)只限于參考。
看到這組數(shù)據(jù)似乎好像真的和標(biāo)題對(duì)應(yīng)，當(dāng)數(shù)據(jù)達(dá)到 2000w 以后，查詢時(shí)長急劇上升；難道這就是鐵律嗎？那下面我們就來看看這個(gè)建議值 2kw 是怎么來的？
3 單表數(shù)量限制
首先我們先想想數(shù)據(jù)庫單表行數(shù)最大多大？
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 '創(chuàng)建時(shí)間',
gmt_modified datetime comment '修改時(shí)間'
) comment '人員信息表';
看看上面的建表 sql，id 是主鍵，本身就是唯一的，也就是說主鍵的大小可以限制表的上限，如果主鍵聲明 int 大小，也就是 32 位，那么支持 2^32-1 ~~21 億；如果是 bigint，那就是 2^62-1 ？（36893488147419103232），難以想象這個(gè)的多大了，一般還沒有到這個(gè)限制之前，可能數(shù)據(jù)庫已經(jīng)爆滿了?。?/p>
有人統(tǒng)計(jì)過，如果建表的時(shí)候，自增字段選擇無符號(hào)的 bigint , 那么自增長最大值是 18446744073709551615，按照一秒新增一條記錄的速度，大約什么時(shí)候能用完？
4 表空間
下面我們?cè)賮砜纯此饕慕Y(jié)構(gòu)，對(duì)了，我們下面講內(nèi)容都是基于 Innodb 引擎的，大家都知道 Innodb 的索引內(nèi)部用的是 B+ 樹
這張表數(shù)據(jù)，在硬盤上存儲(chǔ)也是類似如此的，它實(shí)際是放在一個(gè)叫 person.ibd （innodb data）的文件中，也叫做表空間；雖然數(shù)據(jù)表中，他們看起來是一條連著一條，但是實(shí)際上在文件中它被分成很多小份的數(shù)據(jù)頁，而且每一份都是 16K。大概就像下面這樣，當(dāng)然這只是我們抽象出來的，在表空間中還有段、區(qū)、組等很多概念，但是我們需要跳出來看。
5 頁的數(shù)據(jù)結(jié)構(gòu)
因?yàn)槊總€(gè)頁只有 16K 的大小，但是如果數(shù)據(jù)很多，那一頁肯定就放不下這些數(shù)據(jù)，那數(shù)據(jù)肯定就會(huì)被分到其他的頁中，所以為了把這些頁關(guān)聯(lián)起來，肯定就會(huì)有記錄前后頁地址，方便找到對(duì)應(yīng)頁；同時(shí)每頁都是唯一的，那就會(huì)需要有一個(gè)唯一標(biāo)志來標(biāo)記頁，就是頁號(hào)；頁中會(huì)記錄數(shù)據(jù)所以會(huì)存在讀寫操作，讀寫操作會(huì)存在中斷或者其他異常導(dǎo)致數(shù)據(jù)不全等，那就會(huì)需要有校驗(yàn)機(jī)制，所以里面還有會(huì)校驗(yàn)碼，而讀操作最重要的就是效率問題，如果按照記錄一個(gè)個(gè)進(jìn)行遍歷，那肯定是很費(fèi)勁的，所以這里面還會(huì)為數(shù)據(jù)生成對(duì)應(yīng)的頁目錄（Page Directory）; 所以實(shí)際頁的內(nèi)部結(jié)構(gòu)像是下面這樣的。
從圖中可以看出，一個(gè) InnoDB 數(shù)據(jù)頁的存儲(chǔ)空間大致被劃分成了 7 個(gè)部分，有的部分占用的字節(jié)數(shù)是確定的，有的部分占用的字節(jié)數(shù)是不確定的。
在頁的 7 個(gè)組成部分中，我們自己存儲(chǔ)的記錄會(huì)按照我們指定的行格式存儲(chǔ)到 User Records 部分。
但是在一開始生成頁的時(shí)候，其實(shí)并沒有 User Records 這個(gè)部分，每當(dāng)我們插入一條記錄，都會(huì)從 Free Space 部分，也就是尚未使用的存儲(chǔ)空間中申請(qǐng)一個(gè)記錄大小的空間劃分到 User Records 部分，當(dāng) Free Space 部分的空間全部被 User Records 部分替代掉之后，也就意味著這個(gè)頁使用完了，如果還有新的記錄插入的話，就需要去申請(qǐng)新的頁了。這個(gè)過程的圖示如下。
那下面就來說說，數(shù)據(jù)的查找過程，假如我們需要查找一條記錄，我們可以把表空間中的每一頁都加載到內(nèi)存中，然后對(duì)記錄挨個(gè)判斷是不是我們想要的，在數(shù)據(jù)量小的時(shí)候，沒啥問題，內(nèi)存也可以撐；但是現(xiàn)實(shí)就是這么殘酷，不會(huì)給你這個(gè)局面；為了解決這問題，mysql 中就有了索引的概念；大家都知道索引能夠加快數(shù)據(jù)的查詢，那到底是怎么個(gè)回事呢？下面我就來看看。
6 索引的數(shù)據(jù)結(jié)構(gòu)
在 mysql 中索引的數(shù)據(jù)結(jié)構(gòu)和剛剛描述的頁幾乎是一模一樣的，而且大小也是 16K, 但是在索引頁中記錄的是頁 (數(shù)據(jù)頁，索引頁) 的最小主鍵 id 和頁號(hào)，以及在索引頁中增加了層級(jí)的信息，從 0 開始往上算，所以頁與頁之間就有了上下層級(jí)的概念。
看到這個(gè)圖之后，是不是有點(diǎn)似曾相似的感覺，是不是像一棵二叉樹啊，對(duì)，沒錯(cuò)！它就是一棵樹，只不過我們?cè)谶@里只是簡單畫了三個(gè)節(jié)點(diǎn)，2 層結(jié)構(gòu)的而已，如果數(shù)據(jù)多了，可能就會(huì)擴(kuò)展到 3 層的樹，這個(gè)就是我們常說的 B+ 樹，最下面那一層的 page level =0, 也就是葉子節(jié)點(diǎn)，其余都是非葉子節(jié)點(diǎn)。
看上圖中，我們是單拿一個(gè)節(jié)點(diǎn)來看，首先它是一個(gè)非葉子節(jié)點(diǎn)（索引頁），在它的內(nèi)容區(qū)中有 id 和 頁號(hào)地址兩部分，這個(gè) id 是對(duì)應(yīng)頁中記錄的最小記錄 id 值，頁號(hào)地址是指向?qū)?yīng)頁的指針；而數(shù)據(jù)頁與此幾乎大同小異，區(qū)別在于數(shù)據(jù)頁記錄的是真實(shí)的行數(shù)據(jù)而不是頁地址，而且 id 的也是順序的。
7 單表建議值
哎呀，媽呀！這不是正好就是文章開頭說的最大行數(shù)建議值 2000w 嘛！對(duì)的，一般 B+ 數(shù)的層級(jí)最多也就是 3 層，你試想一下，如果是 4 層，除了查詢的時(shí)候磁盤 IO 次數(shù)會(huì)增加，而且這個(gè) Total 值會(huì)是多少，大概應(yīng)該是 3 百多億吧，也不太合理，所以，3 層應(yīng)該是比較合理的一個(gè)值。
到這里難道就完了？
不 我們剛剛在說 Y 的值時(shí)候假設(shè)的是 1K ，那比如我實(shí)際當(dāng)行的數(shù)據(jù)占用空間不是 1K , 而是 5K, 那么單個(gè)數(shù)據(jù)頁最多只能放下 3 條數(shù)據(jù) 同樣，還是按照 Z=3 的值來計(jì)算，那 Total = （1280 ^2） *3 = 4915200 （近 500w）
所以，在保持相同的層級(jí)（相似查詢性能）的情況下，在行數(shù)據(jù)大小不同的情況下，其實(shí)這個(gè)最大建議值也是不同的，而且影響查詢性能的還有很多其他因素，比如，數(shù)據(jù)庫版本，服務(wù)器配置，sql 的編寫等等，MySQL 為了提高性能，會(huì)將表的索引裝載到內(nèi)存中。在 InnoDB buffer size 足夠的情況下，其能完成全加載進(jìn)內(nèi)存，查詢不會(huì)有問題。但是，當(dāng)單表數(shù)據(jù)庫到達(dá)某個(gè)量級(jí)的上限時(shí)，導(dǎo)致內(nèi)存無法存儲(chǔ)其索引，使得之后的 SQL 查詢會(huì)產(chǎn)生磁盤 IO，從而導(dǎo)致性能下降，所以增加硬件配置（比如把內(nèi)存當(dāng)磁盤使），可能會(huì)帶來立竿見影的性能提升哈。
8 總結(jié)
Mysql 的表數(shù)據(jù)是以頁的形式存放的，頁在磁盤中不一定是連續(xù)的。 頁的空間是 16K, 并不是所有的空間都是用來存放數(shù)據(jù)的，會(huì)有一些固定的信息，如，頁頭，頁尾，頁碼，校驗(yàn)碼等等。 在 B+ 樹中，葉子節(jié)點(diǎn)和非葉子節(jié)點(diǎn)的數(shù)據(jù)結(jié)構(gòu)是一樣的，區(qū)別在于，葉子節(jié)點(diǎn)存放的是實(shí)際的行數(shù)據(jù)，而非葉子節(jié)點(diǎn)存放的是主鍵和頁號(hào)。 索引結(jié)構(gòu)不會(huì)影響單表最大行數(shù)，2kw 也只是推薦值，超過了這個(gè)值可能會(huì)導(dǎo)致 B + 樹層級(jí)更高，影響查詢性能。
參考資料：
https://www.jianshu.com/p/cf5d381ef637 https://www.modb.pro/db/139052 《MYSQL 內(nèi)核：INNODB 存儲(chǔ)引擎 卷 1》
·················END·················
資料鏈接
清華學(xué)姐自學(xué)的Linux筆記，天花板級(jí)別！ 新版鳥哥Linux私房菜資料 阿里大佬總結(jié)的《圖解Java》火了，完整版PDF開放下載！ Alibaba官方上線！SpringBoot+SpringCloud全彩指南 國內(nèi)最強(qiáng)的SpringBoot+Vue全棧項(xiàng)目天花板，不接受反駁！