面试官问我为什么要尽量避免数据表的 join 查询操作

理论指导实践，实践反过来又完善理论，没有孰轻孰重，只有相辅相成

看法

  对于开发提交的含有join的查询，一般比较抗拒，从而建议将join拆分，避免join可能带来的性能问题，同时也增加了程序和DB的网络交互。建议从数据库设计层面和应用程序编码中尽量避免join。

事实

  5.5 版本之前，MySQL本身只支持一种表间关联方式，就是嵌套循环(Nested Loop)。如果关联表的数据量很大，则join关联的执行时间会非常长。在5.5以后的版本中，MySQL通过引入BNL算法来优化嵌套执行,本文介绍两种join算法 Nested-Loop Join (NLJ) 和Block Nested-Loop Join(BNL) 。

深入原理

Nested Loop Join算法

  NLJ 算法:将驱动表/外部表的结果集作为循环基础数据，然后循环从该结果集每次一条获取数据作为下一个表的过滤条件查询数据，然后合并结果。如果有多表join，则将前面的表的结果集作为循环数据，取到每行再到联接的下一个表中循环匹配，获取结果集返回给客户端。
  Nested-Loop 的伪算法如下:

for each row in t1 matching range{for each row in t2 matching reference key{for each row in t3{if row satisfies join conditions,
      send to client}}}

  因为普通Nested-Loop一次只将一行传入内层循环, 所以外层循环(的结果集)有多少行, 内存循环便要执行多少次.在内部表的连接上有索引的情况下，其扫描成本为O(Rn),若没有索引,则扫描成本为O(Rn*Sn)。如果内部表S有很多记录，则SimpleNested-Loops Join会扫描内部表很多次，执行效率非常差。

Block Nested-Loop Join算法

  BNL 算法:将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数。

  举例来说，外层循环的结果集是100行，使用NLJ 算法需要扫描内部表100次，如果使用BNL算法，先把对Outer Loop表(外部表)每次读取的10行记录放到join buffer,然后在InnerLoop表(内部表)中直接匹配这10行数据，内存循环就可以一次与这10行进行比较, 这样只需要比较10次，对内部表的扫描减少了9/10。所以BNL算法就能够显著减少内层循环表扫描的次数。

  前面描述的query, 如果使用join buffer, 那么实际join示意如下:

for each row in t1 matching range{for each row in t2 matching reference key{
    store used columns from t1, t2 in join bufferif buffer is full{for each row in t3{for each t1, t2 combination in join buffer{if row satisfies join conditions,
          send to client}}
      empty buffer}}}if buffer is not empty{for each row in t3{for each t1, t2 combination in join buffer{if row satisfies join conditions,
      send to client}}}

如果t1, t2参与join的列长度只和为s, c为二者组合数, 那么t3表被扫描的次数为

(S * C)/join_buffer_size + 1
扫描t3的次数随着join_buffer_size的增大而减少, 直到join buffer能够容纳所有的t1, t2组合, 再增大join buffer size, query 的速度就不会再变快了。

建议

  驱动表（外层循环）使用小表并开启join_buffer，当然默认情况瞎MySQL优化器会帮我们做这个，除非你使用right join或者left jion，内表使用大表并且在join条件上有索引。

  5.6版本及以后，优化器管理参数optimizer_switch中中的block_nested_loop参数控制着BNL是否被用于优化器。默认条件下是开启，若果设置为off，优化器在选择 join方式的时候会选择NLJ算法。

延伸至exists

SELECT
    `user`.*
FROM
    `user`
WHEREEXISTS(
        SELECT
            `order`.user_id
        FROM
            `order`
        WHERE
            `user`.id= `order`.user_id)

  驱动表（外层）

 SELECT `user`.* FROM `user`

  内层

EXISTS(
        SELECT
            `order`.user_id
        FROM
            `order`
        WHERE
            `user`.id= `order`.user_id)

  同样使用 是Nested Loop Join算法

延伸至in

SELECT*
FROM
    `user`
WHERE
    `user`.idIN(
        SELECT
            `order`.user_id
        FROM
            `order`)

  这条语句会先执行子查询

   SELECT
            `order`.user_id
        FROM
            `order`

  这时候我们建议user.id 的这个id一定要是有索引的，MySQL会对？中的每个元素进行user.id = per 查询，最后根据主键去重合并。所以这时候就希望子查询得到是小结果集，并且in针对的字段在主查询对应的表中是有索引的。

SELECT*
FROM
    `user`
WHERE
    `user`.idIN(?)