mysqlB+tree索引的设计原理

1.什么是数据库的索引
每种查找算法都只能应用于特定的数据结构之上&＃xff0c;例如二分查找要求被检索数据有序&＃xff0c;而二叉树查找只能应用于二叉查找树上&＃xff0c;但是数据本身的组织结构不可能完全满足各种数据结构&＃xff08;例如&＃xff0c;理论上不可能同时将两列都按顺序进行组织&＃xff09;&＃xff0c;所以&＃xff0c;在数据之外&＃xff0c;数据库系统还维护着满足特定查找算法的数据结构&＃xff0c;这些数据结构以某种方式引用&＃xff08;指向&＃xff09;数据&＃xff0c;这样就可以在这些数据结构上实现高级查找算法。这种数据结构&＃xff0c;就是索引。
在向数据库中插入新的数据时&＃xff0c;同时也需要向数据库索引中插入相应的索引键值 &＃xff0c;则需要向 B&＃43;树 中插入新的键值。
当从数据库中删除数据时&＃xff0c;同时也需要从数据库索引中删除相应的索引键值 &＃xff0c;则需要从 B&＃43;树 中删 除该键值
在mysql中使用的这种数据结构就是B&＃43;tree&＃xff0c;它是B-tree的变种。
那么什么是B-tree&＃xff1f;

2.B-tree

3.B-tree查找
模拟查找关键字29的过程&＃xff1a;
根据根节点找到磁盘块1&＃xff0c;读入内存。【磁盘I/O操作第1次】
比较关键字29在区间&＃xff08;17,35&＃xff09;&＃xff0c;找到磁盘块1的指针P2。
根据P2指针找到磁盘块3&＃xff0c;读入内存。【磁盘I/O操作第2次】
比较关键字29在区间&＃xff08;26,30&＃xff09;&＃xff0c;找到磁盘块3的指针P2。
根据P2指针找到磁盘块8&＃xff0c;读入内存。【磁盘I/O操作第3次】
在磁盘块8中的关键字列表中找到关键字29。
分析上面过程&＃xff0c;发现需要3次磁盘I/O操作&＃xff0c;和3次内存查找操作。由于内存中的关键字是一个有序表结构&＃xff0c;可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数&＃xff0c;使每次磁盘I/O取到内存的数据都发挥了作用&＃xff0c;从而提高了查询效率。

4.B-tree的插入和删除
假设需依次插入关键字30&＃xff0c;26

1) 首先通过查找确定插入的位置。由根a 起进行查找&＃xff0c;确定30应插入的在d 节点中。由于*d 中关键字数目不超过2&＃xff08;即m-1&＃xff09;&＃xff0c;故第一个关键字插入完成&＃xff1a;如&＃xff08;b&＃xff09;

2) 同样&＃xff0c;通过查找确定关键字26亦应插入 d. 由于d节点关键字数目超过2&＃xff0c;此时需要将 d分裂成两个节点&＃xff0c;关键字26及其前、后两个指针仍保留在 d 节点中&＃xff0c;而关键字37 及其前、后两个指针存储到新的产生的节点 d中。同时将关键字30 和指示节点 *d的指针插入到其双亲的节点中。由于 b节点中的关键字数目没有超过2&＃xff0c;则插入完成.如&＃xff08;c&＃xff09;(d)

5.B&＃43;tree
B&＃43;Tree是在B-Tree基础上的一种优化&＃xff0c;使其更适合实现外存储索引结构&＃xff0c; 即B&＃43;树是应文件系统所需而产生的一种B-树的变形树。InnoDB存储引擎就是用B&＃43;Tree实现其索引结构。
从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值&＃xff0c;还有data值。而每一个页的存储空间是有限的&＃xff0c;如果data数据较大时将会导致每个节点&＃xff08;即一个页&＃xff09;能存储的key的数量很小&＃xff0c;当存储的数据量很大时同样会导致B-Tree的深度较大&＃xff0c;增大查询时的磁盘I/O次数&＃xff0c;进而影响查询效率。在B&＃43;Tree中&＃xff0c;所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上&＃xff0c;而非叶子节点上只存储key值信息&＃xff0c;这样可以大大加大每个节点存储的key值数量&＃xff0c;降低B&＃43;Tree的高度。
B&＃43;Tree相对于B-Tree有几点不同&＃xff1a;
非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。

