MySQL数据库，深入了解连接查询及原理(三)

用户1289394

发布于 2021-11-16 11:01:05

4290

发布于 2021-11-16 11:01:05

文章被收录于专栏：Java学习网

java代码实现连接查询

下⾯是⼀个简略版的实现

package com.javalearns.sql;import org.junit.Test;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.stream.Collectors;
public class Test1 {
 public static class Table1 {
 int a;
 public int getA() {
 return a;
 }
 public void setA(int a) {
 this.a = a;
 }
 public Table1(int a) {
 this.a = a;
 }
 @Override
 public String toString() {
 return "Table1{" +
 "a=" + a +
'}';
 }
 public static Table1 build(int a) {
 return new Table1(a);
 }
 }
 public static class Table2 {
 int b; public int getB() {
 return b;
 }
 public void setB(int b) {
 this.b = b;
 }
 public Table2(int b) {
 this.b = b;
 }
 public static Table2 build(int b) {
 return new Table2(b);
 }
 @Override
 public String toString() {
 return "Table2{" +
 "b=" + b +
'}';
 }
 }
 public static class Record<R1, R2> {
 R1 r1;
 R2 r2;
 public R1 getR1() {
 return r1;
 }
 public void setR1(R1 r1) {
 this.r1 = r1;
 }
 public R2 getR2() {
 return r2;
 } public void setR2(R2 r2) {
 this.r2 = r2;
 }
 public Record(R1 r1, R2 r2) {
 this.r1 = r1;
 this.r2 = r2;
 }
 @Override
 public String toString() {
 return "Record{" +
 "r1=" + r1 +
", r2=" + r2 +
'}';
 }
 public static <R1, R2> Record<R1, R2> build(R1 r1, R2 r2) {
 return new Record(r1, r2);
 }
 }
 public static enum JoinType {
 innerJoin, leftJoin
 }
 public static interface Filter<R1, R2> {
 boolean accept(R1 r1, R2 r2);
 }
 public static <R1, R2> List<Record<R1, R2>> join(List<R1> table1, 
List<R2> table2, JoinType joinType, Filter<R1, R2> onFilter, 
Filter<R1, R2> whereFilter) {
 if (Objects.isNull(table1) || Objects.isNull(table2) || 
joinType == null) {
 return new ArrayList<>();
 }
 List<Record<R1, R2>> result = new CopyOnWriteArrayList<>(); for (R1 r1 : table1) {
 List<Record<R1, R2>> onceJoinResult = joinOn(r1, table2, 
onFilter);
 result.addAll(onceJoinResult);
 }
 if (joinType == JoinType.leftJoin) {
 List<R1> r1Record = 
result.stream().map(Record::getR1).collect(Collectors.toList());
 List<Record<R1, R2>> leftAppendList = new ArrayList<>();
 for (R1 r1 : table1) {
 if (!r1Record.contains(r1)) {
 leftAppendList.add(Record.build(r1, null));
 }
 }
 result.addAll(leftAppendList);
 }
 if (Objects.nonNull(whereFilter)) {
 for (Record<R1, R2> record : result) {
 if (!whereFilter.accept(record.r1, record.r2)) {
 result.remove(record);
 }
 }
 }
 return result;
 }
 public static <R1, R2> List<Record<R1, R2>> joinOn(R1 r1, List<R2> 
table2, Filter<R1, R2> onFilter) {
 List<Record<R1, R2>> result = new ArrayList<>();
 for (R2 r2 : table2) {
 if (Objects.nonNull(onFilter) ? onFilter.accept(r1, r2) : 
true) {
 result.add(Record.build(r1, r2));
 }
 }
 return result;
 } @Test
 public void innerJoin() {
 List<Table1> table1 = Arrays.asList(Table1.build(1), 
Table1.build(2), Table1.build(3));
 List<Table2> table2 = Arrays.asList(Table2.build(3), 
Table2.build(4), Table2.build(5));
 join(table1, table2, JoinType.innerJoin, null, 
null).forEach(System.out::println);
 System.out.println("-----------------");
 join(table1, table2, JoinType.innerJoin, (r1, r2) -> r1.a == 
r2.b, null).forEach(System.out::println);
 }
 @Test
 public void leftJoin() {
 List<Table1> table1 = Arrays.asList(Table1.build(1), 
Table1.build(2), Table1.build(3));
 List<Table2> table2 = Arrays.asList(Table2.build(3), 
Table2.build(4), Table2.build(5));
 join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a == 
r2.b, null).forEach(System.out::println);
 System.out.println("-----------------");
 join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a > 10, 
null).forEach(System.out::println);
 }
}

