MySQL 中 聚集索引、非聚集索引、覆盖索引、索引下推 到底是什么

2024-03-25 12:12

本文主要是介绍MySQL 中 聚集索引、非聚集索引、覆盖索引、索引下推 到底是什么,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

一、什么是 聚集索引、非聚集索引

MySQL数据库中,索引是提高查询效率的关键。而聚集索引、非聚集索引、覆盖索引、索引下推其实是索引优化的重要策略之一。那这些名词的含义到底是什么呢?

在开始分析前,先来了解下 B+ 树的索引结构 和 回表查询:

B+ 树结构

B+树是 B树的变体,将树的结构分为了叶子节点非叶子节点。其中非叶子节点不存储具体数据,只存放主键和指向下一级数据的指针。而叶子节点在最尾端,存放主键和指向数据行的地址。叶子节点和非叶子节点采用指针连接,例如下面结构:

在这里插入图片描述
也就是叶子节点包含了主键ID和数据行信息,找到具体叶子节点也就找到了具体数据。

什么是回表查询

回表查询主要针对于非主键索引的情况下,在主键索引中叶子节点包含了主键和数据行信息。但是非主键索引的叶子节点就不是包含主键和数据行信息了,而是索引和该数据的主键ID,所以要拿到完整的行信息,就需要再拿着主键去主键索引中查询一次获得数据,这个过程就叫做回表查询。

了解到这里就可以先来看下 聚集索引非聚集索引了。

聚集索引

聚集索引其实就是上面的 B+树结构,也就是主键索引 就是 聚集索引,它按照数据存储顺序排列,叶子节点包含了整行数据,数据行的物理顺序和索引顺序一致,这样可以减少磁盘 I/O 操作,提高查询效率。

假设有一个名为 user 的表,其中包含 id(主键)、nameagesex 字段。如果我们在id上创建了聚集索引,那么当我们根据 id 进行查询时,数据库引擎会直接通过索引找到对应的数据行,而不需要再进行二次查找,从而提高查询速度。

非聚集索引

非聚集索引其实也就是上面提到的非主键索引的情况下,非聚集索引并不影响数据在磁盘上的存储顺序,而是在磁盘上额外创建一个索引文件,用于存储索引的键和指向对应数据的主键。当根据非聚集索引的列进行查询时,会首先在索引文件中找到对应的主键,然后再查询到实际的数据行。

还是以 user 表为例,如果我们在 name 字段上创建了一个非聚集索引,那么当根据 name 进行查询时,会首先查找索引文件中的 name 键,获得主键 id ,然后再通过主键 id 找到对应的数据行。这种情况下可以加速基于非主键字段的查询,但相比聚集索引会稍慢一些。

二、什么是覆盖索引、索引下推

上面讲述的 聚集索引 和 非聚集索引主要关注点在单索引上的设计不同,而 覆盖索引 和 索引下推,则会关注到 联合索引 或 多个索引 的情况下。

覆盖索引

覆盖索引就是指查询的结果可以直接通过索引返回,而不必再去查找表的数据。当查询语句所需的字段都包含在索引中时,就可以利用覆盖索引,减少对数据表的访问,提高查询性能。也就是减少回表查询的过程。

继续以上面的users表为例,如果我们需要查询所有满足 age > 30 条件的 idagename 字段,其中在 agename 字段上创建了联合索引,那么数据库引擎可以直接通过索引返回满足条件的数据,而不必再去查找原始数据行信息,从而提高查询效率。

但是如何还要查询 sex 字段,此时覆盖索引就不满足了,需要进行回表查询补充完整的数据信息。

索引下推

索引下推(Index Condition Pushdown,简称ICP)是MySQL 5.6版本引入的一种优化技术,将应该在 server 层进行筛选的条件,尽量下推到存储引擎层来进行筛选判断,目的也是减少 回表查询 的过程。它可以在索引的扫描过程中,对索引条件进行判断和过滤,减少不必要的行访问,从而提高查询性能。

在这里插入图片描述

MySQL 中该功能默认是开启的,可以使用下面指令查看:

select @@optimizer_switch;

在这里插入图片描述

如果想要关闭或开启可以使用下列指令:

关闭索引下推:

set optimizer_switch = 'index_condition_pushdown=off'

开启索引下推:

set optimizer_switch = 'index_condition_pushdown=on'

这里举个例子来理解下索引下推,还是上面 user 表,假如给 nameage 字段设置了联合索引,然后进行查询:

 select id,name,age from user where name like '小%' and age > 15

