首页 > 数据库 >mysql中的各种索引大总结

mysql中的各种索引大总结

时间:2024-01-20 22:03:39浏览次数:35  
标签:总结 hash 索引 key mysql 数据 节点 指针



文章目录

  • 为啥不用二叉搜索树?
  • 为啥不用平衡二叉(avl)树?
  • 为啥不用b-树?
  • 为啥用b+树?(重点)
  • 索引
  • 聚簇索引
  • 聚簇索引的局限
  • 聚集的数据的优点
  • 非聚簇索引介绍
  • 组合索引
  • 覆盖索引
  • 前缀索引
  • 前缀索引选择算法
  • 全文索引
  • hash索引
  • b-tree索引
  • 自适应哈希索引
  • 小咸鱼的技术窝



b-tree索引使用的是b+树的数据结构,树有这么多种,那为啥就选择b+树呢?那就从为啥使用b+树开始,到分析其原理的思路一步步分析吧。以下内容针对innodb来说

为啥不用二叉搜索树?

二叉搜索树定义:

  1. 非空左子树的所有键值小于其根结点的键值。
  2. 非空右子树的所有键值大于其根结点的键值。
  3. 左、右子树都是二叉搜索树。就是左节点<根节点<右节点。 看下图树太高了。查询效率太低,故不推荐。

mysql中的各种索引大总结_mysql

为啥不用平衡二叉(avl)树?

平衡二叉(avl)树定义:

  1. 非叶子节点最多拥有两个子节点。
  2. 非叶子节值大于左边子节点、小于右边子节点。
  3. 树的左右两边的层级数相差不会大于1。
  4. 没有值相等重复的节点。

虽然avl树对于二叉树来说是平衡了降低了树的高度,虽然提高了性能,但是数据量大一点的时候,高度依旧很高。毕竟定义一摆在那,非叶子节点怎么找最多也只能有俩个子节点,故不推荐。

为啥不用b-树?

b-树定义:

  1. 在一个节点中,存放着数据(包括key和data)以及指针,且相互间隔。
  2. 同一个节点,key增序。
  3. 一个节点最左边的指针不为空,则它指定的节点左右的key小于最左边的key。右边同理。中间的指针指向的节点的key位于相邻两个key的中间。
  4. B-Tree中不同节点存放的key和指针可能数量不一致,但是每个节点的域和上限是一致的,所以在实现中B-Tree往往对每个节点申请同等大小的空间。
  5. 每个非叶子节点由n-1个key和n个指针组成,其中d<=n<=2d。

简单点来说呢,b-树又称多路查找树相对于avl树来说就是每个节点下面可以有多个分叉。至少多于2个叉撒,对于百万级别的数据又是降低了树的高度,虽然提高了性能,但是由于每个索引节点都有存放
data域的指针,而我们计算机每次从磁盘读取数据时以页(4kb)为单位,每次读取4096byte的数据,导致读取单个节点时,可能由于节点过大,每次读出的数据有限,从而增大io次数,故在mysql这种关系型数据库中不推荐。但是b-树还是有它自己的优点的,如果查找的节点,离根节点很近那么查找的效率还是很高的

为啥用b+树?(重点)

b+树定义:

  1. 内节点不存储data,只存储key和指针;叶子节点不存储指针,存key和data。
  2. 内节点和叶子节点大小不同。
  3. 每个节点的指针上限为2d而不是2d+1

b+树相对与b-树来说好的地方就是:

  1. 数据只在叶子节点上面才有。解决了b-树非叶子节点过大导致io次数过多的问题。
  2. 树高更低同时由于非叶子节点不放数据,单个节点能存的关键字更多了,极大的降低了树的层级高度。
  3. 可以范围查找由于叶子节点之间是有一个有序链表来维护的,进行范围查找十分方便,单向的还是双向的不清楚,先搁着下面测试在来看。
  4. 全节点遍历更快b+树只需扫描叶子节点,而不是像b-树那样逐层扫描每个节点
  5. 查询速度更稳定如果是b-树,查离根节点近的速度就快,远的就慢,而b+树数据全在叶子节点,每次查找的速度都相同
    到此为啥使用b+树就有底了。那么开始正文。。。。。

索引

索引定义:一种数据结构

按照范围从大到小来说吧,最大的是聚簇索引、非聚簇索引。接着是我们常说的b-tree索引、hash索引,而我们的覆盖索引、前缀索引、组合索引、复合索引、全文索引是基于b-tree、hash索引来说的,还有的就是Innodb引擎独有的自适应哈希索引下面一一展开了来说。

