Mysql【索引覆盖、索引下推、索引合并、索引跳跃】介绍

        索引覆盖、索引下推、索引合并、索引跳跃都是Mysql对索引的优化手段,它们的思想就是尽量让查询数据走索引,那它们有什么区别呢?

一、首先介绍一下MySQL体系结构

上图来自MySQL官方文档。
通常把MySQL从上至下分为以下几层:

  • MySQL服务层:包括NoSQL和SQL接口、查询解析器、优化器、缓存和Buffer等组件。
  • 存储引擎层:各种插件式的表格存储引擎,实现事务、索引等各种存储引擎相关的特性。
  • 文件系统层: 读写物理文件。

MySQL服务层负责SQL语法解析、触发器、视图、内置函数、binlog、生成执行计划等,并调用存储引擎层去执行数据的存储和检索。我们后续说到的“索引下推”,它的“下”其实就是指将部分上层(服务层)负责的事情,交给了下层(存储引擎)去处理。

二、再介绍一下它们的概念

1、索引覆盖(Index Covering):索引覆盖是指查询的列包含在索引中,而不需要再次访问数据行。换句话说,查询可以直接从索引中获取所需的数据,而不必去查找实际的数据行。这样可以减少I/O操作,提高查询性能。

2、索引下推(Index Pushdown):索引下推是MySQL5.6中的新技术,是一种数据库查询优化技术,它利用了数据库引擎中的索引和过滤条件,将部分过滤工作下推到存储引擎层面进行处理,从而减少不必要的数据读取和传输。

        在传统的查询执行过程中,数据库引擎首先根据索引定位到符合过滤条件的数据行,并将这些数据行读取到内存中,然后再进一步进行过滤操作。而索引下推则再这一步骤中尽可能将过滤操作下推到存储引擎层面,避免将不符合条件的数据行读取到内存中,减少了IO次数。

索引下推(简称ICP)的条件:

  • 只能用于二级索引(secondary index);
  • explain显示的执行计划中type值(join 类型)为 range 、ref 、 eq_ref 或者 ref_or_null ;
  • 并非全部where条件都可以用ICP筛选,如果where条件的字段不在索引列中,还是要读取整表的记录到server端做where过滤;
  • ICP只可以用于MyISAM和InnnoDB存储引擎;

通过一下命令开启/关闭索引下推(mysql 5.6之后默认开启)

set optimizer_switch="index_condition_pushdown=off";

set optimizer_switch="index_condition_pushdown=on";

3、索引合并(Index Merge):索引合并是指数据库系统在执行查询时,利用多个索引来加速查询的过程。它通过同时使用多个索引,并将它们的结果合并,来获取最终的查询结果。索引合并通常在数据库系统无法选择一个最优的单一索引来满足查询需求时使用。

索引合并又包含三个算法,在explain中显示:

using intersect:
index merge intersection access algorithm(索引合并交集访问算法)。
对于每一个使用到的索引进行查询,查询主键值集合,然后进行合并,求交集,也就是AND运算。

using union:
index merge union access algorithm(索引合并并集访问算法)
容易看出,与上述的算法类似,不过是使用了or连接条件,求并集。
执行流程与index merge intersect 类似,依旧是查询了有序的主键集合,然后进行求并集。

using sort_union:
index merge sort sort-union access algorithm (索引合并排序并集访问算法)
根据索引查询得到主键集合,对于每个主键集合进行排序,然后求并集。

4、索引跳跃(Index Skip Scan):索引跳跃是一种优化技术,用于在多列索引中查找数据,即使查询不是以索引的第一列开始。当索引的第一列选择性很差时,索引跳跃可以跳过该列,并在后续列上进行查找。这可以减少所需的索引扫描次数,从而提高查询性能。

我们可以通过SHOW VARIABLES like '%optimizer_switch%'查看它们的开启情况

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,use_invisible_indexes=off,

skip_scan=on(索引跳跃),

hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,

hypergraph_optimizer=off,derived_condition_pushdown=on

三、接下来创建一张员工表分别模拟一下这四个场景

1、首先我们创建了一个名为 employees 的表并创建相关索引,有 idnameagedepartment 四个字段。

-- 创建员工表
CREATE TABLE employees (
    ID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age int,
    department VARCHAR(50)
);

-- 创建name 和 age 列的联合索引
CREATE INDEX idx_name_age ON employees (name, age);

