MySQL 索引相关基本概念

前言

InnoDB存储引擎支持以下几种常见索引：B+Tree索引，哈希索引，全文索引

一. B+ Tree 索引

1. 概念

B+Tree 是一种为了快速检索数据的一种数据结构，但数据才是我们真正需要的数据，索引只是辅助数据，用来便于查找我们需要的数据。

2. 聚集索引/聚簇索引

Innodb使用的聚簇索引，将表中主键作为索引从而构建一颗B+Tree，并将整表数据的行记录存放在B+Tree的叶子结点。从而延伸出索引即数据，数据即索引。

聚簇索引是由表中主键构成，所以一张表只有一个聚簇索引。如：

alter table test_table on PRIMARY KEY (id);

聚簇索引的叶子结点为数据页。数据页存放着完整的每行数据

延伸出聚簇索引优势：

1. 通过聚簇索引即可获取完整的整行记录。
2. 对于主键排序查询及范围查询，速度是非常快的，性能是非常高效的。

如果未定义主键索引，MySQL会使用唯一索引，没有唯一索引，MySQL会创建一个隐含列RowID作为主键，用这个主键来建立聚簇索引。

3. 辅助索引/二级索引

聚簇索引使用在搜索条件为主键值的时候，因为B+Tree都是按照主键进行排序的。

若以别的列作为搜索条件时，就需延伸出二级索引。如：

create index idx_a on test_table (a);

每建立一个索引，就需要一个B+Tree。

对于二级索引，叶子节点不包含行记录的完整数据，叶子节点包含键值，每个叶子节点的索引行还包含一个书签（Bookmark），书签用来告诉InnoDB如何回查对应索引行的完整数据。InnoDB引擎的二级索引书签就是对应行数据的聚簇索引。

4. 回表

辅助索引不影响数据在聚簇索引的组织结构（聚簇索引的叶子节点存储着完整的数据行），因此表中可以有多个辅助索引。

当返回的列字段不符合辅助索引所包含的索引行时，InnoDB引擎会遍历辅助索引并通过其叶子级的指针，获得聚簇索引（Bookmark）指向的主键，通过聚簇索引来获取对应索引行的完整记录。这个过程称为回表。

根据辅助索引的值查询一条完整的行记录需要使用2颗B+Tree：

1. 一次辅助索引的B+Tree
2. 一次聚簇索引的B+Tree

若把所有行用于辅助索引创建，是不是不用回表了？

是的，但太占磁盘空间了，相当于每建立一个辅助索引，需要把表中所有数据拷贝一遍。每次对数据的变化要在所包含数据的索引中进行一次的全部修改，性能消耗非常大。

回表次数越少，性能越高。若回表次数越多，二级索引性能越低，有时候甚至会使用全局扫表，也不会使用二级索引。

对于全局扫表，二级索引+回表操作？

查询优化器通过事先对表中的记录计算一些统计数据，用这个结果根据查询条件来计算回表的记录数，回表记录数越多，越倾向全局扫表，反之倾向二级索引+回表操作。

5. 联合索引/复合索引

前面提及的辅助索引/二级索引，我们只对一个字段进行构建索引。但实际工作中，可以对多个字段进行构建索引，延伸出联合索引的概念。

多个列组合起来创建的索引，称为联合索引/复合索引，如：

create index idx_a_b on test_table (a,b);

在这个index(a,b)，包含两个意思：

1. 把各个记录，按照a的列进行排序
2. 在a列值相同情况下，进行b列值的排序

6. 覆盖索引

InnoDB存储引擎支持覆盖索引（covering index，别称：索引覆盖）。

即从辅助索引中可以得到查询的记录，而不需要查询聚簇索引中的记录（回表）。

覆盖索引带来的好处就是，若查询指定的几个字段的话，辅助索引不包含完整索引行记录信息，大小要远小于聚簇索引，因此可以减少大量的IO操作。

覆盖索引不是索引类型的一种。

二. 哈希索引

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

InnoDB存储引擎内部自己去监控表，如果监控到某个索引经常被使用，那就会认为是个热数据，内部创建一个hash索引，称之为自适应hash索引（Adaptive Hash Index，AHI）。

创建后，下次查询若还用到此索引，InnoDB会通过hash算法推导出记录的地址，直接一次就能查询数据，相对于重复查询B+Tree索引中查询3，4次节点效率显著提高。

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

show engine innodb status

show engine innodb status;

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 5 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

哈希索引只能用来搜索等值，也就是所谓的精确匹配查询。如：

select * from test_table where a = xxx

而对于其他的查找类型，如范围查询，不能使用哈希索引的。

因此在show engine innodb status的结果中，出现了non-hash searches/s的情况。通过hash searches/s;non-hah searches/s可以大概了解到哈希索引使用的效率

开启/关闭自适应哈希索引，默认AHI是开启状态。

1. 使用my.cnf或my.ini配置文件：

   编辑MySQL的配置文件（通常为my.cnf或my.ini）, 在[mysqld]或[innodb]添加以下行：

   [mysqld]
   innodb_adaptive_hash_index=OFF
   

   或

   [innodb]
   innodb_adaptive_hash_index=OFF
   

2. 在MySQL客户端：

   -- 关闭
   SET GLOBAL innodb_adaptive_hash_index = OFF;
   -- 开启
   SET GLOBAL innodb_adaptive_hash_index = ON;
   

3. 在启动MySQL服务时：

   mysqld --innodb_adaptive_hash_index=OFF
   

三. 全文索引

将存储于数据库的整本书/整篇文章中的任意内容信息查找出来，称为全文索引（Full-Text Search），可根据全文中的章，节，段，句等信息，进行各种统计及分析。如ES，Solr等就是全文检索引擎，底层是基于Apache Lucene。

全文索引通常只适用于VARCHAR, CHAR, 和 TEXT类型的列。每张表只能有一个全文检索索引，MySQL的全文搜索支持英文和其他一些语言，但对于中文等非拉丁字母语言的支持有限。在这种情况下，可能需要使用第三方插件（如myisam_ft或innodb_ft）或外部全文搜索引擎（如Elasticsearch）来实现更高级的全文搜索功能。

创建全文索引：

alter table test_table add fulltext(content);

全文搜索

select * from test_table where match(content) against(xx);