誰說MySQL單表行數(shù)不要超過2000W？

背景

網(wǎng)上看了一篇文章《為什么說MySQL單表行數(shù)不要超過2000w》，親自實踐了一下，跟原作者有不同的結論。原文的結論是2000W左右性能會成指數(shù)級的下降，而我的結論是：隨著數(shù)據(jù)量成倍地增加，查詢的時間也剛好是成倍增加，是成正比的。

我并不會直接搬運網(wǎng)上的文章和結論，下邊的實踐過程是參考文章的實踐方式進行優(yōu)化的。原文的理論感覺是正確的，但為啥我實踐的結果不支持他的理論？動手能力強的小伙伴，可以照的我的實踐過程試試。

前置條件

查看sql語句執(zhí)行時間和效率

showprofiles;#是mysql提供可以用來分析當前會話中語句執(zhí)行的資源消耗情況?？梢杂脕鞸QL的調(diào)優(yōu)測量。
select@@have_profiling；#查看是否支持profiling

setprofiling=1;#設置MySQL支持profile
selectcount(*)fromtmp.person;#執(zhí)行自己的sql語句；
showprofiles;就可以查到sql語句的執(zhí)行時間；

效果如下

mysql>setprofiling=1;
QueryOK,0rowsaffected,1warning(0.00sec)

mysql>selectcount(*)fromtmp.person;
+----------+
|count(*)|
+----------+
|2|
+----------+
1rowinset(0.00sec)

mysql>showprofiles;
+----------+------------+---------------------------------+
|Query_ID|Duration|Query|
+----------+------------+---------------------------------+
|1|0.00017775|selectcount(*)fromtmp.person|
+----------+------------+---------------------------------+
1rowinset,1warning(0.00sec)

實驗

建一張表

dropdatabaseifexiststmp;
createdatabasetmp;
usetmp;
CREATETABLEperson(
idintNOTNULLAUTO_INCREMENTPRIMARYKEYcomment'主鍵',
person_idtinyintnotnullcomment'用戶id',
person_nameVARCHAR(200)comment'用戶名稱',
gmt_createdatetimecomment'創(chuàng)建時間',
gmt_modifieddatetimecomment'修改時間'
)comment'人員信息表';

插入一條數(shù)據(jù)

insertintopersonvalues(1,1,'user_1',NOW(),now());

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

select(@i:=@i+1)asrownum,person_namefromperson,(select@i:=100)asinit;
set@i=1;

運行下面的 sql，連續(xù)執(zhí)行 20 次，就是 2 的 20 次方約等于 100w 的數(shù)據(jù)；執(zhí)行 23 次就是 2 的 23 次方約等于 800w , 如此下去即可實現(xiàn)千萬測試數(shù)據(jù)的插入，如果不想翻倍翻倍的增加數(shù)據(jù)，而是想少量，少量的增加，有個技巧，就是在 SQL 的后面增加limit條件，如limit 100控制將要新增的數(shù)據(jù)量。

insertintoperson(id,person_id,person_name,gmt_create,gmt_modified)
select@i:=@i+1,
left(rand()*10,10)asperson_id,
concat('user_',@i%2048),
date_add(gmt_create,interval+@i*cast(rand()*100assigned)SECOND),
date_add(date_add(gmt_modified,interval+@i*cast(rand()*100assigned)SECOND),interval+cast(rand()*1000000assigned)SECOND)
fromperson;

此處需要注意的是，也許你在執(zhí)行到近 800w 或者 1000w 數(shù)據(jù)的時候，會報錯：The total number of locks exceeds the lock table size，這是由于你的臨時表內(nèi)存設置的不夠大，只需要擴大一下設置參數(shù)即可。

SETGLOBALtmp_table_size=512*1024*1024;#（512M）
SETglobalinnodb_buffer_pool_size=1*1024*1024*1024;#(1G);

驗證

selectcount(1)fromperson;
selectcount(1)frompersonwhereperson_id=6;
showprofiles;

優(yōu)化測試

