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
- [355] 如何拿下简短的域名
- [353] WEB系统需要关注的一些点
- [11] 关于身份证号的那些事
- [11] 大并发下的高性能编程 – 改进的(用户态)自
- [11] Python中的闭包
- [10] Zookeeper工作原理
- [10] 一致性哈希算法及其在分布式系统中的应用
- [10] 分布式系统的事务处理
- [10] 一步一步教你怎样给Apache Spark贡
- [9] 什么是DNS劫持和DNS污染?