本文转载自微信公众号“微科技”，作者TomGE。转载本文请联系微科公众号。大家好，我是汤姆哥~今日寄语：一个元气满满的新人，竟然能让身边的每一个人都回归初心，真是不可思议。Mysql的InnoDB存储引擎B+树可以存储多少行数据？（文章里有答案！！）要弄清楚这个问题，首先要从InnoDB的索引数据结构和数据组织说起。我们都知道计算机有五个主要部件：控制器、运算器、存储器、输入设备和输出设备。与今天的话题最重要和相关的话题之一是记忆。我们知道万物都有自己的单位系统，几个小单位组成一个大个体。就像玩乐高一样，可以自由组合。因此，如果我们能够熟悉最小的单位，就说明我们掌握了事物的能力，再复杂的问题也能迎刃而解。存储单元的内存范围比较大，但是如何存储数据都有自己的最小存储单元。1、在数据持久化存储磁盘中，磁盘的最小单位是扇区，一个扇区的大小为512字节。2、文件系统的最小单位是块，块的大小为4K3。InnoDB存储引擎有自己的最小单位，称为页。页的大小是16K的扇区、块、页的存储关系。如果本地部署了InnoDB引擎，通过命令行连接mysql，默认端口为3306，然后输入密码输入mysql-uroot-p查看InnoDB的pagesizeshowvariableslike'innodb_page_size'；在mysql数据库中，table表中的记录都是分页存储的，那么一个分页可以存储多少行数据呢？如果一行数据的大小约为1K字节，那么根据16K/1K=16，可以计算出一页可以存储大约16条数据。mysql最小的存储单元叫做“页”。那么多的页面是如何构建一个庞大的数据组织的，我们又如何知道数据存放在哪个页面呢？如果一个一个遍历，性能肯定很差。为了提高搜索速度，我们引入了B+树。我们先来看一下B+树的存储结构页。除了存储数据（叶子节点），它还可以存储健康值和指针（非叶子节点）。当然，它们是有序的。这种数据组织形式称为索引组织表。例如：上图中pagenumber=3的页面存放的是键值和指向数据页的指针。这样一个页面由N个键值+指针组成。B+树是如何检索记录的？先找到根页，怎么知道一个表的根页在哪里呢？其实每个表的根页的位置在表空间文件中是固定的，即pagenumber=3的页找到根页，使用二分查找的方式定位到id=5的数据应该在指针P5指向的页中查找，然后在页码=5的页中查找。另外，id=5的记录可以通过二进制查询的方式找到。B+树的高度如何计算？InnoDB的表空间文件中，约定页码=3表示主键索引SELECTb.name,a.name,index_id,type,a.space,a.PAGE_NOFROMinformation_schema.INNODB_SYS_INDEXESa,information_schema.INNODB_SYS_TABLESbWHEREa的根页。table_id=b.table_idANDa.space<>0andb.namelike'%sp_job_log';从图中可以看出，每个表的主键索引根页的页码为3，而其他二级索引的页码为4，B+树存储在根页偏移量64处页面级别。主键索引B+树的根页在整个表空间文件中从第3页开始，所以计算它在文件中的偏移量：16384*3+64=49152+64=49216，在前2个字节。首先找到MySql数据库的物理文件存放位置：showglobalvariableslike"%datadir%";hexdump工具，查看表空间文件指定offset上的数据：hexdump-s49216-n10sp_job_log.ibdpage_level值为1，则B+树高度为pagelevel+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=2190.24亿级数据存储只需要3层左右的B+树。查询数据时，每加载一个页面（page）就代表一个IO。所以根据主键id索引查询3个左右的IO就可以找到目标结果。对于一些复杂的查询，可能需要用到secondaryindex，那么通过secondaryindex查找记录需要多少IO呢？首先从二级索引B+树中，根据name找到对应的主键id，然后根据主键id从聚簇索引中找到对应的记录。如上图，二级索引有3层，聚簇索引有3层，所以最大花费的IO数为：3+3=6聚簇索引默认为主键。如果表中没有定义主键，InnoDB将选择一个Unique非空索引代替。如果没有这样的索引，InnoDB将隐式定义一个主键作为聚簇索引。这就是InnoDB表必须有主键的原因，推荐使用整型自增主键！！！InnoDB使用聚集索引，将主键组织成B+树，行数据存储在叶子节点上。示例：1、如果使用条件“whereid=14”来查找记录，则可以根据B+树查找算法找到对应的叶子节点，然后得到行数据。2、如果对Name列进行条件查找，需要两步：第一步在辅助索引B+树中检索Name，到达其叶子节点，获取对应的主键值。第二步在主索引B+树中使用主键值再进行一次B+树检索操作，最终到达叶子节点获取整行数据。（重点是需要通过其他key建立辅助索引）在实战演示项目中，各个表的结构设计不同，占用的存储空间也不同。如何计算一张表在不同B+树深度下可以存储的记录数？下面以业务日志表sp_job_log为例，说明详细的计算过程：1.查看表的状态信息showtablestatuslike'sp_job_log'\GseeTheaveragerowsizeofsp_job_logtableis153bytes2.查看表结构descsp_job_log；3.计算B+树的行数。单个叶子节点（页）的记录数=16K/153=105一个非叶子节点可以存放多少个指针，16384/14=1170若树的高度为3，则记录行数可以存储的=1170*1170*105=143,734,500最后，普通索引和唯一索引在查询效率上有什么区别？唯一索引是在普通索引上增加了限制，即关键字是唯一的，找到关键字就停止搜索。但是，普通索引在用户记录中可能有相同的关键字。根据页结构的原理，当我们读取一条记录时，并不是单独从磁盘中读取这条记录，而是读取这条记录所在的记录。这些页面都被加载到内存中以供阅读。InnoDB存储引擎的页大小为16KB，一页可以存储数千条记录。所以，在普通索引的字段上查找，就是在内存中多次判断下一条记录的操作。对于CPU来说，这些操作消耗的时间可以忽略不计。因此，在查找索引字段时，使用公共索引和唯一索引在检索效率上基本没有区别。
