首页 Mysql 滴滴面试：单表可以存200亿数据吗？单表真的只能存2000W，为什么？

滴滴面试：单表可以存200亿数据吗？单表真的只能存2000W，为什么？

Author: 菜菜 · 分类： Mysql ·    2024年11月 · tags:

问题1：在实际生产环境中，InnoDB 中一棵 B+ 树索引一般有多少层？

问题2：在实际生产环境中，InnoDB一棵B+树可以存放多少行数据？

问题3：MySQL 对于千万级的大表，为啥要优化？

问题4：mysql 单表最好不要超过2000w？

问题5：单表超过2000w 就要考虑数据迁移了，这个是为啥？

问题6：你这个表数据都马上要到2000w 了，难怪查询速度慢，为什么？

问题7：单表能存200亿数据吗？大家都说，单表只存2000W，为什么？

问题8：单表能存200亿数据吗？单表只能存2000W是真的吗，为什么？

问题N： … 第100个变种

最近有小伙伴面试网易，都问到了相关的面试题。小伙伴没有系统的去梳理和总结，所以支支吾吾的说了几句，面试官不满意，面试挂了。

所以，尼恩给大家做一下系统化、体系化的梳理，使得大家内力猛增，可以充分展示一下大家雄厚的 “技术肌肉”，让面试官爱到 “不能自已、口水直流”，然后实现”offer直提”。

当然，这道面试题，以及参考答案，也会收入咱们的 《尼恩Java面试宝典PDF》V175版本，供后面的小伙伴参考，提升大家的 3高 架构、设计、开发水平。

《尼恩 架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》的PDF，请到文末公号【技术自由圈】获取

本文目录

– 尼恩说在前面

– 首先，告诉面试官一个惊世骇俗的答案， 是可以的。

– 接下来，说说 InnoDB 索引数据结构的磁盘文件格式

– 回到核心问题： 一棵B+树可以存放多少行数据？

 – 高度为2的B+树，可以存放多少行数据?

    –通过简单的公式进行 记录数的估算：

 – 高度为3的B+树可以存放多少行数据？

 – 高度为4的B+树可以存放多少行数据？

– 如何通过元数据，找到InnoDB主键索引B+树的高度？

 – 第一步：通过 mysql 元数据 找到 主键索引 的 根页

 – 第二步：通过 主键索引 的 根页，找到树的高度

– InnoDB一棵B+树的IO次数

– InnoDB一棵B+树的查找流程

 – 执行一次 聚集索引B+树的io次数

 – 执行一次 非聚集索引B+树的io次数

– mysql 一次磁盘io 的耗时为多少?

 – 磁盘 I/O 耗时的影响因素

 – MySQL 中的磁盘 I/O 操作和大致耗时范围

 – InnoDB一棵B+树的查找耗时

– mysql  Buffer Pool 缓冲池对  B+树 访问的加速

 – Buffer Pool  对 B + 树的 索引页和数据页的缓存和加速

 – Buffer Pool 的大小设置

 – 为啥阿里编程规范推荐 inno DB单表记录2kw ？

 – 附录：表空间文件的 基础知识

– 尼恩架构团队的塔尖 sql 面试题

– 说在最后：有问题找老架构取经‍

首先，告诉面试官一个惊世骇俗的答案， 是可以的。

尼恩偷偷的告诉大家， 尼恩在辅导1000多人写简历的过程中， 发现 很多人的单表里边的数据， 都超过了一个亿， 甚至上十个亿。

一点性能问题都没有。

接下来，给面试官做一下 抽丝剥茧的分析， 分析完了， 面试就过了。

接下来，说说 InnoDB 索引数据结构的磁盘文件格式

磁盘扇区、文件系统、InnoDB 存储引擎都有各自的最小存储单元。

来看看三个重要的最小单元

  • 磁盘上，存储数据最小单元是扇区，一个扇区的大小是 512 字节，

  • 文件系统（例如EXT4）,最小单元是块 （block），一个block 块的大小是 4k，

  • InnoDB 存储引擎 的最小储存单元——页（Page），一个页的大小是 16K。

来一个图，更清楚：

