关联查询，左连接，inner join笔记，BNL，NLJ

文章目录left join的最大值和最小值3个表的inner join关联查询时的is_del处理cross join(full join)NLJ 性能高BNL 性能低blj会导致什么问题?left join的最大值和最小值假设左表m条右表n条最小值是m 当一条也匹配不到右表时或者右表中没有多条匹配左表时最大值是mn-1 当左表的一条匹配到右表所有时所以范围是 m 到 mn-13个表的inner join直接inner join结果容易不正确。要先inner join一个拿结果再和第三个表inner join关联查询时的is_del处理一般来说是不用改的如果关联表无数据条件自然不生效。 但是有种特列is_del0。 解决方案1、AND(user.is_del0ORuser.idISnull)2、这个条件加到关联的条件里面?# 好像也不是很好。cross join(full join)连接有几种方式共6种。cross join这种不太常见但是也有用。例如部门用户 测试表。缺点是乘积的关系数据量非常大两张1万的表就能产生1亿的数据。NLJ 性能高NLJ(Nested-Loop Join)(嵌套循环连接)这是MySQL中最理想、也是最高效的连接方式通常发生在被驱动表的关联字段有索引的情况下。工作原理MySQL会先读取驱动表通常是小表的一行数据然后拿着这行数据的关联字段去被驱动表大表里通过索引快速查找匹配的行。匹配成功后就返回结果接着再去取驱动表的下一行循环往复。打个比方就像你在Excel里用VLOOKUP函数。拿着A表的一个名字去B表里瞬间定位并查出对应的电话查完一个再查下一个。性能表现因为走了索引查询速度非常快。如果看到执行计划EXPLAIN里被驱动表的type是ref或eq_ref就说明用到了NLJ。BNL 性能低BNL(Block Nested-Loop Join)(块嵌套循环连接)当被驱动表的关联字段没有索引时MySQL就会退而求其次使用BNL算法。工作原理MySQL不会傻乎乎地逐行去全表扫描被驱动表。它会先把驱动表的一批数据比如100行读取到内存中的一块缓存区叫做JoinBuffer里。然后去全表扫描被驱动表把被驱动表的每一行拿出来和内存缓存区里的这100行数据做批量匹配。打个比方就像考试时的“开卷查找”。你把要查的10个知识点驱动表数据抄在一张小纸条上JoinBuffer然后翻开课本被驱动表从头到尾看每看到一行就赶紧和纸条上的10个知识点对比一遍。这样只用翻一遍课本就能完成10个知识点的查找。性能表现虽然比完全没有优化的全表扫描要好但因为它本质上还是要扫描被驱动表的全表并且极其消耗CPU在内存中进行大量的比对计算所以性能远不如NLJ。在EXPLAIN的Extra列中通常会看到Usingjoinbuffer(BlockNestedLoop)的提示。版本补充HashJoin值得一提的是在MySQL8.0.18及以后的版本中如果遇到大表关联且没有索引的情况MySQL已经不再使用BNL而是引入了更高效的HashJoin哈希连接算法来替代它进一步提升了无索引关联查询的性能。blj会导致什么问题?有一道常见的面试题左表几千条、右表几万条查询的速度居然达到了分钟级按道理说这点数据库量应该很快就能查出。后来找到了问题右表关联字段无索引所以做的bnl就非常慢了。因为相当于不但全表扫描而且全表扫描了很多次。解决方案右表关联字段加索引问题解决。