MySQL函數(shù)

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (youmightwant to use the less safe log_bin_trust_function_creators variable)

這是因為mysql 默認不允許創(chuàng)建自定義函數(shù)（安全性的考慮），此時我們需要將參數(shù) log_bin_trust_function_creators 設置為開啟狀態(tài)

showvariableslike'log_bin_trust_function_creators';
setgloballog_bin_trust_function_creators=1;

但這樣只是臨時設置，重啟終端后該設置即會失效。如果要配置永久的，需要在配置文件的 [mysqld] 上配置以下屬性： log_bin_trust_function_creators=1

--隨機產(chǎn)生字符串
dropfunctionifexistsrand_string;--先判斷是否已存在同名函數(shù)，如果已存在則先刪除
DELIMITER$$--兩個$$表示結束
createfunctionrand_string(nint)returnsvarchar(255)
begin
declarechars_strvarchar(100)default'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
declarereturn_strvarchar(255)default'';
declareiintdefault0;
whilei
--隨機生成編號
dropfunctionifexistsrand_num;
DELIMITER$$
createfunctionrand_num()
returnsint(5)
begin
declareiintdefault0;
seti=floor(100+rand()*10);
returni;
end$$
DELIMITER;

	

	自定義函數(shù)的調(diào)用和其他普通函數(shù)的調(diào)用一樣，示例如下：

	
selectrand_string(5);
selectrand_num();

	

	一鍵測試

	
dropdatabaseifexiststmp;
createdatabasetmp;
usetmp;
CREATETABLEperson(
idintNOTNULLAUTO_INCREMENTPRIMARYKEYcomment'主鍵',
person_idtinyintnotnullcomment'用戶id',
person_nameVARCHAR(200)comment'用戶名稱',
gmt_createdatetimecomment'創(chuàng)建時間',
gmt_modifieddatetimecomment'修改時間'
)comment'userinfo';


SET@@profiling=0;
SET@@profiling_history_size=0;
SET@@profiling_history_size=100;
SET@@profiling=1;
insertintopersonvalues(1,1,'user_1',NOW(),now());
showprofiles;

set@i=1;
dropfunctionifexiststest_performance;
DELIMITER$$#設置結束符
createfunctiontest_performance(numint)returnsvarchar(255)
begin
declarereturn_strvarchar(255)default'';
if(num>0)then
insertintoperson(id,person_id,person_name,gmt_create,gmt_modified)
select@i:=@i+1,
left(rand()*10,10)asperson_id,
concat('user_',@i%2048),
date_add(gmt_create,interval+@i*cast(rand()*100assigned)SECOND),
date_add(date_add(gmt_modified,interval+@i*cast(rand()*100assigned)SECOND),interval+cast(rand()*1000000assigned)SECOND)
frompersonlimitnum;
else
insertintoperson(id,person_id,person_name,gmt_create,gmt_modified)
select@i:=@i+1,
left(rand()*10,10)asperson_id,
concat('user_',@i%2048),
date_add(gmt_create,interval+@i*cast(rand()*100assigned)SECOND),
date_add(date_add(gmt_modified,interval+@i*cast(rand()*100assigned)SECOND),interval+cast(rand()*1000000assigned)SECOND)
fromperson;
endif;

selectcount(1)intoreturn_strfrompersonwhereperson_id="9";
selectcount(1)intoreturn_strfromperson;

returnreturn_str;
end$$

DELIMITER;

