本文深入探讨MySQL如何确定JOIN操作中表的连接顺序，通过源码分析，包括make_join_plan、choose_table_order、greedy_search和best_extension_by_limited_search等关键步骤，揭示了MySQL如何基于行数、依赖关系等因素进行表顺序优化，以提高查询效率。
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大于等于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
CREATE TABLE `t3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`msg` char(32) DEFAULT NULL,
`t2_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
CREATE TABLE `t4` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`msg` char(32) DEFAULT NULL,
`t3_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
插入数据
insert into t1 select 1, "aaa";
insert into t2 select 1, "t2", 1;
insert into t2 select 2, "t2", 2;
insert into t3 select 1, "t3", 1;
insert into t3 select 2, "t3", 2;
insert into t3 select 3, "t3", 3;
insert into t4 select 1, "t4", 1;
insert into t4 select 2, "t4", 2;
insert into t4 select 3, "t4", 3;
insert into t4 select 4, "t4", 4;
4.2 查询语句
set @@optimizer_search_depth=3;
select * from t1, t2, t3, t4 where t2.t1_id = t1.id and t3.t2_id = t2.id and t4.t3_id = t3.id;
optimizer_search_depth设置搜索深度为3 ，默认为62 。
4.3 结果分析
在sql/http://sql_planner.cc中添加了一些日志，用于记录确定JOIN顺序过程中的一些值和状态。
// 开始调用 greedy_search，搜索深度为3.由于SELECT查询了4个表，因此do循环会执行2次。
call greedy_search, search_depth: 3
// call_idx:1 第一轮迭代，此次只确定顺序中的第一个表是哪个表，是t1, t2, t3, 还是t4
call_idx: 1, idx: 0, size_remain: 4, search_depth: 3
// 在greedy_search函数中，第1次调用best_extension_by_limited_search
begin call best_extension_by_limited_search
enter best_extension_by_limited_search idx: 0, current_search_depth: 3
// 根据t2, t1, t4, t3的顺序，开始处理t2
process table name: t2
// t2的prefix_const为 1.4, best_read为最大值 2e308
prefix_const: 1.400000, best_read: 2e308
// prefix_const < best_read，有优化空间，不舍弃t2.
not give up table name: t2
// prune_level == 1,表示打开启发优化
prune_level == 1
// 满足优化条件
condition: 1, (best_rowcount: 2e308, position->prefix_rowcount: 2), (best_cost: 2e308, position->prefix_cost: 1.4), (idx: 0, join->const_tables: 0, s->table(): 0x7f7ad401e480, join->sort_by_table: 0x0)
// 但不更新
condition: 0, s->key_dependent:4, remaining_tables:15, position->rows_fetched: 2
// 递归调用
enter best_extension_by_limited_search idx: 1, current_search_depth: 2
// 这次处理 t1
process table name: t1
// prefix_const为2.8, best_read由于是栈内变量，因此初次进入时，best_read都为2e308
prefix_const: 2.800313, best_read: 2e308
// prefix_const < best_read，有优化空间
not give up table name: t1
// 打开优化
prune_level == 1
// 满足优化条件
condition: 1, (best_rowcount: 2e308, position->prefix_rowcount: 2), (best_cost: 2e308, position->prefix_cost: 2.8003), (idx: 1, join->const_tables: 0, s->table(): 0x7f7ad4021770, join->sort_by_table: 0x0)
// 满足更新条件，rows_fetched < 2.0
condition: 1, s->key_dependent:2, remaining_tables:13, position->rows_fetched: 1
// 更新栈内的 best_rowcount, best_const，这两个变量只对此栈处理的表有影响，对外层或内层的表都没有任何影响。这两个值会忽略表行数或cost过大的表，应该是启发优化！
update best_rowcount: 2, best_cost: 2.8003
// 递归调用
enter best_extension_by_limited_search idx: 2, current_search_depth: 1
// 此次处理t4
process table name: t4
// prefix_const < best_read
prefix_const: 5.000908, best_read: 2e308
// t4有优化空间
not give up table name: t4
// 准备优化
prune_level == 1
// 满足优化条件
condition: 1, (best_rowcount: 2e308, position->prefix_rowcount: 6), (best_cost: 2e308, position->prefix_cost: 5.0009), (idx: 2, join->const_tables: 0, s->table(): 0x7f7ad4036f60, join->sort_by_table: 0x0)
// 满足更新条件
condition: 1, s->key_dependent:0, remaining_tables:12, position->rows_fetched: 3
// 更新此栈内的 best_rowcount, best_cost
update best_rowcount: 6, best_cost: 5.0009
// 复制表到 best_posotion 中，表示目前为此，最优的访问顺序是t2, t1, t4.
copy table count: 3, copy best_read: 4.999908
// 循环处理t3
process table name: t3
// prefix_const > best_read
prefix_const: 5.000908, best_read: 4.9999
// t3没有优化空间，放弃
give up table name: t3
// 退出栈
exit best_extension_by_limited_search
// 继续处理 t4
process table name: t4
// prefix_const < best_read
prefix_const: 3.600313, best_read: 4.9999
// 有优化空间
not give up table name: t4
// 打开优化
prune_level == 1
// 不满足优化条件，当前位置的表 t4 prefix_rowcount(6) > best_rowcount(2)，并且 prefix_cost(3.6003) > best_cost(2.8003)。这两个最优值是由之前处理t1时更新的。t4的行数和cost都比t1要高，因此根据启发优化的规则，忽略t4.
condition: 0, (best_rowcount: 2, position->prefix_rowcount: 6), (best_cost: 2.8003, position->prefix_cost: 3.6003), (idx: 1, join->const_tables: 0, s->table(): 0x7f7ad4036f60, join->sort_by_table: 0x0)
// 不满足启发优化，将忽略表 t4
pruned_by_heuristic, continue
// 继续处理t3,也是忽略到t3.
process table name: t3
prefix_const: 3.600313, best_read: 4.9999
not give up table name: t3
prune_level == 1
condition: 0, (best_rowcount: 2, position->prefix_rowcount: 2), (best_cost: 2.8003, position->prefix_cost: 3.6003), (idx: 1, join->const_tables: 0, s->table(): 0x7f7ad40246e0, join->sort_by_table: 0x0)
pruned_by_heuristic, continue
// 退出栈
exit best_extension_by_limited_search
// 处理完t2后,开始处理t1
process table name: t1
// prefix_const < best_read，有优化空间
prefix_const: 1.200000, best_read: 4.9999
not give up table name: t1
// 打开优化
prune_level == 1
// 之前处理t1时，没有更新 best_rowcount, best_cost，此次可以更新。更新完成后，后续的t3, t4就会被启发优化优化掉！！
condition: 1, (best_rowcount: 2e308, position->prefix_rowcount: 1), (best_cost: 2e308, position->prefix_cost: 1.2), (idx: 0, join->const_tables: 0, s->table(): 0x7f7ad4021770, join->sort_by_table: 0x0)
condition: 1, s->key_dependent:2, remaining_tables:15, position->rows_fetched: 1
update best_rowcount: 1, best_cost: 1.2
enter best_extension_by_limited_search idx: 1, current_search_depth: 2
process table name: t2
prefix_const: 2.600141, best_read: 4.9999
not give up table name: t2
prune_level == 1
condition: 1, (best_rowcount: 2e308, position->prefix_rowcount: 1), (best_cost: 2e308, position->prefix_cost: 2.6001), (idx: 1, join->const_tables: 0, s->table(): 0x7f7ad401e480, join->sort_by_table: 0x0)
condition: 0, s->key_dependent:4, remaining_tables:14, position->rows_fetched: 2
enter best_extension_by_limited_search idx: 2, current_search_depth: 1
process table name: t4
prefix_const: 4.200439, best_read: 4.9999
not give up table name: t4
prune_level == 1
condition: 1, (best_rowcount: 2e308, position->prefix_rowcount: 3), (best_cost: 2e308, position->prefix_cost: 4.2004), (idx: 2, join->const_tables: 0, s->table(): 0x7f7ad4036f60, join->sort_by_table: 0x0)
condition: 1, s->key_dependent:0, remaining_tables:12, position->rows_fetched: 3
update best_rowcount: 3, best_cost: 4.2004
// 确定表的方问顺序为t1, t2, t4!! 注意这里虽然顺序是t1, t2, t4,但由于表的个数(4)>搜索深度(3)，因此只有t1的顺序是有效的，后续表的顺序(目前是t2, t4)会在下一轮迭代中重新确定！
copy table count: 3, copy best_read: 4.199439
process table name: t3
prefix_const: 4.200439, best_read: 4.1994
give up table name: t3
exit best_extension_by_limited_search
process table name: t4
prefix_const: 2.800141, best_read: 4.1994
not give up table name: t4
prune_level == 1
condition: 1, (best_rowcount: 2e308, position->prefix_rowcount: 3), (best_cost: 2e308, position->prefix_cost: 2.8001), (idx: 1, join->const_tables: 0, s->table(): 0x7f7ad4036f60, join->sort_by_table: 0x0)
condition: 1, s->key_dependent:0, remaining_tables:14, position->rows_fetched: 3
update best_rowcount: 3, best_cost: 2.8001
enter best_extension_by_limited_search idx: 2, current_search_depth: 1
process table name: t2
prefix_const: 5.001034, best_read: 4.1994
give up table name: t2
process table name: t3
prefix_const: 5.601034, best_read: 4.1994
give up table name: t3
exit best_extension_by_limited_search
process table name: t3
prefix_const: 2.800141, best_read: 4.1994
not give up table name: t3
prune_level == 1
condition: 0, (best_rowcount: 3, position->prefix_rowcount: 3), (best_cost: 2.8001, position->prefix_cost: 2.8001), (idx: 1, join->const_tables: 0, s->table(): 0x7f7ad40246e0, join->sort_by_table: 0x0)
pruned_by_heuristic, continue
exit best_extension_by_limited_search
// 处理t4,但被优化掉
process table name: t4
prefix_const: 1.600000, best_read: 4.1994
not give up table name: t4
prune_level == 1
condition: 0, (best_rowcount: 1, position->prefix_rowcount: 3), (best_cost: 1.2, position->prefix_cost: 1.6), (idx: 0, join->const_tables: 0, s->table(): 0x7f7ad4036f60, join->sort_by_table: 0x0)
pruned_by_heuristic, continue
// 处理t3,但被优化掉
process table name: t3
prefix_const: 1.600000, best_read: 4.1994
not give up table name: t3
prune_level == 1
condition: 0, (best_rowcount: 1, position->prefix_rowcount: 3), (best_cost: 1.2, position->prefix_cost: 1.6), (idx: 0, join->const_tables: 0, s->table(): 0x7f7ad40246e0, join->sort_by_table: 0x0)
pruned_by_heuristic, continue
exit best_extension_by_limited_search
after call best_extension_by_limited_search
// greedy_search第一次do...while循环结束后，确定表顺序中第一个表是t1!!
table order idx: 0, table name: t1
// call_idx: 2,第二轮迭代，在第一表确定为t1后,此次需要确定第二，第三，第四个表!!
call_idx: 2, idx: 1, size_remain: 3, search_depth: 3
enter best_extension_by_limited_search idx: 1, current_search_depth: 3
// 此次处理t2
process table name: t2
prefix_const: 2.600141, best_read: 2e308
not give up table name: t2
prune_level == 1
condition: 1, (best_rowcount: 2e308, position->prefix_rowcount: 1), (best_cost: 2e308, position->prefix_cost: 2.6001), (idx: 1, join->const_tables: 0, s->table(): 0x7f7ad401e480, join->sort_by_table: 0x0)
condition: 0, s->key_dependent:4, remaining_tables:14, position->rows_fetched: 2
enter best_extension_by_limited_search idx: 2, current_search_depth: 2
process table name: t4
prefix_const: 4.200439, best_read: 2e308
not give up table name: t4
prune_level == 1
condition: 1, (best_rowcount: 2e308, position->prefix_rowcount: 3), (best_cost: 2e308, position->prefix_cost: 4.2004), (idx: 2, join->const_tables: 0, s->table(): 0x7f7ad4036f60, join->sort_by_table: 0x0)
condition: 1, s->key_dependent:0, remaining_tables:12, position->rows_fetched: 3
update best_rowcount: 3, best_cost: 4.2004
enter best_extension_by_limited_search idx: 3, current_search_depth: 1
process table name: t3
prefix_const: 7.001801, best_read: 2e308
not give up table name: t3
prune_level == 1
condition: 1, (best_rowcount: 2e308, position->prefix_rowcount: 9), (best_cost: 2e308, position->prefix_cost: 7.0018), (idx: 3, join->const_tables: 0, s->table(): 0x7f7ad40246e0, join->sort_by_table: 0x0)
condition: 1, s->key_dependent:8, remaining_tables:4, position->rows_fetched: 3
update best_rowcount: 9, best_cost: 7.0018
// 确定表顺序为t2, t4, t3
copy table count: 4, copy best_read: 7.000801
exit best_extension_by_limited_search
process table name: t3
prefix_const: 4.200439, best_read: 7.0008
not give up table name: t3
prune_level == 1
condition: 1, (best_rowcount: 3, position->prefix_rowcount: 1), (best_cost: 4.2004, position->prefix_cost: 4.2004), (idx: 2, join->const_tables: 0, s->table(): 0x7f7ad40246e0, join->sort_by_table: 0x0)
condition: 0, s->key_dependent:8, remaining_tables:12, position->rows_fetched: 3
enter best_extension_by_limited_search idx: 3, current_search_depth: 1
process table name: t4
prefix_const: 5.800893, best_read: 7.0008
not give up table name: t4
prune_level == 1
condition: 1, (best_rowcount: 2e308, position->prefix_rowcount: 3), (best_cost: 2e308, position->prefix_cost: 5.8009), (idx: 3, join->const_tables: 0, s->table(): 0x7f7ad4036f60, join->sort_by_table: 0x0)
condition: 1, s->key_dependent:0, remaining_tables:8, position->rows_fetched: 3
update best_rowcount: 3, best_cost: 5.8009
// 更新表顺序为t2, t3, t4!!!
copy table count: 4, copy best_read: 5.799893
exit best_extension_by_limited_search
exit best_extension_by_limited_search
process table name: t4
prefix_const: 2.800141, best_read: 5.7999
not give up table name: t4
prune_level == 1
condition: 1, (best_rowcount: 2e308, position->prefix_rowcount: 3), (best_cost: 2e308, position->prefix_cost: 2.8001), (idx: 1, join->const_tables: 0, s->table(): 0x7f7ad4036f60, join->sort_by_table: 0x0)
condition: 1, s->key_dependent:0, remaining_tables:14, position->rows_fetched: 3
update best_rowcount: 3, best_cost: 2.8001
enter best_extension_by_limited_search idx: 2, current_search_depth: 2
process table name: t2
prefix_const: 5.001034, best_read: 5.7999
not give up table name: t2
prune_level == 1
condition: 1, (best_rowcount: 2e308, position->prefix_rowcount: 3), (best_cost: 2e308, position->prefix_cost: 5.001), (idx: 2, join->const_tables: 0, s->table(): 0x7f7ad401e480, join->sort_by_table: 0x0)
condition: 0, s->key_dependent:4, remaining_tables:6, position->rows_fetched: 2
enter best_extension_by_limited_search idx: 3, current_search_depth: 1
process table name: t3
prefix_const: 7.802396, best_read: 5.7999
give up table name: t3
exit best_extension_by_limited_search
process table name: t3
prefix_const: 5.601034, best_read: 5.7999
not give up table name: t3
prune_level == 1
condition: 1, (best_rowcount: 2e308, position->prefix_rowcount: 3), (best_cost: 2e308, position->prefix_cost: 5.601), (idx: 2, join->const_tables: 0, s->table(): 0x7f7ad40246e0, join->sort_by_table: 0x0)
condition: 1, s->key_dependent:8, remaining_tables:6, position->rows_fetched: 3
update best_rowcount: 3, best_cost: 5.601
enter best_extension_by_limited_search idx: 3, current_search_depth: 1
process table name: t2
prefix_const: 7.201034, best_read: 5.7999
give up table name: t2
exit best_extension_by_limited_search
exit best_extension_by_limited_search
process table name: t3
prefix_const: 2.800141, best_read: 5.7999
not give up table name: t3
prune_level == 1
condition: 0, (best_rowcount: 3, position->prefix_rowcount: 3), (best_cost: 2.8001, position->prefix_cost: 2.8001), (idx: 1, join->const_tables: 0, s->table(): 0x7f7ad40246e0, join->sort_by_table: 0x0)
pruned_by_heuristic, continue
exit best_extension_by_limited_search
after call best_extension_by_limited_search
table order idx: 1, table name: t2
table order idx: 2, table name: t3
table order idx: 3, table name: t4
// 最终表的顺序为t1, t2, t3, t4,基本上是按照行数大小排序的!!
可以看到，最后确定的表方问顺序为t1, t2, t3, t4 。整个过程还是比较复杂的！
扫一扫
333
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
