包含IN子查询的SQL语句的优化

最新推荐文章于 2024-03-19 14:35:04 发布
最新推荐文章于 2024-03-19 14:35:04 发布
阅读量1k 收藏
点赞数 1
分类专栏： 数据库

当SQL语句中包含in语句时,有时候会极大的影响性能,我们可以把in子查询用exists子查询或外连接替代

例子如下:

   1.SQL语句中包含IN子查询:

Sql代码   收藏代码
  1. SQL> select * from servers s  
  2.   2   where s.srvr_id = 3333333 or  
  3.   3   s.srvr_id in (select t.srvr_id  
  4.   4                         from serv_inst t  
  5.   5                        where t.si_status = 'Activated'  
  6.   6                          and t.type = 'UNIX')  
  7.   7  /  
  8.   
  9. 已选择11行。  
  10.   
  11.   
  12. 执行计划  
  13. ----------------------------------------------------------  
  14. Plan hash value: 910321333  
  15.   
  16. --------------------------------------------------------------------------------  
  17. | Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
  18. --------------------------------------------------------------------------------  
  19. |   0 | SELECT STATEMENT   |           |     6 |   378 |     2   (0)| 00:00:01 |  
  20. |*  1 |  FILTER            |           |       |       |            |          |  
  21. |   2 |   TABLE ACCESS FULL| SERVERS   |    96 |  6048 |     2   (0)| 00:00:01 |  
  22. |*  3 |   TABLE ACCESS FULL| SERV_INST |     2 |    38 |     2   (0)| 00:00:01 |  
  23. --------------------------------------------------------------------------------  
  24.   
  25. Predicate Information (identified by operation id):  
  26. ---------------------------------------------------  
  27.   
  28.    1 - filter("S"."SRVR_ID"=3333333 OR  EXISTS (SELECT /*+ */ 0 FROM  
  29.               "SERV_INST" "T" WHERE "T"."SRVR_ID"=:B1 AND "T"."SI_STATUS"='Activated'  
  30.               AND "T"."TYPE"='UNIX'))  
  31.    3 - filter("T"."SRVR_ID"=:B1 AND "T"."SI_STATUS"='Activated' AND  
  32.               "T"."TYPE"='UNIX')  
  33.   
  34. Note  
  35. -----  
  36.    - dynamic sampling used for this statement  
  37.   
  38.   
  39. 统计信息  
  40. ----------------------------------------------------------  
  41.           0  recursive calls  
  42.           0  db block gets  
  43.         818  consistent gets  
  44.           0  physical reads  
  45.           0  redo size  
  46.        1146  bytes sent via SQL*Net to client  
  47.         385  bytes received via SQL*Net from client  
  48.           2  SQL*Net roundtrips to/from client  
  49.           0  sorts (memory)  
  50.           0  sorts (disk)  
  51.          11  rows processed  
  52.   
  53. SQL>   

 2.把上面SQL语句改为EXISTS子查询形式

Sql代码   收藏代码
  1. SQL> select *  
  2.   2    from servers s  
  3.   3   where s.srvr_id = 3333333 or exists (select 1  
  4.   4                         from serv_inst t  
  5.   5                        where  s.srvr_id = t.srvr_id   
  6.   6                          and t.si_status = 'Activated'  
  7.   7                          and t.type = 'UNIX')  
  8.   8  /  
  9.    
  10. 已选择11行。  
  11.   
  12.   
  13. 执行计划  
  14. ----------------------------------------------------------  
  15. Plan hash value: 910321333  
  16.   
  17. --------------------------------------------------------------------------------  
  18. | Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
  19. --------------------------------------------------------------------------------  
  20. |   0 | SELECT STATEMENT   |           |     6 |   378 |     2   (0)| 00:00:01 |  
  21. |*  1 |  FILTER            |           |       |       |            |          |  
  22. |   2 |   TABLE ACCESS FULL| SERVERS   |    96 |  6048 |     2   (0)| 00:00:01 |  
  23. |*  3 |   TABLE ACCESS FULL| SERV_INST |     2 |    38 |     2   (0)| 00:00:01 |  
  24. --------------------------------------------------------------------------------  
  25.   
  26. Predicate Information (identified by operation id):  
  27. ---------------------------------------------------  
  28.   
  29.    1 - filter("S"."SRVR_ID"=3333333 OR  EXISTS (SELECT /*+ */ 0 FROM  
  30.               "SERV_INST" "T" WHERE "T"."SRVR_ID"=:B1 AND "T"."SI_STATUS"='Activated'  
  31.               AND "T"."TYPE"='UNIX'))  
  32.    3 - filter("T"."SRVR_ID"=:B1 AND "T"."SI_STATUS"='Activated' AND  
  33.               "T"."TYPE"='UNIX')  
  34.   
  35. Note  
  36. -----  
  37.    - dynamic sampling used for this statement  
  38.   
  39.   
  40. 统计信息  
  41. ----------------------------------------------------------  
  42.           0  recursive calls  
  43.           0  db block gets  
  44.         818  consistent gets  
  45.           0  physical reads  
  46.           0  redo size  
  47.        1146  bytes sent via SQL*Net to client  
  48.         385  bytes received via SQL*Net from client  
  49.           2  SQL*Net roundtrips to/from client  
  50.           0  sorts (memory)  
  51.           0  sorts (disk)  
  52.          11  rows processed  
  53.   
  54. SQL>   

