mysql多表结构 mysql多表连接的方式

转载

mob6454cc6acccd 2023-08-30 18:51:14

文章标签 mysql多表结构一对多表连接 ci 文章分类 MySQL 数据库

（1）表的横向连接

（1.1）表连接的方式

在SQL中表的横向连接主要有left join、right join、inner join、outer join四种方式。

（1.1.1）left join

left join是左连接，左连接就是以左边的表为主表，然后将右边的表根据两张表的公共列往左边的表上连接。比如我们将chapter12_user表当作主表，放在左边，然后将chapter12_order表往左连接，两张表的公共列为userid，具体实现代码如下：

select chapter12_user.userid,
       chapter12_user.sex,
       chapter12_user.city_name,
       chapter12_user.level,
       chapter12_order.userid,
       chapter12_order.first_time,
       chapter12_order.orders_7,
       chapter12_order.orders_14
from chapter12_user
    left join chapter12_order
        on chapter12_user.userid = chapter12_order.userid;

在进行表连接时，我们用on来指明两张表中的公共列。运行上面的代码，会得到左表中的全部信息、右表中的部分信息，具体运行结果如下表所示。

mysql多表结构 mysql多表连接的方式_一对多

（1.1.2）right join

right join是右连接，右连接与左连接相对应。右连接是以右边的表为主表，然后将左边的表根据两张表的公共列往右边的表上连接。比如，我们将chapter12_order表当作主表，放在右边，然后将chapter12_user表往右连接，两张表的公共列为userid，具体实现代码如下：

select chapter12_user.userid,
       chapter12_user.sex,
       chapter12_user.city_name,
       chapter12_user.level,
       chapter12_order.userid,
       chapter12_order.first_time,
       chapter12_order.orders_7,
       chapter12_order.orders_14
from chapter12_user
    right join chapter12_order
        on chapter12_order.userid = chapter12_user.userid;

运行上面代码，具体运行结果如下表所示。

mysql多表结构 mysql多表连接的方式_mysql多表结构_02

（1.1.3）inner join

inner join是内连接，内连接是针对两张表取交集的，即获取公共列中都出现的值的信息。比如，我们将chapter12_user表与chapter12_order表进行内连接，两张表的公共列为userid，具体实现代码如下：

select chapter12_user.userid,
       chapter12_user.sex,
       chapter12_user.city_name,
       chapter12_user.level,
       chapter12_order.userid,
       chapter12_order.first_time,
       chapter12_order.orders_7,
       chapter12_order.orders_14
from chapter12_user
    inner join chapter12_order
        on chapter12_order.userid = chapter12_user.userid;

运行上面的代码，具体运行结果如下表所示。

mysql多表结构 mysql多表连接的方式_ci_03

（1.1.4）outer join

outer join是外连接，外连接与内连接相对应，是针对两张表取并集的，要查询的信息只要在任意一张表中存在，最后就会显示在结果中。但是MySQL暂不支持外连接的方式。我们就可以用左连接和右连接相组合的方式来达到外连接的效果，具体实现代码如下：

-- 我们就可以用左连接和右连接相组合的方式来达到外连接的效果，具体实现代码如下：
select chapter12_user.userid,
       chapter12_user.sex,
       chapter12_user.city_name,
       chapter12_user.level,
       chapter12_order.userid,
       chapter12_order.first_time,
       chapter12_order.orders_7,
       chapter12_order.orders_14
from chapter12_user
    left join chapter12_order
        on chapter12_user.userid = chapter12_order.userid

union 
select chapter12_user.userid,
       chapter12_user.sex,
       chapter12_user.city_name,
       chapter12_user.level,
       chapter12_order.userid,
       chapter12_order.first_time,
       chapter12_order.orders_7,
       chapter12_order.orders_14
from chapter12_user
    right join chapter12_order
        on chapter12_order.userid = chapter12_user.userid;

运行上面的代码，具体运行结果如下表所示。

mysql多表结构 mysql多表连接的方式_ci_04

把这两个结果进行纵向连接，在纵向连接的时候我们使用的是union，对连接后的结果进行删除重复值处理，这样就获取到了两张表的并集。

（1.2）表连接的类型

表的连接类型，主要有一对一、一对多、多对多三种。

（1.2.1）一对一

一对一是指用于连接两张表的公共列的值在左表和右表中都是没有重复值的。

（1.2.2）一对多

一对多是指用于连接两张表的公共列的值在左表或右表中是有重复值的。
现在如果我们要对chapter12_user表和chapter12_order_info表根据userid列进行连接时，就是一对多连接，因为userid在chapter12_order_info表中会有重复值，比如E001、E002。这个时候程序就会自动把一对多中没有重复值的一列复制成多条记录，具体实现代码如下：

select
    *
from chapter12_user
    right join chapter12_order_info
        on chapter12_order_info.userid = chapter12_user.userid
order by chapter12_user.userid;

运行上面的代码，具体运行结果如下表所示。

mysql多表结构 mysql多表连接的方式_ci_05

（1.2.3）多对多

多对多相当于多个一对多，就是用于连接两张表的公共列的值在左右表中都有重复。这个时候就是传说中的笛卡儿积（Cartesian product）。

笛卡儿乘积是指在数学中，两个集合X和Y的笛卡儿积，又称为直积，表示为X × Y，第一个对象是X的成员，而第二个对象是Y的所有可能有序对的其中一个成员。

