提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

玩转Mysql系列 - 第11篇：深入了解连接查询及原理

mysql连接原理 查询 系列 深入 了解 11
2023-09-27 14:26:04 时间

打算提升sql技能的，可以加我微信itsoku，带你成为sql高手。

这是Mysql系列第11篇。

环境：mysql5.7.25，cmd命令中进行演示。

当我们查询的数据来源于多张表的时候，我们需要用到连接查询，连接查询使用率非常高，希望大家都务必掌握。

本文内容

  1. 笛卡尔积

  2. 内连接

  3. 外连接

  4. 左连接

  5. 右连接

  6. 表连接的原理

  7. 使用java实现连接查询，加深理解

准备数据

2张表：

t_team：组表。

t_employee：员工表，内部有个team_id引用组表的id。

  1. drop table if exists t_team;
  2. create table t_team(
  3.   id int not null AUTO_INCREMENT PRIMARY KEY comment '组id',
  4.   team_name varchar(32) not null default '' comment '名称'
  5. ) comment '组表';
  6.  
  7. drop table if exists t_employee;
  8. create table t_employee(
  9.   id int not null AUTO_INCREMENT PRIMARY KEY comment '部门id',
  10.   emp_name varchar(32) not null default '' comment '员工名称',
  11.   team_id int not null default 0 comment '员工所在组id'
  12. ) comment '员工表表';
  13.  
  14. insert into t_team values (1,'架构组'),(2,'测试组'),(3,'java组'),(4,'前端组');
  15. insert into t_employee values (1,'路人甲Java',1),(2,'张三',2),(3,'李四',3),(4,'王五',0),(5,'赵六',0);

t_team表4条记录，如下：

  1. mysql> select * from t_team;
  2. +----+-----------+
  3. | id | team_name |
  4. +----+-----------+
  5. |  1 | 架构组    |
  6. |  2 | 测试组    |
  7. |  3 | java组    |
  8. |  4 | 前端组    |
  9. +----+-----------+
  10. 4 rows in set (0.00 sec)

t_employee表5条记录，如下：

  1. mysql> select * from t_employee;
  2. +----+---------------+---------+
  3. | id | emp_name      | team_id |
  4. +----+---------------+---------+
  5. |  1 | 路人甲Java    |       1 |
  6. |  2 | 张三          |       2 |
  7. |  3 | 李四          |       3 |
  8. |  4 | 王五          |       0 |
  9. |  5 | 赵六          |       0 |
  10. +----+---------------+---------+
  11. 5 rows in set (0.00 sec)

笛卡尔积

介绍连接查询之前，我们需要先了解一下笛卡尔积。

笛卡尔积简单点理解：有两个集合A和B，笛卡尔积表示A集合中的元素和B集合中的元素任意相互关联产生的所有可能的结果。

假如A中有m个元素，B中有n个元素，A、B笛卡尔积产生的结果有m*n个结果，相当于循环遍历两个集合中的元素，任意组合。

java伪代码表示如下：

  1. for(Object eleA : A){
  2.     for(Object eleB : B){
  3.         System.out.print(eleA+","+eleB);
  4.     }
  5. }

过程：拿A集合中的第1行，去匹配集合B中所有的行，然后再拿集合A中的第2行，去匹配集合B中所有的行，最后结果数量为m*n。

sql中笛卡尔积语法

  1. select 字段 from 表1,表2[,表N];
  2. 或者
  3. select 字段 from 表1 join 表2 [join 表N];

