MySQL查询语句中的IN 和Exists 对比分析

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 背景介绍最近在写SQL语句时,对选择IN 还是Exists 犹豫不决,于是把两种方法的SQL都写出来对比一下执行效率,发现IN的查询效率比Exists高了很多,于是想当然的认为IN的效率比Exists好,但本着寻根究底的原则,我想知道这个结论是否适用所有场景,以及为什么会出现这个结果。

背景介绍

最近在写SQL语句时,对选择IN 还是Exists 犹豫不决,于是把两种方法的SQL都写出来对比一下执行效率,发现IN的查询效率比Exists高了很多,于是想当然的认为IN的效率比Exists好,但本着寻根究底的原则,我想知道这个结论是否适用所有场景,以及为什么会出现这个结果。

网上查了一下相关资料,大体可以归纳为:外部表小,内部表大时,适用Exists;外部表大,内部表小时,适用IN。那我就困惑了,因为我的SQL语句里面,外表只有1W级别的数据,内表有30W级别的数据,按网上的说法应该是Exists的效率会比IN高的,但我的结果刚好相反!!

“没有调查就没有发言权”!于是我开始研究IN 和Exists的实际执行过程,从实践的角度出发,在根本上去寻找原因,于是有了这篇博文分享。

实验数据

我的实验数据包括两张表:t_author表 和 t_poetry表。

对应表的数据量:

t_author表,13355条记录;

t_poetry表,289917条记录。

对应的表结构如下:

