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

最新推荐文章于 2024-06-29 10:36:00 发布
最新推荐文章于 2024-06-29 10:36:00 发布
阅读量1.8k 收藏 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
荒野大码农
关注
专栏目录
MySQL8.0与MySQL5.7差异分析
Bertram的博客
12-09 1万+
前景 MySQL8.0与MySQL5.7有什么区别和坑？有必要升级MySQL8.0吗？MySQL 8.0是2018年4月20日发布的全球最受欢迎的开源数据库的一个非常令人兴奋的新版本，一些关键的增强包括：SQL窗口函数，公用表表达式，NOWAIT和SKIP LOCKED，降序索引，分组，正则表达式，字符集，成本模型和直方图。JSON扩展语法，新功能，改进排序和部分更新。 那么我们有必要及时升级到mysql8.0吗？我们先说说Mysql8.0的新特点： Mysql8.0的新特点 使用JSON表函数，您可以使用
2022-09-07 mysql8-对IN操作符-优化为EXISTS
悟世之路
09-07 683
mysql8对于IN操作符, 存在使用EXISTS进行优化的策略。本文分析mysql8是如何实现此优化的。
MySQL 8.0 优化 in ,exist,join 性能比较 及索引命中情况
dingwei92的博客
11-23 1282
环境 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 EXISTS的用法
s1162276945的博客
05-25 505
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
Mysql5.7 in,exists,表直接关联性能比较
关注系统性能调优
06-19 2301
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 5.7 与 MySQL 8.0：关键差异与升级考量
My_wife_QBL的博客
06-29 2660
MySQL作为全球最受欢迎的开源关系型数据库管理系统之一，其每个新版本的发布都带来了性能、功能和安全性的显著提升。MySQL 5.7和MySQL 8.0是两个重要的里程碑版本，它们在多个方面都有显著的差异。本文将深入探讨这两个版本之间的关键差异，并提供升级到MySQL 8.0的考量因素，以帮助数据库管理员和开发人员做出明智的决策。
MySQL 8.0 官方文档 第八章 优化（二十六）—— 使用EXISTS策略优化子查询
HZ_Samuel博客
05-21 550
目录第八章 优化（二十六）—— 使用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带来的挑战。讨论的最后一
Mysql5.7及8.0版本索引失效情况汇总
01-19
没有特殊说明，测试环境均为MySQL8.0，早期版本可能会有更多情况导致索引失效。8.0失效的情况，早期版本也失效；8.0不失效的情况，早期版本可能失效。 所有测试默认不考虑表为空的情况，特殊情况文中会有说明。 本文...
记录mysql5.7升级mysql8.0遇到的问题及解决（一）
smile的专栏
01-02 1924
一、备份数据库及卸载mysql5.7 因运行2年的项目所在服务器被扫描mysql病毒，建议升级mysql新的版本，所以开始为升级mysql8.0做准备，备份项目数据及卸载mysql5.0 欢迎使用Markdown编辑器 你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章，了解一下Markdown的基本语法知识。 新的改变 我们对Markdown编辑器进行了一些功能拓展与语法支持，除了标准的Markdown编辑器功能，我们增加了
Linux centOS源码安装mysql 5.7和安装8.0填过的坑
make_a_difference
07-27 1056
环境说明：vm14.1.3， centOS6.10 mysql:5.7.22 mysql8.0.17的那些坑：本来想安装mysql8.0版本，尝试了一半就放弃了，是因为安装过程中各种依赖文件版本问题如：cmake必须是3.5.1以上、gcc必须5.0以上的版本安装所以各种需要新版本的依赖然后就放弃了，如果继续往下装需要安装多少依赖的新版本不知。。。实在是没有时间 经验经验经验:大家...
MySQL 8.0与5.7的json性能对比
热门推荐
wzj_whut的专栏
03-12 1万+
文章目录前言安装8.08.0与5.7的启动参数差异准备测试环境mysql 8.0禁用binlogmysql 5.7配置准备数据8.0版本的测试结果5.7版本的测试结果 前言 MySQL 8.0最重要的特性是对NoSQL的支持更加完善, 官网上用太极图来表达了NoSQL比重, 官方号称可以干掉其它的NoSQL数据库了 8.0 使用json-partial-updates特性来提高json的更新效率...
MySQL EXISTS 关键字使用
欧阳啸天的专栏
08-15 3652
MySQL exists 关键字使用简单介绍
mysql中exists的用法详解
zhudongyanga的博客
06-02 1万+
👉 有了上面的执行原理的说明，我们明白了一个道理：内层循环次数的多少不会影响到外层的次数，但是外层循环的次数直接会影响到内层循环的次数，外层循环每多一次，内层循环就需要多完整的一次循环，所以我们优化的目标其实就是使外层的循环次数尽量少，总结来说：小表驱动大表。
MySQL 8.0中对EXISTS、NOT EXISTS的持续优化
老叶茶馆
04-19 3416
继续给MySQL 8.0吹吹水MySQL在8.0.16版本之前，对 IN 和 EXISTS 处理是不一样的，EXISTS只能采用子查询方式，所以执行计划中能看到DEPENDENT SUB...
MySQL5.7和MySQL8的区别
weixin_44260806的博客
06-20 6444
MySQL8新特性
mysql exits优化_mysql8 参考手册--使用EXISTS策略优化子查询
weixin_35891142的博客
01-19 338
某些优化适用于使用IN(或=ANY)运算符测试子查询结果的比较。本节讨论这些优化，尤其是关于NULL价值所面临的挑战。讨论的最后部分提出了如何帮助优化器的建议。考虑以下子查询比较：outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)MySQL的评估查询“ 从外到内。” 即，首先获得外表达式的值 outer_expr，然后运行...
mysql 5.7 多条件 in_MySQL5.7的参数优化
weixin_35459464的博客
01-27 432
cat /etc/my.cnf[client]port = 3306socket = /var/lib/mysql/mysql.sock[mysqld]port = 33...
mysql 5.7 多条件 in_mysql5.7在多列索引 in條件查詢的優化
weixin_42516048的博客
02-17 179
山谷中偶遇，分享出來。某日(ri)，zabbix mysql慢日志監控發現很多陌生的慢查詢。大概如下：SELECT * FROM `entry_device` WHERE (`entry_no`, `device_udid`) IN (('464580908742086656', 'ffffffffdeaea5fcffffffff96fddfcf')), ('464109860674342912'...
mysql table exists_mysql8 参考手册-table_exists()过程
weixin_34352565的博客
01-18 604
测试给定表是否以常规表， TEMPORARY表或视图的形式存在。该过程在OUT参数中返回表类型。如果同时存在具有给定名称的临时表和永久表，TEMPORARY则返回。参量in_db VARCHAR(64)：要在其中检查表是否存在的数据库的名称。in_table VARCHAR(64)：要检查其存在的表的名称。out_exists ENUM('', 'BASE TABLE', 'VIEW', 'TEM...
mysql中not in 和not exists区别
最新发布
10-28
MySQL中`NOT IN`和`NOT EXISTS`这两个关键字用于从结果集中排除特定的值或行，它们的区别在于查询的方式： 1. `NOT IN`：当你想排除一组具体的值时，可以使用`NOT IN`。它会返回所有不在指定列表中的记录。例如： ``` SELECT * FROM table_name WHERE column_name NOT IN (value1, value2, ...); ``` 这将返回`column_name`字段中不包含`value1`, `value2`等任何提供的值的所有记录。 2. `NOT EXISTS`：这个关键词则用于检查是否存在某个条件下的行。它基于的是子查询的结果，如果子查询中的某行不存在于主表中，则满足`NOT EXISTS`。例如： ``` SELECT * FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.id = table2.id); ``` 这将返回table1中，没有对应id在table2中存在的所有记录。 总结一下： - `NOT IN`适合排除一组固定的、已知的值； - `NOT EXISTS`更适用于查找某个条件不存在的情况，比如外键关联不存在的记录。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值