mysql表连接有先后顺序_MySQL确定JOIN表顺序

1 目的

了解MySQL是如何确定表的JOIN顺序的。

2 背景

MySQL在确定表的JOIN顺序前，会确定各表的行数(针对InnoDB而言，行数只是一个估算；针对MyISAM而言，行数是一个精确值)，还有方问成本。这些是准备工作。下面进入源码分析。

3 源码分析

3.1 主流程

JOIN::make_join_plan

Optimize_table_order::choose_table_order

merge_sort

Optimize_table_order::greedy_search

Optimize_table_order::best_extension_by_limited_search

Optimize_table_order::consider_plan

3.2 make_join_plan

make_join_plan 函数用于生成join计划，确定表的连接顺序。

bool JOIN::make_join_plan()

{

if (!(select_lex->active_options() & OPTION_NO_CONST_TABLES))

{

if (extract_const_tables())

DBUG_RETURN(true);

}

if (Optimize_table_order(thd, this, NULL).choose_table_order())

DBUG_RETURN(true);

DBUG_RETURN(false);

}

extract_const_tables 函数确定const table(表行数小于等于1)。MyISAM表有设置HA_STATS_RECORDS_IS_EXACT，而InnoDB表没有设置此标志。因此当MyISAM表行数小于等于1时，为const table。InnoDB表使用唯一索引查询时，应该为const table。const table会被确定在连接顺序靠前的位置，以此加快访问速度。

处理完const table后，继而调用choose_table_order 函数。

3.3 choose_table_order

bool Optimize_table_order::choose_table_order()

{

merge_sort(join->best_ref + join->const_tables,

join->best_ref + join->tables,

Join_tab_compare_default());

if (greedy_search(join_tables))

DBUG_RETURN(true);

DBUG_RETURN(false);

}

choose_table_order 首先对table进行排序。Join_tab_compare_default 利用依赖关赖，行数等因素比较两个表的先后顺序。行数小的表排前面，行数大的表排后面。

确定表顺序后，继而调用greedy_search进行贪婪搜索。

3.4 greedy_search

bool Optimize_table_order::greedy_search(table_map remaining_tables)

{

// 确定余下表的数量 const uint n_tables= my_count_bits(remaining_tables);

uint size_remain= n_tables;

do {

// 每次循环都初始化best_read为最大值 join->best_read= DBL_MAX;

join->best_rowcount= HA_POS_ERROR;

// 调用best_extension_by_limited_search，搜索深度为search_depth。 if (best_extension_by_limited_search(remaining_tables, idx, search_depth))

DBUG_RETURN(true);

if (size_remain <= search_depth)

{

// 余下的表都完成搜索，可以退出了。 DBUG_RETURN(false);

}

// size_remain > search_depth // 只能确定idx位置的表为最优表，大于idx的表还要再进行搜索 best_pos= join->best_positions[idx];

best_table= best_pos.table;

join->positions[idx]= best_pos;

bool is_interleave_error MY_ATTRIBUTE((unused))=

check_interleaving_with_nj (best_table);

best_idx= idx;

JOIN_TAB *pos= join->best_ref[best_idx];

while (pos && best_table != pos)

pos= join->best_ref[++best_idx];

memmove(join->best_ref + idx + 1, join->best_ref + idx,

sizeof(JOIN_TAB*) * (best_idx - idx));

join->best_ref[idx]= best_table;

remaining_tables&= ~(best_table->table_ref->map());

// 剩余表数减1 --size_remain;

++idx;

} while (true);

}

greedy_search中循环调用best_extension_by_limited_search 。这两个函数比较复杂。可以结合后续的实验了解。

3.5 best_extension_by_limited_search

bool Optimize_table_order::best_extension_by_limited_search(

table_map remaining_tables,

uint idx,

uint current_search_depth)

{

// best_rowcount, best_cost用于保存位于索引(idx)的表的最优值。 double best_rowcount= DBL_MAX;

double best_cost= DBL_MAX;

JOIN_TAB *saved_refs[MAX_TABLES];

memcpy(saved_refs, join->best_ref + idx,

sizeof(JOIN_TAB*) * (join->tables - idx));

for (JOIN_TAB **pos= join->best_ref + idx; *pos; pos++)

{

JOIN_TAB *const s= *pos;

const table_map real_table_bit= s->table_ref->map();

// 交换idx与pos的值，虽然idx值不变，但idx指向的表却是最新的表 swap_variables(JOIN_TAB*, join->best_ref[idx], *pos);

if ((remaining_tables & real_table_bit) &&

!(eq_ref_extended & real_table_bit) &&

!(remaining_tables & s->dependent) &&

(!idx || !check_interleaving_with_nj(s)))

{

POSITION *const position= join->positions + idx;

best_access_path(s, remaining_tables, idx, false,

idx ? (position-1)->prefix_rowcount : 1.0,

position);

// 设置cost position->set_prefix_join_cost(idx, cost_model);

if (position->prefix_cost >= join->best_read && found_plan_with_allowed_sj)

{

// prefix_cost大于等于bes