MySQL InnoDB存储引擎一个B树可以存储多少行数据？要理解这个问题，首先要从InnoDB索引的数据结构和数据组织入手。

我们都知道计算机有五大组成部分：控制器、运算器、存储器、输入设备和输出设备。其中最重要的一个，也和今天的话题有关，就是记忆。

我们知道任何事物都有自己的单位体系，几个小单体组成一个大个体。就像拼乐高一样，可以自由组合。

所以，如果我们熟悉了最小的单元，就意味着我们掌握了事物的技能，即使是最复杂的问题也能轻松解决。

00-1010内存范围大，但如何存储数据有自己的最小内存单元。

1.在数据持久存储磁盘中，磁盘的最小单位是扇区，扇区的大小是512字节。

2.文件系统的最小单位是块，块的大小是4K。

3.InnoDB存储引擎有自己最小的单元，叫做page，一页的大小是16K。

扇区、块和页面之间的存储关系是什么？

存储单元

如果mysql部署在本地，请通过命令行、默认端口3306连接mysql，然后输入要输入的密码。

查看InnoDB的页面大小

showvariablelike ' innodb _ page _ size '；

在mysql数据库中，表中的记录都存储在页面中，那么一个页面可以存储多少行数据呢？如果一行数据的大小约为1K字节，按16K/1K=16，可以算出一页可以容纳约16条数据。

mysql最小的存储单元叫做“页面”。这么多页面是如何构建一个庞大的数据组织的，我们如何知道数据存储在哪个页面？

如果你一个一个遍历，性能肯定会很差。为了提高搜索速度，我们引入了B树。我们先来看看B树的存储结构。

页面不仅可以存储数据(叶节点)，还可以存储键值和指针(非叶节点)。当然，他们是有秩序的。这种数据组织形式称为索引组织表。

例如，上图中页码=3的页面存储键值和指向数据页面的指针，这样的页面由n个键值指针组成。

B树如何检索记录？

首先找到根页面，你怎么知道一个表的根页面在哪里？实际上，每个表的根页在表空间文件中的位置是固定的，即页码=3的页找到根页后，id=5的数据应该在指针P5指向的页中搜索，然后在页码=5的页中搜索。同样，id=5

InnoDB引擎

的记录可以由二分搜索法找到。在InnoDB的表空间文件中，约定页码=3代表主键索引的根页。

挑选

名字，名字，索引号，类型，空格，页码

从

信息模式。INNODB _ SYS _ INDEX a，

信息模式。INNODB_SYS_TABLES b

在哪里

a.table_id=b.table_id和a.space 0

和b.name，如“% sp _ job _ log”；

从图中可以看出，每个表的主键索引的根页的页码是3，而其他二级索引的页码是4。

B树的页面级别存储在根页面偏移量64处。主键索引B树的根页从整个表空间文件的第三页开始，所以计算它在文件中的偏移量：16384*3 64=49152 64=49216，在前两个字节中。

首先，找到MySql数据库。

物理文件存放位置：

show global variables like "%datadir%" ; 

hexdump工具，查看表空间文件指定偏移量上的数据：

hexdump -s 49216 -n 10 sp_job_log.ibd 

page_level 值是 1，那么 B+树高度为 page level + 1 = 2

特别说明：

查询数据库时，不论读一行，还是读多行，都是将这些行所在的整页数据加载，然后在内存中匹配过滤出最终结果。表的检索速度跟树的深度有直接关系，毕竟一次页加载就是一次IO，而磁盘IO又是比较费时间。对于一张千万级条数B+树高度为3的表与几十万级B+树高度也为3的表，其实查询效率相差不大。

一棵树可以存放多少行数据?

假设B+树的深度为2

这棵B+树的存储总记录数 = 根节点指针数 * 单个叶子节点记录条数

那么指针数如何计算?

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

那么一个页中能存放多少这样的组合，就代表有多少指针，即 16384 / 14 = 1170。那么可以算出一棵高度为2 的B+树，能存放 1170 * 16 = 18720 条这样的数据记录。

同理：

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

千万级的数据存储只需要约3层B+树，查询数据时，每加载一页(page)代表一次IO。所以说，根据主键id索引查询约3次IO便可以找到目标结果。

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

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

然后，再根据主键id 从 聚簇索引查找到对应的记录。如上图所示，二级索引有3层，聚簇索引有3层，那么最多花费的IO次数是：3+3 = 6

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

这也是为什么InnoDB表必须有主键，并且推荐使用整型的自增主键!!!

InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上

举例说明：

1、若使用"where id = 14"这样的条件查找记录，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

2、若对Name列进行条件搜索，则需要两个步骤：

第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键值。

第二步使用主键值在主索引B+树中再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。(重点在于通过其他键需要建立辅助索引)

实战演示

实际项目中，每个表的结构设计都不一样，占用的存储空间大小也各不相等。如何计算不同的B+树深度下，一个表可以存储的记录条数?

我们以业务日志表 sp_job_log 为例，讲解详细的计算过程：

1、查看表的状态信息

show table status like 'sp_job_log'G 

图中看到sp_job_log表的行平均大小为153个字节

2、查看表结构

desc sp_job_log; 

3、计算B+树的行数

单个叶子节点(页)中的记录数 = 16K / 153 = 105

非叶子节点能存放多少指针， 16384 / 14 = 1170

如果树的高度为3，可以存放的记录行数 = 1170 * 1170 * 105 = 143,734,500

最后加餐

普通索引和唯一索引在查询效率上有什么不同？唯一索引就是在普通索引上增加了约束性，也就是关键字唯一，找到了关键字就停止检索。

而普通索引，可能会存在用户记录中的关键字相同的情况，根据页结构的原理，当我们读取一条记录的时候，不是单独将这条记录从磁盘中读出去，而是将这个记录所在的页全部加载到内存中进行读取。

InnoDB 存储引擎的页大小为 16KB，在一个页中可能存储着上千个记录，因此在普通索引的字段上进行查找也就是在内存中多几次判断下一条记录的操作，对于 CPU 来说，这些操作所消耗的时间是可以忽略不计的。

所以对一个索引字段进行检索，采用普通索引还是唯一索引在检索效率上基本上没有差别。

作者：TomGE

简介：前阿里架构师，出过专利，竞赛拿过奖，博客专家，负责过电商交易、社区生鲜、营销、金融等业务，多年团队管理经验，爱思考，喜欢结交朋友

编辑：pbdbbt

出处：转载自公众号微观技术（ID：weiguanjishu）