当前位置:首页 » 编程语言 » sqlin和的效率

sqlin和的效率

发布时间: 2024-12-24 12:33:07

① 在sql语句多表连接中,in、exists、join哪个效率更高一点

EXISTS、IN与JOIN,都可以用来实现形如“查询A表中在(或不在)B表中的记录”的查询逻辑。x0dx0ax0dx0a在查询的两个表大小相当的情况下,3种查询方式的执行时间通常是:x0dx0aEXISTS <= IN <= JOINx0dx0aNOT EXISTS <= NOT IN <= LEFT JOINx0dx0a只有当表中字段允许NULL时,NOT IN的方式最慢:x0dx0aNOT EXISTS <= LEFT JOIN <= NOT INx0dx0ax0dx0a但是如果两个表中一个较小,一个较大,则子查询表大的用exists,子查询表小的用in,因为in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。而无论那个表大,用not exists都比not in要快。这是因为如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。x0dx0ax0dx0aIN的好处是逻辑直观简单(通常是独立子查询);缺点是只能判断单字段,并且当NOT IN时效率较低,而且NULL会导致不想要的结果。x0dx0aEXISTS的好处是效率高,可以判断单字段和组合字段,并不受NULL的影响;缺点是逻辑稍微复杂(通常是相关子查询)。x0dx0aJOIN用在这种场合,往往是吃力不讨好。JOIN的用途是联接两个表,而不是判断一个表的记录是否在另一个表。

② SQL优化——IN和EXISTS谁的效率更高

在SQL编程中,IN和EXISTS是常被使用的子查询方式,它们的功能相似但效率存在差异。通过对比不同场景下的测试,可以发现使用哪一种更为高效。

在测试1中,我们使用了子查询`select flag from B where B_id<100`,结果集为99条。在这个场景下,IN的效率高于EXISTS。IN执行计划中,首先执行A表的查询,并利用索引优化,然后执行B表子查询,同样使用索引。而EXISTS的执行计划则先取A表所有记录,然后逐行关联B表子查询,使用Block Nested Loop优化。

测试2中,子查询`select flag from B where B_id>100`,结果集为299899条。这次EXISTS的效率比IN高。两者在索引使用上与第一次测试一致,但当子查询结果集较大而外部表较小时,EXISTS的Block Nested Loop开始发挥优势,查询效率优于IN。

综上所述,并不能断言IN或EXISTS的效率更高,具体情况需要具体分析。IN的执行原理是先查询内表得到结果集,再与外表匹配,不管子查询是否有数据,都会进行全部匹配,这在内表数据量较大时效率较低。而EXISTS则是先对外表进行循环查询,只在匹配到数据时返回结果,适用于子查询数据量较大而外表数据量较小时。

结论是,子查询结果集较大时使用EXISTS,而结果集较小时使用IN可能更有效。对于更详细的信息和深入理解,可以参考墨天轮原文链接。

③ sql语句中条件查询里in、like、及=三个的效率怎么样

1、如果条件字段都是非索引字段,那么效率都差不多,就看结果大小。
2、有差别的在于条件字段是索引字段时:
=在所以的情况下都会进行索引扫描,所以效率总是高的。
like 当模糊查询为右模糊,比如'abc%'时,扫描索引,高效。
当模糊查询含左模糊时,比如'%abc',进行全表扫描,低效。
in的作用等同于or ,也是进行索引扫描,高效。

另外,in还可以连接查询结果集,这时往往会和exists做比较。
a、 select * from t1 where f1 in (select f1 from t2 where t2.fx='x'),

其中子查询的where里的条件不受外层查询的影响,这类查询一般情况下,自动优化会转成exist语句,也就是效率和exist一样。

b、 select * from t1 where f1 in (select f1 from t2 where t2.fx=t1.fx),

其中子查询的where里的条件受外层查询的影响,这类查询的效率要看相关条件涉及的字段的索引情况和数据量多少,一般效率不如exists,数据量大时,效果就更加明显。

④ sql exist和in的区别及查询效率比较

SQL查询中in和exists的区别分析

select * from A where id in (select id from B);

select * from A where exists (select 1 from B where A.id=B.id);

对于以上两种情况,in是在内存里遍历比较,而exists需要查询数据库,所以当B表数据量较大时,exists效率优于in。

1、IN()语句内部工作原理

IN()只执行一次,它查出B表中的所有id字段并缓存起来。之后,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录。
它的查询过程类似于以下过程:

List resultSet={};

Array A=(select * from A);

Array B=(select id from B);for(int i=0;i<A.length;i++)

{

for(int j=0;j<B.length;j++) {

if(A[i].id==B[j].id) {
resultSet.add(A[i]); break;
}
}
}return resultSet;

