資料建構
為了友善說明，我們用一個簡單的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隻要能在索引中覆寫，應該就可以下推，至少還可以減少函數調用的開銷。
![SQL優化SQL語句優化的目的[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)