精华内容
下载资源
问答
  • 一 什么是“索引条件下推”“索引条件下推”,称为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这样的非聚集索引有效。

    三 原理

    先看//关闭ICP

    Query OK, 0 rows affected (0.00 sec)

    myrange | 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技术(过程使用数字符号标示,如①②③等) 过程解释:

    ①:My

    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技术(过程使用数字符号标示,如①②③等)

    80bcb30fcbb26bd1dc50a9a422e3bbf5.png过程解释:

    ①: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()->

    Q

    DsMrr_impl::dsmrr_next()->handler::multi_range_read_next()->

    handler::read_range_first()->handler::ha_index_read_

    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 5.6开始,在索引方面有了一些改进,比如索引条件下推(Index condition pushdown,ICP),严格来说属于优化器层面的改进。如果简单来理解,就是优化器会尽可能的把index condition的处理从Server层下推到存储...

    自MySQL 5.6开始,在索引方面有了一些改进,比如索引条件下推(Index condition pushdown,ICP),严格来说属于优化器层面的改进。

    如果简单来理解,就是优化器会尽可能的把index condition的处理从Server层下推到存储引擎层。举一个例子,有一个表中含有组合索引idx_cols包含(c1,c2,…,cn)n个列,如果在c1上存在范围扫描的where条件,那么剩余的c2,…,cn这n-1个上索引都无法用来提取和过滤数据,而ICP就是把这个事情优化一下。

    我们在MySQL 5.6的环境中来简单测试一下。

    我们创建表emp,含有一个主键,一个组合索引来说明一下。

    create table emp(

    empno smallint(5) unsigned not null auto_increment,

    ename varchar(30) not null,

    deptno smallint(5) unsigned not null,

    job varchar(30) not null,

    primary key(empno),

    key idx_emp_info(deptno,ename)

    )engine=InnoDB charset=utf8;

    当然我也随机插入了几条数据,意思一下。

    insert into emp values(1,'zhangsan',1,'CEO'),(2,'lisi',2,'CFO'),(3,'wangwu',3,'CTO'),(4,'jeanron100',3,'Enginer');

    ICP的控制在数据库参数中有一个优化器参数optimizer_switch来统一管理,我想这也是MySQL优化器离我们最贴近的时候了。可以使用如下的方式来查看。

    show variables like 'optimizer_switch';

    当然在5.6以前的版本中,你是看不到index condition pushdown这样的字样的。在5.6版本中查看到的结果如下:

    # mysqladmin var|grep optimizer_switch 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,subquery_materialization_cost_based=on,use_index_extensions=on下面我们就用两个语句来对比说明一下,就通过执行计划来对比。

    set optimizer_switch = "index_condition_pushdown=off"

    > explain select  *   from emp  where deptno between 1 and 100 and ename ='jeanron100';

    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

    |  1 | SIMPLE      | emp   | ALL  | idx_emp_info  | NULL | NULL    | NULL |    4 | Using where |

    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

    而如果开启,看看ICP是否启用。

    set optimizer_switch = "index_condition_pushdown=on";> explain select  *   from emp  where deptno between 10 and 3000 and ename ='jeanron100';

    +----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+

    | id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra                 |

    +----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+

    |  1 | SIMPLE      | emp   | range | idx_emp_info  | idx_emp_info | 94      | NULL |    1 | Using index condition |

    +----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+

    1 row in set (0.00 sec)如果你观察仔细,会发现两次的语句还是不同的,那就是范围扫描的范围不同,如果还是用原来的语句,结果还是有一定的限制的。

    > explain select  *   from emp  where deptno between 1 and 300 and ename ='jeanron100';

    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

    |  1 | SIMPLE      | emp   | ALL  | idx_emp_info  | NULL | NULL    | NULL |    4 | Using where|

    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

    1 row in set (0.00 sec)这个地方就值得好好推敲了。

    展开全文
  • 自MySQL 5.6开始,在索引方面有了一些改进,比如索引条件下推(Index condition pushdown,ICP),严格来说属于优化器层面的改进。如果简单来理解,就是优化器会尽可能的把index condition的处理从Server层下推到存储...

    自MySQL 5.6开始,在索引方面有了一些改进,比如索引条件下推(Index condition pushdown,ICP),严格来说属于优化器层面的改进。

    ab11da02356211f36a81adf2f38fd752.png

    如果简单来理解,就是优化器会尽可能的把index condition的处理从Server层下推到存储引擎层。举一个例子,有一个表中含有组合索引idx_cols包含(c1,c2,…,cn)n个列,如果在c1上存在范围扫描的where条件,那么剩余的c2,…,cn这n-1个上索引都无法用来提取和过滤数据,而ICP就是把这个事情优化一下。

    我们在MySQL 5.6的环境中来简单测试一下。

    我们创建表emp,含有一个主键,一个组合索引来说明一下。

    createtableemp(

    empno smallint(5) unsignednotnullauto_increment,

    ename varchar(30)notnull,

    deptno smallint(5) unsignednotnull,

    job varchar(30)notnull,

    primarykey(empno),

    keyidx_emp_info(deptno,ename)

    )engine=InnoDB charset=utf8;

    当然我也随机插入了几条数据,意思一下。

    insertintoempvalues(1,'zhangsan',1,'CEO'),(2,'lisi',2,'CFO'),(3,'wangwu',3,'CTO'),(4,'jeanron100',3,'Enginer');

    ICP的控制在数据库参数中有一个优化器参数optimizer_switch来统一管理,我想这也是MySQL优化器离我们最贴近的时候了。可以使用如下的方式来查看。

    show variableslike'optimizer_switch';

    当然在5.6以前的版本中,你是看不到index condition pushdown这样的字样的。在5.6版本中查看到的结果如下:

    # mysqladmin var|grep optimizer_switch 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,subquery_materialization_cost_based=on,use_index_extensions=on下面我们就用两个语句来对比说明一下,就通过执行计划来对比。

    setoptimizer_switch ="index_condition_pushdown=off"

    > explain select*fromempwheredeptnobetween1and100andename ='jeanron100';

    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

    | id | select_type | table| type | possible_keys |key| key_len | ref  |rows| Extra      |

    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

    |  1 | SIMPLE      | emp  | ALL| idx_emp_info  |NULL|NULL|NULL|    4 | Usingwhere|

    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

    而如果开启,看看ICP是否启用。

    setoptimizer_switch ="index_condition_pushdown=on";> explainselect*fromempwheredeptnobetween10and3000andename ='jeanron100';

    +----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+

    | id | select_type | table| type  | possible_keys |key| key_len | ref  |rows| Extra                |

    +----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+

    |  1 | SIMPLE      | emp  | range | idx_emp_info  | idx_emp_info | 94      | NULL|    1 | Usingindexcondition |

    +----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+

    1 row in set (0.00 sec)如果你观察仔细,会发现两次的语句还是不同的,那就是范围扫描的范围不同,如果还是用原来的语句,结果还是有一定的限制的。

    > explainselect*fromempwheredeptnobetween1and300andename ='jeanron100';

    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

    | id | select_type | table| type | possible_keys |key| key_len | ref  |rows| Extra      |

    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

    |  1 | SIMPLE      | emp  | ALL| idx_emp_info  |NULL|NULL|NULL|    4 | Usingwhere|

    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

    1 row in set (0.00 sec)这个地方就值得好好推敲了。

    【编辑推荐】

    【责任编辑:武晓燕 TEL:(010)68476606】

    点赞 0

    展开全文
  • 索引条件下推(ICP:index condition pushdown)是mysql中一个常用的优化,尤其是当mysql需要从一张表里检索数据时。 如果没有ICP,存储引擎将会根据WHERE子句的条件遍历整个表单数据,然后返回给mysql服务器。启用ICP...

    索引条件下推(ICP:index condition pushdown)是mysql中一个常用的优化,尤其是当mysql需要从一张表里检索数据时。 如果没有ICP,存储引擎将会根据WHERE子句的条件遍历整个表单数据,然后返回给mysql服务器。启用ICP,如果可以通过使用索引的列来满足WHERE条件,MySQL服务器将WHERE条件的这部分推送到存储引擎。然后,存储引擎通过使用索引来确定推送的条件,并且通过这样的方式从表中读取行。

    ICP可以减少存储引擎必须访问基础表的次数以及MySQL服务器必须访问存储引擎的次数

    可以采用ICP优化的场景:

    1,需要访问整表。比如用于方法:range,ref,eq_ref,ref_or_null等

    2,适用于使用InnoDB和MyISAM的表单,以及它们的分区表。但对于InnoDB来说,ICP仅用于二级索引。它的目的是去减少整表读的次数,和I/O操作次数。而对于InnoDB集群索引,完整的记录已经被读取到了InnoDB的缓冲区,这个时候使用ICP则不是为了减少I/O操作次数。注意:ICP不支持在虚拟的数据列上建立二级索引,而InnoDB则支持!

    3,引用子查询的条件不能被下推

    4,触发条件不能被下推(对于这一点,详情请看:使用exists策略优化子查询)

    要想明白ICP是怎么工作的,首先,需要考虑在没有ICP的时候,索引的扫描过程:

    1,获取下一行,首先读索引元组,然后使用索引去查找并读取所有的行

    2,根据WHERE条件部分,判断数据是否符合。根据判断结果接受或拒绝该行

    使用ICP,这个过程则会变成这样:

    1,获取下一行的索引元组(不是所有行)

    2,根据WHERE条件部分,判断是否可以只通过索引列满足条件。如果不满足,则获取下一行索引元组

    3,如果满足条件,则通过索引元组去查询并读取所有的行

    4,根据遗留的WHERE子句中的条件,在当前表中进行判断,根据判断结果接受或者拒绝改行

    当使用ICP时,EXPLAIN 输出显示在额外的列中正在使用的索引条件。它不显示使用索引,因为它不适用于全表搜索

    设想一个表中包含了用户的信息以及它们的住址信息,而且这张表定义了一个索引为:(zipcode, lastname, firstname)。如果我们知道一个人的zipcode,但是不确定这个人的lastname,我们可以进行以下搜索:

    SELECT * FROM people

    WHERE zipcode='95054'

    AND lastname LIKE '%etrunia%'

    AND address LIKE '%Main Street%';

    Mysql会使用索引去扫描zipcode=“95054”的用户。第二部分lastname LIKE '%etrunia%'不用背用于去限制扫描条件。所以,不启用ICP,这个查询将遍历所有的行去找到zipcode=“95054”的用户。当使用ICP时,mysql在读取全表前,会检查lastname LIKE '%etrunia%' 。这也就避免了读取与匹配zipcode条件但不是lastname条件的索引元组相对应的所有行。

    ICP默认启动。可以通过optimizer_switch系统变量去控制它是否开启:

    SET optimizer_switch = 'index_condition_pushdown=off';

    SET optimizer_switch = 'index_condition_pushdown=on';

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

    千次阅读 2019-01-31 16:09:02
    一 什么是“索引条件下推” “索引条件下推”,称为Index Condition Pushdown (ICP),这是MySQL提供的用某一个索引对一个特定的表从表中获取元组”,注意我们这里特意强调了“一个”,这是因为这样的索引优化...
  • 一 什么是“索引条件下推”“索引条件下推”,称为Index Condition Pushdown (ICP),这是MySQL提供的用某一个索引对一个特定的表从表中获取元组”,注意我们这里特意强调了“一个”,这是因为这样的索引优化不是用于...
  • 自MySQL 5.6开始,在索引方面有了一些改进,比如索引条件下推(Index condition pushdown,ICP),严格来说属于优化器层面的改进。如果简单来理解,就是优化器会尽可能的把indexcondition的处理从Server层下推到存储引擎...
  • 索引条件下推,Index Condition Pushdown,简称ICP,是MySQL内部通过索引查询数据的一种优化方法,简单来说就是将原本需要在Server层对数据进行过滤的条件下推到了引擎层去做,在引擎层过滤更多的数据,这样从引擎层...
  • 索引条件下推(ICP)是对MySQL使用索引从表中检索行的情况的优化。如果没有ICP,存储引擎会遍历索引以查找基表中的行,并将它们返回给MySQL服务器,由server层再做一波筛选。启用ICP后,如果只使用索引中的列来评估...
  • 索引条件下推(ICP)是针对MySQL使用索引从表中检索行的情况的一种优化。如果不使用ICP,则存储引擎将遍历索引以在基表中定位行,并将其返回给MySQL服务器,后者将评估WHERE行的条件。启用ICP后,如果WHERE可以仅使用...
  • MySQL 索引条件下推 Index Condition Pushdown 出现在MySQL5.6及之后的版本中,能大幅提升查询效率,原因如下:内容摘录自《深入理解MariaDB和MySQL》下面使实验,使用官方提供的employees 测试数据库演示。...
  • ICP 优化适用于 MySQL 利用索引从表里检索数据的场景。ICP 适用的场景ICP 用于访问方法是 range/ref/eq_ref/ref_or_null,且需要访问表的完整行记录。ICP适用于 InnoDB 和 MyISAM 的表,包括分区的表。对于 InnoDB ...
  • 索引条件下推(ICP), Index condition pushdown,简单的来讲,使用索引查询后,不立即进行回表查询,通过where条件中的字段(该字段也是位于索引中)进行过滤,将过滤之后的结果进行回表查询。相对于没有开启ICP,减少了...
  • 索引条件下推(ICP)是对MySQL使用索引从表中检索行的情况的优化。如果没有ICP,存储引擎会遍历索引以查找基表中的行,并将它们返回给MySQL服务器,由server层再做一波筛选。启用ICP后,如果只使用索引中的列来评估...
  • 索引条件下推是对使用索引从表中检索行的一种优化。如果不使用icp,存储引擎先遍历索引然后去基表中去定位所需要的行,并将其返回给Mysql服务器,然后服务器进行where条件的过滤。使用到icp后,如果where的部分列...
  • 索引条件下推”,称为Index Condition Pushdown (ICP),这是MySQL提供的用某一个索引对一个特定的表从表中获取元组”,注意我们这里特意强调了“一个”,这是因为这样的索引优化不是用于多表连接...
  • 索引条件下推(index condition pushdown) 概念 ​ 当没有icp时,存储引擎会运用索引定位到符合索引条件的行,将这些行发送给MySQL server去计算where 条件是否正确。当有icp时,如果where 条件的一部分可以通过索引...
  • 索引条件下推,Index Condition Pushdown,简称ICP,是MySQL内部通过索引查询数据的一种优化方法,简单来说就是将原本需要在Server层对数据进行过滤的条件下推到了引擎层去做,在引擎层过滤更多的数据,这样从引擎层...
  • 一、介绍联合索引:对多个字段同时建立索引(联合索引是有顺序的,比如ABC,ACB是完全不同的两种联合索引)。覆盖索引:即从辅助索引中得到查询的记录,而不...索引条件下推:称为Index Condition Pushdown (ICP),...
  • 索引条件下推(ICP) ICP(index condition pushdown)是mysql利用索引(二级索引)元组和筛选字段在索引中的where条件从表中提取数据记录的一种优化操作。 ICP的思想是:存储引擎在访问索引的时候检查筛选字段在...
  • MySQL的ICP技术的使用条件Index Condition Pushdown (ICP) ,是索引条件下推,是MySQL利用索引快速获取数据的技术。只要在查询语句中,使用了WHERE子句,且子句中有存在索引的条件表达式,比如a>3且列上存在索引,则...
  • 上一篇文章一起学习Mysql索引二(索引的高性能策略)中我们提到了Mysql5.7版本的一个改进:"索引条件下推"(index condition pushdown)。那么,今天就让我们来揭开它的神秘面纱。 从ICP(index condition pushdown...
  • 目录第八章 优化(六)—— 索引条件下推优化8.2 优化SQL语句8.2.1 优化 SELECT 语句8.2.1.6 索引条件下推优化 第八章 优化(六)—— 索引条件下推优化 8.2 优化SQL语句 8.2.1 优化 SELECT 语句 8.2.1.6 索引条件下...
  • 自MySQL 5.6开始,在索引方面有了一些改进,比如索引条件下推(Index condition pushdown,ICP),严格来说属于优化器层面的改进。 如果简单来理解,就是优化器会尽可能的把index condition的处理从Server层下推到...
  • 自MySQL 5.6开始,在索引方面有了一些改进,比如索引条件下推(Index condition pushdown,ICP),严格来说属于优化器层面的改进。如果简单来理解,就是优化器会尽可能的把index condition的处理从Server层下推到存储...

空空如也

空空如也

1 2 3 4 5 ... 15
收藏数 291
精华内容 116
关键字:

索引条件下推