可以看出,当B表数据较大时不适合使用in(),因为它会B表数据全部遍历一次

  • 例1:A表有10000条记录,B表有1000000条记录,那么最多有可能遍历10000*1000000次,效率很差。

  • 例2:A表有10000条记录,B表有100条记录,那么最多有可能遍历10000*100次,遍历次数大大减少,效率大大提升。

  • 结论:IN()适合B表比A表数据小的情况

    2、EXISTS()语句内部工作原理

    exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是其内查询语句的结果集空或者非空,空则返回false,非空则返回true。
    它的查询过程类似于以下过程:

  • List resultSet={};

  • Array A=(select * from A);

  • for(int i=0;i<A.length;i++)

  • { if(exists(A[i].id) { //执行select 1 from B where B.id=A.id是否有记录返回


  • resultSet.add(A[i]);

  • }

  • }return resultSet;

  • 当B表比A表数据大时适合使用exists(),因为它没有那么多遍历操作,只需要再执行一次查询就行。

  • 例1:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。

  • 例2:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果。

  • 例3:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快。

  • 结论:EXISTS()适合B表比A表数据大的情况

    3、使用情况分析

    当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用。

    在插入记录前,需要检查这条记录是否已经存在,只有当记录不存在时才执行插入操作,可以通过使用 EXISTS 条件句防止插入重复记录。

  • insert into A (name,age) select name,age from B

  • where not exists (select 1 from A where A.id=B.id);


  • EXISTS与IN的使用效率的问题,通常情况下采用exists要比in效率高,因为IN不走索引。但要看实际情况具体使用:

  • IN适合于外表大而内表小的情况;

  • EXISTS适合于外表小而内表大的情况。

  • 4、关于EXISTS:

    EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False。

    EXISTS 指定一个子查询,检测行的存在。

    语法: EXISTS subquery

    参数: subquery 是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。

    结果类型: Boolean 如果子查询包含行,则返回 TRUE ,否则返回 FLASE 。

    结论:

  • select * from A where exists (select 1 from B where A.id=B.id)

  • EXISTS(包括 NOT EXISTS )子句的返回值是一个boolean值。 EXISTS内部有一个子查询语句(SELECT ... FROM...),我将其称为EXIST的内查询语句。其内查询语句返回一个结果集, EXISTS子句根据其内查询语句的结果集空或者非空,返回一个布尔值。

    一种通俗的可以理解为:将外查询表的每一行,代入内查询作为检验,如果内查询返回的结果取非空值,则EXISTS子句返回TRUE,这一行行可作为外查询的结果行,否则不能作为结果。

    分析器会先看语句的第一个词,当它发现第一个词是SELECT关键字的时候,它会跳到FROM关键字,然后通过FROM关键字找到表名并把表装入内存。接着是找WHERE关键字,如果找不到则返回到SELECT找字段解析,如果找到WHERE,则分析其中的条件,完成后再回到SELECT分析字段。最后形成一张我们要的虚表。

    WHERE关键字后面的是条件表达式。条件表达式计算完成后,会有一个返回值,即非0或0,非0即为真(true),0即为假(false)。同理WHERE后面的条件也有一个返回值,真或假,来确定接下来执不执行SELECT。

    分析器先找到关键字SELECT,然后跳到FROM关键字将STUDENT表导入内存,并通过指针找到第一条记录,接着找到WHERE关键字计算它的条件表达式,如果为真那么把这条记录装到一个虚表当中,指针再指向下一条记录。如果为假那么指针直接指向下一条记录,而不进行其它操作。一直检索完整个表,并把检索

    java">

    出来的虚拟表返回给用户。EXISTS是条件表达式的一部分,它也有一个返回值(true或false)。



    作者:IronM
    链接:https://www.jianshu.com/p/f212527d76ff
    来源:简书
    着作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

热点内容
交换机服务器路由器电脑怎么连接 发布:2024-12-25 03:10:42 浏览:504
javaweb实现文件上传 发布:2024-12-25 03:10:41 浏览:349
秀哥php 发布:2024-12-25 02:53:52 浏览:775
centos安装phpapache 发布:2024-12-25 02:49:41 浏览:758
随机优化算法 发布:2024-12-25 02:38:09 浏览:278
phpnullempty 发布:2024-12-25 02:30:39 浏览:591
手机版高级语言编译器 发布:2024-12-25 02:12:52 浏览:38
苹果和安卓打cf手游哪个好 发布:2024-12-25 02:12:52 浏览:662
喵咪斗恶龙安卓哪里下载 发布:2024-12-25 02:07:58 浏览:515
pythonfuture 发布:2024-12-25 01:46:47 浏览:587