• 售前

  • 售后

热门帖子
入门百科

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

[复制链接]
红星酒斩 显示全部楼层 发表于 2021-10-26 12:54:25 |阅读模式 打印 上一主题 下一主题
背景先容
迩来在写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条记载。

对应的表结构如下:
  1. CREATE TABLE [code]t_poetry
复制代码
(
  1. id
复制代码
bigint(20) NOT NULL AUTO_INCREMENT,
  1. poetry_id
复制代码
bigint(20) NOT NULL COMMENT '诗词id',
  1. poetry_name
复制代码
varchar(200) NOT NULL COMMENT '诗词名称',<font color=red>
  1. author_id
复制代码
bigint(20) NOT NULL COMMENT '作者id'</font>PRIMARY KEY (
  1. id
复制代码
),UNIQUE KEY
  1. pid_idx
复制代码
(
  1. poetry_id
复制代码
) USING BTREE,KEY
  1. aid_idx
复制代码
(
  1. author_id
复制代码
) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=291270 DEFAULT CHARSET=utf8mb4[/code]
  1. CREATE TABLE [code]t_author
复制代码
(
  1. id
复制代码
int(15) NOT NULL AUTO_INCREMENT,
  1. author_id
复制代码
bigint(20) NOT NULL,</font>
  1. author_name
复制代码
varchar(32) NOT NULL,
  1. dynasty
复制代码
varchar(16) NOT NULL,
  1. poetry_num
复制代码
int(8) NOT NULL DEFAULT '0'PRIMARY KEY (
  1. id
复制代码
),<font color=red>UNIQUE KEY
  1. authorid_idx
复制代码
(
  1. author_id
复制代码
) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=13339 DEFAULT CHARSET=utf8mb4[/code]执行计分别析 IN 执行过程
sql示例:
  1. 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示例:
  1. select <em>from tabA where exists (select </em>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语句:
  1. select <em>from t_author ta where author_id in
  2. (select author_id from t_poetry tp where tp.poetry_id>3650 );
  3. </em>
复制代码
包罗Exists的SQL语句:
  1. <em>select </em>from t_author ta where exists
  2. (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表的遍历耗时差异对团体服从影响可以忽略,执行计划里多了一行<auto_key>,在接近全表扫描的情况下,mysql优化器选择了auto_key来遍历t_author表:


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


实行结论
根据上述两个实行及实行效果,我们可以较清楚的明白IN 和Exists的执行过程,并归纳出IN 和Exists的实用场景:
IN查询在内部表和外部表上都可以利用到索引; Exists查询仅在内部表上可以利用到索引;当子查询效果集很大,而外部表较小的时候,Exists的Block Nested Loop(Block 嵌套循环)的作用开始显现,并增补外部表无法用到索引的缺陷,查询服从会优于IN。当子查询效果集较小,而外部表很大的时候,Exists的Block嵌套循环优化效果不显着,IN 的外表索引优势占主要作用,此时IN的查询服从会优于Exists。 网上的说法不正确。其实“表的规模”不是看内部表和外部表,而是外部表和子查询效果集。最后一点,也是最重要的一点:凡间没有绝对的真理,把握事物的本质,针对差异的场景进行实践验证才是最可靠有用的方法。 实行过程中发现的标题增补
仅对差异数据集情况下的上述exists语句分析时发现,数据集越大,消耗的时间反而变小,觉得很奇怪。
具体查询条件为:
  1. where tp.poetry_id>3650,耗时0.13S
  2. where tp.poetry_id>293650,耗时0.46S
复制代码
可能原因:条件值大,查询越靠后,必要遍历的记载越多,造成终极消耗越多的时间。这个表明有待进一步验证后再增补。

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作