比较分析MySQL语句中的IN 和Exists
发布时间：2022-02-28 21:28:45 所属栏目：MySql教程 来源：互联网
导读：最近在写SQL语句时，对选择IN 还是Exists 犹豫不决，于是把两种方法的SQL都写出来对比一下执行效率，发现IN的查询效率比Exists高了很多，于是想当然的认为IN的效率比Exists好，但本着寻根究底的原则，我想知道这个结论是否适用所有场景，以及为什么会出现
|
最近在写SQL语句时，对选择IN 还是Exists 犹豫不决，于是把两种方法的SQL都写出来对比一下执行效率，发现IN的查询效率比Exists高了很多，于是想当然的认为IN的效率比Exists好，但本着寻根究底的原则，我想知道这个结论是否适用所有场景，以及为什么会出现这个结果。 “没有调查就没有发言权”！于是我开始研究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 '诗词名称', <font color=red> author_id bigint(20) NOT NULL COMMENT '作者id'</font> 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 执行计划分析 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。 实验过程 实验针对相同结果集的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表能使用索引: 对比分析MySQL语句中的IN 和Exists 使用exists时，t_author表全表扫描: 对比分析MySQL语句中的IN 和Exists 在子查询结果集较小时，查询耗时主要表现在对t_author表的遍历上。 第二次实验数据情况 t_author表，13355条记录；t_poetry表，子查询筛选结果集 where poetry_id>3650 ，287838条记录； 执行时间 使用exists耗时0.12S， 使用in耗时0.48S，Exists效率高于 IN。 实验结论 根据上述两个实验及实验结果，我们可以较清晰的理解IN 和Exists的执行过程，并归纳出IN 和Exists的适用场景： IN查询在内部表和外部表上都可以使用到索引； Exists查询仅在内部表上可以使用到索引；当子查询结果集很大，而外部表较小的时候，Exists的Block Nested Loop(Block 嵌套循环)的作用开始显现，并弥补外部表无法用到索引的缺陷，查询效率会优于IN。当子查询结果集较小，而外部表很大的时候，Exists的Block嵌套循环优化效果不明显，IN 的外表索引优势占主要作用，此时IN的查询效率会优于Exists。 网上的说法不准确。其实“表的规模”不是看内部表和外部表，而是外部表和子查询结果集。最后一点，也是最重要的一点：世间没有绝对的真理，掌握事物的本质，针对不同的场景进行实践验证才是最可靠有效的方法。 实验过程中发现的问题补充。 （编辑：温州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