6.为什么使用B-Tree&＃xff08;B&＃43;Tree&＃xff09;
一般来说&＃xff0c;索引本身也很大&＃xff0c;不可能全部存储在内存中&＃xff0c;因此索引往往以索引文件的形式存储的磁盘上。这样的话&＃xff0c;索引查找过程中就要产生磁盘I/O消耗&＃xff0c;相对于内存存取&＃xff0c;I/O存取的消耗要高几个数量级&＃xff0c;所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说&＃xff0c;索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。下面先介绍内存和磁盘存取原理&＃xff0c;然后再结合这些原理分析B-/&＃43;Tree作为索引的效率。
局部性原理与磁盘预读
由于存储介质的特性&＃xff0c;磁盘本身存取就比主存慢很多&＃xff0c;再加上机械运动耗费&＃xff0c;磁盘的存取速度往往是主存的几百分分之一&＃xff0c;因此为了提高效率&＃xff0c;要尽量减少磁盘I/O。为了达到这个目的&＃xff0c;磁盘往往不是严格按需读取&＃xff0c;而是每次都会预读&＃xff0c;即使只需要一个字节&＃xff0c;磁盘也会从这个位置开始&＃xff0c;顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理&＃xff1a;
当一个数据被用到时&＃xff0c;其附近的数据也通常会马上被使用。
程序运行期间所需要的数据通常比较集中。
由于磁盘顺序读取的效率很高&＃xff08;不需要寻道时间&＃xff0c;只需很少的旋转时间&＃xff09;&＃xff0c;因此对于具有局部性的程序来说&＃xff0c;预读可以提高I/O效率。
预读的长度一般为页&＃xff08;page&＃xff09;的整倍数。页是计算机管理存储器的逻辑块&＃xff0c;硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块&＃xff0c;每个存储块称为一页&＃xff08;在许多操作系统中&＃xff0c;页得大小通常为4k&＃xff09;&＃xff0c;主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时&＃xff0c;会触发一个缺页异常&＃xff0c;此时系统会向磁盘发出读盘信号&＃xff0c;磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中&＃xff0c;然后异常返回&＃xff0c;程序继续运行。
B-/&＃43;Tree索引的性能分析
从使用磁盘I/O次数评价索引结构的优劣性&＃xff1a;根据B-Tree的定义&＃xff0c;可知检索一次最多需要访问h个结点。数据库系统的设计者巧妙的利用了磁盘预读原理&＃xff0c;将一个结点的大小设为等于一个页面&＃xff0c;这样每个结点只需要一次I/O就可以完全载入。为了达到这个目的&＃xff0c;在实际实现B-Tree还需要使用如下技巧&＃xff1a;
每次新建结点时&＃xff0c;直接申请一个页面的空间&＃xff0c;这样可以保证一个结点的大小等于一个页面&＃xff0c;加之计算机存储分配都是按页对齐的&＃xff0c;就实现了一个node只需一次I/O。
B-Tree中一次检索最多需要h-1次I/O&＃xff08;根结点常驻内存&＃xff09;&＃xff0c;渐进复杂度为O(h)&＃61;O(logdN)。一般实际应用中&＃xff0c;出读d是非常大的数字&＃xff0c;通常超过100&＃xff0c;因此h非常小。
综上所述&＃xff0c;用B-Tree作为索引结构效率是非常高的。
而红黑树结构&＃xff0c;h明显要深得多。由于逻辑上很近的结点&＃xff08;父子结点&＃xff09;物理上可能离得很远&＃xff0c;无法利用局部性原理。所以即使红黑树的I/O渐进复杂度也为O(h)&＃xff0c;但是查找效率明显比B-Tree差得多。
B&＃43;Tree更适合外存索引&＃xff0c;是和内结点出度d有关。从上面分析可以看到&＃xff0c;d越大索引的性能越好&＃xff0c;而出度的上限取决于结点内key和data的大小&＃xff1a;dmax&＃61;floor(pagesize/(keysize&＃43;datasize&＃43;pointsize))。
floor表示向下取整。由于B&＃43;Tree内结点去掉了data域&＃xff0c;因此可以拥有更大的出度&＃xff0c;拥有更好的性能。

