MySQL带你秒懂索引下推

目录
  • 一、索引下推优化的原理
  • 二、索引下推的具体实践
    • 1、没有使用ICP
    • 2、使用ICP
  • 三、索引下推使用条件

索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本的新特性,它能减少回表查询次数,提高查询效率。

一、索引下推优化的原理

我们先简单了解一下MySQL大概的架构:

MySQL服务层负责SQL语法解析、生成执行计划等,并调用存储引擎层去执行数据的存储和检索。

索引下推的下推其实就是指将部分上层(服务层)负责的事情,交给了下层(引擎层)去处理。

我们来具体看一下,在没有使用ICP的情况下,MySQL的查询:

  • 存储引擎读取索引记录;
  • 根据索引中的主键值,定位并读取完整的行记录;
  • 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP的情况下,查询过程:

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

二、索引下推的具体实践

理论比较抽象,我们来上一个实践。

使用一张用户表tuser,表里创建联合索引(name, age)。

如果现在有一个需求:检索出表中名字第一个字是张,而且年龄是10岁的所有用户。那么,SQL语句是这么写的:

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

假如你了解索引最左匹配原则,那么就知道这个语句在搜索索引树的时候,只能用 ,找到的第一个满足条件的记录id为1。

那接下来的步骤是什么呢?

1、没有使用ICP

MySQL 5.6之前,存储引擎根据通过联合索引找到name like '张%' 的主键id(1、4),逐一进行回表扫描,去聚簇索引找到完整的行记录,server层再对数据根据age=10进行筛选。

我们看一下示意图:

可以看到需要回表两次,把我们联合索引的另一个字段age浪费了。

2、使用ICP

MySQL 5.6 以后, 存储引擎根据(nameage)联合索引,找到name like '张%',由于联合索引中包含age列,所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。

我们看一下示意图:

可以看到只回表了一次。

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

+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tuser | NULL       | range | na_index      | na_index | 102     | NULL |    2 |    25.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

三、索引下推使用条件

  • 只能用于rangerefeq_refref_or_null访问方法;
  • 只能用于InnoDBMyISAM存储引擎及其分区表;
  • InnoDB存储引擎来说,索引下推只适用于二级索引(也叫辅助索引);

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

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

相关系统参数:

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

查看默认状态:

mysql> select @@optimizer_switch\G;
*************************** 1. row ***************************
@@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
1 row in set (0.00 sec)

切换状态:

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

到此这篇关于几分钟搞懂MySQL索引下推的文章就介绍到这了,更多相关MySQL索引下推内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

时间: 2021-09-14

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

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

一篇文章读懂什么是MySQL索引下推(ICP)

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

MySQL索引下推(ICP)的简单理解与示例

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

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

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

带你5分钟读懂MySQL字符集设置

一.内容概述 在MySQL的使用过程中,了解字符集.字符序的概念,以及不同设置对数据存储.比较的影响非常重要.不少同学在日常工作中遇到的"乱码"问题,很有可能就是因为对字符集与字符序的理解不到位.设置错误造成的. 本文由浅入深,分别介绍了如下内容: 字符集.字符序的基本概念及联系 MySQL支持的字符集.字符序设置级,各设置级别之间的联系 server.database.table.column级字符集.字符序的查看及设置 应该何时设置字符集.字符序 二.字符集.字符序的概念与联系 在

五分钟带你搞懂python 迭代器与生成器

前言 大家周末好,今天给大家带来的是Python当中生成器和迭代器的使用. 我当初第一次学到迭代器和生成器的时候,并没有太在意,只是觉得这是一种新的获取数据的方法.对于获取数据的方法而言,我们会一种就足够了.但是在我后来Python的使用以及TensorFlow等学习使用当中,我发现很多地方都用到了迭代器和生成器,或者是直接使用,或者是借鉴了思路.今天就让我们仔细来看看,它们到底是怎么回事. 迭代器 我们先从迭代器开始入手,迭代器并不是Python独有的概念,在C++和Java当中都有itera

四步五分钟Spring4快速集成Swagger

如果你所在的公司的还没有使用swagger甚至没有听说过swagger,赶快学习一下我的这篇博客吧,五分钟速成,傻瓜式的集成,但就是这么简单的应用一定会让他们震惊到的. 首先对swagger做一个简介吧:swagger是后台开发的神器,也是前后端交流的渠道.你可以用swagger做什么?首先,你以后基本可以告别单元测试了:其次,你不用再写接口文档了,也不需要写完之后再去对文档进行维护了.swagger可以完全模拟http请求,入参出参和实际情况差别几乎为零.说了这些,直接来干货吧! 集成四部曲:

