34 | 到底可不可以使用join

1  join 有什么问题 ？

2  两个大小不同表 join， 哪个做驱动表？

主键索引 id 和 索引 a ，b 上无索引。 t2 插 1000 行， t1  100 行 。

select from t1  straight_join t2 on ( t1a=t2a );

straight_join 指定方式join ：t1 驱动表，t2 被驱动表。

直接用 join ， 优化器选驱动表 ，影响分析执行过程。

1  t1 读一行数据 R；       //全表扫描，扫描 100 行 ；

2  R 中a 字段到t2 里查    //树搜索，每次只扫一行， 总100 行 ；

3  t2 满足条件行+R = 结果集一部分（一行）；

4  重复直到 t1 循环结束。

不使用 join， 单表 查询。不如直接 join 

1 select from t1，所有数据， 100 行 ； 1句

2从每一行 R 取出字段 a 的值 $Ra；select from t2 where a=$Ra； 100 行 ； 100 句

3返回结果+R = 结果集一行。// 多100 次交互: 拼接 SQL 语句和结果 。

驱动表（行数 N）：全表扫描，被驱动表（行数M）：树搜索。

树搜索复杂度： 2 为底M对数，log2M，查一行是 2log2M

驱动表： N 行到被驱动表上匹配一次。

近似复杂度N + N2log2M。

N 对扫描行数影响更大， 小表做驱动表。

select from t1  straight_join t2 on (t1a= t2b );

b 上没有索引， t2 全表扫描（100次）。总共扫描 1001000=10 万行。

t1 和 t2 都是 10 万行表，要扫描 100 亿行，太“笨重”，MySQL 没用这个算法

三、Block Nested-Loop Join 没可用索引

1   t1读入线程 内存 join_buffer 中 ，select 整个 t1 放内存；

2  扫描 t2，每行取出，跟 join_buffer 对比 ，满足 join 条件作为结果集一部分返回。

内存 *** 作，速度快多，性能好 。时间复杂度和上面一样

都一样

1  总扫描行 M+N；（1100）

2  判断次数是 MN。（1001000=10 万次join_buffer 无序数组）

分段放 （Block由来） 。join_buffer_size 默认 256k， 改成 1200，再执行：

1  扫描表 t1，顺序读取数据行放入 join_buffer 中， 放完第 88 行 join_buffer 满

2 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；

3  清空 join_buffer；重复上面

判断等值条件的次数还是不变的，依然是(88+12)1000=10 万次。

小表 当驱动表。驱动表 N行，分 K段完成，被驱动表M行。

K = λN，λ (0,1)，N 越大 K 越大

1  扫描行数N+λNM；

2  内存判断 NM 次。

考虑扫描行数，N 小结果小。λ才是关键因素，越小越好。

N 固定，什么参数会影响 K 的大小呢？（就是λ的大小）join_buffer_size。 分段越少，扫描次数越少。

join 语句很慢，把 join_buffer_size 改大 。

1   Index Nested-Loop Join 算法，没问题；

2  Block Nested-Loop Join 算法 不要 ，扫描行多。尤其大表上 join *** 作，扫描被驱动表很多次，会占用大量的系统资源。

explain 结果，Extra 有没有出现“Block Nested Loop”字样。

1  Index Nested-Loop Join ， 小表

2  Block Nested-Loop Join 算法：

join_buffer_size 足够 大 ， 一样 ； 不够 ， 小表

select from t1  straight_join t2 on (t1b=t2b) where t2id<=50;

select from t2   straight_join t1 on (t1b=t2b) where t2id<=50;

都用不上索引， 第二 ：join_buffer 只放t2 前 50 行 （ 是“小表” ）， 更好

另一组例子：

select t1b,t2  from  t1  straight_join t2 on (t1b=t2b) where  t2id<=100;

select t1b,t2  from  t2  straight_join t1 on (t1b=t2b) where  t2id<=100;

t1 放到 join_buffer 中，只放b值 （小表） t1 为驱动表。

 t2 放 id、a 和 b。

