Mysql系列-索引类型

一 、索引类型别

根据叶子节点的内容分类的索引类型

InnoDB 使用B+ tree 索引模型,根据叶子节点是否存储数(根据叶子节点的内容)分为主键索引和非主键索引;非主键索引包括:普通索引、唯一索引、组合索引主键索引的叶子节点存的是整行数据。在 InnoDB 里,主键索引也被称为聚簇索引(clustered index)。非主键索引的叶子节点内容是主键的值。在 InnoDB 里,非主键索引也被称为二级索引(secondary index)。基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询,也称为回表查询。使用主键索引查询会一次性查询出数据。

1.普通索引

MySQL中最基本的索引类型,没有限制,允许重复,允许NULL

CREATE INDEX index_code ON tb_student(code);

2.唯一索引

唯一索引跟普通索引类似,唯一索引列的值必须唯一,允许有空值。
如果是组合索引,则列值的组合必须唯一。
创建唯一索引通常使用 UNIQUE 关键字,例如电话号码必须唯一,创建唯一索引

CREATE INDEX index_phone_number ON tb_student(phone_number);

3.主键索引

主键索引其实也是唯一索引的一种,但是不允许值重复或者值为NULL。必须使用PRIMARY KEY,在创建表结构的时候创建,不能单独用CREATE 的方式创建,主键一般都是自增ID

4.全文索引

全文索引主要用来查找文本中的关键字,只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。
全文索引允许在索引列中插入重复值和NULL。
不过对于大容量的数据表,生成全文索引非常消耗时间和硬盘空间。
创建全文索引使用 FULLTEXT 关键字。

CREATE FULLTEXT INDEX index_name ON tb_student(name);

根据索引和数据存储方式分类存储方式

都属于B + tree的所有结构,那么他们的区别是什么呢?最大的区别就是存储方式的不同

聚簇索引

聚簇索引:将数据和索引放在一起存储,并且是按照顺序,数据的物理存放顺序和索引顺序是一直的,找到索引,就能找到数据

非聚簇索引

非聚簇索引:叶子节点不存数据,存储的是数据对应的地址,也就是说先要查询到地址,然后在去磁盘中找到对应的数据,因为数据和索引是分开的,举个例子,我们要找一本书中的某一个知识点,那么就需要先去目录中查询对应的章节页码,然后通过页码找到对应的那一页,这个目录就相当于索引,而那一页就是对应的数据

优点
聚簇索引查询可以直接获取数据,相比于非聚簇索引需要二次查询效率更高
聚簇索引的范围查询效率更高,因为是有序排列的
缺点
维护成本较高,特别是插入数据,会导致分页(page split),当数据插入一个已满的页中时,存储引擎就会把这一页分页,这样就导致占用更多磁盘空间
更新数据代价高,需要把更新后的行移动到新的位置

二 索引的规则

最左匹配原则

例如给日志表创建了3个联合索引,分别是
operate_type操作类型,
operate_user操作人,
operate_status操作状态

CREATE INDEX index_type_user_status ON eom_log(operate_type,operate_user,operate_status);

下面我们就来例如explain来看看是否命中索引

explain select * from eom_log where operate_type < 10

先来查一个字段,operate_type,结果如下,命中索引
在这里插入图片描述
再加一个字段,执行的结果也是命中索引

explain select * from eom_log where operate_type < 10 and operate_user < 10

在这里插入图片描述
那么如果我们查最右边的那个字段,会发生什么呢?很遗憾,没有走索引,
在这里插入图片描述
同意的道理,只查询中间字段,也是不走索引的

explain select * from eom_log where operate_user < 10

在这里插入图片描述
所以在多个字段组成的联合索引中,where条件中必须要从最左边的第一个字段开始匹配,如果没有就无法命中索引

那么问题又来了,如果只用第一个和第三个 字段当条件能命中索引吗?我们来试试

explain select * from eom_log where operate_type < 10 and operate_status  < 10

在这里插入图片描述
结果显示命中索引,所以只要where条件中包含最左边的字段,那么一定是命中

那么问题又来了,如果把这3个字段的顺序颠倒一下,能命中索引吗?

explain select * from eom_log where operate_user < 10 and operate_status  < 10 and operate_type < 10

在这里插入图片描述
结果显示命中索引,这里可能会有小伙伴问,不是最左匹配吗,其实这是mysql的优化器的功劳,优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高,他会排列where的查询顺序,使他能够命中索引,最后才生成真正的执行计划。所以mysql查询优化器会最终以这种顺序进行查询执行。

覆盖索引

