Nested-Loop Join算法

Nested-Loop Join Algorithm:
Assume that a join between three tables t1, t2, and t3 is to be executed using the following jointypes:
Table Join Type
t1 range
t2 ref
t3 ALL
If a simple NLJ algorithm is used, the join is processed like this:
for each row in t1 matching range {
for each row in t2 matching reference key {
for each row in t3 {
if row satisfies join conditions, send to client
}
}
}
Because the NLJ algorithm passes rows one at a time from outer loops to inner loops, it typically reads tables processed in the inner loops many times.

Block Nested-Loop Join Algorithm:

MySQL join buffering has these characteristics:
• Join buffering can be used when the join is of type ALL or index (in other words, when no possible keys can be used, and a full scan is done, of either the data or index rows, respectively), or range.
• A join buffer is never allocated for the first nonconstant table, even if it would be of type ALL or index.
• Only columns of interest to a join are stored in its join buffer, not whole rows.
• The join_buffer_size system variable determines the size of each join buffer used to process a query.
• One buffer is allocated for each join that can be buffered, so a given query might be processed using multiple join buffers.
• A join buffer is allocated prior to executing the join and freed after the query is done.Optimizing SELECT Statements

For the example join described previously for the NLJ algorithm (without buffering), the join is done as
follows using join buffering:
for each row in t1 matching range {
for each row in t2 matching reference key {
store used columns from t1, t2 in join buffer
if buffer is full {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions, send to client
}
}
empty join buffer
}
}
}
if buffer is not empty {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions, send to client
}
}
}
参考文章:
https://blog.csdn.net/ghsau/article/details/43762027
http://dev.mysql.com/doc/refman/5.7/en/nested-loop-joins.html
https://blog.csdn.net/shangmingtao/article/details/78809133

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值