MySQL索引（InnoDB）

一：数据在表中的组织存放方式

索引组织表

在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。在InnoDB存储引擎表中，每张表都有个主键，如果在创建表时没有显式地定义主键，则InnoDB存储引擎会按如下方式选择或创建主键：

• 首先判断表中是否有非空的唯一索引（Unique NOT NULL），如果有，则该列即为主键。
• 如果不符合上述条件，InnoDB存储引擎自动创建一个6字节大小的指针。
  另外，当表中有多个非空唯一索引时，InnoDB存储引擎将选择建表时第一个定义的非空唯一索引为主键。这里需要非常注意的是，主键的选择根据的是定义索引的顺序，而不是建表时列的顺序。

InnoDB逻辑存储结构

从InnoDB存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间
中，称之为表空间（Tablespace），表空间又由段（Segment）、区（Extent）、页（Page）组成。

1. 表空间：
   表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。在默认情况下InnoDB存储引擎有一个共享表空间ibdata1，即所有数据都存放在这个表空间内。若启用了参数innodb_file_per_table，则每张表内的数据可以单独放到一个表空间内。
2. 段：
   表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。因为InnoDB存储引擎表是索引组织的，因此数据即索引，索引即数据。那么数据段存放的是B+树的叶子节点（Leaf node segment），索引段存放的是B+树的非叶子节点（Non-leaf node segment）。
3. 区：
   区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4～5个区。在默认情况下，InnoDB存储引擎页的大小为16KB，即一个区中有64个连续的页。
4. 页：
   页是InnoDB磁盘管理的最小单位，在InnoDB存储引擎中，默认每个页的大小为16KB。从InnoDB 1.2.x版本开始，可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。若设置完成，则所有表中页的大小都为innodb_page_size，不可以对其再次进行修改。需要注意的是，==B+树索引只能找到记录所在的页，但是并不能定位到记录在页中的具体位置，这需要通过page directory的二分查找得到具体的记录。==然而，由于通过B+树索引得知记录所在的页后，InnoDB存储引擎会将页加载到缓冲池中，二分查找在内存中完成，速度很快，因此一般会忽略这个查询的开销。

二：索引的分类

1. B+树索引：
   B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。在数据库中，B+树的高度一般都在2～4层，这也就是说查找某一键值的行记录时最多只需要2到4次IO。这很不错，因为当前一般的机械磁盘每秒至少可以做100次IO，2～4次的IO意味着查询时间只需0.02～0.04秒。在数据库中，B+树索引还可以分为聚集索引和辅助索引，但不管是聚集索引还是辅助索引，其内部都是B+树的，即高度平衡的，叶子节点存放着所有的数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。
2. 哈希索引：
   InnoDB存储引擎支持的哈希索引是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。
3. 全文索引：
   全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。

对于哈希索引来说，由于它是自适应的，我们不用过多的去考虑。而全文索引：例如模糊查询 like…，对于这种业务我们最好是引入搜索引擎（例如：Elasticsearch）来操作，这样效果会更好。 所以我们把重点放在B+树索引上。

三：B+ 树索引

聚集索引（聚簇索引）

聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引，因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。

辅助索引

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

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。举例来说，如果在一棵高度为3的辅助索引树中查找数据，那需要对这棵辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问以得到最终的一个数据页。

四：B+ 树索引的使用

如何选择索引？

一张表上可以建立多个索引，在执行查询时具体选择哪一个索引，这是由SQL优化器来决定的。我们可以通过分析执行计划，来查看SQL优化器选择了哪一个索引。

通常，SQL优化器选择的索引都是高效的。但如果你确定要显示指定个索引来完成查询，则可以使用索引提示功能来实现这样的需求。

建议MySQL走索引I
SELECT * FROM 表名 use index(I) WHERE ......;

强制MySQL走索引I
SELECT * FROM 表名 force index(I) WHERE ......;

联合索引

摘自敖丙的文章：
原文链接：https://blog.csdn.net/qq_35190492/article/details/109257302

联合索引是指对表上的多个列进行索引。
以一个表为例：表 abc_innodb，id为主键索引，创建了一个联合索引idx_abc(a,b,c)。

CREATE TABLE `abc_innodb`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a`  int(11)     DEFAULT NULL,
  `b`  int(11)     DEFAULT NULL,
  `c`  varchar(10) DEFAULT NULL,
  `d`  varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_abc` (`a`, `b`, `c`)
) ENGINE = InnoDB;

select * from abc_innodb order by a, b, c, id;

联合索引的数据结构：

联合索引的查询过程：

select * from abc_innodb where a = 13 and b = 16 and c = 4;

最左匹配原则：

最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。

在联合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。
就像上面的查询，B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起。
可以说创建的idx_abc(a,b,c)索引，相当于创建了(a)、（a,b）（a,b,c）三个索引。

联合索引的最左前缀匹配原则：使用联合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配

回表：
回表大概就是我们有个主键为ID的索引，和一个普通name字段的索引，我们在普通字段上搜索：

sql select * from table where name = '张三'

执行的流程是先查询到name索引上的 “张三”，然后找到他的id是2，最后去主键索引，找到id为2对应的值。
回到主键索引树搜索的过程，就是回表。不过也有方法避免回表，那就是覆盖索引。

覆盖索引：
这个其实比较好理解，刚才我们是 select * ，查询所有的，我们如果只查询ID，那其实在Name字段的索引上就已经有了，那就不需要回表了。

覆盖索引可以减少树的搜索次数，提升性能，他也是我们在实际开发过程中经常用来优化查询效率的手段。

很多联合索引的建立，就是为了支持覆盖索引，特定的业务能极大的提升效率。

覆盖索引

覆盖索引并不是说是索引结构，覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况，在上面abc_innodb表中的组合索引查询时，如果我只需要abc字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。

覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。当发起一个被索引覆盖的查询时，在EXPLAIN的Extra列可以看到 “Using index” 的信息。

覆盖索引的情况：

未使用到覆盖索引：

五：哈希索引

InnoDB存储引擎使用哈希算法来对字典进行查找，其冲突机制采用链表方式，哈希函数采用除法散列方式。对于缓冲池页的哈希表来说，在缓冲池中的Page页都有一个chain指针，它指向相同哈希函数值的页。

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引（Adaptive Hash Index，AHI）。AHI是通过缓冲池的B+树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。

自适应哈希索引经哈希函数映射到一个哈希表中，因此对于字典类型的查找非常快速，但是对于范围查找就无能为力了。

个人理解是：哈希索引就像是建立一个缓存。（如果观察到建立哈希索引可以带来速度提升，则建立哈希索引。这就相当于建立一个缓存，把内容放到缓存中，下次查询时速度会变快）。

六：全文索引

倒排索引

全文检索通常使用倒排索引（inverted index）来实现。倒排索引同B+树索引一样，也是一种索引结构。它在辅助表（auxiliarytable）中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。这通常利用关联数组实现。（就像搜索引擎的分词一样）

因此在全文检索的表中，有两个列，一个是word字段（经过分词得到的关键字），另一个是ilist字段，并且在word字段上有设有索引。倒排索引需要将word存放到一张表中，这个表称为Auxiliary Table（辅助表）。在InnoDB存储引擎中，为了提高全文检索的并行性能，共有6张Auxiliary Table，目前每张表根据word的Latin编码进行分区。

Auxiliary Table是持久的表，存放于磁盘上。然而在InnoDB存储引擎的全文索引中，还有另外一个重要的概念FTS Index Cache（全文检索索引缓存），其用来提高全文检索的性能。FTS Index Cache是一个红黑树结构，其根据（word，ilist）进行排序。