MySQL之JOIN原理学习
表结构
代码语言：javascript复制#test1表100w数据，test2表100条数据
CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `test2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;案例一
explain计划中id相同，自上而下执行，从执行计划中得知test1的type级别是eq_ref,从后面的key也了解
到他用到了主键索引，rows是扫描行数，explain只是估计值，并不实际计算，此时test1表应该是扫描了100
行，因为test2全表扫到了id，知道id后就可以直接去test1表的主键索引树精确扫到这100行test1的数据了。
所以这次sql其实一共扫了200行数据。案例二
这个其实也和案例一一样，只不过是在index_a索引树上找的对应关系，多出的一步就是回表操作。和案例一的区别只是
一开始寻找的树是index_a树，最后匹配在树中匹配到后回表。案例三
因为没有使用索引，索引全表扫描test2，将b列的值扫出来都放入内存区中，内存中就不是按照B 树这
种结构存放了，它是无序的。然后将test1表的每一行拿出来去与join_buffer中的值做对比。所以最
坏情况可能是总共对比100*1000000次（因为无序），俩个表扫描的数据行数分别是1000000 100。一个内存块默认大小256KB,驱动表数据太多怎么办？
在第一个内存块的数据都与test1的一条数据对比完后，清空内存块，将剩余数据再放入内存卡，
出现内存块一次性放不数据的情况，对比很费时。嵌套循环连接Nested-Loop-Join（NLJ）算法
概念
代码语言：javascript复制一次次循环地从驱动表中读取数据，根据关联数据在被驱动表里取出满足条件的行，然后取出满足要求的集合。例如上面中的案例一和案例二，mysql优化器一般会选择用小表来驱动大表。并不是谁在前，谁就是大表，如下图。优化器一般会选择小表做驱动表。使用NLJ算法，一般join语句中，如果执行计划Extra中未出现Using join buffer则表示使用的是NLJ算法NLJ算法只针对join，如果是left join则左标是驱动表，右表是被驱动表，right join则相反
基于块的嵌套循环连接Block Nested-Loop-Join(BNL)算法
概念
代码语言：javascript复制将驱动表的数据读到join_buffer中，然后扫面被驱动表，将被驱动表中每一行
数据拿出来与join_buffer中的数据对比。如案例三中所示，看Extra中的信息是否有Using join buffer
反思
NLJ是在磁盘上做关联，因为有索引的关系，值对比关联才会快
BLJ是将一张表的数据放在内存中，然后将另一张表的数据取出来一一对比，在内存中的操作还是比较快的，但不如索引，因为B 本身就是排序树的原因。
将案例三使用NLJ算法来连接会怎么样
因为b列不是索引列，所以每次操作都会涉及到磁盘IO操作，然后将test2的数
据拿到test1，
再一条一条取出test1表的数据对比，至始至终都没有用到索引，都是底层IO硬
搜，所以速度
肯定会慢四。。。。
所以有索引的还是走NLJ算法快，没索引的走BNL算法，但也要注意驱动表的数
据量，太大的话
，内存块一次放不下也麻烦。
