InnoDB索引总结

最新推荐文章于 2024-07-25 21:19:23 发布

肃林

最新推荐文章于 2024-07-25 21:19:23 发布

阅读量144

点赞数

分类专栏： o和1的家：数据库文章标签：代理模式装饰器模式外观模式

本文链接：https://blog.csdn.net/qq_38289451/article/details/116570614

版权

o和1的家：数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

InnoDB索引总结

测试版本：MySQL-8.0.20
测试版本：MySQL-8.0.20

文章目录

InnoDB索引总结

1）索引类型

[单列索引]

//1新增
CREATE INDEX 索引名 ON  表名(字段名) 
ALTER TABLE 表名 ADD INDEX 索引名(字段名)
//2删除
ALTER TABLE 表名 DROP INDEX 索引名

单列索引是最基本的索引。即为某一列添加的索引。

[前缀索引]

//语句 其实区别只是在选择的字段名后添加括号，然后加上截取的长度即可
ALTER TABLE 表名 ADD INDEX 索引名(字段名(长度))

前缀索引：即选择字段的前几个字符用作索引，此时既加快查询速度，又不至于索引的字段过长。
当选择的索引列存储的字段过长时，索引会变得有大又慢，但又不能不建立该字段的索引时，便可以使用前缀索引。
但问题来了，该选择多长呢？为了合理性，这时便引入另外一个概念：选择性。

选择性：不重复的索引值占所有记录的比值。当这个比值越接近1时这个字段的数据越“离散”。当比值=1，所有的记录都不重复。例如唯一性索引。

计算方法：

SELECT count(distinct 字段)/count(*) FROM 表名
SELECT count(distinct left(name,长度))/count(*) FROM 表名

尝试不同的长度，然后比较 字段不截取 和 截取部分长度时 的差异，选择一个合适的值。

另外，需要注意：前缀索引不能用来排序或者分组。

[主键索引]

首先主键索引不允许为空，InnoDB会为表的主键添加主键索引，不需要操作员手动创建主键索引。
主键索引是一种聚集索引，但聚集索引不等于主键索引，这在之后会详细说。

[唯一索引]

语法和单列索引（普通索引）一样，区别只是添加了UNIQUE关键字

CREATE UNIQUE INDEX 索引名 ON  表名(字段名) 
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名(字段名(长度))

在日常使用上来说，一般唯一索引更多的还是为了避免数据的重复，大多数都不是因为提高查询速度。
另外在这里简单的提一下唯一约束和唯一索引的区别，尽管在实际的业务使用上二者几乎没有太多的区别。

在创建唯一约束的同时，会自动创建唯一索引。
从功能上来说，约束是对业务进行检查，如主键约束、check约束等等，而索引是一种提高查询速度的数据结构。
需要注意的是，在使用外键的时候，选用的字段必须是主键或者具备唯一约束的字段，单纯唯一索引是不行的。同样，从外键的角度上来看，外键也是在业务层面上进行限制，这里也能看出约束和索引是两种东西。

[组合索引]

可以添加UNIQUE的组合索引，并且也可以在字段后加括号限制长度，也就是前缀索引（MySQL8支持）

ALTER TABLE 表名 ADD INDEX 索引名(字段1,字段2，字段3)
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名(字段1,字段2，字段3)

组合索引：将多个索引组合在一起，建立同一棵B+树上。节省开销。并且某些情况下，排序很有优势。
既然提到组合索引了，说一下最左匹配原则。
最左匹配原则包含以下几个内容：

InnoDB在存储组合索引的时候会按照最左边的索引存储在到B+树上；
与普通的B+树也就是普通的辅助索引相比，B+树的键值数量>=2；
组合索引的顺序是先排第一个，再排第二个，然后排下一个，相当于order by a,b,c 这种；

其实这几个定义只是简单总结了一下，老实说在了解了组合索引到底是如何存储的之后，和组合索引息息相关的最左匹配和排序就瞬间明白了，不需要死记硬背。

