尼恩说在前面

在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个变种

《尼恩 架构笔记》《尼恩高并发三部曲》《尼恩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 索引数据结构的磁盘文件格式

• 磁盘上，存储数据最小单元是扇区，一个扇区的大小是 512 字节，
• 文件系统（例如EXT4）,最小单元是块 （block），一个block 块的大小是 4k，
• InnoDB 存储引擎 的最小储存单元——页（Page），一个页的大小是 16K。

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

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

• 我们假设主键的类型是 BigInt，长度为 8 字节，
• 而指针大小在 InnoDB 中设置为 6 字节，

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

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

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

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

• 第一层、第二层 都是 非叶节点（索引页）， 用来存储  key + 页指针
• 我们假设主键的类型是 BigInt，长度为 8 字节，
• 而指针大小在 InnoDB 中设置为 6 字节
• 第三层是 叶子， 存储数据 ,是 数据页

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

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

• 第一层、第二层、第三层 都是 非叶节点（索引页）， 用来存储  key + 页指针
• 高为3的B+树一共可以存放 1170 * 1170 * * 1170 个 叶子节点 。

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

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

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)

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;

• 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 |

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

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

[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

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

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

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

[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]# 

InnoDB一棵B+树的IO次数

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

• 树高度为 1 的情况（极端简单情况）：如果索引树高度为 1，这意味着索引数据可能全部存储在一个数据页中（假设只有根节点）。查询时，只需要一次磁盘 I/O 操作将这个数据页读取到内存中，就可以获取到所需的索引信息，进而找到对应的行记录，这种情况下查询效率很高。
• 树高度为 2 的情况：当树高度为 2 时，首先需要一次磁盘 I/O 操作读取根节点到内存，然后根据根节点中的指针信息，再进行一次磁盘 I/O 操作读取叶子节点到内存，总共需要两次磁盘 I/O 操作来获取索引数据。
• 树高度为 3 及以上的情况：随着树高度的增加，每增加一层，查询就需要多进行一次磁盘 I/O 操作。例如，树高度为 3 时，可能需要先读取根节点，再读取中间节点，最后读取叶子节点，总共需要三次磁盘 I/O 操作。

InnoDB一棵B+树的查找流程

• 从根页开始，首先通过非叶子节点的二分查找法，
• 确定数据在下一层哪个页之后，一层一层往下找，一直到 叶子节点，
• 进而在 叶子节（数据页）中查找到需要的数据；

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

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

1. 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
2. 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
3. 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。

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+树的查找耗时

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

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

• 根节点页：由于根节点是索引树访问的起始点，经常会被访问到。在系统运行过程中，根节点所在的页很可能会被缓存到缓冲池中。这样，在进行索引查询时，能快速定位到根节点，减少磁盘 I/O。
• 中间节点页：随着查询操作的进行，中间节点也会被频繁访问。如果缓冲池足够大，这些中间节点所在的页也会被缓存。这有助于在索引遍历过程中，快速从一个中间节点跳转到下一个相关的中间节点，而不需要每次都从磁盘读取。
• 叶子节点页：叶子节点包含了实际的数据记录（在聚簇索引中）或者指向数据记录的指针（在二级索引中）。叶子节点页的缓存对于数据的读取至关重要。当进行数据查询时，最终是要从叶子节点获取数据的，所以叶子节点页也会被缓存到缓冲池中，以加快数据访问速度。

Buffer Pool 的大小设置

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

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

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

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

• sql查询语句的执行流程：

• 索引的底层原理

• 索引下推 ?

• 索引失效

• MVCC

• binlog、redolog、undo log

• mysql 事务

• 分布式事务

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

空窗8月：中厂大龄34岁，被裁8月收一大厂offer， 年薪65W，转架构后逆天改命!

空窗2年：42岁被裁2年，天快塌了，急救1个月，拿到开发经理offer，起死回生

空窗半年：35岁被裁6个月， 职业绝望，转架构急救上岸，DDD和3高项目太重要了

空窗1.5年：失业15个月，学习40天拿offer， 绝境翻盘，如何实现？