聚簇索引

聚簇索引定义:是一种存储数据的方式,数据与索引在一个文件中。它的辅助索引的叶子节点存储的是指向行的指针。

一般来说innodb是按照主键来进行聚集的,这就意味着被索引的列就是主键列。如果主键不存在,其次就是按照非空唯一性约束来进行聚集,如果这个约束都没得,其次就是按照Innodb它自己生成的一个隐藏主键列来聚集。那么使用聚簇索引有什么优缺点吗?当然是有的。

聚簇索引的局限

如果我们往聚簇索引中插入非顺序的数据时的情况下面

  1. 由于新行的主键不一定比前一个大,Innodb不能总是把数据插入到最后一行,因此需要为新行进行寻找位置,从而进行多次随机的io
  2. 更新数据时innodb有时不得不进行分页,为新行开辟内存空间,这会导致移动大量的数据
  3. 页面会因为分页变得稀疏不规则从而就会导致一些不规则的碎片产生
  4. 在没有覆盖索引的情况下,查辅助索引的时候,经常需要回表
    而且如果我们按照主键顺序插入行的情况下,使用uuid來聚集也是不好的。也会产生写,到磁盘上的数据被重新读取出来,并且由于位置的不确定,也会导致大量的数据移动

聚集的数据的优点

  1. 聚集索引把数据和索引全部放到一个b-tree下面,从聚集索引中取得数据通常比非聚集索引取得的数据更快,因为不需要二次查找。
  2. 对于范围查询的效率很高,因为数据是有序的

非聚簇索引介绍

非聚簇索引是什么呢?其实我们所说的辅助索引、第二索引啊啥的其实可以理解为就是非聚簇索引。非聚簇索引就是叶子节点的data域中存的不是完整的数据,而是指向磁盘数据的指针。

组合索引

定义:单列索引的组合。我感觉和覆盖索引没啥差别,假设现在有一张表(test)有a,b,c,d,e这么几个字段,其中我们为a,b,c列添加一个索引叫a_b_c,这个就是组合索引。

覆盖索引

定义:索引列已经包含了我们需要查询的数据。其实就是组合索引(复合索引)在特定情况下的又一别称。假设现在有一张表(test)有a,b,c,d,e这么几个字段,其中我们为a,b,c列添加一个索引叫a_b_c,现在select a,b,c from test where a = x1 and b = x2 and c = x3;我们需要查询的数据在辅助索引上面已经有了,此时使用的就是复合索引。

前缀索引

定义:为数据的前面几个字段创建的索引。我个人理解其实就是单列索引的变种。那为什么要为前几个字符创建索引而不是为其全部来创建索引呢?原因也是很简单,这个得结合应用场景来说了。现在有这么一张表,专门是存储家庭地址的,一个地址动不动就是十来个字的长度,如果我们以全部长度来创建索引,那么这个索引文件是超级的大啊。此时创建前缀索引是个不错的选择,可以大幅度压缩索引的大小,那么我们该如何确定前缀索引的长度的呢?这就是涉及到一个算法了。

前缀索引选择算法

不重复的索引值/所有行 = 比值,这个比值接近7的时候,所得到的结果就会越加精确。

全文索引

参考

hash索引

使用的场景:一些读操作密集的表建议使用hash索引,因为hash索引的查找速度是很快的。但是也有一些局限。
hash索引的局限性:

  1. hash索引只包含哈希码和行指针,不能使用索引的值避免读取行,也就是要回表,不能像覆盖索引那样避免回表。
  2. hash索引不能进行排序以及范围查找,因为它们不会按照顺序保存行。
  3. 有可能产生hash碰撞,那么就必须要访问链表的每一个行指针,然后逐行进行比较得出正确数据。
  4. 只支持 = 、in 、<=>的比较,为啥呢原因很简单,不会按照顺序存行。
  5. 不支持部分键匹配,例如有个组合索引a_b,那么此时即使我们的where子句中使用到了a,也不会使用索引。

b-tree索引

底层数据结构b+tree,对比hash的局限来看,hash索引的局限在b-tree索引这都不是事,除了hash索引查询速度快这点上面

自适应哈希索引

