【MySQL—SQL编程】联接

2022-04-23 10:19:45

转：

【MySQL—SQL编程】联接

联接查询

联接查询是一种常见的数据库操作，即在两张表（或更多表）中进行行匹配的操作。一般称之为水平操作，这是因为对几张表进行联接操作所产生的结果集可以包含这几张表中所有的列。

CROSS JOIN

CROSS JOIN对两个表执行笛卡儿积，返回两个表中所有列的组合。若左表有m行数据，右表有n行数据，则CROSS JOIN将返回m*n行的表。

CROSS JOIN的一个用处是快速生成重复测试数据，因为通过它可以很快地构造m*n*o行的数据。

CROSS JOIN的另一个用处是可以作为返回结果集的行号，例如：

SELECT emp_no, dept_no, @a:=@a+1 AS row_num FROM dept_emp, (SELECT @a:=0 ) t;

INNER JOIN

通过INNER JOIN用户可以根据一些过滤条件来匹配表之间的数据。在逻辑查询的前三个处理阶段中，INNER JOIN应用前两个阶段，即首先产生笛卡儿积的虚拟表，再按照ON过滤条件来进行数据的匹配操作。

INNER关键字可省略。因为不会添加外部行，INNER JOIN中WHERE的过滤条件可以写在ON子句中。在MySQL数据库中，如果INNER JOIN后不跟ON子句，这时INNER JOIN等于CROSS JOIN。此外，如果ON子句中的列具有相同的名称，可以使用USING子句来进行简化。

OUTER JOIN

通过OUTER JOIN用户可以按照一些过滤条件来匹配表之间的数据。与INNER JOIN不同的是，在通过OUTER JOIN添加的保留表中存在未找到的匹配数据。

NATURAL JOIN

ANSI SQL还支持NATURAL JOIN，即自然联接。NATURAL JOIN等同于INNTER JOIN与USING的组合，它隐含的作用是将两个表中具有相同名称的列进行匹配。

NONEQUI JOIN

NONEQUI JOIN的联接条件包含“等于”运算符之外的运算符。例如，有一张部门经理表dept_manager，要生成表中所有两个不同经理的组合。这里先假设当前表中仅包含员工号A、B、C，执行CROSS JOIN后将生成下面九对：（A，A）、（A，B）、（A，C）、（B，A）、（B，B）、（B，C）、（C，A）、（C，B）、（C，C）。

显然，（A，A）、（B，B）和（C，C）包含相同的员工号，不是有效的员工组合。而（A， B）、（B，A）又表示同样的组合。要解决这个问题，可以指定一个左边值小于右边值的联接条件，这样可以移除上述两种情况。该问题的解决方案为：

SELECT a.emp_no,b.emp_no FROM dept_manager a INNER JOIN dept_manager b ON a.emp_no < b.emp_no;

联接算法

联接算法是MySQL数据库用于处理联接的物理策略。旧版本的MySQL数据库仅支持Nested-Loops Join算法，自8.0.18版本开始支持Hash Join算法。

当联接的表上有索引时，Nested-Loops Join是非常高效的算法。根据B+树的特性，其联接的时间复杂度为O（N），若没有索引，则可视为最坏的情况，时间复杂度为O（N2）。MySQL数据库根据不同的使用场合，支持两种Nested-Loops Join算法，一种是Simple Nested-LoopsJoin（NLJ）算法，另一种是Block Nested-Loops Join（BNL）算法。

Simple Nested-Loops Join算法（简单嵌套循环算法）

Simple Nested-Loops Join从第一张表中每次读取一条记录，然后将记录与嵌套表中的记录进行比较。

假设在两张表R和S上进行联接的列都不含有索引。这个算法的扫描次数为：Rn+Rn*Sn，扫描成本为O（Rn*Sn）。Rn和Sn分别代表R表和S表中分别含有的记录数。

对于联接的列含有索引的情况，外部表的每条记录不再需要扫描整张内部表，只需要扫描内部表上的索引即可得到联接的判断结果。如果内部表联接列的索引高度为SBH，那么上述算法的扫描次数为Rn+SBH*Rn，扫描成本为O（Rn）。而一般B+树的高度为3～4层，因此在有索引的情况下，Simple Nested-Loops Join算法的执行速度是比较快的。

优化器在一般情况下总是选择将联接列含有索引的表作为内部表。如果两张表R和S在联接的列上都有索引，并且索引的高度相同，那么优化器会选择将记录数最少的表作为外部表，这是因为内部表的扫描次数总是索引的高度，与记录的数量无关。

Block Nested-Loops Join算法（块嵌套循环算法）

Block Nested-Loops Join算法是针对没有索引的联接情况设计的，其使用Join Buffer（联接缓冲）来减少内部循环读取表的次数。

例如，Block Nested-Loops Join算法先把对Outer Loop表（外部表）每次读取的10行记录（准确地说是10行需要进行联接的列）放入Join Buffer中，然后在Inner Loop表（内部表）中直接匹配这10行数据。因此，对Inner Loop表的扫描减少了1/10。

MySQL数据库使用Join Buffer的原则如下：

系统变量join_buffer_size决定了Join Buffer的大小。
Join Buffer可被用于联接是ALL、index和range的类型。
每次联接使用一个Join Buffer，因此多表的联接可以使用多个Join Buffer。
Join Buffer在联接发生之前进行分配，在SQL语句执行完后进行释放。
Join Buffer只存储需要进行查询操作的相关列数据，而不是整行的记录。

