mysql中主键索引和联合索引的原理解析

在这里插入图片描述


mysql中主键索引和联合索引的原理解析

  • 一、主键索引
  • 二、什么是联合索引? 对应的B+树是如何生成的?
    • 1、建立索引方式
    • 2、什么是最左前缀原则?
    • 3、回表
    • 4、为什么要遵守最左前缀原则才能利用到索引?
    • 5、什么是覆盖索引?
    • 6、索引扫描底层原理
    • 7、order by为什么会导致索引消失?
    • 8、有哪些情况会导致索引失效?
    • 9、索引设计原则
  • 三、InnoDB 与MyISAM 的区别

一、主键索引

主键索引:按照主键数据从小到大按照从左到右进行排序,叶节点只存储数据区;
接着将上面的页生成出来,页只存储索引和指针,指针指向数据域,当通过主键查找数据时,从B+树的头部开始寻址数据、读取数据。

在这里插入图片描述
上面为索引页
下面为数据页

查询select * from table where a=6,会从上到下走法,找索引

查询select * from table where a<6,会从上到下走法,找索引;
原因:先找a=6是走索引的,找到a=6数据后,将左边的数据全部返回即可。

为了方便把前面数据给你采用双向指针

查询select * from table where b=6,会从左到右走法,全表扫描,因为b未有命中索引。

二、什么是联合索引? 对应的B+树是如何生成的?

联合索引:将数据库表中多个字段组成一个索引。bcd联合索引;bcd三个字段进行排序

1、建立索引方式

create index idx_t1_bcd on t1(b,c,d);bcd三个字段进行排序

2、什么是最左前缀原则?

