mysql-联接查询-联接算法

1.Simple Nested-Loops Join算法

1.1概念

从一张表（外部表）中每次读取一条记录，然后将记录与内部表中的记录进行比较。
对于外部表R，内部表S，并且不存在索引
扫描成本O（Rn*Sn），Rn和Sn表示对应表的记录数

1.2 算法

算法如下：

For each row r in R do //从表R取出一条记录r
    For each row s in S do // 从表S取出一条记录s
        If r and s satisfy the join condition // 判断他们是否满足条件
            Then output the tuple <r, s> // 满足条件则输出记录

1.3 关于索引情况

但是当内部表对所联接的列含有索引时，Simple Nested-Loops Join算法可以利用索引的特性来进行快速匹配，此时的算法调整为如下：

For each row r in R do //从表R取出一条记录r
    lookup r in S index // 根据索引 从表S中寻找满足联接条件的记录是否存在
        If find s == r   //如果找到
           Then output the tuple <r, s> //则输出记录

对于联接的列含有索引的情况，外部表的每条记录不再需要扫描整张内部表，只需要扫描内部表上的索引即可得到联接的判断结果。
在INNER JOIN中，两张联接表的顺序是可以变换的，根据前面描述的Simple Nested-Loops Join算法，优化器在一般情况下总是选择将联接列含有索引的表作为内表。如果两张表R和S在联接列上都有索引，并且索引的高度相同，那么优化器会选择记录数少的表作为外部表，这是因为内部表的扫描次数总是索引的高度，与记录的数量无关。
结论
添加索引时需要往数据多的表加索引
具体示例：
mysql-联接查询-增加索引

1.4 复杂情况

1.4.1存在where条件，而且是多张表

SELECT * FROM T1
INNER JOIN T2 ON P1 (T1, T2 )
INNER JOIN T3 ON P2 (T2,T3)
WHERE P (T1, T2, T3)

执行时的程序可能是

FOR each row t1 in T1{
FOR each row t2 in T2 such that P1(t1,t2) {//对于记录t1和t2 满足联接条件P1
FOR each row t3 in T3 such that P2(t2,t3) {
IF P(t1,t2,t3){ //如果满足where 条件
t:=t1||t2||t3; //赋值么？
 OUTPUT t;  //输出结果
}
}
}
}

1.4.2 但是可能存在表的执行顺序优化

FOR each row  t3 in T3 {
FOR each row t2 in T2 such that P1(t2,t3) {
FOR each row t1 in T1such that P2(t1,t2) {
IF P(t1,t2,t3){ /
t:=t1||t2||t3; //
 OUTPUT t;  //输出结果
}
}
}
}

1.4.3 push-down-conditions优化
内部的条件可能会放到外部表中去判断，减少外部表循环次数，内部表循环次数也会大大减少，整体循环次数就大大降低了

P(t1,t2,t3)等价于C1(t1)&C2(t2)&C3(t3)

所以优化为

FOR each row t1 in T1 such that C1(t1){
FOR each row t2 in T2 such that P1(t1,t2) and C2(t2) {
FOR each row t3 in T3 such that P2(t2,t3) and C3(t3){
IF P(t1,t2,t3){ //如果满足where 条件
t:=t1||t2||t3; //
 OUTPUT t;  //输出结果
}
}
}
}

注意：outer join是不能优化执行顺序，还需要进行一个是否outer join的判断

2.Block Nested-Loops Join算法

2.1 简介

如果联接表没有索引时，Simple Nested-Loops Join算法扫描内部表很多次，执行效率会非常差。而Block Nested-Loops Join算法就是针对没有索引的联接情况设计的，其使用Join Buffer（联接缓存）来减少内部循环取表的次数。

2.2 概念

例如，当缓存块大小为10时
Block Nested-Loops Join算法先把对Outer Loop表(外部表)每次读取的10行
记录(准确地说是10行需要进行联接的列)放入Join Buffer中，然后在Inner Loop表(内部表)中直接匹配这10行数据。因此，对Inner Loop表的扫描减少了1/10。对于没有索引的表来说，Block Nested-Loops Join算法可以极大地提高联接的速度。
具体示例：
mysql-联接查询-增加索引

EXPLAIN select *   from   dept_emp as d inner join salaries  as s  on s.to_date=d.to_date;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  110 |   100.00 | NULL                                               |
|  1 | SIMPLE      | s     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2814 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

在 Extra 这一列中指出了使用到了join buffer。当在使用联接查询，并且没有加上索引的情况下，mysql会自动帮我们使用join buffer优化查询次数

ps：可以通过系统变量设置Join Buffer的大小