MySQL(八)：MySQL之索引

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操作。但是，覆盖索引并不是索引类型的一种。

如：

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