一、前言

二、MySQL是如何存儲數(shù)據(jù)的？

    1. 數(shù)據(jù)頁（Page）

    2. 從頁到索引——B+樹索引

    3. 存入數(shù)據(jù)如下

    4. 關(guān)鍵原理總結(jié)

三、MySQL是如何查詢到數(shù)據(jù)的？

    1. 舉個例子：select * from table where id = 5

    2. 查詢步驟總結(jié)

四、2000萬這個上限值如何算出來的？

    1. B+樹承載的記錄數(shù)量

    2. 行數(shù)超一億就慢了嗎？

    3. B樹承載的記錄數(shù)量

五、總結(jié)：生死博弈的核心

六、拓展問題

    1. 為啥設(shè)計單頁大小16k?

    2. 字符串怎么做索引?

    3. 索引字段的長度有限制嗎?

一、前言

本文核心介紹，為何業(yè)界會有這樣的說法？—— “MySQL單表存儲數(shù)據(jù)量最好別超過千萬級別”

當然這里是有前提條件的，也是我們最常使用到的：

• InnoDB存儲引擎；
• 使用的是默認索引數(shù)據(jù)結(jié)構(gòu)——B+樹;
• 正常普通表數(shù)據(jù)（列數(shù)量控制在幾個到一二十個，普通字段類型及長度）。

接下來咱們就探究一下原因，逐步揭開答案。

二、MySQL是如何存儲數(shù)據(jù)的？

核心結(jié)構(gòu)：B+樹 + 16KB數(shù)據(jù)頁

這里如下，建一張普通表user：

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '名字',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年齡',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

數(shù)據(jù)頁（Page）

介紹

InnoDB存儲的最小單位，固定為16KB 。每頁存儲表數(shù)據(jù)（行記錄）、索引、元信息等。數(shù)據(jù)加載到內(nèi)存時以頁為單位，減少磁盤I/O次數(shù)。

頁的結(jié)構(gòu)

假設(shè)我們有這么一張user數(shù)據(jù)表。其中id是唯一主鍵。這看起來的一行行數(shù)據(jù)，為了方便，我們后面就叫它們record吧。這張表看起來就跟個excel表格一樣。excel的數(shù)據(jù)在硬盤上是一個xx.excel的文件。而上面user表數(shù)據(jù)，在硬盤上其實也是類似，放在了user.ibd文件下。含義是user表的innodb data文件，又叫表空間。雖然在數(shù)據(jù)表里，它們看起來是挨在一起的。但實際上在user.ibd里他們被分成很多小份的數(shù)據(jù)頁，每份大小16k。類似于下面這樣。

圖片

ibd文件內(nèi)部有大量的頁，我們把視角聚焦一下，放到頁上面。整個頁16k，不大，但record這么多，一頁肯定放不下，所以會分開放到很多頁里。并且這16k，也不可能全用來放record對吧。因為record們被分成好多份，放到好多頁里了，為了唯一標識具體是哪一頁，那就需要引入頁號（其實是一個表空間的地址偏移量）。同時為了把這些數(shù)據(jù)頁給關(guān)聯(lián)起來，于是引入了前后指針，用于指向前后的頁。這些都被加到了頁頭里。頁是需要讀寫的，16k說小也不小，寫一半電源線被拔了也是有可能發(fā)生的，所以為了保證數(shù)據(jù)頁的正確性，還引入了校驗碼。這個被加到了頁尾。那剩下的空間，才是用來放我們的record的。而record如果行數(shù)特別多的話，進入到頁內(nèi)時挨個遍歷，效率也不太行，所以為這些數(shù)據(jù)生成了一個頁目錄，具體實現(xiàn)細節(jié)不重要。只需要知道，它可以通過二分查找的方式將查找效率從O(n) 變成O(lgn)。

圖片

從頁到索引—B+樹索引

如果想查一條record，我們可以把表空間里每一頁都撈出來（全表掃描），再把里面的record撈出來挨個判斷是不是我們要找的。行數(shù)量小的時候，這么操作也沒啥問題。行數(shù)量大了，性能就慢了，于是為了加速搜索，我們可以在每個數(shù)據(jù)頁里選出主鍵id最小的record，而且只需要它們的主鍵id和所在頁的頁號。組成新的record，放入到一個新生成的一個數(shù)據(jù)頁中，這個新數(shù)據(jù)頁跟之前的頁結(jié)構(gòu)沒啥區(qū)別，而且大小還是16k。但為了跟之前的數(shù)據(jù)頁進行區(qū)分。數(shù)據(jù)頁里加入了頁層級（page level）的信息，從0開始往上算。于是頁與頁之間就有了上下層級的概念，就像下面這樣。