当没有索引下推的时候:

  1. 通过联合索引找到 name like '小%' 的主键 ID
  2. 然后进行回表找个数据行信息,交给 server
  3. server 再根据其余条件进行筛选。

当有索引下推的时候:

  1. 通过联合索引找到 name like '小%' 的索引信息。
  2. 判断索引中是否有 age ,如果有则一起筛选。
  3. 返回信息给到 server

索引下推的目的是为了减少回表次数,主要减少磁盘的IO操作。主要针对于非聚集索引的一种优化手段。

通过 explain 也可以看出是否进行了索引下推:

explain select * from user where name like '小%' and age > 15

当开启了 索引下推 时,Extra 使用的是 Using index condition

在这里插入图片描述

当关闭 索引下推 时,Extra 使用的是 Using where

在这里插入图片描述

这篇关于MySQL 中 聚集索引、非聚集索引、覆盖索引、索引下推 到底是什么的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!


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

相关文章

MySQL基本查询示例总结

《MySQL基本查询示例总结》:本文主要介绍MySQL基本查询示例总结,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录Create插入替换Retrieve(读取)select(确定列)where条件(确定行)null查询order by语句li

查看MySql主从同步的偏移量方式

《查看MySql主从同步的偏移量方式》:本文主要介绍查看MySql主从同步的偏移量方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 1.mysql的主从同步方案mysqlphp为了在实现读写分离,主库写,从库读mysql的同步方案主要是通过从库读取主库的binl

MySQL表空间结构详解表空间到段页操作

《MySQL表空间结构详解表空间到段页操作》在MySQL架构和存储引擎专题中介绍了使用不同存储引擎创建表时生成的表空间数据文件,在本章节主要介绍使用InnoDB存储引擎创建表时生成的表空间数据文件,对... 目录️‍一、什么是表空间结构1.1 表空间与表空间文件的关系是什么?️‍二、用户数据在表空间中是怎么

Java JSQLParser解析SQL的使用指南

《JavaJSQLParser解析SQL的使用指南》JSQLParser是一个Java语言的SQL语句解析工具,可以将SQL语句解析成为Java类的层次结构,还支持改写SQL,下面我们就来看看它的具... 目录一、引言二、jsQLParser常见类2.1 Class Diagram2.2 Statement

mysql中的group by高级用法详解

《mysql中的groupby高级用法详解》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,本文给大家介绍mysql中的groupby... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使

MySQL数据库实现批量表分区完整示例

《MySQL数据库实现批量表分区完整示例》通俗地讲表分区是将一大表,根据条件分割成若干个小表,:本文主要介绍MySQL数据库实现批量表分区的相关资料,文中通过代码介绍的非常详细,需要的朋友可以参考... 目录一、表分区条件二、常规表和分区表的区别三、表分区的创建四、将既有表转换分区表脚本五、批量转换表为分区

宝塔安装的MySQL无法连接的情况及解决方案

《宝塔安装的MySQL无法连接的情况及解决方案》宝塔面板是一款流行的服务器管理工具,其中集成的MySQL数据库有时会出现连接问题,本文详细介绍两种最常见的MySQL连接错误:“1130-Hostisn... 目录一、错误 1130:Host ‘xxx.xxx.xxx.xxx’ is not allowed

sql语句字段截取方法

《sql语句字段截取方法》在MySQL中,使用SUBSTRING函数可以实现字段截取,下面给大家分享sql语句字段截取方法,感兴趣的朋友一起看看吧... 目录sql语句字段截取sql 截取表中指定字段sql语句字段截取1、在mysql中,使用SUBSTRING函数可以实现字段截取。例如,要截取一个字符串字

SQL Server身份验证模式步骤和示例代码

《SQLServer身份验证模式步骤和示例代码》SQLServer是一个广泛使用的关系数据库管理系统,通常使用两种身份验证模式:Windows身份验证和SQLServer身份验证,本文将详细介绍身份... 目录身份验证方式的概念更改身份验证方式的步骤方法一:使用SQL Server Management S

MySQL 字符串截取函数及用法详解

《MySQL字符串截取函数及用法详解》在MySQL中,字符串截取是常见的操作,主要用于从字符串中提取特定部分,MySQL提供了多种函数来实现这一功能,包括LEFT()、RIGHT()、SUBST... 目录mysql 字符串截取函数详解RIGHT(str, length):从右侧截取指定长度的字符SUBST