示例：

  1. mysql> select * from t_team,t_employee;
  2. +----+-----------+----+---------------+---------+
  3. | id | team_name | id | emp_name      | team_id |
  4. +----+-----------+----+---------------+---------+
  5. |  1 | 架构组    |  1 | 路人甲Java    |       1 |
  6. |  2 | 测试组    |  1 | 路人甲Java    |       1 |
  7. |  3 | java组    |  1 | 路人甲Java    |       1 |
  8. |  4 | 前端组    |  1 | 路人甲Java    |       1 |
  9. |  1 | 架构组    |  2 | 张三          |       2 |
  10. |  2 | 测试组    |  2 | 张三          |       2 |
  11. |  3 | java组    |  2 | 张三          |       2 |
  12. |  4 | 前端组    |  2 | 张三          |       2 |
  13. |  1 | 架构组    |  3 | 李四          |       3 |
  14. |  2 | 测试组    |  3 | 李四          |       3 |
  15. |  3 | java组    |  3 | 李四          |       3 |
  16. |  4 | 前端组    |  3 | 李四          |       3 |
  17. |  1 | 架构组    |  4 | 王五          |       0 |
  18. |  2 | 测试组    |  4 | 王五          |       0 |
  19. |  3 | java组    |  4 | 王五          |       0 |
  20. |  4 | 前端组    |  4 | 王五          |       0 |
  21. |  1 | 架构组    |  5 | 赵六          |       0 |
  22. |  2 | 测试组    |  5 | 赵六          |       0 |
  23. |  3 | java组    |  5 | 赵六          |       0 |
  24. |  4 | 前端组    |  5 | 赵六          |       0 |
  25. +----+-----------+----+---------------+---------+
  26. 20 rows in set (0.00 sec)

t_team表4条记录，t_employee表5条记录，笛卡尔积结果输出了20行记录。

内连接

语法：

  1. select 字段 from 表1 inner join 表2 on 连接条件;
  2. 或
  3. select 字段 from 表1 join 表2 on 连接条件;
  4. 或
  5. select 字段 from 表1, 表2 [where 关联条件];

内连接相当于在笛卡尔积的基础上加上了连接的条件。

当没有连接条件的时候，内连接上升为笛卡尔积。

过程用java伪代码如下：

  1. for(Object eleA : A){
  2.     for(Object eleB : B){
  3.         if(连接条件是否为true){
  4.             System.out.print(eleA+","+eleB);
  5.         }
  6.     }
  7. }

示例1：有连接条件

查询员工及所属部门

  1. mysql> select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2 on t1.team_id = t2.id;
  2. +---------------+-----------+
  3. | emp_name      | team_name |
  4. +---------------+-----------+
  5. | 路人甲Java    | 架构组    |
  6. | 张三          | 测试组    |
  7. | 李四          | java组    |
  8. +---------------+-----------+
  9. 3 rows in set (0.00 sec)
  10.  
  11. mysql> select t1.emp_name,t2.team_name from t_employee t1 join t_team t2 on t1.team_id = t2.id;
  12. +---------------+-----------+
  13. | emp_name      | team_name |
  14. +---------------+-----------+
  15. | 路人甲Java    | 架构组    |
  16. | 张三          | 测试组    |
  17. | 李四          | java组    |
  18. +---------------+-----------+
  19. 3 rows in set (0.00 sec)
  20.  
  21. mysql> select t1.emp_name,t2.team_name from t_employee t1, t_team t2 where t1.team_id = t2.id;
  22. +---------------+-----------+
  23. | emp_name      | team_name |
  24. +---------------+-----------+
  25. | 路人甲Java    | 架构组    |
  26. | 张三          | 测试组    |
  27. | 李四          | java组    |
  28. +---------------+-----------+
  29. 3 rows in set (0.00 sec)

上面相当于获取了2个表的交集，查询出了两个表都有的数据。

示例2：无连接条件

无条件内连接，上升为笛卡尔积，如下：

  1. mysql> select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2;
  2. +---------------+-----------+
  3. | emp_name      | team_name |
  4. +---------------+-----------+
  5. | 路人甲Java    | 架构组    |
  6. | 路人甲Java    | 测试组    |
  7. | 路人甲Java    | java组    |
  8. | 路人甲Java    | 前端组    |
  9. | 张三          | 架构组    |
  10. | 张三          | 测试组    |
  11. | 张三          | java组    |
  12. | 张三          | 前端组    |
  13. | 李四          | 架构组    |
  14. | 李四          | 测试组    |
  15. | 李四          | java组    |
  16. | 李四          | 前端组    |
  17. | 王五          | 架构组    |
  18. | 王五          | 测试组    |
  19. | 王五          | java组    |
  20. | 王五          | 前端组    |
  21. | 赵六          | 架构组    |
  22. | 赵六          | 测试组    |
  23. | 赵六          | java组    |
  24. | 赵六          | 前端组    |
  25. +---------------+-----------+
  26. 20 rows in set (0.00 sec)