代码中的innerJoin()⽅法模拟了下⾯的sql：

mysql> select * from test1 t1,test2 t2;

+------+------+

| a | b |

+------+------+

| 1 | 3 |

| 2 | 3 |

| 3 | 3 |

| 1 | 4 |

| 2 | 4 || 3 | 4 |

| 1 | 5 |

| 2 | 5 |

| 3 | 5 |

+------+------+

9 rows in set (0.00 sec)

mysql> select * from test1 t1,test2 t2 where t1.a = t2.b;

+------+------+

| a | b |

+------+------+

| 3 | 3 |

+------+------+

1 row in set (0.00 sec)

运⾏⼀下innerJoin()输出如下：

Record{r1=Table1{a=1}, r2=Table2{b=3}}

Record{r1=Table1{a=1}, r2=Table2{b=4}}

Record{r1=Table1{a=1}, r2=Table2{b=5}}

Record{r1=Table1{a=2}, r2=Table2{b=3}}

Record{r1=Table1{a=2}, r2=Table2{b=4}}

Record{r1=Table1{a=2}, r2=Table2{b=5}}

Record{r1=Table1{a=3}, r2=Table2{b=3}}

Record{r1=Table1{a=3}, r2=Table2{b=4}}

Record{r1=Table1{a=3}, r2=Table2{b=5}}

-----------------

Record{r1=Table1{a=3}, r2=Table2{b=3}}

对⽐⼀下sql和java的结果，输出的结果条数、数据基本上⼀致，唯⼀不同的是顺序上⾯不

⼀样，顺序为何不⼀致，稍微介绍。

代码中的leftJoin()⽅法模拟了下⾯的sql：

mysql> select * from test1 t1 left join test2 t2 on t1.a = t2.b;

+------+------+

| a | b |

+------+------+

| 3 | 3 || 1 | NULL |

| 2 | NULL |

+------+------+

3 rows in set (0.00 sec)

mysql> select * from test1 t1 left join test2 t2 on t1.a>10;

+------+------+

| a | b |

+------+------+

| 1 | NULL |

| 2 | NULL |

| 3 | NULL |

+------+------+

3 rows in set (0.00 sec)

运⾏leftJoin()，结果如下：

Record{r1=Table1{a=3}, r2=Table2{b=3}}

Record{r1=Table1{a=1}, r2=null}

Record{r1=Table1{a=2}, r2=null}

-----------------

Record{r1=Table1{a=1}, r2=null}

Record{r1=Table1{a=2}, r2=null}

Record{r1=Table1{a=3}, r2=null}

效果和sql的效果完全⼀致，可以对上。

现在我们来讨论java输出的顺序为何和sql不⼀致？

上⾯java代码中两个表的连接查询使⽤了嵌套循环，外循环每执⾏⼀次，内循环的表都会全部遍历⼀次，如果放到mysql中，就相当于内表（被驱动表）全部扫描了⼀次（⼀次全表io读取操作），主表（外循环）如果有n条数据，那么从表就需要全表扫描n次，表的数据是存储在磁盘中，每次全表扫描都需要做io操作，io操作是最耗时间的，如果mysql按照上⾯的java⽅式实现，那效率肯定很低。

那mysql是如何优化的呢？msql内部使⽤了⼀个内存缓存空间，就叫他join_buffer吧，先把外循环的数

据放到join_buffer中，然后对从表进⾏遍历，从表中取⼀条数据和

join_buffer的数据进⾏⽐较，然后从表中再取第2条和join_buffer数据进⾏

⽐较，直到从表遍历完成，使⽤这⽅⽅式来减少从表的io扫描次数，当

join_buffer⾜够⼤的时候，⼤到可以存放主表所有数据，那么从表只需要全

表扫描⼀次（即只需要⼀次全表io读取操作）。

mysql中这种⽅式叫做Block Nested Loop。

java代码改进⼀下，来实现join_buffer的过程。

java代码改进版本