由于文件系统（例如EXT4）的最小单元是块 （block），一个block 块的大小是 4k。所以，假设一个文件大小只有1个字节，那么，这个文件在磁盘上，还是不得不占4KB的空间。

具体如下图：

要知道，Innodb 的所有数据文件（后缀为 ibd 的文件），也是存储在磁盘的，当然也是由block组成，

所以，Innodb 的所有数据文件，全部都是 16384（16k）的整数倍。

具体如下图：

InnoDB 存储引擎 的最小储存单元——页（Page），一个页的大小是 16K，

在 MySQL 中我们的InnoDB 页的大小当然也可以通过参数设置的，具体如下图：

通过上图，可以看到，在 MySQL 中我们的 InnoDB 页的大小默认是 16k

回到核心问题： 一棵B+树可以存放多少行数据？

那么回到我们开始的问题，通常一棵B+树可以存放多少行数据？

高度为2的B+树，可以存放多少行数据?

首先，需要计算出非叶子节点能存放多少指针？

页作为 InnoDB 磁盘管理的最小单位，不仅可以用来存放具体的行数据，还可以存放键值和指针。

回到文题，我们先从简单的入手，

这里我们先假设B+树高为2，即存在一个根节点和若干个叶子节点，那么 B+ 树只有两层。

如下图：

这B+树的存放总记录数为, 是一个简单的公式：

记录总数 = 非叶子节点存放的page 指针数 * 每个叶子节点存放的行记录数

非叶子节点里面存的是主键值 + 指针

为了方便分析，这里我们把一个主键值 + 一个指针称为一个单元，

  • 我们假设主键的类型是 BigInt，长度为 8 字节，

  • 而指针大小在 InnoDB 中设置为 6 字节，

这样一个单元，一共 14 字节。

这样的话，一页或者说一个非叶子节点能够存放 16384 / 14=1170 个这样的单元。

也就是说一个非叶子节点中能够存放 1170 个指针，即对应 1170 个叶子节点，

叶子节点里面存的是 数据行记录

每个叶子节点存放的行记录数就是每页存放的记录数，由于各个数据表中的字段数量都不一样，这里我们就不深究叶子节点的存储结构了，

实际上现在很多互联网业务数据记录大小通常就是 1K 左右， 简单按照一行记录的数据大小为 1k 来算的话，

一页（16K）或者说一个叶子节点可以存放 16 行这样的数据。

通过简单的公式进行 记录数的估算：

记录总数 = 非叶子节点存放的page 指针数 * 每个叶子节点存放的行记录数

那么 ，这颗2层B+ 树 的非叶子节点（ 唯一的）能够存储多少数据呢？

所以对于这样一棵高度为 2 的 B+ 树，根节点能存放 1170（一个非叶子节点中的指针数） * 16（一个叶子节点中的行数）= 18720 行数据。

尼恩提示，这样分析其实不是很严谨，

为啥呢 ？ InnoDB 数据页结构，不全是 主键值 + 一个指针，还有其他的一些 元数据。按照 《MySQL 技术内幕：InnoDB 存储引擎》中的定义，InnoDB 数据页结构包含如下几个部分：

但是咱们这里主要是 估算树的高度和 一颗2层的B+树的大概 容量， 所以就把 他的结构简化理解吧。

高度为3的B+树可以存放多少行数据？

分析完高度为 2 的 B+ 树，同样的道理，我们来看高度为 3 的：

非叶子节点里面存的是主键值 + 指针

为了方便分析，这里我们把一个主键值 + 一个指针称为一个单元，

  • 第一层、第二层 都是 非叶节点（索引页）， 用来存储  key + 页指针

  • 我们假设主键的类型是 BigInt，长度为 8 字节，

  • 而指针大小在 InnoDB 中设置为 6 字节

  • 第三层是 叶子， 存储数据 ,是 数据页

这样的话：

  • 第一层 根页（page10）可以存放 1170 个指针，有 1170 个二层page

  • 第二层的每个页 也都分别可以存放1170个指针，有 1170 * 1170 个 三层 page

叶子节点里面存的是 数据行记录

每条记录 简单按照一行记录的数据大小为 1k 来算的话，一页（16K）或者说一个叶子节点可以存放 16 行这样的数据。

