技术头条 - 一个快速在微博传播文章的方式     搜索本站
您现在的位置首页 --> 源码分析 --> MariaDB数据库5.5.27 HASH JOIN源码解读

MariaDB数据库5.5.27 HASH JOIN源码解读

浏览:1642次  出处信息

•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都是有很大影响的。每写满一次,都需要再扫一遍大表。

建议继续学习:

  1. MariaDB常见问题FAQ    (阅读:7570)
  2. MariaDB与Percona XtraDB的Group Commit实现原理分析    (阅读:1259)
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
© 2009 - 2024 by blogread.cn 微博:@IT技术博客大学习

京ICP备15002552号-1