selecttest_performance(0);#2^1
selecttest_performance(0);#2^2
selecttest_performance(0);#2^3
selecttest_performance(0);#2^4
selecttest_performance(0);#2^5
selecttest_performance(0);#2^6
selecttest_performance(0);#2^7
selecttest_performance(0);#2^8
selecttest_performance(0);#2^9
selecttest_performance(0);#2^10
selecttest_performance(0);#2^11
selecttest_performance(0);#2^12
selecttest_performance(0);#2^13
selecttest_performance(0);#2^14
selecttest_performance(0);#2^15
selecttest_performance(0);#2^16
selecttest_performance(0);#2^17
selecttest_performance(0);#2^18
selecttest_performance(0);#2^19次方=524288
selecttest_performance(475712);#補上475712湊夠100w
selecttest_performance(250000);#125w
selecttest_performance(0);#250w
selecttest_performance(0);#500w
selecttest_performance(0);#1kw
selecttest_performance(0);#2kw
selecttest_performance(0);#4kw
selecttest_performance(0);#8kw
selecttest_performance(0);#16kw
selecttest_performance(0);#32kw


	

	實驗結果

				數(shù)據(jù)量
			

				有查詢條件
			

				無查詢條件
		

				125w
			

				0.1309075
			

				0.08538975
		

				250w
			

				0.25213025
			

				0.18290725
		

				500w
			

				0.4816255
			

				0.35839375
		

				1kw
			

				0.94493875
			

				0.6809015
		

				2kw
			

				1.878788
			

				1.44631675
		

				4kw
			

				5.40815725
			

				3.05356825
		

				8kw
			

				11.074242
			

				6.6517985
		

				16kw
			

				22.753852
			

				17.94861325
		

				2kw
			

				46.36041225
			

				36.5971315
		

	Figure_1

	理論

	單表數(shù)量限制

	首先我們先想想數(shù)據(jù)庫單表行數(shù)最大多大？

	