示例3：组合条件进行查询

查询架构组的员工，3种写法

  1. mysql> select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2 on t1.team_id = t2.id and t2.team_name = '架构组';
  2. +---------------+-----------+
  3. | emp_name      | team_name |
  4. +---------------+-----------+
  5. | 路人甲Java    | 架构组    |
  6. +---------------+-----------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql> select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2 on t1.team_id = t2.id where t2.team_name = '架构组';
  10. +---------------+-----------+
  11. | emp_name      | team_name |
  12. +---------------+-----------+
  13. | 路人甲Java    | 架构组    |
  14. +---------------+-----------+
  15. 1 row in set (0.00 sec)
  16.  
  17. mysql> select t1.emp_name,t2.team_name from t_employee t1, t_team t2 where t1.team_id = t2.id and t2.team_name = '架构组';
  18. +---------------+-----------+
  19. | emp_name      | team_name |
  20. +---------------+-----------+
  21. | 路人甲Java    | 架构组    |
  22. +---------------+-----------+
  23. 1 row in set (0.00 sec)

上面3中方式解说。

方式1：on中使用了组合条件。

方式2：在连接的结果之后再进行过滤，相当于先获取连接的结果，然后使用where中的条件再对连接结果进行过滤。

方式3：直接在where后面进行过滤。

总结

内连接建议使用第3种语法，简洁：

select 字段 from 表1, 表2 [where 关联条件];

外连接

外连接涉及到2个表，分为：主表和从表，要查询的信息主要来自于哪个表，谁就是主表。

外连接查询结果为主表中所有记录。如果从表中有和它匹配的，则显示匹配的值，这部分相当于内连接查询出来的结果；如果从表中没有和它匹配的，则显示null。

最终：外连接查询结果 = 内连接的结果 + 主表中有的而内连接结果中没有的记录。

外连接分为2种：

左外链接：使用left join关键字，left join左边的是主表。

右外连接：使用right join关键字，right join右边的是主表。

左连接

语法

select 列 from 主表 left join 从表 on 连接条件;

示例1:

查询所有员工信息，并显示员工所在组，如下：

  1. mysql> SELECT
  2.         t1.emp_name,
  3.         t2.team_name
  4.     FROM
  5.         t_employee t1
  6.     LEFT JOIN
  7.         t_team t2
  8.     ON
  9.         t1.team_id = t2.id;
  10. +---------------+-----------+
  11. | emp_name      | team_name |
  12. +---------------+-----------+
  13. | 路人甲Java    | 架构组    |
  14. | 张三          | 测试组    |
  15. | 李四          | java组    |
  16. | 王五          | NULL      |
  17. | 赵六          | NULL      |
  18. +---------------+-----------+
  19. 5 rows in set (0.00 sec)

上面查询出了所有员工，员工team_id=0的，team_name为NULL。

示例2：

查询员工姓名、组名，返回组名不为空的记录，如下：

  1. mysql> SELECT
  2.         t1.emp_name,
  3.         t2.team_name
  4.     FROM
  5.         t_employee t1
  6.     LEFT JOIN
  7.         t_team t2
  8.     ON
  9.         t1.team_id = t2.id
  10.     WHERE
  11.         t2.team_name IS NOT NULL;
  12. +---------------+-----------+
  13. | emp_name      | team_name |
  14. +---------------+-----------+
  15. | 路人甲Java    | 架构组    |
  16. | 张三          | 测试组    |
  17. | 李四          | java组    |
  18. +---------------+-----------+
  19. 3 rows in set (0.00 sec)

