【MySQL】连接查询的本质

最新推荐文章于 2024-05-26 14:49:39 发布

morris131

最新推荐文章于 2024-05-26 14:49:39 发布

阅读量1.2w

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库 database join hash join

本文链接：https://blog.csdn.net/u022812849/article/details/122266215

版权

MySQL 专栏收录该内容

57 篇文章 15 订阅

订阅专栏

了解连接、内连接、外连接这些基本概念后，我们需要理解MySQL怎么样来进行表与表之间的连接，才能明白为什么有的连接查询运行的快，有的却慢。

嵌套循环连接（Nested-Loop Join）

我们前边说过，对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。

对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的，也就是说左（外）连接的驱动表就是左边的那个表，右（外）连接的驱动表就是右边的那个表。

如果有3个表进行连接的话，那么首先两表连接得到的结果集就像是新的驱动表，然后第三个表就成为了被驱动表，可以用伪代码表示一下这个过程就是这样：

for each row in e1 { #遍历e1
	for each row in e2 { #遍历e2
		for each row in t3 { #遍历e3
			if (found)
				send to client
		}
	}
}

这个过程就像是一个嵌套的循环，所以这种驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数，这种连接执行方式称之为嵌套循环连接（Nested-Loop Join），这是最简单，也是最笨拙的一种连接查询算法，时间复杂度是O（N*M*L）。

使用索引加快连接速度

我们知道在嵌套循环连接中可能需要访问多次被驱动表，如果访问被驱动表的方式都是全表扫描的话，那酸爽不敢想象！但是查询被驱动表其实就相当于一次单表查询，我们可以利用索引来加快查询速度。

例如下面的SQL：

SELECT * FROM e1, e2 WHERE e1.m1 > 1 AND e1.m1 = e2.m2 AND e2.n2 < 'd';

使用嵌套循环连接算法执行上边的查询，执行过程如下：

假设查询驱动表e1后的结果集中有两条记录，e1.m1的值为[2, 3]，嵌套循环连接算法需要对被驱动表查询2次：

当e1.m1=2时，去查询一遍e2表，对e2表的查询语句相当于：

SELECT * FROM e2 WHERE e2.m2 = 2 AND e2.n2 < 'd';

当e1.m1=3时，再去查询一遍e2表，此时对e2表的查询语句相当于：

SELECT * FROM e2 WHERE e2.m2 = 3 AND e2.n2 < 'd';

可以看到，原来的e1.m1=e2.m2这个涉及两个表的过滤条件在针对e2表做查询时关于e1表的条件就已经确定了，所以我们只需要单单优化对e2表的查询了，上述两个对e2表的查询语句中利用到的列是m2和n2列，我们可以：

在m2列上建立索引，因为对m2列的条件是等值查找，比如e2.m2=2、e2.m2=3等，所以可能使用到ref的访问方法，假设使用ref的访问方法去执行对e2表的查询的话，需要回表之后再判断e2.n2<d这个条件是否成立。

这里有一个比较特殊的情况，就是假设m2列是e2表的主键或者唯一二级索引列，那么使用e2.m2=常数值这样的条件从e2 表中查找记录的过程的代价就是常数级别的。我们知道在单表中使用主键值或者唯一二级索引列的值进行等值查找的方式称之为const，而MySQL把在连接查询中对被驱动表使用主键值或者唯一二级索引列的值进行等值查找的查询执行方式称之为：eq_ref。

在n2列上建立索引，涉及到的条件是e2.n2 < 'd'，可能用到range的访问方法，假设使用range的访问方法对e2表的查询的话，需要回表之后再判断在m2列上的条件是否成立。

假设m2和n2列上都存在索引的话，那么就需要从这两个里边儿挑一个代价更低的去执行对e2表的查询。当然，建立了索引不一定使用索引，只有在二级索引+回表的代价比全表扫描的代价更低时才会使用索引。

另外，有时候连接查询的查询列表和过滤条件中可能只涉及被驱动表的部分列，而这些列都是某个索引的一部分，这种情况下即使不能使用eq_ref、ref、ref_or_null或者range这些访问方法执行对被驱动表的查询的话，也可以使用索引扫描，也就是index（索引覆盖）的访问方法来查询被驱动表。

基于块的嵌套循环连接（Block Nested-Loop Join）

扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。

现实生活中的表成千上万条记录都是少的，几百万、几千万甚至几亿条记录的表到处都是。内存里可能并不能完全存放的下表中所有的记录，所以在扫描表前边记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，所以需要把前边的记录从内存中释放掉。

而采用嵌套循环连接算法的两表连接过程中，被驱动表可是要被访问好多次的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个I/O代价就非常大了，所以我们得想办法：尽量减少访问被驱动表的次数。

当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次。

所以我们可不可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。

所以MySQL提出了一个join buffer的概念，join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价。使用join buffer的过程如下图所示：

最好的情况是join buffer足够大，能容纳驱动表结果集中的所有记录。这种加入了join buffer的嵌套循环连接算法称之为基于块的嵌套连接（Block Nested-Loop Join）算法。

这个join buffer的大小是可以通过启动参数或者系统变量join_buffer_size进行配置，默认大小为262144 字节（也就是256KB），最小可以设置为128字节。

mysql> show variables like 'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.04 sec)

当然，对于优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引，如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大join_buffer_size的值来对连接查询进行优化。

另外需要注意的是，驱动表的记录并不是所有列都会被放到join buffer中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中，所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样还可以在join buffer中放置更多的记录。

Hash Join

MySQL中的第二种JOIN算法是Hash Join，用于两张表之间连接条件没有索引的情况。

有同学会提问，没有连接，那创建索引不就可以了吗？或许可以，但：

如果有些列是低选择度的索引，那么创建索引在导入数据时要对数据排序，影响导入性能；
二级索引会有回表问题，若筛选的数据量比较大，则直接全表扫描会更快。

对于OLAP业务查询来说，Hash Join是必不可少的功能，MySQL8.0版本开始支持Hash Join算法，加强了对于OLAP业务的支持。

所以，如果你的查询数据量不是特别大，对于查询的响应时间要求为分钟级别，完全可以使用单个实例MySQL 8.0来完成大数据的查询工作。

Hash Join算法的伪代码如下：

foreach row r in R with matching condition:
    create hash table ht on r
foreach row s in S with matching condition:
    search s in hash table ht:
    if (found)
        send to client