MySQL索引之联合索引

article/2025/10/13 9:47:56

目录

  • 1. 联合索引
    • 1.1. 联合索引的存储结构
    • 1.2. 联合索引的查询流程
    • 1.3. 最左前缀匹配原则
      • 1.3.1. 最左前缀匹配原则说明
  • 2. 索引下推
    • 2.1. 无索引下推的执行流程
    • 2.2. 有索引下推的执行流程

1. 联合索引

在平时开发中，我们最常见的是聚集索引，但在我们需要多条件查询的时候，就不得不建立联合索引，来提高我们的查询效率

  • 联合索引：也称复合索引，就是建立在多个字段上的索引。联合索引的数据结构依然是 B+ Tree
  • 一颗 B+ Tree 只能根据一个值来构建，所以联合索引使用 最左 的字段来构建 B+ Tree

1.1. 联合索引的存储结构

如下图所示，表的数据如右图，ID 为主键，创建的联合索引为 (A，B)，注意联合索引顺序，左图是模拟的联合索引的 B+ Tree 存储结构
在这里插入图片描述

  • 叶子节点是线性排列，并且每个节点的数据排列顺序和创建索引字段的顺序一致。如 1,1，1,2，3,1 是对应着联合索引 (A，B) 字段顺序的，可以对照右图看
  • 非叶子节点存储完整的索引关键字信息，排列规则和叶子节点一致。如 2,8，4,3 是对应着联合索引 (A，B) 字段顺序的
  • InnoDB 会使用主键索引 B+ Tree 维护索引和数据文件，同样联合索引 (A，B) 也会生成一个 B+ Tree ，只不过联合索引 B+ Tree的 data 部分存储的是联合索引所在行的主键值。如 01，02，102，09 等它们是联合索引所在行的主键值
  • 根据图中叶子节点的数据可以看出，所有的数据都是按照列 A 进行排序的 1，1，1，2，3，3，4，4；B 列的顺序为 1，2，2，1，1，5，1，5，B 列是 全局 无序的。如果使用 B = 1 这种查询条件是没有办法使用到索引的，因为联合索引首先是按 A 排序的（使用 最左 的字段来构建 B+ Tree），B 是无序的
  • 我们还可以发现在 A 值相等的情况下，B 值又是按顺序排列的，但是这种顺序是局部的。如 1,1，1,2，1,2，3,1，3,5。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如 A = 1 and B = 2，A,B 字段都可以使用索引，因为在 A 值确定的情况下 B 是相对有序的，而 A > 1 and B = 2，A 字段可以匹配上索引，但 B 值不可以，因为 A 的值是一个范围，在这个范围中 B 是无序的

1.2. 联合索引的查询流程

  • InnoDB 会使用主键索引 B+ Tree 维护索引和数据文件，同样联合索引 (A，B) 也会生成一个 B+ Tree ，只不过联合索引 B+ Tree的 data 部分存储的是联合索引所在行的主键值
  • 拿到联合索引所在行的主键值后，在通过主键索引 B+ Tree 就可以直接拿到具体的行数据了

1.3. 最左前缀匹配原则

最左优先，以最左边的为起点任何 连续 的索引都能匹配上，但遇到范围查询 (>、<、between、like) 就会停止匹配。之所以会有 最左前缀匹配原则和联合索引的索引构建方式及存储结构 是有关系的

  • 联合索引是使用多列索引的第一列（最左）构建的 B+ Tree
  • 用上面 (A，B) 的例子就是使用 A 列构建的 B+ Tree ，当 A 列值相等时再以 B 列进行排序（相对有序）

1.3.1. 最左前缀匹配原则说明

  • 如查询 A = 1，那么 A 字段肯定是可以使用索引的，因为所构建的索引 B+ Tree 是依据最左即 A 列构建的，数据自然是全局有序的
  • 如查询 A = 1 and B = 2，那么 A,B 字段都可以使用索引，因为在 A 值确定的情况下 B 是相对有序的
  • 如查询 B= 2 and A = 1，对于这种情况 A,B 字段都可以使用索引的。MySQL 查询优化器会判断纠正这条 sql 语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。所以，当然是我们能尽量的利用到索引时的查询顺序效率最高咯，所以 MySQL 查询优化器会最终以这种顺序进行查询执行
  • 另外如查询 B = 2，这种情况 也是会用到索引的；可观察 explain 结果中的 type 字段是一个 index，index 与 ALL 的区别为 index 类型只遍历索引树，这通常比 ALL 快。而上述 3 种查询情况 type 字段是一个 ref。详情：https://blog.csdn.net/qq_24690761/article/details/52787897

