Mysql表连接的误区与原理浅谈

在使用MySQL数据库过程中,left join 基本是必用的语法,下面这篇文章主要给大家介绍了关于Mysql表连接的误区与原理的相关资料,需要的朋友可以参考下

前言

搞后端的肯定要经常接触到数据库,搞数据库一个避免不了的地方就是 join, join的语法很简单,但是在使用时常常陷入一下两种误区:

• 误区一: 业务至上,管他三七二十一,再复杂的查询一个连接语句搞定
• 误区二: 敬而远之,上次写的慢查询sql就是使用了join导致的,以后再也不敢用了

先来举个栗子:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

mysql> SELECT * FROM t1; +------+------+ | m1   | n1   | +------+------+ |    1 | a    | |    2 | b    | |    3 | c    | +------+------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM t2; +------+------+ | m2   | n2   | +------+------+ |    2 | b    | |    3 | c    | |    4 | d    | +------+------+ 3 rows in set (0.00 sec)

现在我们对这张表进行连接:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> SELECT * FROM t1, t2; +------+------+------+------+ | m1   | n1   | m2   | n2   | +------+------+------+------+ |    1 | a    |    2 | b    | |    2 | b    |    2 | b    | |    3 | c    |    2 | b    | |    1 | a    |    3 | c    | |    2 | b    |    3 | c    | |    3 | c    |    3 | c    | |    1 | a    |    4 | d    | |    2 | b    |    4 | d    | |    3 | c    |    4 | d    | +------+------+------+------+ 9 rows in set (0.00 sec)

这个过程看起来就是把t1表的记录和t2的记录连起来组成新的更大的记录，所以这个查询过程称之为连接查询。连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为笛卡尔积。因为表t1中有3条记录，表t2中也有3条记录，所以这两个表连接之后的笛卡尔积就有3×3=9行记录。

连接过程简介

如果我们乐意，我们可以连接任意数量张表，但是如果没有任何限制条件的话，这些表连接起来产生的笛卡尔积可能是非常巨大的。比方说3个100行记录的表连接起来产生的笛卡尔积就有100×100×100=1000000行数据！所以在连接的时候过滤掉特定记录组合是有必要的

下边我们就要看一下携带过滤条件的连接查询的大致执行过程了，比方说下边这个查询语句：

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

在这个查询中我们指明了这三个过滤条件：

• t1.m1 > 1
• t1.m1 = t2.m2
• t2.n2 < 'd'

那么这个连接查询的大致执行过程如下：

• 首先确定第一个需要查询的表，这个表称之为驱动表。只需要选取代价最小的那种访问方法去执行单表查询语句就好了。此处假设使用t1作为驱动表，那么就需要到t1表中找满足t1.m1 > 1的记录，因为表中的数据太少，我们也没在表上建立二级索引，所以此处查询t1表的访问方法就是全表扫描。

• 针对上一步骤中从驱动表产生的结果集中的每一条记录，分别需要到t2表中查找匹配的记录，所谓匹配的记录，指的是符合过滤条件的记录。因为是根据t1表中的记录去找t2表中的记录，所以t2表也可以被称之为被驱动表。上一步骤从驱动表中得到了2条记录，所以需要查询2次t2表。此时涉及两个表的列的过滤条件t1.m1 = t2.m2就派上用场了：

  • 当t1.m1 = 2时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 2，所以此时t2表相当于有了t2.m2 = 2、t2.n2 < 'd'这两个过滤条件，然后到t2表中执行单表查询。
  • 当t1.m1 = 3时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 3，所以此时t2表相当于有了t2.m2 = 3、t2.n2 < 'd'这两个过滤条件，然后到t2表中执行单表查询。

从上边两个步骤可以看出来，我们上边唠叨的这个两表连接查询共需要查询1次t1表，2次t2表。当然这是在特定的过滤条件下的结果，如果我们把t1.m1 > 1这个条件去掉，那么从t1表中查出的记录就有3条，就需要查询3次t2表了。

也就是说在两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次。

转自：微点阅读   https://www.weidianyuedu.com