package com.javalearns.sql;
import org.junit.Test;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.stream.Collectors;
import com.itsoku.sql.Test1.*;
public class Test2 {
 public static int joinBufferSize = 10000;
 public static List<?> joinBufferList = new ArrayList<>();
 public static <R1, R2> List<Record<R1, R2>> join(List<R1> table1, 
List<R2> table2, JoinType joinType, Filter<R1, R2> onFilter, 
Filter<R1, R2> whereFilter) {
 if (Objects.isNull(table1) || Objects.isNull(table2) || 
joinType == null) {
 return new ArrayList<>();
 } List<Test1.Record<R1, R2>> result = new
CopyOnWriteArrayList<>();
 int table1Size = table1.size();
 int fromIndex = 0, toIndex = joinBufferSize;
 toIndex = Integer.min(table1Size, toIndex);
 while (fromIndex < table1Size && toIndex <= table1Size) {
 joinBufferList = table1.subList(fromIndex, toIndex);
 fromIndex = toIndex;
 toIndex += joinBufferSize;
 toIndex = Integer.min(table1Size, toIndex);
 List<Record<R1, R2>> blockNestedLoopResult = 
blockNestedLoop((List<R1>) joinBufferList, table2, onFilter);
 result.addAll(blockNestedLoopResult);
 }
 if (joinType == JoinType.leftJoin) {
 List<R1> r1Record = 
result.stream().map(Record::getR1).collect(Collectors.toList());
 List<Record<R1, R2>> leftAppendList = new ArrayList<>();
 for (R1 r1 : table1) {
 if (!r1Record.contains(r1)) {
 leftAppendList.add(Record.build(r1, null));
 }
 }
 result.addAll(leftAppendList);
 }
 if (Objects.nonNull(whereFilter)) {
 for (Record<R1, R2> record : result) {
 if (!whereFilter.accept(record.r1, record.r2)) {
 result.remove(record);
 }
 }
 }
 return result;
 }
 public static <R1, R2> List<Record<R1, R2>> 
blockNestedLoop(List<R1> joinBufferList, List<R2> table2, Filter<R1, R2> onFilter) {
 List<Record<R1, R2>> result = new ArrayList<>();
 for (R2 r2 : table2) {
 for (R1 r1 : joinBufferList) {
 if (Objects.nonNull(onFilter) ? onFilter.accept(r1, 
r2) : true) {
 result.add(Record.build(r1, r2));
 }
 }
 }
 return result;
 }
 @Test
 public void innerJoin() {
 List<Table1> table1 = Arrays.asList(Table1.build(1), 
Table1.build(2), Table1.build(3));
 List<Table2> table2 = Arrays.asList(Table2.build(3), 
Table2.build(4), Table2.build(5));
 join(table1, table2, JoinType.innerJoin, null, 
null).forEach(System.out::println);
 System.out.println("-----------------");
 join(table1, table2, JoinType.innerJoin, (r1, r2) -> r1.a == 
r2.b, null).forEach(System.out::println);
 }
 @Test
 public void leftJoin() {
 List<Table1> table1 = Arrays.asList(Table1.build(1), 
Table1.build(2), Table1.build(3));
 List<Table2> table2 = Arrays.asList(Table2.build(3), 
Table2.build(4), Table2.build(5));
 join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a == 
r2.b, null).forEach(System.out::println);
 System.out.println("-----------------");
 join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a > 10, 
null).forEach(System.out::println);
 }
}执⾏innerJoin()，输出：
Record{r1=Table1{a=1}, r2=Table2{b=3}}
Record{r1=Table1{a=2}, r2=Table2{b=3}}
Record{r1=Table1{a=3}, r2=Table2{b=3}}
Record{r1=Table1{a=1}, r2=Table2{b=4}}
Record{r1=Table1{a=2}, r2=Table2{b=4}}
Record{r1=Table1{a=3}, r2=Table2{b=4}}
Record{r1=Table1{a=1}, r2=Table2{b=5}}
Record{r1=Table1{a=2}, r2=Table2{b=5}}
Record{r1=Table1{a=3}, r2=Table2{b=5}}
-----------------
Record{r1=Table1{a=3}, r2=Table2{b=3}}
执⾏leftJoin()，输出：
Record{r1=Table1{a=3}, r2=Table2{b=3}}
Record{r1=Table1{a=1}, r2=null}
Record{r1=Table1{a=2}, r2=null}
-----------------
Record{r1=Table1{a=1}, r2=null}
Record{r1=Table1{a=2}, r2=null}
Record{r1=Table1{a=3}, r2=null}
结果和sql的结果完全⼀致。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-11-06，如有侵权请联系 cloudcommunity@tencent.com 删除

java