MySQL中in与exists的使用及区别介绍

先放一段代码

for(int i=0;i<1000;i++){
 for(int j=0;j<5;j++){
 System.out.println("hello");
 }
}

 for(int i=0;i<5;i++){
 for(int j=0;j<1000;j++){
 System.out.println("hello");
 }
}

分析以上代码可以看到两行代码除了循环的次序不一致意外,其他并无区别,在实际执行时两者所消耗的时间和空间应该也是一致的。但是这仅仅是在Java中,现在我们转化一下情景,最外层循环是数据库中的连接操作,内层循环为查找操作,那么现在两次的结果将相差巨大。

之所以出现这样的原因是数据库的特点决定的,数据库中相比较于查询操作而言,建立连接是更消耗资源的。第一段代码建立了1000次连接,每一次连接却只做了5次查询,显然是很浪费的。

因此在我们对数据库进行操作时需要遵循的操作应当是小表驱动大表(小的数据集驱动大的数据集)。

in与exists

表结构

tbl_emp为员工表,deptld为部门id。tbl_dept为部门表。员工表中含有客人,其deptld字段为-1

mysql> desc tbl_emp;
+--------+-------------+------+-----+---------+----------------+
| Field | Type  | Null | Key | Default | Extra   |
+--------+-------------+------+-----+---------+----------------+
| id  | int(11)  | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES |  | NULL |    |
| deptld | int(11)  | YES | MUL | NULL |    |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc tbl_dept;
+----------+-------------+------+-----+---------+----------------+
| Field | Type  | Null | Key | Default | Extra   |
+----------+-------------+------+-----+---------+----------------+
| id  | int(11)  | NO | PRI | NULL | auto_increment |
| deptName | varchar(30) | YES | MUL | NULL |    |
| locAdd | varchar(40) | YES |  | NULL |    |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

我们知道对于一个公司而言相对于部门来说员工数要多得多。现在我们有这样一个需求:查询属于这个公司的员工(不含来访客人),我们可以用以下代码解决

利用in

# 先查询部门表中所有的id,然后拿员工表中的deptld字段与之对比,若含有则保留。

mysql> select * from tbl_emp a where a.deptld in (select id from tbl_dept);

in关键字就像or的拼接,例如上述sql雨中子查询查出的结果为1,2,3。则sql语句等价于以下形式

mysql> select * from tbl_emp a where a.deptld=1 or a.deptld=2 or a.deptld=3

总的来说in关键字就是将子查询中的所有结果都查出来,假设结果集为B,共有m条记录,然后在将子查询条件的结果集分解成m个,再进行m次查询。可以看到这里主要是用到了A的索引,B表如何对查询影响不大

利用exists

mysql> select * from tbl_emp a where exists (select 1 from tbl_dept b where a.deptld = b.id );

exits:将主查询的数据放到子查询中做条件验证,根据验证结果(True或False)来判断是否保留主查询中的记录。

for (i = 0; i < count(A); i++) { //遍历A的总记录数
  a = get_record(A, i); //从A表逐条获取记录
  if (B.id = a[id]) //如果子条件成立
    result[] = a;
}
return result;

可以看到:exists主要是用到了B表的索引,A表如何对查询的效率影响不大

结论

mysql> select * from tbl_emp a where a.deptld in (select id from tbl_dept);

若tbl_dept的记录数少余tbl_emp则使用in效率比较高

mysql> select * from tbl_emp a where exists (select 1 from tbl_dept b where a.deptld = b.id );

若tbl_dept的记录数多余tbl_emp则使用in效率比较高

下面给大家介绍IN与EXISTS的区别

1、IN查询分析

SELECT * FROM A WHERE id IN (SELECT id FROM B);

等价于:1、SELECT id FROM B ----->先执行in中的查询

2、SELECT *  FROM A  WHERE A.id = B.id

以上in()中的查询只执行一次,它查询出B中的所有的id并缓存起来,然后检查A表中查询出的id在缓存中是否存在,如果存在则将A的查询数据加入到结果集中,直到遍历完A表中所有的结果集为止。

以下用遍历结果集的方式来分析IN查询

通过以上程序可以看出,当B表的数据较大时不适合使用in()查询,因为它会将B表中的数据全部遍历一次

例如:

1、A表中有100条记录,B表中有1000条记录,那么最多可能遍历100*1000次,效率很差

2、A表中有1000条记录,B表中有100条记录,那么最多可遍历1000*100此,内循环次数减少,效率大大提升

结论:IN()查询适合B表数据比A表数据小的情况,IN()查询是从缓存中取数据

2、EXISTS查询分析

语法:SELECT  字段 FROM  table WHERE EXISTS(subquery);

SELECT * FROM a WHERE EXISTS(SELECT 1 FROM b WHERE B.id = A.id);

以上查询等价于:

SELECT * FROM A;
SELECT I FROM B WHERE B.id = A.id;

EXISTS()查询会执行SELECT * FROM A查询,执行A.length次,并不会将EXISTS()查询结果结果进行缓存,因为EXISTS()查询返回一个布尔值true或flase,它只在乎EXISTS()的查询中是否有记录,与具体的结果集无关。

EXISTS()查询是将主查询的结果集放到子查询中做验证,根据验证结果是true或false来决定主查询数据结果是否得以保存。

总结

以上所述是小编给大家介绍的MySQL中in与exists的使用及区别介绍,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对我们网站的支持!