这一段的内容会在第二节，做B+树索引总结的时候展开。

[全文索引]

--建立索引
ALTER TABLE 表名 add  FULLTEXT INDEX  ftxt(字段) with PARSER ngram;
--MATCH AGAINST 查询

--1 默认自然语言模式  (NATURAL LANGUAGE MODE)
SELECT * FROM 表名 WHERE MATCH(字段1，字段2) AGAINST('关键字')
SELECT * FROM 表名 WHERE MATCH(字段1，字段2) AGAINST('关键字' IN NATURAL LANGUAGE MODE )

--2 采用布尔模式(BOOLEAN MODE)
SELECT * FROM 表名 WHERE MATCH(字段1，字段2) AGAINST('-关键字*' IN NATURAL LANGUAGE MODE )

InnoDB在1.2.x的版本开始支持全文检索技术，MySQL早期的InnoDB是不支持全文索引的，5.7之后开始支持。

全文检索采用了名叫倒排索引的技术，为了提高效率，还设计了一种底层是红黑树结构的FTX INDEX CACHE（全文检索索引缓存）的机制。主要作用还是避免时刻更新索引，InnoDB会在之后新增后的某个时段内批量刷新索引结构到持久层。

涉及到全文索引的底层，如倒排索引，索引缓存等等，会在之后单独总结一篇关于搜索方面的文章。对于全文检索，本文主要介绍使用方式。

使用全文索引时，需要特定的语法进行搜索，其中主要包含两种搜索模式：

自然语言模式（NATURAL LANGUAGE MODE）：该模式下就是简单的包含作用，查询包含关键字的记录。该模式为语法默认模式，不需要手动指定。
布尔模式（BOOLEAN MODE）：该模式支持符号匹配，可以添加匹配规则。需要手动指定 IN NATURAL LANGUAGE MODE

注意：当两个词空格相间时表示或的概念，如 : a b

符号	作用
+	前导符号，匹配内容必须包含关键字如：+a
-	前导符号，匹配内容必须不包含关键字如：-a
>	增加词的相关性影响
<	减少词的相关性影响
()	词嵌套，划分子表达式
~	否定该词对相关性的影响如：~a
*	置于词语之后，表示以某词结尾如：a*
“”	短语匹配，只和该短语顺序匹配如： AGAINST(’“全文索引”’ IN BOOLEAN MODE)
(无操作符)	即关键词前不添加任意操作符，但是该词的相关性较高

InnoDB和MyISAM在全文检索上有一些区别，主要区别如下：

区别	InnoDB	MyISAM
按照相关性降低的顺序自动对行进行排序	支持	不支持
最小搜索关键词对应系统变量	innodb_ft_min_token_size	ft_min_word_len
最大搜索关键词对应系统变量	innodb_ft_max_token_size	ft_max_word_len

另外使用InnoDB在建立全文索引时需要指定Ngram解析模式。早期MySQL不支持中文，后期支持中文，但需要使用该插件。有关该插件的内容暂不展开。

注意：有些时候某些词太短或太长，搜索时不会生效，嘴就是上面提到的最小和最大搜索关键词系统变量的问题。

2）B+树索引

简单总结一下B+树的几个特点：

非叶节点仅存储索引，叶子结点存储内容
叶子结点存储内容，并且键值从小到大排序，叶子结点形成链表

直接看实际应用吧。

聚集索引

在这里插入图片描述

此处为《MySQL技术内幕 InnoDB存储引擎书籍中》的图。（该表内仅有4条数据）

在InnoDB存储引擎中，表中的数据按照主键顺序存放，叫做索引组织表。
叶子结点会存放直接的表数据，每个叶子结点也因此称为数据页。

设计里面，叶子结点存放了表的全部数据，排序时只能按照一颗B+树，所以一个表只能有一个聚集索引，总不可能数据存多份吧，那就没什么意义了。所以得出结论：只有聚集索引会在叶子结点存储表数据。

