怎么在mysql加入算法 mysql中如何添加数据( 四 )

关于 join buffer
可以看到相比Simple Nested-Loop Join算法，Block Nested-LoopJoin算法仅多了一个所谓的Join Buffer，为什么这样就能减少内表的扫描次数呢？下图相比更好地解释了Block Nested-Loop Join算法的运行过程：
可以看到Join Buffer用以缓存联接需要的列（所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样还可以在join buffer中放置更多的记录呢），然后以Join Buffer批量的形式和内表中的数据进行联接比较。就上图来看，记录r1 ， r2 … rT的联接仅需扫内表一次，如果join buffer可以缓存所有的外表列，那么联接仅需扫描内外表各一次，从而大幅提升Join的性能。
Block Nested-Loop Join开销
Block Nested-Loop Join极大的避免了内表的扫描次数，如果Join Buffer可以缓存外表的数据，那么内表的扫描仅需一次，这和Hash Join非常类似。但是Block Nested-Loop Join依然没有解决的是Join比较的次数，其仍然通过Join判断式进行比较。综上所述，到目前为止各Join算法的成本比较如下所示：
Block Nested-Loop Join影响
在使用 Block Nested-Loop Join(BNL) 算法时，还是可能会对被驱动表做多次扫描（尽管可能已经将驱动表中大部分关联字段数据存入join buffer）。如果这个被驱动表是一个大的冷数据表，除了会导致 IO 压力大以外，还会对 buffer pool产生严重的影响！
如果了解 InnoDB 的 LRU 算法就会知道，由于 InnoDB 对 Bufffer Pool 的 LRU 算法做了优化，即：第一次从磁盘读入内存的数据页，会先放在 old 区域。如果 1 秒之后这个数据页不再被访问了，就不会被移动到 LRU 链表头部，这样对 Buffer Pool 的命中率影响就不大。
但是，如果一个使用 BNL 算法的 join 语句，多次扫描一个冷表，而且这个语句执行时间超过 1 秒，就会在再次扫描冷表的时候，把冷表的数据页移到 LRU 链表头部。这种情况对应的，是冷表的数据量小于整个 Buffer Pool 的 3/8，能够完全放入 old 区域的情况。如果这个冷表很大，就会出现另外一种情况：业务正常访问的数据页，没有机会进入 young 区域。(导致正常业务sql查询因为没有剩余buffer pool空间进一步让磁盘IO变多而变得缓慢)
由于优化机制的存在，一个正常访问的数据页，要进入 young 区域，需要隔 1 秒后再次被访问到。但是，由于我们的 join 语句在循环读磁盘和淘汰内存页，进入 old 区域的数据页，很可能在 1 秒之内就被淘汰了。这样，就会导致这个 MySQL 实例的 Buffer Pool 在这段时间内，young 区域的数据页没有被合理地淘汰。
也就是说，这两种情况都会影响 Buffer Pool 的正常运作。大表 join 操作虽然对 IO 有影响，但是在语句执行结束后，对 IO 的影响也就结束了。但是，对 Buffer Pool 的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。
为了减少这种影响，你可以考虑增大 join_buffer_size 的值，减少对被驱动表的扫描次数!
也就是说，BNL 算法对系统的影响主要包括三个方面：可能会多次扫描被驱动表，占用磁盘 IO 资源；判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。
那么假设被驱动表全在内存中，这个时候 SNLJ 和 BNL 算法还有性能差别吗？当然是有的，由于 SNLJ 这个算法天然会对被驱动表的数据做多次访问，所以更容易将这些数据页放到 Buffer Pool 的头部，从而污染 Buffer Pool 。另外，即使被驱动表数据都在内存中，但每次查找“下一个记录的操作”，都是类似指针操作。而 BNL 算法中的 join_buffer 是数组，遍历的成本更低，从被驱动表读取一条数据去 join_buffer 中遍历。