这样， 3层的一共可以存放 1170 * 1170 个指针，即对应的有 1170 * 1170 个叶子节点，每一个叶子节点 可以放大概 16条 record。

所以，高为3的B+树一共可以存放 1170 * 1170 * 16 = 21902400 行记录。

回到问题，InnoDB 一棵 B+ 树可以存放多少行数据？ 这个问题的简单回答是：约 2 千万。

高度为4的B+树可以存放多少行数据？

非叶子节点里面存的是主键值 + 指针

为了方便分析，这里我们把一个主键值 + 一个指针称为一个单元，

  • 第一层、第二层、第三层 都是 非叶节点（索引页）， 用来存储  key + 页指针

  • 高为3的B+树一共可以存放 1170 * 1170 * * 1170 个 叶子节点 。

叶子节点里面存的是 数据行记录

每条记录 简单按照一行记录的数据大小为 1k 来算的话，一页（16K）或者说一个叶子节点可以存放 16 行这样的数据。

所以，高为4的B+树一共可以存放 1170 * 1170 * 1170 * 16 = 25,625,808,000 （约200亿） 行记录。

回到问题，InnoDB 一棵 B+ 树可以存放多少行数据？ 这个问题的简单回答是：200亿。

如何通过元数据，找到InnoDB主键索引B+树的高度？

在InnoDB 引擎中，实际的情况如何呢？

在InnoDB的表空间文件中，约定page number为3的代表主键索引的根页，而在根页偏移量为64的地方存放了该B+树的page level。

第一步：通过 mysql 元数据 找到 主键索引 的 根页

首先我们通过 mysql 元数据 找到 主键索引 的 根页的 page number 为3

如果page level为1，树高为2，page level为2，则树高为3。

即B+树的高度=page level+1；

下面我们将从实际环境中尝试找到这个page level。

实验环境中，下面这三张表（一个表30W，一个表 3W，一个表3行数据） 如下：


mysql> select count(*) from book ;
+----------+
| count(*) |
+----------+
| 312221 |
+----------+
1 row in set (0.07 sec)
mysql> select count(*) from biz_user ;
+----------+
| count(*) |
+----------+
| 3570 |
+----------+
1 row in set (0.02 sec)
mysql> select count(*) from biz_patient_pdf_stamp_position ;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.02 sec)

从图中可以看到，一个表30W，一个表 3W，一个表3行数据。

在实际操作之前，可以通过InnoDB 元数据表 ， 确认主键索引根页的page number为3，当然，也可以从《InnoDB存储引擎》这本书中得到确认。


mysql> SELECT
b.NAME,
a.NAME,
index_id,
type,
a.space,
a.PAGE_NO
FROM
information_schema.INNODB_SYS_INDEXES a,
information_schema.INNODB_SYS_TABLES b
WHERE
a.table_id = b.table_id
AND a.space <> 0;

说明： information_schema是mysql自带的一个 元数据表，其保存着关于mysql服务器所维护的所有其他数据库的信息，如数据库名，数据库的表，表栏的数据类型与访问权限等。

  • innodb_sys_indexes：innodb表的索引 信息

  • innodb_sys_tables：表格 存储特性，包括行格式，压缩页面大小位级别的信息

执行结果：