覆盖索引就是SQL只需要通过索引就可以返回查询所需要的数据,而不必通过二级索引查到主键之后再去查询数据。说白了,就是你的查询走到了索引,并且需要返回的数据刚好是索引的组成字段,不需要回表就能得到想要的结果;
简单粗暴理解:如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫做覆盖索引,如根据主键查询走主键索引,或者如下情况

看看例子,有个学生表student,里面有个联合索引学号和电话号码index_code_phone

SELECT code,phone from tb_user where code = 'a' and phone = 'b'

那么这个要查询的code 和phone 正好索引,那么查询的时候就直接能够返回数据,不需要走二次查询,这就是覆盖索引
覆盖索引的优点

  • 减少了磁盘I/O,提高了查询速度

  • 减少了内存占用,节省了资源

  • 减少了锁的竞争,提高了并发能力,它可以避免回表查询原始数据,从而减少了对数据行的访问和锁定。如果使用索引覆盖,那么只需要对索引记录加锁,而不需要对数据记录加锁。这样就可以减少锁的范围和冲突,提高并发能力

覆盖索引的缺点

  • 增加了索引的大小,占用了更多的磁盘空间
  • 增加了数据更新的开销,因为每次更新数据都需要更新索引
    因此,在创建覆盖索引时,我们需要权衡利弊,选择合适的字段和顺序,避免创建过多或过大的索引。

索引下推

索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本的新特性,它能减少回表查询次数,提高查询效率。索引下推是指在使用联合索引时,将过滤条件下推到索引层,从而减少回表的次数。索引下推的下推其实就是指将部分上层(服务层)负责的事情,交给了下层(引擎层)去处理。索引下推的目的是为了减少回表次数,也就是要减少IO操作。对于的聚簇索引来说,数据和索引是在一起的,不存在回表这一说。

索引下推原理

我们来具体看一下,在没有使用ICP的情况下,MySQL的查询:
存储引擎读取索引记录;
根据索引中的主键值,定位并读取完整的行记录;
存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP的情况下,查询过程
存储引擎读取索引记录(不是完整的行记录);
判断条件部分能否用索引中的列来做检查,条件不满足,则处理下一行索引记录;
条件满足,使用索引中的主键去定位并读取完整的行记录(就是所谓的回表);
存储引擎把记录交给层,层检测该记录是否满足条件的其余部分。

例如,假设我们有一个订单表,包含了订单的id,用户id,商品id,价格等字段,我们想要查询所有用户id为1且价格大于1000的订单的商品id和价格,那么我们可以创建一个包含了用户id,价格和商品id的联合索引,这样在扫描索引时就可以先过滤掉不满足用户id为1或价格大于1000的记录,而不需要回表查询订单表。

与联合索引不同的是,联合索引只能在查询条件中包含索引的前缀部分时才能生效,而索引下推可以在查询条件中包含任意部分的索引时生效。 例如,如果一个联合索引是(a,b,c),那么只有当查询条件中包含a或者a和b时才能使用该索引,而如果查询条件中只包含b或者c时则不能使用该索引。 但是如果使用了索引下推,那么即使查询条件中只包含b或者c,也可以将这些条件下推到索引层面上进行过滤,从而减少回表的次数和范

索引下推的具体实践

理论比较抽象,我们来上一个实践。使用一张用户表tuser,表里创建联合索引(name, age)
在这里插入图片描述
如果现在有一个需求:检索出表中名字第一个字是张,而且年龄是10岁的所有用户。那么,SQL语句是这么写的:

select * from tuser where name like '张%' and age=10;

假如你了解索引最左匹配原则,那么就知道这个语句在搜索索引树的时候,只能用 张,找到的第一个满足条件的记录id为1
在这里插入图片描述
那接下来的步骤是什么呢?
没有使用ICP
在MySQL 5.6之前,存储引擎根据通过联合索引找到name likelike ‘张%’ 的主键id(1、4),逐一进行回表扫描,去聚簇索引找到完整的行记录,server层再对数据根据age=10进行筛选。

我们看一下示意图:
在这里插入图片描述
可以看到需要回表两次,把我们联合索引的另一个字段age浪费了。
使用ICP
而MySQL 5.6 以后, 存储引擎根据(name,age)联合索引,找到,由于联合索引中包含列,所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。

我们看一下示意图:
在这里插入图片描述
可以看到只回表了一次。

除此之外我们还可以看一下执行计划,看到Extra一列里 Using index condition,这就是用到了索引下推。

在这里插入图片描述

索引下推使用条件

只能用于range、 ref、 eq_ref、ref_or_null访问方法;

