為什么會有“建議MySQL單表數(shù)據(jù)控制在2000W”的說法？
前言
經(jīng)常聽到“建議MySQL單表數(shù)據(jù)控制在2000W”這樣的說法，這個說法的背后是因為表數(shù)據(jù)量過大會導致查詢性能低下。那么2000W是怎么來的？
行記錄是如何存儲的？
這得從MySQL存儲設計說起，以InnoDB存儲引擎為例：
- InnoDB是以頁為單位進行存儲和管理數(shù)據(jù)的，目的是為了減少磁盤IO的次數(shù)，提高數(shù)據(jù)的讀寫速度。
- 為了提高查詢效率，InnoDB以索引組織表數(shù)據(jù)，且每張表至少會有一個索引（聚簇/主鍵索引）。
所以，在組織表數(shù)據(jù)時會存在索引頁和數(shù)據(jù)頁。不論是哪種頁，其結(jié)構(gòu)是一樣的，大概是這樣的：
圖片
圖中的「用戶數(shù)據(jù)」可以存放多少行記錄是至關(guān)重要的，因為它關(guān)系到“單表數(shù)據(jù)控制在2000W”的這個說法是否成立。
那么，索引頁和數(shù)據(jù)頁可以存放多少行記錄？
數(shù)據(jù)頁可以存放多少行記錄？
InnoDB頁大小默認為16KB，「文件頭」、「頁頭」、「頁目錄」等占用了1/16的空間，剩下的15KB就用來存行記錄。
索引頁存放的數(shù)據(jù)是索引和指針，數(shù)據(jù)頁則存放的是完整的行數(shù)據(jù)。
通常我們的主鍵都是數(shù)值類型（int、bigint...），以bigint為例，一行索引記錄會占用8B加上指針占用的空間6B，也就是14B。那么索引頁就可以存放15*1024/14≈1098行記錄。
數(shù)據(jù)頁能存放多少行記錄則需要看每個字段占用多少空間來估算，這里假設一行記錄是1KB，那么數(shù)據(jù)頁可以存放15行記錄。
基于以上的情況，InnoDB是如何以索引組織2000W數(shù)據(jù)的？
InnoDB如何以索引組織2000W數(shù)據(jù)？
以B+tree索引結(jié)構(gòu)為例，其葉子結(jié)點是數(shù)據(jù)頁，非葉子結(jié)點為索引頁。
- 如果樹的高度為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”并不是沒有道理的。因為樹高度過高時，會有以下兩個原因?qū)е虏樵兯俣茸兟?/p>
- 樹高度過高，意味著在進行數(shù)據(jù)檢索時需要進行更多的磁盤I/O操作。
- 在這樣大的數(shù)據(jù)量下，索引的體積也會很大，維護索引結(jié)構(gòu)的復雜度也會相應提高。
總結(jié)
綜上所述，“建議MySQL單表數(shù)據(jù)控制在2000W”是根據(jù)每行記錄大小為1KB估算的，并非一個標準，還是要根據(jù)實際情況去決定單表的數(shù)據(jù)量。
