您现在的位置：首页 --> 源码分析 --> MariaDB数据库5.5.27 HASH JOIN源码解读

MariaDB数据库5.5.27 HASH JOIN源码解读

浏览:1708次出处信息

•JOIN_CACHE::generate_full_extensions

enum_nested_loop_state JOIN_CACHE::generate_full_extensions(uchar *rec_ptr)
{
enum_nested_loop_state rc= NESTED_LOOP_OK;
DBUG_ENTER(“JOIN_CACHE::generate_full_extensions”);

/*
Check whether the extended partial join record meets
the pushdown conditions.
*/
if (check_match(rec_ptr))
{
int res= 0;

if (!join_tab->check_weed_out_table ||
!(res= join_tab->check_weed_out_table->sj_weedout_check_row(join->thd)))
{
set_curr_rec_link(rec_ptr);
rc= (join_tab->next_select)(join, join_tab+1, 0);//此处进入end_send
if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
{
reset(TRUE);
DBUG_RETURN(rc);
}
}
if (res == -1)
{
rc= NESTED_LOOP_ERROR;
DBUG_RETURN(rc);
}
}
else if (join->thd->is_error())
rc= NESTED_LOOP_ERROR;
DBUG_RETURN(rc);
}

end_send

if (join->do_send_rows)
{
int error;
/* result < 0 if row was not accepted and should not be counted */
if ((error= join->result->send_data(*join->fields)))//再跟踪，就到网络接口了，应该是发送记录了
DBUG_RETURN(error < 0 ? NESTED_LOOP_OK : NESTED_LOOP_ERROR);
}

到此，找到并发送了一条满足条件的记录

之后继续循环读取t3记录，做同样处理

两种explain结果比较

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using where
1	SIMPLE	t3	hash_ALL	NULL	#hash#$hj	104	tm.t1.name	262038	Using where; Using join buffer (flat, BNLH join)

上面是支持HASH JOIN时的计划，下面是不支持HASH JOIN时的计划

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	261619	Using where; Using join buffer (flat, BNL join)

Show profile比较

Status HASH JOIN	Duration
starting	0.000116
checking permissions	0.000010
checking permissions	0.000012
Opening tables	0.000064
System lock	0.000015
Table lock	0.000020
init	0.000056
optimizing	0.000037
statistics	0.000080
preparing	0.000077
executing	0.000008
Sending data	0.931718
end	0.000020
query end	0.000012
closing tables	0.000025
freeing items	0.000118
logging slow query	0.000005
cleaning up	0.000008

上面是支持HASH JOIN时的PROFILE

Status	Duration
starting	0.000170
checking permissions	0.000014
checking permissions	0.000016
Opening tables	0.000094
System lock	0.000021
Table lock	0.000028
init	0.000072
optimizing	0.000061
statistics	0.000087
preparing	0.024370
executing	0.000016
Sending data	2.603823
end	0.000026
query end	0.000021
closing tables	0.000025
freeing items	0.000092
logging slow query	0.000005
cleaning up	0.000007

虽然只是优化减少了内层的循环次数，但性能也明显提升了。

Join_buffer写满时处理

•sub_select_cache

•满了则put_record返回1

•调用join_records，循环读大表全部记录，与当前hash中记录匹配

大表全部读完后，清空hash，再读小表填充HASH

if (!test_if_use_dynamic_range_scan(join_tab))
{
if (!cache->put_record())
DBUG_RETURN(NESTED_LOOP_OK);
/*
We has decided that after the record we’ve just put into the buffer
won’t add any more records. Now try to find all the matching
extensions for all records in the buffer.
*/
rc= cache->join_records(FALSE);
DBUG_RETURN(rc);
}

因此join buffer写满，对BNLH或者BNL都是有很大影响的。每写满一次，都需要再扫一遍大表。

建议继续学习：

QQ技术交流群：445447336，欢迎加入！
扫一扫订阅我的微信号：IT技术博客大学习

<< 前一篇：Tomcat 5源码分析

后一篇：BufferedIO和DirectIO混用导致的脏页回写问题 >>

文章信息

作者：央掘魔罗来源： MySQLOPS 数据库与运维自动化技术分享
标签： MariaDB
发布时间：2012-10-22 22:02:21

建议继续学习

近3天十大热文