.ibd代表我们现在使用的存储引擎是InnoDBibd里面既有数据又有索引

不管是MyISAM还是InnoDB存储引擎内部使用的数据结构都是以B树为载体的

一次查询数据库的过程主要涉及到三个计算机的硬件硬盘、内存、CPU

大概过程是把硬盘中的数据读取到内存中树的根节点本身是缓存在内存里的然后CPU从内存中读取数据进行计算我们简单演示一下从0001-0010这棵树查找0007的过程

1.第一步先从磁盘读取根节点0004实际上已经缓存了这是第一次磁盘IO的过程判断0007比根节点大往右侧进行寻址

2.CPU进行调度把0006、0008这节点从磁盘读取到内存里这是第二次磁盘IO的过程CPU从内存读取数据进行判断发现0007大于0006但是小于0008所以往二者之间的分支进行寻址

3.CPU进行调度把0007这个节点从磁盘中读取到内存中CPU从内存中读取数据发现与查找目标一致查询结束这是第三次磁盘IO的过程。

Mysql进行磁盘读取的时候不会只读取一个节点而是会按照以数据页为最小单位最小数据交互单元进行读取Windows的数据页为4kMySQL的数据页为16k。

数据页我们把他想象为一个个的格子每次都要读取一个格子的数据如果要读取的数据不到一个格子则读取一个格子超过一个格子小于2个格子读取两个格子以此类推。

好比我们有一个衣柜原来是所有的东西都放在里面找起来特别麻烦然后呢产生了文件系统的概念打成了一个个的格子按照格子进行分类每个给子的大小就是数据页的大小

mysql的数据页是16kb比如我们刚才查找0007的过程整个过程共读取了3个数据页也就是48kb这是单人单次查询的磁盘IO消耗

下面我们看一下B树的数据结构每一个节点的大小磁盘块大小是固定的16kb对于B树来说这16kb的空间用来放三类数据指针*子节点的寻址地址占用少量空间、索引列的数据比如id占用的空间比较少、数据图中data的部分这部分是特别耗空间的。

因为大小是固定的16kb所以单条数据占用的空间越小则磁盘块可以放的数据条数越多比如如果单条数据是1kb那一个磁盘块只能放16条数据而如果是1b就可以放16000条数据也就是存储同样数量的数据如果单条数据越小则需要的磁盘块节点越少也就是基于同样的Max.Degree树的高度会降低

B树每个节点都放了数据而B树只有叶子节点放了数据其他的层的数据都只有指针和原始的索引列的值

图中X,

:非叶子节点内指向其他内存页的指针数量B树和B树数据结构中的Max.Degree

因为B树只有叶子节点能存放数据我们这里要先算一下叶子节点的数量

大家都学过最简单的树的数据结构二叉树特殊的多叉树Max.Degree为2Z层二叉树的节点数量是2^(Z-1)

图中的B树叶子节点的数量应该是X^(Z-1)个然后每个叶子节点页能放Y条数据由此我们得出这棵B树最多能放X^(Z-1)*Y条记录。

因为Mysql的页大小16kb我们页头页尾那部分数据全加起来大概128Byte加上页目录毛估占1k左右吧也就是只有15k左右可以用来放数据索引列的值和指针这里假设索引列是bigint类型占8Byte然后页号指向前后页的指针在源码中叫做FIL_PAGE_OFFSET(4Byte),二者大概是1:1的关系相当于每条索引占用12Byte左右的空间所以非叶子节点每页可以容纳15KByte/12Byte1280条数据图中的X)如果是3层B树那图中的Z就是3.

现在我们评估一下Y对于叶子节点来说每个页的大小也是16kb但是叶子节点放的是真正的数据占的空间会比较大一些假设每一条数据1kb那每个页只能放15条数据我们页头页尾那部分数据全加起来大概128Byte加上页目录毛估占1k,然后我们把Y15代入上面的公式可以得到3层的B树可以放的数据记录的条数为1280^*(3-1*15

24576000这个可能就是我们平时传言的超过2000万要分库分表的依据。

但是这个不是绝对的比如我们刚才评估每条数据占1kb那如果数据比较简单每条数据只需要200b呢那刚才的3层B树就可以容纳1.25亿条数据。

mysql的查询速度主要取决于B树的高度因为只有叶子节点有数据所以一定要经历树的高度次IO这里与B树不同B树最少1次最多树的高度次所以具体可以容纳多少条数据而不影响性能需要根据具体的数据来分析。