注册 登录
  • 欢迎访问"运维那点事",推荐使用Google浏览器访问,可以扫码关注本站的"微信公众号"。
  • 如果您觉得本站对你有帮助,那么可以扫码捐助以帮助本站更好地发展。

MySQL ICP(Index Condition Pushdown)特性

MySQL InnoDB 彭东稳 6091次浏览 已收录 0个评论

一、SQL的where条件提取规则

在说 ICP(Index Condition Pushdown,索引条件下推) 特性之前,必须先搞明白根据何登成大神总结出一套放置于所有 SQL 语句而皆准的 where 查询条件的提取规则,为 ICP 提供理论支持。详情请看:SQL语句中where条件,在数据库中提取与应用浅析

二、Index Condition Pushdown

Index Condition Pushdown (ICP,索引条件下推) 是MySQL 5.6版本中的新特性,是一种在存储引擎层根据二级索引过滤 where 条件,从而达到减少回表次数的一种优化方式。不难看出,索引条件下推的重点就是索引过滤了,简单说就是有些搜索条件中虽然出现在了索引列,但却不能使用到索引,比如下边这个查询:

其中的key1 > 'w'可以使用到索引,但是key1 LIKE '%a'却无法使用到索引,在以前版本的 MySQL 中,是按照下边步骤来执行这个查询的:

  • 先根据key1 > 'w'这个条件,形成一个范围区间后从二级索引 idx_key1 中获取到对应的二级索引记录。
  • 根据上一步骤得到的二级索引记录中的主键值进行回表,找到完整的用户记录返回给 MySQL Server 层。
  • MySQL Server 层再检测该记录是否符合key1 LIKE '%a'这个条件,将符合条件的记录返回给用户。

但是虽然key1 LIKE '%a'不能组成范围区间参与range访问方法的执行,但这个条件毕竟只涉及到了key1列,所以在这个基础上还是可以优化的,MySQL 把上面的步骤改进了一下:

  • 先根据key1 > 'w'这个条件,定位到二级索引 idx_key1 中对应的二级索引记录。
  • 对于指定的二级索引记录,先不着急回表,而是先检测一下该记录是否满足key1 LIKE '%a'这个条件,如果这个条件不满足,则该二级索引记录压根儿就没必要回表。
  • 对于满足key1 LIKE '%a'这个条件的二级索引记录执行回表操作。

我们说回表操作其实是一个随机 IO,比较耗时,所以上述修改虽然只改进了一点点,但是可以省去好多回表操作的成本。MySQL中把这个改进称之为索引条件下推(英文名:Index Condition Pushdown,ICP)。

如果在查询语句的执行过程中将要使用索引条件下推这个特性,在 Extra 列中将会显示 Using index condition,比如这样:

另外,下面这种查询也可能会看见 Extra 列显示 Using index condition:

对于这种查询,理论上形成一个范围区间的搜索条件后从二级索引 idx_key1 直接就可以获取到对应的二级索引记录,然后回表取数据,应该不涉及到 ICP 啊,但确实显示了 Using index condition。

另外,在 MySQL 中也是可以手动关闭 ICP 的,在 optimizer_switch 变量中记录了 MySQL 所有相关特性,都可以可以选择开启或关闭。比如下面关闭 ICP :set optimizer_switch='index_condition_pushdown=off'。当我们关闭 ICP 后,同样的查询,我们看一下 Extra 列显示什么:

可以看到,显示了 Using where,有点不可理解哈。我的理解是当 MySQL Server 层牵扯到条件过滤才会显示 Using where。显然这里是可以通过存储引擎层直接从索引定位数据的。

后来咨询了一些源码朋友后,得到结论是:

在优化器拿到一条 SQL 后(如:SELECT * FROM s1 WHERE key1 > 'w' AND key1 LIKE '%a'),经过成本分析产生执行计划,执行计划里写了用哪个索引(如:idx_key1),确定使用某个索引时,才会把跟这个索引相关的条件提取出来(如:key1 > 'w' AND key1 LIKE '%a'),并确定范围区间搜索条件(如:key1 > 'w'),然后设置到有关存储引擎的一个数据结构中去,然后调用存储引擎提供的一些查找数据的接口,比如最常用的几个如下(更多可以参考“Innodb Handler_read_*变量解释”):

  • ha_innobase::index_read,这个函数是访问索引的时候定位到值所在的位置用到的函数,因为必须要知道读取索引的开始位置才能向下访问。
  • ha_innobase::index_next(uchar* buf),这个函数是从游标中读取索引的下一条数据用到的函数,该游标必须先使用 index_read 定位到。但也不是所有的从游标中读取索引下一条数据都会使用这个函数,比如当访问路径为 range(范围查询) 或 index(索引扫描) 时才使用这个函数,
  • ha_innobase::index_next_same(uchar* buf),这个函数是读取与作为参数给出的键值匹配的下一行,就是 ref(等值查询) 访问方式会使用这个函数。可以看出跟 index_next 函数一样虽然都是读数据,但是两者读取数据的方式是不同的。