7.MyISAM索引实现
1.主键索引
MyISAM引擎使用B&＃43;Tree作为索引结构&＃xff0c;叶节点的data域存放的是数据记录的地址。

2.辅助索引
在MyISAM中&＃xff0c;主索引和辅助索引&＃xff08;Secondary key&＃xff09;在结构上没有任何区别&＃xff0c;只是主索引要求key是唯一的&＃xff0c;而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引&＃xff0c;则此索引的结构如下图所示&＃xff1a;

8.InnoDB索引实现
1.InnoDB主键索引
InnoDB中&＃xff0c;表数据文件本身就是按B&＃43;Tree组织的一个索引结构&＃xff0c;这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键&＃xff0c;因此InnoDB表数据文件本身就是主索引。

2.InnoDB辅助索引
InnoDB的所有辅助索引都引用主键作为data域。例如&＃xff0c;下图为定义在Col3上的一个辅助索引&＃xff1a;

InnoDB存储引擎中有页&＃xff08;Page&＃xff09;的概念&＃xff0c;页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB&＃xff0c;可通过参数innodb_page_size将页的大小设置为4K、8K、16K&＃xff0c;在MySQL中可通过如下命令查看页的大小&＃xff1a;show variables like &＃39;innodb_page_size&＃39;;
系统从磁盘读取数据到内存时是以磁盘块&＃xff08;block&＃xff09;为基本单位的&＃xff0c;位于同一个磁盘块中的数据会被一次性读取出来&＃xff0c;而不是需要什么取什么。
而系统一个磁盘块的存储空间往往没有这么大&＃xff0c;因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位&＃xff0c;在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置&＃xff0c;这将会减少磁盘I/O次数&＃xff0c;提高查询效率。
.推算
InnoDB存储引擎中页的大小为16KB&＃xff0c;一般表的主键类型为INT&＃xff08;占用4个字节&＃xff09;或BIGINT&＃xff08;占用8个字节&＃xff09;&＃xff0c;指针类型也一般为4或8个字节&＃xff0c;也就是说一个页&＃xff08;B&＃43;Tree中的一个节点&＃xff09;中大概存储16KB/(8B&＃43;8B)&＃61;1K个键值&＃xff08;因为是估值&＃xff0c;为方便计算&＃xff0c;这里的K取值为〖10〗^3&＃xff09;。也就是说一个深度为3的B&＃43;Tree索引可以维护10^3 * 10^3 * 10^3 &＃61; 10亿 条记录。
实际情况中每个节点可能不能填充满&＃xff0c;因此在数据库中&＃xff0c;B&＃43;Tree的高度一般都在2~4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的&＃xff0c;也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

9.数据库中的B&＃43;Tree索引可以分为聚集索引&＃xff08;clustered index&＃xff09;和辅助索引&＃xff08;secondary index&＃xff09;
聚集索引的B&＃43;Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据&＃xff0c;而是存储相应行数据的聚集索引键&＃xff0c;即主键。当通过辅助索引来查询数据时&＃xff0c;InnoDB存储引擎会遍历辅助索引找到主键&＃xff0c;然后再通过主键在聚集索引中找到完整的行记录数据。

1. InnoDB索引和MyISAM索引的区别&＃xff1a;
   一是主索引的区别&＃xff0c;InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。
   二是辅助索引的区别&＃xff1a;InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

所以&＃xff1a;
1.不建议使用过长的字段作为主键&＃xff0c;因为所有辅助索引都引用主索引&＃xff0c;过长的主索引会令辅助索引变得过大
2.用非单调的字段作为主键在InnoDB中不是个好主意&＃xff0c;因为InnoDB数据文件本身是一颗B&＃43;Tree&＃xff0c;非单调的主键会造成在插入新记录时数据文件为了维持B&＃43;Tree的特性而频繁的分裂调整&＃xff0c;十分低效&＃xff0c;而使用自增字段作为主键则是一个很好的选择。

参考&＃xff1a;http://blog.sina.com.cn/s/blog_4e0c21cc01010itp.html
https://blog.csdn.net/ifollowrivers/article/details/73614549
https://www.cnblogs.com/tgycoder/p/5410057.html