- 浏览: 851692 次
文章分类
- 全部博客 (365)
- java (124)
- spring mvc (21)
- spring (22)
- struts2 (6)
- jquery (27)
- javascript (24)
- mybatis/ibatis (8)
- hibernate (7)
- compass (11)
- lucene (26)
- flex (0)
- actionscript (0)
- webservice (8)
- rabbitMQ/Socket (15)
- jsp/freemaker (5)
- 数据库 (27)
- 应用服务器 (21)
- Hadoop (1)
- PowerDesigner (3)
- EJB (0)
- JPA (0)
- PHP (2)
- C# (0)
- .NET (0)
- html (2)
- xml (5)
- android (7)
- flume (1)
- zookeeper (0)
- 证书加密 (2)
- maven (1)
- redis (2)
- cas (11)
最新评论
-
zuxianghuang:
通过pom上传报错 Artifact upload faile ...
nexus上传了jar包.通过maven引用当前jar,不能取得jar的依赖 -
流年末年:
百度网盘的挂了吧???
SSO单点登录系列3:cas-server端配置认证方式实践(数据源+自定义java类认证) -
953434367:
UfgovDBUtil 是什么类
Java发HTTP POST请求(内容为xml格式) -
smilease:
帮大忙了,非常感谢
freemaker自动生成源代码 -
syd505:
十分感谢作者无私的分享,仔细阅读后很多地方得以解惑。
Nginx 反向代理、负载均衡、页面缓存、URL重写及读写分离详解
in和exists的讨论从未间断过。之前有“今年是龙年大哥”的有数据有真相的测试博文,现在有程序员老鸟写sql语句的经验之谈上的疯狂讨论。关于exists和in,就是很少人站出来,直白地分析二者本质上的差别,这方面的文章大都是用晦涩的文字表述,或者直接给结论——什么情况下用exists,什么情况下用in,而不给出原理。结果时至今日,还有许多人认为exists一定比in性能高。下面鄙人用JAVA的伪代码,从理论上分析exists和in的时间复杂度。
学生信息表(student_id 学生id, name 学生名称)
student(student_id,name)
学生总分表
score(student_id,total)
现在查询出总分(total)超过90分的学生信息。
一、粗略的时间复杂度估算
1 exists方式
select * from student a where exists (select 1 from score b where b.total>90 and b.student_id = a.student_id);
- List<Map<String,String>> studentList = select * from student ;
- for(i=0;i<studentList.size();i++){
- String _student_id = studentList.get(i).get("student_id");
- if(exists("select 1 from score where total>90 and student_id = " + _student_id )){//建立有索引,这执行很快,O(1)时间
- studentRow = studentList.get(i)
- println(studentList.get(i));
- }
- }
List<Map<String,String>> studentList = select * from student ; for(i=0;i<studentList.size();i++){ String _student_id = studentList.get(i).get("student_id"); if(exists("select 1 from score where total>90 and student_id = " + _student_id )){//建立有索引,这执行很快,O(1)时间 studentRow = studentList.get(i) println(studentList.get(i)); } }
时间复杂度为studentList.size() * 1
2 in方式
select * from student where student_id in (select student_id from score where total>90);
- List<Map<String,String>> scoreList = select student_id from score where total>90;
- for(i=0;i<scoreList.size();i++){
- String _student_id = scoreList.get(i).get("student_id ");
- String studentRow = select * from student where studentId=_student_id;//建立有索引,这执行很快O(1)时间
- if(null != studentRow {
- println(studentRow);
- }
- }
List<Map<String,String>> scoreList = select student_id from score where total>90; for(i=0;i<scoreList.size();i++){ String _student_id = scoreList.get(i).get("student_id "); String studentRow = select * from student where studentId=_student_id;//建立有索引,这执行很快O(1)时间 if(null != studentRow { println(studentRow); } }
时间复杂度为scoreList.size() * 1
根据时间复杂度,
exists的耗费的时间,与主表student的记录数成正比,student 表越大,exists耗费时间越长;
in耗费的时间,与子查询的记录数成正比,记录数越多,in耗费时间越长。
也就是说,理论上,注意是理论上,
如果子查询的结果集很大,即是scoreList.size()很大,可能就不适合用in。
如果主查询的表记录数很大,即使studentList.size()很大,而子查询的结果很小,可能就不适合用exists。
对比子查询结果集的大小scoreList.size()和主表student表的大小studentList.size(),相信大家能比较简单地对in和exists做出初步选择。
二、 细致的时间复杂度估算
上面的伪代码是粗略的估算。这里说细致一些。
1. 上面的两段伪代码中O(1)时间的部分,因为实际情况中未必使用到索引,所以未必为O(1)。
2. exists伪代码的第一句List<Map<String,String>> studentList = select * from student ;必然是全表扫描,算上这一句的,exists伪代码的时间复杂度就是,
studentList.size() * 1+studentTable.size() = 2*studentTable.size().
in伪代码的第一句,List<Map<String,String>> scoreList = select student_id from score where score>90;实际情况中,子查询未必是全表扫描。
如果是子查询是全表扫描,那么in的时间复杂度为
scoreList.size() * 1+scoreTable.size()
如果使用到索引,不是全表扫描,那么in的时间复杂度为
scoreList.size() *1 + scoreList.size() = 2*scoreList.size()
3. 综合1,2
exists:
studentTable.size() * Time(一条exists语句的执行时间)+studentTable.size()*Time(顺序扫描出一条记录的时间)
注释:studentTable就是主表。
in(子查询索引扫描):
scoreList.size() *Time(一条select语句的执行时间) + scoreList.size()*Time(索引扫描出一条记录的时间)
注释: scoreList就是子查询的结果集。一条select 语句就是主表做in判断的select语句
select * from student where studentId=_student_id
in(子查询全表扫描):
scoreList.size() *Time(一条select语句的执行时间) + scoreTable.size()*Time(顺序扫描出一条记录的时间)
4 简化
现在简化对比in 和 exists的时间复杂度,二者的表达式有乘法和加法,我们只保留乘法。
Time_Exists = studentTable.size() * Time(一条exists语句的执行时间)
Time_IN = scoreList.size() *Time(一条select语句的执行时间)
通常情况下,我们不允许TABLE ACCESS FULL的出现,该加索引的地方加索引,因此进一步简化为
Time_Exists = studentTable.size(),即是主表大小
Time_IN = scoreList.size() ,即是子查询结果集大小
数据量大,在决定该使用exsits和in的时候,我们只需要根据主表记录数和子查询结果集记录数就可做出初步选择。主表记录数多,我们就该有限不考虑用exists;子查询结果集记录数多,我们就该优先不考虑用in。如果子查询结果多,主表记录数多,用哪个呢?那就看实际数据了,要测试具体的时间。
5 结论
显然,细致分析之后,我们不能很快就下结论孰快孰慢了,索引的情况增加了分析的步骤。特别地,如果in伪代码中每条语句都用到了索引,子查询结果集合很小,另一方面主查询表很大,那么我们可以马上确定用in了。觉得exists一定比in快的同学,现在需要思考下了。
三、结论
实际上,一切还是看具体的存储过程以及看测试结果。理论和实际总会有差距,数据量,索引,硬件,ORACLE版本等等都会对结果产生影响。我们要具体问题具体分析。首先,我们可以套用上面两段伪代码去做估算,某些情况下还是可以估算得出来的孰快孰慢。其次,如果数据量大的话,就必须看执行计划,进一步,如果可以的话,就直接执行sql语句查看耗费时间。有时候执行计划还真的对EXISTS,IN有区别对待,这时候估算的思想就要用上。
我建议大家不要去纠结in、exists究竟用谁好。数据量不大,in、exists根本无区别,数据量大的时候,你说能不去看看执行计划吗?
值得注意的是,据说oracle11g在CBO的情况下,ORACLE会根据数据,对IN,EXISTS做出最佳的选择,而不管你写SQL是IN或者EXISTS。细心想想这也是合理的,IN,EXISTS所表达出的要做的事情是一样的,数据库为什么要区分对待呢?性能的问题交给数据库自己判断好了,不要麻烦开发人员。这也是我建议大家不要纠结in和exists区别的一个原因。
四、后记
后面我看到了一篇文章,同样比较简单易明,应该描述得比我准确,摘录下来,与大家共享 :)
from:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:953229842074
发表评论
-
根据多年经验整理的《互联网MySQL开发规范》
2015-08-10 16:09 786写在前面:无规矩不成方圆。对于刚加入互联网的朋友们,肯定会接 ... -
Mysql一主多从和读写分离配置简记
2015-05-15 10:56 1417近期开发的系统中使用MySql作为数据库,由于数据涉及到Mo ... -
MySQL第二天早上第一次连接超时报错,解决方法
2015-02-03 15:36 719| 浏览:44 | 更新:2014- ... -
MYSQL 设置 FOR UPDATE 超时时间
2015-01-21 10:37 2607show global variables like &q ... -
如何设计动态(不定)字段的产品数据库表?--淘宝多产品属性字段设计方法
2014-07-17 13:46 1993项目组会议上讨论的关于不定字段数目的数据库表问 ... -
要想学好SQL语句必会的50题,也是考试常考的点
2014-05-28 10:46 607-- 一、创建教学系统的数据库,表,以及数据 ... -
关于MYSQL group by 分组按时间取最大值的实现方法!
2013-02-06 08:22 1173类如 有一个帖子的回复表,posts( id , tid , ... -
ORACLE之游标实战
2012-12-07 08:45 1192这是一个忙碌的季节,也是一个收获颇多的季节。在忙碌工作的同时, ... -
全国行政区域数据库(地市数据库)
2012-10-28 13:48 1084创建表格: -- Create table creat ... -
oracle 数据库字段按照拼音首字母排序
2012-10-27 13:00 15271、创建oracle存储过程(网上拿来的的) Sql ... -
ORACALE查询数据库按汉字拼音排序
2012-10-27 13:03 1313CREATE OR REPLACE FUNCTION PUB_ ... -
ORA-12519,TNS:no appropriate service handler found的问题
2012-10-18 15:47 7691Java代码 ORA-12519, TNS:n ... -
dbcp的基本配置
2012-10-18 15:41 10321. 引入dbcp (选择1.4)Java代码 <dep ... -
ORACLE-分区表分区字段的update操作
2012-10-16 09:44 10885默认情况下,oracle的分区表对于分区字段是不允许进行u ... -
oracle分区表的建立方法(包含已经存在的表要分区)
2012-10-13 10:22 1565Oracle提供了分区技术以支持VLDB(Very L ... -
查询Oracle表实际物理使用大小
2012-10-13 09:54 1110Oracle中有两种含义的表大小一种是分配给一个表的物理空 ... -
Oracle tablespace (表空间)的创建、删除、修改、扩展及检查等
2012-10-13 09:39 1471oracle 数据库表空间的作用 1.决定数据库实体的空 ... -
Oracle异常ORA-01502: 索引或这类索引的分区处于不可用状态
2012-10-13 09:25 8034原因: 出现这个问题,可能有人move过表,或者disab ... -
Oracle中分区表的使用
2012-10-13 08:53 739前提: 查询分区:Select *From use ... -
Oracle 分区表
2012-10-11 16:06 893--================== -- ...
相关推荐
在Oracle数据库中,"IN"和"EXISTS"都是用于查询某个集合的元素是否存在于另一个集合中的关键字。然而,它们在处理数据时的效率和适用场景有所不同,这主要取决于涉及的数据量以及表之间的关联。 首先,让我们来看看...
IN、EXISTS、NOT IN、NOT EXISTS 是 SQL 中四种常用的条件判断运算符,它们之间的区别主要体现在使用场景、执行效率和语法结构上。 IN IN 是一种条件判断运算符,用于判断某个值是否存在于一个列表中。其基本语法...
标题和描述均聚焦于SQL查询语句中"IN"与"EXISTS"的区别及执行效率问题,这是一个在数据库操作中非常关键的话题,尤其对于优化查询性能有着不可忽视的作用。下面,我们将深入探讨这两种语句的不同之处及其对执行效率...
在数据库查询语言中,`IN` 和 `EXISTS` 子句是两种常见的用于关联两个表的方法,它们各自有其独特的性能特点和适用场景。本文将深入解析Oracle中`IN`与`EXISTS`的性能差异,以及如何根据具体需求选择最合适的查询...
且看接下来的具体分析:in其实是将外表和内表进行hash join,exists是先对外表进行loop操作,然后每次loop后再对内表进行查询。 如果两张表大小差不多,那么exists和in的效率差不多。 例如: 一张大表为A,一张小表B...
Oracle 中 EXISTS 和 IN 的效率问题详解 EXISTS 和 IN 都是 Oracle 中的集合操作符,但它们在使用和执行效率上有所不同。本文将深入探讨 EXISTS 和 IN 的使用场景、执行机制和效率问题。 EXISTS 的使用场景和机制 ...
根据给定的信息,本文将详细解析`EXISTS`与`IN`的区别以及如何在SQL优化过程中将`IN`转换为`EXISTS`来提高查询性能。 ### SQL优化需求背景 在系统开发与维护的过程中,经常会出现SQL查询效率低下、响应时间过长等...
### "Exists"与"In"的效率问题详解 #### 引言 在数据库查询语言SQL中,“Exists”与“In”是两种常用的子查询方法,它们在实际应用中各有优势与局限。本文将深入探讨这两种方法的工作原理、应用场景以及性能差异,...
SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句.txt欢迎下载!
PostgreSQL作为一种强大的开源关系数据库系统,它支持多种SQL操作,其中包括IN、EXISTS、ANY/ALL和JOIN等操作符。这些操作符在不同的业务场景下有着不同的表现和性能影响。在实际的数据库操作中,选择合适的操作符是...
在SQL查询中,`CASE WHEN`、`EXISTS`、`NOT EXISTS`以及`IN`和`NOT IN`是常用的操作符,它们用于处理复杂的条件判断和数据筛选。这些概念对于理解和编写高效的SQL语句至关重要,尤其是在数据分析和数据库管理中。 `...
- 在某些情况下,可以考虑使用 `EXISTS` 或 `NOT EXISTS` 来替代 `IN` 和 `NOT IN`,以提高查询效率。 #### 三、左连接、右连接与全连接 除了上述几种查询方式之外,SQL 还提供了不同的连接类型来处理不同情况下...
in与exists之争(11g).sql
这是因为`IN`和`NOT IN`需要将主查询中的每一行都与子查询结果进行比较,而`EXISTS`和`NOT EXISTS`则只需判断子查询是否返回至少一行数据即可,无需获取所有数据行。 **性能对比:** - **IN 和 NOT IN**:需要将主...
- 效率:在某些情况下,`EXISTS` 和 `NOT EXISTS` 可能会提供相似的性能,但通常 `IN` 和 `NOT IN` 比较操作符在处理大量数据时更有效。然而,当涉及到子查询时,`EXISTS` 和 `NOT EXISTS` 可能更合适,因为它们只...
MySQL优化之in,exists,not in,not exists的区别in与existsin查询过程结论:exists查询过程:结论:not in与not existsnot in查询过程:结论:not exists查询过程:结论: 首先我们使用两个用户表作为实例 insert ...
在查询中,我们可以使用EXISTS和NOT EXISTS来代替IN和NOT IN。例如,我们要查询Sendorder表中的冗余数据(没有和reg_person或worksite相连的数据): ```sql select Sendorder.id, Sendorder.reads, Sendorder....
在Oracle数据库中,`IN`、`EXISTS`、`NOT IN` 和 `NOT EXISTS` 是四个常用的子查询操作符,它们在SQL查询语句中扮演着不同的角色,且各有其性能特点。以下是对这些操作符的详细分析和比较。 1. `IN` 操作符: `IN` ...