突然頁跟頁之間看起來就像是一棵倒過來的樹了。也就是我們常說的B+樹索引。最下面那一層，page level 為0，也就是所謂的葉子結(jié)點，其余都叫非葉子結(jié)點。上面展示的是兩層的樹，如果數(shù)據(jù)變多了，我們還可以再通過類似的方法，再往上構(gòu)建一層。就成了三層的樹。

• 聚簇索引：數(shù)據(jù)按主鍵組織成一棵B+樹。葉子節(jié)點存儲完整行數(shù)據(jù) ，非葉子節(jié)點存儲主鍵值+指向子頁的指針（類似目錄）。
• 二級索引：葉子節(jié)點存儲主鍵值，查詢時需回表（根據(jù)主鍵回聚簇索引查數(shù)據(jù)）。
• 行格式：如COMPACT格式，行數(shù)據(jù)包含事務(wù)ID、回滾指針、列值等信息。行大小影響單頁存儲的行數(shù)。

存入數(shù)據(jù)

比如表數(shù)據(jù)已存在id為1-10的數(shù)據(jù)存儲，簡單比方如下：

然后需要插入id=11的數(shù)據(jù)：

• 加載1號數(shù)據(jù)頁入內(nèi)存，分析判定；
• id=11的數(shù)據(jù)大于id=10，那么鎖定頁號5，判定5號頁是否還可以存下數(shù)據(jù)11；
• 可以存下，將id=11的數(shù)據(jù)寫入到5號頁中。

關(guān)鍵原理總結(jié)

所有數(shù)據(jù)通過B+樹有序組織，數(shù)據(jù)存儲在數(shù)據(jù)頁上，頁與頁之間以雙向鏈表連接，非葉子節(jié)點提供快速定位路徑，葉子節(jié)點存儲實際的數(shù)據(jù)。 

三、MySQL是如何查詢到數(shù)據(jù)的？

上面我們已經(jīng)介紹了MySQL中使用頁存儲數(shù)據(jù)，以及B+樹索引數(shù)據(jù)的結(jié)構(gòu)，那現(xiàn)在我們就可以通過這樣一棵B+樹加速查詢。

舉個例子：select * from table where id = 5

比方說我們想要查找行數(shù)據(jù)5。會先從頂層頁的record們?nèi)胧?。record里包含了主鍵id和頁號（頁地址）。

如下圖所示，左邊2號頁最小id是1，向右3號頁最小id是4，然后4號頁最小是7，最后5號頁最小是10。

圖片

那id=5的數(shù)據(jù)如果存在，5大于4小于7，那必定在3號頁里面。于是順著的record的頁地址就到了3號數(shù)據(jù)頁里，于是加載3號數(shù)據(jù)頁到內(nèi)存。在數(shù)據(jù)頁里找到id=5的數(shù)據(jù)行，完成查詢。

另外需要注意的是，上面的頁的頁號并不是連續(xù)的，它們在磁盤里也不一定是挨在一起的。這個過程中查詢了2個頁（1號跟3號），如果這三個頁都在磁盤中（沒有被提前加載到內(nèi)存中），那么最多需要經(jīng)歷兩次磁盤IO查詢，它們才能被加載到內(nèi)存中。（如果考慮1號如果是root常駐內(nèi)存，那么需要磁盤IO一次即可定位到）。

圖片

查詢步驟總結(jié)

以聚簇索引搜索為例（假設(shè)id是主鍵）：

• 從根頁開始搜索 ：

加載根頁（常駐內(nèi)存）到Buffer Pool，根據(jù)指針找到下一層節(jié)點。

• 逐層定位葉子節(jié)點 ：

在非葉子節(jié)點頁（存儲主鍵+指針）中二分查找 ，定位id=5所在范圍的子頁（如頁A）。

重復(fù)此過程，直到葉子節(jié)點頁。

• 葉子節(jié)點二分查找 ：

在葉子頁內(nèi)通過主鍵二分查找定位到行記錄，返回完整數(shù)據(jù)。

I/O次數(shù)分析 ：

• 樹高為3時：根頁 + 中間頁 + 葉子頁 = 3次磁盤I/O （若頁不在內(nèi)存中）。
• B+樹矮胖特性 ：3層即可支撐千萬級數(shù)據(jù)（接下來分析），是高效查詢的基礎(chǔ)。

四、2000萬這個上限值如何算出來的？

在我們清楚了MySQL是如何存儲及查詢數(shù)據(jù)后，那么2000萬這個數(shù)值又是如何得來的呢？超過2000萬比如存儲一億數(shù)據(jù)會如何？

