一、SQL的where条件提取规则
在说 ICP（Index Condition Pushdown，索引条件下推） 特性之前，必须先搞明白根据何登成大神总结出一套放置于所有 SQL 语句而皆准的 where 查询条件的提取规则，为 ICP 提供理论支持。详情请看：SQL语句中where条件，在数据库中提取与应用浅析。
二、Index Condition Pushdown
Index Condition Pushdown（ICP，索引条件下推）是 MySQL 5.6 版本中的新特性，是一种在 InnoDB 存储引擎层根据二级索引过滤 where 条件从而达到减少回表次数的一种优化方式。简单的来说就是将本该在 MySQL Server 层进行过滤的条件下推到 InnoDB 引擎层去做。但是这种策略和我们平时说的使用到了索引实际上是不同的，我们平时说的用到了索引一般指的是使用到了索引进行定位和访问，但是这里却是一种过滤操作。
主要针对于有些搜索条件中虽然出现在了索引列，但却不能使用到索引，比如下边这个查询：
|
1 |
SELECT * FROM s1 WHERE key1 > <span class="hljs-string">'w'</span> AND key1 LIKE <span class="hljs-string">'%a'</span>; |
其中的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）。
除了上面这种常见的查询，还有下面这种也比较常见：
|
1 |
SELECT * FROM s1 WHERE key1 > 'w' AND key1 < 'm'; |
根据 key1 > 'w' 做索引定位查找，然后根据条件 key1 < 'm' 下推做额外过滤。
对于 InnoDB 而言只有数据定位的能力，而没有判断结束的能力。除非使用 ICP，这是范围访问结束判定下推到引擎层做的。简单来说，对于范围扫描而言，定位后，无法进行记录和查询元祖的比较（没有查询元祖）。但是对于范围扫描来说会下推，直到 key1 < 'm' 这个条件不满足，会超出范围，会停止扫描。
为了更好地理解上面这句话，看一个示例，比如现在 s1 表有组合索引 idx_k1_k2(key1, key2)，查询条件如下：
|
1 |
SELECT * FROM s1 WHERE key1 = 'a' AND key2 > 'm'; |
其中的key1 = 'a' AND key2 > 'm'可以使用到组合索引 idx_k1_k2，在没有 ICP 之前，MySQL Server 与 InnoDB 的交互过程如下：
- 先根据
key1 > 'w'这个条件，形成一个范围区间后从二级索引 idx_k1_k2 中获取到对应的二级索引记录。 - 根据上一步骤得到的二级索引记录中的主键值进行回表，找到完整的用户记录返回给 MySQL Server 层。
- MySQL Server 层再检测该记录是否符合
key2 > 'm'这个条件，将符合条件的记录返回给用户。 - MySQL Server 层接着继续调用 InnoDB 访问下一条数据，InnoDB 继续扫描数据返回到 MySQL Server 层，MySQL Server 进行判断是否符合过滤条件。
- 就这样，MySQL Server 与 InnoDB 一直循环这个操作，直到 InnoDB 返回的数据不符合
key2 > 'm'条件时，才停止调用 InnoDB。
上面这个过程就是 InnoDB 只有数据定位的能力，而没有判断结束的能力的体现。数据停止扫描是由 MySQL Server 层来判断的。有了 ICP 能力之后呢？
- 先根据
key1 = 'a' AND key2 > 'm'这个条件，定位到二级索引 idx_k1_k2 中对应的二级索引记录。 - 对于指定的二级索引记录，先不着急回表，而是先检测一下该记录是否满足
key2 > 'm'这个条件，如果这个条件满足，则执行回表操作。并把结果返回给 MySQL Server 层。MySQL Server 层接收到数据后还会再进行一次条件过滤。 - 当 InnoDB 检测到有不符合
key2 > 'm'的条件时，就会终止扫描，也就有了数据结束的能力。
通过拿组合索引来对比这两个数据访问交互流程，可以看出有没有 ICP 的区别，虽然在组合索引的案例上面两者数据扫描的数量几乎都是一样的，但是数据结束的控制者则不同，一个是 MySQL Server 层，一个是 InnoDB 层。
Note
MySQL Server 与 InnoDB 的交互方式，可以参考一下 HANDLE 语句。
如果在查询语句的执行过程中将要使用索引条件下推这个特性，在 Extra 列中将会显示 Using index condition，比如这样：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'w' AND key1 LIKE '%a'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 7692 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) |
另外，下面这种查询也可能会看见 Extra 列显示 Using index condition：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'w'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 7692 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) |
对于这种查询，理论上形成一个范围区间的搜索条件后从二级索引 idx_key1 直接就可以获取到对应的二级索引记录，然后回表取数据，应该不涉及到 ICP 啊，但确实显示了 Using index condition。在代码中，只要不是等于条件匹配，都需要经过 ICP 判断，也就是说key1 > 'w'在存储引擎层面也过滤了一次（虽然是多余的）。
另外，在 MySQL 中也是可以手动关闭 ICP 的，在 optimizer_switch 变量中记录了 MySQL 所有相关特性，都可以可以选择开启或关闭。比如下面关闭 ICP ：set optimizer_switch='index_condition_pushdown=off'。当我们关闭 ICP 后，同样的查询，我们看一下 Extra 列显示什么：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'w'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 7692 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) |
可以看到，显示了 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'）及 ICP 条件，然后设置到有关存储引擎的一个数据结构中去，然后调用存储引擎提供的一些查找数据的接口，比如最常用的几个如下：
- ha_innobase::index_read，这个函数是访问索引的时候定位到值所在的位置用到的函数，因为必须要知道读取索引的开始位置才能向下访问。
- ha_innobase::index_next(uchar* buf)，这个函数是从游标中读取索引的下一条数据用到的函数，该游标必须先使用 index_read 定位到。但也不是所有的从游标中读取索引下一条数据都会使用这个函数，比如当访问路径为 range（范围查询） 或 index（索引扫描） 时才使用这个函数，
- ha_innobase::index_next_same(uchar* buf)，这个函数是读取与作为参数给出的键值匹配的下一行，就是 ref（等值查询） 访问方式会使用这个函数。可以看出跟 index_next 函数一样虽然都是读数据，但是两者读取数据的方式是不同的。
Note
这里就以我们上面的执行计划 range 查询方式为例子，MySQL Server 层调用 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 层还要继续判断一下这个条件，不太懂作用是什么。不过对某个记录多判断一次给定搜索条件是否成立算不上啥成本，所以没啥关系也～
另外，对于索引条件的 in 或者 or 的查询，也都会用到 ICP。而下推的条件正是 in 或者 or 条件。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> desc select * from t1 where c2 in (1,2); +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t1 | NULL | range | c2 | c2 | 5 | NULL | 2 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> desc select * from t1 where c2 = 1 or c2 =2; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t1 | NULL | range | c2 | c2 | 5 | NULL | 2 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) |
可以看到并不是只有在多列索引中才会用到 ICP 特性。这种索引下推对性能有很大的提升吗？有待考证。
套用条件提取规则
如果套用何登成分析的 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，如下查询：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'w' AND key1 LIKE '%a' AND nokey1 > '10'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 7692 | 33.33 | Using index condition; Using where | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ 1 row in set, 1 warning (0.00 sec) |
下面简单对比下有无 ICP 时，数据检索过程的区别。
关闭 ICP 时如下：
- 读取二级索引元组，然后使用索引元组中的主键去聚集索引上定位整行数据并返回给 MySQL Server 层
- MySQL Server 拿其他的 where 条件来看此行数据是否满足，满足的话保留，不满足的话丢弃
打开 ICP 时如下：
- 获取二级索引元组
- 检测此二级索引元组是否满足下推的条件，如果不满足，读取下一条二级索引元组
- 如果满足，通过此二级索引元组到主键索引中定位读取整行数据并返回给 MySQL Server 层
- MySQL Server 层然后再检测此行数据是否满足其他未能下推的条件，如果满足保留，如果不满足，丢弃
三、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 优化方法。
四、代码控制
关于 ICP 的流程控制大体上分为两部分，一是 server 层判断是否可以进行 ICP，并且确定 ICP 的条件，传递给存储引擎；二是存储引擎去真正的执行 ICP 的过程。
server 层处理
server 层的处理在 sql 优化阶段，入口函数为QEP_TAB::push_index_cond(JOIN_TAB const*, unsigned int, Opt_trace_object*) sql_select.cc:1769，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 |
if (condition() && tbl->file->index_flags(keyno, 0, 1) & HA_DO_INDEX_COND_PUSHDOWN && hint_key_state(join_->thd, tbl, keyno, ICP_HINT_ENUM, OPTIMIZER_SWITCH_INDEX_CONDITION_PUSHDOWN) && join_->thd->lex->sql_command != SQLCOM_UPDATE_MULTI && join_->thd->lex->sql_command != SQLCOM_DELETE_MULTI && !has_guarded_conds() && type() != JT_CONST && type() != JT_SYSTEM && !(keyno == tbl->s->primary_key && tbl->file->primary_key_is_clustered())) // 判断是否满足ICP的各种前置条件 { DBUG_EXECUTE("where", print_where(condition(), "full cond", QT_ORDINARY);); Item *idx_cond= make_cond_for_index(condition(), tbl, keyno, other_tbls_ok); // 生成下推条件 DBUG_EXECUTE("where", print_where(idx_cond, "idx cond", QT_ORDINARY);); if (idx_cond) { /* Check that the condition to push actually contains fields from the index. Without any fields from the index it is unlikely that it will filter out any records since the conditions on fields from other tables in most cases have already been evaluated. */ idx_cond->update_used_tables(); if ((idx_cond->used_tables() & table_ref->map()) == 0) { /* The following assert is to check that we only skip pushing the index condition for the following situations: 1. We actually are allowed to generate an index condition on another table. 2. The index condition is a constant item. 3. The index condition contains an updatable user variable (test this by checking that the RAND_TABLE_BIT is set). */ DBUG_ASSERT(other_tbls_ok || // 1 idx_cond->const_item() || // 2 (idx_cond->used_tables() & RAND_TABLE_BIT) ); // 3 DBUG_VOID_RETURN; } Item *idx_remainder_cond= 0; /* For BKA cache we store condition to special BKA cache field because evaluation of the condition requires additional operations before the evaluation. This condition is used in JOIN_CACHE_BKA[_UNIQUE]::skip_index_tuple() functions. */ if (join_tab->use_join_cache() && /* if cache is used then the value is TRUE only for BKA[_UNIQUE] cache (see setup_join_buffering() func). In this case other_tbls_ok is an equivalent of cache->is_key_access(). */ other_tbls_ok && (idx_cond->used_tables() & ~(table_ref->map() | join_->const_table_map))) { cache_idx_cond= idx_cond; trace_obj->add("pushed_to_BKA", true); } else { idx_remainder_cond= tbl->file->idx_cond_push(keyno, idx_cond); DBUG_EXECUTE("where", print_where(tbl->file->pushed_idx_cond, "icp cond", QT_ORDINARY);); } /* Disable eq_ref's "lookup cache" if we've pushed down an index condition. TODO: This check happens to work on current ICP implementations, but there may exist a compliant implementation that will not work correctly with it. Sort this out when we stabilize the condition pushdown APIs. */ if (idx_remainder_cond != idx_cond) { ref().disable_cache= TRUE; trace_obj->add("pushed_index_condition", idx_cond); } Item *row_cond= make_cond_remainder(condition(), TRUE); DBUG_EXECUTE("where", print_where(row_cond, "remainder cond", QT_ORDINARY);); if (row_cond) { if (idx_remainder_cond) and_conditions(&row_cond, idx_remainder_cond); idx_remainder_cond= row_cond; } set_condition(idx_remainder_cond); trace_obj->add("table_condition_attached", idx_remainder_cond); } } |
engine 层处理
负责处理 ICP 逻辑在函数row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long) row0sel.cc:5723，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
switch (row_search_idx_cond_check(buf, prebuilt, rec, offsets)) { // 进行ICP访问控制，通常是这里进行控制 case ICP_NO_MATCH: if (did_semi_consistent_read) { row_unlock_for_mysql(prebuilt, TRUE); // ICP不命中，直接解锁 } goto next_rec; // 不匹配直接下一行 case ICP_OUT_OF_RANGE: err = DB_RECORD_NOT_FOUND; goto idx_cond_failed; case ICP_MATCH: break; } |
其中 row_search_idx_cond_check 来判断索引元组是否匹配下推的条件。
另外，如果 ICP 不匹配的行也就不会记录到慢日志 Rows_examined 字段了，就是扫描行不记录 ICP 没有匹配到的数据，这一点需要注意。
<参考>
https://www.jianshu.com/p/8c3a45ff27be
https://blog.csdn.net/sun_ashe/article/details/83506649
