MySQL索引(1)：InnoDB存储引擎概述

最新推荐文章于 2021-01-21 11:15:27 发布

Jakob_Hu

最新推荐文章于 2021-01-21 11:15:27 发布

阅读量457

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/Jakob_Hu/article/details/102689777

版权

MySQL 专栏收录该内容

16 篇文章 2 订阅

订阅专栏

InnoDB引擎概述

InnoDB常见索引
B+索引相关数据结构
哈希算法及自适应哈希索引
全文索引

索引是数据库应用和开发的重要方面。索引过少，查询性能会有影响；反之，过多，对应用的性能也有产生影响。在实际开发中，会在创建索引时取一个平衡点。

InnoDB常见索引

InnoDB支持如下常见索引，

B+树索引
全文索引
哈希索引

InnoDB支持的哈希索引时自适应的，根据表的使用情况自动为表生成哈希索引，不能人为干预在表中创建哈希索引。

B+树索引时传统意义上的索引，是目前关系型数据库中最常用且最有效的索引，其结构类似于二叉树，依据键值找到数据。需要注意的一点，B+树索引能找到的只是被查找数据所在的页，把该页读入内存中，再在内存中寻找相应的行数据。

B+索引相关数据结构

对B+树索引相关的数据结构和算法总结如下，

1）二分法查找

下图引自《MySQL技术内幕》5.2节内容，
在这里插入图片描述
对上面10个数而言，使用顺序查找法的平均查找次数为 $\frac{1+2+3+...+10}{10}=5.5$ 次；而二分查找的平均查找次数为 $\frac{4+3+2+4+3+1+4+3+2+3}{10}=2.9$ 次。最坏的情况下，顺序查找的次数为10次；而二分法查找次数为4次。

2）二叉查找树和平衡二叉树

在这里插入图片描述
以中序遍历的顺序 2、3、5、6、7、8对每个数进行查找，顺序查找法所用的平均次数是 $\frac{1+2+3+...+6}{6}=3.3$ ；而二叉树查找的平均查找次数为 $\frac{3+3+3+2+2+1}{6}=2.3$ 次。

但是二叉树的缺点是可能出现不平衡的情况，如下图，
在这里插入图片描述
以上图为例，二叉树查找的平均查找次数为 $\frac{1+2+3+4+5+5}{6}=3.16$ 次，与顺序查找相比并不存在优势。

于是引入了平衡树的概念，平衡树首先满足二叉树的定义，同时满足任何节点的两棵子树的高度差最大为1。
在这里插入图片描述
平衡树插入新值后会进行相应的左旋、右旋来维持树的平衡。但是维护平衡的代价是很大的，尤其是数据很多的时候。

3）B+树

B+树由B树和索引顺序访问方法(ISAM)演化而来，对B+树的简要说明如下，

B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树
所有记录节点都是按键值的大小顺序存放在同一层叶节点上
各叶节点之间使用双向指针连接

下图是一个高度为2的B+树，每页可存放4条记录，扇出为5(节点指向子节点的指针数，此处可简单理解为子节点数)，
在这里插入图片描述
叶节点一般书中使用"Leaf Page"表示，中间节点一般使用"Index Page"表示。

B+树添加节点

插入节点后必须保证插入后叶节点中记录依然保持顺序，插入节点可能遇上3种情况，

Leaf Page未满，Index Page未满
直接将记录插入到B+树中即可，如在上面的树中插入键值28，
Leaf Page已满，Index Page未满
具体插入步骤如下，
I. 拆分Leaf Page
II. 将Leaf Page中间的节点放入Index Page中
III. 小于中间节点的记录放在左边；大于等于中间节点的记录放在右边
如在上面的树中插入键值70，

如果插入70，则之前的Leaf Page变为50、55、60、65、70，将中间值60移动到Index Page中拆分叶节点。
Leaf Page已满，Index Page已满
具体插入过程如下，
I. 拆分Leaf Page
II. 小于中间节点的记录放左边，大于等于中间节点的记录放右边
III. 中间节点放入到上一层Index Page中
IV. 拆分Index Page
V. 小于中间节点的记录放左边，大于等于中间节点的记录放右边
VI. 中间节点放入上一层Index Page中
如在上面的树中插入键值95，

插入95，原本的Leaf Page变为75、80、85、90、95，中间节点是85，将85放入到上一层Index Page中。
插入85，原本的Index Page变为20、50、60、75、85，中间节点是60，将60提取出来放入上一层Index Page，如果上一层没有Index Page，则新建一个Index Page。

B+树删除节点

B+树使用填充因子(fill factor)控制树删除变化，50%是填充因子的最小值。B+树删除操作需要保证删除节点后记录依旧保持顺序，删除节点依据节点删除后填充因子分为2种情况，其中第二种情况又分为两种情况。与插入不同的是，删除依据填充因子的变换衡量，

