精华内容
下载资源
问答
  • 索引条件下推(index condition pushdown) 概念 ​ 当没有icp时,存储引擎会运用索引定位到符合索引条件的行,将这些行发送给MySQL server去计算where 条件是否正确。当有icp时,如果where 条件的一部分可以通过索引...

    索引条件下推(index condition pushdown)

    概念

    ​ 当没有icp时,存储引擎会运用索引定位到符合索引条件的行,将这些行发送给MySQL server去计算where 条件是否正确(重点是在server端计算)。当有icp时,如果where 条件的一部分可以通过索引来计算(意思就是索引中包含的信息足以计算这一部分where条件),那么MySQL Server就会将这部分索引条件下推到(index condition push)存储引擎(下推的意思可以看MySQL逻辑架构图)去计算(这里是在存储引擎端就把计算给做了,不需要在发送到server端),这样的话就可以返回尽量少的行给MySQL Server,也尽量少的让MySQL Server访问存储引擎层。
    一言以蔽之,在一个用到索引的查询当中,所有二级索引里包含的信息,都可以通过“ICP机制”被用到“在二级索引中过滤尽可能多的行”。

    Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.

    ——官方原文

    ​ 使用索引下推的标志是explain的extra列中显示了Using index condition。

    打开和关闭索引条件下推

    SET optimizer_switch = 'index_condition_pushdown=off'; 
    
    SET optimizer_switch = 'index_condition_pushdown=on';
    

    例子

    例子1:有一个联合索引(a,b,c),当查询where a=x and c=x2,根据最左匹配原则,只有a会用得上,对于c不同版本就有区别了:

    1. 在MySQL5.6之前,就是根据二级索引找出每一条a=x的叶子节点的主键,然后拿着主键去回表,根据回表拿到对应行c的值,将不符合c=x2的行过滤掉
    2. 在MySQL5.6引入了索引下推优化,找到a=x后,因为在二级索引中就有c的值了,MySQL Server 就会将c=x2这个索引条件下推给存储引擎去做,所以在二级索引内就判断该行是否符合c=x2,不符合直接就跳过了,这样就少了很多次回表

    例子2(官方给的例子):

    Suppose that a table contains information about people and their addresses and that the table has an index defined as INDEX (zipcode, lastname, firstname). If we know a person’s zipcode value but are not sure about the last name, we can search like this:

    SELECT * FROM people
      WHERE zipcode='95054'
      AND lastname LIKE '%etrunia%'
      AND address LIKE '%Main Street%';
    

    MySQL can use the index to scan through people with zipcode='95054'. The second part (lastname LIKE '%etrunia%') cannot be used to limit the number of rows that must be scanned, so without Index Condition Pushdown, this query must retrieve full table rows for all people who have zipcode='95054'.

    With Index Condition Pushdown, MySQL checks the lastname LIKE '%etrunia%' part before reading the full table row. This avoids reading full rows corresponding to index tuples that match the zipcode condition but not the lastname condition.

    假设一个包含人们信息和他们的地址的表,并且这个表包含了index(zipcode,lastname,firstname)定义的表。如果我们知道一个人的zipcode但不确定他的lastname,我们就可以像这样一样搜索

    select * from people
      where zipcode = '95054'
        and lastname like '%entrunia%'
        and address like '%Main Street%';
    

    MySQL可以使用索引扫描zipcode='95054’的人。但是第二部分(lastname like ‘%etrunia%’)不能被使用(不符合最左匹配原则)用来限制必须被扫描的行的数量,所以没有icp的话,这个查询必须检索所有zipcode='95054’的人的整个数据行。

    使用icp的话,MySQL会在读取整个数据行之前判断’lastname like ‘%etrunia%’’ 部分。这避免了读取 ‘可以使用zipcode 条件但不可以使用lastname条件’ 的索引项对应的整个数据行。

    展开全文
  • 索引条件下推(ICP) ICP(index condition pushdown)是mysql利用索引(二级索引)元组和筛选字段在索引中的where条件从表中提取数据记录的一种优化操作。 ICP的思想是:存储引擎在访问索引的时候检查筛选字段在...

    索引条件下推(ICP)

    ICP(index condition pushdown)是mysql利用索引(二级索引)元组筛选字段在索引中的where条件从表中提取数据记录的一种优化操作
    ICP的思想是:存储引擎在访问索引的时候检查筛选字段在索引中的where条件(pushed index condition,推送的索引条件),如果索引元组中的数据不满足推送的索引条件,那么就过滤掉该条数据记录。ICP(优化器)尽可能的把index condition的处理从server层下推到storage engine层。storage engine使用索引过滤不相关的数据,仅返回符合index condition条件的数据给server层。也是说数据过滤尽可能在storage engine层进行,而不是返回所有数据给server层,然后后再根据where条件进行过滤。
    使用ICP(mysql 5.6版本以前)和没有使用ICP的数据访问和提取过程如下:
    优化器没有使用ICP时,数据访问和提取的过程如下:

    1. 当storage engine读取下一行时,首先读取索引元组(index tuple),然后使用索引元组在基表中(base table)定位和读取整行数据。

    2. server层评估where条件,如果该行数据满足where条件则使用,否则丢弃。

    3. 循环执行1,直到最后一行数据。

    在这里插入图片描述
    优化器使用ICP时,server层将会把能够通过使用索引进行评估的where条件下推storage engine层。数据访问和提取过程如下:

    1. storage engine从索引中读取下一条索引元组。

    2. storage engine使用索引元组评估下推的索引条件。如果没有满足where条件,storage engine将会处理下一条索引元组(回到上一步)。只有当索引元组满足下推的索引条件的时候,才会继续去基表中读取数据

    3. 如果满足下推的索引条件,storage engine通过索引元组定位基表的行和读取整行数据并返回给server层

    4. server层评估没有被下推到storage engine层的where条件,如果该行数据满足where条件则使用,否则丢弃。
      在这里插入图片描述

    索引条件下推的意思就是筛选字段在索引中的where条件server层下推到storage engine层,这样可以在存储引擎层过滤数据。由此可见,ICP可以减少存储引擎访问基表的次数mysql server访问存储引擎的次数。

    ICP使用的条件
    • 只能用于二级索引(secondary index)
    • explain显示的执行计划中type值(join 类型)为rangerefeq_ref或者ref_or_null
    • 查询需要访问表的整行数据,即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)
    • ICP可以用于MyISAMInnnoDB存储引擎,不支持分区表(5.7将会解决这个问题)
    ICP优化功能的开启与关闭

    index_condition_push优化功能,默认开启

    MySQL5.6可以通过设置optimizer_switch([global|session],dynamic)变量开启或者关闭
    mysql > set optimizer_switch=’index_condition_pushdown=on|off’

    用explain查看执行计划时,如果执行计划中的Extra信息为“using index condition”,表示优化器使用的ICP。

    展开全文
  • MySQL 索引条件下推

    千次阅读 2019-01-31 16:09:02
    一 什么是“索引条件下推” “索引条件下推”,称为Index Condition Pushdown (ICP),这是MySQL提供的用某一个索引对一个特定的表从表中获取元组”,注意我们这里特意强调了“一个”,这是因为这样的索引优化...

    一 什么是“索引条件下推”

    “索引条件下推”,称为 Index Condition Pushdown (ICP),这是MySQL提供的用某一个索引对一个特定的表从表中获取元组”,注意我们这里特意强调了“一个”,这是因为这样的索引优化不是用于多表连接而是用于单表扫描,确切地说,是单表利用索引进行扫描以获取数据的一种方式。 

    二 “索引条件下推”的目的

    用ySQL官方手册描述:

    The goal of ICP is to reduce the number of full-record reads and thereby reduce IO operations. For InnoDB clustered indexes, the complete record is already read into the InnoDB buffer. Using ICP in this case does not reduce IO.

    这句官方描述,一是说明减少完整记录(一条完整元组)读取的个数;二是说明对于InnoDB聚集索引无效,只能是对SECOND INDEX这样的非聚集索引有效。

    三 原理

    先看实例:

    mysql> set optimizer_switch='index_condition_pushdown=off'; //关闭ICP
    Query OK, 0 rows affected (0.00 sec)
    mysql> EXPLAIN SELECT * FROM t4 WHERE 1=t4.a4 AND t4.name like 'char%';
    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | t4    | NULL       | range | a4_i          | a4_i | 28      | NULL |    1 |   100.00 | Using where |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    mysql> set optimizer_switch='index_condition_pushdown=on';  //打开ICP,则Extra列中显示“Using index condition”
    Query OK, 0 rows affected (0.00 sec)
    mysql> EXPLAIN SELECT * FROM t4 WHERE 1=t4.a4 AND t4.name like 'char%';
    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
    | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
    |  1 | SIMPLE      | t4    | NULL       | range | a4_i          | a4_i | 28      | NULL |    1 |   100.00 | Using index condition |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)
    如果打开ICP,则执行计划的Extra列会显示“Using index condition”,这表明在。
    借用网上的2张图加以改造,并配以解释,来说明原理,更清晰地说明问题。
    
    图一:不使用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 Server,MySQL Server在⑦得到较多的元组。
    ⑦--⑧:⑦到⑧依据WHERE子句条件进行过滤,得到满足条件的元组。注意在MySQL Server层得到较多元组,然后才过滤,最终得到的是少量的、符合条件的元组。
     
    图二:使用ICP技术(过程使用数字符号标示,如①②③等)
     

     

    过程解释:

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

    ②、③:进入存储引擎,读取索引树,在索引树上查找,把满足已经下推的条件的(经过查找,红色的满足)从表记录中读出(步骤④,通常有IO),从存储引擎返回⑤标识的结果。此处,不仅要在索引行进行索引读取(通常是内存中,速度快。步骤③),还要在③这个阶段依据下推的条件进行进行判断,不满足条件的,不去读取表中的数据,直接在索引树上进行下一个索引项的判断,直到有满足条件的,才进行步骤④,这样,较没有ICP的方式,IO量减少。

    ⑥:从存储引擎返回查找到的少量元组给MySQL Server,MySQL Server在⑦得到少量的元组。因此比较图一无ICP的方式,返回给MySQL Server层的即是少量的、符合条件的元组。
     另外,图中的部件层次关系,不再进行解释。
    四 实现细节
    1 ICP只能用于辅助索引,不能用于聚集索引。
    2 ICP只用于单表,不是多表连接是的连接条件部分(如开篇强调)
    如果表访问的类型为:
    3 EQ_REF/REF_OR_NULL/REF/SYSTEM/CONST: 可以使用ICP
    4 range:如果不是“index tree only(只读索引)”,则有机会使用ICP
    5 ALL/FT/INDEX_MERGE/INDEX_SCAN:  不可以使用ICP


    五 上楼

    1 条件下推,一直是SQL优化的基本规则。所以,条件下推技术是常规技术。数据库的优化器几乎不会不实现条件下推优化。

    2 技术层面,MySQL存在MySQL Server层和储存层,使得条件下推显得“有些割裂”。

    3 非技术层面,MySQL之所以引入ICP,猜一猜或拍拍脑袋,原因你懂得。


    六 从代码的角度看

    对于图一的解释,给出了读数据的代码片段,无论是关闭还是打开ICP, 从下面给出的函数调用关系可以看出,2幅图对应的情况下,代码路径是一致的.

    首条元组读取调用关系(蓝色标识和非首条元组不同之处):

    JOIN::exec()->do_select()->sub_select()->join_init_read_record()->rr_quick()->
      QUICK_RANGE_SELECT::get_next()->ha_innobase::multi_range_read_next()->
      DsMrr_impl::dsmrr_next()->handler::multi_range_read_next()->
      handler::read_range_first()->handler::ha_index_read_map()->
      handler::index_read_map()->ha_innobase::index_read()

     

    除首条元组读取调用关系(蓝色标识和首条元组不同之处)

    JOIN::exec()->do_select()->sub_select()->join_init_read_record()->rr_quick()->
      QUICK_RANGE_SELECT::get_next()->ha_innobase::multi_range_read_next()->
      DsMrr_impl::dsmrr_next()->handler::multi_range_read_next()->
      handler::read_range_next()->handler::ha_index_next()->
      ha_innobase::index_next()->ha_innobase::general_fetch()

    展开全文
  • 【MySQL】索引条件下推优化

    千次阅读 2019-08-06 01:50:37
    索引下推优化欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题,有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右...


    官方链接: https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html.

    概述

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

    工作过程

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

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

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

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

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

    EXPLAN分析

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

    示例

    假设有一张people表,包含字段zipcode、lastname、firstname
    索引为(zipcode,lastname,firstname)
    然后我们执行下面的查询

    SELECT * FROM people
      WHERE zipcode='95054'
      AND lastname LIKE '%etrunia%'
      AND address LIKE '%Main Street%';
    

    如果不使用索引条件下推优化的话,MySQL只能根据索引查询出zipcode为95054的所有行,然后再依次比较是否符合全部条件。当使用了索引条件下推优化技术后,可以通过索引中存储的数据判断当前索引对应的数据是否符合条件,只有符合条件的数据才将整行数据查询出来。

    配置

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

    SET optimizer_switch = 'index_condition_pushdown=off';
    SET optimizer_switch = 'index_condition_pushdown=on';
    

    思考

    索引下推优化技术其实就是充分利用了索引中的数据,尽量在查询出整行数据之前过滤掉无效的数据。由于需要存储引擎将索引中的数据与条件进行判断,所以这个技术是基于存储引擎的,只有特定引擎可以使用。并且判断条件需要是在存储引擎这个层面可以进行的操作才可以,比如调用存储过程的条件就不可以,因为存储引擎没有调用存储过程的能力。

    展开全文
  • 使用到icp后,如果where的部分列可以仅使用索引中的列来过滤,则mysql服务器会将这部分条件下推到存储引擎,存储引擎使用索引条目来计算已入的索引条件,只有满足这个条件,才从表中读取行。从而减少存储引擎访问...
  • 【mysql优化 2】索引条件下推优化

    千次阅读 2017-08-02 20:41:04
    索引条件下推(ICP:index condition pushdown)是mysql中一个常用的优化,尤其是当mysql需要从一张表里检索数据时。 如果没有ICP,存储引擎将会根据WHERE子句的条件遍历整个表单数据,然后返回给mysql服务器。启用...
  • 索引条件下推(ICP), Index condition pushdown,简单的来讲,使用索引查询后,不立即进行回表查询,通过where条件中的字段(该字段也是位于索引中)进行过滤,将过滤之后的结果进行回表查询。相对于没有开启ICP,减少了...
  • MySQL--索引条件下推优化

    千次阅读 2017-03-13 19:06:13
    一 什么是“索引条件下推”“索引条件下推”,称为 Index Condition Pushdown (ICP),这是MySQL提供的用某一个索引对一个特定的表从表中获取元组”,注意我们这里特意强调了“一个”,这是因为这样的索引优化不是...
  • 索引条件下推(ICP)是对MySQL使用索引从表中检索行的情况的优化。如果没有ICP,存储引擎会遍历索引以查找基表中的行,并将它们返回给MySQL服务器,由server层再做一波筛选。启用ICP后,如果只使用索引中的列来评估...
  • 我们容易有以一个误区,就是在经常使用的查询条件上都建立索引索引越多越好,那到底是不是这样呢? 在讲正文之前,我们先来看一个重要的属性列的离散度,公式如下: count(distinct(column_name)) : count(*) --列...
  • 联合索引的树结构、最左匹配原则、如何选择合适的索引列顺序、索引下推图文讲解
  • 上一篇文章一起学习Mysql索引二(索引的高性能策略)中我们提到了Mysql5.7版本的一个改进:"索引条件下推"(index condition pushdown)。那么,今天就让我们来揭开它的神秘面纱。 从ICP(index condition pushdown...
  • 索引条件下推(ICP)是对MySQL使用索引从表中检索行的情况的优化。 如果没有ICP,存储引擎会遍历索引以查找基表中的行,并将它们返回给MySQL服务器,该服务器会评估行的WHERE条件。 启用ICP后,如果只使用索引中的列...
  • 索引条件下推的意思就是筛选字段在索引中的where条件从server层下推到storage engine层,这样可以在存储引擎层过滤数据。由此可见,ICP可以减少存储引擎访问基表的次数和mysql server访问存储引擎的次数。 注意...
  • 1、主键索引:联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符 2、联合索引:根据创建联合索引的顺序,以最左原则进行where检索,比如(age,name)以...3、覆盖索引:如果查询条件使用的是普通索引(...
  • MySQL是怎么使用索引条件下推的?

    千次阅读 2014-03-27 13:47:49
    转自:... ... Index Condition Pushdown (ICP) ,是索引条件下推,是MySQL利用索引快速获取数据的技术。 只要在查询语句中,使用了WHERE子句,且子句中有存在索引的条件表达式,比如a>3且列上存在索引
  • 上一节我们讲解了聚集索引和非聚集索引的区别(索引知识系列一:聚集索引与非索引详解 ),我们知道非聚集索引在查询过程中有回表的过程,这就造成了效率的下降。那如何不用回表或者减少回表以提高查询速度呢?这...
  • 1、覆盖索引:如果查询条件使用的是普通索引(或是联合索引的最左原则字段),查询结果是联合索引的字段或是主键,不用回表操作,直接返回结果,减少IO磁盘读写读取正行数据 2、最左前缀:联合索引的最左 N 个字段...
  • 有了索引下推优化,可以在有like条件查询的情况,减少回表次数。 对于user_table表,我们现在有(username,age)联合索引 如果现在有一个需求,查出名称中以“张”开头且年龄小于等于10的用户信息,语句如下: ...
  • 索引下推

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

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 54,046
精华内容 21,618
关键字:

索引条件下推

友情链接: text.rar