两个表按照各自条件过滤，计算参与 join 各个字段总数据量，数据量小就是 “ 小表 ” ，驱动表。

能否用 被驱动表索引 ，对 join 性能 影响大

用被 驱动表 索引 ， join 好 ；否则，不要用；

用 join 时， 小表做驱动表

Block Nested-Loop Join 算法，可能会因为 join_buffer 不够大，被驱动表做多次全表扫描。

被驱动表 是 大表 （冷数据），除查询可能会IO 压力大，MySQL 服务还有什么更严重影响？（结合上一篇知识点）

两种情况：

（1） 数据量小于old ，驱动表分段，被驱动表 多次读 ，大表，循环读取间隔超1秒， 移到young区 。 Buffer pool hit rate 命中率极低，其他请求 读磁盘 ， 响应慢，阻塞。

(2 )被驱动表数据量大于 Buffer Pool ,影响young部分更新

评论1

为什么会进入young区域？

大表t M页>old区域N页，对t进行k次全表扫描。第一次扫描时，1~N页依次被放入old区域，访问N+1页时淘汰1页，放入N+1页，以此类推，第一次扫描结束后old区域存放的是M-N+1~M页。第二次扫描开始，访问1页，淘汰M-N+1页，放入1页。可以把M页想象成一个环，N页想象成在这个环上滑动的窗口，由于M>N， 不管是哪次扫描，需要访问的页都不会在滑动窗口上，所以不会存在“被访问的时候数据页在 LRU 链表中存在的时间超过了 1 秒“而被放入young的情况。 我能想到的会被放入young区域的情况是，在当次扫描中，由于一页上有多行数据，需要对该页访问多次，超过了1s，不管这种情况就和t大小没关系了，而是由于page size太大，而一行数据太少。

 join_buffer 不够大，多次全表扫描，“ 长事务 ”。除了导致 undo log 不能被回收 ， 回滚段空间膨胀 问题，还会出现：

1 长期 占DML锁 ，引发 DDL拿不到锁 ， 堵满连接池 ；

 2 SQL执行 socket_timeout 超时后业务接口 重复发起 ， IO负载上升 雪崩；

3 实例异常，DBA kill SQL因繁杂的回滚执行时间过长， 不能快速恢复 ；

4 select 返回， 网络拥堵，拖慢服务端 ；

数据库优化你学理论不如进行实践。其实这个公式偏向于SQL优化，进行排序性聚合算法

Br+BrBs/（K-1） 这个就是嵌套循环连接算法（join）的效率估算公式

sql：

select from table1 as R

inner join tabel2 as S on Rid=Sid

其中：

B=全表

r代表R为别名的表table1，s代表S为别名的表table2

Br和Bs就是代表 table1 和 table2了

K为内存缓冲区块，系统会分配K-1块给相关的表辨得出Br+BrBs/（K-1）

(FrsNrNs)/MrsNr 则是系统筛选算法估算，

N=元组总数，F=选择率，M=链接结果

Frs就是r和s的合并量

Nr和Ns就是table1和tabel2的总元素

Mrs则是符合条件Rid=Sid的链接结果因子块

Java的标准库里面没有类似这样的函数，不过你完全可以自己实现一个，提高效率就用StringBuffer实现

import javautilArrayList;

import javautilList;

public class StringBuilderDemo1 {

public static String join(String join,String[] strAry){

StringBuffer sb=new StringBuffer();

for(int i=0;i<strArylength;i++){

if(i==(strArylength-1)){

sbappend(strAry[i]);

}else{

sbappend(strAry[i])append(join);

}

}

return new String(sb);

}

public static void main(String[] args){

String[] sa={"a","b","c"};

String s1=StringBuilderDemo1join("-",sa);

Systemoutprintln(s1);

}

}

以上就是关于34 | 到底可不可以使用join全部的内容，包括:34 | 到底可不可以使用join、数据库排序合并算法中，cost=Br+Bs+(Frs*Nr*Ns)/Mrs.Nr和Ns是什么、java有没有类似leftjoin的算法等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！