上面先使用内连接获取连接结果，然后再使用where对连接结果进行过滤。

右连接

语法

select 列 from 从表 right join 主表 on 连接条件;

示例

我们使用右连接来实现上面左连接实现的功能，如下：

  1. mysql> SELECT
  2.         t2.team_name,
  3.         t1.emp_name
  4.     FROM
  5.         t_team t2
  6.     RIGHT JOIN
  7.         t_employee t1
  8.     ON
  9.         t1.team_id = t2.id;
  10. +-----------+---------------+
  11. | team_name | emp_name      |
  12. +-----------+---------------+
  13. | 架构组    | 路人甲Java    |
  14. | 测试组    | 张三          |
  15. | java组    | 李四          |
  16. | NULL      | 王五          |
  17. | NULL      | 赵六          |
  18. +-----------+---------------+
  19. 5 rows in set (0.00 sec)
  20.  
  21. mysql> SELECT
  22.         t2.team_name,
  23.         t1.emp_name
  24.     FROM
  25.         t_team t2
  26.     RIGHT JOIN
  27.         t_employee t1
  28.     ON
  29.         t1.team_id = t2.id
  30.     WHERE
  31.         t2.team_name IS NOT NULL;
  32. +-----------+---------------+
  33. | team_name | emp_name      |
  34. +-----------+---------------+
  35. | 架构组    | 路人甲Java    |
  36. | 测试组    | 张三          |
  37. | java组    | 李四          |
  38. +-----------+---------------+
  39. 3 rows in set (0.00 sec)

理解表连接原理

准备数据

  1. drop table if exists test1;
  2. create table test1(
  3.   a int
  4. );
  5. drop table if exists test2;
  6. create table test2(
  7.   b int
  8. );
  9. insert into test1 values (1),(2),(3);
  10. insert into test2 values (3),(4),(5);
  1. mysql> select * from test1;
  2. +------+
  3. | a    |
  4. +------+
  5. |    1 |
  6. |    2 |
  7. |    3 |
  8. +------+
  9. 3 rows in set (0.00 sec)
  10.  
  11. mysql> select * from test2;
  12. +------+
  13. | b    |
  14. +------+
  15. |    3 |
  16. |    4 |
  17. |    5 |
  18. +------+
  19. 3 rows in set (0.00 sec)

我们来写几个连接，看看效果。

示例1：内连接

  1. mysql> select * from test1 t1,test2 t2;
  2. +------+------+
  3. | a    | b    |
  4. +------+------+
  5. |    1 |    3 |
  6. |    2 |    3 |
  7. |    3 |    3 |
  8. |    1 |    4 |
  9. |    2 |    4 |
  10. |    3 |    4 |
  11. |    1 |    5 |
  12. |    2 |    5 |
  13. |    3 |    5 |
  14. +------+------+
  15. 9 rows in set (0.00 sec)
  16.  
  17. mysql> select * from test1 t1,test2 t2 where t1.a = t2.b;
  18. +------+------+
  19. | a    | b    |
  20. +------+------+
  21. |    3 |    3 |
  22. +------+------+
  23. 1 row in set (0.00 sec)

9条数据正常。

示例2：左连接

  1. mysql> select * from test1 t1 left join test2 t2 on t1.a = t2.b;
  2. +------+------+
  3. | a    | b    |
  4. +------+------+
  5. |    3 |    3 |
  6. |    1 | NULL |
  7. |    2 | NULL |
  8. +------+------+
  9. 3 rows in set (0.00 sec)
  10.  
  11. mysql> select * from test1 t1 left join test2 t2 on t1.a>10;
  12. +------+------+
  13. | a    | b    |
  14. +------+------+
  15. |    1 | NULL |
  16. |    2 | NULL |
  17. |    3 | NULL |
  18. +------+------+
  19. 3 rows in set (0.00 sec)
  20.  
  21. mysql> select * from test1 t1 left join test2 t2 on 1=1;
  22. +------+------+
  23. | a    | b    |
  24. +------+------+
  25. |    1 |    3 |
  26. |    2 |    3 |
  27. |    3 |    3 |
  28. |    1 |    4 |
  29. |    2 |    4 |
  30. |    3 |    4 |
  31. |    1 |    5 |
  32. |    2 |    5 |
  33. |    3 |    5 |
  34. +------+------+
  35. 9 rows in set (0.00 sec)