B+樹承載的記錄數(shù)量

從上面的結(jié)構(gòu)里可以看出B+樹的最末級葉子結(jié)點里放了record數(shù)據(jù)。而非葉子結(jié)點里則放了用來加速查詢的索引數(shù)據(jù)。也就是說同樣一個16k的頁，非葉子節(jié)點里每一條數(shù)據(jù)都指向一個新的頁，而新的頁有兩種可能。

• 如果是末級葉子節(jié)點的話，那么里面放的就是一行行record數(shù)據(jù)。
• 如果是非葉子節(jié)點，那么就會循環(huán)繼續(xù)指向新的數(shù)據(jù)頁。

假設(shè)

• 非葉子節(jié)點內(nèi)指向其他內(nèi)存頁的指針數(shù)量為x（非葉子節(jié)點指針扇出值）
• 葉子節(jié)點內(nèi)能容納的record數(shù)量為y（葉子節(jié)點單頁行數(shù)）
• B+樹的層數(shù)為z（樹高）

那這棵B+樹放的行數(shù)據(jù)總量等于 (x ^ (z-1)) * y。

核心公式：單表最大行數(shù) = 非葉節(jié)點扇出指針數(shù) ^ (樹高-1) × 單頁行數(shù)

非葉子節(jié)點指針扇出值—x 怎么算？

我們回去看數(shù)據(jù)頁的結(jié)構(gòu)。

圖片

非葉子節(jié)點里主要放索引查詢相關(guān)的數(shù)據(jù)，放的是主鍵和指向頁號。

• 主鍵假設(shè)是bigint（8Byte），而頁號在源碼里叫FIL_PAGE_OFFSET（4Byte），那么非葉子節(jié)點里的一條數(shù)據(jù)是12Byte左右。
• 整個數(shù)據(jù)頁16k， 頁頭頁尾那部分數(shù)據(jù)全加起來大概128Byte，加上頁目錄毛估占1k吧。那剩下的15k除以12Byte，等于1280，也就是可以指向x=1280頁。

我們常說的二叉樹指的是一個結(jié)點可以發(fā)散出兩個新的結(jié)點。m叉樹一個節(jié)點能指向m個新的結(jié)點。這個指向新節(jié)點的操作就叫扇出（fanout）。而上面的B+樹，它能指向1280個新的節(jié)點，恐怖如斯，可以說扇出非常高了。

單頁行數(shù)—y的計算

葉子節(jié)點和非葉子節(jié)點的數(shù)據(jù)結(jié)構(gòu)是一樣的，所以也假設(shè)剩下15kb可以發(fā)揮。

葉子節(jié)點里放的是真正的行數(shù)據(jù)。假設(shè)一條行數(shù)據(jù)1kb，所以一頁里能放y=15行。

行總數(shù)計算

回到 (x ^ (z-1)) * y 這個公式。

已知x=1280，y=15。

假設(shè)B+樹是兩層，那z=2。則是(1280 ^ (2-1)) * 15 ≈ 2w

假設(shè)B+樹是三層，那z=3。則是(1280 ^ (3-1)) * 15 ≈ 2.5kw

這個2.5kw，就是我們常說的單表建議最大行數(shù)2kw的由來。畢竟再加一層，數(shù)據(jù)就大得有點離譜了。三層數(shù)據(jù)頁對應(yīng)最多三次磁盤IO，也比較合理。

臨界點 ：當行數(shù)突破約2000萬時，樹高可能從3層變?yōu)?層：

• 樹高=4時：最大行數(shù) ≈ 1280^3 × 15 結(jié)果已超過百億（遠大于2000萬）
• 性能斷崖 ：樹高從3→4，查詢I/O次數(shù)從3次增至4次 （多一次磁盤尋址），尤其在回表查詢、高并發(fā)、深分頁時性能驟降。

行數(shù)超一億就慢了嗎？

上面假設(shè)單行數(shù)據(jù)用了1kb，所以一個數(shù)據(jù)頁能放個15行數(shù)據(jù)。

如果我單行數(shù)據(jù)用不了這么多，比如只用了250byte。那么單個數(shù)據(jù)頁能放60行數(shù)據(jù)。

那同樣是三層B+樹，單表支持的行數(shù)就是 (1280 ^ (3-1)) * 60 ≈ 1個億。

你看我一個億的數(shù)據(jù)，其實也就三層B+樹，在這個B+樹里要查到某行數(shù)據(jù)，最多也是三次磁盤IO。所以并不慢。

B樹承載的記錄數(shù)量

