MySQL中聚集索引、非聚集索引、联合索引、覆盖索引[通俗易懂]

MySQL中聚集索引、非聚集索引、联合索引、覆盖索引[通俗易懂]在《面试官:为啥加了索引查询会变快?》一文中,我们介绍了索引的数据结构,正是因为索引使用了B+树,才使得查询变快。说白了,索引的原理就是减少查询的次数、减少磁盘IO,达到快速查找所需数据的目的我们一起来看一下InnoDB存储引擎中的索引聚集索引聚集索引(clusteredindex)就是按照每张表的主键构造一棵B+树,同时叶子节点中存放的即为整张表的行记录数据,也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分(备注:真实的B+树叶子节点是通过链表相连的,

大家好,又见面了,我是你们的朋友全栈君。

《面试官:为啥加了索引查询会变快?》一文中,我们介绍了索引的数据结构,正是因为索引使用了B+树,才使得查询变快。说白了,索引的原理就是减少查询的次数、减少磁盘IO,达到快速查找所需数据的目的

我们一起来看一下InnoDB存储引擎中的索引

聚集索引

聚集索引(clustered index)就是按照每张表的主键构造一棵B+树,同时叶子节点中存放的即为整张表的行记录数据,也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分

在这里插入图片描述

(备注:真实的B+树叶子节点是通过链表相连的,这里只是为了说明聚集索引存储了行数据,凑合着看吧~)

每张表只能拥有一个聚集索引。在多数情况下,查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外,由于定义了数据的逻辑顺序,聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描

辅助索引

辅助索引(Secondary Index,也称非聚集索引),叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点中的索引行中还包含了指向主键的指针。

辅助索引的存在并不影响数据在聚集索引中的组织,因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时,InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键,然后再通过聚集索引来找到一个完整的行记录

在这里插入图片描述

联合索引

联合索引是指对表上的多个列进行索引

从本质上来说,联合索引也是一棵B+树,不同的是联合索引的键值的数量不是1,而是大于等于2

联合索引有如下特点:

最左前缀原则

创建了(a,b,c)联合索引,如下几种情况都可以走索引:

  • select * from table where a = xxx;
  • select * from table where a = xxx and b = xxx;
  • select * from table where a = xxx and b = xxx and c = xxx

如下几种情况不走索引

  • select * from table where b = xxx;
  • select * from table where c = xxx;
  • select * from table where b = xxx and c = xxx;

本质上讲(a,b,c)联合索引等同于(a)单列索引、(a,b)联合索引、(a,b,c)联合索引三种索引的组合,符合最左前缀原则

覆盖索引

InnoDB存储引擎支持覆盖索引(covering index,或称索引覆盖),即从辅助索引中就可以得到查询的记录,而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息,故其大小要远小于聚集索引,因此可以减少大量的IO操作

  • 非聚集索引上直接可以拿到所需数据,不需要再回表查,比如 select id from table where name = xxx;(id为主键、name为索引列)
  • 在统计操作中也会使用覆盖索引。比如(a,b)联合索引,select * from table where b = xxx语句按最左前缀原则是不会走索引的,但如果是统计语句select count(*) from table where b = xxx;就会使用覆盖索引。

选择性

并不是在所有的查询条件中出现的列都需要添加索引。对于什么时候添加B+树索引,一般的经验是,在访问表中很少一部分时使用B+树索引才有意义。对于性别字段、地区字段、类型字段,它们可取值的范围很小,称为低选择性

按性别进行查询时,可取值的范围一般只有’M’、‘F’。因此上述SQL语句得到的结果可能是该表50%的数据(假设男女比例1∶1),这时添加B+树索引是完全没有必要的。相反,如果某个字段的取值范围很广,几乎没有重复,即属于高选择性,则此时使用B+树索引是最适合的

所以,我们在添加索引的时候,要尽量选择高选择性的字段,反之你在低选择性的字段上加了字段,查询可能也不会走索引

如果感觉对你有些帮忙,请收藏好,你的关注和点赞是对我最大的鼓励!
如果想跟我一起学习,坚信技术改变世界,请关注【Java天堂】公众号,我会定期分享自己的学习成果,第一时间推送给您

在这里插入图片描述

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/135995.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • Django(47)drf请求生命周期分析

    Django(47)drf请求生命周期分析前言一般我们写完序列化以后,我们就会开始写视图了,drf中我们一般使用CBV的方式,也就是类视图的方式,最基础的我们会使用fromrest_framework.viewsimportAPIVi

  • python urlopen 乱码[通俗易懂]

    1234567importurllib2importsys content=urllib2.urlopen(‘http://xxx.com’).read()#thewebpageisencodedbyuft-8 type=sys.getfilesystemencodin

  • idea打包jar文件_idea如何打包jar外部包

    idea打包jar文件_idea如何打包jar外部包文章目录项目打包-贪吃蛇为例一.打包为jar1.打开结构2.添加结构3.选择4.设置参数5.添加依赖6.设置完成点击apply后,点击ok7.回到代码页面点击build8.选择建立9.目录会生成所需的包文件10.在文件夹里打开11.在cmd里运行jar即可运行12.在输入java-jarsnake.jar即可运行项目打包-贪吃蛇为例一.打包为jar1.打开结构2.添加结构3.选择因为有好多项目,所以这里需要建立空,如果只有一个目的项目,可以选择根据这个依赖,选择下面一项。4.

  • windows server 2008 web服务器搭建

    windows server 2008 web服务器搭建在众多小企业中,Web服务器的应用也需是最广泛的,企业网站的建立促进了服务器进驻中小企业,而Web服务器的建立也越来越多。而面对诸多的Web服务器应用系统,可以说是多种多样,有大家最熟悉的微软的IIS,也有开源的Apache,还有大型网站应用比较多的Unix,总之可以说是各有各的优点。  今天介绍的是,微软的IIS,因其图形界面的操作方式,给人以直观的操作感受,部署简便,是很多用户的首选,而Apache的部署要相对复杂,一般新手很难马上上手,所以这里先跟大家讲解一下IIS7.0的搭建Web服务器的方法。

  • hdu1078 zoj1107(记忆化搜索/DP)

    hdu1078 zoj1107(记忆化搜索/DP)题目链接:点击链接题目大意:老鼠从(0,0)出发,每次在同一个方向上最多前进k步,且每次到达的位置上的数字都要比上一个位置上的数字大,求老鼠经过的位置上的数字的和的最大值#include#include#definemax(a,b)a>b?a:bintn;intk;//前进的步数intmap[105][105];intans[105][105];//记忆化搜索,保存

  • JSON字符串转List集合操作「建议收藏」

    JSON字符串转List集合操作「建议收藏」场景:JSON字符串转List集合与List集合转JSON字符串使用的jar包是fastjson-1.2.41.jar1.JSON字符串转List集合操作publicstatic<T>List<T>parseArray(Stringtext,Class<T>clazz){try{re…

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号