MariaDB数据库5.5.27 HASH JOIN源码解读
•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);
}
建议继续学习:
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:央掘魔罗 来源: MySQLOPS 数据库与运维自动化技术分享
- 标签: MariaDB
- 发布时间:2012-10-22 22:02:21
- [66] Oracle MTS模式下 进程地址与会话信
- [65] Go Reflect 性能
- [64] 如何拿下简短的域名
- [60] android 开发入门
- [59] 图书馆的世界纪录
- [59] 【社会化设计】自我(self)部分――欢迎区
- [58] IOS安全–浅谈关于IOS加固的几种方法
- [54] 视觉调整-设计师 vs. 逻辑
- [47] 界面设计速成
- [47] 读书笔记-壹百度:百度十年千倍的29条法则