我們都知道，現(xiàn)在MySQL的索引都是B+樹，而有一種樹，跟B+樹很像，叫B樹，也叫B-樹。

它跟B+樹最大的區(qū)別在于，B+樹只在末級葉子結(jié)點處放數(shù)據(jù)表行數(shù)據(jù)，而B樹則會在葉子和非葉子結(jié)點上都放。

于是，B樹的結(jié)構(gòu)就類似這樣：

圖片

B樹將行數(shù)據(jù)都存在非葉子節(jié)點上，假設(shè)每個數(shù)據(jù)頁還是16kb，掐頭去尾每頁剩15kb，并且一條數(shù)據(jù)表行數(shù)據(jù)還是占1kb，就算不考慮各種頁指針的情況下，也只能放個15條數(shù)據(jù)。數(shù)據(jù)頁扇出明顯變少了。

計算可承載的總行數(shù)的公式也變成了一個等比數(shù)列。

15 + 15^2 +15^3 + ... + 15^z

其中z還是層數(shù)的意思。

為了能放2kw左右的數(shù)據(jù)，需要z>=6。也就是樹需要有6層，查一次要訪問6個頁。假設(shè)這6個頁并不連續(xù)，為了查詢其中一條數(shù)據(jù)，最壞情況需要進行6次磁盤IO。

而B+樹同樣情況下放2kw數(shù)據(jù)左右，查一次最多是3次磁盤IO。

磁盤IO越多則越慢，這兩者在性能上差距略大。

為此，B+樹比B樹更適合成為MySQL的索引。

五、總結(jié)：生死博弈的核心

B+樹葉子和非葉子結(jié)點的數(shù)據(jù)頁都是16k，且數(shù)據(jù)結(jié)構(gòu)一致，區(qū)別在于葉子節(jié)點放的是真實的行數(shù)據(jù)，而非葉子結(jié)點放的是主鍵和下一個頁的地址。

B+樹一般有兩到三層，由于其高扇出，三層就能支持2kw以上的數(shù)據(jù)，且一次查詢最多1~3次磁盤IO，性能也還行。

存儲同樣量級的數(shù)據(jù)，B樹比B+樹層級更高，因此磁盤IO也更多，所以B+樹更適合成為MySQL索引。

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

單表最大值還受主鍵大小和磁盤大小限制。

16KB頁與B+樹的平衡 ：頁大小限制了單頁行數(shù)和指針數(shù)，B+樹通過多階平衡確保低樹高。

2000萬不是絕對 ：若行小于1KB（如只存ID），上限可到5000萬+；若行較大（如含大字段），可能500萬就性能下降。

優(yōu)化建議：

• 控制單行大?。ū苊釺EXT/BLOB直接入表）。
• 分庫分表：單表接近千萬級時提前規(guī)劃。
• 冷熱分離：歷史數(shù)據(jù)歸檔。

本質(zhì)：通過頁大小和B+樹結(jié)構(gòu)，MySQL在磁盤I/O和內(nèi)存效率之間取得平衡。超出平衡點時，性能從“平緩下降”變?yōu)椤皵嘌孪碌薄?/p>

六、拓展問題

為啥設(shè)計單頁大小16k?

MySQL索引采用的是B+樹數(shù)據(jù)結(jié)構(gòu)，每個葉子節(jié)點（葉子塊）存儲一個索引條目的信息。而MySQL使用的是頁式存儲（Paged storage）技術(shù)，將磁盤上的數(shù)據(jù)劃分為一個個固定大小的頁面，每個頁面包含若干個索引條目。

為了提高索引查詢效率和降低磁盤I/O的頻率，MySQL設(shè)置了16KB的單頁大小。這是因為在MySQL中：

• 內(nèi)存大小限制：MySQL的索引需要放在內(nèi)存中進行查詢，如果頁面過大，將導(dǎo)致索引無法完全加載到內(nèi)存中，從而影響查詢效率。
• 磁盤I/O限制：當需要查詢一個索引時，MySQL需要把相關(guān)的頁面加載到內(nèi)存中進行處理，如果頁面過大，將增加磁盤I/O的開銷，降低查詢效率。
• 索引效率限制：在B+樹數(shù)據(jù)結(jié)構(gòu)中，每個葉子節(jié)點存儲著一個索引條目，因此如果每個頁面能夠存放更多索引條目，就可以減少B+樹結(jié)構(gòu)的深度，從而提高索引查詢效率。