快速学习MySQL索引的入门超级教程

所谓索引就是为特定的mysql字段进行一些特定的算法排序,比如二叉树的算法和哈希算法,哈希算法是通过建立特征值,然后根据特征值来快速查找.而用的最多,并且是mysql默认的就是二叉树算法 BTREE,通过BTREE算法建立索引的字段,比如扫描20行就能得到未使用BTREE前扫描了2^20行的结果,具体的实现方式后续本博客会出一个算法专题里面会有具体的分析讨论; Explain优化查询检测 EXPLAIN可以帮助开发人员分析SQL问题,explain显示了mysql如何使用索引来处理select语

从一个不错的留言本弄的mysql数据库操作类

从一个不错的留言本弄的mysql数据库操作类,初学php的朋友可以参考下 复制代码 代码如下: <?php class mysql{     var $querynum = 0;     function connect($dbhost, $dbuser, $dbpw, $dbname = '',$dbcharset='') {         if(!@mysql_connect($dbhost, $dbuser, $dbpw)) {             $this->show('Can

彻底弄懂CSS盒子模式系列教程集合

blueidea终于可以出点让人惊呀的东西了,一直对clear和position不太理解,没想到这次的系列教程全给提及到了,不错,赞一下. 彻底弄懂CSS盒子模式之一彻底弄懂CSS盒子模式之二彻底弄懂CSS盒子模式之三彻底弄懂CSS盒子模式之四彻底弄懂CSS盒子模式之五

睡五分钟等于六钟头的方法(熬夜站长必看)

睡觉的诀窍 根据医学和我的体验.观察,一个人真正睡着觉最多只有两个钟头,其余都是浪费时间,躺在枕头上做梦,没有哪个人不做梦.至于醒来觉得自己没有做梦,那是因为他忘记了. 通常一个人睡两个钟头就够了,为什么有人要睡七.八个钟头?那是你赖床躺在枕头上休息的习惯养成的,并非我们需要那么久的睡眠时间,尤其打坐做功夫的人晓得,正午只要闭眼真正睡着三分钟,等于睡两个钟头,不过要对好正午的时间.夜晚则要在正子时睡着,五分钟等于六个钟头. 就这个时间的学问又大了,同宇宙法则.地球法则.易经阴阳的道理有关系,而且

彻底弄懂 JavaScript 执行机制

不论你是javascript新手还是老鸟,不论是面试求职,还是日常开发工作,我们经常会遇到这样的情况:给定的几行代码,我们需要知道其输出内容和顺序.因为javascript是一门单线程语言,所以我们可以得出结论: javascript是按照语句出现的顺序执行的 看到这里读者要打人了:我难道不知道js是一行一行执行的?还用你说?稍安勿躁,正因为js是一行一行执行的,所以我们以为js都是这样的: let a = '1'; console.log(a); let b = '2'; console.lo

一文搞懂MySQL预编译

1.预编译的好处 大家平时都使用过JDBC中的PreparedStatement接口,它有预编译功能.什么是预编译功能呢?它有什么好处呢? 当客户发送一条SQL语句给服务器后,服务器总是需要校验SQL语句的语法格式是否正确,然后把SQL语句编译成可执行的函数,最后才是执行SQL语句.其中校验语法,和编译所花的时间可能比执行SQL语句花的时间还要多. 如果我们需要执行多次insert语句,但只是每次插入的值不同,MySQL服务器也是需要每次都去校验SQL语句的语法格式,以及编译,这就浪费了太多的时

一文弄懂Pytorch的DataLoader, DataSet, Sampler之间的关系

以下内容都是针对Pytorch 1.0-1.1介绍. 很多文章都是从Dataset等对象自下往上进行介绍,但是对于初学者而言,其实这并不好理解,因为有的时候会不自觉地陷入到一些细枝末节中去,而不能把握重点,所以本文将会自上而下地对Pytorch数据读取方法进行介绍. 自上而下理解三者关系 首先我们看一下DataLoader.next的源代码长什么样,为方便理解我只选取了num_works为0的情况(num_works简单理解就是能够并行化地读取数据). class DataLoader(obje