left join、right join、inner join的区别

• left join(左连接)：返回包括左表中的所有记录和右表中联结字段相等的记录
• right join(右连接)：返回包括右表中的所有记录和左表中联结字段相等的记录
• inner join(等值连接)：只返回两个表中联结字段相等的行

那我们看看在join连接时哪个表是驱动表，哪个表是被驱动表：

• 1.当使用left join时，左表是驱动表，右表是被驱动表
• 2.当使用right join时，右表是驱动表，左表是被驱动表
• 3.当使用inner join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表

具体情况大家可以用Explain执行计划验证下

举个例子：

假如有两张表：A是小表，B是大表

使用left join 时，则应该这样写

select * from A a left join B b on a.id=b.id;

此时A表时驱动表，B表是被驱动表

测试：假设B表140多条数据，A表20万左右的数据量

select * from A a left join B b on a.id=b.id;

执行时间：8s

select * from B b left join A a on a.id=b.id;

执行时间：19s

所以记住：小表驱动大表优于大表驱动小表

一个注意点

join查询在有索引条件下

• 驱动表有索引不会使用到索引
• 被驱动表建立索引会使用到索引

所以在以小表驱动大表的情况下，再给大表建立索引会大大提高执行速度

举例子测试一下：

假设有2张表：A表，B表，分别建立索引

select * from A a left join B b on a.name=b.name;

发现只有B表name使用到索引

如果同时只给A表的name建立索引会是什么情况？

在这种情况下，A表索引失效

所以可以通过给被驱动表建立索引来优化SQL

Join原理

mysql的join算法叫做Nested-Loop Join（嵌套循环连接）

而这个Nested-Loop Join有三种变种，下面分别介绍下

Simple Nested-Loop

这个算法相当简单、直接。即驱动表中的每一条记录与被驱动表中的记录进行比较判断（就是个笛卡尔积）。对于两表联接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍

假设R为驱动表，S被驱动表，用伪代码表示一下这个过程就是这样：

for r in R                      # 扫描R表（驱动表）
    for s in S                   # 扫描S表（被驱动表）
        if (r and s satisfy the join condition)  # 如果r和s满足join条件
            output result    # 返回结果集

所以如果R有1万条数据，S有1万条数据，那么数据比较的次数1万 * 1万 =1亿次，这种查询效率会非常慢。

Index Nested-Loop

这个是基于索引进行连接的算法

它要求被驱动表上有索引，可以通过索引来加速查询。

假设R为驱动表，S被驱动表，用伪代码表示一下这个过程就是这样：

For r in R                  # 扫描R表
    for s in Sindex                    # 查询S表的索引（固定3~4次IO，B+树高度）
        if （s == r)                   # 如果r匹配了索引s
            output result   # 返回结果集

Block Nested-Loop

这个算法较Simple Nested-Loop Join的改进就在于可以减少被驱动表的扫描次数

因为它使用Join Buffer来减少内部循环读取表的次数

假设R为驱动表，S被驱动表，用伪代码表示一下这个过程就是这样：

for r in R                             # 扫描表R
    store p from R in Join Buffer    # 将部分或者全部R的记录保存到Join Buffer中，记为p
    for s in S                        # 扫描表S
        if (p and s satisfy the join condition)        # p与s满足join条件
           output result                    # 返回为结果集

可以看到相比Simple Nested-Loop Join算法，Block Nested-LoopJoin算法仅多了一个所谓的Join Buffer

为什么这样就能减少被驱动表的扫描次数呢？

下图相比更好地解释了Block Nested-Loop Join算法的运行过程

可以看到Join Buffer用以缓存联接需要的列（所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样还可以在join buffer中放置更多的记录呢，是不是这个道理哈，哈哈）

然后以Join Buffer批量的形式和被驱动表中的数据进行联接比较。

关于Join Buffer

1. Join Buffer会缓存所有参与查询的列而不是只有Join的列。
2. join_buffer_size的默认值是256K

总结

在选择Join算法时，会有优先级：

Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join

当不使用Index Nested-Loop Join的时候，默认使用Block Nested-Loop Join。

使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on，默认为开启。