一篇文章带你掌握MySQL索引下推

目录
  • 1.什么是索引下推
  • 2.案例
    • 2.1.MySQL5.5版本
    • 2.2.MySQL5.7版本
  • 3.小结

1.什么是索引下推

索引下推(Index Condition PushDown,简称ICP)是从MySQL5.6开始引入的一个特性,索引下推通过减少回表的次数来提高数据库的查询效率;

2.案例

准备:

①.为了演示索引下推,需要安装MySQL5.5和MySQL5.7两个版本的MySQL,因为索引下推是MySQL5.6版本中开始引入的新特性,所以这两个版本就可以演示出索引下推的特点;

②.数据库脚本:

CREATE TABLE `user1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `username` (`username`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
insert into user1(username,age,address) values('zhangsan',25,'China'),('lisi',30,'China');

2.1.MySQL5.5版本

1>.精确匹配:

select * from user1 where username='zhangsan' and age=25;

2>.查看执行计划

type: ref表示通过索引查找数据,一般出现在等值匹配的时候,type为ref;

extra: Usering where表示数据在server层进行了过滤操作;

可以看到,这个查询SQL是使用了索引(非主键索引)的!

在MySQL5.5中,由于没有索引下推,所以上面查询SQL的执行流程如下:

①.首先MySQL的server层调用存储引擎获取username='zhangsan’的一条记录;

②.存储引擎找到username='zhangsan’的第一条记录之后,在B+Tree的叶子节点中保存着主键id,此时通过回表操作,去主键索引中找到该条记录的完整数据,并返回给server层;

③.server层拿到数据之后,判断该条记录的age是否为25,如果是,就把该条记录返回给客户端,如果不是,那么就丢弃该条记录;

④.由于userame+age组成的复合索引只是一个普通索引,并不是唯一索引(如果是唯一索引,那么这个查询就到此结束了),所以还需要继续去搜索有没有满足条件的记录;

注意: 第④步的搜索方式,并不是直接去B+Tree中搜索.由于在username索引中,username字段的存储是有序的,即username='zhangsan'的记录都是挨着的,而B+Tree的叶子节点之间通果双向链表关联,通过一个叶子节点就能找到下一个叶子节点(或者上一个叶子节点),第②步返回的数据中有一个next_record属性,该属性就直接指向二级索引的下一条记录,找到下一条记录之后,回表拿到所有数据并返回给server层,然后重复③,④步;

3>.模糊匹配:

select * from user1 where username like 'l%' and age=30;

type: range表示按照范围搜索;

也使用了索引,其SQL的执行流程跟上面一条查询SQL的执行流程基本一致!

小结:

前面两个查询SQL,由于查询的时候是"select *",所以都是需要回表操作的,虽然是复合索引,索引中既有username又有age,但是查询条件中只能传入username到存储引擎中,从存储引擎中回表拿到一行数据的完整记录之后,再返回给server层,再在server层判断age是否满足条件.其实这样的查询效率比较低,明明索引中有age的值,但是却不在索引中比较age的值,而是要回表,取一行的完整记录出来,返回给server层,然后在和age去比较,要是比较不通过,这条记录就会被丢弃了.如果我们能够把age直接传入存储引擎,在存储引擎中直接去判断age是否满足条件.如果满足条件了,再去回表查询完整的记录.如果不满足条件就到此结束,这样就可以减少回表的次数,进而提高查询效率;

从MySQL5.6开始引进的索引下推技术,就是用来解决这样的问题的!

2.2.MySQL5.7版本

1>.模糊匹配:

select * from user1 where username like 'l%' and age=30;

2>.查看执行计划:

可以看到,MySQL5.7中的这个执行计划和上面MySQL5.5中的执行计划相比,主要是最后的Extra为"Using index condition",这就是MySQL5.6开始引入的索引下推技术(ICP);

执行流程如下:

①.MySQL中的server层首先调用存储引擎定位到第一个以"l"开头的username;

②.找到记录后,存储引擎并不急着回表,而是继续在存储引擎中判断这条记录的age是否为30,如果是,再去回表查询完整的记录;如果不是,不去回表了,直接继续读取下一条记录;

③.存储引擎将符合条件的数据返回给server层,此时如果还有其他非索引的查询条件,server层继续过滤,在上面的案例中,此时没有其他查询条件了,server层将最终的数据返回给客户端.假设server层还有其他的查询条件,并且这个查询条件把刚刚查到的记录过滤掉了,那么就会通过该记录中的next_record属性读取下一条记录,然后重复第②步;

这就是索引下推(Index Condition Pushdown,ICP),有效的减少了回表次数,提高了查询效率!

上面的案例索引下推的时候不仅判断age的值也判断username的值;

3>.精确匹配:

select * from user1 where username='zhangsan' and age=25;

可以看到,这个查询计划也使用了索引.如果最后的Extra为null,就表示没有额外的操作了,其实这只是一个特殊的处理而已,利用搜索条件"username='zhangsan' and age=25",从存储引擎中找到数据之后,没有再去重复判断了而已;

3.小结

所谓的索引下推,就是在搜索引擎中提前判断对应的搜索条件是否满足,满足了再去回表,通过减少回表次数进而提高查询效率;

到此这篇关于一篇文章带你掌握MySQL索引下推的文章就介绍到这了,更多相关MySQL索引下推内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • 一篇文章带你了解MySQL索引下推

    一篇文章带你了解MySQL索引下推

    目录 前言 SELECT 语句执行过程 什么是索引下推? 动手实验: 索引下推限制 拓展:虚拟列 总结 前言 本文围绕这三个话题来学习索引下推: SELECT 语句执行过程 什么是索引下推? 索引下推限制 SELECT 语句执行过程 MySQL 数据库由 Server 层和 Engine 层组成: Server 层: 有 SQL 分析器.SQL 优化器.SQL 执行器,用于负责 SQL 语句的具体执行过程. Engine 层: 负责存储具体的数据,如最常使用的 InnoDB 存储引擎,还有用于在

  • MySQL 回表,覆盖索引,索引下推

    MySQL 回表,覆盖索引,索引下推

    目录 回表 覆盖索引 索引下推 无索引下推: 查看索引下推的状态 有索引下推: 开启索引下推 回表 在研究mysql二级索引的时候,发现Mysql回表这个操作,往下研究了一下 字面意思,找到索引,回到表中找数据 解释一下就是: 先通过索引扫描出数据所在的行,再通过行主键ID 取出数据. 举个例子说明: SELECT * FROM INNODB_USER WHERE AGE = 18 AND USER_NAME LIKE '模糊查%'; 假如age和user_name两个字段是个联合索引,我们通过

  • 五分钟让你快速弄懂MySQL索引下推

    五分钟让你快速弄懂MySQL索引下推

    大家好,我是老三,今天分享一个小知识点--索引下推. 如果你在面试中,听到MySQL5.6"."索引优化" 之类的词语,你就要立马get到,这个问的是"索引下推". 什么是索引下推 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本的新特性,它能减少回表查询次数,提高查询效率. 索引下推优化的原理 我们先简单了解一下MySQL大概的架构: MySQL服务层负责SQL语法解析.生成执行计划等,并调用存储引擎层去执

  • MySQL带你秒懂索引下推

    MySQL带你秒懂索引下推

    目录 一.索引下推优化的原理 二.索引下推的具体实践 1.没有使用ICP 2.使用ICP 三.索引下推使用条件 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本的新特性,它能减少回表查询次数,提高查询效率. 一.索引下推优化的原理 我们先简单了解一下MySQL大概的架构: MySQL服务层负责SQL语法解析.生成执行计划等,并调用存储引擎层去执行数据的存储和检索. 索引下推的下推其实就是指将部分上层(服务层)负责的事情,交给了下层(引擎层)去处理.

  • MySQL索引下推（ICP）的简单理解与示例

    MySQL索引下推（ICP）的简单理解与示例

    前言 索引下推(Index Condition Pushdown, 简称ICP)是MySQL 5.6 版本的新特性,它能减少回表查询次数,提升检索效率. MySQL体系结构 要明白索引下推,首先要了解MySQL的体系结构: 上图来自MySQL官方文档. 通常把MySQL从上至下分为以下几层: MySQL服务层:包括NoSQL和SQL接口.查询解析器.优化器.缓存和Buffer等组件. 存储引擎层:各种插件式的表格存储引擎,实现事务.索引等各种存储引擎相关的特性. 文件系统层: 读写物理文件. M

  • Mysql性能优化之索引下推

    Mysql性能优化之索引下推

    索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询. 在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件 . 在使用ICP的情况下,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出

  • MySQL索引下推详细

    MySQL索引下推详细

    目录 1.最左前缀原则 2.回表 3.索引下推 前言: 索引下推(ICP)是针对MySQL使用索引从表中检索数据行的情况的优 在没有索引下推的情况下,MySQL通过存储引擎遍历索引来定位表中的数据行并将它们返回给MySQl服务器,服务器再进行WHERE条件的判断,确认是否将数据行加入结果集. 开启索引下推,且WHERE条件部分可以仅使用索引中的列来评估,这时MySQL服务器会将这部分WHERE条件下推到存储引擎,接着存储引擎使用索引条目评估推送的索引条件,仅当满足该条件时才从表中进行读取 索引下

  • 五分钟带你搞懂MySQL索引下推

    五分钟带你搞懂MySQL索引下推

    目录 什么是索引下推 索引下推优化的原理 索引下推的具体实践 没有使用ICP 使用ICP 索引下推使用条件 相关系统参数 总结 如果你在面试中,听到MySQL5.6"."索引优化" 之类的词语,你就要立马get到,这个问的是"索引下推". 什么是索引下推 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本的新特性,它能减少回表查询次数,提高查询效率. 索引下推优化的原理 我们先简单了解一下MySQL大概的架构:

  • MySQL查询性能优化索引下推

    MySQL查询性能优化索引下推

    目录 前言 1. 索引下推的作用 2. 案例实践 3. 索引下推配置 4. 索引下推原理剖析 5. 索引下推应用范围 前言 前面已经讲了MySQL的其他查询性能优化方式,没看过可以去了解一下: MySQL查询性能优化七种方式索引潜水 MySQL查询性能优化武器之链路追踪 今天要讲的是MySQL的另一种查询性能优化方式 — 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本增加的特性. 1. 索引下推的作用 主要作用有两个: 减少回表查询的次数 减少存

  • 一篇文章带你掌握MySQL索引下推

    目录 1.什么是索引下推 2.案例 2.1.MySQL5.5版本 2.2.MySQL5.7版本 3.小结 1.什么是索引下推 索引下推(Index Condition PushDown,简称ICP)是从MySQL5.6开始引入的一个特性,索引下推通过减少回表的次数来提高数据库的查询效率; 2.案例 准备: ①.为了演示索引下推,需要安装MySQL5.5和MySQL5.7两个版本的MySQL,因为索引下推是MySQL5.6版本中开始引入的新特性,所以这两个版本就可以演示出索引下推的特点; ②.数据

  • 一篇文章带你了解MySQL数据库基础

    一篇文章带你了解MySQL数据库基础

    目录 1. 数据库概念 1.1 数据库是干嘛的? 1.2 数据库和数据结构是啥关系? 1. 数据库是一个软件/程序 2. 数据结构是一个学科~ 1.3 两种类型的数据库 2. MySQL数据库 2.1 MySQL数据库概念 2.2 MySQL基本操作 2.2.1 建立数据库 2.2.2 查看数据库 2.2.3 选中数据库 2.2.4 删除数据库 2.3 MySQL数据类型 总结 1. 数据库概念 1.1 数据库是干嘛的? 数据库的功能就是用来组织数据,组织很多很多的数据.这些数据通常都是存储在外

  • 一篇文章读懂什么是MySQL索引下推（ICP）

    一篇文章读懂什么是MySQL索引下推（ICP）

    目录 一.简介 二.原理 三.实践 3.1 不使用索引下推 3.2 使用索引下推 四.使用条件 五.相关系统参数 总结 一.简介 ICP(Index Condition Pushdown)是在MySQL 5.6版本上推出的查询优化策略,把本来由Server层做的索引条件检查下推给存储引擎层来做,以降低回表和访问存储引擎的次数,提高查询效率. 二.原理 为了理解ICP是如何工作的,我们先了解下没有使用ICP的情况下,MySQL是如何查询的: 存储引擎读取索引记录: 根据索引中的主键值,定位并读取完

  • 一篇文章带你了解清楚Mysql 锁

    一篇文章带你了解清楚Mysql 锁

    一丶为什么数据库需要锁 数据库锁设计的初衷是处理并发问题.作为多用户共享 的资源,当出现并发访问的时候,数据库需要合理地控制资源的访问规则.而锁就是用来实 现这些访问规则的重要数据结构. 根据加锁的范围,MySQL 里面的锁大致可以分成全局锁.表级锁和行锁三类 二丶全局锁&全库逻辑备份 全局锁就是对整个数据库实例加锁.全局锁的典型使用场景是,做全库逻辑备份,全库逻辑备份有以下几种方式: 1.Flush tables with read lock (FTWRL) Flush tables with

  • 一篇文章带你弄清楚Redis的精髓

    目录 一.Redis的特性 1.1 Redis为什么快? 1.2 Redis其他特性 1.3 Redis高可用 二.Redis数据类型以及使用场景 2.1 String 2.1.1 基本指令 2.1.2 应用场景 2.2 Hash 2.2.1 基本指令 2.2.2 应用场景 2.3 List 2.3.1 基本指令 2.3.2 应用场景 2.4 Set 2.4.1 基本指令 2.4.2 应用场景 2.5 ZSet(SortedSet) 2.5.1 基本指令 2.5.2 应用场景 三.Redis的事

  • 一篇文章带你搞定 springsecurity基于数据库的认证(springsecurity整合mybatis)

    一.前期配置 1. 加入依赖 <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.10</version> </dependency> <dependency> <groupId>mysql</groupId> &

  • 一篇文章带你彻底搞懂Redis 事务

    目录 Redis 事务简介 Redis 事务基本指令 实例分析 Redis 事务与 ACID 总结 Redis 事务简介 Redis 只是提供了简单的事务功能.其本质是一组命令的集合,事务支持一次执行多个命令,在事务执行过程中,会顺序执行队列中的命令,其他客户端提交的命令请求不会插入到本事务执行命令序列中.命令的执行过程是顺序执行的,但不能保证原子性.无法像 MySQL 那样,有隔离级别,出了问题之后还能回滚数据等高级操作.后面会详细分析. Redis 事务基本指令 Redis 提供了如下几个事

  • 一篇文章带你使用Typescript封装一个Vue组件(简单易懂)

    一.搭建项目以及初始化配置 vue create ts_vue_btn 这里使用了vue CLI3自定义选择的服务,我选择了ts.stylus等工具.然后创建完项目之后,进入项目.使用快捷命令code .进入Vs code编辑器(如果没有code .,需要将编辑器的bin文件目录地址放到环境变量的path中).然后,我进入编辑器之后,进入设置工作区,随便设置一个参数,这里比如推荐设置字号,点下.这里是为了生成.vscode文件夹,里面有个json文件. 我们在开发项目的时候,项目文件夹内的文件很

随机推荐