InnoDB中的索引

InnoDB中的索引
InnoDB中的索引是按照B+树来组织的，我们知道B+树的叶子节点用来放数据的，但 是放什么数据呢？索引自然是要放的，因为B+树的作用本来就是就是为了快速检索数据 而提出的一种数据结构，不放索引放什么呢？但是数据库中的表，数据才是我们真正需 要的数据，索引只是辅助数据，甚至于一个表可以没有自定义索引。InnoDB中的数据到 底是如何组织的？
聚集索引/聚簇索引 InnoDB中使用了聚集索引，就是将表的主键用来构造一棵B+树，并且将整张表的行 记录数据存放在该B+树的叶子节点中。也就是所谓的索引即数据，数据即索引。由
于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。 聚集索引的叶子节点就是数据页。换句话说，数据页上存放的是完整的每行记录。 因此聚集索引的一个优点就是：通过过聚集索引能获取完整的整行数据。另一个优 点是：对于主键的排序查找和范围查找速度非常快。 如果我们没有定义主键呢？MySQL会使用唯一性索引，没有唯一性索引，MySQL也会 创建一个隐含列RowID来做主键，然后用这个主键来建立聚集索引。

辅助索引/二级索引
上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据 都是按照主键进行排序的,那如果我们想以别的列作为搜索条件怎么办？我们一般 会建立多个索引，这些索引被称为辅助索引/二级索引。
对于辅助索引(Secondary Index，也称二级索引、非聚集索引)，叶子节点并不包 含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还 包含了相应行数据的聚集索引键。

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

为什么我们还需要一次回表操作呢?直接把完整的用户记录放到辅助索引d的叶子节 点不就好了么？如果把完整的用户记录放到叶子节点是可以不用回表，但是太占地 方了，相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍，这就有点太 浪费存储空间了。而且每次对数据的变化要在所有包含数据的索引中全部都修改一 次，性能也非常低下。 很明显，回表的记录越少，性能提升就越高，需要回表的记录越多，使用二级索引 的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引。 那什么时候采用全表扫描的方式，什么时候使用采用二级索引 + 回表的方式去执 行查询呢？这个就是查询优化器做的工作，查询优化器会事先对表中的记录计算一 些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录 数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。具体怎么算的，我们后面会详细说到。MRR从上文可以看出，每次从二级索引中读取到一条记录后，就会根据该记录的主键值执行回表操作。而在某个扫描区间中的二级索引记录的主键值是无序的，也就是说这些 二级索引记录对应的聚簇索引记录所在的页面的页号是无序的。
每次执行回表操作时都相当于要随机读取一个聚簇索引页面，而这些随机IO带来的 性能开销比较大。MySQL中提出了一个名为Disk-Sweep Multi-Range Read (MRR，多范围 读取)的优化措施，即先读取一部分二级索引记录，将它们的主键值排好序之后再统一执 行回表操作。 相对于每读取一条二级索引记录就立即执行回表操作，这样会节省一些IO开销。使用这 个 MRR优化措施的条件比较苛刻，所以我们直接认为每读取一条二级索引记录就立即执行回表操作。MRR的详细信息，可以查询官方文档。
联合索引/复合索引 前面我们对索引的描述，隐含了一个条件，那就是构建索引的字段只有一个，但实 践工作中构建索引的完全可以是多个字段。所以，将表上的多个列组合起来进行索
引我们称之为联合索引或者复合索引，比如index(a,b)就是将a,b两个列组合起来 构成一个索引。 千万要注意一点，建立联合索引只会建立1棵B+树，多个列分别建立索引会分别以 每个列则建立B+树，有几个列就有几个B+树，比如，index(note)、index(b)，就 分别对note,b两个列各构建了一个索引。 index(note,b)在索引构建上，包含了两个意思： 1、先把各个记录按照note列进行排序。
2、在记录的note列相同的情况下，采用b列进行排序

自适应哈希索引
InnoDB存储引擎除了我们前面所说的各种索引，还有一种自适应哈希索引，我们知 道B+树的查找次数,取决于B+树的高度,在生产环境中,B+树的高度一般为3-4层,故 需要3~4次的IO查询。 所以在InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么 就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)，创建以后，如果下次又查询到这个索引，那么直接通 过hash算法推导出记录的地址，直接一次就能查到数据，比重复去B+tree索引中查询三四次节点的效率高了不少。 InnoDB存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。注 意，对于自适应哈希索引仅是数据库自身创建并使用的，我们并不能对其进行干预。通过命令show engine innodb status\G可以看到当前自适应哈希索引的使用状况，如:

哈希索引只能用来搜索等值的查询,如 SELECT* FROM table WHERE index co=xxx。而对于其他查找类型,如范围查找,是不能使用哈希索引的, 因此这里会显示non- hash searches/s的统计情况。通过 hash searches: non- hash searches可以大概了解使用哈希索引后的效率。
由于AHI是由 InnoDB存储引擎控制的,因此这里的信息只供我们参考。不过我们可 以通过观察 SHOW ENGINE INNODB STATUS的结果及参数innodb_adaptive_hash_index
来考虑是禁用或启动此特性,默认AHI为开启状态。
什么时候需要禁用呢？如果发现监视索引查找和维护哈希索引结构的额外开销远远 超过了自适应哈希索引带来的性能提升就需要关闭这个功能。

同时在MySQL 5.7中，自适应哈希索引搜索系统被分区。每个索引都绑定到一个特 定的分区，每个分区都由一个单独的 latch 锁保护。分区由 innodb_adaptive_hash_index_parts 配置选项控制 。在早期版本中，自适应哈希 索引搜索系统受到单个 latch 锁的保护，这可能成为繁重工作负载下的争用点。 innodb_adaptive_hash_index_parts 默认情况下，该 选项设置为8。最大设置为 512。当然禁用或启动此特性和调整分区个数这个应该是DBA的工作，我们了解即 可。

全文检索之倒排索引

什么是全文检索（Full-Text Search）？它是将存储于数据库中的整本书或整篇文章中 的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词 等信息，也可以进行各种统计和分析。我们比较熟知的Elasticsearch、Solr等就是全文 检索引擎，底层都是基于Apache Lucene的。 举个例子，现在我们要保存唐宋诗词，数据库中我们们会怎么设计？诗词表我们可 能的设计如下：

要根据朝代或者作者寻找诗，都很简单，比如“select 诗词全文 from 诗词表 where作者=‘李白’”，如果数据很多，查询速度很慢，怎么办？我们可以在对应 的查询字段上建立索引加速查询。 但是如果我们现在有个需求：要求找到包含“望”字的诗词怎么办？用
“select 诗词全文 from 诗词表 where诗词全文 like‘%望%’”，这个意味着要 扫描库中的诗词全文字段，逐条比对，找出所有包含关键词“望”字的记录，。基 本上，数据库中一般的SQL优化手段都是用不上的。数量少，大概性能还能接受， 如果数据量稍微大点，就完全无法接受了，更何况在互联网这种海量数据的情况下 呢？怎么解决这个问题呢，用倒排索引。
比如现在有：
蜀道难（唐）李白 蜀道之难难于上青天，侧身西望长咨嗟。
静夜思（唐）李白 举头望明月，低头思故乡。
春台望（唐）李隆基 暇景属三春，高台聊四望。

鹤冲天(宋)柳永 黄金榜上，偶失龙头望。明代暂遗贤，如何向？未遂风云便，争 不恣狂荡。何须论得丧？才子词人，自是白衣卿相。烟花巷陌，依约丹青屏障。幸 有意中人，堪寻访。且恁偎红翠，风流事，平生畅。青春都一饷。忍把浮名，换了 浅斟低唱！
都有望字，于是我们可以这么保存

如果查哪个诗词中包含上，怎么办，上述的表格可以继续填入新的记录