-- 创建部门列的单列索引
CREATE INDEX idx_department ON employees (department);

-- 创建覆盖了 name 和 department 列的联合索引
CREATE INDEX idx_name_department ON employees (name, department);

-- 创建年龄列的单列索引
CREATE INDEX idx_age ON employees (age);

2、加入一些模拟数据,向数据库插入20条数据

INSERT INTO employees (name, age, department) VALUES
('张三', 30, '人力资源部'),
('李四', 25, '市场部'),
('王五', 35, '财务部'),
('赵六', 28, '信息技术部'),
('刘七', 32, '人力资源部'),
('陈八', 27, '市场部'),
('周九', 40, '财务部'),
('吴十', 26, '信息技术部'),
('郑十一', 33, '人力资源部'),
('孙十二', 29, '市场部'),
('朱十三', 38, '财务部'),
('冯十四', 24, '信息技术部'),
('田十五', 31, '人力资源部'),
('马十六', 26, '市场部'),
('韩十七', 37, '财务部'),
('顾十八', 23, '信息技术部'),
('张十九', 29, '人力资源部'),
('李二十', 28, '市场部'),
('王二十一', 39, '财务部'),
('赵二十二', 27, '信息技术部');

1)索引覆盖场景:因为现在我们数据库有 name 和 age  列的联合索引,当我们只查询 name 和 age时,就会走索引覆盖。

-- 查询员工姓名和年龄【索引覆盖】
SELECT name, age FROM employees;

 我们用explain分析一下:

可以看到type显示的是index,表示查询语句在索引字段中遍历,再通过索引指向存储实际数据的地方,而不需要回表去磁盘遍历;

并且Extra字段里面显示Using index,这个就表示使用了索引覆盖;

2)索引下推场景:因为我们建立了(name,age)的联合索引,在一起作为条件过滤时,两个过滤条件都会下推到存储引擎层进行过滤,而无需返回服务层进行where过滤。

-- 查询姓张并且年龄>20的员工信息【索引下推】

SELECT  *  FROM employees WHERE NAME LIKE '张%'  AND age > 20 ;

我们先关闭索引下推,用explain分析一下:

set optimizer_switch="index_condition_pushdown=off";

可以看到使用了联合索引(name,age),Extra是Using where,也就是表示使用了where条件过滤。

过程:先在存储引擎层通过name索引查询以 '张' 开头的id,再回表查询这部分id的数据,再回到MySQL服务层使用where条件过滤age>20。(联合索引只能走name这一部分)

然后我们打开索引下推,再用explain分析一下:

set optimizer_switch="index_condition_pushdown=on";

可以看到使用了联合索引(name,age),并且Extra是Using index condition,也就是使用了索引下推。

过程:先在存储引擎层通过name索引查询以 '张' 开头的id,然后在这一批id中,继续在存储引擎层通过age索引查出年龄大于20的id,回表查询剩余id的数据,然后将符合条件的数据返回到MySQL服务层。(在储存引擎层过滤了name和age)

因为【使用了索引下推,是在符合name条件的id的基础上,再次筛选符合age条件的id】所以它符合条件的id数量一定小于等于【未使用索引下推,筛选出只符合name条件的id数量】,因为它们都要通过id回表,即索引下推最大限度减少了回表的次数。

3)索引合并场景:因为我们的name和department都是单列索引,在一起作为条件过滤时,会将两个结果集合并,得到最终的结果。

-- 查询姓张并且部门为市场部的员工信息【索引合并】

SELECT  * FROM employees WHERE name = '张三'  OR department = '市场部';

我们使用explain分析一下:

可以看到此时type为index_merge,为索引合并;

同时Extra中显示Using sort_union(idx_name_age,idx_department),表示索引合并中的排序并集操作。

它与索引下推的区别是:

        索引下推应用于联合索引,在存储引擎层面一层一层筛选出符合条件的id集合;而索引合并是应用于多个单列索引,并且将每个单列索引得到的id集合取交集,获得最后符合条件的id集合;

4)索引跳跃场景:因为现在我们数据库有 name 和 age  列的联合索引,当我们只查询 age时,它就会跳过name索引,直接走age索引,不必去匹配最左原则。

-- 查询年龄>20的员工信息【索引跳跃】

SELECT * FROM employees FORCE INDEX(idx_name_age) WHERE age > 20;

我们再次用explain分析一下:

发现它的type是ALL,并没有走索引;