只能用于InnoDB和 MyISAM存储引擎及其分区表;

对存储引擎来说,索引下推只适用于二级索引(也叫辅助索引);

索引下推的目的是为了减少回表次数,也就是要减少IO操作。对于的聚簇索引来说,数据和索引是在一起的,不存在回表这一说。

引用了子查询的条件不能下推;

引用了存储函数的条件不能下推,因为存储引擎无法调用存储函数。

索引下推优缺点

索引下推的优点

  • 减少了回表的次数和范围,提高了查询速度
  • 减少了数据传输量和网络开销
    索引下推的缺点
  • 增加了索引扫描的复杂度和开销
  • 可能会导致误判或漏判,因为索引层无法处理一些复杂或模糊的过滤条件

因此,在使用索引下推时,我们需要注意一些限制和条件,比如:

  • 索引下推只适用于联合索引
  • 索引下推只适用于部分数据类型和操作符
  • 索引下推可能会受到优化器的影响

相关系统参数

索引条件下推默认是开启的,可以使用系统参数optimizer_switch来控制器是否开启。
查看默认状态:

mysql> select @@optimizer_switch\G;
*************************** 1\. row
: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)

切换状态:

set ="index_condition_pushdown=off";
set ="index_condition_pushdown=on";

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

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

Docker(1)

文章目录 Docker物理机部署的缺点虚拟机Docker 与虚拟机的区别Docker 的优势 Docker 概念安装 DockerDocker 架构镜像加速Docker 命令进程服务相关命令 镜像相关文件命令容器相关的命令 镜像加载的原理UnionFS(联合文件系统)docker 镜像加载原理 容器的数据卷数据卷概念配置数据…

数据库实验:SQL的数据定义与单表查询

目录 实验目的实验内容实验要求实验过程实验步骤实例代码结果示意 数据库的实验&#xff0c;对关系型数据库MySQL进行一些实际的操作 实验目的 (1) 掌握DBMS的数据定义功能 (2) 掌握SQL语言的数据定义语句 (3) 掌握RDBMS的数据单表查询功能 (4) 掌握SQL语言的数据单表查询语句…

这两天公司面了一个字节来的要求月薪23K,明显感觉他背了很多面试题...

最近有朋友去字节面试&#xff0c;面试前后进行了20天左右&#xff0c;包含4轮电话面试、1轮笔试、1轮主管视频面试、1轮hr视频面试。 据他所说&#xff0c;80%的人都会栽在第一轮面试&#xff0c;要不是他面试前做足准备&#xff0c;估计都坚持不完后面几轮面试。 其实&…

魔术般的速度,焕然一新的磁盘空间 - Magic Disk Cleaner for Mac 2023

在当今这个信息时代&#xff0c;我们的磁盘空间无时无刻不在被各种文件和数据所填满。无论是工作文件&#xff0c;还是日常生活的照片、视频&#xff0c;亦或是下载的各种应用程序&#xff0c;都在不断地蚕食着我们的磁盘空间。面对这种情况&#xff0c;一款高效、便捷的磁盘垃…

springboot整合七牛云oss操作文件

文章目录 springboot整合七牛云oss操作文件核心代码&#xff08;记得修改application.yml配置参数⭐&#xff09;maven依赖QiniuOssProperties配置类UploadControllerResponseResult统一封装响应结果ResponseType响应类型枚举OssUploadService接口QiniuOssUploadServiceImpl实现…

PerfectPixel 插件,前端页面显示优化工具

1.简介 PerfectPixel 插件是一款适用于 Chrome 浏览器的网页前端页面显示优化工具&#xff0c;该插件能够帮助开发人员和标记设计人员在开发时将设计图直接加载至网页中&#xff0c;与已成型的网页进行重叠对比&#xff0c;以规范网页像素精度 作为一款可以优化前端页面显示的…

Idea快速生成测试类

例如写写完一个功能类,需要对里面方法进行测试 在当前页面 按住CTRLSHFITT 选择你要生成的测试方法 点击OK,就会在test目录下在你对应包下生成对应测试类

防范欺诈GPT

去年&#xff0c;ChatGPT的发布让全世界都感到惊讶和震惊。 突然间出现了一个平台&#xff0c;它比之前的任何其他技术都更深入地了解互联网。人工智能可以被训练成像阿姆一样说唱&#xff0c;以世界著名诗人的风格写作&#xff0c;并精确地翻译内容&#xff0c;以至于它似乎能…

C语言KR圣经笔记 2.8自增和自减 2.9位运算 2.10赋值