与其说这是一种索引不如称其为是一种机制。自适应哈希索引的由来就是:当Innodb注意到一些索引值被频繁的访问时,内部会在b-tree索引的顶端为其创建索引保存在内存之中,使其具有快速哈希查找的特性,这个过程是它自动完成的

小咸鱼的技术窝

关注不迷路,日后分享更多技术干货,B站、微信公众号同名,名称都是(小咸鱼的技术窝)更多详情在主页


标签:总结,hash,索引,key,mysql,数据,节点,指针
From: https://blog.51cto.com/u_16414043/9346310

相关文章

  • 深度了解mysql事务mvcc实现原理
    一:事务概念:一组原子性的sql查询语句,也可以看作是一个工作单元特点:要么全部执行成功,要么全部执行失败一个有效的事务需满足的条件(ACID)原子性(Atomicity)一个事务必须被视为一个单独的内部最小的,”不可分“的工作单元,以确保事务要么全部执行,要么全部执行失败,当一个事务具有原子性的时候......
  • MySQL-13.MySQL约束
    1.约束(constraint)概述1.1为什么需要约束数据完整性(DataIntegrity)是指数据的精确性(Accuracy)和可靠性(Reliability)。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成的无效操作或错误信息而提出的。为了保证数据的完整性,SQL规范以约束的方式对表数据......
  • 1.20寒假每日总结11
    学习执行计划。简单的解释为:explainquery;一个简单的例子为:explainselectsum(id)fromtest1;该语句的执行计划为:STAGEDEPENDENCIES:Stage-1isarootstageStage-0dependsonstages:Stage-1STAGEPLANS:Stage:Stage-1MapReduceMap......
  • 1.20每日总结
    Python3数字(Number)Python数字数据类型用于存储数值。数据类型是不允许改变的,这就意味着如果改变数字数据类型的值,将重新分配内存空间。以下实例在变量赋值时Number对象将被创建:var1=1var2=10您也可以使用del语句删除一些数字对象的引用。del语句的语法是:delva......
  • 数据库学习笔记(二)—— MySQL 之 存储引擎和索引篇
    存储引擎和索引 前言关于MySQL的学习着实有些混乱,虽然才到学习笔记二,但学习笔记四都已经写完了,其他写一点,可以说是东一榔头西一棒槌;写出的东西也不忍直视,省略了很多细节,还基本上都是到处搬运的,可即便是搬运,也都绞尽脑汁了。网上的知识大多都模糊不清,甚至还错误百出,为了......
  • 将MySQL数据库数据转换为PGSQL数据库 --- 实操可以
    利用navicate,傻瓜操作即可。选中要迁移的数据库,用navicate上面的工具,数据传输,传输到要迁移的数据库(可以在不同的连接之间传输的)https://huaweicloud.csdn.net/63356c9ed3efff3090b5653e.html......
  • 算法分析与设计学习总结_2
    算法分析与设计四、动态规划(1)动态规划​ ①基本思想:n将原问题分解为若干个子问题,先求子问题的解,然后从这些子问题的解得到原问题的解。这些子问题的解往往不是相互独立的。​ ②基本要素:最优子结构和重叠子问题。​ ③最优子结构性质:最优解的子结构也是最优的。问题的最优......
  • 算法分析与设计学习总结_1
    算法分析与设计一、算法概述1.1算法和过程(1)算法和过程都是解决问题的一种方法的逐步描述(2)他们都是由若干条指令组成的有穷序列;每条指令意义确定;具有零个或多个输入;产生若干个输出。(3)算法的执行时间是有限的(终止性);过程的执行时间可能是无限的。1.2算法(1)程序和算法​ ①......
  • 人工智能学习总结_1
    人工智能一、人工智能绪论、基础(1)人工智能、基因工程、纳米科学被认为是21世纪的三大尖端技术。(2)人工智能的典型应用领域:交通、服务机器人、医疗健康、教育、公共安全、工作就业、娱乐。二、搜索(1)单智能体搜素:规划盲目搜索启发式搜索局部搜索(2)多智能体搜索:零和博弈极......
  • 人工智能学习总结_3
    人工智能七、神经网络7.1概述(1)适用问题:用于处理更加复杂的输入和输出之间的非线性关系问题(2)特点:​ ①可以用来拟合非常复杂的函数(3)应用:图像分类、语音识别、机器翻译、自动驾驶7.2人工神经网络设计(1)人工神经元:线性模型+激活函数(2)人工神经网络设计的三方面​ ①神经......