InnoDB存储引擎-索引与算法

小白白白_
759 阅读10分钟

本文是阅读笔记，前几篇见个人首页，并且能力有限，望指出不足。

5.1. InnoDB存储引擎索引概述

InnoDB支持以下集中常见的索引：

  • B+树索引
  • 全文索引
  • 哈希索引 前面提到过，哈希索引是InnoDB为了优化B+树索引而自动生成的自适应哈希索引，所以无法做到人为干预。

B+树索引就是传统意义上的索引，但是需要明确一点，B+树无法找到具体某一行记录，它只能找到记录所在的页，然后把页载入到内存，再进行查找。

5.2. 数据结构与算法

5.2.1. 二分查找法

不说了，数据结构基础

5.2.2. 二叉查找树和平衡二叉树

也不说了，不是本节重点。

5.3. B+树

在B+树中，所有记录节点都是按照键值顺序存放在同一层的叶子结点上。各节点之间通过双向链表链接，节点内就是多个页，页与页之间也是通过双向链表链接的。

3BF07D0F-AE1B-4438-B8C3-6322A6F84855_1_105_c.jpeg

5.4. B+树索引

B+索引在数据库中有一个特点就是高扇出性。聚族索引和辅助索引的区别在于，聚族索引存放的是是实际的数据，而辅助索引存放的是聚族索引的键(或者说表的主键)。

5.4.1. 聚集索引(又名：聚族索引/聚簇索引)

在这里，首先明确一个概念，就是，聚族索引之所以被称为聚族索引，是因为聚族索引一般会包含实际的行记录，有点像把行记录“聚集在一起”的意思；但是呢，索引里可以实现记录行数据的一般只有主键索引，所以主键索引一般也称为聚集索引，聚集索引也称为主键索引。

聚集索引就是按照每张表的主键生成一棵B+树，同时叶子结点存放的是行记录数据，因此也将聚集索引的叶子结点称为数据页。

由于实际上数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。一般来说，优化器倾向于使用聚集索引，因为它能在B+树的叶子结点上直接获得数据。

聚集索引的另一个优点是，对于主键的排序查找和范围查找非常快。另外，因为B+树索引基于双链表，所以可以快速找到最后一个数据页，此时插入操作会很快。

另一个优势是范围查询，因为B+树的性质，所以可以很容易得到某一范围所在的页，然后完成读取即可。

5.4.2. 辅助索引

辅助索引的数据部分存放的是主键。每次通过辅助索引索引数据时，需要定位到主键，然后再通过主键索引定位到数据。

5.4.3. B+树索引的分裂

5.4.4. B+树索引的管理

可以通过ALTER TABLE或CREATE/DROP INDEX来创建或删除索引，同时用户可以设置对整个列的索引，也可以只设置某个列的开头部分数据作为索引。

5.5. Cardinality值

一般来说，并不是所有的查询条件都需要建立索引，如果某些数据本身在SELECT语句中用的不多，那么为其创建索引可能是一种负担(索引的维护代价不小，且占用磁盘空间)。

那么如何判断是否要为某个列创建索引呢？在这里引入了Cardinality值的概念，该值用来记录列是否具有高选择性，具有高选择性的列具有创建索引的价值。尤其是在高选择性的列中取出其中一小部分的数据时。

5.5.1. InnoDB对于Cardinality的计算

介于性能考量，InnoDB对于Cardinality的计算是通过样本采样完成的。对于Cardinality的计算发生在两个时期：

  • INSERT时
  • UPDATE时 因为不可能在每次操作发生时都进行更新，所以InnoDB的采样策略是：
  • 表中1/16的数据已发生变化。
  • stat_modified_counter > 2*10^9

如果数据一直是某一行的更新，那么第一个策略就不适用，所以引入了第二个策略。

InnoDB默认对8个页进行采样，因此可能和实际结果有误差，另外，每次都是随机8个页，所以每次的结果都可能不同。

5.6. B+树索引的使用

5.6.1. 联合索引

联合索引的创建和单个索引很像，而且它的索引排序方式取决于各个索引的排列顺序，首先按照第一个索引排序，一直的话根据第二个排序，以此类推...

比如有列a, b, c。且有联合索引(a, b, c)。那么排序顺序就是先a，后b，最后c。

如果想要通过第一个索引查询，然后以第二个索引排序，那么通过这两个列的联合索引可以很快得到，因为第二个索引已经是有序的了；但是如果想通过联合索引进行第二个索引的索引，这是不可以的。

比如有列a, b, c。且有联合索引(a, b, c)。语句：

SELECT xxx FROM tableA WHERE tableA.a = axxx ORDER BY tableA.b;

此时就可以直接得到以a为索引的，以b有序的结果。

5.6.2. 覆盖索引

因为联合索引的某一个索引的后面的索引包含了实际这列的数据，因此想要获得联合索引包含的列的值，可以很快获得(以排前面的索引为索引，后面的索引所指示的列为需要的值的时候)。

比如有列a, b, c。且有联合索引(a, b, c)。语句：

SELECT b, c FROM tableA WHERE tableA.a = axxx;

此时可以通过联合索引(a, b, c)直接得到需要的结果。

5.6.3. 优化器不选择使用索引

有时，SQL优化器并不会使用索引来SELECT，即使存在索引。原因在于：对于辅助索引，如果是SELECT返回一个还好，如果SELECT的结果是多个，那这多个结果还需要再次去主键索引查询，而此时这些结果在辅助索引可能是顺序IO，但是再去聚集索引查询的时候，可能就是随机IO，同样会降低性能。

