MySQL 连接类型以及小表驱动大表

1. 内连接

内连接（INNER JOIN）通过设置连接条件的方式，移除查询结果中某些数据行的交叉连接。即利用条件表达式消除交叉连接的某些数据行。

内连接使用 INNER JOIN 关键字连接两张表，使用 ON 子句设置连接条件。如果没有连接条件，INNER JOIN 和 CROSS JOIN 在语法上等同，两者可以互换。

内连接的语法格式如下：

SELECT <字段名列表> FROM <表 1> INNER JOIN <表 2> [ON 子句]

• 字段名列表：要查询的字段名列表。
• <表 1>、<表 2>：要内连接的表名。
• INNER JOIN：内连接中可以省略 INNER 关键字，只用关键字 JOIN。
• ON 子句：用于设置内连接的连接条件。

INNER JOIN 也可以使用 WHERE 子句指定连接条件，但是 INNER JOIN ... ON 语法是官方推荐的标准写法，而且 WHERE 子句在某些时候影响查询性能。

2. 左外连接

内连接的查询结果都是符合连接条件的记录，而外连接将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录。

左外连接又称为左连接，使用 LEFT OUTER JOIN 关键字连接两张表，使用 ON 子句设置连接条件。

左连接的语法格式如下：

SELECT <字段名列表> FROM <表 1> LEFT OUTER JOIN <表 2> <ON 子句>

• 字段名列表：要查询的字段名列表。
• <表 1>、<表 2>：要左连接的表名。
• LEFT OUTER JOIN：左连接中可以省略 OUTER 关键字，只用关键字 LEFT JOIN。
• ON 子句：用于设置左连接的连接条件，不能省略。

在上述语法中，“表 1”为基表，“表 2”为参考表。左连接查询时，将查询出“表 1”中的所有记录和“表 2”中匹配连接条件的记录。如果“表 1”的行在“表 2”中没有匹配行，那么在返回结果中，“表 2”的字段值均为空值（NULL）。

3. 右外连接

右外连接又称为右连接，右连接是左连接的反向连接。使用 RIGHT OUTER JOIN 关键字连接两张表，使用 ON 子句设置连接条件。

右连接的语法格式如下：

SELECT <字段名列表> FROM <表 1> RIGHT OUTER JOIN <表 2> <ON 子句>

• 字段名列表：要查询的字段名列表。
• <表 1>、<表 2>：要右连接的表名。
• RIGHT OUTER JOIN：右连接中可以省略 OUTER 关键字，只用关键字 RIGHT JOIN。
• ON 子句：用于设置右连接的连接条件，不能省略。

与左连接相反，右连接以“表 2”为基表，“表 1”为参考表。右连接查询时，将查询出“表 2”中的所有记录和“表 1”中匹配连接条件的记录。如果“表 2”的行在“表 1”中没有匹配行，那么在返回结果中，“表 1”的字段值均为空值（NULL）。

4. 交叉连接

交叉连接（CROSS JOIN）一般用于返回连接表的笛卡尔积。

交叉连接的语法格式如下：

SELECT <字段名列表> FROM <表 1> CROSS JOIN <表 2> [WHERE 子句]

或

SELECT <字段名列表> FROM <表 1>, <表 2> [WHERE 子句]

• 字段名列表：要查询的字段名列表。
• <表 1>、<表 2>：要交叉连接的表名。
• WHERE 子句：用于设置交叉连接的查询条件。

注意：

• 以上两种语法的返回结果相同，但第一种语法是官方建议的标准写法。

当省略 WHERE 子句时，返回结果是两张表的笛卡尔积，返回结果的数量是两张表的数据行相乘。如果每个表有 1000 行，那么返回结果的数量有 1000 x 1000 = 1000000 行，数据量非常巨大。

5. 小表驱动大表

假设有两张表 student 和 school，对于如下 SQL：

SELECT student.name, school.name FROM student INNER JOIN school ON student.sid = school.id 

假设 student.sid 和 school.id 都存在索引，并且数据库采用的是 Nested Loop Join 算法，那么选择哪张表作为外驱表和内驱表至关重要。

• 外驱表：只遍历一次。

• 内驱表：将遍历多次。

Nested Loop Join 算法如下：

FOR ro IN '外驱表' {
    FOR ri IN '内驱表' {
        if (ro.xx == ri.xx) {
            选择（rw, rn）
        }
    }
}

假设 student 有 10 万条数据，school 有 100 条数据，如果选择 student 作为外驱表，即大表驱动小表，时间复杂度是 100000 * log(100)。而如果选择 school 作为外驱表，即小表驱动大表，时间复杂度是 100 * log(100000)，可见两者根本不是一个数量级。

DROP TABLE IF EXISTS student;

DROP TABLE IF EXISTS school;

CREATE TABLE IF NOT EXISTS student
(
    sid  BIGINT UNSIGNED NOT NULL,
    name VARCHAR(1024),
    KEY idx_sid (sid)
);

CREATE TABLE IF NOT EXISTS school
(
    id   BIGINT UNSIGNED NOT NULL,
    name VARCHAR(1024),
    PRIMARY KEY uk_id (id)
);

TRUNCATE TABLE student;

TRUNCATE TABLE school;

SET @@cte_max_recursion_depth = 1000000;

SET @SCHOOLS = 2000;

INSERT INTO student(sid, name)
WITH RECURSIVE cte AS (SELECT 1 AS n
                       UNION ALL
                       SELECT n + 1
                       FROM cte
                       WHERE n < @@cte_max_recursion_depth)
SELECT IF(cte.n % @SCHOOLS = 0, @SCHOOLS, cte.n % @SCHOOLS), CONCAT('student-', cte.n)
FROM cte;

INSERT INTO school(id, name)
WITH RECURSIVE cte AS (SELECT 1 AS n
                       UNION ALL
                       SELECT n + 1
                       FROM cte
                       WHERE n < @SCHOOLS)
SELECT cte.n, CONCAT('school-', cte.n)
FROM cte;

EXPLAIN
SELECT student.name, school.name
FROM student
         STRAIGHT_JOIN school ON student.sid = school.id;

EXPLAIN
SELECT student.name, school.name
FROM school
         STRAIGHT_JOIN student ON student.sid = school.id;

在 MySQL 中，优化器将根据表大小，索引使用情况等多种因素选择 JOIN 操作的执行顺序，以达到最优执行计划。使用 STRAIGHT_JOIN 可以强制 MySQL 按照手动指定的 JOIN 顺序执行，从而达到更好的性能。

参考文档

• https://c.biancheng.net/view/7407.html