1、MySQL索引及作用

  MySQL官网:索引(Index)是帮助MySQL高效获取数据的数据结构。索引是数据结构。

  一个索引就是一个B+树,加快数据查询的速度。一个select查询语句在执行过程中一般最多能使用一个辅助索引,即使在where条件中用了多个辅助索引。

2、InnoDB存储引擎支持的常见索引

2.1、B+树索引

2.2、哈希索引

  哈希索引只能用于等值查找。

  B+树的查找次数,取决于B+树的高度,在生产环境中,B+树的高度一般为3、4层,故需要3、4次的IO查询。

  InnoDB存储引擎内部自己去监控索引表,如果监控到某个索引经常用,那么就认为是热数据,然后内部自己创建一个hash索引,称之为自适应哈希索引( Adaptive Hash Index,AHI),创建以后,如果下次又查询到这个索引,那么直接通过hash算法推导出记录的地址,直接一次就能查到数据,比重复去B+tree索引中查询三四次节点的效率高了不少。

  InnoDB存储引擎使用的哈希函数采用除法散列方式,其冲突机制采用链表方式。注意,对于自适应哈希索引仅是数据库自身创建并使用的。

3、为什么哈希索引不适合做数据库索引

  1、不能实现范围查找,hash表只能匹配是否相等。

  2、组合索引可以支持部分索引查询,如(a,b,c)的组合索引,查询中只用到了a和b也可以查询的,如果使用hash表,组合索引会将几个字段合并hash,没办法支持部分索引;

  3、数据量大,hash冲突概率增加。

4、MySQL索引 – B+树

  B+树中的B是balance的含义,B+数是由平衡二叉树掩化而来。

4.1、B+树特征

  1、非叶子节点只保存索引信息和下一层节点的索引信息,不保存数据

  2、叶子也存储实际数据,相邻的叶子节点之间用指针相连,叶子节点由小到大(有序)串联在一起。

4.2、B+树

  MySQL中实现的B+树,叶子节点之间的链表是双向链表。

  

B+树优点:

1、在磁盘设备上,通过B+树可以有效的存储数据

  在InnoDB存储引擎中,默认定义的B+树的节点大小是16KB。在计算机中数据的读取按页(page)的整倍数,页的大小为4K。InnoDB每一次磁盘I/O,读取的都是 16KB的整数倍的数据。也就是说InnoDB在节点的读写上是可以充分利用磁盘顺序IO的高速读写特性。

2、所有记录都存储在叶子节点上,非叶子(non-leaf)存储索引(keys)信息;而且记录按照索引列的值由小到大排好了序。

3、B+树含有非常高的扇出(fanout),通常超过100,在查找一个记录时,可以有效的减少IO操作;

  *扇出:是每个索引节点(Non-LeafPage)指向每个叶子节点(LeafPage)的指针;

  *扇出数 = 索引节点(Non-LeafPage)可存储的最大关键字个数 + 1

4.3、B+树与B*树、B树的区别

1、B+树与B树

1.1、区别

  B树的非叶子节点也需要存放数据、没有将叶子结点用链表串联起来。

  

1.2、相较于B+树的缺点

  B树叶子节点、非叶子节点都会保存数据,这样导致在非叶子节点中能保存的指针数量变少,指针少的情况下要保存大量数据,只能增加树的高度,导致IO 操作变多,查询性能变低。

1.3、B+树与B树适用场景

  存在大量范围查询的场景,适合使用B+树(比如数据库);

  而对大量单个key查询的场景,可以考虑B树(比如NOSQL的MongoDB)。

2、B+树与B*树

  非叶子节点之间,也有相互的指针指向。Oracle使用的是B*树。

  

5、MySQL索引 – B+树索引

5.1、聚集索引/聚簇索引

  聚簇索引,将表的主键来构造一棵B+树,并将整张表的行数据存放在该B+树的叶子节点中。

  聚簇索引 => 索引即数据,数据即索引。

  利用数据库表的主键构建的,每张表只有一个聚簇索引。

5.2、辅助索引/二级索引/非聚集索引

  聚簇索引只能在搜索条件是主键值时才能发挥作用,因为B+树中的数据都是按照主键进行排序的。

  以非主键的列建立索引,这种索引被称为辅助索引/二级索引。

  每建立一个索引,就有一颗B+树。

  对于辅助索引(Secondary Index,也称二级索引、非聚集索引),叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点中的索引行中还包含了相应行数据的聚集索引键。

5.3、回表

  回表:通过辅助索引来寻找数据时,InnoDB存储引擎会遍历辅助索引并获取指向主键索引的主键,然后再通过主键索引(聚集索引)来找到一个完整的行记录。

  根据辅助索引的值查询一条完整的用户记录需要使用到2棵B+树 –> 一次辅助索引,一次聚集索引。

5.4、联合索引/复合索引

5.4.1、概念

  联合索引(复合索引): 多个列组合起来进行索引。

  建立联合索引只会建立1棵B+树,多个列分别建立索引会分别以每个列则建立B+树,有几个列就有几个B+树。

5.4.2、复合索引最左匹配原则

在a,b两列建立索引,index(a,b)在索引构建上,包含了两个意思:

  1、先把各个记录按照note列进行排序。

  2、在记录的note列相同的情况下,采用b列进行排序。

5.5、覆盖索引

  覆盖索引:从辅助索引中就可以得到查询的记录,而不需要查询聚集索引中的记录(回表)。

  辅助索引不包含整行记录的所有信息,故其大小要远小于聚集索引,因此可以减少大量的IO操作。但是,覆盖索引并不是索引类型的一种。

如:

  辅助索引中包含主键及指向下一层节点的指针,根据辅助索引只查询主键时,可直接通过辅助索引返回,不需要回表操作。

 

声明:本站所有文章,如无特殊说明或标注,均为本站原创发布。任何个人或组织,在未征得本站同意时,禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益,可联系我们进行处理。