复合索引,也叫联合索引,用户可以在多个列上建立索引,这种索引叫做复合索引。
当我们创建一个组合索引的时候,如(k1,k2,k3),相当于创建了(k1)、
(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则。

select * from table where k1=A and k2=B and k3=C

3、回表

在这里插入图片描述

select * from t1 where b=1 and c=1 and d=1执行逻辑:
通过联合索引找到一条数据的3个字段,但是select * 查找多个字段,所以需要存储主键字段,通过主键字段去主键索引里面找到完整的这条数据,并将这条数据返回。

通过主键再去主键索引里面找数据的过程叫做回表

4、为什么要遵守最左前缀原则才能利用到索引?

explain select * from t1 where c=1 and d=1 and b=1 ;
最左前缀原则和后面条件的顺序没有关系

不符合最左匹配原则:*11

符合最左匹配原则:1*1

b=1:走联合索引 (需要回表1次)

b>1:不走联合索引(因为需要回表很多次)

拿到主键会到主键索引里面拿数据(回表7次)

5、什么是覆盖索引?

查询的字段正好在联合索引字段里面,不需要回表

select b from t1 where b>1;(不需要回表了,直接从联合索引字段中将该字段取出来)

select a,b,c,d from t1 where b>1;
同样的也符合联合索引,也不需要回表,因为a字段(主键)在联合索引中。

select a,b,c,d,e from t1 where b>1;
不走联合索引,需要回表,因为e字段不在联合索引中,需要回表很多次

6、索引扫描底层原理

从bcd索引的叶子节点开始遍历
主键索引存储的是8条完整的数据
联合索引中存储的是8条不完整的数据,并且b字段在联合索引中,不会去回表到主键索引中。

从叶子节点开始扫描不需要符合最左匹配原则。

7、order by为什么会导致索引消失?

因为走全表扫描效率更高,不需要回表
走索引会回表多次,效率低

8、有哪些情况会导致索引失效?

1、索引列使用!=、not、is null、is not null查询的时候,由于索引数据的检索效率非常低,因此Mysql引擎会判断不走索引。
2、使用like通配符匹配后缀%xxx的时候,由于这种方式不符合索引的最左匹配原则,所以也不会走索引。

  • 但是反过来,如果通配符匹配的是前缀xxx%,符合最左匹配,也会走索引。

3、对索引列上进行函数运算、导致mysql无法识别索引列,就不会走索引了。

4、使用or连接查询的时候,or语句前后没有同时使用索引,那么索引会失效。只有or左右查询字段都是索引列的时候,才会生效。

5、当索引列存在隐式转化的时候, 比如索引列是字符串类型,但是在sql查询中没有使用引号。

9、索引设计原则

查询更快、占用空间更小
1.适合索引的列是出现在where子句中的列,或者连接子句中指定的列
2.数据量较小的表,索引效果较差,没有必要在此列建立索引
3.使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,这样能够节省大量索引空间,如果搜索词
超过索引前缀长度,则使用索引排除不匹配的行,然后检查其余行是否可能匹配。
4.不要过度索引。索引需要额外的磁盘空间,并降低写操作的性能。在修改表内容的时候,索引会进行更新甚至重构,索引列越多,这个时间就会越长。所以只保持需要的索引有利于查询即可。
5.定义有外键的数据列一定要建立索引。
6.更新频繁字段不适合创建索引
7.若是不能有效区分数据的列不适合做索引列(如性别,男女未知,最多也就三种,区分度实在太低)
8.尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。
9.对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。
10.对于定义为text、image和bit的数据类型的列不要建立索引。

三、InnoDB 与MyISAM 的区别


聚簇索引:将数据存储与索引放到了一块、并且是按照一定的顺序组织的,找到索引也就找到了数据,数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。
非聚簇索引:叶子节点不存储数据、存储的是数据行地址,也就是说根据索引查找到数据行的位置再取磁盘查找数据,这个就有点类似一本树的目录,比如我们要找第三章第一节,那我们先在这个目录里面找,找到对应的页码后再去对应的页码看文章。

优势:
1、查询通过聚簇索引可以直接获取数据,相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率要高
2、聚簇索引对于范围查询的效率很高,因为其数据是按照大小排列的
3、聚簇索引适合用在排序的场合,非聚簇索引不适合

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/519718.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

vue3和vue2项目中如何根据不同的环境配置基地址?

在不同环境下取出的变量的值是不同的, 像这样的变量称为环境变量 为什么要使用环境变量呢? 开发环境生产环境下的接口地址有可能是不一样的&#xff0c;所以我们需要根据环境去配置不同的接口基地址 1、vue2环境变量配置 在根目录创建&#xff1a;.env.development和.env.p…

RTPS协议概述

一.RTPS协议概述 RTPS协议主要由四个部分组成&#xff1a; 1.发现模块&#xff08;Discovery&#xff09; ​ 发现模块是定义了RTPS的参与者&#xff08;Participant&#xff09;获取其他RTPS的参与者&#xff08;Participant&#xff09;&#xff0c;端点&#xff08;Endpo…

解决沁恒ch592单片机在tmos中使用USB总线时,接入USB Hub无法枚举频繁Reset的问题

开发产品时采用了沁恒ch592&#xff0c;做USB开发时遇到了一个奇葩的无法枚举问题。 典型症状 使用USB线直连电脑时没有问题&#xff0c;可以正常使用。 如果接入某些特定方案的USB Hub&#xff08;例如GL3510、GL3520&#xff09;&#xff0c;可能会出现以下2种情况&#xf…

C# MES通信从入门到精通(9)——c#使用visual studio2019社区版部署webservice服务

前言 在上位机软件开发领域,我们经常使用调用webservice接口来和mes系统进行交互,我们在开发Mes程序之前也可以自己先模拟发布客户的webservice接口进行调用从而加快软件开发进度,本文就是详细介绍如何自己发布webservice接口。 1、创建 参考这篇博文:webservice服务创建…

【MYSQL进阶篇】详细版MYSQL优化

Mysql优化&#xff0c;让你彻底知道优化的各种情况以及解决方案 有一系列的代码和图片让你一步一步走&#xff0c;成为索引的真正实践者 坚持看完这篇文章&#xff0c;会让你功力瞬间提升一个档次 制作不易&#xff0c;觉得不错请点赞收藏 &#xff01…

java——文件上传

一、文件上传——简介 文件上传的简介&#xff1a;文件上传是指将本地计算机中的文件传输到网络上的服务器或另一台计算机上的过程。在 Web 开发中&#xff0c;文件上传通常指的是将用户通过 Web 页面提交的文件&#xff08;如图像、文档、音频、视频等&#xff09;传输到服务器…

包子凑数【蓝桥杯】/完全背包

包子凑数 完全背包 完全背包问题和01背包的区别就是&#xff0c;完全背包问题每一个物品能取无限次。 思路&#xff1a;当n个数的最大公约数不为1&#xff0c;即不互质时&#xff0c;有无限多个凑不出来的&#xff0c;即n个数都可以表示成kn&#xff0c;k为常数且不为1。当n个…

DFS序列

什么是DFS序 DFS序是指对一棵树进行DFS时&#xff0c;每个节点被访问到的顺序。DFS序分成两个部分&#xff1a;进入该节点的顺序和退出该节点的顺序。 如何求DFS序 对于DFS中当前节点 1&#xff1a;计数 2&#xff1a;进入当前节点的顺序等于当前计数 3&#xff1a;想所有…

达梦使用disql登录数据库显示“未连接”

基础环境 操作系统&#xff1a;Red Hat Enterprise Linux Server release 7.9 (Maipo) 数据库版本&#xff1a;DM Database Server 64 V8 架构&#xff1a;单实例问题&#xff1a;达梦数据库在使用disql登录时&#xff0c;显示“未连接”。 指定了IP和端口号还是连接异常。 […

淘宝扭蛋机小程序源码搭建:打造专属电商娱乐新平台

在数字化浪潮的推动下&#xff0c;电商平台不断创新&#xff0c;以满足消费者日益多样化的需求。淘宝扭蛋机小程序作为一种创新的电商娱乐形式&#xff0c;受到了广大用户的热烈追捧。为了满足市场需求&#xff0c;许多企业和开发者开始关注淘宝扭蛋机小程序的源码搭建&#xf…

SAP S/4HANA的常见部署方式

SAP S/4HANA是SAP面向企业数字化转型推出一代商务ERP 套件&#xff0c;与传统的SAP ERP系统相比&#xff0c;它基于内存计算和先进的数据处理技术&#xff0c;提供更快的数据处理速度、实时分析和更好的用户体验。SAP S/4HANA可以根据企业的需求以多种部署方式进行实施&#xf…

【零基础学数据结构】顺序表实现书籍存储

目录 书籍存储的实现规划 ​编辑 前置准备&#xff1a; 书籍结构体&#xff1a; 书籍展示的初始化和文件加载 书籍展示的销毁和文件保存 书籍展示的容量检查 书籍展示的尾插实现 书籍展示的书籍增加 书籍展示的书籍打印 书籍删除展示数据 书籍展示修改数据 在指定位置之前…

LogicFlow 在HTML中的引入与使用

LogicFlow 在HTML中的引入与使用 LogicFlow的引入与使用&#xff0c;相较于BPMNJS相对容易一些&#xff0c;更加灵活一些&#xff0c;但是扩展代码可能写得更多一些。 示例展示 使用方式 这个的使用方式就简单很多了&#xff0c;利用cdn把js下载下来&#xff0c;引入到HTML文…

Python爬虫-懂车帝新能源汽车近一年销量榜

前言 本文是该专栏的第24篇,后面会持续分享python爬虫干货知识,记得关注。 笔者在本专栏之前,有详细介绍以“懂车帝平台的新能源汽车销量榜单”为例,获取各车型的销量排行榜单数据。而本文,笔者将单独详细来介绍如何获取“近一年的新能源汽车销量榜单”数据。 具体实现思…

信创(统信)系统上的软件安装及软件使用手册

一.各软件的安装文档 1.达梦数据库在统信系统上的安装 官方手册:https://eco.dameng.com/document/dm/zh-cn/start/install-dm-linux-prepare.html 1.1下载安装包 官网:https://www.dameng.com/list_103.html 点击”服务与合作”--> “下载中心” 这里选择对应的cpu和操作…

#include<初见c语言之字符函数和字符串函数>

目录 一、字符分类函数 二、字符转换函数 三、strlen的使⽤和模拟实现 1.strlen使用 2.strlen函数的模拟实现 四、 strcpy的使⽤和模拟实现 1.strcpy使用 2.strcpy函数的模拟实现 五、strcat的使用和模拟实现 1.strcat的使用 2.strcat的模拟实现 六、strcmp的使用…

【HTML】CSS样式(二)

上一篇我们学习了CSS基本样式和选择器&#xff0c;相信大家对于样式的使用有了初步认知。 本篇我们继续来学习CSS中的扩展选择器及CSS继承性&#xff0c;如何使用这些扩展选择器更好的帮助我们美化页面。 下一篇我们将会学习CSS中常用的属性。 喜欢的 【点赞】【关注】【收藏】…

214基于matlab的交互多模算法(IMM)机动目标跟踪算法

基于matlab的交互多模算法&#xff08;IMM&#xff09;机动目标跟踪算法&#xff0c;完整的15页文档论文。根据二维空间内目标作匀速直线运动和匀速圆周运动的特点&#xff0c;在建立目标运动模型和观测模型的基础上采用基于交互多模算法&#xff08;IMM&#xff09;的卡尔曼滤…

MySQL 50 道查询题汇总,足以巩固大部分查询(附带数据准备SQL、题型分析、演示、50道题的完整SQL)

目录 MySQL 50 道查询题&#xff0c;足以巩固大部分查询数据准备&#xff1a;创建表sql添加表数据sql 50道查询题目汇总01 - 05 题&#xff1a;1、查询 “01” 语文成绩比 “02” 数学成绩高的学生的信息及课程分数2、查询 "01语文课程"比"02数学课程"成绩…

ARM架构学习笔记2-汇编

RISC是精简指令集计算机&#xff08;RISC:Reduced Instruction Set Computing&#xff09; ARM汇编概述 一开始&#xff0c;ARM公司发布两类指令集&#xff1a; ① ARM指令集&#xff0c;这是32位的&#xff0c;每条指令占据32位&#xff0c;高效&#xff0c;但是太占空间 2…