2. 索引下推

现在有一个需求：检索出表中名字第一个字是张，而且没有删除的信息 (is_del = 1)

select * from t_user where name like '张%' and is_del=1
  • 在 MySQL 5.6 之前，只能从匹配的位置一个个回表。到主键索引上找出数据行，再对比字段值
  • 在 MySQL 5.6 中引入的索引下推优化 (index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

2.1. 无索引下推的执行流程

根据 (username, is_del) 联合索引查询所有满足名称以“张”开头的索引，然后回表查询出相应的全行数据，然后再筛选出未删除的用户数据。过程如下图

每一个虚线箭头表示回表一次
在这里插入图片描述

2.2. 有索引下推的执行流程

每一个虚线箭头表示回表一次
在这里插入图片描述
图 1 跟图 2 的区别是，InnoDB 在 (username, is_del) 索引内部就判断了数据是否逻辑删除，对于逻辑删除的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID1、ID4 这两条记录回表取数据判断，就只需要回表 2 次

  • InnoDB 引擎的表，索引下推 只能用于联合索引，因为 InnoDB 的主键索引树叶子结点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果
  • 索引下推一般可用于所求查询字段（select 列）不是或不全是联合索引 的字段，查询条件为多条件查询且查询条件子句（where 或 order by）字段全是联合索引

参考：https://blog.csdn.net/sinat_41917109/article/details/88944290


http://chatgpt.dhexx.cn/article/JB2GNghx.shtml

相关文章

jedis和redisTemplate

jedis和redisTemplate

使用原生jedis和spring的redisTemplate调用连接池&#xff0c;发现差别巨大&#xff1a; redis配置&#xff1a; redis:database: 0host: 127.0.0.1port: 6379password: 123456timeout: 5000lettuce:shutdown-timeout: 200pool:max-active: 500max-idle: 100min-idle: 50max-w…
阅读更多...
16.Jedis

16.Jedis

目录 一、Jedis知识点总览。 二、连接池配置&#xff1a; 三、测试类&#xff1a; 一、Jedis知识点总览。 public class ProvinceServiceImpl implements ProvinceService {private ProvinceDao dao new ProvinceDaoImpl();Overridepublic List<Province> findAll(…
阅读更多...
Jedis的配置和使用

Jedis的配置和使用

什么是jedis 是官方推荐的java连接开发工具&#xff0c;使用java操作Redis的中间件&#xff0c;如果要使用java操作redis&#xff0c;那么要对jedis十分熟悉 测试 导入对应的依赖(Jedis和fastjson)&#xff1a; <dependencies><dependency><groupId>redi…
阅读更多...
Jredis操作redis的入门级例子

Jredis操作redis的入门级例子

redis入门级例子&#xff1a; Java代码 package com.liuxinglanyue.test; import java.util.ArrayList; import java.util.List; import org.jredis.JRedis; import org.jredis.RedisException; import org.jredis.ri.alphazero.JRedisClient; import org.…
阅读更多...
jedis入门

jedis入门

目录 一、Jedis 1.Jedis简介 2.导包 3.官方文档 4.常用API 5.基本操作 6.jedis连接池的使用 7.工具类的编写 8.简单的使用 9.JedisPoolConfig的配置参数 一、Jedis 1.Jedis简介 Redis不仅是使用命令来操作&#xff0c;现在基本上主流的语言都有客户端支持&#xf…
阅读更多...
Jedis的简单使用

Jedis的简单使用

Jedis的简单使用 创建连接操作key操作string操作list操作set操作zset操作hashJedis连接池工具类 创建连接 public static Jedis jedis null;static {// 地址 和 端口jedis new Jedis("127.0.0.1", 6379);// jedis.auth("helloworld"); // 若你的redis设…
阅读更多...
【国产开源】兼容redis协议的内存数据库

【国产开源】兼容redis协议的内存数据库

背景 jredis是一个高性能、高可用、低延迟的内存数据库&#xff0c;服务端源码请移步这里 编写目的 加深对底层网络传输&#xff0c;文件存储&#xff0c;文件索引的认知&#xff0c;同时也巩固自身的知识点。 协议特征 兼容redis原生协议 set get lpush rpush lrange blp…
阅读更多...
Jedis 入门

Jedis 入门

Jedis官网快速跳转 Jedis 的 Github 官方网站跳转&#xff1a;redis/jedis 1. Jedis的基本使用 1.1 引入Jedis依赖 <dependency><groupId>redis.clients</groupId><artifactId>jedis</artifactId><version>4.2.3</version> </d…
阅读更多...
Redis Java客户端Jredis

Redis Java客户端Jredis

JRedis 是一个高性能的 Java 客户端&#xff0c;用来连接到Redis分布式哈希键-值数据库。提供同步和异步的连接。 项目地址&#xff1a;https://github.com/alphazero/jredis 由于jreds的jar包不在公网的maven仓库中&#xff0c;所以需要下载源码使用如下命令&#xff0c;将j…
阅读更多...
4.jedis

4.jedis

1.pom依赖 <dependency><groupId>redis.clients</groupId><artifactId>jedis</artifactId><version>2.9.0</version> </dependency>2.客户端连接redis
阅读更多...
JDK: JRE

JDK: JRE

序言 这个跟类加载有关. 其它的没啥好说的,直接说重点吧 重点 我们安装JDK后会有两个目录一个是JDK,一个是JRE.如下图所示: JDK: 这个是我们开发时需要往Eclipse或者Idear中引入的,里面主要包含了编译器,即把Java文件编译成Class文件的相关Jar.以及开发的过程所需要的一些工…
阅读更多...
Jedis详解

Jedis详解

因为工作的需要&#xff0c;底层同事对Redis进行了部分改造&#xff0c;增加了几个命令&#xff0c;对应着也就需要对Jedis进行部分修改&#xff0c;于是就把Jedis相关的代码读了一遍&#xff0c;发现其设计还是非常简单但又巧妙使用。 通常而言&#xff0c;我们对于Redis集群…
阅读更多...
Jedis简介

Jedis简介

Jedis用于java语言连接redis服务&#xff0c;并提供对应的操作API 一、准备工作 (1)jar包导入 下载地址&#xff1a;https://mvnrepository.com/artifact/redis.clients/jedis 基于maven <dependency> <groupId>redis.clients</groupId> <artifactId&…
阅读更多...
jedis介绍

jedis介绍

jedis是redis的java版本的客户端实现。下面演示jedis的相关操作&#xff1a; 　　 　　首先在eclipse新建动态web工程&#xff1a; 　　 　　 　　 　　将jedis所需的jar包导入到工程中&#xff1a; 　　 　　 　　 　　编写Java代码测试连通性&#xff1a; import …
阅读更多...
jedis相关详解

jedis相关详解

一、jedis是什么&#xff1f; Jedis是redis的java版本的客户端实现&#xff0c;使用Jedis提供的Java API对Redis进行操作&#xff0c;是Redis官方推崇的方式&#xff1b;并且&#xff0c;使用Jedis提供的对Redis的支持也最为灵活、全面&#xff1b;不足之处&#xff0c;就是编…
阅读更多...
Redis 客户端：Jredis 和 spring-data-redis 整合

Redis 客户端：Jredis 和 spring-data-redis 整合

因为我使用的是 java &#xff0c;所以我学习的是java 的客户端 &#xff1a;Jredis 。整合结合自己的项目&#xff0c;把 Jredis 的客户端整合一下。 我们整合需要的工具&#xff1a; Redis Serviceredis.clients.jedis 2.9.0 &#xff08;java 客户端链接redis&#xff09;…
阅读更多...
Redis使用教程（一）

Redis使用教程（一）

Redis:单线程、高读写 redis数据库初识 Redis 简介 Redis 的安装配置 Redis 的常见操作 Redis 的数据类型 Redis 的事务控制 Java 操作 Redis 数据库 Redis简介 redis的作用 Redis:REmote DIctionary Server( 远程字典服务器 ) 是完全开源免费的&#xff0c;用 C 语言编写的…
阅读更多...
html如何隐藏input,html如何让input隐藏

html如何隐藏input,html如何让input隐藏

隐藏方法&#xff1a;1、设置input标签 type属性的值为“hidden”&#xff1b;2、利用input标签的style属性&#xff0c;添加“display:none”样式&#xff1b;3、利用input标签的style属性&#xff0c;添加“visibility:hidden”样式。 本教程操作环境&#xff1a;windows7系统…
阅读更多...
使用java实现杨辉三角的输出

使用java实现杨辉三角的输出

案例&#xff1a;杨辉三角 实现代码 结果输出
阅读更多...
【Java】 杨辉三角 二维数组打印杨辉三角

【Java】 杨辉三角 二维数组打印杨辉三角

Tips 打印三角的操作 : 每行打印前预留空格打印指定数量的星号后换行重复循环打印直至完成所有打印结果 杨辉三角: 三角规律空格最大对称值为 5 大于 5 则会变形三角规律为当前值为上一个值加上前一个值 首尾的数字值相等参考二维数组的规律生成数值 import java.util.Sca…
阅读更多...
推荐文章

Copyright @ 2022~2023