本篇文章讓我們看看WHERE條件是如何起作用的。有問題請聯絡我：zhangtiey@gmail.com
先看一下呼叫鏈：
JOIN::optimize()-->
make_join_select()-->
JOIN_TAB::set_condition()-->
這裡就把condition賦值給了JOIN_TAB。那麼condition是如何產生的呢？是在parse一個query的時候，具體的是yacc產生的程式碼，不用特意關心。舉例來說的，WHERE S1>3 AND S1<5 這個條件在parse的時候就會new Item_cond_and呼叫下面的建構函式： Item_cond_and(const POS &pos, Item i1, Item i2) :Item_cond(pos, i1, i2) {}
這裡的pos帶了一個char內容就是S1>3 AND S1<5，Item1是S1>3，Item2是S1<5。這裡，Item1實際上是Item_func_gt物件，Item2是Item_func_lt物件。Item_cond_and是從Item_func派生出來的，Item_func帶了一個Item* args成員變數，Item1和Item2就賦給了args。同時會把Item_func的arg_count置為2.
在make_join_select中，
for (uint i=join->const_tables ; i < join->tables ; i++)
{
JOIN_TAB *const tab= join->best_ref[i];
if (!tab->position())
continue;
/*
first_inner is the X in queries like:
SELECT * FROM t1 LEFT OUTER JOIN (t2 JOIN t3) ON X
*/
const plan_idx first_inner= tab->first_inner();
const table_map used_tables= tab->prefix_tables();
const table_map current_map= tab->added_tables();
Item *tmp= NULL;
if (cond)
tmp= make_cond_for_table(cond,used_tables,current_map, 0);
這裡的
if (cond)
tmp= make_cond_for_table(cond,used_tables,current_map, 0);是起作用的。在make_cond_for_table內部，是下面這個迴圈真正起作用：
List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
Item *item;
while ((item= li++))
{
Item *fix= make_cond_for_table_from_pred(root_cond, item,
tables, used_table,
exclude_expensive_cond);
if (fix)
new_cond->argument_list()->push_back(fix);
}它遍歷當前的condition的Item，然後生成了新的Item fix，第一次生成了Item_func_gt，第二次生成了Item_func_lt，也就是說這裡生成的new_cond和傳入的是一樣的。之後在make_join_select裡把tmp設定進去：
*/
if (cond && tmp)
{
/*
Because of QUICK_GROUP_MIN_MAX_SELECT there may be a select without
a cond, so neutralize the hack above.
*/
if (!(tmp= add_found_match_trig_cond(join, first_inner, tmp, NO_PLAN_IDX)))
DBUG_RETURN(true);
tab->set_condition(tmp);
之後再push一個新的condition到底層儲存引擎：
/* Push condition to storage engine if this is enabled
and the condition is not guarded */
if (thd->optimizer_switch_flag(OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN) &&
first_inner == NO_PLAN_IDX)
{
Item *push_cond=
make_cond_for_table(tmp, tab->table_ref->map(),
tab->table_ref->map(), 0);
if (push_cond)
{
/* Push condition to handler */
if (!tab->table()->file->cond_push(push_cond))
tab->table()->file->pushed_cond= push_cond;
}
}
之後在又做了一些key相關的：
if (!tab->table()->quick_keys.is_subset(tab->checked_keys) ||
!tab->needed_reg.is_subset(tab->checked_keys))
{
tab->keys().merge(tab->table()->quick_keys);
tab->keys().merge(tab->needed_reg);
....
else
tab->use_quick= QS_RANGE;
}
...
if (join->attach_join_conditions(i))
使用cmp函式（item_cmpfunc.cc）：
int Arg_comparator::compare_int_signed()
呼叫longlong val1= (*a)->val_int(); 實際上是longlong Item_field::val_int() -->
Field_long::val_int(void)
Item_field:
Field *field;
field如何被賦值的？是被賦值為Field_long型，裡面的ptr是如何被賦值的。
這裡要提到的是bit
是在
select_lex->prepare()-->
select_lex->setup_cond()
fix_field來呼叫
find_field_in_tables-->
find_field_in_table_ref-->
fld->table->mark_column_used(thd, fld, thd->mark_used_columns)-->
TABLE::mark_column_used-->
case MARK_COLUMNS_READ:
bitmap_set_bit(read_set, field->field_index);
/*
- For parallel execution, create JOINs. These JOINs will be used during join->exec
*/
if (join->select_lex->m_parallel) {
join->create_parallel_joins(thd, this);-->open_table_from_share-->
bitmap_init
關於bitset的
/**
Add field into table read set.
@param field field to be added to the table read set.
*/
static void update_table_read_set(Field *field)
{
TABLE *table= field->table;
if (!bitmap_fast_test_and_set(table->read_set, field->field_index))
table->covering_keys.intersect(field->part_of_key);
}
opt_sum.cc:
opt_sum_query() {
...
/*
Necessary columns to read from the index have been determined by
find_key_for_maxmin(); they are the columns involved in
'WHERE col=const' and the aggregated one.
We may not need all columns of read_set, neither all columns of
the index.
*/
DBUG_ASSERT(table->read_set == &table->def_read_set);
DBUG_ASSERT(bitmap_is_clear_all(&table->tmp_set));
table->read_set= &table->tmp_set;
table->mark_columns_used_by_index_no_reset(ref.key, table->read_set,
ref.key_parts);
// The aggregated column may or not be included in ref.key_parts.
bitmap_set_bit(table->read_set, item_field->field->field_index);
}
這裡的可能有用：
static bool init_fields(THD *thd, TABLE_LIST *tables,
struct st_find_field *find_fields, uint count)
{
Name_resolution_context *context= &thd->lex->select_lex->context;
DBUG_ENTER("init_fields");
context->resolve_in_table_list_only(tables);
for (; count-- ; find_fields++)
{
/* We have to use 'new' here as field will be re_linked on free */
Item_field *field= new Item_field(context,
"mysql", find_fields->table_name,
find_fields->field_name);
if (!(find_fields->field= find_field_in_tables(thd, field, tables, NULL,
0, REPORT_ALL_ERRORS,
false, // No priv checking
true)))
DBUG_RETURN(1);
bitmap_set_bit(find_fields->field->table->read_set,
find_fields->field->field_index);
/* To make life easier when setting values in keys */
bitmap_set_bit(find_fields->field->table->write_set,
find_fields->field->field_index);
}
DBUG_RETURN(0);
}
在sql_join_buffer.cc有兩處bitmap_intersect和一處bitmap_copy
bitmap_intersect(table->read_set, &range_read_set);
改造
longlong Item_func_lt::val_int()
{
DBUG_ASSERT(fixed == 1);
int value= cmp.compare();
return value < 0 && !null_value ? 1 : 0;
}呼叫關係：
cmp.compare()-->
Arg_comparator::compare() { return (this->*func)(); }-->
int Arg_comparator::compare_int_signed()
{ ...
longlong val1= (*a)->val_int();
這裡的呼叫：
(*a)->val_int()-->
Item_field::val_int()