暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL中的 in和exists详解

MySQL中的 in和exists详解

程序员恰恰 2024-03-13
91

在MySQL中，小表驱动大表是一种常见的查询优化策略。这个策略基于一个简单的原则：在执行连接（JOIN）操作时，以行数较少的表（即“小表”）为基础，去驱动扫描行数较多的表（即“大表”）。这样做的主要目的是减少查询过程中需要处理的数据量，从而提高查询效率。

为什么要小表驱动大表呢?

减少扫描次数：当使用小表去驱动大表时，数据库引擎需要遍历的记录数会减少，因为它首先处理的是行数较少的表。这意味着在查找匹配项时，对大表的扫描次数会相应减少。


高效利用索引：如果小表与大表之间的连接条件能够利用到大表上的索引，那么这种方式可以更高效地利用索引，因为数据库引擎可以快速地在大表中查找到与小表中每行相匹配的行。

减少内存和临时表的使用：在处理连接查询时，数据库可能需要在内存中创建临时表来存储中间结果。小表驱动大表可以减少这些临时表可能需要存储的数据量，从而减少内存使用和磁盘I/O操作。

举例说明:


假设我们有两个表：employees（员工表）和sales（销售记录表）。employees表包含公司所有员工的信息，假设有1,000行数据；sales表记录了所有的销售活动，假设有100,000行数据。


现在我们想要查询所有员工及其对应的销售记录总数。这里涉及到一个JOIN操作。


如果我们让sales表（大表）驱动employees表（小表），数据库引擎需要遍历sales表中的每一行，对于每一行销售记录，都去employees表中查找匹配的员工信息。这种情况下，数据库引擎的工作量非常大，因为它需要执行大量的查找操作。

for(int i=1000;.......){
     for(int j=5;......){
     }
}


相反，如果我们让employees表（小表）驱动sales表（大表），数据库引擎只需要遍历employees表的每一行，然后对于每个员工，去sales表中查找匹配的销售记录。虽然最终还是要处理所有的销售记录，但通过这种方式，数据库引擎可以更有效地利用sales表上的索引来快速定位数据，从而减少查找时间和资源消耗


类似于：

for(int i=5;.......){
     for(int j=1000;......){
     }
}


MySql中EXISTS基本语法
SELECT * FROM table WHERE EXISTS(sub_query)
sub_query：子查询语句


可以理解为：将主查询的数据放入子查询中做条件验证，根据验证结果（true或false）来决定主查询的数据是否得以保留。

举例子：
SELECT * FROM a WHERE EXISTS(SELECT 1 FROM b WHERE a.`key` = b.`key`)


EXISTS查询步骤如下：

a. 首先执行外部查询SELECT * FROM a，并缓存结果集R；
b. 遍历外部结果集R，并代入子查询SELECT 1 FROM b WHERE a.`key` = b.`key`中使用条件a.`key` = b.`key`进行查询；
c. 子查询中如果符合条件a.`key` = b.`key`，则EXISTS返回true，那么R中这一行作为结果行，反之，则不能作为结果。

所以：可以推断出，上面的sql中，a表越小越好。


下面再看下使用in的情况：

SELECT * FROM a WHERE a.`key` IN (SELECT b.`key` FROM b)


IN查询步骤如下：


a.执行子查询：首先执行括号内的子查询SELECT b.key FROM b。这一步会扫描表b，并得到所有存在于表b中的key值，形成一个结果集。

b.缓存结果集：将上一步得到的结果集缓存起来。这个结果集包含了所有在表b中可以找到的key值。

c.执行外部查询：然后执行外部查询SELECT * FROM a，但在检索表a的每一行时，会检查每行的key值是否存在于第一步得到的结果集中。


所以：可以推断出：上面的sql中，b表越小越好



测试：假设有（大表）学生选课表（student_info）,有100万数据。



表的定义如下：外键course_id（课程id）



另外有(小表)课程表（course），有100条数据。



当student_info（外层大表） 表中数据多于 course（子表小表） 表中的数据时，这时我们使用IN 优于 EXISTS。

用Exist



用in




当student_info（子表小表） 表中数据多于course （外层大表） 表中的数据时(我们这里只是假设)，这时我们使用EXISTS 优于 IN.
用EXISTS
用IN



总结一下：in后面跟的是小表，exists后面跟的是大表，简记：in小，exists大。

附件:相关sql
SELECT * FROM course WHERE EXISTS(SELECT 1 FROM student_info WHERE course.`course_id` = student_info.`course_id`)


#创建索引
CREATE INDEX idx_course_id ON course(course_id);
#创建索引
CREATE INDEX idx_course_id ON student_info(course_id);

#优化原则,小表驱动大表

#表A的数据大于表B,用in优于EXISTS

#测试EXISTS
SELECT * FROM student_info A WHERE  EXISTS (SELECT 1 FROM course B WHERE B.`course_id` = A.`course_id`)
#测试in 
SELECT * FROM student_info A WHERE A.`course_id` in (SELECT B.`course_id` FROM course B )


#当A表的数据集小于B表的数据集时，用EXISTS优于IN。
#测试EXISTS
SELECT * FROM course A WHERE  EXISTS(SELECT 1 FROM student_info B WHERE A.`course_id` = B.`course_id`)
#测试in 
SELECT * FROM course A WHERE A.`course_id`in (SELECT B.`course_id` FROM student_info B )


文章转载自程序员恰恰，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论