MySQL内核技术之“WHERE条件”

本篇文章让我们看看WHERE条件是如何起作用的。有问题请联系我：[e-mail]

先看一下调用链：

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()