前言

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

行記錄是如何存儲(chǔ)的？

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

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

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

圖片

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

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

數(shù)據(jù)頁可以存放多少行記錄？

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

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

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

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

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

InnoDB如何以索引組織2000W數(shù)據(jù)？

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

• 如果樹的高度為2，1層索引頁和數(shù)據(jù)頁，那么可以存放1098*15≈2W行記錄。
• 如果樹的高度為3，2層索引頁和1層數(shù)據(jù)頁，那么可以存放1098*1098*15≈2000W行記錄。
• 如果樹的高度為4，3層索引頁和1層數(shù)據(jù)頁，可以存放1098*1098*1098*15≈200000W行記錄。

如此看來，“建議MySQL單表數(shù)據(jù)控制在2000W”并不是沒有道理的。因?yàn)闃涓叨冗^高時(shí)，會(huì)有以下兩個(gè)原因?qū)е虏樵兯俣茸兟?/p>

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

總結(jié)

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