mysql表执行简单原理-多表关联

多表关联查询

上一篇介绍了单个表查询的情况，接下来我们看看多表关联的例子：

select tb1.user_name from tbl_user as tb1 left join tbl_login as tb2 on tb2.user_id = tb1.user_id

上面这条检查的两表关联查询语句，很清晰知道拿tb1跟tb2的user_id做观察查询，从笛卡尔积的角度讲，就是先从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录

这里简单介绍下什么是笛卡尔积

笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录。

下面的例子，t_blog有10条记录，t_type有5条记录，所有他们俩的笛卡尔积有50条记录

现在很多大数据处理工具，会将表中的所有数据形成笛卡尔积，典型的用空间换时间的思想，也是一种多维数据的模型。

在开始介绍连表相关的时候，要先介绍下驱动表以及对应的选择算法

• 当连接查询没有 where 条件时，左连接查询时，前面的表是驱动表，后面的表是被驱动表，右连接查询时相反，内连接查询时，哪张表的数据较少，哪张表就是驱动表

• 当连接查询有 where 条件时，带 where 条件的表是驱动表，否则是被驱动表

• 用结果集来选择驱动表，那结果集是什么？如何计算结果集？

• mysql 在选择前会根据 where 里的每个表的筛选条件，相应的对每个可作为驱动表的表做个结果记录预估，预估出每个表的返回记录行数，同时再根据 select 里查询的字段的字节大小总和做乘积

以下这个图就是mysql的基本连表图

这个图相信大家都有看过，这里指的是两个表的关联，那如果是3个或者以上表关联的时候是怎么处理的呢？还是a &b联合成一个临时表，跟c做关联吗？

select tb1.user_name,tb2.login_id,tb3.class_id from tbl_user as tb1
left join tbl_login as tb2 on tb2.user_name = tb1.user_name
left join tbl_class as tb3 on tb3.user_name = tb2.login_id

以上例子是tb1跟tb2关联,然后tb2跟tb3关联,逻辑很清晰,这里比较容易让人理解错误的就是tb1跟tb2关联所有数据后,组成一个临时表,跟tb3关联。

这个理解是错误的

mysql在多表相连的时候有多个连表算法，这里先简单介绍以下三种，还有其他的大家可以自主去官网了解

Nested-Loop Join Algorithm NLJ

Index Nested-Loop Join Algorithms

Block Nested-Loop Join Algorithm BNL

mysql5.5以前三个表以上的连接方式十分简单粗暴，这个算法就是NLJ，看下图，官网是怎么介绍的，这几行代码相信大家一看就懂，一懂就蒙圈，这算法好像比我写的还差~哈哈

我们用个简单的图来描述

确实，mysql也意识到了这个问题，从5.5以后就不再采用NLJ算法，并增加了多个不同场景下采用不同的算法，例如，连表时候使用到索引才用”Index Nested-Loop Join Algorithms“算法

通过以上个图，就能知道有索引的情况下关联查询是有多大的效率提升，所以在关联查询的时候尽量要采用索引，如果实在没有索引的情况下mysql是怎么处理的呢？

接下来我们看下本章节最后介绍的”Block Nested-Loop Join Algorithm BNL“算法

• 这个算法其实很简单就是把一行变成了一批，块嵌套循环（BNL）嵌套算法使用对在外部循环中读取的行进行缓冲，以减少必须读取内部循环中的表的次数。例如，如果将 10 行读入缓冲区并将缓冲区传递到下一个内部循环，则可以将内部循环中读取的每一行与缓冲区中的所有 10 行进行比较。这将内部表必须读取的次数减少了一个数量级。

• MySQL 连接缓冲区大小通过这个参数控制 ：join_buffer_size

• MySQL 连接缓冲区有一些特征，只有无法使用索引时才会使用连接缓冲区；联接中只有感兴趣的列存储在其联接缓冲区中，而不是整个行；为每个可以缓冲的连接分配一个缓冲区，因此可以使用多个连接缓冲区来处理给定查询；在执行连接之前分配连接缓冲区，并在查询完成后释放连接缓冲区

• 所以查询时最好不要把 * 作为查询的字段，而是需要什么字段查询什么字段，这样缓冲区能够缓冲足够多的行。

下图就是官方给出的连表语句，一些博主增加了中文注释

实际的连接流程图

通过这个图可以了解到，如果内层循环有 100 条记录，外层循环也有 100 条记录，这样的话，每次外层循环先将 10 条记录放到 buffer 中，内层循环的 100 条记录每条与这个 buffer 中的 10 条记录进行匹配，只需要匹配内层循环总记录数次即可结束一次循环 (在这里，即只需要匹配 100 次即可结束)，然后将匹配成功的记录连接后放入结果集中，接着，外层循环继续向 buffer 中放入 10 条记录，同理进行匹配，并将成功的记录连接后放入结果集。后续循环以此类推，直到循环结束，将结果集发给 client 为止。可以发现，若用 NLJ，则需要 100 * 100 次才可结束，BNLJ 则需要 100 / block_size * 100 = 10 * 100 次就可结束，减少了 9/10。 

mysql表执行的简单原理（二）多表关联就介绍到这里，有不对的地方欢迎指正，感谢阅读。