mysql> SELECT
b.NAME,
a.NAME,
index_id,
type,
a.space,
a.PAGE_NO
FROM
information_schema.INNODB_SYS_INDEXES a,
information_schema.INNODB_SYS_TABLES b
WHERE
a.table_id = b.table_id
AND a.space <> 0;
+------------------------------------+-------------+----------+------+-------+---------+
| NAME | NAME | index_id | type | space | PAGE_NO |
+------------------------------------+-------------+----------+------+-------+---------+
| iam/biz_organization | PRIMARY | 116 | 3 | 95 | 3 |
| iam/biz_patient_pdf_stamp_position | PRIMARY | 117 | 3 | 96 | 3 |
| iam/biz_patient_sign_pdf | PRIMARY | 118 | 3 | 97 | 3 |
| iam/biz_patient_sign_pdf_details | PRIMARY | 119 | 3 | 98 | 3 |
| iam/biz_signed_pdf | PRIMARY | 120 | 3 | 99 | 3 |
| iam/biz_signed_pdf_details | PRIMARY | 121 | 3 | 100 | 3 |
| iam/biz_sys_info | PRIMARY | 122 | 3 | 101 | 3 |
| iam/biz_ukey_login | PRIMARY | 123 | 3 | 102 | 3 |
| iam/biz_ukey_login_details | PRIMARY | 124 | 3 | 103 | 3 |
| iam/biz_ukey_sign | PRIMARY | 125 | 3 | 104 | 3 |
| iam/biz_ukey_sign_details | PRIMARY | 126 | 3 | 105 | 3 |
| iam/biz_ukey_signed_pdf | PRIMARY | 127 | 3 | 106 | 3 |
| iam/biz_ukey_signed_pdf_details | PRIMARY | 128 | 3 | 107 | 3 |
| iam/biz_user | PRIMARY | 129 | 3 | 108 | 3 |
| iam/biz_user | mobile | 130 | 0 | 108 | 4 |
| iam/biz_user | authon_mark | 131 | 0 | 108 | 5 |
| iam/biz_user_employee_num | PRIMARY | 230 | 3 | 188 | 3 |
| iam/biz_user_employee_num | biz_num | 231 | 2 | 188 | 4 |
| iam/book | PRIMARY | 235 | 3 | 197 | 3 |
| iam/book | index_uer_id| 236 | 0 | 197 | 4 |

可以看出主键索引（PRIMARY）根页的page number均为3，而其他的二级索引page number(PAGE_NO)为4还有5。

B+树的高度通常是1-3;

可以通过InnoDB元数据表，我们已经确认主键索引 的 根页的 page number 为3。

第二步：通过 主键索引 的 根页，找到树的高度

在InnoDB的表空间文件中，约定 page number 为3的代表主键索引的根页 （root page ），而在根页偏移量为 64 的地方存放了该B+树的page level。

我们需要找到这个page level。

如果根页偏移量为 64 的地方的 page level为1，树高为2，page level为2，则树高为3。

B+树的高度=page level+1；

索引树高度决定查询的IO次数，当然树高度越大则查询需要的IO次数就越多，查询效率相对来说就越低！

下面我们对数据库表空间文件做 相关的解析：


[root@localhost iam]# ls -l *.ibd
-rw-r-----. 1 mysql mysql 98304 10月 30 15:12 biz_h5_sign_details.ibd
-rw-r-----. 1 mysql mysql 98304 10月 30 15:12 biz_h5_sign.ibd
-rw-r-----. 1 mysql mysql 98304 10月 30 15:12 biz_organization.ibd
-rw-r-----. 1 mysql mysql 98304 10月 30 15:12 biz_patient_pdf_stamp_position.ibd
-rw-r-----. 1 mysql mysql 9437184 10月 30 15:12 biz_patient_sign_pdf_details.ibd
-rw-r-----. 1 mysql mysql 98304 10月 30 15:12 biz_patient_sign_pdf.ibd
-rw-r-----. 1 mysql mysql 131072 10月 30 15:12 biz_signed_pdf_details.ibd
-rw-r-----. 1 mysql mysql 98304 10月 30 15:12 biz_signed_pdf.ibd
-rw-r-----. 1 mysql mysql 98304 10月 30 15:12 biz_sys_info.ibd
-rw-r-----. 1 mysql mysql 98304 10月 30 15:12 biz_ukey_login_details.ibd
-rw-r-----. 1 mysql mysql 98304 10月 30 15:12 biz_ukey_login.ibd
-rw-r-----. 1 mysql mysql 98304 10月 30 15:12 biz_ukey_sign_details.ibd
-rw-r-----. 1 mysql mysql 98304 10月 30 15:12 biz_ukey_signed_pdf_details.ibd
-rw-r-----. 1 mysql mysql 98304 10月 30 15:12 biz_ukey_signed_pdf.ibd
-rw-r-----. 1 mysql mysql 98304 10月 30 15:12 biz_ukey_sign.ibd
-rw-r-----. 1 mysql mysql 9437184 10月 30 15:12 biz_user_copy1.ibd
-rw-r-----. 1 mysql mysql 9437184 10月 30 15:12 biz_user_copy2.ibd
-rw-r-----. 1 mysql mysql 9437184 10月 30 15:12 biz_user_copy3.ibd
-rw-r-----. 1 mysql mysql 114688 11月 23 15:12 biz_user_employee_num.ibd
-rw-r-----. 1 mysql mysql 9437184 10月 30 15:12 biz_user.ibd
-rw-r-----. 1 mysql mysql 125829120 1月 26 09:19 book.ibd

