MySQL各种关联查询的底层算法最终归为一种

MySQL的普通联接、内联接、外联接、左联接、右联接、自然联接……，算法层的处理，到最终只有一种联接——普通联接。

 先简单的描述一下常用联接：

1. 普通联接

SELECT * FROM T1, T2 WHERE T1.id=T2.id AND T1.id=1

很多文章也说这是 INNER JOIN 的简写，但 MySQL 文档中并未如此说，只是说在无联合条件下是语义相同的，内联接表达式会被替换为 T1,T2。两者都可以对指定的表计算出笛卡尔乘积（无 WHERE 限制时）。

2. 内联接

INNER JOIN，也可简写为 JOIN，所有 T1 INNER JOIN T2 ON p(T1,T2) 形式的内联接表达式被替换为 T1, T2、p(T1, T2) ，并根据 WHERE 条件（或嵌入连接的联接条件）联接为一个连接。p1(T1,T2)是一些联接条件(表达式)。

SELECT * FROM T1 INNER JOIN T2 ON p1(T1,T2)
    INNER JOIN T3 ON p2(T2,T3) WHERE p(T1,T2,T3)

3. 左外联接

也就是左联接，LEFT JOIN，而 LEFT OUTER JOIN 语法的目的只是为了保持与 ODBC 的兼容性。

SELECT * FROM T1 LEFT JOIN 
    (T2 LEFT JOIN T3 ON p(T2,T3)) 
    ON p(T1,T2) WHERE p(T1,T2,T3)

4. 右外联接

也就是右联接，RIGHT JOIN，RIGHT OUTER JOIN，带右外联接操作的查询被转换为只包含左联接操作的等效查询。官方建议使用 LEFT JOIN。

其底层对右联接转为左联接：

TABLE_LIST *st_select_lex::convert_right_join()
{
  TABLE_LIST *tab2= join_list->pop();
  TABLE_LIST *tab1= join_list->pop();
  DBUG_ENTER("convert_right_join");

  join_list->push_front(tab2);
  join_list->push_front(tab1);
  tab1->outer_join|= JOIN_TYPE_RIGHT;

  DBUG_RETURN(tab1);
}

5. 交叉联接

CROSS JOIN，也称笛卡尔乘积。在 MySQL 中，CROSS JOIN 语法上等价于 INNER JOIN（它们可以彼此替代。在标准 SQL 中，它们不等价。INNER JOIN 结合 ON 子句使用；CROSS JOIN 用于其它地方），详见文档，没什么好说的。

6. 联合查询

UNION ALL 与 UNION，将多条查询的结果合并，UNION 会对数据去重，这里不详述的。

7. 全联接

FULL JOIN，MySQL 不支持全联接。忽略（其他的骚操作一并忽略）。

 

所以，这么多联接、就剩下了 普通联接和左联接。

 

嵌套算法：

1. 普通联接（内联接）

普通联接和内联接语义相同，其嵌套联接算法将按下面的方式查询：

// 其中 p(t1,t2,t3)是表 T1,T2,T3 的列的一个条件
FOR each row t1 in T1 {
    FOR each row t2 in T2 such that p1(t1,t2) {
        FOR each row t3 in T3 such that p2(t2,t3) {
            // 根据 where 条件判断
            IF p(t1,t2,t3) {
                t:=t1||t2||t3;
                OUTPUT t;
            }
        }
    }
}

对于有内联接的查询，优化器可以选择不同的嵌套环顺序。例如：优化器评估 T3 比 T2 的扫描行少，则会调整嵌套环的顺序。

FOR each row t1 in T1
    FOR each row t3 in T3 such that p2(t2,t3)
        FOR each row t2 in T2 such that p1(t1,t2)

如果 p(T1,T2,T3) = C1(T1) AND C2(T2) AND C3(T3)，则 MySQL 实际使用了下面的嵌套环方案来执行带内联接得到查询：

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) {
                t:=t1||t2||t3;
                OUTPUT t;
            }
        }
    }
}

如果 C1(T1) 是一个限制性很强的条件，下推条件可以大大降低从表 T1 传递到内环的行数。结果是大大加速。对内嵌套环下推的条件不能直接用于带外联接的查询。

 

2. 左联接

对于有外联接的查询，优化器可以只选择这样的顺序：外表的环优先于内表的环。只可能有一种嵌套顺序。

SELECT * FROM T1 LEFT JOIN 
    (T2 LEFT JOIN T3 ON p(T2,T3))
    ON p(T1,T2) WHERE p(T1,T2,T3)

// 该查询算法
FOR each row t1 in T1 {
    BOOL f1:=FALSE;
    FOR each row t2 in T2 such that p1(t1,t2) {
        BOOL f2:=FLASE;
        FOR each row t3 in T3 such that p2(t2,t3) {
            //是否符合 where 条件
            IF p(t1,t2,t3) {
                t:=t1||t2||t3;
                OUTPUT t;
            }
            f2=TRUE;
            f1=TRUE;
        }
        //如果表 T3 无匹配行
        IF (!f2) {
            //如果满足 T2 表的条件，T3 的列为 NULL
            IF p(t1,t2,NULL) {
                t:=t1||t2||NULL;
                OUTPUT t;
            }
            f1=TRUE;
        }
    }
    IF (!f1) {
        IF p(t1,NULL,NULL) {
            t:=t1||NULL||NULL;
            OUTPUT t;
        }
    }
}

如果同时有 外联接和内联接，如下，优化器将评估两个不同的嵌套：

SELECT * FROM T1 LEFT JOIN (T2,T3) 
      ON p1(T1,T2) AND p2(T1,T3) 
      WHERE p(T1,T2,T3)
//嵌套为：
//第一种如内联接方式的 T3,T2 顺序优化器可互换，第二种如下：
FOR each row t1 in T1 {
    f1:=FALSE;
    FOR each row t2 in T2 such that p1(t1,t2) {
        FOR each row t3 in T3 such that p2(t1,t3) {
            IF p(t1,t2,t3) {
                t:=t1||t2||t3;
                OUTPUT t;
            }
        }
    }
    IF (!f1) {
        IF p(t1,NULL,NULL) {
            t:=t1||NULL||NULL;
            OUTPUT t;
        }
    }
}

在书写联接查询时可将 LEFT JOIN 替换成 INNER JOIN，那么优化器会自行评估嵌套环顺序，或直接按普通联接方式查询。 普通联接会过滤未匹配到的表中的行，因此这两种联接得到的结果可能会不一致。

假如使用了 LEFT JOIN ，而 where 条件中会过略 NULL，则外联接被内联接代替。如：T2 表中对 group_id 进行了筛选，则匹配 T2 表中为 NULL 值的行就被过滤了，优化器评估该查询拒绝 NULL 值，会被内联接代替。

SELECT * FROM T1 LEFT JOIN T2 ON T1.id=T2.id 
    WHERE T2.group_id > 1

 

在 MySQL 中，诸多联接，最终这些联接归为普通联接一种；

但是，如果像分类统计型的必须要所有的行（不管是否匹配到数据行，未匹配到的列则用 NULL 填充），那还是需要 LEFT JOIN，但要注意 where 条件。