【mysql优化 2】索引条件下推优化

原文地址:Index Condition Pushdown Optimization


索引条件下推(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';

转载于:https://www.cnblogs.com/hhx626/p/7534579.html

发布了0 篇原创文章 · 获赞 13 · 访问量 4万+
展开阅读全文
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览