因为主键索引B+树的根页在整个表空间文件中的第3个页开始，所以可以算出它在文件中的偏移量：16384*3=49152（16384为页大小 16KB）。

根页的64偏移量位置前2个字节，保存了page level的值，因此我们想要的page level的值在整个文件中的偏移量为：16384*3+64=49152+64=49216，前2个字节中。

接下来我们用hexdump工具，查看表空间文件指定偏移量上的数据：

hexdump 是一个用于查看文件或其他输入流的十六进制和 ASCII 码表示的工具。它能够以十六进制字节序列的形式显示文件内容，并且在旁边同时展示对应的 ASCII 字符（如果字符是可打印的），方便用户查看文件的二进制数据结构。

hexdump 基本功能：当你有一个二进制文件，想要查看其内部的数据布局时，hexdump 可以将文件内容逐字节地以十六进制形式展示出来。

假设我们有一个简单的文件，其中存储了字符序列 “ABC”，使用 hexdump 查看时，会显示出每个字符对应的 ASCII 码值（在十六进制下，A 是 41，B 是 42，C 是 43）以及对应的 ASCII 字符，这样就可以直观地看到文件的内容在二进制层面的表示。

用hexdump工具 分别查看book、biz_user、biz_patient_pdf_stamp_position三张表的ibd表空间文件


[root@localhost iam]# hexdump -s 49216 -n 10 book.ibd
000c040 0200 0000 0000 0000 eb00
000c04a
[root@localhost iam]# hexdump -s 49216 -n 10 biz_user.ibd
000c040 0100 0000 0000 0000 8100
000c04a
[root@localhost iam]# hexdump -s 49216 -n 10 biz_patient_pdf_stamp_position.ibd
000c040 0000 0000 0000 0000 7500
000c04a
[root@localhost iam]#

book 表的page level为2，B+树高度为page level+1=3； biz_user 表的page level为1，B+树高度为page level+1=2； biz_patient_pdf_stamp_position 表的page level为0，B+树高度为page level+1=1；

InnoDB一棵B+树的IO次数

数据表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢？

InnoDB存储引擎的最小存储单元是页，页可以用于存放数据（叶子），也可以用于存放键值+指针（非叶子）， 用B+树的方式组织这些数据

  • B+ 树的叶子节点存储真正的记录，对应的文件系统 page页面，可以叫做 数据页。假设一行数据的大小是1k，那么一个16K页，一个数据页 可以存放16行这样的数据。

  • B+ 树的非叶子节点存放的是键值 + 指针，其对应的文件系统 page页面，可以叫做 索引页

注意：查询数据时，每加载一页（page）代表一次IO，

索引树高度决定查询的IO次数，当然树高度越大则查询需要的IO次数就越多，查询效率相对来说就越低！

索引树高度与 IO 次数的关系

  • 树高度为 1 的情况（极端简单情况）：如果索引树高度为 1，这意味着索引数据可能全部存储在一个数据页中（假设只有根节点）。查询时，只需要一次磁盘 I/O 操作将这个数据页读取到内存中，就可以获取到所需的索引信息，进而找到对应的行记录，这种情况下查询效率很高。

  • 树高度为 2 的情况：当树高度为 2 时，首先需要一次磁盘 I/O 操作读取根节点到内存，然后根据根节点中的指针信息，再进行一次磁盘 I/O 操作读取叶子节点到内存，总共需要两次磁盘 I/O 操作来获取索引数据。

  • 树高度为 3 及以上的情况：随着树高度的增加，每增加一层，查询就需要多进行一次磁盘 I/O 操作。例如，树高度为 3 时，可能需要先读取根节点，再读取中间节点，最后读取叶子节点，总共需要三次磁盘 I/O 操作。

