尼恩说在前面
在40岁老架构师 尼恩的读者交流群(50+)中，最近有小伙伴拿到了一线互联网企业如得物、阿里、滴滴、极兔、有赞、希音、百度、网易、美团、蚂蚁、得物的面试资格，遇到很多很重要的相关面试题：
问题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，请到文末公号【技术自由圈】获取
首先，告诉面试官一个惊世骇俗的答案， 是可以的。
尼恩偷偷的告诉大家， 尼恩在辅导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 的自增主键。
什么是二级索引(辅助索引)?
二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。常用的二级索引包括：
- 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
- 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
- 前缀索引(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左右了，当然这个大小是包括这个表的所有索引等其他相关数据。
而在使用单独表空间存储方式时，每个表的数据以一个单独的文件来存放，这个时候的单表限制，又变成文件系统的大小限制了。
尼恩架构团队的塔尖 sql 面试题
- sql查询语句的执行流程：
- 索引的底层原理
阿里面试：为什么要索引？什么是MySQL索引？底层结构是什么？
- 索引下推 ?
- 索引失效
美团面试：mysql 索引失效？怎么解决？（重点知识，建议收藏，读10遍+）
- MVCC
- binlog、redolog、undo log
美团面试：binlog、redolog、undo log底层原理是啥？分别实现ACID哪个特性？（尼恩图解，史上最全）
- mysql 事务
京东面试：RR隔离mysql如何实现？什么情况RR不能解决幻读？
说在最后：有问题找老架构取经
只要按照上面的 流程去作答， 你的答案不是 100分，而是 120分。 面试官一定是 心满意足， 五体投地。
按照尼恩的梳理，进行 深度回答，可以充分展示一下大家雄厚的 “技术肌肉”，让面试官爱到 “不能自已、口水直流”，然后实现”offer直提”。
在面试之前，建议大家系统化的刷一波 5000页《尼恩Java面试宝典PDF》，里边有大量的大厂真题、面试难题、架构难题。
很多小伙伴刷完后， 吊打面试官， 大厂横着走。
在刷题过程中，如果有啥问题，大家可以来 找 40岁老架构师尼恩交流。
另外，如果没有面试机会， 可以找尼恩来改简历、做帮扶。前段时间，刚指导一个27岁 被裁小伙，拿到了一个年薪45W的JD +PDD offer，逆天改命。
狠狠卷，实现 “offer自由” 很容易的， 前段时间一个武汉的跟着尼恩卷了2年的小伙伴， 在极度严寒/痛苦被裁的环境下， offer拿到手软， 实现真正的 “offer自由” 。
尼恩技术圣经系列PDF
- 《NIO圣经：一次穿透NIO、Selector、Epoll底层原理》
- 《Docker圣经：大白话说Docker底层原理，6W字实现Docker自由》
- 《K8S学习圣经：大白话说K8S底层原理，14W字实现K8S自由》
- 《SpringCloud Alibaba 学习圣经，10万字实现SpringCloud 自由》
- 《大数据HBase学习圣经：一本书实现HBase学习自由》
- 《大数据Flink学习圣经：一本书实现大数据Flink自由》
- 《响应式圣经：10W字，实现Spring响应式编程自由》
- 《Go学习圣经：Go语言实现高并发CRUD业务开发》
……完整版尼恩技术圣经PDF集群，请找尼恩领取
《尼恩 架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》PDF，请到下面公号【技术自由圈】取↓↓↓