在MySQL 5.5以及之前的版本中，Join Buffer只能在INNER JOIN中使用，在OUTER JOIN中则不能使用，即Block Nested-Loops Join算法不支持OUTER JOIN。

Batched Key Access Join算法（批量键值访问联接算法）

MySQL 5.6（MariaDB 5.3）开始支持BatchedKey Access Join算法（简称BKA），该算法的思想为结合索引和group这两种方法（SimpleNested-Loops Join和Block Nested-Loops Join只能使用一种）来提高search-for-match的操作，以此加快联接的执行效率。我们可以把这种算法理解为group-index-lookup（组索引查询）。

为什么使用group-index-lookup能提高search-for-match的操作呢？因为这样可以提高数据库整体资源的Cache命中率，从CPU Cache、PageCache到Disk的访问，效率都变得更高。如果外部表扫描的是主键，那么表中的记录访问都是比较有序的，但是如果联接的列是非主键索引，那么对于表中记录的访问可能就是非常离散的。因此对于非主键索引的联接，Batched Key Access Join算法将能极大提高SQL的执行效率。

Batched Key Access Join算法的工作步骤如下：

将外部表中相关的列放入Join Buffer中。
批量地将Key（索引键值）发送到Multi-Range Read（MRR）接口。
Multi-Range Read（MRR）通过收到的Key，根据其对应的ROWID进行排序，然后再进行数据的读取操作。
返回结果集给客户端。

举个例子，现在有这样一条SQL语句：

SELECT MAX(l_extendedprice) FROM orders, lineitem WHERE o_orderdate BETWEEN ‘1995-01-01‘ AND ‘1995-01-31‘ AND l_orderkey=o_orderkey;

在上述SQL语句中，lineitem的主键为（l_orderkey，l_linenumber）的联合索引，l_orderkey、o_orderdate和o_orderkey上都有索引。l_extendedprice是表lineitem中的列，不在索引l_orderkey中。因此当通过l_orderkey索引来进行search-for-match操作之后，还需要根据ROWID来查找l_extendedprice的值，而这个查找是离散的，故使用Batched Key Access Join算法能提高SQL语句执行的效率。

在MySQL 5.5下，数据库内部表的search-for-match操作选择了PRIMARY这个索引，也就是主键索引，而没有使用已经存在于列l_orderkey上的i_l_orderkey索引。产生这个结果的原因还是优化器认为利用辅助索引得到联接判断后，还要再次读取主键上的列l_ extendedprice，这是离散读取，因此要远远慢于直接的主键访问，并且主键索引的最左列也是l_orderkey。

在MySQL 5.6下，可以使用下列语句启用BatchedKey Access Join算法。

SET optimizer_switch=‘mrr=on,mrr_cost_based=off,batched_key_access=on‘;

因为Batched Key Access Join算法的本质是通过Multi-Range Read接口将非主键索引对于记录的访问，转化为根据ROWID排序的较为有序的记录获取，所以要想通过Batched Key Access Join算法来提高性能，不但需要确保联接的列参与match的操作，还要有对非主键列的search操作。如果联接不涉及针对主键进一步获取数据，内部表只参与联接判断，那么就不会启用Batched Key Access Join算法，因为没有必要去调用Multi-Range Read接口。

Batched Key Access Join算法从本质上来说还是Simple Nested-Loops Join算法，其发生的条件为内部表上有索引，并且该索引为非主键的，并且联接需要访问内部表主键上的索引。这时Batched Key Access Join算法调用Multi-RangeRead接口，批量地进行索引键的匹配和主键索引上获取数据的操作，以此来提高联接的执行效率。

Classic Hash Join算法（经典哈希联接算法）

Batched Key Access Join算法虽然解决了一些问题，但是还存在一些问题。一方面，不是每个联接语句都是有索引的，对于没有索引的情况，MySQL数据库目前只能使用Block Nested-LoopsJoin算法。这样虽然减少了内部表的扫描次数，但是没有减少执行search-for-match的次数。另一方面，即使有索引，但是当得到的数据占据表中大部分时，直接使用主键索引扫描更有效率，使用Multi-Range Read反而显得没有什么必要。

对于上述的两方面问题，可以通过另外一种联接算法来解决，即Hash Join，是一种广泛应用于数据仓库和OLAP应用的经典联接算法。

Classic Hash Join算法同样使用Join Buffer，先将外部表中数据放入Join Buffer中，然后根据键值产生一张散列表，这是第一个阶段，称为build阶段。随后读取内部表中的一条记录，对其应用散列函数，将其和散列表中的数据进行比较，这是第二个阶段，称为probe阶段。

如果将Hash查找应用于Simple Nested-LoopsJoin中，则执行计划的Extra列会显示BNLH。如果将Hash查找应用于Batched Key Access Join中，则执行计划的Extra列会显示BKAH。

倘若Join Buffer能够完全存放下外部表的数据，那么Classic Hash Join算法只需要扫描一次内部表。反之，Classic Hash Join需要扫描多次内部表。为了使Classic Hash Join更有效率，应该更好地规划Join Buffer的大小。

注：Hash Join只能应用于等值的联接操作中，因为已通过散列函数生成新的联接值，不能将Hash Join用于非等值的联接操作中。

转：

码农公寓