Leaf Page不小于填充因子，Index Page不小于填充因子
直接将记录从Leaf Page中删除，如果该节点还是Index Page中的节点，则用该节点的右节点代替。
在之前的树中删除键值70，

70所在的节点中含有60、65、70，删除70后剩余两个元素，不小于填充因子。同时，70不是所在节点的父节点中的元素，即70不在Index Page中，所以直接删除即可。删除70对其Index Page的填充因子没有影响。
之后，再删除键值25，

25所在的节点中含有25、28、30，删除25后剩余两个元素，不小于填充因子。同时，25是所在节点的父节点中的元素，即25在Index Page中，所以删除25，同时将25右边的节点28放入到Index Page中原来存放25的位置。将28填充到Index Page后对该节点的填充因子没有影响。
Leaf Page小于填充因子
情况1：Index Page不小于填充因子
合并该Leaf Page和它的兄弟节点，如果删除的键在Index Page中，更新Index Page。上面的树中不存在这种情况，就不进行演示。
情况2：Index Page小于填充因子
具体删除步骤如下，
I. 合并Leaf Page及其兄弟节点
II. 更新Index Page
III. 合并Index Page及其兄弟节点
假设删除键值60，

删除60后，节点从60、65变为65，此时填充因子小于50%，合并该节点及其兄弟节点，得到65、75、80。因为75是Index Page中的节点，从Index Page中删除75，此时Index Page变为85，小于填充因子。
合并Index Page及其兄弟节点，得到新的Index Page为28、50、85。合并后在 $[50, 85)$ 的区间内存在一个含有5个元素的Leaf Page，50、55、65、75、80。
该Leaf Page已满，但是Index Page未满，故将其中间元素65放入到Index Page中，同时拆分Leaf Page得到上图中的树。

哈希算法及自适应哈希索引

MySQL的哈希表与Java的哈希表是类似的，底层使用数组和链表实现，具体可参考Java的HashMap源码

1）B+索引与哈希索引对比

两种索引概述
B+树是一个平衡的多叉树。B+树从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动。
哈希索引采用一定的哈希算法，把键值换成新的哈希值，检索时不需要类似B+树那样从根节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置
哈希索引优势
等值查询。哈希索引具有绝对优势（前提是没有大量重复键值，如果大量重复键值时，哈希索引的效率很低，因为存在所谓的哈希冲突问题。）
哈希索引不适用场景
1）不支持范围查询
2）不支持索引完成排序
3）不支持联合索引的最左前缀匹配规则

2）自适应哈希索引

InnoDB引擎中默认使用的是B+树索引，它会实时监控表上索引的使用情况，如果认为建立哈希索引可以提高查询效率，则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引（在InnoDB中默认开启自适应哈希索引），通过观察搜索模式，MySQL会利用index key的前缀建立哈希索引，如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。(参考自博文)

3）哈希索引注意事项

在某些工作负载下，通过哈希索引查找带来的性能提升远大于额外的监控索引搜索情况和保持这个哈希表结构所带来的开销。

但某些时候，在负载高的情况下，自适应哈希索引中添加的read/write锁也会带来竞争，比如高并发的join操作。like操作和%的通配符操作也不适用于自适应哈希索引，可能要关闭自适应哈希索引。

全文索引

1）全文索引概述

在基础优化索引相关优化中提及了InnoDB引擎在模糊查询时无法使用索引的情况。

下面两行语句中，InnoDB支持的是第一条这种前缀查询的语句，

# sql1
select * from blog where content like 'xxxx%';

# sql2
select * from blog where content like '%xxxx%';

但是在实际开发中，第二种情况更为使用，因为有时搜索是在全文本范围内进行的。

MySQL5.7.6开始，MySQL内置了ngram全文解析器，支持了对英文以外的语言的全文检索。

例如，用ngram全文解析器对“生日快乐”进行分词，

n=1: '生', '日', '快', '乐' 
n=2: '生日', '日快', '快乐' 
n=3: '生日快', '日快乐' 
n=4: '生日快乐'

MySQL 中使用全局变量ngram_token_size来配置ngram中n的大小，它的取值范围是1到10，默认值是2。

设置全局变量ngram_token_size的方式，

mysqld命令

mysqld --ngram_token_size=2

修改MySQL配置文件

[mysqld] 
ngram_token_size=2

2）倒排索引

全文检索一般使用倒排索引实现，使用一个辅助表存储单词与单词在一个或多个文本中的位置之间的映射。如下例中表t的存储内容，
在这里插入图片描述
映射关系的实现有两种，

inverted file index

记录单词和单词出现在哪条文本中。
full inverted index

记录单词的出现文本及文本中对应的位置。

第二种形式更占据空间，但是可以更好的定位数据，能够在功能上进行拓展。