上面的左连接第一个好理解。

第2个sql连接条件t1.a>10，这个条件只关联了test1表，再看看结果，是否可以理解？不理解的继续向下看，我们用java代码来实现连接查询。

第3个sql中的连接条件1=1值为true，返回结果为笛卡尔积。

java代码实现连接查询

下面是一个简略版的实现

  1. package com.itsoku.sql;
  2.  
  3. import org.junit.Test;
  4.  
  5. import java.util.ArrayList;
  6. import java.util.Arrays;
  7. import java.util.List;
  8. import java.util.Objects;
  9. import java.util.concurrent.CopyOnWriteArrayList;
  10. import java.util.stream.Collectors;
  11.  
  12. public class Test1 {
  13.     public static class Table1 {
  14.         int a;
  15.  
  16.         public int getA() {
  17.             return a;
  18.         }
  19.  
  20.         public void setA(int a) {
  21.             this.a = a;
  22.         }
  23.  
  24.         public Table1(int a) {
  25.             this.a = a;
  26.         }
  27.  
  28.         @Override
  29.         public String toString() {
  30.             return "Table1{" +
  31.                     "a=" + a +
  32.                     '}';
  33.         }
  34.  
  35.         public static Table1 build(int a) {
  36.             return new Table1(a);
  37.         }
  38.     }
  39.  
  40.     public static class Table2 {
  41.         int b;
  42.  
  43.         public int getB() {
  44.             return b;
  45.         }
  46.  
  47.         public void setB(int b) {
  48.             this.b = b;
  49.         }
  50.  
  51.         public Table2(int b) {
  52.             this.b = b;
  53.         }
  54.  
  55.         public static Table2 build(int b) {
  56.             return new Table2(b);
  57.         }
  58.  
  59.         @Override
  60.         public String toString() {
  61.             return "Table2{" +
  62.                     "b=" + b +
  63.                     '}';
  64.         }
  65.     }
  66.  
  67.     public static class Record<R1, R2> {
  68.         R1 r1;
  69.         R2 r2;
  70.  
  71.         public R1 getR1() {
  72.             return r1;
  73.         }
  74.  
  75.         public void setR1(R1 r1) {
  76.             this.r1 = r1;
  77.         }
  78.  
  79.         public R2 getR2() {
  80.             return r2;
  81.         }
  82.  
  83.         public void setR2(R2 r2) {
  84.             this.r2 = r2;
  85.         }
  86.  
  87.         public Record(R1 r1, R2 r2) {
  88.             this.r1 = r1;
  89.             this.r2 = r2;
  90.         }
  91.  
  92.         @Override
  93.         public String toString() {
  94.             return "Record{" +
  95.                     "r1=" + r1 +
  96.                     ", r2=" + r2 +
  97.                     '}';
  98.         }
  99.  
  100.         public static <R1, R2> Record<R1, R2> build(R1 r1, R2 r2) {
  101.             return new Record(r1, r2);
  102.         }
  103.     }
  104.  
  105.     public static enum JoinType {
  106.         innerJoin, leftJoin
  107.     }
  108.  
  109.  
  110.     public static interface Filter<R1, R2> {
  111.         boolean accept(R1 r1, R2 r2);
  112.     }
  113.  
  114.     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) {
  115.         if (Objects.isNull(table1) || Objects.isNull(table2) || joinType == null) {
  116.             return new ArrayList<>();
  117.         }
  118.  
  119.         List<Record<R1, R2>> result = new CopyOnWriteArrayList<>();
  120.  
  121.         for (R1 r1 : table1) {
  122.             List<Record<R1, R2>> onceJoinResult = joinOn(r1, table2, onFilter);
  123.             result.addAll(onceJoinResult);
  124.         }
  125.  
  126.         if (joinType == JoinType.leftJoin) {
  127.             List<R1> r1Record = result.stream().map(Record::getR1).collect(Collectors.toList());
  128.             List<Record<R1, R2>> leftAppendList = new ArrayList<>();
  129.             for (R1 r1 : table1) {
  130.                 if (!r1Record.contains(r1)) {
  131.                     leftAppendList.add(Record.build(r1, null));
  132.                 }
  133.             }
  134.             result.addAll(leftAppendList);
  135.         }
  136.         if (Objects.nonNull(whereFilter)) {
  137.             for (Record<R1, R2> record : result) {
  138.                 if (!whereFilter.accept(record.r1, record.r2)) {
  139.                     result.remove(record);
  140.                 }
  141.             }
  142.         }
  143.         return result;
  144.     }
  145.  
  146.     public static <R1, R2> List<Record<R1, R2>> joinOn(R1 r1, List<R2> table2, Filter<R1, R2> onFilter) {
  147.         List<Record<R1, R2>> result = new ArrayList<>();
  148.         for (R2 r2 : table2) {
  149.             if (Objects.nonNull(onFilter) ? onFilter.accept(r1, r2) : true) {
  150.                 result.add(Record.build(r1, r2));
  151.             }
  152.         }
  153.         return result;
  154.     }
  155.  
  156.     @Test
  157.     public void innerJoin() {
  158.         List<Table1> table1 = Arrays.asList(Table1.build(1), Table1.build(2), Table1.build(3));
  159.         List<Table2> table2 = Arrays.asList(Table2.build(3), Table2.build(4), Table2.build(5));
  160.  
  161.         join(table1, table2, JoinType.innerJoin, null, null).forEach(System.out::println);
  162.         System.out.println("-----------------");
  163.         join(table1, table2, JoinType.innerJoin, (r1, r2) -> r1.a == r2.b, null).forEach(System.out::println);
  164.     }
  165.  
  166.     @Test
  167.     public void leftJoin() {
  168.         List<Table1> table1 = Arrays.asList(Table1.build(1), Table1.build(2), Table1.build(3));
  169.         List<Table2> table2 = Arrays.asList(Table2.build(3), Table2.build(4), Table2.build(5));
  170.  
  171.         join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a == r2.b, null).forEach(System.out::println);
  172.         System.out.println("-----------------");
  173.         join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a > 10, null).forEach(System.out::println);
  174.     }
  175.  
  176. }