綜上所述，MySQL索引單頁大小設(shè)置為16KB可以兼顧內(nèi)存大小、磁盤I/O和索引查詢效率等多方面因素，是一種比較優(yōu)化的方案。需要注意的是，對于某些特殊的應(yīng)用場景，可能需要根據(jù)實際情況對單頁大小進行調(diào)整。

字符串怎么做索引?

在MySQL中，可以通過B+樹索引結(jié)構(gòu)對字符串類型的列進行排序。具體來說，當使用B+樹索引進行排序時，MySQL會根據(jù)字符串的字典序（Lexicographic Order）進行排序。

字典序是指將字符串中的每個字符依次比較，直到找到不同的字符為止。如果兩個字符串在相同的位置上具有不同的字符，則以這兩個字符的ASCII碼值比較大小，并按照升序或降序排列。例如，字符串"abc"和"def"比較大小時，先比較'a'和'd'的ASCII碼，因為'd'的ASCII碼大于'a'，所以"def"大于"abc"。

需要注意的是，如果對長字符串進行排序，可能會影響索引查詢的性能，因此可以考慮使用前綴索引或全文索引來優(yōu)化。同時，在實際開發(fā)中，還需要注意選擇適當?shù)淖址团判蛞?guī)則，以確保排序結(jié)果正確和穩(wěn)定。

中文字符串怎么做索引?

中文字符串排序在MySQL中可以使用多種方式，最常見的有以下兩種：

• 按拼音排序：對于中文字符串，可以按照拼音進行排序。可以使用拼音排序插件，如pinyin或zhuyin插件，來實現(xiàn)中文字符串按照拼音進行排序。這些插件會將中文字符串轉(zhuǎn)換為拼音或注音后，再進行排序。

例如，先安裝pinyin插件：

INSTALL PLUGIN pinyin SONAME 'ha_pinyin.so';

然后創(chuàng)建對應(yīng)的索引并按拼音排序：

CREATE INDEX idx_name_pinyin ON mytable(name) USING BTREE WITH PARSER pinyin;
SELECT * FROM mytable ORDER BY name COLLATE pinyin;

• 按Unicode碼點排序：可以使用UTF-8字符集，并選擇utf8mb4_unicode_ci排序規(guī)則，在使用此排序規(guī)則時，MySQL會按照Unicode碼點進行排序，適合于較為通用的中文字符串排序需求。

例如：

CREATE INDEX idx_name_unicode ON mytable(name) USING BTREE;
SELECT * FROM mytable ORDER BY name COLLATE utf8mb4_unicode_ci;

需要注意的是，不同的排序方式可能會對性能產(chǎn)生影響，因此需要根據(jù)具體需求選擇合適的排序方式，并進行必要的測試和驗證。同時，在進行中文字符串排序時，還需要考慮到中文字符的復(fù)雜性，例如同音字、繁簡體等問題，以確保排序結(jié)果正確和穩(wěn)定。

索引字段的長度有限制嗎?

在MySQL中，索引的長度通常是由三個因素決定的：數(shù)據(jù)類型、字符集和存儲引擎。不同的數(shù)據(jù)類型、字符集和存儲引擎所支持的最大索引長度也有所不同。

一般情況下，索引的長度不應(yīng)該超過存儲引擎所支持的最大索引長度。在InnoDB存儲引擎中，單個索引所能包含的最大字節(jié)數(shù)為767個字節(jié)（前綴索引除外）。如果索引的長度超過了最大長度，則會導(dǎo)致創(chuàng)建索引失敗。因此，在設(shè)計表結(jié)構(gòu)時，需要根據(jù)索引列的數(shù)據(jù)類型和字符集等因素，合理設(shè)置索引長度，以充分利用索引的優(yōu)勢。

對于字符串類型的索引，還需要注意以下幾點：

• 對于UTF-8字符集，每個字符占用1-4個字節(jié)，因此索引長度需要根據(jù)實際情況進行計算。例如，一個VARCHAR(255)類型的列在utf8mb4字符集下的最大長度為255*4=1020個字節(jié)。
• 可以使用前綴索引來減少索引的大小，提高索引查詢效率。在創(chuàng)建前綴索引時需要指定前綴長度。例如，可以在創(chuàng)建索引時使用name(10)來指定name列的前10個字符作為索引。
• 在使用全文索引對字符串進行搜索時，MySQL會將文本內(nèi)容分割成單個詞匯后建立倒排索引。在建立索引時需要考慮到中英文分詞的問題，以確保全文索引的準確性和查詢效率。

綜上所述，索引的長度需要根據(jù)數(shù)據(jù)類型、字符集和存儲引擎等多個因素進行綜合考慮，并合理設(shè)置索引長度，以提高索引查詢效率和利用率。