上周写了mysql的子查询,主要是从不同的维度梳理了子查询,那么提到子查询就很容易联想到表连接,因为子查询和表连接在功能上基本是相同的。今天就来梳理一下表连接吧。

mysql表连接:

mysql的表连接大体上可以分为这四类,为什么会这样画这个图,主要是右面的三类是我们使用的最多的三类,左边的是不常见的,主要是一些是与优化器有关的(straight join),下面会按每一种join类型进行分析。

此处分析表连接,我想再一次把逻辑查询拿出来,因为表连接是一个select操作,既然提到查询,我们必须要清楚select语句的执行流程。

考虑到有的小伙伴可能没有看之前的文章,就再一次简单的分析一下这张图,这张图讲的是一个完整的select语句的执行流程,并且每一步执行完成后都会把结果放到一张虚拟表里,理解这个执行流程的目的是为了更好的优化SQL。上次也举了一个慢SQL的例子,有兴趣的小伙伴可以参考和这两篇文章。

cross join:

cross join中文翻译叫做交叉连接,交叉连接会产生笛卡尔积,即假设表a有m行记录,表b有n行记录,那么采用cross join去连接的时候会产生m*n行记录,sql示例:

SELECT * FROM customer CROSS JOIN orders;

交叉连接的意义一般不是很大,这里需要注意的是内连接和外连接不挂on条件时也是交叉连接,结果同样是两张表的笛卡尔积。

inner join:

innerj join是两张表通过条件关联的操作,inner join应用逻辑查询的前两个阶段,即不添加外部行(没有保留表),inner join的写法有两种,一种是隐士内连接,一种是显示内连接,两种内连接只是语法不同,执行流程是没有任何区别的。

隐士内连接SQL示例:

SELECT c.*,o.oname FROM customer AS c , orders AS o WHERE c.cnum = o.cnum;

显式内连接sql示例:

SELECT c.*,o.oname FROM customer AS c INNER JOIN orders AS o ON c.cnum = o.cnum;

隐士内连接和显式内连接语法的一个最重要的区别是,隐士内连接的条件语句只能是where语句不能使用on来过滤,而显式内连接的条件语句既可以是where也可以是on,虽然隐士内连接的语法要简单一些,但是由于它不支持on语句,在此我不推荐使用隐式内连接,因为刚才在cross join中提到了如果inner join和outer join不加关联条件,其本质是cross join,结果是两个表的笛卡尔积,而隐士内连接不支持on语句,所以关联条件很容易漏掉,这是很危险的,所以还是建议使用显式内连接。

outer join:

outer join与inner join不同的是它应用逻辑查询的前三步,即比inner join多了一步添加外部行(有保留表),也就是说与内部表匹配不上的外部表的数据也会保留,内部表的字段为空,同时外连接分为左外连接和右外连接,他们的区别和简单,左外连接即左表数据全部保留,而右连接是右表数据全部保留,SQL示例:

SELECT c.*,o.oname FROM customer AS c LEFT OUTER JOIN orders AS o ON c.cnum = o.cnum;

可以明显的看出外连接比内连接多了一条数据,那就是左表customer没关联上的保留行,同时也可以看出orders表的字段为空。

straight_join:

straight_join不是一个新的join类型,而是对优化器的控制,本质等同于join类型,下面看两个join的执行流程。

EXPLAINSELECT c.*,o.oname FROM orders AS o INNER JOIN  customer AS c ON c.cnum = o.cnum;

执行流程:

EXPLAINSELECT c.*,o.oname FROM customer AS c INNER JOIN  orders AS o ON c.cnum = o.cnum;

执行流程:

不知道大家有没有注意到这两个SQL的不同以及他们的执行流程的不同,这两个SQL的不同之处在于左右表交换了位置,而执行流程是一样的,这里执行流程一样的原因是优化器,优化器会对我们的SQL进行优化,即先扫描行少的,后扫描行多的,这里就要引出straight_join了,刚才也提到了他可以控制优化器即可以强制先执行左表,下面演示强制先扫描orders表。

EXPLAINSELECT c.*,o.oname FROM orders AS o STRAIGHT_JOIN  customer AS c ON c.cnum = o.cnum;

执行流程:

可以看到这里强制先扫描了orders表,那么大家可能会有一个疑问,既然mysql的优化器能够找到执行流程的最优解,为什么还要straight_join呢?,这里有一个问题需要注意的是,对于两张表的关联mysql的优化器可以找到执行流程的最优解,但是如果关联的表多了,mysql的优化器就可能找不到最优解了,就需要我们开发人员或者dba自己去设计执行流程了,当然随着mysql数据库的越来越完善,这种情况也会越来越少。

美女来了!!!喜欢就关注吧!!!