开发大佬教你五步做好MySQLjoin语句优化

　　在MySQL的实现中，NestedLoopJoin有3种实现的算法：
　　1、SimpleNestedLoopJoin：简单嵌套循环连接
　　2、BlockNestedLoopJoin：缓存块嵌套循环连接
　　3、IndexNestedLoopJoin：索引嵌套循环连接
　　MySQL8。0。18版本推出了hashjoin的方式以替代BNLJ（缓存块嵌套循环连接）。提高非索引的join操作查询效率，这篇有关hashjoin并没有整理，以后会整理的！一、原理篇1、SimpleNestedLoopJoin
　　比如：SELECTFROMuseruLEFTJOINclasscONu。idc。userid
　　我们来看一下当进行join操作时，mysql是如何工作的：
　　当我们进行leftjoin连接操作时，左边的表是驱动表，右边的表是被驱动表
　　特点：
　　SimpleNestedLoopJoin简单粗暴容易理解，就是通过双层循环比较数据来获得结果，但是这种算法显然太过于粗鲁，如果每个表有1万条数据，那么对数据比较的次数
　　1万1万1亿次，很显然这种查询效率会非常慢。这个全是磁盘扫描！
　　因为每次从驱动表取数据比较耗时，所以MySQL即使在没有索引命中的情况下也并没有采用这种算法来进行连接操作，而是下面这种！2、BlockNestedLoopJoin
　　同样以上面的sql为例，我们看下mysql是如何工作的SELECTFROMuseruLEFTJOINclasscONu。idc。userid
　　因为每次从驱动表取一条数据都是磁盘扫描所有比较耗时。
　　这里就做了优化就是每次从驱动表取一批数据放到内存中，然后对这一批数据进行匹配操作。
　　这批数据匹配完毕，再从驱动表中取一批数据放到内存中，直到驱动表的数据全都匹配完毕。
　　这块内存在MySQL中有一个专有的名词，叫做joinbuffer，我们可以执行如下语句查看joinbuffer的大小showvariableslikejoinbuffer
　　思考，JoinBuffer缓存的对象是什么，这个问题相当关键和重要。
　　JoinBuffer存储的并不是驱动表的整行记录，具体指所有参与查询的列都会保存到JoinBuffer，而不是只有Join的列。
　　比如下面sqlSELECTa。col3FROMaJOINbONa。col1b。col2WHEREa。col20ANDb。col20
　　上述SQL语句的驱动表是a，被驱动表是b，那么存放在JoinBuffer中的列是所有参与查询的列，在这里就是（a。col1，a。col2，a。col3）。
　　也就是说查询的字段越少，JoinBuffer可以存的记录也就越多！
　　变量joinbuffersize的默认值是256K，显然对于稍复杂的SQL是不够用的。好在这个是会话级别的变量，可以在执行前进行扩展。
　　建议在会话级别进行设置，而不是全局设置，因为很难给一个通用值去衡量。另外，这个内存是会话级别分配的，如果设置不好容易导致因无法分配内存而导致的宕机问题。调整到1Msetsessionjoinbuffersize102410241024；再执行查询SELECTa。col3FROMaJOINbONa。col1b。col2WHEREa。col20ANDb。col203、IndexNestedLoopJoin
　　当我们了解BlockNestedLoopJoin算法，我们发现虽然可以将驱动表的数据放入JoinBuffer中，但是缓存中的每条记录都要和被驱动表的所有记录都匹配一遍，
　　也会非常耗时，所以我们应该如何提高被驱动表匹配的效率呢？其实很简单就是给被驱动表连接的列加上索引，这样匹配的过程就非常快，如图所示
　　上面图中就是先匹配索引看有没有命中的数据，有命中数据再回表查询这条记录，获取其它所需要的数据，但列的数据在索引中都能获取那都不需要回表查询，效率更高！
　　二、SQL示例1、新增表和填充数据表1a字段加索引b字段没加CREATETABLEt1（idintNOTNULLAUTOINCREMENTCOMMENT主键，aintDEFAULTNULLCOMMENT字段a，bintDEFAULTNULLCOMMENT字段b，PRIMARYKEY（id），KEYidxa（a））ENGINEInnoDBDEFAULTCHARSETutf8；表2createtablet2liket1；t1插入10000条数据t2插入100条数据dropprocedureifexistsinsertdata；delimiter；；createprocedureinsertdata（）begindeclareiint；seti1；while（i10000）doinsertintot1（a，b）values（i，i）；setii1；endwhile；seti1；while（i100）doinsertintot2（a，b）values（i，i）；setii1；endwhile；end；；delimiter；callinsertdata（）；2、BlockNestedLoopJoin算法示例b字段没有索引explainselectt2。fromt1innerjoint2ont1。bt2。b；执行结果idselecttypetablepartitionstypepossiblekeyskeykeylenrefrowsfilteredExtra1SIMPLEt2NULLALLNULLNULLNULLNULL100100。00NULL1SIMPLEt1NULLALLNULLNULLNULLNULL1033710。00Usingwhere；Usingjoinbuffer（BlockNestedLoop）
　　从执行计划我们可以得出一些结论：驱动表是t2，被驱动表是t1。所以使用innerjoin时，排在前面的表并不一定就是驱动表。Extra中的Usingjoinbuffer（BlockNestedLoop）说明该关联查询使用的是BNLJ算法。
　　上面的sql大致流程是：将t2的所有数据放入到joinbuffer中将joinbuffer中的每一条数据，跟表t1中所有数据进行比较返回满足join条件的数据3、IndexNestedLoopJoin算法a字段有索引EXPLAINselectfromt1innerjoint2ont1。at2。a；
　　执行结果
　　从执行计划我们可以得出一些结论：我们可以看出t1的type不在是all而是ref，说明不在是全表扫描，而是走了idxa的索引。这里并没有出现Usingjoinbuffer（BlockNestedLoop），说明走的是IndexNestedLoopJoin。
　　上面的sql大致流程是：从表t2中读取一行数据从第1步的数据中，取出关联字段a，到表t1idxa索引中查找；从idxa索引上找到满足条件的数据，如果查询数据在索引树都能找到，那就可以直接返回，否则回表查询剩余字段属性再返回。返回满足join条件的数据
　　发现这里效率最大的提升在于t1表中rows1，也就是说因为idxa索引的存在，不需要把t1每条数据都遍历一遍，而是通过索引1次扫描可以认为最终只扫描t1表一行完整数据。三、join优化总结
　　根据上面的知识点我们可以总结以下有关join优化经验：在关联查询的时候，尽量在被驱动表的关联字段上加索引，让MySQL做join操作时尽量选择INLJ算法。
　　2）小表做驱动表！
　　当使用leftjoin时，左表是驱动表，右表是被驱动表，当使用rightjoin时，右表是驱动表，左表是被驱动表，当使用join时，mysql会选择数据量比较小的表作为驱动表，
　　大表作为被驱动表，如果说我们在join的时候明确知道哪张表是小表的时候，可以用straightjoin写法固定连接驱动方式，省去mysql优化器自己判断的时间。
　　对于小表定义的明确：
　　在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是小表，应该作为驱动表。
　　3）在适当的情况下增大joinbuffer的大小，当然这个最好是在会话级别的增大，而不是全局级别。
　　4）不要用作为查询列表，只返回需要的列！
　　这样做的好处可以让在相同大小的joinbuffer可以存更多的数据，也可以在存在索引的情况下尽可能避免回表查询数据。
　　声明：公众号如需转载该篇文章，发表文章的头部一定要告知是转至公众号：后端元宇宙。同时也可以问本人要markdown原稿和原图片。其它情况一律禁止转载！
　　原文链接：https：www。cnblogs。comqdhxhzp16893154。html