CREATETABLEperson(
idint(10)NOTNULLAUTO_INCREMENTPRIMARYKEYcomment'主鍵',
person_idtinyintnotnullcomment'用戶id',
person_nameVARCHAR(200)comment'用戶名稱',
gmt_createdatetimecomment'創(chuàng)建時間',
gmt_modifieddatetimecomment'修改時間'
)comment'人員信息表';

	

	看看上面的建表 sql，id 是主鍵，本身就是唯一的，也就是說主鍵的大小可以限制表的上限，如果主鍵聲明 int 大小，也就是 32 位，那么支持 2^32-1 ~~21 億；如果是 bigint，那就是 2^62-1 ？（36893488147419103232），難以想象這個的多大了，一般還沒有到這個限制之前，可能數(shù)據(jù)庫已經(jīng)爆滿了?。?/p>

	有人統(tǒng)計過，如果建表的時候，自增字段選擇無符號的 bigint , 那么自增長最大值是 18446744073709551615，按照一秒新增一條記錄的速度，大約什么時候能用完？

	圖片

	表空間

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

	

	圖片

	這張表數(shù)據(jù)，在硬盤上存儲也是類似如此的，它實際是放在一個叫 person.ibd （innodb data）的文件中，也叫做表空間；雖然數(shù)據(jù)表中，他們看起來是一條連著一條，但是實際上在文件中它被分成很多小份的數(shù)據(jù)頁，而且每一份都是 16K。

	大概就像下面這樣，當然這只是我們抽象出來的，在表空間中還有段、區(qū)、組等很多概念，但是我們需要跳出來看。

	

	圖片

	頁的數(shù)據(jù)結構

	因為每個頁只有 16K 的大小，但是如果數(shù)據(jù)很多，那一頁肯定就放不下這些數(shù)據(jù)，那數(shù)據(jù)肯定就會被分到其他的頁中，所以為了把這些頁關聯(lián)起來，肯定就會有記錄前后頁地址，方便找到對應頁；同時每頁都是唯一的，那就會需要有一個唯一標志來標記頁，就是頁號；

	頁中會記錄數(shù)據(jù)所以會存在讀寫操作，讀寫操作會存在中斷或者其他異常導致數(shù)據(jù)不全等，那就會需要有校驗機制，所以里面還有會校驗碼，而讀操作最重要的就是效率問題，如果按照記錄一個個進行遍歷，那肯定是很費勁的，所以這里面還會為數(shù)據(jù)生成對應的頁目錄（Page Directory）; 所以實際頁的內(nèi)部結構像是下面這樣的。

	

	圖片

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

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

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

	

	圖片

	剛剛上面說到了數(shù)據(jù)的新增的過程。

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

	索引的數(shù)據(jù)結構

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

	

	圖片

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

	

	圖片

	看上圖中，我們是單拿一個節(jié)點來看，首先它是一個非葉子節(jié)點（索引頁），在它的內(nèi)容區(qū)中有 id 和 頁號地址兩部分，這個 id 是對應頁中記錄的最小記錄 id 值，頁號地址是指向?qū)摰闹羔?；而?shù)據(jù)頁與此幾乎大同小異，區(qū)別在于數(shù)據(jù)頁記錄的是真實的行數(shù)據(jù)而不是頁地址，而且 id 的也是順序的。

	單表建議值

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

	比如說我們需要查找一個 id=6 的行數(shù)據(jù)，因為在非葉子節(jié)點中存放的是頁號和該頁最小的 id，所以我們從頂層開始對比，首先看頁號 10 中的目錄，有 [id=1, 頁號 = 20],[id=5, 頁號 = 30], 說明左側節(jié)點最小 id 為 1，右側節(jié)點最小 id 是 5；6>5, 那按照二分法查找的規(guī)則，肯定就往右側節(jié)點繼續(xù)查找，找到頁號 30 的節(jié)點后，發(fā)現(xiàn)這個節(jié)點還有子節(jié)點（非葉子節(jié)點），那就繼續(xù)比對，同理，6>5&&6<7, 所以找到了頁號 60，找到頁號 60 之后，發(fā)現(xiàn)此節(jié)點為葉子節(jié)點（數(shù)據(jù)節(jié)點），于是將此頁數(shù)據(jù)加載至內(nèi)存進行一一對比，結果找到了 id=6 的數(shù)據(jù)行。

	從上述的過程中發(fā)現(xiàn)，我們?yōu)榱瞬檎?id=6 的數(shù)據(jù)，總共查詢了三個頁，如果三個頁都在磁盤中（未提前加載至內(nèi)存），那么最多需要經(jīng)歷三次的磁盤 IO。需要注意的是，圖中的頁號只是個示例，實際情況下并不是連續(xù)的，在磁盤中存儲也不一定是順序的。

	

	圖片

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

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

	所以，同樣一個 16K 的頁，非葉子節(jié)點里的每條數(shù)據(jù)都指向新的頁，而新的頁有兩種可能

	? 如果是葉子節(jié)點，那么里面就是一行行的數(shù)據(jù)

	? 如果是非葉子節(jié)點的話，那么就會繼續(xù)指向新的頁

	假設

	? 非葉子節(jié)點內(nèi)指向其他頁的數(shù)量為 x

	? 葉子節(jié)點內(nèi)能容納的數(shù)據(jù)行數(shù)為 y

	? B+ 數(shù)的層數(shù)為 z

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

	

	圖片

	X =？

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

	Y=？

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

	算到這邊了，是不是心里已經(jīng)有譜了啊 根據(jù)上述的公式，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）

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

	到這里難道就完了？

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

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

	總結

	1.Mysql 的表數(shù)據(jù)是以頁的形式存放的，頁在磁盤中不一定是連續(xù)的。

	2.頁的空間是 16K, 并不是所有的空間都是用來存放數(shù)據(jù)的，會有一些固定的信息，如，頁頭，頁尾，頁碼，校驗碼等等。

	3.在 B+ 樹中，葉子節(jié)點和非葉子節(jié)點的數(shù)據(jù)結構是一樣的，區(qū)別在于，葉子節(jié)點存放的是實際的行數(shù)據(jù)，而非葉子節(jié)點存放的是主鍵和頁號。

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

	上邊理論是原文的，我的實踐結果是：隨著數(shù)據(jù)量成倍地增加，查詢的時間也剛好是成倍增加，是成正比的。

	我感覺原作者的理論是對的，但我照著原作者的實踐思路，得出的結果并不支持他的理論，有高手來評判一下嗎？

	審核編輯：湯梓紅