MySQL~B+树索引(聚集索引与非聚集索引)、联合索引、覆盖索引、哈希索引

2401_85112052

于 2024-06-24 02:36:09 发布

阅读量555

点赞数 30

文章标签： mysql b树哈希算法

本文链接：https://blog.csdn.net/2401_85112052/article/details/139910103

版权

非聚集索引(辅助索引)

对于非聚集索引（辅助索引）来说，叶子节点并不包含行记录的全部数据，而是包含了键值和一个指针，这个指针所指向的就是我们需要的数据在聚集索引中的位置, 我们需要在非聚集索引中查找到对应键值和其指针，再通过指针在聚集索引中查找到具体的完整数据，也就是需要两次查找。

基于以上特性，由于辅助索引的存在并不会影响数据在聚集索引中的组织，因此每张表中可以有多个辅助索引
创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。

B+树索引管理

创建和删除索引的方式有俩种, 一种是ALTER TABLE, 还有一种是CREATE / DROP INDEX
查看索引

SHOW INDEX FROM 表名

创建

CREATE INDEX 索引名 ON 表名（字段名）

或者

ALTER TABLE 表名 ADD {KEY | INDEX} 索引名

删除

DROP INDEX 索引名 ON 表名

Cardinality值

在我们show index from XXX\G; 可以看到Cardinality的值, 这个值表示索引唯一值的数据估计数, 如果这个数据很小, 就表示这个索引下的数据的重复度很高, 不应该设置这个索引

在这里插入图片描述

联合索引

联合索引即对表上的多个列进行索引，例如下图

联合索引和普通的B+树不同的地方在于它具有多个键值，虽然键值有序，但是是按照从左往右的优先级以此对键值的大小进行排序，例如上图有两个键值，首先会先按照第一个键值的大小进行排序，当第一个键相同时，再按照第二个键的大小进行排序。
对于查询语句为 select * from student where a=XXX and b= XXX;很有帮助, 但是上面说到叶子中键值的排序是以第一个数据为主, 第二个数据为辅, 那么如果只查第二个数据就会出现前缀匹配规则

前缀匹配规则

就以上述数据进行举例，此时以键值（a, b）构建联合索引
三条对比匹配查询

SELECT * FROM TABLE WHERE a = xxx AND b = xxx 、

SELECT * FROM TABLE WHERE a = xxx

SELECT * FROM TABLE WHERE b = xxx

对于以上几条查询语句来说，虽然看起来差不多，但是效率却大相径庭。
对于前两句来说，它们是可以使用联合索引的，因为无论是按照a作为条件，或者是a和b作为条件，都可以利用索引进行搜索，因为前面也说过了，联合索引的排序是按照从左往右优先的，所以当查找条件中包含a的时候则不会出现问题。
但对于b=xxx则无法适用以上性质，因为在联合索引中，后面的主键只有在前面的主键相同时才会具有有序性，而单独适用它的时候显然数据是无序的，所以这时只能进行行全索引扫描。
对于范围查找也是这么一个道理，假设此时联合索引的主键为（a, b, c)

SELECT * FROM TABLE WHERE a > 3 AND b > 3

SELECT * FROM TABLE WHERE a > 3 AND b > 3 AND c > 3

SELECT * FROM TABLE WHERE a > 3 AND c > 3

同样按照上面的规则，前两种查询都可以适用联合索引，因为其遵循了从左至右的匹配原则，而第三条因为跳过了b，此时的数据是无序的，无法适用索引。

覆盖索引

覆盖索引即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处就是由于辅助索引中不包含整行的所有记录，所以它的大小要远远小于聚集索引，因此可以减少大量的I/O操作。
由于辅助索引中叶子节点存放的数据就是键值，所以当我们要查找键值，或者通过键值来统计数量的时候，就可以使用覆盖索引来完成。
但是在实际开发中, 使用范围查找或者join连接查找的时候, 如果查找的数据很小, 还是会使用辅助索引, 但是在数据较大的时候,优化器会选择通过聚集索引来进行查找数据,因为使用辅助索引的时候如果在需要大量指针进行映射到聚集索引的时候, 效率是很低的, 毕竟殊勋查询比离散查询要好很多

哈希索引

哈希索引是基于哈希算法实现, 哈希算法是很常见的一种算法, 其时间复杂度为O(1), 对于查找效率来说是很高效的.
由于哈希是直接通过哈希值来将数据映射到对应位置，所以哈希索引对于等值查询的效率特别高，但是也正因为这个特性，使得哈希查找在面对范围查找的时候就毫无用武之地了。
哈希索引的原理其实就是通过除留余数法将键值转换为哈希值，并将数据存储对应的槽中，如果出现了哈希冲突，则使用链地址法进行解决，将数据插入对应槽中的链表。

自适应哈希索引

在InnoDB存储引擎会监控对表上各个索引页的查询，如果它观察到建立哈希索引可以带来速度提升，则会自行建立哈希索引，这也就是自适应哈希索引。即会自动根据访问频率和模式来为热点数据建立哈希索引。
由于哈希索引是数据库自身自动创建并使用的，人工无法对其进行干预

全文索引

全文索引即根据部分段落、句、词从数据库中查询除全文的技术，即根据部分查询词获取对应的文档。其主要使用的是倒排索引实现

倒排索引

InnoDB使用关联数组拥有两种倒排索引的表现形式

inverted file index（倒排文件索引）

表现形式 {单词，单词所在的文档ID）

full inverted index（全文倒排索引）

表现形式{单词, (单词所在文档ID，在具体文档的位置）

例如我们存在以下数据

在这里插入图片描述

并将其构建成inverted file index（倒排文件索引）的形式

在这里插入图片描述

接着建立full inverted index（全文倒排索引）

此时在之前的基础上，我们不仅确定了单词所在的文章，还确定了其所在文章中的对应位置，虽然比起inverted file index来说更加复杂，占据的空间也更多，但是却能更好的定位数据，并且扩充一些其它的搜索特性
上面俩张表也叫全文索引的辅助表, 要清除, 数据是放在原文索引表中的, 而关联数组只是复制实现倒排索引的