代码中的innerJoin()方法模拟了下面的sql：

  1. mysql> select * from test1 t1,test2 t2;
  2. +------+------+
  3. | a    | b    |
  4. +------+------+
  5. |    1 |    3 |
  6. |    2 |    3 |
  7. |    3 |    3 |
  8. |    1 |    4 |
  9. |    2 |    4 |
  10. |    3 |    4 |
  11. |    1 |    5 |
  12. |    2 |    5 |
  13. |    3 |    5 |
  14. +------+------+
  15. 9 rows in set (0.00 sec)
  16.  
  17. mysql> select * from test1 t1,test2 t2 where t1.a = t2.b;
  18. +------+------+
  19. | a    | b    |
  20. +------+------+
  21. |    3 |    3 |
  22. +------+------+
  23. 1 row in set (0.00 sec)

运行一下innerJoin()输出如下：

  1. Record{r1=Table1{a=1}, r2=Table2{b=3}}
  2. Record{r1=Table1{a=1}, r2=Table2{b=4}}
  3. Record{r1=Table1{a=1}, r2=Table2{b=5}}
  4. Record{r1=Table1{a=2}, r2=Table2{b=3}}
  5. Record{r1=Table1{a=2}, r2=Table2{b=4}}
  6. Record{r1=Table1{a=2}, r2=Table2{b=5}}
  7. Record{r1=Table1{a=3}, r2=Table2{b=3}}
  8. Record{r1=Table1{a=3}, r2=Table2{b=4}}
  9. Record{r1=Table1{a=3}, r2=Table2{b=5}}
  10. -----------------
  11. Record{r1=Table1{a=3}, r2=Table2{b=3}}