(0)

相关推荐

  • mysql中插入emoji表情失败的原因与解决

    失败场景 用户昵称中存在emoji表情,调用jdbc往mysql数据库插入的时候抛出异常 java.sql.SQLException: Incorrect string value: '\xF0\x9F\x90\x9B' 失败原因 mysql的utf8编码的一个字符最多3个字节,但是一个emoji表情为4个字节,所以utf8不支持存储emoji表情.但是utf8的超集utf8mb4一个字符最多能有4字节,所以能支持emoji表情的存储. 下面话不多说了,来一起看看详细的介绍吧 解决方案 一. 修

  • mysql8重置root用户密码的完整步骤

    前言 最近很多新入职的同事问这个问题,特别是通过 homebrew 自动安装的 mysql ,其版本默认已经是 8.0 了,由于增加了一些安全策略等限制,所以修改用户密码会稍微麻烦些,索性就把这个总结贴一下吧. 下面话不多说了,来一起看看详细的介绍吧 步骤如下: 1.首先查看 root 用户相关信息,在 mysql 数据库的 user 表中: select host, user, authentication_string, plugin from user; host:允许用户登录的ip'位置

  • mysql自增id超大问题的排查与解决

    引言 小A正在balabala写代码呢,DBA小B突然发来了一条消息,"快看看你的用户特定信息表T,里面的主键,也就是自增id,都到16亿了,这才多久,在这样下去过不了多久主键就要超出范围了,插入就会失败,balabala......" 我记得没有这么多,最多1k多万,count了下,果然是1100万.原来运维是通过auto_increment那个值看的,就是说,表中有大量的删除插入操作,但是我大部分情况都是更新的,怎么会这样? 下面话不多说了,来一起看看详细的介绍吧 问题排查 这张表

  • Django中更改默认数据库为mysql的方法示例

    Django中默认使用sqlite3数据库,今天研究了下如何将它换成常见的mysql数据库. 由于项目用得python3,而MySQLdb没有支持python3的版本,如果使用python3.x版本时,pip install MySQLdb时会报错. 后来通过谷歌发现可以使用pymysql替代MySQLdb 1 在项目根目录下的__init__.py文件中加入如下代码: import pymysql pymysql.install_as_MySQLdb() 2 使用mysqlclient代替My

  • MySQL 8.0.12的安装与卸载教程详解

    一,MySQL8.0.12版本安装步骤. 1,下载 https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.12-winx64.zip 2,解压 将mysql-8.0.12-winx64.zip解压到 D:/mysql 目录下 3打开cmd命令控制台,输入以下命令. d: cd\ cd mysql cd bin d:\mysql\bin>mysqld --initialize-insecure                      初始

  • MYSQL8.0.13免安装版配置教程实例详解

    一.下载,本人以8.0为例 下载地址:https://dev.mysql.com/downloads/mysql/ 二.解压到某个目录,例如:D:/mysql/mysql-8.0.13-winx64 三.配置环境变量 1.新建一个变量:MYSQL_HOME 变量值:D:/mysql/mysql-8.0.13-winx64 2.修改path变量 添加一条记录:%MYSQL_HOME%/bin 四.在D:/mysql/mysql-8.0.13-winx64目录下创建my.ini文件 [mysqld]

  • windows 环境下 MySQL 8.0.13 免安装版配置教程

    目录 下载压缩包 地址 https://dev.mysql.com/downloads/mysql/ 解压zip文件,创建 my.ini 文件 解压后目录: basedir=D:\mysql-8.0.13-winx64 数据目录: datadir=D:\mysql-8.0.13-winx64\data 创建 my.ini 文件,文件内容如下 [mysql] # 设置mysql客户端默认字符集 default-character-set=utf8 [mysqld] # 设置3306端口 port=

  • MySQL用户账户管理和权限管理深入讲解

    前言 MySQL 的权限表在数据库启动的时候就载入内存,当用户通过身份认证后,就在内存中进行相应权限的存取,这样,此用户就可以在数据库中做权限范围内的各种操作了. 下面话不多说了,来一起看看详细的介绍吧 mysql 的权限体系大致分为5个层级: 全局层级 全局权限适用于一个给定服务器中的所有数据库.这些权限存储在mysql.user表中.GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限. 数据库层级 数据库权限适用于一个给定数据库中的所有目标.这些权限存储

  • MySQL 5.7安装好后打开命令行窗口闪退的解决方法

    前言 最近在安装好MySQL 5.7发现了一个问题,打开命令行窗口会闪退,后来发现原来是这个问题 若是因为在mysql安装目录下找不到my.ini文件,请接着往下看 解决方法 返回桌面,在开始出找出--MySQL 5.7 cammand line client并右键打开文件位置 属性-查看"目标"中的完整路径,我这里是"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" "--defaults-fi

  • Mysql查询正在执行的事务以及等待锁的操作方式

    使用navicat测试学习: 首先使用set autocommit = 0;(取消自动提交,则当执行语句commit或者rollback执行提交事务或者回滚) 在打开一个执行update 查询 正在执行的事务: SELECT * FROM information_schema.INNODB_TRX 根据这个事务的线程ID(trx_mysql_thread_id): 从上图看出对应的mysql 线程:一个94362 (第二个正在等待锁)另一个是93847(第一个update 正在执行 没有提交事务

随机推荐