搞懂Mysql InnoDB B+树索引

  哈希索引是自适应的,也就是说这个不能人为干预在一张表生成哈希索引,InnoDB会根据这张表的使用情况来自动生成。

  全文索引是将存在数据库的整本书的任意内容信息查找出来的技术,InnoDB从1.2.x版本支持。每张表只能有一个全文检索的索引。

  B+树索引是传统意义上的索引,B+树索引并不能根据键值找到具体的行数据,B+树索引只能找到行数据锁在的页,然后通过把页读到内存,再在内存中查找到行数据。B+树索引也是最常用的最为频繁使用的索引。

 

二.什么是B+树 概念

  B+树是一种平衡查找树,其实先想想看为什么要用平衡查找树,不用二叉树?普通的二叉树可能因为插入的数据最后变成一个很长的链表,怎么能提高搜索的速度呢?你可以想想,为什么HashMap和ConcurrentHashMap在JDK8的时候,当链表大于8的时候把链表转成红黑树(红黑树也是平衡查找树)。技术思维是想通的,那么答案无非是加快速度,性能咯。

  一个B+树有以下特征:

有n个子树的中间节点包含n个元素,每个元素不保存数据,只用来索引,所有数据都保存在叶子节点。

所有叶子节点包含元素的信息以及指向记录的指针,且叶子节点按关键字自小到大顺序链接。

所有的中间节点元素都同时存在于子节点,在子节点元素中是最大(或最小)元素。

  那么我们先来看一个B+树的图

搞懂Mysql InnoDB B+树索引

  所有的数据都在叶子节点,且每一个叶子节点都带有指向下一个节点的指针,形成了一个有序的链表。为什么要有序呢?其实是为了范围查询。比如说select * from Table where id > 1 and id < 100; 当找到1后,只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点,极大提到了区间查询效率。是不是范围查询的话hash就搞不定这个事情了?以下为B+树的优势:

单一节点存储更多元素,减少IO

所有查询都要找到叶子节点,查询稳定

所有叶子节点形成有序链表,方便范围查询

  一般性情况,数据库的B+树的高度一般在2~4层,这就是说找到某一键值的行记录最多需要2到4次逻辑IO,相当于0.02到0.04s。

 

三.聚集索引和辅助索引 聚集索引

  聚集索引是按表的主键构造的B+树,叶子节点存放的为整张表的行记录数据,每张表只能有一个聚集索引。优化器更倾向采用聚集索引。因为直接就能获取行数据。

  请选择自增id来做主键,不要非空UK列。避免大量分页碎片。下面来看一个聚集索引的图:

搞懂Mysql InnoDB B+树索引

  那么很简单了,每个叶子节点,都存有完整的行记录。对于主键的查找速度那是相当的快,美滋滋。

辅助索引

  辅助索引也叫非聚集索引,叶子节点除了键值以外还包含了一个bookmark,用来告诉InnoDB在哪里可以找到对应的行数据,InnoDB的辅助索引的bookmark就是相对应行数据的聚集索引键。也就是先获取指向主键索引的主键,然后通过主键索引来找到一个完整的行。如果辅助索引的树和聚集索引的树的高度都是3,如果不是走主键索引走辅助索引的话,那么需要6次逻辑IO访问得到最终的数据页。辅助索引和聚集索引的概念关系图如下:

搞懂Mysql InnoDB B+树索引

 

四.索引实战 设计索引

  设计索引的时候,无论是组合索引还是普通索引等。一般经验是,选择经常被用来过滤记录的字段,高选择性,高区分性。别把性别字段设计索引,性别属于低选择性的。你可以选择名字嘛,你好我大名叫苗嘉杏:)

  知道加索引快,但是也别乱加索引,插入以及更新索引的操作InnoDB都会维护B+树的,多加很多索引只会导致效率降低!

  不要用重复的索引,比如有个联合索引是a,b,你又整个a列的普通索引。那不是搞事么?

  不要在索引上用函数和like

一颗聚集索引B+树可以放多少行数据?

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/wpwwwp.html