在 MySQL 中，使用 join 语句的时候会涉及到驱动表和被驱动表，MySQL 会自动为你优化，选择数据量较小的表作为驱动表。在分析性能的时候，可以使用straight_join 替换 join 可以便于我们分析执行中的性能问题。
1 | select * from table1 staight_join table2 on (table1.name=table2.name) |
上面这条 SQL 语句中，table1 是驱动表，table2 是被驱动表。
这条语句的执行流程是：
- 从表table1 中读入一行数据 R；
- 从数据R中，取出name字段到表table2里去查找
- 取出表table2中满足条件的行，跟R组成一行，放到结果集中
- 重复以上1-3步骤，直到table1的末尾循环结束。
这个过程类似我们写程序的嵌套循环，如果可以用得上被驱动表的索引，这种算法就称为 “Index Nested-Loop Join”，简称 NLJ。
假设table1和table2各有100行数据。
在table2 的name字段有索引的情况下，那么总扫描行数就是200。扫描table1的每一行拿出来对比，然后在table2中寻找，因为索引的缘故，可以粗略算为1行就找到。在这个join语句中，驱动表走的是全表扫描，被驱动表走的是树搜索。
假设被驱动表的行数是M。树搜索的复杂度为 lgM，如果考虑到回表，那么在驱动表上查询每一行的复杂度为 2 * lgM, 总复杂度 N + N * 2 * lgM。 (lgM 表示以2为底的对数)
显然，N对扫描行数的影响更大（N为驱动表的行数）。N扩大1000倍，整个式子只扩大1000倍，而 M（M为被驱动表的行数）扩大1000倍，整个式子扩大不到10倍。
结论：
如果使用join语句，需要让小表来做驱动表。
在table2 的name字段没有索引的情况下，那么总扫描行数就是100 * 100行。
MySQL 会使用一个名为 Block Nested-Loop Join 的算法，流程：
- 把表 table1 的数据读入到线程内存的 join_buffer 中
- 扫描表 table2， 把table2中的每一行取出来，跟 join_buffer 中的数据作对比，满足join条件的，放入结果集中。
另外 MySQL 还有另一种算法叫做 Simple Nested-Loop Join， 只不过他不会利用 join buffer 。一个是一次加载，后面的操作只需要从内存读取，一个是一批一批从硬盘加载，速度肯定是内存里的快。
如果 table1 和 table2 都很大，而 MySQL 选择了较小的那个表作为驱动表，但是还是无法一次性把数据读到内存，那么它就会选择分批来读取到内存。我们可以增大join_buffer 的大小，来减少分批加载的次数。
结论：
如果使用join语句，在没有索引的情况下，也建议使用小表来做驱动表。
使用 Join 的原则：
- 如果可以使用 Index Nested-Loop Join 算法，就是可以用得上被驱动表上的索引，就可以使用 Join
- 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。
小表指什么？
更准确的说，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据小的那个表，就是“小表”，应该作为驱动表。
如果一定要使用Join可以如何优化
- Multi-Range Read优化（MRR）这个优化的目的就是尽可能使用顺序读盘。
对于 MySQL 来说，数据存储在磁盘上，可能在cpu、内存之间花费的时间和从磁盘读取的时间来说就相差很大了。
如果随着name的递增顺序查询的话，id的值就会变成随机的，那么就会出现随机访问，性能相对较差。虽然“按行差”这个机制不能改，但是调整查询的顺序，还是可以加速的。
因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。（想到这一点，innodb 必须要有主键字段常见为id，可见 id 也能表示数据的磁盘顺序）
MRR开启优化： set optimizer_switch="mrr_cost_based=off"
MRR能够提升性能的核心在于，查询语句在索引name上做的一个范围查询，可以得到足够多的主键id。这样通过排序后，再去主键索引查数据，就能体现出“顺序性”的优势。
- Batched Key Access（BKA算法）
BKA算法其实是改进的 NLJ 算法，在 BNL 算法中，使用到了 join buffer 优化， 但是 BNL 本来就比 NLJ 慢，所以就在 NLJ中也加入了 join_buffer 来缓存数据。
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
通过以上语句开启 BKA 算法。 BKA算法依赖于 MRR，所以上面也开启了 MRR 的参数。
- BNL 算法优化
使用 Block Nested-Loop Join（BNL）算法时，可能会对被驱动表做多次扫描。如果这个被驱动表是一个大的冷数据表，除了会导致IO压力大以外，还会影响缓存。
InnoDB对Buffer Pool的LRU算法做了优化，即：第一次从磁盘读入内存的数据页，会先放在 old 区域。如果1秒之后这个数据也不再被访问了，就不会被移动到 LRU链表的头部，这样对 Buffer Pool 的命中率影响就不大。
但是，如果一个使用 BNL 算法的 join 语句，多次扫描一个冷表，而且这个语句执行时间超过1秒，就会再次扫描冷表的时候，把冷表的数据页移动到 LRU 链的头部。这种情况对应的是冷表的数据量较小，可以完全放入 old 区域的情况。
如果冷表很大，就会出现另一种请款：业务正常访问的数据也，没有机会进入 young 区域。
由于这种机制的存在，一个正常访问的数据页，要进入 young 区域，需要隔1秒后再次被访问到。但是，由于我们的join语句在循环读取磁盘和淘汰页内存，这样正常访问的数据页很可能在1秒以内就被淘汰了。
以上两种情况都会影响 Buffer Pool 的正常工作。
结论：
大表join操作虽然对IO有影响，但是在语句执行结束后，对IO的影响也结束了。但是，对 Buffer Pool的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。
为了减少这种影响，可以考虑增大 join_buffer_size 的值，减少被驱动表的扫描次数。
总结：BNL 算法对系统的影响：
- 可能会多次扫描被驱动表占用IO资源
- 判断join条件需要执行M*N次对比（M、N分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源
- 可能会导致Buffer Pool 的热数据被淘汰，影响内存命中率。
在执行语句前，使用 explain 查看是否会使用 BNL 算法。
- BNL 优化方法
- 通过建立临时表，然后在临时表上建立索引，将 BNL 转为 BKA 算法。
- 扩展-hash join， 因为MySQL 的优化器和执行器不支持哈希join，所以我们可以在业务端实现，然后将其放到集合中进行判断，理论上这种操作会比临时表的方案快。
总结：
- BKA 优化是 MySQL 已经内置支持的
- BNL算法效率低，建议尽量转换成 BKA 算法。给被驱动表关联的字段加上索引即可
- 基于临时表的改进方案，对于能够提前过滤出小数据的join语句来说，效果还是很好的
- MySQL 目前还不支持 hash join，但是我们可以配合业务端模拟出来，理论上效果好于临时表的方案
- 多个表做join时，使用 explain 配合 straight_join 进行观察，找出每次参与join的驱动表的数据集，越小越好，这就是最终确定的连接顺序，以及需要在表的哪些字段上加索引。
这次业务因一些软删除数据，导致表的数据异常庞大，只是查询一下数据， 在使用了 BKA 算法的前提下，扫描行数还是达到了大约200万行的量级，速度也很慢，主要是最小的驱动表都有6万多行。
解决思路就是：
- 减少join数量，放弃不必要的条件限制，然后在业务端模拟 hash join
- 定时删除无效的数据（意义并不是特别大，毕竟 join 前 where 也会过滤）
- 重新学习设计表（主要是有两个表的join数据达到了180万行）