一、前言

二、MySQL是如何存儲數據的？

    1. 數據頁（Page）

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

    3. 存入數據如下

    4. 關鍵原理總結

三、MySQL是如何查詢到數據的？

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

    2. 查詢步驟總結

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

    1. B+樹承載的記錄數量

    2. 行數超一億就慢了嗎？

    3. B樹承載的記錄數量

五、總結：生死博弈的核心

六、拓展問題

    1. 為啥設計單頁大小16k?

    2. 字符串怎么做索引?

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

一、前言

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

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

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

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

二、MySQL是如何存儲數據的？

核心結構：B+樹 + 16KB數據頁

這里如下，建一張普通表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;

數據頁（Page）

介紹

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

頁的結構

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

圖片

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

圖片

從頁到索引—B+樹索引

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

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

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

存入數據

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

然后需要插入id=11的數據：

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

關鍵原理總結

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

三、MySQL是如何查詢到數據的？

上面我們已經介紹了MySQL中使用頁存儲數據，以及B+樹索引數據的結構，那現在我們就可以通過這樣一棵B+樹加速查詢。

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

比方說我們想要查找行數據5。會先從頂層頁的record們入手。record里包含了主鍵id和頁號（頁地址）。

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

圖片

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

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

圖片

查詢步驟總結

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

• 從根頁開始搜索 ：

加載根頁（常駐內存）到Buffer Pool，根據指針找到下一層節點。

• 逐層定位葉子節點 ：

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

重復此過程，直到葉子節點頁。

• 葉子節點二分查找 ：

在葉子頁內通過主鍵二分查找定位到行記錄，返回完整數據。

I/O次數分析 ：

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

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

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

B+樹承載的記錄數量

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

• 如果是末級葉子節點的話，那么里面放的就是一行行record數據。
• 如果是非葉子節點，那么就會循環繼續指向新的數據頁。

假設

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

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

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

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

我們回去看數據頁的結構。

圖片

非葉子節點里主要放索引查詢相關的數據，放的是主鍵和指向頁號。

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

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

單頁行數—y的計算

葉子節點和非葉子節點的數據結構是一樣的，所以也假設剩下15kb可以發揮。

葉子節點里放的是真正的行數據。假設一條行數據1kb，所以一頁里能放y=15行。

行總數計算

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

已知x=1280，y=15。

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

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

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

臨界點 ：當行數突破約2000萬時，樹高可能從3層變為4層：

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

行數超一億就慢了嗎？

上面假設單行數據用了1kb，所以一個數據頁能放個15行數據。

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

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

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

B樹承載的記錄數量

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

它跟B+樹最大的區別在于，B+樹只在末級葉子結點處放數據表行數據，而B樹則會在葉子和非葉子結點上都放。

于是，B樹的結構就類似這樣：

圖片

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

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

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

其中z還是層數的意思。

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

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

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

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

五、總結：生死博弈的核心

B+樹葉子和非葉子結點的數據頁都是16k，且數據結構一致，區別在于葉子節點放的是真實的行數據，而非葉子結點放的是主鍵和下一個頁的地址。

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

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

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

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

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

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

優化建議：

• 控制單行大小（避免TEXT/BLOB直接入表）。
• 分庫分表：單表接近千萬級時提前規劃。
• 冷熱分離：歷史數據歸檔。

本質：通過頁大小和B+樹結構，MySQL在磁盤I/O和內存效率之間取得平衡。超出平衡點時，性能從“平緩下降”變為“斷崖下跌”。

六、拓展問題

為啥設計單頁大小16k?

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

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

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

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

字符串怎么做索引?

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

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

需要注意的是，如果對長字符串進行排序，可能會影響索引查詢的性能，因此可以考慮使用前綴索引或全文索引來優化。同時，在實際開發中，還需要注意選擇適當的字符集和排序規則，以確保排序結果正確和穩定。

中文字符串怎么做索引?

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

• 按拼音排序：對于中文字符串，可以按照拼音進行排序?？梢允褂闷匆襞判虿寮?，如pinyin或zhuyin插件，來實現中文字符串按照拼音進行排序。這些插件會將中文字符串轉換為拼音或注音后，再進行排序。

例如，先安裝pinyin插件：

INSTALL PLUGIN pinyin SONAME 'ha_pinyin.so';

然后創建對應的索引并按拼音排序：

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排序規則，在使用此排序規則時，MySQL會按照Unicode碼點進行排序，適合于較為通用的中文字符串排序需求。

例如：

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

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

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

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

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

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

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

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