这可能是由于数据量太少,执行器在优化阶段直接选择了全表扫描。

如果走了索引跳跃,应该是下图的场景,Extra为Using index for skip scan;

走索引跳跃有三个条件:

  1. Mysql的版本在8.0以上;
  2. 在优化器选项中设置skip_scan = on;
  3. Msql的优化器认为走索引跳跃的成本更低,效率更快。

索引跳跃可以参考:MySQL8.0优化(一)松散索引扫描/索引跳跃扫描ISS - 掘金 (juejin.cn)

ps:以下是我整理的java面试资料,感兴趣的可以看看。最后,创作不易,觉得写得不错的可以点点关注!

链接:https://www.yuque.com/u39298356/uu4hxh?# 《Java知识宝典》 

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

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

相关文章

备考ICA----Istio实验18---单集群中部署多个Istio控制面

备考ICA----Istio实验18—单集群中部署多个Istio控制面 单个 Kubernetes 控制面以及多个 Istio 控制面和多个网格。通过 Kubernetes 命名空间和 RBAC 实现软多租户业务隔离。 1. 环境准备 1.1 创建2个命名空间 kubectl create ns usergroup-1 kubectl label ns usergroup-…

外包干了6天,技术明显进步

先说一下自己的情况,本科生,2019年我通过校招踏入了南京一家软件公司,开始了我的职业生涯。那时的我,满怀热血和憧憬,期待着在这个行业中闯出一片天地。然而,随着时间的推移,我发现自己逐渐陷入…

探索Kubernetes的大二层网络:原理、优势与挑战

在云原生领域,Kubernetes (K8s) 已经成为容器编排的事实标准☁️📦。为了支撑其灵活的服务发现和负载均衡🔍🔄,K8s采用了大二层网络的设计理念🕸️。本文将深入探讨大二层网络的工作原理、带来的好处✨&…

在线JSON工具

功能支持 ctrls json格式化游览器本地保存ctrla ctrlc 自动检测选中范围是否是全选,然后按照格式化方式添加到粘贴板中json 粘贴JSON自动格式化json可视化修改json压缩复制json层级折叠json关键key 搜索(自动提示高亮)满足某些近视的可以自行调整字体大小, 并且会游…

敦煌网、速卖通、国际站铺需要自养号补单来稳定出单率吗?

亚马逊、速卖通、Lazada、shoppe、速卖通、敦煌网、Temu、shein、美客多、阿里国际、卖家如何保证店铺出单稳定?在竞争激烈的平台上,保持店铺的稳定出单是每个卖家都追求的目标。为了实现这一目标,卖家需要综合考虑产品、运营、客户服务等多个方面的因素…

推动科技创新润德生物邀您到场参观2024第13届生物发酵展

参展企业介绍 山东润德生物科技有限公司成立于2014年10月17日,是一家围绕生物制品的研发、生产、营销、国际贸易、技术服务为核心业务的国家高新技术企业,近年来荣获国家制造业单项冠军示范企业、国家级绿色工厂、国家知识产权优势企业、国家工业产品绿…

Nacos Namespace 未授权访问漏洞

Nacos Namespace 未授权访问漏洞 问题 nacos 源码启动,发现即使开启了鉴权:nacos.core.auth.enabledtrue,未登录情况下,命名空间列表接口仍旧能查询到数据 鉴权逻辑 通过**AuthFilter **进行权限校验判断方法上是否存在注解 …

电脑硬件 - 硬盘

硬盘是一台电脑的数据中心,存放着我们用户的所有文件和数据 对于一块硬盘,其重要指标:顺序读写能力,随机读写能力 顺序读写影响大文件的拷贝,随机读写影响大量小文件的拷贝(打开软件的快慢) 因…

Chatgpt掘金之旅—有爱AI商业实战篇|内容策展业务|(八)

演示站点: https://ai.uaai.cn 对话模块 官方论坛: www.jingyuai.com 京娱AI 一、AI技术创业内容策展业务有哪些机会? 人工智能(AI)技术作为当今科技创新的前沿领域,为创业者提供了广阔的机会和挑战。随着…

蓝色系UX/UI设计求职面试作品集模版figmasketchPPT可编辑源文件

页面数量: 20P 页面尺寸:1920*1080PX 交付格式:figma、sketch、PPT 赠送文件:24款高质量样机(PSD格式) 该作品集虽然只有20页,但可根据需求复制作品集里已有的页面作为模版来扩展您的设计项目 该作品集模版可编辑可修…

