為什么會(huì)有“建議MySQL單表數(shù)據(jù)控制在2000W”的說(shuō)法？

作者：Hi程序員 2024-08-05 10:44:32

“建議MySQL單表數(shù)據(jù)控制在2000W”是根據(jù)每行記錄大小為1KB估算的，并非一個(gè)標(biāo)準(zhǔn)，還是要根據(jù)實(shí)際情況去決定單表的數(shù)據(jù)量。

前言

經(jīng)常聽(tīng)到“建議MySQL單表數(shù)據(jù)控制在2000W”這樣的說(shuō)法，這個(gè)說(shuō)法的背后是因?yàn)楸頂?shù)據(jù)量過(guò)大會(huì)導(dǎo)致查詢(xún)性能低下。那么2000W是怎么來(lái)的？

這得從MySQL存儲(chǔ)設(shè)計(jì)說(shuō)起，以InnoDB存儲(chǔ)引擎為例：

InnoDB是以頁(yè)為單位進(jìn)行存儲(chǔ)和管理數(shù)據(jù)的，目的是為了減少磁盤(pán)IO的次數(shù)，提高數(shù)據(jù)的讀寫(xiě)速度。
為了提高查詢(xún)效率，InnoDB以索引組織表數(shù)據(jù)，且每張表至少會(huì)有一個(gè)索引（聚簇/主鍵索引）。

所以，在組織表數(shù)據(jù)時(shí)會(huì)存在索引頁(yè)和數(shù)據(jù)頁(yè)。不論是哪種頁(yè)，其結(jié)構(gòu)是一樣的，大概是這樣的：

圖片

圖中的「用戶(hù)數(shù)據(jù)」可以存放多少行記錄是至關(guān)重要的，因?yàn)樗P(guān)系到“單表數(shù)據(jù)控制在2000W”的這個(gè)說(shuō)法是否成立。

那么，索引頁(yè)和數(shù)據(jù)頁(yè)可以存放多少行記錄？

InnoDB頁(yè)大小默認(rèn)為16KB，「文件頭」、「頁(yè)頭」、「頁(yè)目錄」等占用了1/16的空間，剩下的15KB就用來(lái)存行記錄。

索引頁(yè)存放的數(shù)據(jù)是索引和指針，數(shù)據(jù)頁(yè)則存放的是完整的行數(shù)據(jù)。

通常我們的主鍵都是數(shù)值類(lèi)型（int、bigint...），以bigint為例，一行索引記錄會(huì)占用8B加上指針占用的空間6B，也就是14B。那么索引頁(yè)就可以存放15*1024/14≈1098行記錄。

數(shù)據(jù)頁(yè)能存放多少行記錄則需要看每個(gè)字段占用多少空間來(lái)估算，這里假設(shè)一行記錄是1KB，那么數(shù)據(jù)頁(yè)可以存放15行記錄。

基于以上的情況，InnoDB是如何以索引組織2000W數(shù)據(jù)的？

以B+tree索引結(jié)構(gòu)為例，其葉子結(jié)點(diǎn)是數(shù)據(jù)頁(yè)，非葉子結(jié)點(diǎn)為索引頁(yè)。

如此看來(lái)，“建議MySQL單表數(shù)據(jù)控制在2000W”并不是沒(méi)有道理的。因?yàn)闃?shù)高度過(guò)高時(shí)，會(huì)有以下兩個(gè)原因?qū)е虏樵?xún)速度變慢：

樹(shù)高度過(guò)高，意味著在進(jìn)行數(shù)據(jù)檢索時(shí)需要進(jìn)行更多的磁盤(pán)I/O操作。
在這樣大的數(shù)據(jù)量下，索引的體積也會(huì)很大，維護(hù)索引結(jié)構(gòu)的復(fù)雜度也會(huì)相應(yīng)提高。

綜上所述，“建議MySQL單表數(shù)據(jù)控制在2000W”是根據(jù)每行記錄大小為1KB估算的，并非一個(gè)標(biāo)準(zhǔn)，還是要根據(jù)實(shí)際情況去決定單表的數(shù)據(jù)量。

責(zé)任編輯：武曉燕來(lái)源： Hi程序員