精华内容
下载资源
问答
  • 索引下推

    2021-02-25 16:03:03
    当使用索引条件下推优化时,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索...

    概述

    索引条件下推优化(Index Condition Pushdown (ICP) )是MySQL5.6添加的,用于优化数据查询。 

    • 不使用索引条件下推优化时存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件。 
    • 当使用索引条件下推优化时,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

     

    索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。 

    网上搜了下相关的文章不少都将Index Condition Pushdown 称为索引下推优化,我认为还是索引条件下推优化更合适一些,因为这个优化技术关键的操作就是将与索引相关的条件由MySQL服务器向下传递至存储引擎,由此减少IO次数。MySQL服务器到存储引擎是向下,传递的是与索引列相关的查询条件,所以还是索引条件下推优化更容易理解一些。

    适用条件

    1. 需要整表扫描的情况。比如:range, ref, eq_ref, ref_or_null 。适用于InnoDB 引擎和 MyISAM 引擎的查询。(5.6版本不适用分区表查询,5.7版本后可以用于分区表查询)。
    2.  对于InnDB引擎只适用于二级索引,因为InnDB的聚簇索引会将整行数据读到InnDB的缓冲区,这样一来索引条件下推的主要目的减少IO次数就失去了意义。因为数据已经在内存中了,不再需要去读取了。 
    3. 引用子查询的条件不能下推。 
    4. 调用存储过程的条件不能下推,存储引擎无法调用位于MySQL服务器中的存储过程。
    5.  触发条件不能下推。

    工作过程

    既然是优化,我们要清楚优化了些什么就要了解原本是如何工作的,所以分为两部分来描述工作过程。

    不使用索引条件下推优化时的查询过程

    获取下一行,首先读取索引信息,然后根据索引将整行数据读取出来。然后通过where条件判断当前数据是否符合条件,符合返回数据。

    使用索引条件下推优化时的查询过程

    获取下一行的索引信息。检查索引中存储的列信息是否符合索引条件,如果符合将整行数据读取出来,如果不符合跳过读取下一行。用剩余的判断条件,判断此行数据是否符合要求,符合要求返回数据。

    EXPLAN分析

    当使用explan进行分析时,如果使用了索引条件下推,Extra会显示Using index condition。并不是Using index因为并不能确定利用索引条件下推查询出的数据就是符合要求的数据,还需要通过其他的查询条件来判断。

    图一:不使用ICP技术(过程使用数字符号标示,如①②③等)

     过程解释:

    ①:MySQL Server发出读取数据的命令,这是在执行器中执行如下代码段,通过函数指针和handle接口调用存储引擎的索引读或全表表读。此处进行的是索引读。 

     

     

     

     

    if (in_first_read){
        in_first_read= false;
        //设定合适的读取函数,如设定索引读函数/全表扫描函数
        error= (*qep_tab->read_first_record)(qep_tab); 
    }else
    error= info->read_record(info);复制代码

    ②、③:进入存储引擎,读取索引树,在索引树上查找,把满足条件的(经过查找,红色的满足)从表记录中读出(步骤④,通常有IO),从存储引擎返回⑤标识的结果。

    此处,不仅要在索引行进行索引读取(通常是内存中,速度快。步骤③),还要进行进行步骤④,通常有IO。

    ⑥:从存储引擎返回查找到的多条元组给MySQL ServerMySQL Server在⑦得到较多的元组。

    ⑦--⑧:⑦到⑧依据WHERE子句条件进行过滤,得到满足条件的元组。注意在MySQL Server层得到较多元组,然后才过滤,最终得到的是少量的、符合条件的元组。

    图二:使用ICP技术(过程使用数字符号标示,如①②③等)

     

    过程解释:

    ①:MySQL Server发出读取数据的命令,过程同图一。

    ②、③:进入存储引擎,读取索引树,在索引树上查找,把满足已经下推的条件的(经过查找,红色的满足)从表记录中读出(步骤④,通常有IO),从存储引擎返回⑤标识的结果。

    此处,不仅要在索引行进行索引读取(通常是内存中,速度快。步骤③),还要在③这个阶段依据下推的条件进行进行判断,不满足条件的,不去读取表中的数据,直接在索引树上进行下一个索引项的判断,直到有满足条件的,才进行步骤④,这样,较没有ICP的方式,IO量减少。

    ⑥:从存储引擎返回查找到的少量元组给MySQL ServerMySQL Server在⑦得到少量的元组。因此比较图一无ICP的方式,返回给MySQL Server层的即是少量的、符合条件的元组。另外,图中的部件层次关系,不再进行解释。

    示例

    假设有一张people表,包含字段name、address、first_name

    索引为(name,address,first_name)

    然后我们执行下面的查询 

     

     

     

    SELECT * FROM person WHERE `name` = "1" AND `address` LIKE "%222" and 复制代码

     

    如果不使用索引条件下推优化的话,MySQL只能根据索引查询出name=1的所有行,然后再依次比较是否符合全部条件。

    当使用了索引条件下推优化技术后,可以通过索引中存储的数据判断当前索引对应的数据是否符合条件,只有符合条件的数据才将整行数据查询出来。查看执行计划时发现extra一栏中有Using index condition信息,说明使用了索引下推。

    配置 

    索引下推优化是默认开启的。可以通过下面的脚本控制开关

    SET optimizer_switch = 'index_condition_pushdown=off'; 
    SET optimizer_switch = 'index_condition_pushdown=on';复制代码

    思考

    索引下推优化技术其实就是充分利用了索引中的数据,尽量在查询出整行数据之前过滤掉无效的数据。

    由于需要存储引擎将索引中的数据与条件进行判断,所以这个技术是基于存储引擎的,只有特定引擎可以使用。并且判断条件需要是在存储引擎这个层面可以进行的操作才可以,比如调用存储过程的条件就不可以,因为存储引擎没有调用存储过程的能力。


    作者:古柏树下
    链接:https://juejin.cn/post/6844904017332535304
    来源:掘金
    著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

    展开全文
  • 上一节我们讲解了聚集索引和非聚集索引的区别(索引知识系列一:聚集索引与非索引详解 ),我们知道非聚集索引在查询过程中有回表的过程,这就造成了效率的下降。那如何不用回表或者减少回表以提高查询速度呢?这...

    一、前言

    上一节我们讲解了聚集索引和非聚集索引的区别(索引知识系列一:聚集索引与非索引详解 ),我们知道非聚集索引在查询过程中有回表的过程,这就造成了效率的下降。那如何不用回表或者减少回表以提高查询速度呢?这就是本章要讲的内容。

    二、联合索引

    联合索引(也叫组合索引、复合索引、多列索引)是指对表上的多个列进行索引。联合索引的创建方法跟单个索引的创建方法一样,不同之处仅在于有多个索引列。

    开讲之前我们先弄一张学生表,表数据如下:
    在这里插入图片描述
    下面我们给出一个需求:查询表中以字母"L"开头的姓名及年龄。

    1、常规的写法(回表查询)

    SELECT name,age FROM `t_user` where name like 'l%' ;
    

    这种写法,明显效率是低下的,我们用explain 分析一下:
    在这里插入图片描述
    由图中可以看出,在数据库中进行了全表扫描。下面我们看一下数据库中的执行过程。

    第一步:全表扫描数据,找出以“l”开头的主键id.
    第二步:将所有查询出来的数据每一个都回表,根据id来查询出想要的数据。
    

    2.优化写法(索引覆盖)
    因为我们要查询name和age。所以,我们对name和age建立了联合索引,建立后的索引图如下:
    在这里插入图片描述
    从图中我们可以看出,叶子节点中的键值都是按顺序存储的并且都包含了名字和年龄,即(“Ann”,36)、(“HanMeimei”,17)、(“Kate”,17)、(“LiLei”,18)、(“Lili”,16)、(“Lisa”,19)、(“Lucy”,17)、(“WeiHua”,32)、(“ZhangWei”,18)、(“ZhangWei”,25)。

    索引会先根据 name 排序,如果 name 相同,再根据 age 进行排序。

    我们对name和age建立索引后,当我们查询name和age二个字段时,直接会从索引中查出来而不需要回表查询,这种方式就是索引覆盖。执行步骤是这样的:

    第一步:使用联合索引(name,age)查询以“l”开头的数据
    第二步:在索引中取出name和age.
    

    这种方式是不是高效多了,你要是还不信,我们用explain看一下,如下图:

    EXPLAIN SELECT name,age FROM `t_user` where name like 'l%' ;
    

    在这里插入图片描述
    从图中我们看的出,使用了(name,age)索引。

    2.1 联合索引最左匹配原则

    联合索引在使用的时候一定要注意顺序,一定要注意符合最左匹配原则。

    最左匹配原则:在通过联合索引检索数据时,从索引中最左边的列开始,一直向右匹配,如果遇到范围查询(>、<、between、like等),就停止后边的匹配。

    这个定义不太好理解,我解释一下:

    假如对字段 (a, b, c) 建立联合索引,现在有这样一条查询语句:

    where a > xxx and b=yyy and c=zzz
    where a like 'xxx%' and b=yyy and c=zzz
    

    在这个条件语句中,只有a用到了索引,后面的b,c就不会用到索引。这就是“如果遇到范围查询(>、<、between、like等),就停止后边的匹配。”的意思。

    我们还是假如对字段 (a, b, c) 建立联合索引,

    1.如下查询语句可以使用到索引:

    where a = xxx
    where a = xxx and b = xxx
    where a = xxx and b = xxx and c = xxx
    where a like 'xxx%'
    where a > xxx
    where a = xxx order by b
    where a = xxx and b = xxx order by c group by a
    

    2.如下查询条件也会使用索引:

    where b = xxx and a = xxx
    where a = xxx and c = xxx and b = xxx
    

    虽然b和a的顺序换了,但是mysql中的优化器会帮助我们调整顺序。

    3.如下查询条件只用到联合索引的一部分:

    where a = xxx and c = xxx   可以用到 a 列的索引,用不到 c 列索引。
    where a like 'xxx%' and b = xxx 可以用到 a 列的索引,用不到 b 列的索引。
    where a > xxx and b = xxx 可以用到 a 列的索引,用不到 b 列的索引。
    

    4.如下查询条件完全用不到索引

    where b = xxx
    where c = xxx
    where a like '%xxx'			-- 不满足最左前缀
    where d = xxx order by a	-- 出现非排序使用到的索引列 d 
    where a + 1 = xxx	-- 使用函数、运算表达式及类型隐式转换等
    

    如何选择合适的联合索引

    1.where a = xxx and b = xxx and c = xxx 如果我们的查询是这样的,建索引时,就可以考虑将选择性高的列放在索引的最前列,选择性低的放后边。

    2.如果是 where a > xxx and b = xxx 或 where a like ‘xxx%’ and b = xxx 这样的语句,可以对 (b, a) 建立索引。

    3.如果是 where a = xxx order by b 这样的语句,可以对 (a, b) 建立索引。

    三、索引覆盖

    索引覆盖在上面我们已经介绍了。由上面的介绍我们知道,建立了联合索引后,直接在索引中就可以得到查询结果,从而不需要回表查询聚簇索引中的行数据信息。

    索引覆盖可以带来很多的好处:

    • 辅助索引不包含行数据的所有信息,故其大小远小于聚簇索引,因此可以减少大量的IO操作。
    • 索引覆盖只需要扫描一次索引树,不需要回表扫描聚簇索引树,所以性能比回表查询要高。
    • 索引中列值是按顺序存储的,索引覆盖能避免范围查询回表带来的大量随机IO操作。 判断一条语句是否用到索引覆盖:

    这个我们需要用explain查看一下。
    在这里插入图片描述
    Using index 就表示使用到了索引 , 并且所取的数据完全在索引中就能拿到,也就是用到了索引覆盖。

    四、索引下推

    索引下推是索引下推是 MySQL 5.6 及以上版本上推出的,用于对查询进行优化。

    索引下推是把本应该在 server 层进行筛选的条件,下推到存储引擎层来进行筛选判断,这样能有效减少回表。

    举例说明:

    首先使用联合索引(name,age),现在有这样一个查询语句:

    select *  from t_user where name like 'L%' and age = 17;
    

    这条语句从最左匹配原则上来说是不符合的,原因在于只有name用的索引,但是age并没有用到。

    不用索引下推的执行过程:

    第一步:利用索引找出name带'L'的数据行:LiLei、Lili、Lisa、Lucy 这四条索引数据
    第二步:再根据这四条索引数据中的 id 值,逐一进行回表扫描,从聚簇索引中找到相应的行数据,将找到的行数据返回给 server 层。
    第三步:在server层判断age = 17,进行筛选,最终只留下 Lucy 用户的数据信息。
    

    使用索引下推的执行过程:

    第一步:利用索引找出name带'L'的数据行:LiLei、Lili、Lisa、Lucy 这四条索引数据
    第二步:根据 age = 17 这个条件,对四条索引数据进行判断筛选,最终只留下 Lucy 用户的数据信息。
    (注意:这一步不是直接进行回表操作,而是根据 age = 17 这个条件,对四条索引数据进行判断筛选)
    第三步:将符合条件的索引对应的 id 进行回表扫描,最终将找到的行数据返回给 server 层。
    

    比较二者的第二步我们发现,索引下推的方式极大的减少了回表次数。

    索引下推需要注意的情况:

    下推的前提是索引中有 age 列信息,如果是其它条件,如 gender = 0,这个即使下推下来也没用

    开启索引下推:

    索引下推是 MySQL 5.6 及以上版本上推出的,用于对查询进行优化。默认情况下,索引下推处于启用状态。我们可以使用如下命令来开启或关闭。

    set optimizer_switch='index_condition_pushdown=off'; 	-- 关闭索引下推
    set optimizer_switch='index_condition_pushdown=on';		-- 开启索引下
    

    五、结尾

    好了,本章就讲到这里吧,下一章,我们对所有的知识进行一下总结。

    另外大家帮忙关注我,每天更新优质内容。关注我有大量学习资料和学习视频赠送。
    在这里插入图片描述
    扫二维码关注公众号【Java程序员的奋斗路】可领取如下学习资料:
    1.1T视频教程(大约有100多个视频):涵盖Javaweb前后端教学视频、机器学习/人工智能教学视频、Linux系统教程视频、雅思考试视频教程,android.等
    2.项目源码:20个JavaWeb项目源码

    展开全文
  • 联合索引的树结构、最左匹配原则、如何选择合适的索引列顺序、索引下推图文讲解

    联合索引

    联合索引(也叫组合索引、复合索引、多列索引)是指对表上的多个列进行索引。联合索引的创建方法跟单个索引的创建方法一样,不同之处仅在于有多个索引列。

    例如,创建如下表,idx_name 是联合索引,索引列为 (name,age)

    CREATE TABLE `t_user` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
      `name` varchar(32) NOT NULL COMMENT '姓名',
      `age` tinyint(3) unsigned NOT NULL COMMENT '年龄',
      `gender` tinyint(3) unsigned NOT NULL COMMENT '性别:1男,0女',
      PRIMARY KEY (`id`),
      KEY `idx_name` (`name`,`age`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    假如表中有如下数据

    idnameagegender
    1LiLei181
    2HanMeimei170
    3Lucy170
    4Lili160
    5WeiHua320
    6ZhangWei251
    7Ann360
    8Lisa190
    9ZhangWei181
    10Kate171

    我们来看一下这棵索引树的结构
    在这里插入图片描述

    从图中我们可以看出,叶子节点中的键值都是按顺序存储的,即(“Ann”,36)、(“HanMeimei”,17)、(“Kate”,17)、(“LiLei”,18)、(“Lili”,16)、(“Lisa”,19)、(“Lucy”,17)、(“WeiHua”,32)、(“ZhangWei”,18)、(“ZhangWei”,25)。

    索引会先根据 name 排序,如果 name 相同,再根据 age 进行排序。

    联合索引的最左匹配原则

    索引的目的其实就是为了提高数据查询的效率,联合索引也一样,使用联合索引时,一定要注意符合最左匹配原则:在通过联合索引检索数据时,从索引中最左边的列开始,一直向右匹配,如果遇到范围查询(>、<、between、like等),就停止后边的匹配。

    假如对字段 (a, b, c) 建立联合索引,如下查询语句可以使用到索引:

    where a = xxx
    where a = xxx and b = xxx
    where a = xxx and b = xxx and c = xxx
    where a like 'xxx%'
    where a > xxx
    where a = xxx order by b
    where a = xxx and b = xxx order by c
    group by a
    

    当然,像如下:

    where b = xxx and a = xxx
    where a = xxx and c = xxx and b = xxx
    

    这种查询条件书写顺序不影响对联合索引的使用,因为执行 sql 的时候,MySQL优化器会帮我们调整 where 后 a,b,c 的顺序,让我们用上索引。

    而还有一些语句是只能用到联合索引的一部分的。

    where a = xxx and c = xxx 可以用到 a 列的索引,用不到 c 列索引。

    where a like 'xxx%' and b = xxx 可以用到 a 列的索引,用不到 b 列的索引。

    where a > xxx and b = xxx 可以用到 a 列的索引,用不到 b 列的索引。

    最需要注意类似下边的这些查询

    where b = xxx
    where c = xxx
    where a like '%xxx'			-- 不满足最左前缀
    where d = xxx order by a	-- 出现非排序使用到的索引列 d 
    where a + 1 = xxx	-- 使用函数、运算表达式及类型隐式转换等
    

    这些查询语句,完全用不到 (a, b, c) 这个联合索引。

    如何选择合适的索引列顺序

    建立联合索引时,一般我们遵循的经验是:将选择性最高的列放在索引的最前列。这在某些场景下比较有用,但通常不如避免随机IO和排序那么重要。正确的顺序应该依赖于使用该索引的查询,并且同时需要考虑如何更好的满足排序和分组的需要。

    where a = xxx and b = xxx and c = xxx 如果我们的查询是这样的,建索引时,就可以考虑将选择性高的列放在索引的最前列,选择性低的放后边。

    如果是 where a > xxx and b = xxxwhere a like 'xxx%' and b = xxx 这样的语句,可以对 (b, a) 建立索引。

    如果是 where a = xxx order by b 这样的语句,可以对 (a, b) 建立索引。

    索引下推(索引条件下推)

    什么是索引下推(Index Condition Pushdown,ICP)呢?我们通过例子来了解下。

    假设我们想从一开始创建的表中,查询 name 以 ‘L’ 开头,并且 age 为 17 的人员信息。

    select * from t_user where name like 'L%' and age = 17;
    

    在不用索引下推的情况下,根据前边"最左匹配原则"描述的那样,该查询在联合索引中只有 name 列可以使用到索引,age 列是用不到索引的。在扫描 (‘name’, age) 索引树时,根据 name like 'L%' 这个条件,可以查找到 LiLeiLiliLisaLucy 四条索引数据,接下来,再根据这四条索引数据中的 id 值,逐一进行回表扫描,从聚簇索引中找到相应的行数据,将找到的行数据返回给 server 层。server 层中,再根据 age = 17 这个条件进行筛选,最终只留下 Lucy 用户的数据信息。

    不用索引下推的过程,如下图示:
    在这里插入图片描述

    在使用索引下推的情况下,存储引擎层还是先根据 name like 'L%' 这个条件,查找到 LiLeiLiliLisaLucy 四条索引数据,不过接下来不是直接进行回表操作,而是根据 age = 17 这个条件,对四条索引数据进行判断筛选,将符合条件的索引对应的 id 进行回表扫描,最终将找到的行数据返回给 server 层。(也就是我们把本应该在 server 层进行筛选的条件,下推到存储引擎层来进行筛选判断了。这个下推的前提是索引中有 age 列信息,如果是其它条件,如 gender = 0,这个即使下推下来也没用)

    使用索引下推的过程,如下图示:
    在这里插入图片描述

    由上比较可以看出,使用索引下推优化,可以有效减少回表次数,也可以减少 server 层从存储引擎层接收数据的次数,从而大大提升查询效率。

    索引下推是 MySQL 5.6 及以上版本上推出的,用于对查询进行优化。默认情况下,索引下推处于启用状态。我们可以使用如下命令来开启或关闭。

    set optimizer_switch='index_condition_pushdown=off'; 	-- 关闭索引下推
    set optimizer_switch='index_condition_pushdown=on';		-- 开启索引下推
    

    话说,正常情况下,我们有什么理由来关闭这么好用的功能呢?

    展开全文
  • innodb的索引下推

    万次阅读 2020-08-25 16:32:18
    索引下推,是mysql优化多列索引查询的一种方案,叫做索引下推不如翻译为索引条件下推更合适(Index Condition Pushdown)简称ICP,因为他实际上是把where中的查询索引条件,下推给了存储引擎 本文涉及到的内容有: ...

    索引下推,是mysql优化联合索引查询的一种方案,叫做索引下推不如翻译为索引条件下推更合适(Index Condition Pushdown)简称ICP,因为他实际上是把where中的查询索引条件,下推给了存储引擎

    本文涉及到的内容有:

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

    预备知识:

    • b+tree
    • 主键索引和非主键索引
    • 聚簇索引和非聚簇索引

    主键索引和非主键索引

    说到回表,我们先回顾一下innodb主键索引和非主键索引(辅助索引)的区别(具体的之后写一篇文章来讲解,这里只是简单带过)。

    他们数据存储的方式是一样的,但是由于innodb使用的辅助索引的叶子节点上只有主键(不是数据指针哦),如果需要更多列数据,查找就需要分成了两步。

    1. 第一步在辅助索引B+树中检索,到达其叶子节点获取对应的主键。
    2. 第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。

    当我们使用辅助索引检索数据的时候,会有两种情况

    • 当想要查询的字段(列)并不都包含在索引中的时候,一定会涉及到回表取数据,即拿到辅助索引叶子节点上的主键再去主键索引上检索取到全部数据。这个过程叫做回表
    • 当sql语句的所求查询字段(select列)和查询条件字段(where子句)全都包含在一个索引中,可以直接使用索引查询而不需要回表。这就是覆盖索引,可以减少一次树的检索,是常用的性能优化手段。

    索引下推为了解决什么问题

    对低于5.0的mysql来说,只能使用单个索引来筛选数据,从5.1开始,引入了 index merge 优化技术,对同一个表可以使用多个索引分别进行条件扫描,但是和全表扫描或只使用一个索引的速度比起来,去分析两个索引二叉树可能更加耗费时间,所以绝大多数情况下数据库都是是用一个索引。

    在这个前提下,当一个索引选择度(就是通过索引能过滤掉的数据比例)比较低的时候,可能单个索引过滤掉的数据并没有太多。所以我们一般会使用联合索引。但是联合索引又受最左前缀影响。有时候会不起作用,所以才做出了索引下推这个功能。(最左前缀具体请参看上一篇文章:https://blog.csdn.net/winterfeng123/article/details/108150223)

    我们来看一下有索引下推和没有索引下推,在一条sql执行过程中的区别。

    sql执行一般经过三层

    • mysql server
    • 接口层
    • 存储和事务管理层
      由于在索引下推过程中接口层没有什么实际操作,所以简略书写一下。下面分别说一下两种情况的流程
    不使用索引下推
    1. MySQL Server发出读取数据的命令,通过函数指针和handle接口(接口层)调用存储引擎的索引读或全表表读。如果有可使用的索引则进行的是索引读,但是只能选择一个索引。
    2. 指令进入存储引擎,读取索引树,在索引树上查找,拿到到符合条件的主键值,回表把满足条件的记录从表记录中读出,从存储引擎返回标识的结果
    3. 从存储引擎返回查找到的多条元组给MySQL Server,MySQL Server在得到单一索引过滤后的数据
    4. MySQL Server拿到上述数据后,按照where中其他的条件进行过滤,得到符合条件的最终结果。
    使用索引下推

    首先看上面的我们已经大概知道,如果就只有一列索引,是无所谓有没有索引下推这个功能的,所以我们只讨论存在多个where条件,这些条件中的多个列上有索引的情况。

    1. 同上,MySQL Server发出读取数据的命令,通过函数指针和handle接口(接口层)调用存储引擎的索引读或全表表读。如果有可使用的索引则进行的是索引读,但是这里会把含有索引的列的where条件都下放到存储引擎层。
    2. 指令进入存储引擎,读取某个索引树,在该索引树上查找过滤出相应数据后,并不直接去回表查询数据,而是继续通过联合索引的其他索引条件进行过滤把满足已经下推的条件的主键拿到。然后再去回表查询所有符合条件的数据(数据较少,io也就较少)。例如
    #假test表有个联合索引包含两列 a,b。
    
    假如语句如下:
    select * from test where a like "zhang%" and b > 10;
    两个条件都会下推到存储引擎,因为最左前缀,会先按照a来筛选。其次再使用b来过滤筛选过的索引值。然后再回表查询。
    
    
    1. 从存储引擎返回查找到的少量数据给MySQL Server,MySQL Server在根据其他的条件进行筛选。
    对比
    • 第 1 步中MySQL Server发送了额外的索引条件到存储引擎,多了一点点网络开销
    • 第 2 步中的回表阶段:由于比第一步过滤掉了更多的表数据,所以少了很多磁盘io
    • 第 3 步中

    接收数据阶段:要接收更多的数据,增加内部开销

    二次过滤数据阶段:要处理更多的数据,占用了更多的cpu和内存。

    总结:

    总体来说,在有联合索引的情况下,索引下推使得查询的效率有明显提升。但是也不是任何时候都可以使用索引下推的,因为其主要通过减少了回表查询的数量来优化查询,所以其限制如下:

    1. innodb引擎的表,索引下推只能用于辅助索引,这是因为主键索引树叶子结点上保存的是全行数据,根本不涉及到回表问题。

    2. 索引下推一般可用于非索引覆盖的情况下,因为索引覆盖也是没有必要回表的。

    优点如下:

    1. 提高了有联合索引时的查询效率
    2. 一定程度上打破了联合索引的最左前缀原则,详情请看 https://blog.csdn.net/winterfeng123/article/details/108150223

    索引下放也可以关闭,但是不建议管理。关闭和开启语句如下:

    #关闭
    SET optimizer_switch = 'use_index_extensions=off';
    #开启
    SET optimizer_switch = 'use_index_extensions=on';
    
    展开全文
  • 在之前《mysql索引初识》这篇文章中提到过,mysql的innodb引擎通过搜索树方式实现索引索引类型分为主键索引和二级索引(非主键索引),主键索引树中,叶子结点保存着主键即对应行的全部数据;而二级索引树中,叶子...
  • 5分钟搞懂MySQL - 索引下推优化

    千次阅读 多人点赞 2021-03-16 01:23:52
    对于长期与MySQL同流合污的朋友们来说,或许,“索引下推优化”这个词并不陌生,嗯。。经常听到,但是MySQL的这个“优化”到底优化了啥?就懵懵懂懂了,反正不是公司优化我就行了是吧。。来,让我们继续快乐的卷下去...
  • 什么是索引下推

    万次阅读 多人点赞 2019-12-10 10:27:08
    概述 索引条件下推优化(Index Condition Pushdown ...当使用索引条件下推优化时,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQ...
  • 简单易懂的MySQL覆盖索引、前缀索引索引下推

    千次阅读 多人点赞 2021-07-17 20:08:04
    文章目录前言聚集索引/非聚集索引 前言 聚集索引/非聚集索引
  • MySQL性能优化之:索引下推

    千次阅读 2020-08-23 10:21:04
    索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。 在不使用ICP的情况,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后...
  • 四、索引下推 五、小结 一、引入 在开始这篇文章之前,首先明确一个概念,聚集索引的B+树的每个节点就是一个索引页,索引页会根据先前规定好的度数来决定一个索引页放多少个索引值。 非叶子节点只有索引区...
  • 文章目录前言强制走索引覆盖索引优化in和or在表数据量比较大的情况会走索引,在表记录不多的情况下会选择全表扫描like KK% 一般情况都会走索引索引下推(Index Condition Pushdown,ICP)为什么范围查找Mysql没有用...
  • 在之前《mysql索引初识》这篇文章中提到过,mysql的innodb引擎通过搜索树方式实现索引索引类型分为主键索引和二级索引(非主键索引),主键索引树中,叶子结点保存着主键即对应行的全部数据;而二级索引树中,叶子...
  • 通俗的讲就是,如果索引的列在 select 所需获得的列中(因为在 mysql 中索引是根据索引列的值进行排序的,所以索引节点中存在该列中的部分值)或者根据一次索引查询就能获得记录就不需要回表,如果 select 所需获得...
  • 讲一讲索引下推

    2021-06-18 14:17:46
    索引下推
  • MySQL回表、索引覆盖、索引下推

    千次阅读 2020-06-09 00:55:00
    创建一张表,并创建一个自增主键索引和一个组合索引 1 2 3 4 5 6 7 8 9 10 CREATE TABLE index_opt_test ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(11) DEFAULT NULL, title varchar(11) DEFAULT NULL...
  • 索引下推索引条件下推) 2.1 不使用索引下推 2.2 使用索引下推 相关文章: 什么是最左原则、什么是索引下推? 什么是索引下推?(第二篇) 在开始介绍索引下推之前,先看联合索引的特性 1. 联合索引 联合索引...
  • 索引下推ICP详解

    2020-04-29 07:57:17
    MySQL5.6之前,查询的时候,只会根据Index Key去存储引擎层,确定索引的范围,然后将该范围内的 记录取出来,返回到Sql Server层,SqlServer层,再根据where中其他的过滤条件进行过滤。有也 就是说IndexFilter和...
  • 一、覆盖索引 1.1 概念 索引是高效找到行的一个方法,当能通过检索索引就可以读取想要的数据,那就不需要再到数据表中读取行了。如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫做覆盖索引。 1.2...
  • 在5.6版本之前的执行过程如下,先从idx_name_age索引上找到对应的主键值,然后回表找到对应的行,判断其他字段的值是否满足条件 在5.6引入了索引下推优化,可以在遍历索引的过程中,对索引中包含的字段做判断,直接...
  • Mysql联合索引的使用索引下推、覆盖索引概念 建立一张user表,id、name、age、address。建立联合索引(name,age)。 理解索引下推、覆盖索引的概念首先要理解,联合索引的普通索引的区别。 比如(name, age) 和 ...
  • 答案当然不是的,MySQL每次可以使用多个索引,即 index merge(索引合并),但大多数情况都只会使用一个索引,那这是为什么咧? 1. 为什么会有index merge MySQL5.0之前,一个表一次只能使用一个索引,无法同时...
  • 即在使用联合索引进行查询的时候,第一次显示找到最左边的第一个索引所满足的所有值,然后在返回的结果中查找所有满足第二个索引的所有值,以此类推…(mysql5.6之后引入的索引下推,之前在联合索引的第一个索引查找...
  • select id,name where name='shenjian' select id,name,sexwhere name='shenjian' 多查询了一个属性,为何检索过程完全不同?...什么是索引下推? 这些,这是今天要分享的内容。 画外音:本文试验基于My...
  • 什么是索引下推? Index Condition Pushdown(ICP)是针对MySQL使用索引从表中检索行的情况的优化。如果不使用ICP,则存储引擎将遍历索引以在基表中定位行,并将其返回给MySQL服务器,后者将评估WHERE行的条件。启用...
  • Mysql性能优化:什么是索引下推

    千次阅读 多人点赞 2020-03-29 16:25:52
    什么是索引下推索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。在不使用ICP的情况,在使用非主键索引(又叫普...
  • 索引覆盖 最左前缀 索引下推 索引覆盖 总结问题 什么是索引覆盖? 怎么用到索引覆盖 索引覆盖的情况,using index ; using index using where select * from T where k betwee 3 and 5 这条语句的执行流程是...
  • 1、覆盖索引:如果查询条件使用的是普通索引(或是联合索引的最左原则字段),查询结果是联合索引的字段或是主键,不用回表操作,直接返回结果,减少IO磁盘读写读取正行数据 2、最左前缀:联合索引的最左 N 个字段...
  • 索引下推 有了索引下推优化,可以在有like条件查询的情况,减少回表次数。 对于user_table表,我们现在有(username,age)联合索引 如果现在有一个需求,查出名称中以“张”开头且年龄小于等于10的用户信息,语句...
  • 索引失效like 以%开头,索引失效组合索引,不是使用第一列索引索引失效数据类型出现隐式转化,索引失效其它情况不推荐使用索引的情况覆盖索引组合索引最左匹配原则注意组合索引数据结构索引下推

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 117,600
精华内容 47,040
关键字:

索引下推