一、前 言

本文核心介绍，为何业界会有这样的说法？—— “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 会将文本内容分割成单个词汇后建立倒排索引。在建立索引时需要考虑到中英文分词的问题，以确保全文索引的准确性和查询效率。

综上所述，索引的长度需要根据数据类型、字符集和存储引擎等多个因素进行综合考虑，并合理设置索引长度，以提高索引查询效率和利用率。

往期回顾

1. 0 基础带你精通 Java 对象序列化--以 Hessian 为例｜得物技术

2. 前端日志回捞系统的性能优化实践｜得物技术

3. 得物灵犀搜索推荐词分发平台演进 3.0

4. R8 疑难杂症分析实战：外联优化设计缺陷引起的崩溃｜得物技术

5. 可扩展系统设计的黄金法则与 Go 语言实践｜得物技术

文 / 太空

关注得物技术，每周更新技术干货

要是觉得文章对你有帮助的话，欢迎评论转发点赞～

未经得物技术许可严禁转载，否则依法追究法律责任。