B-树、B+树、红黑树,是平衡查找树,那么查询效率上讲,平均都是O(logn)。使用什么哪种数据结构,肯定是出于提高数据库的查询效率的考虑。
一、B+树做索引而不用B-树
Mysql如何衡量查询效率呢?– 磁盘IO次数。
一般来说索引非常大,尤其是关系性数据库这种数据量大的索引能达到亿级别,所以为了减少内存的占用,索引也会被存储在磁盘上。B-树/B+树 的特点就是每层节点数目非常多,层数很少,目的就是为了减少磁盘IO次数,但是B-树的每个节点都有data域(指针),这无疑增大了节点大小,说白了增加了磁盘IO次数(磁盘IO一次读出的数据量大小是固定的,单个数据变大,每次读出的就少,IO次数增多,一次IO多耗时),而B+树除了叶子节点其它节点并不存储数据,节点小,磁盘IO次数就少。
优点一: B+树只有叶节点存放数据,其余节点用来索引,而B-树是每个索引节点都会有Data域。
优点二: B+树所有的Data域在叶子节点,并且所有叶子节点之间都有一个链指针。 这样遍历叶子节点就能获得全部数据,这样就能进行区间访问啦。在数据库中基于范围的查询是非常频繁的,而B树不支持这样的遍历操作。
B+树做索引而不用红黑树
AVL 树(平衡二叉树)和红黑树(二叉查找树)基本都是存储在内存中才会使用的数据结构。在大规模数据存储的时候,红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁,进而导致效率低下的情况。为什么会出现这样的情况,要获取磁盘上数据,必须先通过磁盘移动臂移动到数据所在的柱面,然后找到指定盘面,接着旋转盘面找到数据所在的磁道,最后对数据进行读写。磁盘IO代价主要花费在查找所需的柱面上,树的深度过大会造成磁盘IO频繁读写。根据磁盘查找存取的次数往往由树的高度所决定,所以,只要我们通过某种较好的树结构减少树的结构尽量减少树的高度,B树可以有多个子女,从几十到上千,可以降低树的高度。
数据库系统的设计者巧妙利用了磁盘预读原理,将一个节点的大小设为等于一个页,这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的,在实际实现B-Tree还需要使用如下技巧:每次新建节点时,直接申请一个页的空间,这样就保证一个节点物理上也存储在一个页里,加之计算机存储分配都是按页对齐的,就实现了一个node只需一次I/O。
B-TREE索引
B-TREE索引是使用最多的索引。很多存储引擎采用的都是B-TREE数据结构的变体实现该索引,例如InnoDB使用的是B+TREE,即每个叶子节点都包含指向下一个叶子节点的指针,从而方便叶子节点范围遍历。
B-TREE中的所有值都是按顺序存储的,每个叶子页到根的距离相同。下图展示了InnoDB中的B-TREE索引是如何工作的:
当查找一行记录时,存储引擎会先在索引中搜索。从索引的根节点开始,通过比较节点页的值和要查找的值逐层进入下层节点,最底层叶子节点的指针指向的是被索引的数据。这样的查找方式避免了全表扫描,加快访问数据的速度。此外因为B-Tree对索引列是顺序存储的,所以也很适合查找范围数据。
下面是一个使用B-Tree索引的例子,有如下数据表:
CREATE TABLEPeople (
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender enum('m','f') not null,
key(last_name,first_name,dob)
)
建表语句在last_name、first_name、dob列上建立了一个联合索引,下图展示了该索引的存储结构
联合索引中的索引项会先根据第一个索引列进行排序,第一个索引列相同的情况下,会再按照第二个索引列进行排序,依次类推。根据这种存储特点,B-Tree索引对如下类型的查找有效:
全值匹配:查找条件和索引中的所有列相匹配
匹配最左前缀:查找条件只有索引中的第一列
匹配列前缀:只匹配某一列值的开头部分。这里并不一定只能匹配第一个索引列的前缀。例如在确定第一个索引列的值时,也可以在第二个索引列上匹配列前缀。在上面例子中,对于查找姓为Allen,名为J开头的人,也可以应用到索引。
匹配范围值,或者精确匹配某一列并范围匹配另外一列:例如查找姓在Allen和Barrymore之间的人,或者查找姓为Allen,名字在某一个范围内的人。
只访问索引的查询,即要查询的值在索引中都包含,只需要访问索引就行了,无需访问数据行。这种索引被称作覆盖索引。
对于上面列出的查询类型,索引除了可以用来查询外,还可以用来排序。
下面是B-Tree索引的一些限制:
如果不是从索引的最左列开始查找,则无法使用索引。例如直接查找名字为Bill的人,或查找某个生日的人都无法应用到上面的索引,因为都跳过了索引的第一个列。此外查找姓以某个字母结尾的人,也无法使用到上面的索引。
不能在中间跳过索引中的某个列,例如不能查找姓为Smith,生日为某个特定日期的类。这样的查询只能使用到索引的第一列。
如果查询中有某个列的范围查询,则该列右边的所有列都无法使用索引优化查找。例如有查询WHERE last_name='Smith' AND first_name LIKE 'J%' AND dob='1976-12-23',这个查询只能使用到索引的前两列,而不能使用整个索引。