JOIN查询流程与驱动表

`JOIN` 语句的执行

数据准备

分别创建表 `t1, t2`

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_a` (`a`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4;


CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_a` (`a`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4;

表 `t2` 中插入 `1000` 行数据

--  创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `NewProc`(INOUT `i` int)
BEGIN
	DECLARE
		i INT;
	SET i = 1;
	WHILE
		( i <= 1000 ) 
		DO INSERT INTO t2 VALUES (i, i, i);
	SET i = i + 1;
	END WHILE;
END

表 `t1` 里插入的是 `100` 行数据

INSERT INTO t1 ( SELECT * FROM t2 WHERE id <= 100 )

`JOIN` 语句的执行过程

EXPLAIN SELECT
	* 
FROM
	t1 STRAIGHT_JOIN t2 ON ( t1.a = t2.a )

若直接使用 JOIN 语句，MySQL 优化器可能会选择表 t1 或 t2 作为驱动表，这会影响我们分析 SQL 语句的执行过程。为便于分析执行过程中的性能，改用 straight_join 让 MySQL 使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去 JOIN。所以，该语句里：t1 是驱动表，t2 是被驱动表

当进行多表连接查询时，驱动表的定义为

如果指定了连接条件时，满足查询条件的记录行数少的表为驱动表
如果未指定连接条件时，那么行数少的表为驱动表
也可以通过 EXPLAIN 查看 SQL 语句的执行计划可以判断谁是驱动表，EXPLAIN 语句分析出来的第一行的表即是驱动表

`EXPLAIN` 分析语句

在这里插入图片描述
t2 的字段 a上有索引，JOIN 的过程用了该索引，t1 使用了全表扫描，该语句执行流程

从 t1 表中读入一行数据 R
从数据行 R 中，取出 a 字段到 t2 表里查找
取出 t2 表中满足条件的行，跟 R 组成一行，作为结果集一部分
重复执行步骤 1 ~ 3，直到 t1 的末尾循环结束

这个过程是先遍历 t1，然后根据从 t1 中取出的每行数据中的 a 值，去 t2 中查找满足条件的记录。形式上和我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，称之为 Index Nested-Loop Join 算法，简称 NLJ

该流程

对驱动表 t1 做了全表扫描，需扫描 100 行
对于每一行 R，根据 a 字段去 t2 查找，这是树搜索。由于构造数据一一对应，因此每次搜索过程都只扫描一行，共扫描 100 行
所以整个执行流程，总扫描行数是 200

能不能使用 `JOIN` 语句

假设不使用 JOIN，那就只能用单表查询：

select * from t1

查出 t1 所有数据，这里有 100 行。循环遍历这 100 行数据：

从每一行 R 取出字段 a 的值 $R.a
执行

select * from t2 where a = $R.a

把返回的结果和 R 构成结果集的一行

该查询过程，也扫描了 200 行，但共执行了 101 条语句，比 JOIN 多了 100 次交互。而且客户端还要自己拼接 SQL 语句和结果。这性能还不如直接 JOIN

为何要用小表驱动大表

mysql 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果

例：User 表里有 10000 条数据，Class 表里有 20 条数据

-- 驱动表为 Class 表
select * from user as u left join class as c u.userid = c.userid

这样则需要用 User 表循环 10000 次才能查询出来，而如果用 Class 表驱动 User 表则只需要循环 20 次就能查询出来

`JOIN` 查询如何选择驱动表

LEFT JOIN：左表（主表）是驱动表，右表（从表）是被驱动表
RIGHT JOIN：右表（主表）是驱动表，左表（从表）是被驱动表
INNER JOIN：mysql 会选择数据量比较小的表作为驱动表，大表作为被驱动表

使用 `JOIN` 查询注意点

使用 JOIN 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好
如果使用 JOIN 语句的话，需要让小表做驱动表
这些结论的前提是可以使用被驱动表的索引

能不能使用 `JOIN` （为何阿里不推荐使用 `JOIN`）

当可以使用被驱动表的索引时，是没问题的
否则，扫描行数就会过多。尤其是在大表上的 JOIN，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 JOIN 不推荐使用

所以判断要不要使用 JOIN，就是看 explain 结果里面，Extra 字段里面有没有出现 Block Nested Loop

如果有，则不推荐使用 JOIN
否则，可以使用 JOIN

目录

JOIN 语句的执行

数据准备

分别创建表 t1, t2

表 t2 中插入 1000 行数据

表 t1 里插入的是 100 行数据

JOIN 语句的执行过程

EXPLAIN 分析语句

能不能使用 JOIN 语句