3.改成另一种外连接的方式

Sql代码   收藏代码
  1. SQL> select distinct s.*  
  2.   2    from servers s  
  3.   3    left join serv_inst t on s.srvr_id = t.srvr_id  
  4.   4                         and t.si_status = 'Activated'  
  5.   5                         and t.type = 'UNIX'  
  6.   6   where ((t.siid is not null) or (t.siid is null and s.srvr_id = 3333333))  
  7.   7  /  
  8.   
  9. 已选择11行。  
  10.   
  11. 执行计划  
  12. ----------------------------------------------------------  
  13. Plan hash value: 2837582902  
  14.   
  15. ----------------------------------------------------------------------------------  
  16. | Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
  17. ----------------------------------------------------------------------------------  
  18. |   0 | SELECT STATEMENT     |           |    96 |  8448 |     7  (29)| 00:00:01 |  
  19. |   1 |  HASH UNIQUE         |           |    96 |  8448 |     7  (29)| 00:00:01 |  
  20. |*  2 |   FILTER             |           |       |       |            |          |  
  21. |*  3 |    HASH JOIN OUTER   |           |    96 |  8448 |     6  (17)| 00:00:01 |  
  22. |   4 |     TABLE ACCESS FULL| SERVERS   |    96 |  6048 |     2   (0)| 00:00:01 |  
  23. |*  5 |     TABLE ACCESS FULL| SERV_INST |    57 |  1425 |     3   (0)| 00:00:01 |  
  24. ----------------------------------------------------------------------------------  
  25.   
  26. Predicate Information (identified by operation id):  
  27. ---------------------------------------------------  
  28.   
  29.    2 - filter("T"."SIID" IS NOT NULL OR "T"."SIID" IS NULL AND  
  30.               "S"."SRVR_ID"=3333333)  
  31.    3 - access("S"."SRVR_ID"="T"."SRVR_ID"(+))  
  32.    5 - filter("T"."TYPE"(+)='UNIX' AND "T"."SI_STATUS"(+)='Activated')  
  33.   
  34. Note  
  35. -----  
  36.    - dynamic sampling used for this statement  
  37.   
  38.   
  39. 统计信息  
  40. ----------------------------------------------------------  
  41.           0  recursive calls  
  42.           0  db block gets  
  43.          12  consistent gets  
  44.           0  physical reads  
  45.           0  redo size  
  46.        1154  bytes sent via SQL*Net to client  
  47.         385  bytes received via SQL*Net from client  
  48.           2  SQL*Net roundtrips to/from client  
  49.           0  sorts (memory)  
  50.           0  sorts (disk)  
  51.          11  rows processed  
  52.   
  53. SQL>   

 4.另一种外连接的写法

