mysql数据库join查询默认值_深入理解mysql数据库join查询

我们在日常开发中，时常会面临sql优化的问题，通常情况下，我们总会说join查询比普通的关联查询效率会高很多，话虽如此，但是为何join查询效率通常情况下比关联查询效率高呢，我们一起探究一下。

我们知道，关联查询，会将符合条件的查询结果显示出来；而join查询，不光显示符合条件的数据，还会将符合左表但是不符合右表的全部数据都查询出来。

假如我有两张数据库表，分别是h_user(7条数据) 和 h_user_info(999条数据) ，两个表的主键分别是user_id和info_id，且并无其它索引字段。

如果使用关联查询和join查询，那么，sql语句通常是如下所示：

-- 关联查询

SELECT

a.user_type

FROM

h_user a , h_user_info b where a.user_id = b.info_id

-- 左查询

SELECT

a.user_type

FROM

h_user a

LEFT JOIN h_user_info b ON a.user_id = b.info_id

关于上面的sql语句关联查询和左查询的区别：

1、如果是关联查询的话，只有1条符合条件的数据，则只会显示1条记录出来，有n条记录，则显示n条。

2、如果是使用左查询，当查询记录为1对1时，则显示7条记录；左右表有1对多(m)的情况，且左表有n条这样的记录，则总记录数为7 + ( m – 1 ) * n 。并且，当符合左表的记录，不符合右表，在查询出来记录列表中，右表的字段值为null；同理，当左右表顺序切换时，也是一样的规则计算。

上述sql语句，关于查询效率一块，两者查询效率是相同的。根据mysql查询计划显示结果如下：

从上图中，我们可以看到，两者的查询计划是相同的，查询条件为a.user_id = b.info_id，此时左表需进行全表扫描然后和右表进行匹配，至于为什么，稍后再做解释。而右表也命中索引，只需要和左表进行匹配即可。但是如果将两表切换位置，得到的执行计划，则是另外一回事，如下图：

但是，对于关联查询，将两表切换位置，得到的执行计划依然和上图一致，这里面的原因，暂时未知，也不再本文探讨范围内，后期有机会会单独研究。

看到这里，是不是对join查询感觉很是奇怪？那么，join查询的底层是如何实现的呢，我们一起探索一番。

简单来说，join查询，就类似于java中的for循环，通过两层for循环嵌套，从而遍历两张表中所有的数据并筛选出符合条件的数据集合，如下图所示：

/**

* TODO 为了方便理解，所以命名使用中文，勿效仿

*/

for (行 左表具体行 : 左表所有行){

for (行 右表具体行: 右表所有行){

// 对比条件 on *** = *** 是否满足

// 满足条件则进行记录

}

// 将当前左表数据和右表数据组合，当右表中没有匹配到数据，字段值填充null

// 如果内for循环有多行记录，则左表数据记录多行匹配结果集

}

// 返回所有的符合条件的行数据集合

}

两层for循环即可遍历两张表的全部数据信息进行比较。首先，我们拿到左表的每一行数据，然后循环右表数据的每一行，进行条件对比，当满足条件时，则与左表拼装数据记录；并在中间循环层做记载，当在右表中匹配到多条记录时，则在中间循环层使用同一左表数据与右表符合条件行拼装成多条记录值；当左表数据与右表全部数据没有匹配到相关结果时，则使用左表数据与右表字段进行拼装为一条记录，并且右表字段其值为：null

这就是最简单的Simple Nested-Loop Join ，也叫做“简单嵌套循环连接”，当左表有n条记录，右表有m条数据时，总匹配次数为 n * m ，当数据库表数据量比较大，由于数据库数据是保存在磁盘中，也就相当于要做很多次的I/O操作，这个过程是相当消耗资源的，所以我们平常在sql语句中的join查询，肯定不会直接采用这种形式。

如上面sql所示，有两张表进行左查询，我们可以看一下扫描两张表共查询的数据量

下面，我们再来另外一种方式：Index Nested-Loop Join(索引嵌套循环连接)

所谓索引嵌套连接，即左右表join，右表的匹配条件为索引字段时，此时会触发嵌套索引循环连接。索引嵌套循环连接的主要目的就是为了减少左右表总的匹配次数，减少数据库I/O操作，从而降低查询消耗，提高查询效率。前面我们已经学习过mysql索引原理，我们知道了索引超强的优点。

所谓索引嵌套循环连接，即左查询时，查询条件命中右表索引字段，从而右表数据查询索引，从而降低右表总查询数据量，降低左右表总的匹配次数。

我们通过sql语句来看一下：

如上图所示，我们sql语句查询条件在两个表中都是主键，但是通过执行计划，依然能够看得出来，左表数据匹配了7次，而右表数据则每次匹配时在索引中最多只匹配到一条记录。此次总的I/O匹配次数仅用了7次。相比较上面的查询，匹配次数大大降低了。

所以，当我们明白和理解join查询原理时，才有利于我们做sql优化等操作。

Index Nested-Loop Join 索引嵌套循环连接的触发前提是：查询条件必须命中右表索引。

学习完Index Nested-Loop Join索引嵌套循环后，我们再来看一下通过普通嵌套循环衍生的另一种循环方式：Block Nested-Loop Join(缓存块嵌套循环连接)。

如果我们在搬砖(真的是搬砖，不是码代码)，我们使用双手一块一块的搬砖，和我们拿一个小推车一车一车的搬，效率差异是很大的。所以，我们在刚刚普通的嵌套循环的基础上，我们可以添加一个类似的小推车，也就是缓存。

使用缓存的思路是为了外层循环次数，Block Nested-Loop Join通过一次缓存多条数据，将参与查询的列添加到join buffer 中，然后使用join buffer中的数据与右表数据进行匹配，从而减少外层的循环次数。

当我们不使用Index Nested-Loop Join，mysql默认使用Block Nested-Loop Join查询，如下图所示。

Mysql的Block Nested-Loop Join查询算法是否开启，是通过mysql管理配置器进行配置的，optimizer_switch的设置block_nested_loop为on 默认为开启。如果处于关闭状态的话，则使用普通嵌套循环查询，即Simple Nested-Loop Join 算法。

我们可以通过SHOW VARIABLES LIKE ‘optimizer_switc%’; 命令来查询当前状态。

而Block Nested-Loop Join缓存嵌套循环查询中join buffer 每次载入数据量，则可以通过join_buffer_size参数设置join buffer的大小。可通过SHOW VARIABLES LIKE ‘join_buffer_size%’; 命令查询每次载入数据量

这里，一共提到了三种嵌套循环查询，分别是：普通循环嵌套查询、索引循环嵌套查询、缓存循环嵌套查询；其中，索引形式和缓存形式都是基于普通演变优化而来。

对于sql优化而言，join查询左表会扫描全部列且不管查询条件是否命中索引，如果我们在已知两个表数量的前提下，左表可以数量少，右表数量多，且查询条件命中右表索引。(个人愚见)

2019年1月15日 15:18:59