MySQL5.7和8.0中in和exists关键字

最新推荐文章于 2024-06-29 10:36:00 发布
最新推荐文章于 2024-06-29 10:36:00 发布
阅读量1.7k 收藏 4
点赞数 7
分类专栏： 数据库 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/czx2018/article/details/107287575
版权

之前看了网上很多关于in和exists性能相关的博客，但总感觉自己测试的结果和他们说的不太一样，所以写下这篇博客，记录下自己测试后得出的总结。特别强调，MySQL5.7和8.0中对这两个关键字有不同的优化，所以我要分开两个版本来讨论。

数据准备

我准备了一张tb_class表和一张tb_stu表
在这里插入图片描述
在这里插入图片描述
注：600个班级，12万个学生。tb_stu是大表，tb_class是小表。为了避免索引优化带来的影响，所以我两张表的id都没有设置主键和索引。

MySQL5.7测试

测试查询效率

我们首先测试一下当外层表时小表的时候，in和exists谁的效率比较高。

在这里插入图片描述
在MySQL5.7中，当外层的表是小表的时候，in的效率会优于exists。

用explain看一下两个sql的执行计划
在这里插入图片描述
从执行计划中看到，exists语句中的select_type是 DEPENDENT SUBQUERY ，我认为性能的消耗主要是在这里，因为 DEPENDENT SUBQUERY 循环嵌套子查询，其效率可想而知。

那么当外层的表时大表的时候，in和exists的效率又是谁比较高呢？

在MySQL5.7中，当外层的表是大表的时候，in的效率依然高于exists。

在这里插入图片描述
可以看到当外层的表是大表的时候，执行效率就非常低了，但是in的效率依旧高于exists。
使用explain查看一下两个sql的执行计划

在这里插入图片描述
经过以上的测试，我发现：外层表不论是大表还是小表，in的效率都要高于exists。网上说什么小表驱动大表，感觉那帮人也没真正自己测试过。

查询效率低的优化策略

从上面的测试我们可以看出来，当大表的数据量超过10W+时，查询效率已经开始变低了（我把大于2秒的查询定为慢查询）。
那么优化方案是什么呢，我的策略是加索引。
我们先在tb_stu的class_id字段上加上btree索引
添加上索引之后，再来观察一下外层表是小表时候的查询效率

在这里插入图片描述

加上索引之后的效果就非常明显了，原先exists查询耗时2s多，现在只耗时0.004s

在这里插入图片描述
通过explain可以发现，rows从原来的12w+变成了只有130。

我们再来对比一下外层表是大表的时候，索引是否能有效。
在这里插入图片描述

震惊！！！exists居然还是耗时8s多！！！
我猜测原因是：当使用exists时，外层表的索引无法在子查询中生效
查看explain的结果
在这里插入图片描述

MySQL8.0测试

测试查询效率

我将数据库换成MySQL8.0，先测试一下外层表是小表的时候，in和exists的查询效率
在这里插入图片描述
我们发现，在MySQL8.0中，in和exists的执行效率是一样高的。
甚至我使用explain查看的执行计划都是一样的。

在这里插入图片描述
将外层表改为大表，再测试一下：

在这里插入图片描述

查询效率依旧一样快有木有！
他俩的explain执行计划也是一样的：
在这里插入图片描述

最后总结

  1. 在MySQL5.7中，in的效率要高于exists，但在8.0中，他俩的效率是一样的
  2. 在MySQL5.7中，外层表的字段使用in查询不会使这个字段的索引失效，但是如果使用exists就会使索引失效了
  3. MySQL8.0的查询效率真的比5.7好很多，如果是新项目的话，强烈建议使用MySQL8.0