Sql代码   收藏代码
  1. SQL> select distinct s.*  
  2.   2    from servers s, serv_inst t  
  3.   3   where s.srvr_id = t.srvr_id(+)  
  4.   4     and t.si_status(+) = 'Activated'  
  5.   5     and t.type(+) = 'UNIX'  
  6.   6     and ((t.siid is not null) or (t.siid is null and s.srvr_id = 3333333))  
  7.   7  /  
  8.   
  9. 已选择11行。  
  10.   
  11.   
  12. 执行计划  
  13. ----------------------------------------------------------  
  14. Plan hash value: 2837582902  
  15.   
  16. ----------------------------------------------------------------------------------  
  17. | Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
  18. ----------------------------------------------------------------------------------  
  19. |   0 | SELECT STATEMENT     |           |    96 |  8448 |     7  (29)| 00:00:01 |  
  20. |   1 |  HASH UNIQUE         |           |    96 |  8448 |     7  (29)| 00:00:01 |  
  21. |*  2 |   FILTER             |           |       |       |            |          |  
  22. |*  3 |    HASH JOIN OUTER   |           |    96 |  8448 |     6  (17)| 00:00:01 |  
  23. |   4 |     TABLE ACCESS FULL| SERVERS   |    96 |  6048 |     2   (0)| 00:00:01 |  
  24. |*  5 |     TABLE ACCESS FULL| SERV_INST |    57 |  1425 |     3   (0)| 00:00:01 |  
  25. ----------------------------------------------------------------------------------  
  26.   
  27. Predicate Information (identified by operation id):  
  28. ---------------------------------------------------  
  29.   
  30.    2 - filter("T"."SIID" IS NOT NULL OR "T"."SIID" IS NULL AND  
  31.               "S"."SRVR_ID"=3333333)  
  32.    3 - access("S"."SRVR_ID"="T"."SRVR_ID"(+))  
  33.    5 - filter("T"."SI_STATUS"(+)='Activated' AND "T"."TYPE"(+)='UNIX')  
  34.   
  35. Note  
  36. -----  
  37.    - dynamic sampling used for this statement  
  38.   
  39.   
  40. 统计信息  
  41. ----------------------------------------------------------  
  42.           0  recursive calls  
  43.           0  db block gets  
  44.          12  consistent gets  
  45.           0  physical reads  
  46.           0  redo size  
  47.        1154  bytes sent via SQL*Net to client  
  48.         385  bytes received via SQL*Net from client  
  49.           2  SQL*Net roundtrips to/from client  
  50.           0  sorts (memory)  
  51.           0  sorts (disk)  
  52.          11  rows processed  
  53.   
  54. SQL>   

 以上三种方式比较,看来还是用外连接的方式查询效率更高