非叶节点仅存储主键，不存储内容，如上图的根节点里的Key值，存储对应的主键值，此处80 00 00 01、80 00 00 02、80 00 00 04便对应1，2，4的主键。除了主键外还有存有指向数据页的偏移量，也就是Pointer。

通常来说存放数据时按照主键来顺序排，一般情况下也会是我们在建表时指定的那个主键字段。但是当没有指定主键时，InnoDB会使用一个隐藏的ROWID用来构建聚集索引。这么说不太准确，这里列一下选定的要求。

表情况	选择情况
表存在单主键字段	单主键字段
表无主键，但有非空约束字段	首个非空约束字段
都没有	选择隐藏ROWID

所以明确一个结论，主键!=聚集索引。只是大多数情况下聚集索引是依主键构建。

辅助索引

辅助索引并不影响数据的存储位置，影响的仅仅是聚集索引，所以可以有多个辅助索引。另外，辅助索引和聚集索引最重要的一个区别就是前者在叶子节点不存储数据，存储的除了键值以外，还会存储一个指向聚集索引的主键。这里就出现几个概念了：覆盖索引和回表

覆盖索引：从辅助索引中就直接可以获取到所需的数据。
回表：从辅助索引找到记录后，仍旧需要找到主键后，去主键索引里再找一次（根据主键找其他列的记录）

举例来说，假定T表建立了a字段的单列索引。

查找记录时只查a字段，走该辅助索引树，成功找到记录。此处不需要其他字段，直接返回数据。 这种情况就是覆盖索引
查找记录包含a字段以外的字段，走该辅助索引树，成功找到记录，但还需要其他字段，所以使用该记录存储的主键值，去聚集索引书树再进行一次查找。 这种情况就是回表

所以不言而喻，我们在日常开发时，从性能上说，还是要尽量走覆盖索引，当然例子里面说了前提是走该辅助索引树，实际情况下我们需要结合Explain命令还有经验去判断。

这里再提一点，为什么辅助索引非要存储主键而不是存记录的地址呢？

主要还是维护问题，聚集索引的数据是会不断变化的，会出现分裂重构的问题。辅助索引存主键值，那么在建立之后，至少不需要跟着聚集索引去改变地址。完整的表数据通过聚集索引获取就可以了。

其它

前文提到了组合索引涉及到的最左匹配和排序的问题，那么这里可以稍微展开说一下了。
组合索引还是一颗B+树，但是它存储的键值>1，因为是组合索引，要选定多个字段组合起来成为索引。
在这里插入图片描述
比如上图就是以两个字段组合而成的组合索引树。组合索引树是辅助索引树的一种，也会存主键。
观察这张图，我们就可以大概理解到最左匹配原则和排序技巧了。

假定该树是依（a,b）两个字段构建的组合索引树。

首先，构建这颗树需要选定一个基准。这个基准就是排列在组合索引的第一个字段，会依照这个字段构建树。
第1个字段构建完之后，第2个字段按照从小到大排序（区间内），第2个字段构建完之后，第3个字段按照从小到大排序（区间内）

走组合索引时，最左字段优先，匹配从最左边开始然后任何连续的索引。遇到范围查询（between、like、>、<）停止匹配。

看这棵树的叶子结点：

全局来看第一个字段有序（毕竟选定的基准就是第一个字段），其他字段无序
局部来看，当确定第1个字段时，第2个字段有序

案例：假定存在T表，建立了（a,b,c）的组合索引。以下情况可以直接利用组合索引树的排序效果，不需多进行一次排序

SELECT … FROM T WHERE a=1 ORDER BY b;
SELECT … FROM T WHERE a=1 AND b=1 ORDER BY c;

该案例存在于《MySQL技术内幕 InnoDB存储引擎书籍中》，经过实测，该语句排序时似乎仍旧进行了排序，需要修改为以下语句。此处出现差异可能是版本问题。
SELECT … FROM T WHERE a=1 ORDER BY a,b;
SELECT … FROM T WHERE a=1 AND b=1 ORDER BY a,b,c;

在这里插入图片描述