这一篇文章就来介绍一下关联查询的优化，文章有点长，请耐心看完，有问题欢迎讨论指正。
1 关联查询的算法特性总结
要想弄懂关联查询的优化，就必须先知道关联查询相关的算法：
|
Join算法 |
解释 |
|
Simple Nested-Loop Join算法 |
遍历驱动表中的每一行，每一行再到被驱动表中全表扫描，如果满足关联条件，则返回结果 |
|
Index Nested-Loop Join算法 |
遍历驱动表中的每一行，都通过索引找到被驱动表中关联的记录，如果满足关联条件，则返回结果 |
|
Block Nested-Loop Join算法 |
把驱动表的数据读入到 join_buffer 中，把被驱动表每一行取出来跟 join_buffer 中的数据做对比，如果满足 join 条件，则返回结果 |
|
Hash Join算法 |
将驱动表的数据加载到内存中构建哈希表，然后逐行读取被驱动表的数据，并通过哈希函数将连接条件的列的值映射为哈希值，查找匹配的哈希值，最后返回匹配的结果给客户端，跟Block Nested-Loop Join算法类似，但是不需要将被驱动表的数据块写入内存或磁盘，更少的IO以及更节省资源 |
|
Batched Key Access算法 |
将驱动表中相关列放入 join_buffer 中 批量将关联字段的值发送到 Multi-Range Read(MRR) 接口 MRR 通过接收到的值，根据其对应的主键 ID 进行排序，然后再进行数据的读取和操作 返回结果给客户端 |
2 Simple Nested-Loop Join算法
图片
循环驱动表中的每一行
再到被驱动表找到满足关联条件的记录
因为关联字段没索引，所以在被驱动表里的查询需要全表扫描
这种方法逻辑简单，但是效率很差
比如驱动表数据量是 m，被驱动表数据量是 n，则扫描行数为 m * n
当然，好在，MySQL也没有采用这种算法，即使关联字段没索引，也会采用Block Nested-Loop Join或者Hash Join，等下会细说。
3 Index Nested-Loop Join算法
刚才我们说的是关联字段没索引的情况，假如关联字段有索引，就会采用Index Nested-Loop Join算法（一般简写成：NLJ）
图片
一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里，通过索引匹配，取出满足条件的行，然后取出两张表的结果合集。
为了方便理解，我们会结合实验进行讲解，先来创建测试表并写入测试数据：
use martin;
drop table if exists t1;
CREATE TABLE `t1` (
`id` int NOT NULL auto_increment,
`a` int DEFAULT NULL,
`b` int DEFAULT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
COMMENT '记录更新时间',
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
drop procedure if exists insert_t1;
delimiter ;;
create procedure insert_t1()
begin
declare i int;
set i=1;
while(i