InnoDB一棵B+树的查找流程

InnoDB中主键索引B+树是如何组织数据、查询数据的？

我们总结一下：

1、在B+树中叶子节点存放数据，非叶子节点存放键值+指针。

2、页内的记录是有序的，所以可以使用二分查找在页内到下一层的目标页面的指针

  • 从根页开始，首先通过非叶子节点的二分查找法，

  • 确定数据在下一层哪个页之后，一层一层往下找，一直到 叶子节点，

  • 进而在 叶子节（数据页）中查找到需要的数据；

执行一次 聚集索引B+树的io次数

前面分析了，假设主键ID为 bigint 类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节。

那么一个索引页 能存放多少这样的组合，就代表有多少指针，即 16384 / 14 = 1170。

那么可以算出一棵高度为2 的B+树，能存放 1170 * 16 = 18720 条这样的数据记录。

同理：高度为3的B+树可以存放的行数 = 1170 * 1170 * 16 = 21902400

所以，千万级的数据存储只需要约3层B+树，所以说，根据主键id索引查询约3次IO便可以找到目标结果。

注意：查询数据时，每加载一页（page）代表一次IO，

那么，在不考虑缓存的情况下， 千万级的数据存储只需要约3层B+树 ， 需要3次IO。

同理，在不考虑缓存的情况下， 200亿级的数据存储只需要约4层B+树 ， 需要4次IO。

也就是说， 200亿规模的数据， 和 2000万规模的数据查询，也就多了一次IO，稍后我们看看， 一次IO会耗费多少时间。

执行一次 非聚集索引B+树的io次数

回顾：主键索引和二级索引 一下，什么是 非聚集索引。

什么是主键索引(Primary Key)?

数据表的主键列使用的就是主键索引。一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

什么是二级索引(辅助索引)?

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。常用的二级索引包括：

  1. 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。

  2. 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。

  3. 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。

对于一些复杂的查询，可能需要走二级索引，那么通过二级索引查找记录最多需要花费多少次IO呢？

首先，从二级索引B+树中，根据 name  找到对应的主键id

然后，再回表， 根据主键id 从 聚簇索引查找到对应的记录。 上面分析了， 这里也是 3次IO。

总结：

2000w记录，二级索引有3层，聚簇索引有3层，那么最多花费的IO次数是：3+3 = 6

同理：

200已记录，二级索引有4层，聚簇索引有4层，那么最多花费的IO次数是：4+4 = 8

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。

如果连这样的索引没有，InnoDB 会隐式定义一个主键来作为聚簇索引。

这也是为什么InnoDB表必须有主键，并且推荐使用整型的自增主键！InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上

为啥磁盘IO的性能低？ 不多说啦，具体请参考 尼恩的《葵花宝典：Java 高性能超底层原理》 视频和讲义

mysql 一次磁盘io 的耗时为多少?

磁盘 I/O 耗时的影响因素

  • 磁盘类型：

    • 机械硬盘（HDD）：机械硬盘的读写速度相对较慢。其寻道时间（磁头移动到指定磁道的时间）通常在几毫秒到十几毫秒之间。例如，一般 7200 转 / 分钟的机械硬盘，平均寻道时间约为 9 毫秒左右。数据传输率方面，顺序读取速度可能在 100 – 200MB/s 左右，随机读取速度则会因为寻道时间的影响而大幅降低。一次磁盘 I/O 操作如果是随机小数据量的读取，加上寻道时间和数据传输时间，可能会达到 10 – 20 毫秒左右。

    • 固态硬盘（SSD）：固态硬盘的性能要比机械硬盘好很多。其顺序读取速度可以达到几 GB/s，随机读取速度也能达到几十 MB/s 甚至更高。SSD 没有机械部件，寻道时间可以忽略不计。一般来说，一次简单的磁盘 I/O 操作（如读取一个 4KB 的数据页）在 SSD 上可能只需要 0.1 – 0.2 毫秒左右。

  • I/O 负载和系统环境：

    • 当系统中有多个进程同时竞争磁盘 I/O 资源时，会导致磁盘 I/O 排队，从而增加单次 I/O 的耗时。例如，在一个高并发的数据库服务器上，如果同时有大量的查询和写入操作，磁盘 I/O 队列可能会很长，单次磁盘 I/O 操作的等待时间可能会从几毫秒增加到几十毫秒甚至更多。

    • 另外，磁盘 I/O 控制器、磁盘接口（如 SATA、NVMe 等）的性能以及操作系统的磁盘缓存策略等因素也会对单次磁盘 I/O 耗时产生影响。例如，良好的磁盘缓存策略可以减少实际的磁盘 I/O 操作，从而降低 I/O 耗时。