2.8 自增和自减操作符 C提供了两个不同寻常的操作符&#xff0c;用于对变量进行自增和自减。自增操作符对操作数加上1&#xff0c;而自减操作符 -- 对操作数减去1。我们已经频繁使用 对变量进行自增&#xff0c;如&#xff1a; if (c \n)nl; 不寻常之处在于 和 -- 既能用作…

STM32-创建项目流程

一、基于STM官网得库进行开发 准备工作&#xff1a;下载STM库文件 1、创建项目文件夹 2、在keil 中new uVision project,然后选择刚刚创建得文件夹&#xff0c;在文件夹里面创建一个文件&#xff0c;用来存放这个项目&#xff0c;然后在文件夹里面&#xff0c;写个文件名&am…

掌握微信批量添加好友技巧,让你的社交更高效

微信作为当今的热门通讯工具&#xff0c;在企业营销中扮演着越来越重要的角色。然而&#xff0c;微信并没有提供自动批量添加好友的功能&#xff0c;给运营者带来了不小的挑战。一个个手动添加不仅耗时&#xff0c;而且频繁操作还容易导致账号被封。本文将介绍几种手动批量添加…

Python 应用 之 转换音频格式

目录 一、python音频转换 1、pydub 音频包安装 2、 ffmpeg安装 1&#xff09;、解压后&#xff0c;添加到环境变量中 2&#xff09;、可以直接放在python安装目录下 3、python程序 1&#xff09;、引入相关包 2&#xff09;、重命名 3&#xff09;、to Mp3 4&#xf…

el-table中的el-input标签修改值,但界面未更新,解决方法

el-table中的el-input标签修改值&#xff0c;界面未更新 在el-table中的el-input里面写的change事件根本不触发&#xff0c;都不打印&#xff0c;试了网络上各种方法都没用 然后换成input事件&#xff0c;input事件会触发&#xff0c;但界面也未更新。我在触发事件的时候&…

【设计模式】第23节:行为型模式之“策略模式”

一、简介 策略模式&#xff1a;定义一族算法类&#xff0c;将每个算法分别封装起来&#xff0c;让它们可以互相替换。策略模式可以使算法的变化独立于使用它们的客户端&#xff08;这里的客户端代指使用算法的代码&#xff09;。 二、适用场景 动态选择算法多种类似的行为 …

idea中启动多例项目配置

多实例启动 日常本地开发微服务项目时&#xff0c;博主想要验证一下网关的负载均衡以及感知服务上下线能力时&#xff0c;需要用到多实例启动。 那么什么是多实例启动嘞&#xff1f;简单说就是能在本地同时启动多个同一服务。打个比方项目中有一个 MobileApplication 服务&…

Redis 应用问题

1-缓存穿透 1.1-问题描述 Key 对应的数据在数据源并不存在&#xff0c;每次针对此 Key 的请求从缓存获取不到&#xff0c;请求都会压到数据源&#xff0c;从而可能压垮数据源。 比如&#xff1a;用一个不存在的用户ID 获取用户信息&#xff0c;不论缓存还是数据库都没有&…

项目实战之安装依赖npm install

文章目录 nvmdeasync包和node-gyp报错deasync包node-gyp报错 前言&#xff1a;有些人看着还活着其实已经凉了好一会儿了。 初拿到项目 初拿到项目肯定是先看配置 package.json的啦&#xff0c;看看都需要安装什么依赖&#xff0c;然后 npm install,OK结束 皆大欢喜。 ————…

第十五章 EM期望极大算法及其推广

文章目录 导读符号说明混合模型伯努利混合模型(三硬币模型)问题描述三硬币模型的EM算法1.初值2.E步3.M步初值影响p,q 含义 EM算法另外视角Q 函数BMM的EM算法目标函数LEM算法导出 高斯混合模型GMM的EM算法1. 明确隐变量, 初值2. E步,确定Q函数3. M步4. 停止条件 如何应用GMM在聚…

html获取网络数据,列表展示 一

html获取网络数据&#xff0c;列表展示 js遍历json数组中的json对象 image.png || - 判断数据是否为空&#xff0c;为空就显示 - <!DOCTYPE html> <html><head><meta charset"utf-8"><title>网页列表</title></head><b…

Vite 的基本原理,和 webpack 在开发阶段的比较

目录 1&#xff0c;webpack 的流程2&#xff0c;Vite 的流程简单编译 3&#xff0c;总结 主要对比开发阶段。 1&#xff0c;webpack 的流程 开发阶段大致流程&#xff1a;指定一个入口文件&#xff0c;对相关的模块&#xff08;js css img 等&#xff09;先进行打包&#xff0…