•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);
}
两种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 |
| 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写满时处理
{
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);
}