对比一下sql和java的结果，输出的结果条数、数据基本上一致，唯一不同的是顺序上面不一样，顺序为何不一致，稍微介绍。

代码中的leftJoin()方法模拟了下面的sql：

  1. mysql> select * from test1 t1 left join test2 t2 on t1.a = t2.b;
  2. +------+------+
  3. | a    | b    |
  4. +------+------+
  5. |    3 |    3 |
  6. |    1 | NULL |
  7. |    2 | NULL |
  8. +------+------+
  9. 3 rows in set (0.00 sec)
  10.  
  11. mysql> select * from test1 t1 left join test2 t2 on t1.a>10;
  12. +------+------+
  13. | a    | b    |
  14. +------+------+
  15. |    1 | NULL |
  16. |    2 | NULL |
  17. |    3 | NULL |
  18. +------+------+
  19. 3 rows in set (0.00 sec)

运行leftJoin()，结果如下：

  1. Record{r1=Table1{a=3}, r2=Table2{b=3}}
  2. Record{r1=Table1{a=1}, r2=null}
  3. Record{r1=Table1{a=2}, r2=null}
  4. -----------------
  5. Record{r1=Table1{a=1}, r2=null}
  6. Record{r1=Table1{a=2}, r2=null}
  7. 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代码改进版本 

  1. package com.itsoku.sql;
  2.  
  3. import org.junit.Test;
  4.  
  5. import java.util.ArrayList;
  6. import java.util.Arrays;
  7. import java.util.List;
  8. import java.util.Objects;
  9. import java.util.concurrent.CopyOnWriteArrayList;
  10. import java.util.stream.Collectors;
  11.  
  12. import com.itsoku.sql.Test1.*;
  13.  
  14. public class Test2 {
  15.  
  16.     public static int joinBufferSize = 10000;
  17.     public static List<?> joinBufferList = new ArrayList<>();
  18.  
  19.     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) {
  20.         if (Objects.isNull(table1) || Objects.isNull(table2) || joinType == null) {
  21.             return new ArrayList<>();
  22.         }
  23.  
  24.         List<Test1.Record<R1, R2>> result = new CopyOnWriteArrayList<>();
  25.  
  26.         int table1Size = table1.size();
  27.         int fromIndex = 0, toIndex = joinBufferSize;
  28.         toIndex = Integer.min(table1Size, toIndex);
  29.         while (fromIndex < table1Size && toIndex <= table1Size) {
  30.             joinBufferList = table1.subList(fromIndex, toIndex);
  31.             fromIndex = toIndex;
  32.             toIndex += joinBufferSize;
  33.             toIndex = Integer.min(table1Size, toIndex);
  34.  
  35.             List<Record<R1, R2>> blockNestedLoopResult = blockNestedLoop((List<R1>) joinBufferList, table2, onFilter);
  36.             result.addAll(blockNestedLoopResult);
  37.         }
  38.  
  39.         if (joinType == JoinType.leftJoin) {
  40.             List<R1> r1Record = result.stream().map(Record::getR1).collect(Collectors.toList());
  41.             List<Record<R1, R2>> leftAppendList = new ArrayList<>();
  42.             for (R1 r1 : table1) {
  43.                 if (!r1Record.contains(r1)) {
  44.                     leftAppendList.add(Record.build(r1, null));
  45.                 }
  46.             }
  47.             result.addAll(leftAppendList);
  48.         }
  49.         if (Objects.nonNull(whereFilter)) {
  50.             for (Record<R1, R2> record : result) {
  51.                 if (!whereFilter.accept(record.r1, record.r2)) {
  52.                     result.remove(record);
  53.                 }
  54.             }
  55.         }
  56.         return result;
  57.     }
  58.  
  59.     public static <R1, R2> List<Record<R1, R2>> blockNestedLoop(List<R1> joinBufferList, List<R2> table2, Filter<R1, R2> onFilter) {
  60.         List<Record<R1, R2>> result = new ArrayList<>();
  61.         for (R2 r2 : table2) {
  62.             for (R1 r1 : joinBufferList) {
  63.                 if (Objects.nonNull(onFilter) ? onFilter.accept(r1, r2) : true) {
  64.                     result.add(Record.build(r1, r2));
  65.                 }
  66.             }
  67.         }
  68.         return result;
  69.     }
  70.  
  71.     @Test
  72.     public void innerJoin() {
  73.         List<Table1> table1 = Arrays.asList(Table1.build(1), Table1.build(2), Table1.build(3));
  74.         List<Table2> table2 = Arrays.asList(Table2.build(3), Table2.build(4), Table2.build(5));
  75.  
  76.         join(table1, table2, JoinType.innerJoin, null, null).forEach(System.out::println);
  77.         System.out.println("-----------------");
  78.         join(table1, table2, JoinType.innerJoin, (r1, r2) -> r1.a == r2.b, null).forEach(System.out::println);
  79.     }
  80.  
  81.     @Test
  82.     public void leftJoin() {
  83.         List<Table1> table1 = Arrays.asList(Table1.build(1), Table1.build(2), Table1.build(3));
  84.         List<Table2> table2 = Arrays.asList(Table2.build(3), Table2.build(4), Table2.build(5));
  85.  
  86.         join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a == r2.b, null).forEach(System.out::println);
  87.         System.out.println("-----------------");
  88.         join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a > 10, null).forEach(System.out::println);
  89.     }
  90. }