小祥V5
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql带有in的子查询_含有IN的子查询
weixin_36084686的博客
01-19 1364
本文主要探讨MySQL5.5和MYSQL5.6对包含in子查询的优化情况。MySQL5.5首先来看一下，在MySQL5.5及以下版本是如何处理的，比如下面这条语句：select name from search_hash where info_hash in (select info_hash from search_filelist);这种语句我们见的很多，熟悉mysql的人都会建议对其改写，但...
交叉表查询sql语句
09-11
交叉表查询SQL语句 交叉表查询是一种常用的SQL查询方法，它可以将多个表中的数据联合起来，并将结果呈现出来。在本文中，我们将学习如何使用交叉表查询来解决实际问题。 交叉表查询的基本概念 交叉表查询是指在一...
sql语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句
01-19
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接。在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率。在子查询中,NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。 如 我要查询 Sendorder表中的冗余数据（没有和reg_person或worksite相连的数据） sql=”select Sendorder.id,Sendorder.reads,Sendor
SQL 子查询怎么优化？写的很深！
芋艿V
04-17 559
点击上方“芋道源码”，选择“设为星标”管她前浪，还是后浪？能浪的浪，才是好浪！每天 8:55 更新文章，每天掉亿点点头发...源码精品专栏原创 | Java 2020 超神之路，很肝~中...
子查询里面有树形查询，子查询选择使用in/exists需要特别留意
落落的专栏 专注SQL调优 性能调优
12-06 8753
今天下午盖尔又发来一条坑爹的SQL，哎，最近被盖尔给咕噜惨了，老是发一些鸟SQL让我调，最近都帮忙调了N多个SQL了，还好盖尔良心发现了，说这个月末给我重200元的话费。认识盖尔的人都知道，盖尔发的SQL那绝对是坑爹的，说它是OLTP的SQL不像，因为OLTP的SQL没那么复杂，说它是OLAP的也不像，因为我没看见典型的事实表，维度表的JOIN，那么唯一的解释就是他们DB设计的人是SB。  盖尔
一波骚操作，我把 SQL 执行效率提高了 10,000,000 倍
十指波课堂的博客
08-07 771
场景我用的数据库是mysql5.6，下面简单的介绍下场景课程表：数据100条学生表:数据70000条学生成绩表SC数据70w条查询目的：查找语文考100分的考生查询语句：select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 ) 执行时间：30248...
SQLServer 优化SQL语句 in 和not in的替代方案
09-11
在SQL Server中，优化SQL语句是提升数据库性能的关键环节。在处理大量数据时，尤其需要注意避免使用效率较低的操作符，如`IN`和`NOT IN`。这些操作符虽然在编写时提供了简洁和易读性，但在执行效率上往往不如其他...
SQL子查询语句简单示例
01-19
SQL 中的 SELECT 命令不仅能够直接用来查询数据库中的数据，还能够嵌入到 WHERE 中作为子查询语句，或者将查询结果插入到另一个表中。 　将子查询用于 WHERE 子句 　子查询能够与 SELECT、INSERT、UPDATE 和 ...
postgre写sql时使用别的方法代替IN子句以提高查询效率
oak_javaLearner的博客
09-06 2089
postgre替换in子句以提高查询效率
【一文读懂】Mysql索引原理之环环相扣
zyq8514700的博客
08-14 575
MySQL为什么要使用索引？ MySQL读写比例一般是10:1，大部分性能瓶颈在查询，所有优化性能的关键点都在查询，而索引可以有效的提升MySQL的查询性能。 为什么索引能提升查询性能？ 索引通过特殊的数据结构和查询方式，能有效减少查询范围，减少IO的参数，从而提升查询效率 究竟什么是索引呢？ 数据库索引本质上是一种数据结构（存储结构+算法），目的是为了加快搜索性能 那数据库索引的数据...
sql中用什么替代in
热门推荐
持续改进
10-21 3万+
IN和EXISTS有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where子句中可以使用两种格式的子查询。 第一种格式是使用IN操作符： ... where column in(select * from ... where ...); 第二种格式是使用EXIST操作符： ... where exists (select X from ...whe
MySql中in查询效率低的替代方法
WWF_HelloWorld
12-18 2万+
在项目中，有一个in查询效率很低，耗时大概10多秒，修改后为1秒左右，本来想造一组数据展现效果的，发现实际情况比较复杂，跟具体的关联数据类型、列是否有索引等相关，实际情况并不是某种查询就肯定比另一种查询效率高。在此不再费心思造数据，仅列出几种可能的查询方法，以备需要时尝试。 1. in查询实现 select * from product where id in (select rela_id f...
比in效率更高的sql查询语句
山鹰的专栏
06-03 4849
很多时候用 exists是一个好的选择： elect num from a where num in(select num from b) 用下面的语句替换： select num from a where exists(select 1 from b where num=a.num) SELECT SUM(T1.C1)FROM T1 WHERE(  (SELECT COUNT(*)
SQL优化必知技巧——使用exists 代替 in
neweastsun的专栏
10-07 2万+
SQL优化必知技巧——使用exists 代替 in in 谓词非常方便好用，而且代码也比较容易理解，所以使用非常频繁。但in却成为性能优化的瓶颈，本文通过示例给你详细说明。 示例数据及问题 员工参数课程培训数据，两次不同课程培训分别存在CLASS_A 和 CLASS_B 两张表中。 CLASS_A： id(编号) name(名称) 1 李承 2 李莉 3 李丽 CLAS...
SQL替换NOT IN的几种方法总结
09-25 3823
NOT IN很好用，但效率很低，共有如下几种方法可以替换，我用几十万数据的测试，最后一种效率是最好的 select email from email where email not in(select distinct email from log); SELECT email FROM email WHERE not exists (select email from log where l
SQL 中写 in 和 not in的替代方案
最新发布
浩浩的博客
03-19 1067
SQL 中写 in 和 not in的优化方案
sql查询exist替换in
weixin_30897079的博客
10-16 294
很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)用下面的语句替换：select num from a where exists(select 1 from b where num=a.num) 转载于:https://www.cnblogs.com/l1pe1/p/76763...
SQL中如何使用EXISTS替代IN
zunguitiancheng的专栏
03-22 565
https://blog.csdn.net/xccscsdn/article/details/80537849 出处地址http://www.cnblogs.com/V1haoge/p/6385312.html 　　我们在程序中一般在做SQL优化的时候讲究使用EXISTS带替代IN的做法，理由是EXISTS执行效率要比IN高。 　　之前我一直挺懵懂的一件事情是如何使用EXISTS来替换I...
mysql条件多值使用= in效率,项目中经常使用的19条MySQL优化，你知道几个？
weixin_29605669的博客
03-18 404
1、EXPLAIN作MySQL优化，咱们要善用 EXPLAIN 查看SQL执行计划。mysql下面来个简单的示例，标注(1,2,3,4,5)咱们要重点关注的数据面试 type列，链接类型。一个好的sql语句至少要达到range级别。杜绝出现all级别sqlkey列，使用到的索引名。若是没有选择索引，值是NULL。能够采起强制索引方式数组key_len列，索引长度网络rows列，扫描行数。该值是个预...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值