当前位置:首页 > mysql > 正文内容

Mysql B+树索引常见面试题

phpmianshi4年前 (2017-04-06)mysql661

概念


一个经典的B+树索引数据结构见下图:

image.png

B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接。


在B+树上的常规检索,从根节点到叶子节点的搜索效率基本相当,不会出现大幅波动,而且基于索引的顺序扫描时,也可以利用双向指针快速左右移动,效率非常高。


因此,B+树索引被广泛应用于数据库、文件系统等场景。顺便说一下,xfs文件系统比ext3/ext4效率高很多的原因之一就是,它的文件及目录索引结构全部采用B+树索引,而ext3/ext4的文件目录结构则采用Linked list, hashed B-tree、Extents/Bitmap等索引数据结构,因此在高I/O压力下,其IOPS能力不如xfs。



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


这个问题的简单回答是:约 2 千万。


在计算机中磁盘存储数据最小单元是扇区,一个扇区的大小是 512 字节


而文件系统(例如 XFS/EXT4)他的最小单元是块,一个块的大小是 4K。


而对于我们的 InnoDB 存储引擎也有自己的最小储存单元——页(Page),一个页的大小是 16K。


文件系统中一个文件大小只有 1 个字节,但不得不占磁盘上 4KB 的空间。


InnoDB 的所有数据文件(后缀为 ibd 的文件),他的大小始终都是 16384(16K)的整数倍。


在 MySQL 中我们的 InnoDB 页的大小默认是 16K,当然也可以通过参数设置:


mysql> show variables like 'innodb_page_size';


+------------------+-------+


| Variable_name | Value |


+------------------+-------+


| innodb_page_size | 16384 |


+------------------+-------+


1 row in set (0.00 sec)


数据表中的数据都是存储在页中的,所以一个页中能存储多少行数据呢?假设一行数据的大小是 1K,那么一个页可以存放 16 行这样的数据。


InnoDB 存储引擎的最小存储单元是页,页可以用于存放数据也可以用于存放键值+指针,在 B+ 树中叶子节点存放数据,非叶子节点存放键值+指针。

索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中,进而在去数据页中查找到需要的数据。

那么回到我们开始的问题,通常一棵B+树可以存放多少行数据?


这里我们先假设 B+ 树高为 2,即存在一个根节点和若干个叶子节点,那么这棵 B+ 树的存放总记录数为:根节点指针数*单个叶子节点记录行数。


上文我们已经说明单个叶子节点(页)中的记录数=16K/1K=16。(这里假设一行记录的数据大小为 1K,实际上现在很多互联网业务数据记录大小通常就是 1K 左右)。


那么现在我们需要计算出非叶子节点能存放多少指针?其实这也很好算,我们假设主键 ID 为 bigint 类型,长度为 8 字节,而指针大小在 InnoDB 源码中设置为 6 字节,这样一共 14 字节。


我们一个页中能存放多少这样的单元,其实就代表有多少指针,即 16384/14=1170。


那么可以算出一棵高度为 2 的 B+ 树,能存放 1170*16=18720 条这样的数据记录。


根据同样的原理我们可以算出一个高度为 3 的 B+ 树可以存放:1170*1170*16=21902400 条这样的记录。


所以在 InnoDB 中 B+ 树高度一般为 1-3 层,它就能满足千万级的数据存储。


在查找数据时一次页的查找代表一次 IO,所以通过主键索引查询通常只需要 1-3 次 IO 操作即可查找到数据。


 


总结


lineitem 表的数据行数为 600 多万,B+ 树高度为 3,customer 表数据行数只有 15 万,B+ 树高度也为 3。


可以看出尽管数据量差异较大,这两个表树的高度都是 3。换句话说这两个表通过索引查询效率并没有太大差异,因为都只需要做 3 次 IO。


那么如果有一张表行数是一千万,那么他的 B+ 树高度依旧是 3,查询效率仍然不会相差太大。region 表只有 5 行数据,当然他的 B+ 树高度为 1。


 


为什么 MySQL 的索引要使用 B+ 树而不是其他树形构?比如 B 树?


他的简单版本回答是:因为 B 树不管叶子节点还是非叶子节点,都会保存数据,这样导致在非叶子节点中能保存的指针数量变少(有些资料也称为扇出)。


指针少的情况下要保存大量数据,只能增加树的高度,导致 IO 操作变多,查询性能变低。

版权声明:本文由PHP面试资料网发布,如需转载请注明出处。
分享给朋友:

相关文章

mysql中explain分析sql详解

Explain举例mysql> explain select * from event;  +—-+————-+——-+——+————...

mysql中数据页的相关概念

mysql中数据页的相关概念

概念在 InnoDB 存储引擎中,所有的数据都被逻辑地存放在表空间中,表空间(tablespace)是存储引擎中最高的存储逻辑单位,在表空间的下面又包括段(segment)、区(extent)、页(p...

mysql中group by的实现与优化

mysql中group by的实现与优化

概念    由于 GROUP BY 实际上也同样需要进行排序操作,而且与 ORDER BY 相比,GROUP BY 主要只是多了 排序之后的分组操作。当然,如果在分组的时...

InnoDB体系结构及工作原理

InnoDB体系结构及工作原理

概念InnoDB主要包括了内存池、后台线程以及存储文件。INNODB的三大特性:插入缓存,两次写,自适应hash内存池又是由多个内存块组成的,主要包括Buffer Pool、redo log缓冲等,解...

mysql中distinct的实现与优化

概念DISTINCT 实际上和 GROUP BY的操作非常相似,只不过是在 GROUP BY 之后的每组中只取出一条记录而已。所以,DISTINCT 的实现和 GROUP BY 的实现也基本差不多,没...

mysql中AnalyzeTable优化

Analyze TableMySQL的Optimizer(优化元件)在优化SQL语句时,首先需要收集一些相关信息,其中就包括表的cardinality(可以翻译为“散列程度”),它表示某个索引对应的列...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。