MySQL关联查询时,我们为什么建议小表驱动大表?

作者:留兰香丶

blog.csdn.net/codejas/article/details/78632883

有的时候我们在操作数据库时会将两个或多个数据表关联起来通过一些条件筛选数据,在关联表时我们要遵循一些原则,这样会使我们编写的SQL 语句在效率上快很多。

一、优化原则

小表驱动大表,即小的数据集驱动大得数据集。在知道什么是小表驱动达大表之前,我们先来了解两个查询关键字,IN 与 EXISTS。我们通过两段查询语句先来了解一下它们的作用。我建立了两张表,一张员工表,一张部门表,员工表中有部门id 这个属性,将这两张表关联起来。

我们先使用IN 来查询数据:

SELECT * 
FROM t_emp 
WHERE dept_id IN (SELECT dept_id FROM t_dept) 
LIMIT 5;

查询结果:由于有很多的员工信息,在这里我就只查询5 条数据。

+-------------+----------+------------+--------------+---------+
| emp_id      | emp_name | emp_gender | emp_email    | dept_id |
+-------------+----------+------------+--------------+---------+
| 00000000177 | 41d80    | m          | 41d80@zc.com |       1 |
| 00000000178 | a74b8    | m          | a74b8@zc.com |       1 |
| 00000000179 | 661ca    | m          | 661ca@zc.com |       1 |
| 00000000180 | 9413d    | m          | 9413d@zc.com |       1 |
| 00000000181 | 7d577    | m          | 7d577@zc.com |       1 |
+-------------+----------+------------+--------------+---------+

接下里使用EXISTS 来查询数据:

 SELECT * 
 FROM t_emp 
 WHERE EXISTS 
     (SELECT 1 
     FROM t_dept 
     WHERE t_dept.dept_id = t_emp.dept_id) 
 LIMIT 5;

查询结果:与上面的结果一样。

+-------------+----------+------------+--------------+---------+
| emp_id      | emp_name | emp_gender | emp_email    | dept_id |
+-------------+----------+------------+--------------+---------+
| 00000000177 | 41d80    | m          | 41d80@zc.com |       1 |
| 00000000178 | a74b8    | m          | a74b8@zc.com |       1 |
| 00000000179 | 661ca    | m          | 661ca@zc.com |       1 |
| 00000000180 | 9413d    | m          | 9413d@zc.com |       1 |
| 00000000181 | 7d577    | m          | 7d577@zc.com |       1 |
+-------------+----------+------------+--------------+---------+

既然IN 和 EXISTS 都可以用来查询数据,那它们两个有什么区别呢?

SELECT * 
FROM t_emp 
WHERE dept_id IN 
    (SELECT dept_id 
    FROM t_dept);

// 这条SQL 语句相当于:
for SELECT dept_id FROM t_dept
    for SELECT * FROM t_emp WHERE t_emp.dept_id = t_dept.dept_id

这里虽然我们编写的SQL 语句是主查询员工信息,子查询部门id ,但是MySql 的执行顺序会先执行子查询,再执行主查询,然后获得我们要查询的数据。

 SELECT * 
 FROM t_emp 
 WHERE EXISTS 
     (SELECT 1 
     FROM t_dept 
     WHERE t_dept.dept_id = t_emp.dept_id);

// 这条SQL 语句相当于:     
for SELECT * FROM t_emp 
    for SELECT * FROM t_dept  WHERE t_dept.dept_id = t_emp.dept_id          

我们可以将EXISTS 语法理解为:将主查询的数据放在子查询中做条件验证,根据结果TRUE 和 FALSE 来决定主查询中的数据是否需要保留。EXISTS 子查询只返回TRUE 或 FALSE ,因此子查询中的SELECT * 可以是SELECT 1 或者其他,MySql 的官方说在实际执行时会忽略SELECT 清单,因此是没有 什么区别的。EXISTS 子查询其实在执行时,MySql 已经对它做了一些优化并不是对每条数据进行对比。

二、总结

在实际操作过程中我们要对两张表的dept_id 都设置索引。在一开始我们就讲了一个优化原则即:小表驱动大表,在我们使用IN 进行关联查询时,通过上面IN 操作的执行顺序,我们是先查询部门表再根据部门表查出来的id 信息查询员工信息。我们都知道员工表肯定会有很多的员工信息,但是部门表一般只会有很少的数据信息,我们事先通过查询部门表信息查询员工信息,以小表(t_dept)的查询结果,去驱动大表(t_emp),这种查询方式是效率很高的,也是值得提倡的。