MySQL 中的磁盘 I/O 操作和大致耗时范围

  • 在 MySQL 中，数据存储和读取主要是以页为单位。InnoDB 存储引擎默认的页大小是 16KB。

  • 机械硬盘场景：如果是在机械硬盘环境下，读取一个 16KB 的数据页，加上寻道时间、旋转延迟（磁头等待数据所在扇区旋转到下方的时间）和数据传输时间，一次磁盘 I/O 操作可能在 10 – 20 毫秒左右。如果是写入操作，由于还涉及到数据校验、日志记录等额外操作，耗时可能会更长，可能达到 20 – 30 毫秒左右。

  • 固态硬盘场景：对于固态硬盘，读取一个 16KB 的数据页可能只需要 0.1 – 0.2 毫秒左右，写入操作可能也在 0.2 – 0.5 毫秒左右，具体取决于 SSD 的性能和写入策略（如是否有写入缓存等）。

InnoDB一棵B+树的查找耗时

一图胜过千言万语。

尼恩给大画了一张图，进行了一下 聚族索引、非聚族索引 、固态硬盘、机械硬盘下的 B+树的查找耗时对比， 具体如下:

这是一张全网最全的图， 从这张图可以看出 ， 其实一个 表，超过 2000w数据， 速度也没有慢太多。尤其 在SSD场景下， 也就 多 0.2ms 到 0.4ms ，可以说是微乎其微。

所以结论是：在SSD 硬盘的场景下， 一个表完全可以放入 10亿-100亿 规模的数据，此时 B+树的层数是 4层。

mysql Buffer Pool 缓冲池对 B+树 访问的加速

在 MySQL 的 InnoDB 存储引擎中，Buffer Pool 是一块内存区域，用于缓存从磁盘读取的数据页和索引页。它是提高数据库性能的关键组件，通过减少磁盘 I/O 操作来加快数据的访问速度。

在 MySQL 的 InnoDB 存储引擎中，数据是以页（Page）为单位存储在磁盘上的，索引结构（B + 树）也是基于页来构建的。缓存主要是通过缓冲池（Buffer Pool）来实现的，缓冲池用于缓存数据页和索引页，以减少磁盘 I/O 操作，提高数据库性能。

Buffer Pool 作用原理：当数据库需要读取数据或索引页时，首先会在 Buffer Pool 中查找。如果 Buffer Pool 中已经缓存了所需的页（称为缓存命中），就可以直接从内存中获取，避免了相对较慢的磁盘 I/O 操作；如果没有找到（缓存未命中），则从磁盘读取该页，并将其放入 Buffer Pool 中，以便后续的访问能够利用缓存。

Buffer Pool 的内部结构，大致如下：

Buffer Pool 对 B + 树的 索引页和数据页的缓存和加速

对于 B + 树结构的索引，并没有严格规定缓存 “几层” 的概念，而是以页为单位进行缓存。

缓冲池中的页可能包含 B + 树索引的根节点、中间节点以及叶子节点。

缓冲池 和对 B + 树的影响

对 B + 树索引的缓存：

  • 根节点页：由于根节点是索引树访问的起始点，经常会被访问到。在系统运行过程中，根节点所在的页很可能会被缓存到缓冲池中。这样，在进行索引查询时，能快速定位到根节点，减少磁盘 I/O。

  • 中间节点页：随着查询操作的进行，中间节点也会被频繁访问。如果缓冲池足够大，这些中间节点所在的页也会被缓存。这有助于在索引遍历过程中，快速从一个中间节点跳转到下一个相关的中间节点，而不需要每次都从磁盘读取。

  • 叶子节点页：叶子节点包含了实际的数据记录（在聚簇索引中）或者指向数据记录的指针（在二级索引中）。叶子节点页的缓存对于数据的读取至关重要。当进行数据查询时，最终是要从叶子节点获取数据的，所以叶子节点页也会被缓存到缓冲池中，以加快数据访问速度。

