面试官:说一下Mysql中from多表跟join表的区别

2024年 1月 12日 105.8k 0

在日常的数据库查询中,我们常常需要同时涉及多个表的数据。然而,在处理多表查询时,我们可能会使用不同的语法来达到相同的目的。最近,我的一个同事向我提出了一个问题,他的查询语句使用了 FROM 多表的方式,而我通常更喜欢使用 INNER JOIN。他问:“这两种写法有什么区别吗?它们真的是等效的吗?”这个问题激发了我对这两种写法进行更深入思考的兴趣,我决定动手试试。

在动手之前,我们先创建两张表t_ordert_address

DROP TABLE IF EXISTS `t_order`;  
CREATE TABLE `t_order`(  
 id                 bigint UNSIGNED AUTO_INCREMENT COMMENT '自增主键'  
        PRIMARY KEY,  
 `order_no` varchar(16) NOT NULL DEFAULT '' COMMENT '订单编号',  
`create_time`      datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',  
) ENGINE = InnoDB  
  AUTO_INCREMENT = 1  
  DEFAULT CHARSET = utf8mb4 COMMENT ='测试订单表';  


DROP TABLE IF EXISTS `t_address`;  
CREATE TABLE `t_order`(  
 id                 bigint UNSIGNED AUTO_INCREMENT COMMENT '自增主键'  
        PRIMARY KEY,  
 `order_id` bigint UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单ID',  
 `address_no` varchar(16) NOT NULL DEFAULT '' COMMENT '地址编号',  
`create_time`      datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',  
) ENGINE = InnoDB  
  AUTO_INCREMENT = 1  
  DEFAULT CHARSET = utf8mb4 COMMENT ='测试订单地址表';

测试表,无任何业务含义。

FROM 多表

在 SQL 查询中,使用 FROM 多表的方式是一种直接的表连接方法。通过在 FROM 子句中列出多个表,我们可以将这些表的行组合在一起,形成一个结果集。例如:

SELECT o.id,   
       o.order_no,   
       a.id,   
       a.order_id,  
       a.address_no  
FROM t_order o,  
     t_address a  
WHERE o.id = a.order_id;

上述查询使用逗号 , 将表 table1 和 table2 连接在一起,并通过 WHERE 子句指定了连接条件。这种写法看似简单直观,但在实际应用中可能引发一些潜在问题。如果没有明确指定连接条件,就有可能导致笛卡尔积的产生。笛卡尔积是指将一个表的每一行与另一个表的每一行组合在一起,形成一个庞大的结果集。这可能导致性能下降和不正确的查询结果。如下这种写法:

SELECT o.id,   
       o.order_no,   
       a.id,   
       a.order_id,  
       a.address_no  
FROM t_order o,  
     t_address a 

INNER JOIN

相比于直接使用 FROM 多表,使用 INNER JOIN 提供了更为灵活、明确的连接方式,有助于避免潜在的问题并提升查询的可读性。让我们深入探讨 INNER JOIN 的作用和优势。INNER JOIN 是一种显式的连接语法,通过在 FROM 子句中使用 INNER JOIN 关键字明确指定连接条件。其语法如下:

SELECT o.id,  
       o.order_no,  
       a.id,  
       a.order_id,  
       a.address_no  
FROM t_order o  
INNER JOIN t_address a ON o.id = a.order_id;

使用 INNER JOIN 的主要优势之一是明确指定连接条件。通过在 ON 子句中明确写出连接条件,我们避免了使用逗号 , 连接时可能忽略连接条件的风险。这样做有助于代码的可读性和维护性,减少了产生笛卡尔积的可能性。

当然还有其他的JOIN,例如:LEFT JOINRIGHT JOIN。我们会在后续的文章中继续介绍。

结论

在Mysql中使用FROM查询多表和使用INNER JOIN连接,查询结果,查询效率是一样的。

从执行顺序分析

