【MySQL】之join算法详解

    Simple Nested-Loop Join算法是指读取驱动表t1中的每行数据，将每行数据传递到被驱动表t2上，取出被驱动表t2中满足条件的行,和t1组成结果集。

    在这个算法中，需要对t1进行全表扫描，假设t1表1000行数据，那么需要对t2表进行1000次全表扫描，假设t2表也是1000行数据，那么就需要扫描1000 X 1000=1000000行。

    示例图如下：当t1表5行数据，t2表5行数据时，需要扫描25行数据。

    index nested-loop join算法的优化思路是通过驱动表的匹配条件，直接与被驱动表的索引进行匹配，减少了被驱动表的扫描次数。

    该算法同样要对驱动表t1进行全表扫描，但是我们在拿着t1表的数据去被驱动表t2进行匹配时可以利用t2表的索引，如果t1表中1000行数据，t2表中1000行数据，那么一共就需要扫描1000+1000=2000行数据。这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以称之为“Index Nested-Loop Join”，简称 NLJ。

    示例如下：当t1表有5行数据，t2表有5行数据时，一共需要扫描5+5=10行数据。

 
 set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'; 

• 尽量避免使用join。
• 用小表作为驱动表，减少外层循环的次数。
• 多表关联查询时，要保证被关联的字段要有索引。
• 适当增大join_buffer_size的值，缓存的数据越多，就越能减少被驱动表扫描的次数。
• 减少不必要的字段查询。
• 需要join的字段，数据类型保持绝对一致。