【MySQL进阶之路 | 高级篇】JOIN语句原理

Vez'nan的幸福生活

已于 2024-07-21 16:07:29 修改

阅读量138

点赞数 8

文章标签： sqlite mysql hbase mongodb storm hadoop

于 2024-07-21 14:02:12 首次发布

本文链接：https://blog.csdn.net/2301_80912559/article/details/140587457

版权

JOIN方式连接多个表，本质上就是各个表之间数据的循环匹配。MySQL5.5之前，MySQL只支持一种表间关联方式，就是嵌套循环。如果关联表的数据量很大，则JOIN关联的执行时间会很长。在MySQL5.5以后的版本中，MySQL通过引入BNLJ算法来优化嵌套循环。

1. 驱动表和被驱动表

对内连接来说

select * from A inner join B ON ...

A不一定是驱动表，优化器会根据你查询语句做优化，决定先查哪张表。先查询的那张表就是驱动表，反之就是被驱动表。通过explain关键字可以查看。

如果是内连接，expalin select * from type INNER JOIN book ON type.card = book.card;

如果表的连接条件中只有一个字段有索引，则有索引的字段所在的表会被当做被驱动表。因为该表有索引查询成本低。
如果表的连接条件中两个字段都有索引，则小表会被当作是驱动表。（即小表驱动大表）

对于外连接来说，外连接可能会被优化器改写为内连接，再对内连接进行上述优化。

2. Simple Nested-Loop Join(简答嵌套循环连接)

即从表A中取出一条数据1，遍历表B，将匹配到的数据放到result。以此类推，驱动表A的每一条记录与被驱动表B的记录进行判断。

3. Index Nested-Loop Join(索引嵌套循环连接)

Index Nested-Loop Join其优化的思路是主要为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较，这样极大的减少了对内层表的匹配次数。

驱动表中每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故MySQL优化器都倾向于使用记录数较少的表作为驱动表。

如果被驱动表加索引，效率是非常高的，但如果索引不是主键索引，所以还得进行一次回表查询。相比，被驱动表的索引是主键索引的话，效率会更高。

4. Block Nested-Loop Join(块嵌套循环连接)

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条于其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录再加载到内存匹配，这样周而复始，大大增加了被驱动表的IO次数，就出现了Block Nested-Loop Join。

不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区。将驱动表join相关的部分数据列（大小受join buffer的限制）缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中多次比较合并成一次，降低了被驱动表的访问频率。

注意：

这里缓存的不只是关联表的列，select后面的列也要缓存起来。
在一个有N个join关联的sql会分配N-1个join buffer。所以查询的时候尽量不要减少不必要的字段，可以让join buffer中可以存储更多的列。

驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况下join_buffer_size=256。

5. Join小结

整体效率：INLJ>BNLJ>SNLJ。
永远用小结果集驱动大结果集（其本质就是减少外层循环的数据数量）
为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）
增大join buffer size的大小（一次缓存的数据越多，那么内层包的扫表次数就会很少）。
减少驱动表不必要的字段查询（字段越少，join buffer所缓存的数据就越多）。

6. Hash Join

Nested Loop:

对于被连接的数据子集较小的情况，Nested Loop是个比较好的选择。

Hash Join：

其是做大数据集连接时的常用方式，优化器使用两个表中较小的表利用join key在内存中建立散列表，然后扫描较大的表并探测散列表，找出于hash表匹配的行。

这种方式适用于较小的表完全可以放在内存中的情况，这样总成本就是访问两个表的成本之和。
在表很大的情况下并不能完全放入内存中，这时优化器会将他们分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高IO的性能。
它能够很好的工作于没有索引的大表和并行查询的环境中，并提高很好的性能。hash join智能应用于等值连接（如WHERE A.COL1=B.COL2），这是由于hash的特点决定的。