所以在辅助索引查询的数据量不大时，优化器会使用辅助索引->主键索引这样的顺序；但是如果查询的数据很大，优化器会使用主键索引进行大范围匹配查找，因为顺序读性能高于随机读。

另外，现在固态硬盘盛行，其随机读性能蛮好的，此时可以通过FORCE INDEX来强制使用索引。

5.6.4. 索引提示

MySQL支持索引提示来告诉优化器使用哪个索引。一般来说，需要用到索引提示的有以下两个场景：

  • MySQL错误的选择了某个索引，导致SQL语句执行缓慢。这在新版的MySQL中不太可能发生。
  • 优化选择器因为索引太多了，而在选择索引上浪费了太多的时间。 当然了，提示仅仅是提示，优化器还是可能不按规矩出牌，所以必要时可以使用FORCE INDEX。

5.6.5. Multi Range Read优化

MultiRange Read优化可以带来如下三个好处：

  • 减少磁盘IO操作，化随机IO为顺序IO。
  • 减少缓冲区中页被换出的次数。
  • 批量处理对键值的查询操作。

为什么Multi-Range Read有这样的优点？答案在其实现：

  • 首先把辅助索引索引到的数据放在缓存中。
  • 对缓存中的数据以主键进行排序。
  • 使用主键序访问聚集索引得到实际数据。

关于前两个优点，通过其实现便可得到解释，其中第二点是因为，随机IO会频繁地导致页被换出，被载入，而顺序IO不会。

关于第三点，如果想要查询1000 <= a <= 2000 && b == 1500这样的数据，则可以使用Multi-Range Read进行优化成键值对的形式，然后使用联合索引进行索引(如果有的话)。

5.6.6. Index Condition Pushdown(ICP)优化

使用ICP优化后，可以在查询时进行WHERE筛选，而不是在把全部数据加载到内存后再进行筛选。这样可以提升很大的性能。

5.7. 哈希算法

这里仅仅提一下InnoDB的哈希算法的部分细节。

InnoDB对于哈希碰撞，采用了拉链法(链表法)。对于如何获得散列值，这里使用了除法散列的方式。

除法散列：k = index mod m。一般来说，会设置m = 大于缓冲区页数*2的最小质数。比如有640(10MB / 16KB = 640)个缓冲区，则可以设置>2 * 640 = 1280的质数，比如1399，为m。

此外，缓冲区中的每个哈希页都有一个指针指向下一个哈希页，这用来处理哈希碰撞。

此外，每个数据页的编码值计算方法=space_id << 20 + space_id + offset。然后使用哈希函数散列到对应的哈希页中去。

5.7.1. 自适应哈希索引

哈希索引只能用于等值查询。自适应哈希索引是为了更好地处理热点数据的索引，类似OS里面内存映射的TLB作用。自适应哈希由InnoDB维护，无法手动操作。

5.8. 全文索引

全文索引指的是把存储于数据库中的整个文本中的任意信息找出来的技术。

5.8.1. 倒排索引

倒排索引是全文索引的索引组织形式，就像B+树之于辅助索引，聚集索引一样。它记录了单词与单词所在文档位置之间的映射关系，因为一个单词一般会在多个地方出现，所以这是一种一对多的关系，通常使用关联数组实现。

关联数组有两种实现形式：

  • inverted_file_index{word, docId}
  • full_inverted_index{word, (docId, position)} 很明显，后者更加精确，但是也会消耗更多的空间。

5.8.2. InnoDB的全文索引

InnoDB支持full_inverted_index类型的全文索引。在InnoDB中，把(docId, position)视为一个ilist，所以在全文索引表中，有两个列，一个是word字段，一个是ilist字段，并且在word上设有索引。

全文索引表称为辅助表(Auxiliary Table)，为了提高全文索引的并行性，使用6个辅助表。每个表根据word的Latin编码进行分区。

Auxiliary Table是持久化的，所以引入了FTS Index Cache进行缓冲，提高速度。FTS Index Cache是一个红黑树结构，其根据(word, ilist)进行排序。一般插入操作会先插入到FTSIC中，然后等待InnoDB的批量更新，将其写回到磁盘。而对辅助表进行查询时，会先把FTSIC合并到辅助表中，在进行查询，类似InsertBuffer。

InnoDB总是在事务提交时把分词写入到FTSIC中。此外，为了支持全文索引，还必须有一个列与word进行一一映射，且这个列类行为BIGINT UNSIGNED NOT NULL，然后为其添加一个Unique Index。一般其名为DOC_ID。

另外，对于分词的删除操作，仅仅删除FTSIC中的记录，而不会删除Auxiliary Table中的数据。如果辅助表中真的有数据被删除了，则会把这个分词的DOC_ID存放到DELETE Auxiliary Table中。而使用OPTIMIZE TABLE则可以彻底删除。

此外，还有一个表，称为stopword，它会把这里面的词跳过，不进行分词索引，比如the，this等。

5.8.3. 全文检索

有三种全文检索的模式：

  • Natural Language：根据相关度排序。这也是默认的方式，相关性计算略去不表。
  • Boolean：关键词前后的字符有特殊含义，具体哪些字符有哪些含义略去不表(比如必须包含或必须不包含之类的)。
  • Query Expansion：会自动进行词义扩展，一般这需要隐藏知识的支持，比如搜索数据库，可以显示数据库，也可以显示MySQL，Oracle等相关的词。
avatar
文章
阅读
粉丝