我们先来看一下Msql的SELECT语句的执行顺序:

  • FROM 子句:  从指定的表中获取数据。在这里,涉及到的是表 a 和 b
  • JOIN 操作:  如果查询中包含连接操作(如 INNER JOIN 或 , 符号表示的连接),则会执行连接操作。连接操作的目的是将来自不同表的行组合起来。
  • ON 或 WHERE 子句:  在连接操作后,应用连接条件。如果是 INNER JOIN,连接条件通常包含在 ON 子句中。如果是逗号 , 符号表示的连接,连接条件在 WHERE 子句中。
  • SELECT 子句:  选择要返回的列。在这里,使用 SELECT * 表示选择所有列。
  • GROUP BY 子句:  如果有 GROUP BY 子句,则按指定的列对结果进行分组。
  • HAVING 子句:  如果有 HAVING 子句,则应用于分组后的结果。
  • ORDER BY 子句:  如果有 ORDER BY 子句,则按指定的列对结果进行排序。
  • LIMIT 子句:  如果有 LIMIT 子句,则限制返回的行数。
  • 根据这个执行顺序,可以解释两个查询语句的等效性。在第一个查询 SELECT o.*,a.* FROM t_order o INNER JOIN t_address a ON o.id = a.order_id 中,连接条件是通过 ON 子句指定的;而在第二个查询 SELECT o.*,a.* FROM t_order o,t_address a WHERE o.id = a.order_id 中,连接条件则是通过 WHERE 子句指定的。然而,在执行时,MySQL 会在连接操作阶段考虑这两个条件,并选择合适的连接算法来执行连接操作。

    因此,尽管语法稍有不同,但在执行时,MySQL 解释这两个查询时会考虑连接条件,因此它们具有相同的功能。在实际执行中,MySQL 优化器会选择最有效的执行计划,确保得到相同的结果。

    从执行计划分析

    执行计划是由查询优化器生成的,它是描述查询引擎如何获取和处理数据的计划。优化器在生成执行计划时考虑多个因素,包括表的大小、索引情况、连接条件等,以确定最佳的执行计划。

    我们来执行一下通过FROM的方式的计划如下:

    image.png

    image.png

    接着我们再来看一下INNER JOIN的方式的计划:

    image.png

    image.png

    通过对比,我们发现两者在执行计划上并没有什么区别。

    实例验证

    接文章开头创建的两张表,我们向t_order表中插入100w条数据,t_address表中插入10w条数据进行验证。

    我们开始执行FROM的方式的sql:

    SELECT o.id,
           o.order_no,
           a.id,
           a.order_id,
           a.address_no
    FROM t_order o,
         t_address a
    WHERE o.id = a.order_id  limit 0, 100000;
    

    执行结果:

    image.png

    image.png

    我们在执行一次INNER JOIN的方式的sql:

    SELECT o.id,
           o.order_no,
           a.id,
           a.order_id,
           a.address_no
    FROM t_order o
    INNER JOIN t_address a ON o.id = a.order_id limit 0, 100000;
    

    执行结果:

    image.png

    image.png

    可以看出两者查询所花费的时间相差无几。再看一下两种方式的执行计划,也是一样的。

    总结

    由上述我们可以看出在Mysql中使用FROM查询多表和使用JOIN连接,查询结果以及查询效率是一样的。

    但是我们最好还是使用 INNER JOIN ,它的写法更直观、更易于优化器理解,有助于生成更优化的执行计划。

    本文已收录于我的个人博客:码农Academy的博客,专注分享Java技术干货,包括Java基础、Spring Boot、Spring Cloud、Mysql、Redis、Elasticsearch、中间件、架构设计、面试题、程序员攻略等

    版权声明:本文系公众号 "码农Academy" 原创,转载、引用本文内容请注明出处。

    相关文章

    Oracle如何使用授予和撤销权限的语法和示例
    Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
    下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
    社区版oceanbase安装
    Oracle 导出CSV工具-sqluldr2
    ETL数据集成丨快速将MySQL数据迁移至Doris数据库

    发布评论