MySQL之JOIN原理学习

MySQL之JOIN原理学习

表结构

#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）算法

• 概念

  一次次循环地从驱动表中读取数据，根据关联数据在被驱动表里取出满足条件的行，然后取出满足要求的集合。
  

• 例如上面中的案例一和案例二，mysql优化器一般会选择用小表来驱动大表。并不是谁在前，谁就是大表，如下图。优化器一般会选择小表做驱动表。

• 使用NLJ算法，一般join语句中，如果执行计划Extra中未出现Using join buffer则表示使用的是NLJ算法

• NLJ算法只针对join，如果是left join则左标是驱动表，右表是被驱动表，right join则相反

基于块的嵌套循环连接Block Nested-Loop-Join(BNL)算法

• 概念

  将驱动表的数据读到join_buffer中，然后扫面被驱动表，将被驱动表中每一行
  数据拿出来与join_buffer中的数据对比。
  

• 如案例三中所示，看Extra中的信息是否有Using join buffer
  

反思

• NLJ是在磁盘上做关联，因为有索引的关系，值对比关联才会快

• BLJ是将一张表的数据放在内存中，然后将另一张表的数据取出来一一对比，在内存中的操作还是比较快的，但不如索引，因为B+本身就是排序树的原因。

• 将案例三使用NLJ算法来连接会怎么样

  因为b列不是索引列，所以每次操作都会涉及到磁盘IO操作，然后将test2的数 
  据拿到test1，
  再一条一条取出test1表的数据对比，至始至终都没有用到索引，都是底层IO硬 
  搜，所以速度
  肯定会慢四。。。。
  所以有索引的还是走NLJ算法快，没索引的走BNL算法，但也要注意驱动表的数 
  据量，太大的话
  ，内存块一次放不下也麻烦。