荒野大码农
关注
专栏目录
Mysql5.7及8.0版本索引失效情况汇总
01-19
没有特殊说明，测试环境均为MySQL8.0，早期版本可能会有更多情况导致索引失效。8.0失效的情况，早期版本也失效；8.0不失效的情况，早期版本可能失效。 所有测试默认不考虑表为空的情况，特殊情况文中会有说明。 本文...
MySQL 8.0 官方文档 第八章 优化（二十六）—— 使用EXISTS策略优化子查询
HZ_Samuel博客
05-21 514
目录第八章 优化（二十六）—— 使用EXISTS策略优化子查询8.2 优化SQL语句8.2.2 优化子查询、派生表、视图引用和公共表表达式8.2.2.3 使用EXISTS策略优化子查询 第八章 优化（二十六）—— 使用EXISTS策略优化子查询 8.2 优化SQL语句 8.2.2 优化子查询、派生表、视图引用和公共表表达式 8.2.2.3 使用EXISTS策略优化子查询 某些优化适用于使用IN（或 = ANY）运算符测试子查询结果这样的比较。本节将讨论这些优化，特别是针对NULL带来的挑战。讨论的最后一
mysql if exists 语法_mysqlifexists语法
weixin_39629631的博客
01-19 1034
[忽略] 不支持 不支持 Mysql ‘IF NOT EXIST’ 语法; 不支持 Oracle ‘CREATE DATABASE [db_name]’ 语法; 状态图 创建表语法 SQL Server CREATE TABLE [ ......2.2.1 create function, 创建函数 适用语法:来创建函数 2.2.2 调用函数 2.2.3 drop function , 删除函数 ...
MySQL 5.7 与 MySQL 8.0：关键差异与升级考量
最新发布
My_wife_QBL的博客
06-29 2229
MySQL作为全球最受欢迎的开源关系型数据库管理系统之一，其每个新版本的发布都带来了性能、功能和安全性的显著提升。MySQL 5.7和MySQL 8.0是两个重要的里程碑版本，它们在多个方面都有显著的差异。本文将深入探讨这两个版本之间的关键差异，并提供升级到MySQL 8.0的考量因素，以帮助数据库管理员和开发人员做出明智的决策。
MySQL学习笔记.查询数据1
Linkage interrupt
10-04 712
单表查询查询所有字段查询指定字段查询多个字段查询指定记录带IN关键字查询带BETWEEN AND的范围查询带LIKE的字符匹配查询 查询所有字段 语法格式： SELECT * FROM 表名; 查询指定字段 语法格式： SELECT 字段名 FROM 表名; 查询多个字段 语法格式： SELECT 字段名1,字段名2,... FROM 表名; 查询指定记录 语法格式： SELECT 字段名1,字段名2... FROM 表名 WHERE 查询条件 查询价格为8.2的水果 SELECT f_nam
MySQL EXISTS 关键字使用
欧阳啸天的专栏
08-15 3596
MySQL exists 关键字使用简单介绍
Mysql5.7 in,exists,表直接关联性能比较
关注系统性能调优
06-19 2279
mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.9-log | +-----------+ 1 row in set (0.00 sec) 制造数据 DROP TABLE IF EXISTS test;  CREATE TABLE test(      ID INT(10) N
MySQL优化
ks_1998的博客
01-03 2422
MySQL优化
简述Oracle中in和exists的不同
09-09
在Oracle数据库中，`IN`和`EXISTS`都是用于子查询的比较操作符，但它们在执行方式和性能上存在显著差异。许多人误以为`EXISTS`总是比`IN`更快，但实际情况并非如此，具体表现取决于数据的分布和索引的利用。 1. **...
对比分析MySQL语句中的IN 和Exists
09-09
在MySQL中，`IN` 和 `EXISTS` 都是用来在查询中进行子查询比较的两个关键字，但它们的工作方式和性能表现有所不同。通常，关于它们的效率比较存在一定的误解，这需要根据具体情况进行分析。 首先，`IN` 会将外部表...
MySQL中in与exists的使用及区别介绍
09-09
在MySQL中，`IN` 和 `EXISTS` 都是用来在查询中筛选满足特定条件的行，但它们的工作原理和性能表现有所不同。了解这两者的差异对于优化SQL查询至关重要。 首先，`IN` 关键字用于在主查询中比较某个列的值是否存在于...
mysql基础
2301_78827178的博客
07-24 180
小数类型为DECIMAL，禁止使用FLOAT和DOUBLE 因为在存储的时候，FLOAT和DOUBLE都存在精度损失的问题，很可能比较值的时候，得到不正确的结果，如果存储的数值范围超过DECIMAL的范围，建议将数据拆分为整数和小数分开存储。公用表表达式（或通用表表达式）简称为CTE， CTE是一个命名的临时结果集，作用范围是当前语句，CTE可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE可以引用其他CTE，但是子查询不能引用其他的子查询，所以可以考虑代替子查询。
MySQL 8.0中对EXISTS、NOT EXISTS的持续优化
老叶茶馆
04-19 3337
继续给MySQL 8.0吹吹水MySQL在8.0.16版本之前，对 IN 和 EXISTS 处理是不一样的，EXISTS只能采用子查询方式，所以执行计划中能看到DEPENDENT SUB...
mysql中exists的用法详解
热门推荐
zhudongyanga的博客
06-02 1万+
👉 有了上面的执行原理的说明，我们明白了一个道理：内层循环次数的多少不会影响到外层的次数，但是外层循环的次数直接会影响到内层循环的次数，外层循环每多一次，内层循环就需要多完整的一次循环，所以我们优化的目标其实就是使外层的循环次数尽量少，总结来说：小表驱动大表。
MySQL5.7 EXISTS的用法
s1162276945的博客
05-25 483
EXISTS 内外层两个表之间一定要有某个字段关联起来。 EXISTS取元组为真（true）的记录，NOT EXISTS取元组为假（false）的记录。 1、 NOT EXISTS子查询和主查询为不同的两个表 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1' ); 2、 NOT EXISTS子查询和主查询为同一个表 取别名自身关联：t1.id=t2.id
MySQL 8.0 优化 in ,exist,join 性能比较 及索引命中情况
dingwei92的博客
11-23 1249
环境 windows MySQL 8.0.21 建表语句 首先建表 t_user1 ，t_user2， t_class1， t_class2 -- t_user1 CREATE TABLE IF NOT EXISTS `t_user1` ( `id` INT UNSIGNED AUTO_INCREMENT, user_id INT NOT NULL, `name` VARCHAR ( 100 ) NOT NULL, `age` INT NOT NULL, PRIMARY KEY (.
mysql5.7的存储过程,MySQL5.7从入门到精通-------存储过程与存储函数（3）
weixin_39958138的博客
03-17 301
10.2.1 调用存储过程存储过程必须使用CALL语句来调用。如果要调用其它数据库的存储过程，需要指定数据库名称。例如 CALL dbname.spnameDROP TABLE IF EXISTS t_student;CREATE TABLE t_student(id INT(11) PRIMARY KEY AUTO_INCREMENT,name VARCHAR(255) NOT NULL,ag...
mysql exits优化_mysql8 参考手册--使用EXISTS策略优化子查询
weixin_35891142的博客
01-19 313
某些优化适用于使用IN(或=ANY)运算符测试子查询结果的比较。本节讨论这些优化，尤其是关于NULL价值所面临的挑战。讨论的最后部分提出了如何帮助优化器的建议。考虑以下子查询比较：outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)MySQL的评估查询“ 从外到内。” 即，首先获得外表达式的值 outer_expr，然后运行...
MYSQL中IN和EXISTS的区别
weixin_43355693的博客
01-31 982
介绍mysql中in和exists的执行原理，以及in和exists的区别。
mysql中的in和exists
10-18
好的，关于mysql中的in和exists，它们都是用于子查询的关键字，但是它们的使用方式和效率有所不同。 in关键字用于判断某个字段的值是否在一个集合中，集合可以是一个列表或者是一个子查询的结果集。例如： ``` ...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值