其实，上述诗词的中每个字都可以作为关键字，然后建立关键字和文档之间的对应 关系，也就是标识关键字被哪些文档包含。 所以，倒排索引就是，将文档中包含的关键字全部提取处理，然后再将关键字和文 档之间的对应关系保存起来，最后再对关键字本身做索引排序。用户在检索某一个 关键字是，先对关键字的索引进行查找，再通过关键字与文档的对应关系找到所在 文档。 在存储在关系型数据库中的数据，需要我们事先分析将数据拆分为不同的字段，而 在es这类的存储中，需要应用程序根据规则自动提取关键字，并形成对应关系。 这些预先提取的关键字，在全文检索领域一般被称为term（词项），文档的词项提 取在es中被称为文档分析，这是全文检索很核心的过程，必须要区分哪些是词项， 哪些不是，比如很多场景下，apple和apples是同一个东西，望和看其实是同一个 动作。
MySQL中的全文索引
MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引。从InnoDB 1.2.x版本开 始，InnoDB存储引擎开始支持全文检索，对应的MySQL版本是5.6.x系列。 注意，不管什么引擎，只有字段的数据类型为 char、varchar、text 及其系列才可以建 全文索引。 不过MySQL从设计之初就是关系型数据库，存储引擎虽然支持全文检索，整体架构 上对全文检索支持并不好而且限制很多，比如每张表只能有一个全文检索的索引， 不支持没有单词界定符( delimiter）的语言，如中文、日语、韩语等。

所以如果有大批量或者专门的全文检索需求，还是应该选择专门的全文检索引擎，毕 竟Elastic靠着全文检索起家，然后产品化、公司化后依赖全文检索不断扩充产品线 和应用场景，并推出商业版本的解决方案然后融资上市，现在的市值已达100亿美 元（2021/12/06 -纽约证券交易所中的市值101.5亿美元）。 具体如何使用InnoDB存储引擎的全文检索，只提供简单的使用说明，更多的详情请自 行查阅相关官方文档或者书籍，我们不做任何技术支持。官方文档路径： https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
创建
创建表时创建全文索引

create table fulltext_test (
    id int(11) NOT NULL AUTO_INCREMENT,
    content text NOT NULL,
    tag varchar(255),
    PRIMARY KEY (id),
    FULLTEXT  KEY content_tag_fulltext(content,tag)  
) DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7

在已存在的表上创建全文索引

create fulltext index  content_tag_fulltext
    on fulltext_test(content,tag);
1
2

通过 SQL 语句 ALTER TABLE 创建全文索引

alter table fulltext_test
    add fulltext index  content_tag_fulltext(content,tag);
1
2

使用全文索引
和常用的模糊匹配使用 like + % 不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如

select * from fulltext_test 
    where match(content,tag) against('xxx  xxx');
1
2

Elastic发家小故事
多年前，一个叫做 Shay Banon 的刚结婚不久的失业开发者，由于妻子要去伦敦学 习厨师，他便跟着也去了。在他找工作的过程中，为了给妻子构建一个食谱的搜索 引擎，他开始构建一个早期版本。

直接基于 Lucene 工作会比较困难，所以 Shay 开始抽象 Lucene 代码以便 Java
程序员可以在应用中添加搜索功能。他发布了他的第一个开源项目，叫做“
Compass”。
后来 Shay 找到一份工作，这份工作处在高性能和内存数据网格的分布式环境中， 因此高性能的、实时的、分布式的搜索引擎也是理所当然需要的。然后他决定重写 Compass 库使其成为一个独立的服务叫做 Elasticsearch。
第一个公开版本出现在 2010 年 2 月，在那之后 Elasticsearch 已经成为 Github上最受欢迎的项目之一，代码贡献者超过300人。一家主营 Elasticsearch 的公司就此成立，他们一边提供商业支持一边开发新功能，不过 Elasticsearch 将永远开源且对所有人可用。
总结：MySQL有哪些索引类型
从数据结构角度可分为B+树索引、哈希索引、以及FULLTEXT索引（现在MyISAM和InnoDB 引擎都支持了）和R-Tree索引（用于对GIS数据类型创建SPATIAL索引）；
从物理存储角度可分为聚集索引（clustered index）、非聚集索引（non-clustered index）； 从逻辑角度可分为主键索引、普通索引，或者单列索引、多列索引、唯一索引、非唯一 索引等等。
面试题：什么是密集索引和稀疏索引？
面试中还会被问到什么是密集索引和稀疏索引。 密集索引的定义：叶子节点保存的不只是键值，还保存了位于同一行记录里的其他列的 信息，由于密集索引决定了表的物理排列顺序，一个表只有一个物理排列顺序，所以一 个表只能创建一个密集索引。 稀疏索引：叶子节点仅保存了键位信息以及该行数据的地址，有的稀疏索引只保存了键 位信息机器主键。 mysam存储引擎，不管是主键索引，唯一键索引还是普通索引都是稀疏索引，innodb存储 引擎：有且只有一个密集索引。 所以，密集索引就是innodb存储引擎里的聚簇索引，稀疏索引就是innodb存储引擎里的 普通二级索引。