MySQLJOIN原理学习

表结构

#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;

案例一

image-20210826215732017

image-20210826224523762

explain计划中id相同,自上而下执行,从执行计划中得知test1的type级别是eq_ref,从后面的key也了解
到他用到了主键索引,rows是扫描行数,explain只是估计值,并不实际计算,此时test1表应该是扫描了100
行,因为test2全表扫到了id,知道id后就可以直接去test1表的主键索引树精确扫到这100行test1的数据了。
所以这次sql其实一共扫了200行数据。

案例二

image-20210826222820523

这个其实也和案例一一样,只不过是在index_a索引树上找的对应关系,多出的一步就是回表操作。和案例一的区别只是
一开始寻找的树是index_a树,最后匹配在树中匹配到后回表。

案例三

image-20210827135615123

image-20210827142756480

因为没有使用索引,索引全表扫描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则相反

image-20210826225119643

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

  • 概念

    将驱动表的数据读到join_buffer中,然后扫面被驱动表,将被驱动表中每一行
    数据拿出来与join_buffer中的数据对比。
    
  • 如案例三中所示,看Extra中的信息是否有Using join buffer
    image-20210827135615123

反思

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

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

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

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

Q.E.D.


一个热爱生活的95后精神小伙