执行innerJoin()，输出：

  1. Record{r1=Table1{a=1}, r2=Table2{b=3}}
  2. Record{r1=Table1{a=2}, r2=Table2{b=3}}
  3. Record{r1=Table1{a=3}, r2=Table2{b=3}}
  4. Record{r1=Table1{a=1}, r2=Table2{b=4}}
  5. Record{r1=Table1{a=2}, r2=Table2{b=4}}
  6. Record{r1=Table1{a=3}, r2=Table2{b=4}}
  7. Record{r1=Table1{a=1}, r2=Table2{b=5}}
  8. Record{r1=Table1{a=2}, r2=Table2{b=5}}
  9. Record{r1=Table1{a=3}, r2=Table2{b=5}}
  10. -----------------
  11. Record{r1=Table1{a=3}, r2=Table2{b=3}}

执行leftJoin()，输出：

  1. Record{r1=Table1{a=3}, r2=Table2{b=3}}
  2. Record{r1=Table1{a=1}, r2=null}
  3. Record{r1=Table1{a=2}, r2=null}
  4. -----------------
  5. Record{r1=Table1{a=1}, r2=null}
  6. Record{r1=Table1{a=2}, r2=null}
  7. Record{r1=Table1{a=3}, r2=null}

结果和sql的结果完全一致。

扩展

表连接中还可以使用前面学过的group by、having、order by、limit。

这些关键字相当于在表连接的结果上在进行操作，大家下去可以练习一下，加深理解。

Mysql系列目录

  1. 第1篇：mysql基础知识

  2. 第2篇：详解mysql数据类型（重点）

  3. 第3篇：管理员必备技能(必须掌握)

  4. 第4篇：DDL常见操作

  5. 第5篇：DML操作汇总（insert,update,delete）

  6. 第6篇：select查询基础篇

  7. 第7篇：玩转select条件查询，避免采坑

  8. 第8篇：详解排序和分页(order by & limit)

  9. 第9篇：分组查询详解（group by & having）

  10. 第10篇：常用的几十个函数详解

mysql系列大概有20多篇，喜欢的请关注一下，欢迎大家加我微信itsoku或者留言交流mysql相关技术!

来源：https://itsoku.blog.csdn.net/article/details/101047548

相关文章