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
- [136] WEB系统需要关注的一些点
- [15] 浏览器的工作原理:新式网络浏览器幕后揭秘
- [12] Spark性能优化——和shuffle搏斗
- [12] 我的git笔记
- [12] InnoDB insert性能拐点测试
- [12] 一次神奇的MySQL优化
- [11] 内网穿透神器frp
- [10] 深入剖析 redis replication
- [10] Python 代码规范小结
- [10] Python程序的执行原理