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_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));
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,
mysql下载后的初次使用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⼤于等于best_read时，会放弃当前的表。 continue;
}
if (prune_level == 1)
{
// 打开了优化。 if (best_rowcount > position->prefix_rowcount ||
best_cost > position->prefix_cost ||
(idx == join->const_tables && // 's' is the first table in the QEP s->table() == join->sort_by_table))
{
if (best_rowcount >= position->prefix_rowcount &&
best_cost >= position->prefix_cost &&
/* TODO: What is the reasoning behind this condition? */
(!(s->key_dependent & remaining_tables) ||
position->rows_fetched < 2.0))
{
best_rowcount= position->prefix_rowcount;
best_cost= position->prefix_cost;
}
}
else if (found_plan_with_allowed_sj)
{
// 当前的成本虽然⼩于best_read，但当前表并不是最优的(例如当前表的⾏数⽐较⼤)，因此放弃当前表 continue; }
}
const table_map remaining_tables_after=
(remaining_tables & ~real_table_bit);
if ((current_search_depth > 1) && remaining_tables_after)
{
// 递归搜索 if (best_extension_by_limited_search(remaining_tables_after,
idx + 1,
current_search_depth - 1))
DBUG_RETURN(true);
}
else
// 确定JOIN顺序 consider_plan(idx, &trace_one_table);
}
}
}
done:
// Restore previous #rows sorted best_ref[] memcpy(join->best_ref + idx, saved_refs,
sizeof(JOIN_TAB*) * (join->tables-idx));
DBUG_RETURN(false);
}
3.6 consider_plan
void Optimize_table_order::consider_plan(uint idx,
Opt_trace_object *trace_obj)
{
// 当前⽅问顺序的成本为cost。 double cost= join->positions[idx].prefix_cost;
// 当0~idx表复制到best_position中 memcpy((uchar*) join->best_positions, (uchar*) join->positions, sizeof(POSITION) * (idx + 1));
// 保存当前best_read为cost - 0.001 join->best_read= cost - 0.001;
}
consider_plan就⽐较简单了，确定最终表的JOIN顺序
4 实验
4.1 创建表
创建四个表
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`msg` char(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`msg` char(32) DEFAULT NULL,
`t1_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。