MySQL系列-优化之精准解读in和exists

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL系列-优化之精准解读in和exists 1.解读in和exists 这两个关键字的区别主要是在于子查询上面,in是独立子查询,exists是相关子查询,例如: 用in查询有员工的部门       :select dept_name from dept where id in (select dept_id from emp); 用exists查询有员工的部门:select dept_name from dept where exists (select 1 from emp where dept.id=emp.dept_id); 当然,执行结果完全一致。

MySQL系列-优化之精准解读in和exists

1.解读in和exists

这两个关键字的区别主要是在于子查询上面,in是独立子查询,exists是相关子查询,例如:

用in查询有员工的部门       :select dept_name from dept where id in (select dept_id from emp);

用exists查询有员工的部门:select dept_name from dept where exists (select 1 from emp where dept.id=emp.dept_id);

当然,执行结果完全一致。

2.in和exists的效率问题

上面的SQL语句执行的完全结果一样,那么这两个的效率如何呢?

网上也是有很多文章进行解读,总的来说就是体现一种小数据集驱动大数据集的思想。很多文章是直接说小表驱动大表,其实这样是很不准确的,因为我们可以这样【select dept_name from dept where id in (select dept_id from emp where id>5;)】这样对于子查询来说他返回的结果集与他的表没太大关系了,所以小数据集驱动大的数据集是一种更精准的说法。参考【知乎 MySQL查询语句中的IN 和Exists 对比分析

下面分析它的执行原理(关于版本5.5和5.6的区别在文章后面会说):

对于in来说,他是先执行子查询然后得到子查询的结果集,再用子查询的结果去匹配外部表。这样的话需要遍历一边刚刚的结果集,如果外部表的相应字段建立了索引的话,在匹配外部表的时候就能使用上外部表的索引了。假设子查询结果大小为M,外部表的大小为N,外部表使用B+Tree索引匹配每一条数据的时间复杂度是O(log N),那么这个总的时间复杂度就相当于O(M*log N)。

对于exists来说,他是执行外表的遍历操作(不一定是全表扫描也可能是索引扫描,但是差别不是很大),然后里面的相关子查询会利用外部表的数据对内部表进行匹配,这个时候如果内部表的相关字段建立了索引的话,匹配的时候就能走索引了。同样假设子查询结果大小为M,外部表的大小为N,内部表使用B+Tree索引匹配每一条数据的时间复杂度是O(log M),那么这个总的时间复杂度就相当于O(N*log M)。

很明显:当外表大,内部数据集小,适合适使用in。当外表小,内部数据集大,适合使用exists。

3.实践检验(mysql5.5)

mysql是一代版本一代神,很多人写博客的时候都不标注出系统环境,这样可能会造成不必要的困扰。

准备数据表如下:

可以看出在emp表的dept_id上有索引。两个表的主键都是id。

首先检验exists:执行 explain select dept_name from dept where exists (select 1 from emp where dept.id=emp.dept_id);

结果和我们想像的一样,dept表的访问类型是全表扫描,emp表的访问类型是ref。

接下来检验in:执行 explain select dept_name from dept where id in (select dept_id from emp);

咦?不对啊,没有用上外部表的主键索引,外部表的访问类型是全表扫描啊!

这是因为:mysql5.5会把in的独立子查询语句转化为exists相关子查询语句,所以才出现了上面的结果。参考【搜狐科技

在mysql5.6中则不会了,在mysql5.6中的表现和我们期待的一致,具体可以参考【深入理解MySql子查询IN的执行和优化】。

由于我的本机和服务器使用的都是5.5版本,已经是非常老的一个版本了,所以在考虑是否换成5.6或者5.7,搞不好直接换8.0了,哈哈。

4.not in和not exists

这个就比较明朗了,对于not exists来说,和exists一样会利用内部表建立好的索引,唯一的区别在于当有数据匹配的时候exists返回true而not exists返回false。

实践如下:执行SQL,explain select emp_name from emp where not exists (select 1 from dept where dept.id=dept_id);

和预期一致,外表全表扫描,内表使用上了索引。

对于not in来说,哪个表的索引的用不上,除非覆盖索引的时候用一下。

外表外表扫描,内表的查询覆盖索引,因为extra字段出现了using index,但是效率比使用索引进行查找低多了。

所以对于 not exists 和 not in 来说毫不犹豫的使用 not exists 。

5.join替代

确实,对于只查询的in和exists来说,可以用join来替代。而且用join的话我们可以更好的控制使用索引的情况,对于非外连接来说,join可以自动判断表的大小,从而使小的数据集驱动大的数据集。关于join我会在另一篇博客当中进行详细讲解。

原文地址https://blog.csdn.net/UFO___/article/details/81221983

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
7天前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
58 9
|
1月前
|
SQL 关系型数据库 MySQL
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
MySQL慢查询优化、索引优化,是必知必备,大厂面试高频,本文深入详解,建议收藏。关注【mikechen的互联网架构】,10年+BAT架构经验分享。
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
|
11天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
52 18
|
10天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
18 7
|
9天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化与慢查询优化:原理与实践
通过本文的介绍,希望您能够深入理解MySQL索引优化与慢查询优化的原理和实践方法,并在实际项目中灵活运用这些技术,提升数据库的整体性能。
38 5
|
1月前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案,包括索引优化、SQL慢查询优化和数据库表优化,帮助提升数据库性能。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
MySQL慢查询优化、索引优化、以及表等优化详解
|
29天前
|
关系型数据库 MySQL Java
MySQL索引优化与Java应用实践
【11月更文挑战第25天】在大数据量和高并发的业务场景下,MySQL数据库的索引优化是提升查询性能的关键。本文将深入探讨MySQL索引的多种类型、优化策略及其在Java应用中的实践,通过历史背景、业务场景、底层原理的介绍,并结合Java示例代码,帮助Java架构师更好地理解并应用这些技术。
30 2
|
1月前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
88 3
|
2月前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时,传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案,通过将数据库操作与高速缓存相结合,可以显著提升数据处理的性能。本文将分享一次实际的优化案例,探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
111 9
|
1月前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
114 1