但是我们使用EXISTS 查询时,首先查询员工表,然后根据部门表的查询条件返回的TRUE 或者 FALSE ,再决定员工表中的信息是否需要保留。这不就是用大的数据表(t_emp) 去驱动小的数据表小的数据表(t_dept)了吗?虽然这种方式也可以查出我们想要的数据,但是这种查询方式是不值得提倡的。

当t_emp 表中数据多于 t_dept 表中的数据时,这时我们使用IN 优于 EXISTS。当t_dept 表中数据多于 t_emp 表中的数据时(我们这里只是假设),这时我们使用EXISTS 优于 IN。因此是使用IN 还是使用EXISTS 就需要根据我们的需求决定了。但是如果两张表中的数据量差不多时那么是使用IN 还是使用 EXISTS 差别不大。

更多好文章Java高并发系列(共34篇)MySql高手系列(共27篇)Maven高手系列(共10篇)Mybatis系列(共12篇)聊聊db和缓存一致性常见的实现方式接口幂等性这么重要,它是什么?怎么实现?泛型,有点难度,会让很多人懵逼,那是因为你没有看这篇文章!世界上最好的关系是相互成就,点赞转发 感恩开心????路人甲java
▲长按图片识别二维码关注路人甲Java:工作10年的前阿里P7,所有文章以系列的方式呈现,带领大家成为java高手,目前已出:java高并发系列、mysql高手系列、Maven高手系列、mybatis系列、spring系列,正在连载springcloud系列,欢迎关注!
<p> <span><span style="font-size:14px;"><strong>一、课程简介</strong></span></span> </p> <p> <span><span style="color:#E56600;font-size:14px;">  『</span><span style="color:#E56600;font-size:14px;">Java学习指南系列</span><span style="color:#E56600;"></span><span style="color:#E56600;font-size:14px;">』</span></span><span>的第21篇教程 ,MyBatis 篇,是Java系列的高级课程。本篇介绍 MyBatis 的使用方法,使用 MyBatis 进行数据库开发的相关技术。</span> </p> <p> <span><span></span><span>    MyBatis 是一个基于JDBC的数据库工具框架,使用它可以快速地实现对数据库的访问操作。与之相似的框架还有 af-sql 和 Hibernate。</span><span></span><span style="font-size:14px;"></span><span style="font-size:14px;"></span><br /> </span> </p> <p> <span><span style="font-size:14px;"><br /> </span></span> </p> <p> <span><span><strong>二、主要内容 </strong></span><span style="font-size:14px;"><strong></strong></span></span> </p> <span style="color:#3D3D3D;"></span> <p> <span style="font-size:14px;">本篇主要包含以下内容:</span> </p> <p> <span style="font-size:14px;">* 在项目中加入 MyBatis 支持</span> </p> <p> <span style="font-size:14px;">* 基于 MyBatis 的查询</span> </p> <p> <span style="font-size:14px;">* 基于 MyBatis 的插入,自增主键的配置</span> </p> <p> <span style="font-size:14px;">* 基于 MyBatis 的更新与删除操作</span> </p> <p> <span style="font-size:14px;">* ResultMap 自定义结果映射</span> </p> <p> <span style="font-size:14px;">* Dynamic SQL 动态可变的SQL</span> </p> <p> <span style="font-size:14px;">* 注解方式的 Mapper定义</span> </p> <p> <span style="font-size:14px;">* MyBatis 在Web项目中的应用,全局工厂实例的定义</span> </p> <p> <span style="font-size:14px;">* 添加Log4j支持,输出MyBatis的日志</span><br /> <span style="font-size:14px;"></span> </p> <p> <span><span style="font-size:14px;"><strong>三、课程体系</strong></span></span> </p> <p> <span><span style="font-size:14px;">〖Java学习指南</span><span style="font-size:14px;">系列</span><span style="font-size:14px;">〗</span></span><span>:包含入门与进阶语法,Swing桌面开发,安卓开发,JavaFX开发,网络通信Socket,数据结构与算法等课程。</span> </p> <p> <span><span style="color:#E56600;font-size:14px;">〖网站开发</span><span style="font-size:14px;">系列</span><span style="color:#E56600;"></span><span style="color:#E56600;font-size:14px;">〗</span></span><span><span style="color:#E56600;font-size:14px;">:</span><span style="font-size:14px;">包含网页基础、网站入门、数据库、网站中级、FreeMarker、网站高级、项目应用、MyBatis、Redis等课程。</span></span> </p>
相关推荐
©️2020 CSDN 皮肤主题: 精致技术 设计师:CSDN官方博客 返回首页