如果用于连接两张表的一个公共列的值在左表中重复出现了m次，在右表中重复出现了n次，最后连接下来的结果会是m×n条记录。

我们在实际工作中要尽量避免一对多及多对多情况的出现，在对两张表进行连接时，一定要先检查用于连接表的公共列是否有重复值，如果有，则先处理完重复值以后再去与别的表进行连接。

（1.3）多张表连接

有时候，我们需要的信息不止分布在两张表中，这个时候就需要对大于两张的表进行连接，此处以chapter12_order_info表、chapter12_order表、chapter12_user表为例进行三表连接。具体实现代码如下：

-- 多张表的连接
select chapter12_order_info.orderid,
       chapter12_order_info.userid,
       chapter12_order_info.gmv,
       chapter12_user.level,
       chapter12_order.first_time
from chapter12_order_info
    left join chapter12_user
        on chapter12_order_info.userid = chapter12_user.userid
    left join chapter12_order
        on chapter12_order_info.userid = chapter12_order.userid;

运行上面的代码，最后就会得到三张表中不同的信息，具体运行结果如下表所示。

mysql多表结构 mysql多表连接的方式_一对多_06

（2）表的纵向连接

在SQL中进行纵向连接时，我们使用的是union和union all，两者的区别是，前者会对纵向连接后的结果进行删除重复值处理，而后者是不进行任何处理的，只是把两张表连接在一起。如果表中没有重复值，建议使用union all，这样程序就不会执行删除重复值这个过程，可以提高程序的运行效率。

（3）横向连接的底层原理

join主要有Nested Loop、Hash Join、Merge Join三种方式。

Nested Loop又有三种细分的连接方式，分别是Simple Nested-Loop Join、Index Nested-Loop Join、Block Nested-Loop Join。

在介绍原理之前，先介绍两个概念：驱动表（也称为外表）和被驱动表（也称为非驱动表、匹配表或内表）。简单理解一下，驱动表就是主表，left join中的左表是驱动表，right join中的右表是驱动表，一张是驱动表，那么另一张就只能是非驱动表了，在join的过程中，其实就是从驱动表中依次（注意理解这里面的依次）取出每一个值。

（3.1）Simple Nested-Loop Join（简单的嵌套循环联接）

Simple Nested-Loop Join是最简单、最好理解，也是最符合认知的一种连接方式，现在有table A和table B两张表，我们对两张表进行左连接，如果用Simple Nested-Loop Join连接方式去实现，通过下面这张图来理解。

mysql多表结构 mysql多表连接的方式_表连接_07

首先从驱动表table A中依次取出每个值，然后在非驱动表table B中从上往下依次匹配，接着把匹配到的值进行返回，最后把所有返回的值进行合并，这样我们就查找到了table A left join table B的结果。利用这种方式，如果table A表有10行，table B表有10行，则总共需要执行10×10 = 100次查询。

这种“暴力”匹配的方式在数据库中一般不使用。

（3.2）Index Nested-Loop Join（索引嵌套循环联接）

在Index Nested-Loop Join方式中，这里的Index表示要求非驱动表上要有索引，有了索引以后可以减少匹配的次数，匹配次数减少了就可以提高查询的效率了。

mysql多表结构 mysql多表连接的方式_一对多_08

上图中左边是普通列的存储方式，右边是树结构索引，什么是树结构呢？就是数据分布像树一样一层一层的，树结构有一个特点就是左边的数小于顶点的数，右边的数大于顶点的数，如上图中的右图，左边的数3小于顶点的数6，右边的数7大于顶点的数6；左边的数1小于顶点的数3，右边的数4大于顶点的数3。

假如我们现在要匹配值9，如果使用左边这种数据存储方式，系统需要从第一行依次匹配到最后一行才能找到值9，总共需要匹配7次；但是如果我们使用右边这种树结构索引，先拿9和顶点6去匹配，发现9比6大，然后就去顶点的右边找，再去和7匹配，发现9仍然比7大，再去7的右边找，就找到了9，这样只匹配了3次就把我们想要的9找到了。相比匹配7次节省了很多时间。

mysql多表结构 mysql多表连接的方式_一对多_09

数据库中的索引一般用B+树，为了让读者更好地理解，上图只是最简单的一种树结构，而非真实的B+树。

如果索引是主键，则效率会更高，因为主键必须是唯一的，所以如果非驱动表是用主键连接的，则只会出现多对一或者一对一的情况，而不会出现多对多和一对多的情况。

（3.3）Block Nested-Loop Join（块嵌套循环联接）

在理想情况下，用索引匹配是最高效的一种方式，但是在现实工作中，并不是所有的列都是索引列，这个时候就需要用到Block Nested-Loop Join方式了，这种方式与Simple Nested-Loop Join方式比较类似，唯一的区别就是它会把驱动表中left join涉及的列（不只是用来on的列，还有select部分的列）先取出来放到一个缓存区域，然后去和非驱动表进行匹配，这种方式和Simple Nested-Loop Join方式相比所需要的匹配次数是一样的，差别就在于驱动表的列数不同，也就是数据量的多少不同。所以虽然匹配次数没有减少，但是总体的查询性能还是有所提升的。

Simple Nested-Loop Join方式的连接原理如下图所示，驱动表会拿表中全部列去和非驱动表进行匹配连接。

mysql多表结构 mysql多表连接的方式_mysql多表结构_10