Buffer Pool 的大小设置

种常见的初步估算方法是将服务器内存的 70% – 80% 分配给 Buffer Pool。

例如，在一台 64GB 内存的服务器上，初步可以将 Buffer Pool 大小设置为 45GB – 51GB 左右。

但这只是一个经验性的建议，实际设置还需要根据上述考虑因素进行调整。

Buffer Pool 越大， 如果能在内存中命中数据页和 索引页， 那么磁盘IO的次数就越少， 性能就越高。

为啥阿里编程规范推荐 inno DB单表记录2kw ？

通过上面的分析可以看出， 如果2000W记录，在没有命中Buffer Pool 情况下， 走 非聚集索引查询， 需要6次IO，走 聚焦索引查询，需要3次磁盘IO

当然，以上分析流程，忽略了一些性能的优化措施，比如 B+树根节点 常驻内存，还有可能命中 查询缓存等等。

所以，阿里编程规范中， innodb 单表推荐2kw 记录，超过了这个值可能会导致B+树层级有3层变成4层，影响查询性能，推荐进行 分库分表。

实际上， 如果我们的机器性能高， 一个表的记录完全可以达到 100亿级别， 虽然B+变成4层， 但是其实 DB操作的性能任然很高。

当然，凡事看场景。

的单表记录数最大值，受到硬件条件，和各种优化措施的影响。

不过，如果 能给面试官分析到这里， 这一次 面试官也就口水直流、五体投地了。

附录：表空间文件的 基础知识

从物理意义上来讲，InnoDB表由共享表空间文件（ibdata1）、独占表空间文件（ibd）、表结构文件（.frm）、以及日志文件（redo文件等）组成。

1、表结构文件

在MYSQL中建立任何一张数据表，在其数据目录对应的数据库目录下都有对应表的.frm文件

.frm文件是用来保存每个数据表的元数据(meta)信息，包括表结构的定义等，

.frm文件跟数据库存储引擎无关，也就是任何存储引擎的数据表都必须有.frm文件，

命名方式为数据表名.frm，如user.frm. ， .frm文件可以用来在数据库崩溃时恢复表结构。

2、表空间文件

（1）表空间结构分析

以下为InnoDB的表空间结构图：

来个清晰点的图

数据段即B+树的叶子节点，索引段即为B+树的非叶子节点InnoDB存储引擎的管理是由引擎本身完成的，

表空间（Tablespace）是由分散的段(Segment)组成。一个段(Segment)包含多个区（Extent）。

区（Extent）由64个连续的页（Page）组成，每个页大小为16K，即每个区大小为1MB，创建新表时，先使用32页大小的碎片页存放数据，使用完后才是区的申请（InnoDB最多每次申请4个区，保证数据的顺序性能） 页类型有：数据页、Undo页、系统页、事务数据页、插入缓冲位图页、以及插入缓冲空闲列表页。

（2）独占表空间文件

若将innodb_file_per_table设置为on，则系统将为每一个表单独的生成一个table_name.ibd的文件，

在此文件中，存储与该表相关的数据、索引、表的内部数据字典信息。

（3）共享表空间文件

在InnoDB存储引擎中，默认表空间文件是ibdata1（主要存储的是共享表空间数据），初始化为10M，且可以扩展，如下图所示：

实际上，InnoDB的表空间文件是可以修改的，使用以下语句就可以修改：


Innodb_data_file_path=ibdata1:370M；ibdata2:50M:autoextend

使用共享表空间存储方式时，Innodb的所有数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。

从Innodb的官方文档中可以看到，其表空间的最大限制为64TB，也就是说，Innodb的单表限制基本上也在64TB左右了，当然这个大小是包括这个表的所有索引等其他相关数据。

而在使用单独表空间存储方式时，每个表的数据以一个单独的文件来存放，这个时候的单表限制，又变成文件系统的大小限制了。

3