CREATE TABLE t_poetry (

id bigint(20) NOT NULL AUTO_INCREMENT,

poetry_id bigint(20) NOT NULL COMMENT '诗词id',

poetry_name varchar(200) NOT NULL COMMENT '诗词名称',

author_id bigint(20) NOT NULL COMMENT '作者id'

PRIMARY KEY (id),

UNIQUE KEY pid_idx (poetry_id) USING BTREE,

KEY aid_idx (author_id) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=291270 DEFAULT CHARSET=utf8mb4

CREATE TABLE t_author (

id int(15) NOT NULL AUTO_INCREMENT,

author_id bigint(20) NOT NULL,

author_name varchar(32) NOT NULL,

dynasty varchar(16) NOT NULL,

poetry_num int(8) NOT NULL DEFAULT '0'

PRIMARY KEY (id),

UNIQUE KEY authorid_idx (author_id) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=13339 DEFAULT CHARSET=utf8mb4

执行计划分析

IN 执行过程

sql示例:select * from tabA where tabA.x in (select x from tabB where y>0 );

其执行计划:

(1)执行tabB表的子查询,得到结果集B,可以使用到tabB表的索引y;

(2)执行tabA表的查询,查询条件是tabA.x在结果集B里面,可以使用到tabA表的索引x。

Exists执行过程

sql示例:select from tabA where exists (select from tabB where y>0);

其执行计划:

(1)先将tabA表所有记录取到。

(2)逐行针对tabA表的记录,去关联tabB表,判断tabB表的子查询是否有返回数据,5.5之后的版本使用Block Nested Loop(Block 嵌套循环)。

(3)如果子查询有返回数据,则将tabA当前记录返回到结果集。

tabA相当于取全表数据遍历,tabB可以使用到索引。

实验过程

实验针对相同结果集的IN和Exists 的SQL语句进行分析。

包含IN的SQL语句:

select from t_author ta where author_id in

(select author_id from t_poetry tp where tp.poetry_id>3650 );

包含Exists的SQL语句:

select from t_author ta where exists

(select * from t_poetry tp where tp.poetry_id>3650 and tp.author_id=ta.author_id);

第一次实验

数据情况

t_author表,13355条记录;t_poetry表,子查询筛选结果集 where poetry_id>293650 ,121条记录;

执行结果

使用exists耗时0.94S, 使用in耗时0.03S,IN 效率高于Exists。

原因分析

对t_poetry表的子查询结果集很小,且两者在t_poetry表都能使用索引,对t_poetry子查询的消耗基本一致。两者区别在于,使用 in 时,t_author表能使用索引:

使用exists时,t_author表全表扫描:

在子查询结果集较小时,查询耗时主要表现在对t_author表的遍历上。

第二次实验

数据情况

t_author表,13355条记录;t_poetry表,子查询筛选结果集 where poetry_id>3650 ,287838条记录;

执行时间

使用exists耗时0.12S, 使用in耗时0.48S,Exists IN。

原因分析

两者的索引使用情况跟第一次实验是一致的,唯一区别是子查询筛选结果集的大小不同,但实验结果已经跟第一次的不同了。这种情况下子查询结果集很大,我们看看mysql的查询计划:

使用in时,由于子查询结果集很大,对t_author和t_poetry表都接近于全表扫描,此时对t_author表的遍历耗时差异对整体效率影响可以忽略,执行计划里多了一行,在接近全表扫描的情况下,mysql优化器选择了auto_key来遍历t_author表:

使用exists时,数据量的变化没有带来执行计划的改变,但由于子查询结果集很大,5.5以后的MySQL版本在exists匹配查询结果时使用的是Block Nested-Loop(Block嵌套循环,引入join buffer,类似于缓存功能)开始对查询效率产生显著影响,尤其针对子查询结果集很大的情况下能显著改善查询匹配效率:

实验结论

根据上述两个实验及实验结果,我们可以较清晰的理解IN 和Exists的执行过程,并归纳出IN 和Exists的适用场景:

IN查询在内部表和外部表上都可以使用到索引;

Exists查询仅在内部表上可以使用到索引;

子查询结果集很大,而外部表较小的时候,Exists的Block Nested Loop(Block 嵌套循环)的作用开始显现,并弥补外部表无法用到索引的缺陷,查询效率会优于IN。

子查询结果集较小,而外部表很大的时候,Exists的Block嵌套循环优化效果不明显,IN 的外表索引优势占主要作用,此时IN的查询效率会优于Exists。

网上的说法不准确。其实“表的规模”不是看内部表和外部表,而是外部表和子查询结果集。

最后一点,也是最重要的一点:世间没有绝对的真理,掌握事物的本质,针对不同的场景进行实践验证才是最可靠有效的方法。

实验过程中发现的问题补充

仅对不同数据集情况下的上述exists语句分析时发现,数据集越大,消耗的时间反而变小,觉得很奇怪。

具体查询条件为:

where tp.poetry_id>3650,耗时0.13S

where tp.poetry_id>293650,耗时0.46S

可能原因:条件值大,查询越靠后,需要遍历的记录越多,造成最终消耗越多的时间。这个解释有待进一步验证后再补充。

欢迎工作一到五年的Java程序员朋友们加入Java架构开发:744677563

本群提供免费的学习指导 架构资料 以及免费的解答

不懂得问题都可以在本群提出来 之后还会有职业生涯规划以及面试指导

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
17天前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
|
25天前
|
SQL 前端开发 关系型数据库
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
49 9
|
27天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
59 3
|
1月前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列(5)作者——LJS[含MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]
MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页、INSERT INTO SELECT / FROM查询结合精例等详解步骤及常见报错问题所对应的解决方法
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
171 1
|
2月前
|
SQL Java 关系型数据库
java连接mysql查询数据(基础版,无框架)
【10月更文挑战第12天】该示例展示了如何使用Java通过JDBC连接MySQL数据库并查询数据。首先在项目中引入`mysql-connector-java`依赖,然后通过`JdbcUtil`类中的`main`方法实现数据库连接、执行SQL查询及结果处理,最后关闭相关资源。
|
1月前
|
SQL 关系型数据库 MySQL
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
46 1
|
2月前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现,而持久性则依赖于事务日志中的`Redo Log`。在MySQL中,`Redo Log`确保已提交事务的数据能持久保存,即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改,待事务提交时写入磁盘。此外,`Redo Log`采用简单的物理日志格式和高效的顺序IO,确保快速提交。通过不同的落盘策略,可在性能和安全性之间做出权衡。
1661 14
|
2月前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则,包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限,后者又优先于表级别权限。在权限冲突时,更严格的权限将被优先执行,确保数据库的安全性与资源合理分配。
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
76 0