MySQL索引

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

1、B+树索引

1.1 聚集索引/聚簇索引

InnoDB中使用了聚集索引，就是将表的主键用来构造一棵B+树，并且将整张表的行记录数据存放在该B+树的叶子节点中。也就是所谓的索引即数据，数据即索引。由于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。

聚集索引的叶子节点就是数据页。换句话说，数据页上存放的是完整的每行记录。因此聚集索引的一个优点就是：通过过聚集索引能获取完整的整行数据。另一个优点是：对于主键的排序查找和范围查找速度非常快。

注意：如果我们没有定义主键，MySQL会使用第一个唯一索引（UNIQUE）作为聚集索引；如果没有唯一索引，MySQL也会创建一个隐含列RowID来做主键，然后用这个主键来建立聚集索引。

1.2 辅助索引/二级索引

辅助索引（Secondary Index，也称非聚集索引），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

1.3 回表

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

辅助索引回表操作的原因：

• 如果我们直接把完整的行记录数据放到辅助索引是可以不用回表，但是太占地方了，相当于每建立一棵B+树都需要把所有的行记录数据再都拷贝一遍，浪费存储空间了。
• 每次对数据的变化要在所有包含数据的索引中全部都修改一次，性能也非常低下。

由于回表的记录越少，性能提升就越高，需要回表的记录越多，使用二级索引的性能就越低，因此在某些情况下全表扫描的效率高于二级索引。具体使用全表扫描还是采用二级索引 + 回表的方式查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，如回表记录较多，则更越倾于全表扫描，反之倾向于使用二级索引 + 回表的方式。

1.4 联合索引/复合索引

将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引

注意：每个索引只会建立一棵B+树，所以联合索引也是只建立一棵B+树

如：联合索引index(note,b)，在索引构建过程：

1. 先把各个记录按照note列进行排序。
2. 在记录的note列相同的情况下，采用b列进行排序

因此，最佳左前缀法则就是根据此原理产生

1.5 覆盖索引

InnoDB存储引擎支持覆盖索引(也称为索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要回表操作。由于辅助索引不包含整行记录的所有信息，其存储大小要远小于聚集索引，所以使用覆盖索引可以减少大量的IO操作，注意：覆盖索引并不是索引类型的一种。

2、哈希索引

2.1 哈希索引

哈希索引是另一种常见的数据库索引结构，通过哈希函数将关键字映射为哈希值，并将哈希值与数据的存储地址关联。由于内存的支持，底层结构就是链式哈希表，增删改查的时间复杂度都是O(1)，一断电就没了，因为内存搜索，哈希表是最快的。

优缺点：

• 适用于精确查找，查询性能较好，查询时间复杂度为O(1)；适用于静态数据，插入和删除操作较少时性能较好。
• 不适用于范围查找和排序查询；不支持数据的动态插入和删除，当数据频繁插入和删除时，哈希冲突可能导致性能下降。

2.2  InnoDB自适应哈希索引

InnoDB存储引擎还有一种自适应哈希索引，由于B+树的查找次数,取决于B+树的高度，在实际的环境中，B+树的高度一般为3、4层，故需要3、4次的IO查询。

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

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

3、全文索引

3.1 全文检索（Full-Text Search）

它是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。我们比较熟知的Elasticsearch、Solr等就是全文检索引擎，底层都是基于Apache Lucene的。

3.2 倒排索引（Inverted index）

也被称为反向索引、置入档案或反向档案，是一种索引方法，被用来存储在全文搜索下某个单词在一个文档或者一组文档中与存储位置的映射。它是文档检索系统中最常用的数据结构。

3.3  MySQL各版本全文索引

在MySQL 5.6版本以前，只有MyISAM存储引擎支持全文引擎。从InnoDB 1.2.x版本开始，InnoDB存储引擎开始支持全文检索，对应的MySQL版本是5.6.x系列，在5.7.6版本，MySQL内置了ngram全文解析器，用来支持部分亚洲语种的分词。不过MySQL从设计之初就是关系型数据库，存储引擎虽然支持全文检索，整体架构上对全文检索支持并不好而且限制很多，比如每张表只能有一个全文检索的索引，不支持没有单词界定符( delimiter）的语言，如中文、日语、韩语等。所以MySQL中的全文索引功能比较弱，不建议使用

4、总结

• 一个索引就是一个B+树，索引让我们的查询可以快速定位和扫描到我们需要的数据记录上，加快查询的速度 。
• 一个select查询语句在执行过程中一般最多能使用一个二级索引，即使在where条件中用了多个二级索引。