mysql 的InnoDB存储引擎 一棵B树可以存放多少行数据?答案在文章中要搞清楚这个问题首先要从InnoDB索引数据结构、数据组织方式说起。我们都知道计算机有五大组成部分控制器运算器存储器输入设备输出设备。其中很重要的也跟今天这个题目有关系的是存储器。我们知道万事万物都有自己的单元体系若干个小单体组成一个个大的个体。就像拼乐高一样可以自由组合。所以说如果能熟悉最小单元就意味着我们抓住了事物的本事再复杂的问题也会迎刃而解。存储单元存储器范围比较大但是数据具体怎么存储有自己的最小存储单元。1、数据持久化存储磁盘里磁盘的最小单元是扇区一个扇区的大小是 512个字节2、文件系统的最小单元是块一个块的大小是 4K3、InnoDB存储引擎有自己的最小单元称之为页一个页的大小是16K扇区、块、页这三者的存储关系InnoDB引擎如果mysql部署在本地通过命令行方式连接mysql默认的端口3306然后输入密码即可进入mysql -u root -p查看InnoDB的页大小show variables like innodb_page_size;mysql数据库中table表中的记录都是存储在页中那么一页可以存多少行数据假如一行数据的大小约为1K字节那么按16K / 1K 16可以计算出一页大约能存放16条数据。mysql 的最小存储单元叫做“页”这么多的页是如何构建一个庞大的数据组织我们又如何知道数据存储在哪一个页中如果逐条遍历性能肯定很差。为了提升查找速度我们引入了B树先来看下B树的存储结构页除了可以存放数据叶子节点还可以存放健值和指针非叶子节点当然他们是有序的。这样的数据组织形式我们称为索引组织表。如上图中 page number3的页该页存放键值和指向数据页的指针这样的页由N个键值指针组成B 树是如何检索记录首先找到根页你怎么知道一张表的根页在哪呢其实每张表的根页位置在表空间文件中是固定的即page number3的页找到根页后通过二分查找法定位到id5的数据应该在指针P5指向的页中然后再去page number5的页中查找同样通过二分查询法即可找到id5的记录如何计算B树的高度在InnoDB的表空间文件中约定page number 3表示主键索引的根页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 and b.name like %sp_job_log;从图中可以看出每个表的主键索引的根页的page number都是3而其他的二级索引page number为4在根页偏移量为64的地方存放了该B树的page level。主键索引B树的根页在整个表空间文件中的第3个页开始所以算出它在文件中的偏移量16384*3 64 49152 64 49216前2个字节中。首先找到MySql数据库物理文件存放位置show global variables like %datadir% ;hexdump工具查看表空间文件指定偏移量上的数据hexdump -s 49216 -n 10 sp_job_log.ibdpage_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次数是33 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 来说这些操作所消耗的时间是可以忽略不计的。所以对一个索引字段进行检索采用普通索引还是唯一索引在检索效率上基本上没有差别。