本文简单记录下和Index Condition Pushdown相关的代码路径
涉及的代码只包含InnoDB层
当MySQL使用索引进行数据检索时，不可用于在Innodb进行索引检索的WHERE条件，也可以下推到Innodb层，以减少回表查询的数据量
#对于innodb表，ICP只应用于二级索引
#在MySQL5.6里还不支持对分区表ICP（5.7支持）
MySQL版本：5.7.5
1.创建测试表
create table t1 (a int auto_increment primary key, b int, c int, d int, key(b,c));
生成数据：
insert into t1(b,c,d) select rand()*100, rand()*1000, rand()*10000;
insert into t1(b,c,d) select rand()*100, rand()*1000, rand()*10000 from t1;
insert into t1(b,c,d) select rand()*100, rand()*1000, rand()*10000 from t1;
……
root@sb1 01:57:06>explain select * from t1 where b = 90 and c > 500\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: b
key: b
key_len: 10
ref: NULL
rows: 168
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
Tips: 从MySQL5.7.3开始，explain的输出增加了两列：partitions用户分区表的查询优化的分区选择，filtered表示根据当前查询计划的过滤性百分比，计算方式参考函数Explain_join::explain_rows_and_filtered()
1.optimizer确认是否可以ICP
堆栈：
handle_select –> mysql_select –> mysql_prepare_and_optimize_select
–> JOIN::optimize –> make_join_readinfo
–> QEP_TAB::push_index_cond
–> ha_innobase::idx_cond_push // 初始化icp的条件pushed_idx_cond 和使用到的索引号pushed_idx_cond_keyno，设置标记in_range_check_pushed_down为true
根据函数QEP_TAB::push_index_cond的注释，只有满足如下条件时，才会使用ICP：
/*
We will only attempt to push down an index condition when the
following criteria are true:
0. The table has a select condition
1. The storage engine supports ICP.
2. The system variable for enabling ICP is ON.
3. The query is not a multi-table update or delete statement. The reason
for this requirement is that the same handler will be used
both for doing the select/join and the update. The pushed index
condition might then also be applied by the storage engine
when doing the update part and result in either not finding
the record to update or updating the wrong record.
4. The JOIN_TAB is not part of a subquery that has guarded conditions
that can be turned on or off during execution of a ‘Full scan on NULL
key’.
@see Item_in_optimizer::val_int()
@see subselect_single_select_engine::exec()
@see TABLE_REF::cond_guards
@see setup_join_buffering
5. The join type is not CONST or SYSTEM. The reason for excluding
these join types, is that these are optimized to only read the
record once from the storage engine and later re-use it. In a
join where a pushed index condition evaluates fields from
tables earlier in the join sequence, the pushed condition would
only be evaluated the first time the record value was needed.
6. The index is not a clustered index. The performance improvement
of pushing an index condition on a clustered key is much lower
than on a non-clustered key. This restriction should be
re-evaluated when WL#6061 is implemented.
*/
ha_innobase::build_template
// 初始化prebuilt的idx_cond, 如果使用ICP，指向ha_innobase对象，否则为NULL，后续根据该列是否为NULL来判定
// 初始化prebuilt->idx_cond_n_cols，即使用到的索引列条件列数
2. 检查ICP条件
当从二级索引检索到数据时，需要根据icp条件进行记录过滤
堆栈：
ha_innobase::index_read
|–>row_search_mvcc
|–>row_search_idx_cond_check //当读取到一条合法的二级索引记录后，需要进行下推条件检查
// 将读取到的二级索引记录上，作为condition的列值转换为MySQL Format
// innobase_index_cond：判断是否满足索引条件
// 当满足索引条件时，如果无需回聚集索引查询，则将剩余的索引记录直接转换成MySQL Format
|–> row_sel_get_clust_rec_for_mysql //当找到满足条件的二级索引记录时，再根据其查询对应的聚集索引记录