mysql索引概念要点

InnoDB存储引擎支持以下几种常见的索引：B+树索引、全文索引、哈希索引，其中比较关键的是B+树索引

B+树特征

相同节点数量的情况下，B+树高度远低于平衡二叉树

非叶子节点只保存索引信息和下一层节点的指针信息，不保存实际数据记录

每个叶子页（LeafPage）存储了实际的数据，比如上图中每个叶子页就存放了3条数据记录，当然可以更多，叶子节点由小到大（有序）串联在一起，叶子页中的数据也是排好序的

邻的叶子节点之间用指针相连

叶子节点中的数据在物理存储上完全可以是无序的，仅仅是在逻辑上有序（通过指针串在一起）

B树也B+树的差别是，B树的非叶子节点也需要存放数据

而B+树的话，数据只存在叶子节点上，同时相邻的叶子节点有链表的结构(MySQL中实现的B+树，叶子节点之间的链表是双向链表)

B* 树的话，与B+树的差别就是在非叶子节点之间，也有相互的指针指向。Oracle中使用的是B* 树

 

B+树含有非常高的扇出（fanout），通常超过100，在查找一个记录时，可以有效的减少IO操作；

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

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

聚集索引/聚簇索引

将表的主键用来构造一棵B+树，并且将整张表的行记录数据存放在该B+树的叶子节点中。

通过过聚集索引能获取完整的整行数据。另一个优点是：对于主键的排序查找和范围查找速度非常快。

如果我们没有定义主键呢？MySQL会使用唯一性索引，没有唯一性索引，MySQL也会创建一个隐含列RowID来做主键，然后用这个主键来建立聚集索引。

辅助索引/二级索引

叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签( bookmark)，也就是相应行数据的聚集索引键。

一个select查询语句在执行过程中一般最多能使用一个二级索引。

回表

通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引）来找到一个完整的行记录。这个过程也被称为 回表 。也就是根据辅助索引的值查询一条完整的用户记录需要使用到2棵B+树----一次辅助索引，一次聚集索引

回表的记录越少，性能提升就越高，需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引。

联合索引/复合索引

index(note,b) 先把各个记录按照note列进行排序。在记录的note列相同的情况下，采用b列进行排序。最佳左前缀法则的由来。

覆盖索引

覆盖索引(covering index，或称索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录(回表)

哈希索引

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

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

哈希索引只能用来搜索等值的查询。

全文索引

全文索引也叫倒排索引，将文档中包含的关键字全部提取处理，然后再将关键字和文档之间的对应关系保存起来，最后再对关键字本身做索引排序。用户在检索某一个关键字是，先对关键字的索引进行查找，再通过关键字与文档的对应关系找到所在文档。

MySQL5.6.x系列支持,但是全文索引功能比较弱。

 高性能的索引创建策略

索引列的类型尽量小

应该选择选择性/离散性高的列，不重复的索引值和数据表的记录总数的比值。（SELECT count(DISTINCT name)/count(*) FROM person;）

多列索引（组合索引）的列顺序至关重要。对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。

三星索引

条件如下：

索引将相关的记录放到一起则获得一星 （比重27%）：扫描的索引片宽度就会缩至最短

如果索引中的数据顺序和查找中的排列顺序一致则获得二星（排序星） （比重27%）：group by、 order by，如果查询所需的顺序与索引是一致的（索引本身是有序的）

如果索引中的列包含了查询中需要的全部列则获得三星（宽索引星） （比重50%）：索引中所包含了这个查询所需的所有列（包括 where 子句和 select 子句中所需的列，也就是覆盖索引）