InnoDB采用的是第二种方式，为提高全文检索性能，InnoDB共有6张辅助表，每张表依据词语的编码进行分区。

3）创建全文索引

建表同时创建全文索引

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR (200),
    body TEXT,
    FULLTEXT (title, body) WITH PARSER ngram
) ENGINE = INNODB;

通过alter table命令创建

ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body) WITH PARSER ngram;

通过create index创建

CREATE FULLTEXT INDEX ft_index ON articles (title,body) WITH PARSER ngram;

大量的实验证明，对于大数量的表来说，先加载数据再来定义全文索引的速度要远远优于在一个已经定义好全文索引的表里面插入大量数据的速度。

4）全文检索模式

常用的全文检索模式有两种，

自然语言模式(NATURAL LANGUAGE MODE) ，
自然语言模式是MySQL 默认的全文检索模式。自然语言模式不能使用操作符，不能指定关键词必须出现或者必须不能出现等复杂查询
BOOLEAN模式(BOOLEAN MODE)
BOOLEAN模式可以使用操作符，可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询

自然语言模式

使用match和against函数实现全文检索。

match()中的字段名称要和fulltext中定义的字段一致
against()中定义的是所要搜索的字符串以及要求数据库通过哪种模式去执行全文索引的搜索查询。

下面2条SQL语句是等价的，

# sql1
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('一路一带' IN NATURAL LANGUAGE MODE);

# sql2 不指定模式，默认使用自然语言模式
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('一路一带');

都是使用自然语言模式在全文索引列 title和body中搜索"一路一带"的记录。

更高级的使用，

返回结果按照相关性排序

// 获取相关性的值
SELECT id,title,
MATCH (title,body) AGAINST ('手机' IN NATURAL LANGUAGE MODE) AS score
FROM articles
ORDER BY score DESC;

在这里插入图片描述
score为0表示不相关。

获取匹配结果记录数

SELECT COUNT(*) FROM articles
WHERE MATCH (title,body)
AGAINST ('一路一带' IN NATURAL LANGUAGE MODE);

BOOLEAN模式

相较于自然语言模式，该模式更高级更灵活，需要在against中明确指定。BOOLEAN模式全文检索同样需要保证match()中的字段名称要和fulltext中定义的字段一致，这一点在MySQL 5.7的官方文档中明确提及，
在这里插入图片描述

// 必须包含"腾讯"
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('+腾讯' IN BOOLEAN MODE);

// 必须包含"腾讯"，但是不能包含"通讯工具" 
SELECT * FROM articles 
WHERE MATCH (title,body) 
AGAINST ('+腾讯 -通讯工具' IN BOOLEAN MODE);

上面的语句分别返回结果，

sql1返回
sql2返回

BOOLEAN模式运算符及其使用方式，

运算符	说明
‘apple banana’	无操作符，表示或，要么包含apple，要么包含banana
‘+apple +juice’	必须同时包含两个词
‘+apple macintosh’	必须包含apple，但是如果也包含macintosh的话，相关性会更高。
‘+apple -macintosh’	必须包含apple，同时不能包含macintosh。
‘+apple ~macintosh’	必须包含apple，但是如果也包含macintosh的话，相关性要比不包含macintosh的记录低。
‘+今日头条 +(>NBA <火箭赢了)’	（）可以通过括号来使用字条件。找到有今日头条&(NBA\|火箭赢了)的数据，然后排序规则为：今日头条&NBA > 今日头条&NBA&火箭赢了 > 今日头条&火箭赢了
‘+apple +(>juice <pie)’	查询必须包含apple和juice或者apple和pie的记录，但是apple juice的相关性要比apple pie高。> :提高该字的相关性，查询的结果会排在比较靠前的位置；< :降低相关性，查询的结果会排在比较靠后的位置。>和<修饰的字符不一定必须出现
‘apple*’	查询包含以apple开头的单词的记录，如apple、apples、applet。
‘“some words”’	使用双引号把要搜素的词括起来，效果类似于like ‘%some words%’，例如“some words of wisdom”会被匹配到，而“some noise words”就不会被匹配。

5）全文检索注意事项

参考博文

只能在类型为CHAR、VARCHAR或者TEXT的字段上创建全文索引。
全文索引只支持InnoDB和MyISAM引擎。
MATCH (columnName) AGAINST ('keywords')。MATCH()函数使用的字段名，必须要与创建全文索引时指定的字段名一致。如上面的示例，MATCH (title,body)使用的字段名与全文索引ft_articles(title,body)定义的字段名一致。如果要对title或者body字段分别进行查询，就需要在title和body字段上分别创建新的全文索引。
MATCH()函数使用的字段名只能是同一个表的字段，因为全文索引不能够跨多个表进行检索。
如果要导入大数据集，使用先导入数据再在表上创建全文索引的方式要比先在表上创建全文索引再导入数据的方式快很多，所以全文索引是很影响TPS的。