蓝桥杯嵌入式备考笔记

这里写目录标题 keil配置LED-KEY-LCDledkeyLCD最多21位 RTCPWM捕获占空比ADCI2C按键长按uartPWMDAC双击高亮EEp初始化LED闪烁时间倒计时 keil配置 LED-KEY-LCD 留下这几个 按键 创建俩个文件写代码,记得把这两个文件加进工程 led uwTick 1ms执行一次 写错…

基于springboot实现教师人事档案管理系统项目【项目源码+论文说明】计算机毕业设计

基于springboot实现IT技术交流和分享平台系统演示 摘要 我国科学技术的不断发展,计算机的应用日渐成熟,其强大的功能给人们留下深刻的印象,它已经应用到了人类社会的各个层次的领域,发挥着重要的不可替换的作用。信息管理作为计算…

C语言语法最后一个教案-教案21(预处理 · 头文件)

最近给大家争取到一个 深夜福利 保证你在深夜手机刷到 嘎嘎香~ 那就是 官方授权 大流量卡 缺点:月租太便宜 185GB~ 100分钟通话时长~ 长期套餐~ 畅想自由的气息 流量自由的同时还拥有超长通话,而且免费领取。 名额有限,咱们废话不…

Golang | Leetcode Golang题解之第14题最长公共前缀

题目&#xff1a; 题解&#xff1a; func longestCommonPrefix(strs []string) string {if len(strs) 0 {return ""}isCommonPrefix : func(length int) bool {str0, count : strs[0][:length], len(strs)for i : 1; i < count; i {if strs[i][:length] ! str0 …

LiveGBS流媒体平台GB/T28181常见问题-系统服务日志如何配置日志个数日志路径日志时长web操作日志操如何配置保留天数及过滤

LiveGBS系统服务日志如何配置日志个数日志路径日志时长web操作日志操如何配置保留天数及过滤 1、系统服务日志1.1、日志目录1.2、配置日志文件个数及记录时间1.3、配置日志文件路径 2、Web 操作日志2.1、配置保留天数2.2、配置不记录操作日志2.1.1、不记录所有2.1.2、不记录指定…

项目设计方案:市交通视频监控平台项目设计方案(四)

目录 1 前言 1.1 目的 1.2 适用范围 1.3 术语表 2 现状分析 2.1 业务现状 2.2 组织机构现状 2.3 存在的问题 2.4 项目成果预期 3 系统建设原则 4 项目需求 4.1 项目需求 4.1.1 业务需求主要分为三部分&#xff1a; 4.1.2 技术需求主要分为四部分&#xff1a; 4.…

yolov5旋转目标检测遥感图像检测-无人机旋转目标检测(代码和原理)

YOLOv5&#xff08;You Only Look Once version 5&#xff09;是一个流行且高效的实时目标检测深度学习模型&#xff0c;最初设计用于处理图像中的水平矩形边界框目标。然而&#xff0c;对于旋转目标检测&#xff0c;通常需要对原始YOLOv5架构进行扩展或修改&#xff0c;以便能…

AI智能尺码引导未来决策 推动品牌业绩飙升

零售营销中经常遇到的一个问题是同一款商品&#xff0c;不同尺码的销售情况各不相同&#xff0c;在零售连锁经营的格局中&#xff0c;不同门店的商圈因素、消费人群不尽相同&#xff0c;对不同尺码的需求也各不相同。为了最大程度地满足到访消费者的需求&#xff0c;零售品牌都…

【Spring】SpringBoot整合MybatisPlusGernerator,MybatisPlus逆向工程

&#x1f4dd;个人主页&#xff1a;哈__ 期待您的关注 在我们写项目的时候&#xff0c;我们时常会因为需要创建很多的项目结构而头疼。项目中的表很多的时候&#xff0c;我们连实体类都创建不完&#xff0c;这时候就需要我们的逆向工程来帮助我们生成我们的框架结构。这些结构…

vanilla是什么意思?为什么很多名词都要加上vanilla?比如 vanilla Transformer

Vanilla的含义 香草&#xff0c;是指热带攀缘兰的任何一种&#xff0c;以及从其荚中提取的调味剂。Plain vanilla 是一个形容词&#xff0c;描述某事物的最简单版本&#xff0c;没有任何可选的附加功能&#xff0c;基本的或普通的。与常见的冰淇淋味香草类似&#xff0c;随着人…