通过java模拟mysql join原理

mysql join连接查询时究竟是如何关联两张表的，我们通过java来模拟一个mysql join的过程，就很容易理解了。

这里我们就不和mysql打交道了，直接用java模拟，假设有table1和table2两张表，table1只有一个int类型的字段a, table2只有一个int类型的字段b, 我们分别用如下两个类来模拟。后面的关联查询时我们用a字段和b字段进行关联。等价的sql语句为

select t1.*, t2.* from t1 (left) join t2 on t1.a = t2.b;

//表1的数据结构，只有一个字段a
public class Table1 {
    int a;

    public Table1(int a) {
        this.a = a;
    }

    public static Table1 build(int value) {
        return new Table1(value);
    }

    @Override
    public String toString() {
        return "Table1{" +
                "a=" + a +
                '}';
    }
}

//表2的数据结构，只有一个字段b
public class Table2 {
    int b;

    public Table2(int b) {
        this.b = b;
    }

    public static Table2 build(int value) {
        return new Table2(value);
    }

    @Override
    public String toString() {
        return "Table2{" +
                "b=" + b +
                '}';
    }
}

//join表的数据结构
public class JoinTable {
    Table1 table1;
    Table2 table2;

    public static JoinTable build(Table1 table1, Table2 table2) {
        JoinTable jt = new JoinTable();
        jt.table1 = table1;
        jt.table2 = table2;
        return jt;
    }

    @Override
    public String toString() {
        return "JoinTable{" +
                "table1=" + table1 +
                ", table2=" + table2 +
                '}';
    }
}

模拟inner join和left join的代码如下，代码中有注释，而且比较好理解，这里不过多解释了。

public class Test {
    public static void main(String[] args) {
        //生成两张表的数据
        List<Table1> table1Datas = Arrays.asList(
            Table1.build(1), Table1.build(2), Table1.build(3)
        );
        List<Table2> table2Datas = Arrays.asList(
                Table2.build(3), Table2.build(4), Table2.build(5)
        );

        //内连接
        List<JoinTable> innerJoinResult = innerJoin(table1Datas, table2Datas);
        System.out.println(innerJoinResult);

        //左外连接
        List<JoinTable> leftJoinResult = leftJoint(table1Datas, table2Datas);
        System.out.println(leftJoinResult);
    }

    //左外连接
    private static List<JoinTable> leftJoint(List<Table1> table1Datas, List<Table2> table2Datas) {
        //先按照innerJoin把数据筛选出来
        List<JoinTable> joinTables = innerJoin(table1Datas, table2Datas);
        List<Table1> existedTable = joinTables.stream().map(jt -> jt.table1).collect(Collectors.toList());
        //因为是lefgtJoin，所以遍历table1中的数据，如果不包含在joinTables中，就加入到joinTables中
        for (Table1 table1Data : table1Datas) {
            if (!existedTable.contains(table1Data)) {
                joinTables.add(JoinTable.build(table1Data, null));
            }
        }
        //如果还有where条件，就把joinTables结果再筛选一遍
        for (JoinTable joinTable : joinTables) {
            //where条件筛选，此处略过。
        }
        return joinTables;
    }

    //内连接
    private static List<JoinTable> innerJoin(List<Table1> table1Datas, List<Table2> table2Datas) {
        List<JoinTable> result = new ArrayList<>();
        //嵌套循环遍历table1和table2的数据，找到符合连接条件的，加入结果集。
        for (Table1 table1Data : table1Datas) {
            for (Table2 table2Data : table2Datas) {
                if (table1Data.a == table2Data.b) {
                    result.add(JoinTable.build(table1Data, table2Data));
                }
            }
        }
        //如果还有where条件，就把joinTables结果再筛选一遍
        for (JoinTable joinTable : result) {
            //where条件筛选，此处略过。
        }
        return result;
    }
}

运行结果如下图：

可以看到，内连接就是两层for循环，找到符合连接条件的数据，加入到结果集中，左个连接是在内连接选出的结果基础上，把table1有但是结果集中没有的数据再加入到结果集中。

当然如果按照我们上面的方式来join，效率是很低的，因为我需要把table1中的每个数据读取出来，再全表扫描table2，找到符合条件的数据。table1有n条数据，table2就要全表扫描n次。

mysql对此进行了优化，引入了join buffer，mysql会先将table1中的数据放到一个缓冲区join buffer中，然后对从表table2进行遍历，每遍历到一条数据就和join buffer中的数据进行比较，找到符合条件的数据。这样从表table2只需要遍历一次即可。当join_buffer足够大的时候，大到可以存放table1所有数据，那么从表只需要全表扫描一次（即只需要一次全表io读取操作）。

用java来模拟一个简单的join buffer过程如下。

//模拟关联查询中的join buffer
public class Test {

    //主表的缓冲区大小
    private static final int bufferSize = 10;

    public static void main(String[] args) {
        //生成两张表的数据
        List<Table1> table1Datas = Arrays.asList(
            Table1.build(1), Table1.build(2), Table1.build(3)
        );
        List<Table2> table2Datas = Arrays.asList(
                Table2.build(3), Table2.build(4), Table2.build(5)
        );

        //内连接
        List<JoinTable> innerJoinResult = innerJoin(table1Datas, table2Datas);
        System.out.println(innerJoinResult);

    }

    //内连接
    private static List<JoinTable> innerJoin(List<Table1> table1Datas, List<Table2> table2Datas) {
        List<JoinTable> result = new ArrayList<>();
        int size = table1Datas.size();
        int fromIndex = 0;
        int toIndex = Math.min(bufferSize, size);
        while (fromIndex < size) {
            //模拟把table1中的数据放入join buffer
            List<Table1> bufferedTable1 = table1Datas.subList(fromIndex, toIndex);
            result.addAll(blockNestedLoop(bufferedTable1, table2Datas));
            toIndex = Math.min(fromIndex + bufferSize, size);
            fromIndex = toIndex;
        }
        //如果还有where条件，就把joinTables结果再筛选一遍
        for (JoinTable joinTable : result) {
            //where条件筛选，此处略过。
        }
        return result;
    }

    //遍历从表，和缓冲区中的主表数据进行对比，找到符合条件的数据。
    private static List<JoinTable> blockNestedLoop(List<Table1> bufferedTable1, List<Table2> table2) {
        List<JoinTable> result = new ArrayList<>();
        for (Table2 t2 : table2) {
            for (Table1 t1 : bufferedTable1) {
                if (t2.b == t1.a) {
                    result.add(JoinTable.build(t1, t2));
                }
            }
        }
        return result;
    }
}

 

参考

https://mp.weixin.qq.com/s?__biz=MzA5MTkxMDQ4MQ==&mid=2648933325&idx=1&sn=33274227db275a3570e1e43ccdd4f49c&scene=19#wechat_redirect