爱站程序员基地注：当前测试mysql版本：mysql5.7，编码utf8mb4
准备表和数据的脚本：
[code]-- 学生表DROP TABLE IF EXISTS `t_student`;CREATE TABLE `t_student` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`std_name` varchar(30) NOT NULL,`age` tinyint(3) unsigned NOT NULL,`class_id` int(11) unsigned NOT NULL,`gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`),KEY `idx_std_name_age_class` (`std_name`,`age`,`class_id`) USING BTREE,KEY `idx_std_class` (`class_id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;--添加测试数据的存储过程drop procedure if exists proc_insert_student;DELIMITER ;;create procedure proc_insert_student()begindeclare i int;set i=1;while(i<=10000)doinsert into t_student(std_name,age,class_id) values(CONCAT((SUBSTRING_INDEX(SUBSTRING_INDEX(\'Li Lei,Han Mei,Poli\',\',\',(i%3+1)),\',\',-1)),i), (i%120)+1 ,(i%10)+1);set i=i+1;end while;end;;DELIMITER ;-- 执行存储过程call proc_insert_student();-- 班级表DROP TABLE IF EXISTS `t_class`;CREATE TABLE `t_class` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`class_name` varchar(30) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;insert into t_class(class_name) values(\'class 1\');insert into t_class(class_name) values(\'class 2\');insert into t_class(class_name) values(\'class 3\');insert into t_class(class_name) values(\'class 4\');insert into t_class(class_name) values(\'class 5\');insert into t_class(class_name) values(\'class 6\');insert into t_class(class_name) values(\'class 7\');insert into t_class(class_name) values(\'class 8\');insert into t_class(class_name) values(\'class 9\');insert into t_class(class_name) values(\'class 10\');
驱动表和被驱动表
三种关联方式：
[code] -- sql1 right join:explain select * from t_student t1 right join t_class t2 on t1.class_id=t2.id;-- sql2 left join:explain select * from t_student t1 left join t_class t2 on t1.class_id=t2.id;-- sql3 inner join:explain select * from t_student t1 inner join t_class t2 on t1.class_id=t2.id;-- sql3 inner join 更换表顺序:explain select * from t_class t2 inner join t_student t1 on t1.class_id=t2.id;
执行结果（关联字段都有索引）：
从explain的查询顺序看到：right join时右边表先查询；left join时左边表先查询；inner join时跟前后顺序无关，在关联字段都建了索引的情况下，mysql会自动选一张数据量较小的表先查询。（如果t1表不建class_id的索引会先查询t1表，可以自行测试）。
先查询的叫驱动表，后查询的叫被驱动表。
注：执行顺序是根据explain的id列，id越大越先执行，id相等时执行前面的。第二节课有讲解。
两种循环连接算法
一. Nested-Loop Join(NLJ)嵌套循环连接算法
NLJ定义：从驱动表里循环读取每行数据，在这行数据中取到关联字段，根据关联字段被驱动表里匹配到满足条件的行，然后取出两张表的结果合集。
例：explain select * from t_student t1 inner join t_class t2 on t1.class_id=t2.id;
从以上explain可知 ：
1.t2 是驱动表先查询，t1 是被驱动表后查询。
2.使用了 NLJ 算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。
查询流程大致如下：
1.先从 t2 取出一行数据（如果 t2 表有查询过滤条件的，会从过滤结果里取出一行数据）；
2.从第 1 步的数据中，取出关联字段 id 的值，到表 t1 中与 class_id 匹配；
3.取出 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
4.循环上面三步直至 t2 数据用完。
伪代码逻辑大致如下：
[code]for (select t2.* from t_class t2) { // 第一步，从磁盘循环扫描 t_class，一共扫描10行select t1.* from t_student t1 where t1.class_id=t2.id; //第二步，拿t2的id走t1表的idx_std_class索引每次匹配到995行数据t1.* + t2.*; //第三步合并两表结果}
扫描行数计算：
整个过程中读取了t2的全表数据10行，遍历每行数据取到id字段的值， 拿着id值在t1表的索引 idx_std_class 中进行扫描，每次扫描到 995 行数据。整个过程扫描了10+995*10=9960行。
现在我删除 t_student 表的 idx_std_class 索引再测试一次。
— 删除索引脚本：
alter table t_student drop index idx_std_class;
从以上explain看到，删除索引后，数据量较大的 t_student 表成了驱动表，数据量小的 t_class 成了被驱动表。
伪代码逻辑大致如下：
[code]for (select t1.* from t_student t1) { //第一步，从磁盘循环扫描t_student，一共扫描9958行select t2.* from t_class t2 where t2.id=t1.class_id; //第二步，走t2的主键索引，每次扫描到1行数据t1.* + t2.*; //第三步合并两表结果}
t1 首先扫描到 9958 行数据，然后循环这些数据，取 class_id 的值在 t2 表中进行匹配，每次走索引匹配到1行数据，一共9958次。整个过程扫描了 9958 + 9958 = 19916 行，比上面的 9960行 性能低了一倍。所以在优化关联查询的时候需要把大表作为被驱动表，在大表的关联字段上建立索引。
结论：只有一个索引的时候，使用没索引的表作为驱动表。
二. Block Nested-Loop Join(BNL)基于块的嵌套循环连接算法
如果两个关联的字段都不建索引会怎么样？
我给 t_class 表再建一列 id2，不建立索引，并且值跟 id 一样。
alter table t_class add column id2 int(11) unsigned NOT NULL;
update t_class set id2=id;
执行sql，注意用t_class的id2关联
explain select * from t_student t1 inner join t_class t2 on t1.class_id=t2.id2;
可以看到extra列中有关键字 Using join buffer (Block Nested Loop)，
这意味着当关联字段都没有索引的时候会用 BNL 连接算法。
上面的sql查询流程大致如下：
1.把驱动表 t2 的所有数据放入 join_buffer 中。
2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比。
3. 返回满足 join 条件的数据。
join_buffer 是mysql在内存中分配的一块内存，用于BNL算法时存储驱动表数据使用。默认值256k，可由参数 join_buffer_size 设定
伪代码逻辑大致如下：
[code]List bufferList = select t2.* from t_class t2 ; //第一步，先从磁盘全表扫描t_class 10行数据，放入join_buffer 内存中for (select t1.* from t_student t1) { //第二步，从磁盘循环扫描 t_student，一共扫描9958行for (int t2RowData : bufferList ) { //第三步：从join_buffer 内存中循环拿 t2 表的 id 与 t1 表的 class_id匹配，返回满足条件的数据if (t2RowData.id = t1.class_id) {t2RowData+t1.*;}}}
扫描行数及内存判断次数：
从type列可以看到两个表都做了一次全表扫描，扫描磁盘总行数为 9958 + 10 = 9968行。另外join buffer 中的 t2 表的数据是无序的，每次扫描都要做10次判断，在内存中一共要判断10*9958=99580次。
使用BNL算法一共扫描磁盘9968行，如果使用NLJ算法会怎样？
因为没有索引，t2 表每次取1行数据与 t1 表进行匹配时都需要进行全表扫描，一共扫描 10*9958=99580行。这个几乎是BNL算法的10倍了，而且是磁盘扫描。相比之下BNL基于内存的算法会快得多。
join_buffer 如果一次存不下驱动表里的所有数据会怎么办？
如果 join_buffer 一次存不下驱动表里的所有数据就会进行分段放，也就是把驱动表里的数据分成多个片段。
再拿上面的t假设 join_buffer 只能存1000行数据，而例子中的驱动表 t2 表里有1500行，那就先往 join_buffer 中放入1000行数据，然后从 t1 表中取数据跟 join_buffer 中的数据进行对比得到结果，然后清空join_buffer ，再把剩下的500行放入join_buffer ，然后再从 t1 表里取数据与join_buffer 中的数据进行对比得到结果。
straight_join关键字
前面说到我们可以使用left join控制左表为驱动表，right join控制右表为驱动表。但是inner jion的时候只能由mysql自己判断驱动表。
straight 可以控制左表作为驱动表，同时保证inner join的连接效果。例：
explain select * from t_student t1 straight_join t_class t2 on t1.class_id=t2.id;
explain select * from t_class t2 straight_join t_student t1 on t1.class_id=t2.id;
（关联字段都有索引）
建议不要轻易使用straight_join关键字，mysql会使用优化器自动分析。利用好关联查询的规则即可。
总结：
关联查询算法规则：
1.left join时左表作为驱动表。right join时右边作为驱动表。inner join时mysql会根据索引和数据量判断，关联字段都有索引时选择数据量较小的作为驱动表（小表驱动大表），只有一个索引时选择无索引表作为驱动表（无索引驱动有索引）。
2.连接字段有索引的时候使用 NLJ 算法。
3.两个连接字段都有索引的时候，选择小表作为驱动表。
3.两个连接字段其中一个有索引的时候，选择无索引的表作为驱动表（无视大表小表）。
4.两个连接字段都没索引的时候使用 BNL 算法。
关联查询优化建议：
1.使用小表作为驱动表，小表驱动大表。
2.给关联字段加索引，让mysql做join操作时尽量选择 NLJ 算法。
3.尽量给两个关联字段都加索引，这时候mysql会选择小表作为驱动表，同时在进行循环匹配时大表（被驱动表）的索引会提高匹配效率。（如果只给大表加索引，mysql会选择大表作为驱动表）。
