数据构建
为了方便说明，我们用一个简单的sql为例子，来研究索引下推。这里贴出建表的sql
CREATE TABLE `tuser` ( `id` int(11) NOT NULL, `id_card` varchar(32) DEFAULT NULL, `name` varchar(32) DEFAULT NULL, `age` int(11) DEFAULT NULL, `ismale` tinyint(1) DEFAULT NULL, PRIMARY KEY (`id`), KEY `id_card` (`id_card`), KEY `name_age` (`name`,`age`) ) ENGINE=InnoDB; INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (1, '1', '张三', 10, 1); INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (2, '2', '张三', 10, 1); INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (3, '3', '张六', 30, 1); INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (4, '4', '张三', 20, 1); INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (5, '5', '李四', 20, NULL); INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (6, '6', '王五', 10, NULL); INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (7, '7', '马六', 34, NULL); INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (8, '8', '朱九', 22, NULL); INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (9, '9', '祝马甸', 27, NULL); INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (10, '10', '毛阿敏', 234, NULL); INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (11, '11', '王仲强', 33, NULL); INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (12, '12', '高高高', 26, NULL); INSERT INTO `tuser`(`id`, `id_card`, `name`, `age`, `ismale`) VALUES (13, '13', '李航', 11, NULL); EXPLAIN select * from tuser where name like '李%' and age=11 and ismale=1;
mysql的源码分析
对索引下推的条件进行检查
其主要源代码在QEP_TAB::push_index_cond中，如下。其中，各个函数主要的作用我写到中文注释里面。
/** Try to extract and push the index condition down to table handler @param join_tab join_tab for table @param keyno Index for which extract and push the condition @param trace_obj trace object where information is to be added */ void QEP_TAB::push_index_cond(const JOIN_TAB *join_tab, uint keyno, Opt_trace_object *trace_obj) { JOIN *const join_ = join(); DBUG_TRACE; ASSERT_BEST_REF_IN_JOIN_ORDER(join_); DBUG_ASSERT(join_tab == join_->best_ref[idx()]); if (join_tab->reversed_access) // @todo: historical limitation, lift it! return; TABLE *const tbl = table(); // Disable ICP for Innodb intrinsic temp table because of performance if (tbl->s->db_type() == innodb_hton && tbl->s->tmp_table != NO_TMP_TABLE && tbl->s->tmp_table != TRANSACTIONAL_TMP_TABLE) return; // TODO: Currently, index on virtual generated column doesn't support ICP if (tbl->vfield && tbl->index_contains_some_virtual_gcol(keyno)) return; /* Fields of other non-const tables aren't allowed in following cases: type is: (JT_ALL | JT_INDEX_SCAN | JT_RANGE | JT_INDEX_MERGE) and BNL is used. and allowed otherwise. */ const bool other_tbls_ok = !((type() == JT_ALL || type() == JT_INDEX_SCAN || type() == JT_RANGE || type() == JT_INDEX_MERGE) && join_tab->use_join_cache() == JOIN_CACHE::ALG_BNL); //********************* 以上代码主要是对查询条件进行检查，对一些特殊的情况，不能下推的，直接返回 *******************// /* 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 index_condition_pushdown switch is on and the use of ICP is not disabled by the NO_ICP hint. 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_iterator_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. 7. The index on virtual generated columns is not supported for ICP. */ if (condition() && tbl->file->index_flags(keyno, 0, true) & HA_DO_INDEX_COND_PUSHDOWN && hint_key_state(join_->thd, table_ref, 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是之前在函数push_idx_cond中根据索引判断出的index键 在mysql的index pushdown中，只有当存在index/primary_key的情况下才能下推 *******************// !(keyno == tbl->s->primary_key && tbl->file->primary_key_is_clustered())) //********************* 这个if条件中，主要对能否进行索引下推进行初步的判断，主要包括： 其存储引擎是否支持index_cond_pushdown 有没有hit，不让下推的 当前命令是不是更新多列或者是删除多列 是不是JT_CONST/JT_SYSTEM 是不是主键 *******************// { DBUG_EXECUTE("where", print_where(join_->thd, condition(), "full cond", QT_ORDINARY);); //打印了当前查询中所有的条件 Item *idx_cond = make_cond_for_index(condition(), tbl, keyno, other_tbls_ok); //这个函数主要是对当前所有的条件进行比较细致的遍历，对可以下推的item标记并返回，不能下推的item不返回 DBUG_EXECUTE("where", print_where(join_->thd, 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 return; } Item *idx_remainder_cond = nullptr; /* 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::skip_index_tuple() functions. */ if (join_tab->use_join_cache() && /* if cache is used then the value is true only for BKA 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(join_->thd, 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(join_->thd, 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); } } }
可以看到，这个函数中，判断当前item能不能下推，最主要的函数就是
Item *idx_cond = make_cond_for_index(condition(), tbl, keyno, other_tbls_ok);
检查cond是否可以下推
/* Get a part of the condition that can be checked using only index fields SYNOPSIS make_cond_for_index() cond The source condition table The table that is partially available keyno The index in the above table. Only fields covered by the index are available other_tbls_ok true <=> Fields of other non-const tables are allowed DESCRIPTION Get a part of the condition that can be checked when for the given table we have values only of fields covered by some index. The condition may refer to other tables, it is assumed that we have values of all of their fields. Example: make_cond_for_index( "cond(t1.field) AND cond(t2.key1) AND cond(t2.non_key) AND cond(t2.key2)", t2, keyno(t2.key1)) will return "cond(t1.field) AND cond(t2.key2)" RETURN Index condition, or NULL if no condition could be inferred. */ //********************* 这里首先传入当前的item，是一个树形结构，以我们上面的sql为例： select * from tuser where name like '李%' and age=11 and ismale=1; 这里因为我们有索引： KEY `name_age` (`name`,`age`) 当前我们的cond，主要有三个item，为： $0 (Item_cond_and *) |--$1 (Item_func_eq *) | |--$2 (Item_field *) field = test.tuser.ismale | `--$3 (Item_int *) value = 1 |--$4 (Item_func_eq *) | |--$5 (Item_field *) field = test.tuser.age | `--$6 (Item_int *) value = 11 `--$7 (Item_func_like *) |--$8 (Item_field *) field = test.tuser.name 其中，Item_cond_and 表示我们当前的where条件是and连接的，连接了三个条件 一个为判断相等的操作，主要对应ismale=1; 第二个也为判断相等的操作，主要对应age=11 第三个是like草嘴，对应name like '李%' *******************// static Item *make_cond_for_index(Item *cond, TABLE *table, uint keyno, bool other_tbls_ok) { DBUG_ASSERT(cond != nullptr); if (cond->type() == Item::COND_ITEM) { //********************* 这里主要是对and/or条件进行判断 对于这些cond，需要对其子条件进行循环的遍历，实际上是一个递归的调用 *******************// uint n_marked = 0; if (((Item_cond *)cond)->functype() == Item_func::COND_AND_FUNC) { //********************* 针对and条件进行判断 *******************// table_map used_tables = 0; Item_cond_and *new_cond = new Item_cond_and; //********************* 申请一个new_cond主要是为了对不符合下推条件的cond删除 *******************// if (!new_cond) return nullptr; List_iterator<Item> li(*((Item_cond *)cond)->argument_list()); //******************* 用一个迭代器遍历当前所有的cond *******************// Item *item; while ((item = li++)) { Item *fix = make_cond_for_index(item, table, keyno, other_tbls_ok); //******************* 递归调用当前函数，看能不能下推 *******************// if (fix) { new_cond->argument_list()->push_back(fix); used_tables |= fix->used_tables(); //******************* 能下推，连在list后面，统计计数+1 *******************// } n_marked += (item->marker == Item::MARKER_ICP_COND_USES_INDEX_ONLY); //******************* 标记当前cond能下推 *******************// } if (n_marked == ((Item_cond *)cond)->argument_list()->elements) //******************* 如果当前所有的cond都能下推，直接标记cond能下推 *******************// cond->marker = Item::MARKER_ICP_COND_USES_INDEX_ONLY; switch (new_cond->argument_list()->elements) { case 0: return nullptr; case 1: new_cond->set_used_tables(used_tables); return new_cond->argument_list()->head(); default: //******************* 如果有两个以上的cond可以下推 设置cond的fixed = 1 设置使用到的table，在本sql中就为1 *******************// new_cond->quick_fix_field(); new_cond->set_used_tables(used_tables); return new_cond; } } else /* It's OR */ { Item_cond_or *new_cond = new Item_cond_or; if (!new_cond) return nullptr; List_iterator<Item> li(*((Item_cond *)cond)->argument_list()); Item *item; while ((item = li++)) { Item *fix = make_cond_for_index(item, table, keyno, other_tbls_ok); if (!fix) return nullptr; //********************* 可以看到，在or条件中，只要有一个子条件不能下推，就下推不了 *******************// new_cond->argument_list()->push_back(fix); n_marked += (item->marker == Item::MARKER_ICP_COND_USES_INDEX_ONLY); } if (n_marked == ((Item_cond *)cond)->argument_list()->elements) cond->marker = Item::MARKER_ICP_COND_USES_INDEX_ONLY; new_cond->quick_fix_field(); new_cond->set_used_tables(cond->used_tables()); new_cond->apply_is_true(); return new_cond; } } //********************* 对于非and/or条件进行判断 *******************// if (!uses_index_fields_only(cond, table, keyno, other_tbls_ok)) { /* Reset marker since it might have the value MARKER_ICP_COND_USES_INDEX_ONLY if this condition is part of the select condition for multiple tables. */ cond->marker = Item::MARKER_NONE; return nullptr; } cond->marker = Item::MARKER_ICP_COND_USES_INDEX_ONLY; return cond; }
对于单个的cond能否下推，主要通过函数uses_index_fields_only进行判断。其源码为：
这个源码注释讲的比较清楚，主要是对cond所在的field是不是索引进行判断，满足对应的条件就返回true表示可以下推。
/** Check if given expression only uses fields covered by index @a keyno in the table tbl. The expression can use any fields in any other tables. The expression is guaranteed not to be AND or OR - those constructs are handled outside of this function. Restrict some function types from being pushed down to storage engine: a) Don't push down the triggered conditions. Nested outer joins execution code may need to evaluate a condition several times (both triggered and untriggered). TODO: Consider cloning the triggered condition and using the copies for: 1. push the first copy down, to have most restrictive index condition possible. 2. Put the second copy into tab->m_condition. b) Stored functions contain a statement that might start new operations (like DML statements) from within the storage engine. This does not work against all SEs. c) Subqueries might contain nested subqueries and involve more tables. TODO: ROY: CHECK THIS d) Do not push down internal functions of type DD_INTERNAL_FUNC. When ICP is enabled, pushing internal functions to storage engine for evaluation will open data-dictionary tables. In InnoDB storage engine this will result in situation like recursive latching of same page by the same thread. To avoid such situation, internal functions of type DD_INTERNAL_FUNC are not pushed to storage engine for evaluation. @param item Expression to check @param tbl The table having the index @param keyno The index number @param other_tbls_ok true <=> Fields of other non-const tables are allowed @return false if No, true if Yes */ bool uses_index_fields_only(Item *item, TABLE *tbl, uint keyno, bool other_tbls_ok) { // Restrictions b and c. if (item->has_stored_program() || item->has_subquery()) return false; // No table fields in const items if (item->const_item()) return true; const Item::Type item_type = item->type(); switch (item_type) { //******************* FUNC_ITEM主要指的是item会做一些函数的操作，比如大于小于等于之类的 *******************// case Item::FUNC_ITEM: { Item_func *item_func = (Item_func *)item; const Item_func::Functype func_type = item_func->functype(); if (func_type == Item_func::TRIG_COND_FUNC || // Restriction a. func_type == Item_func::DD_INTERNAL_FUNC) // Restriction d. return false; /* This is a function, apply condition recursively to arguments */ if (item_func->argument_count() > 0) { Item **item_end = (item_func->arguments()) + item_func->argument_count(); for (Item **child = item_func->arguments(); child != item_end; child++) { //******************* 这里检查当前的条件是够涉及到多个表 如果涉及到多个表的话，会直接返回fslse 这种情况下目前是没办法下推的 *******************// if (!uses_index_fields_only(*child, tbl, keyno, other_tbls_ok)) return false; } } return true; } //******************* cond_item有两类，and 或者是 or *******************// case Item::COND_ITEM: { /* This is a AND/OR condition. Regular AND/OR clauses are handled by make_cond_for_index() which will chop off the part that can be checked with index. This code is for handling non-top-level AND/ORs, e.g. func(x AND y). */ List_iterator<Item> li(*((Item_cond *)item)->argument_list()); Item *cond_item; while ((cond_item = li++)) { if (!uses_index_fields_only(cond_item, tbl, keyno, other_tbls_ok)) return false; } return true; } //******************* field是表的列名，因此这里主要是对具体的列进行判断，主要包括 当前列是否是索引的一部分 是否在当前表内 是否是blob类型或者GEOMETRY类型 *******************// case Item::FIELD_ITEM: { const Item_field *item_field = down_cast<const Item_field *>(item); //******************* 主要看当前查询的item与当前的表是否一致， 如果一致的话：主要判断当前字段是不是主键/是不是blob类型或者GEOMETRY 如果不一致：主要根据other_tbls_ok判断当前表的join操作能否下推 如果当前表是(JT_ALL | JT_INDEX_SCAN | JT_RANGE | JT_INDEX_MERGE) 这几种情况，都不能下推 能下推的主要是：JT_CONST｜JT_EQ_REF｜JT_REF｜JT_FT｜JT_REF_OR_NULL这几种情况 *******************// if (item_field->field->table != tbl) return other_tbls_ok; /* The below is probably a repetition - the first part checks the other two, but let's play it safe: */ return item_field->field->part_of_key.is_set(keyno) && item_field->field->type() != MYSQL_TYPE_GEOMETRY && item_field->field->type() != MYSQL_TYPE_BLOB; } case Item::REF_ITEM: return uses_index_fields_only(item->real_item(), tbl, keyno, other_tbls_ok); default: return false; /* Play it safe, don't push unknown non-const items */ } }
具体的下推操作
上面我们已经分析出了当前可以进行下推的条件。此时，如果当前设置了dbug信息，就可以通过print_where(join_->thd, idx_cond, "idx cond", QT_ORDINARY);打印出可以下推的条件了。
在push_index_cond函数中，我们可以继续研究优化器是如何把条件下推到存储引擎的。这里把push_index_cond的后半部分代码重新贴一下，方便阅读：
DBUG_EXECUTE("where", print_where(join_->thd, idx_cond, "idx cond", QT_ORDINARY);); if (idx_cond) { //******************* 上面的检查，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. */ //******************* 主要检查当前cond所对应的表，是不是确实包含索引 update_used_tables主要是更新一下当前cond对应的表 *******************// idx_cond->update_used_tables(); //******************* 如果cond没有对应的表，就会返回。 *******************// 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 return; } Item *idx_remainder_cond = nullptr; /* 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::skip_index_tuple() functions. */ //******************* 判断是否可以使用BKA缓存 *******************// if (join_tab->use_join_cache() && /* if cache is used then the value is true only for BKA 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_cond_push进行实际的下推工作 *******************// idx_remainder_cond = tbl->file->idx_cond_push(keyno, idx_cond); //******************* 这里存储引擎层会把不能下推的条件返回给server，存储在idx_remainder_cond中 *******************// DBUG_EXECUTE("where", print_where(join_->thd, 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. */ //******************* 如果下推了 做出相应的标记 trace增加pushed_index_condition *******************// 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(join_->thd, row_cond, "remainder cond", QT_ORDINARY);); //******************* 如果现在还有下推后的条件 *******************// if (row_cond) { //******************* idx_remainder_cond里面是存储引擎层会返回给server的item 把所有不能下推的条件返回 因此需要把这些条件再加回去 *******************// 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); } } }
在idx_cond_push函数，其在innodb引擎的源码如下，实际上已经把filter条件下推到了innodb引擎中。
/** Attempt to push down an index condition. @param[in] keyno MySQL key number @param[in] idx_cond Index condition to be checked @return Part of idx_cond which the handler will not evaluate */ class Item *ha_innobase::idx_cond_push(uint keyno, class Item *idx_cond) { DBUG_TRACE; DBUG_ASSERT(keyno != MAX_KEY); DBUG_ASSERT(idx_cond != nullptr); pushed_idx_cond = idx_cond; pushed_idx_cond_keyno = keyno; in_range_check_pushed_down = TRUE; /* We will evaluate the condition entirely */ //******************* innodb引擎都可以下推，直接返回nullptr *******************// return nullptr; }
随后的函数make_cond_remainder，输入的是原始的cond，其源码如下，主要作用就是把之前下推的条件删掉。
static Item *make_cond_remainder(Item *cond, bool exclude_index) { if (exclude_index && cond->marker == Item::MARKER_ICP_COND_USES_INDEX_ONLY) return 0; /* Already checked */ if (cond->type() == Item::COND_ITEM) { table_map tbl_map = 0; //******************* 针对and条件 *******************// if (((Item_cond *)cond)->functype() == Item_func::COND_AND_FUNC) { /* Create new top level AND item */ Item_cond_and *new_cond = new Item_cond_and; if (!new_cond) return (Item *)0; List_iterator<Item> li(*((Item_cond *)cond)->argument_list()); Item *item; while ((item = li++)) { //******************* 递归调用当前函数，检查是否标记为cond->marker == Item::MARKER_ICP_COND_USES_INDEX_ONLY 确定item是否可以下推 *******************// Item *fix = make_cond_remainder(item, exclude_index); if (fix) { new_cond->argument_list()->push_back(fix); tbl_map |= fix->used_tables(); } } switch (new_cond->argument_list()->elements) { case 0: return (Item *)0; case 1: return new_cond->argument_list()->head(); default: new_cond->quick_fix_field(); new_cond->set_used_tables(tbl_map); return new_cond; } } else /* It's OR */ { Item_cond_or *new_cond = new Item_cond_or; if (!new_cond) return (Item *)0; List_iterator<Item> li(*((Item_cond *)cond)->argument_list()); Item *item; while ((item = li++)) { Item *fix = make_cond_remainder(item, false); if (!fix) return (Item *)0; new_cond->argument_list()->push_back(fix); tbl_map |= fix->used_tables(); } new_cond->quick_fix_field(); new_cond->set_used_tables(tbl_map); new_cond->apply_is_true(); return new_cond; } } return cond; }
总结
可以看到，整个下推流程的代码，在mysql8.0版本上还是非常清晰的，可以下推的条件都会通过handler的函数idx_cond_push存储到引擎上，并且在优化器上删除对应的函数。这样后续优化器执行的时候，就可以利用对应的条件，筛除掉一部分数据，以减轻server层的负担。
可以看到在mysql源码中，只能针对二级索引进行下推，实际上我觉得这个限制没有意义，condition只要能在索引中覆盖，应该就可以下推，至少还可以减少函数调用的开销。
