•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);
}

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

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

大表全部读完后，清空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技术博客大学习