这里就以我们上面的执行计划 range 查询方式为例子,使用 index_next 函数,我们所指定的区间搜索条件(如:key1 > 'w')及 ICP 条件(如:key1 > 'w' AND key1 LIKE '%a')同时会传给 index_next 函数。执行查询的过程是以记录为单位的,每读取一条记录判断一遍,处理完了再读取下一条记录继续判断,也就是循环调用存储引擎的 index_next 函数。首先,先给定索引的范围区间并调用 index_read 函数定位到区间的开始位置,返回游标,然后调用 index_next 函数根据索引位置的游标开始读取数据,同样去看看有没有 ICP 条件,如果有,顺带判断一下,判断为 True 就继续执行,为 False 直接去找下一条记录。也就是说如果符合 ICP 条件,就继续回表找到完整记录,然后到 MySQL Server 层再去判断剩余的条件是否满足,如果满足的话发送给客户端。

当开启 ICP 时,它只要是确定了使用某个索引,而且有包含这个索引的搜索条件,就把这个搜索条件(不管这个条件是不是产生范围区间的条件,也就是说条件key1 > 'w'即是区间搜索条件也是 ICP 条件)当作 ICP 的搜索条件交给搜索引擎,我也比较纳闷为啥它不判断一下这些搜索条件是否是产生范围区间的条件,那样存储引擎就不需要多判断一次 ICP 条件了,或许有别的原因吧。那么这样一来,形成范围区间的搜索条件在存储引擎层面是会被显式的判断一遍,同时还会经过 ICP 确认一下,但是在 MySQL Server 层就不继续判断了,Extra 列显示为 Using index condition。

当关闭 ICP 时,虽然还是会使用索引从给定的范围区间中取二级索引记录,但是不会判断任何条件,到最终的 MySQL Server 层再判断一遍,Extra 列就会看见显示 Using where。既然根据某个搜索条件形成了一个范围区间到二级索引中取记录,但是取出记录后在 MySQL Server 层还要继续判断一下这个条件,不太懂作用是什么。不过对某个记录多判断一次给定搜索条件是否成立算不上啥成本,所以没啥关系也~

套用条件提取规则

如果套用何登成分析的 where 条件提取规则对应着我们这个查询中来,其中条件key1 > 'w'就被称之为index key ,而条件key1 LIKE '%a'就被称之为index filter。开启ICP时,MySQL Server 把 index filter 下推到存储引擎层,在存储引擎内部通过索引过滤了条件key1 LIKE '%a',就可以直接过滤掉相关不符合条件的数据,然后根据过滤后的数据进行回表,并发送给 MySQL Server 层,该过程就是减少了回表操作。

如果说我们这个查询还有一个非索引字段的条件nokey1 < 10,那么这个条件就被称之为table filter。也就是说,当存储引擎层做完件index filter,然后根据过滤后的数据进行回表,并把回表数据发送给 MySQL Server 层,接下来就该 MySQL Server 层进行 table filter 处理了,也就是进行nokey1 < 10条件过滤,最终把匹配数据发送给客户端。 当 MySQL Server 层参与条件过滤事,我们在执行计划的 Extra 列就会看见 Using where,如下查询:

三、ICP使用说明

1. 当 SQL 需要回表访问时,ICP 的优化策略可用于 range, ref, eq_ref, ref_or_null 类型的访问方法。

2. 支持 InnoDB 和 MyISAM 表。

3. ICP 只能用于二级索引,不能用于主索引。

4. 并非全部 where 条件都可以用 ICP 筛选,如果 where 条件的字段不在索引列中,还是要读取整表的记录到 server 端做 where 过滤。

5. ICP 的加速效果取决于在存储引擎内通过 ICP 筛选掉的数据的比例。

6. MySQL 5.6 版本的不支持分表的 ICP 功能,5.7版本的开始支持。

7. 当 SQL 使用覆盖索引时,不支持 ICP 优化方法。


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (1)or分享 (0)
关于作者:

您必须 登录 才能发表评论!