sql in not in where 混搭 效率低下

Question

   一个 旧项目 出现的 能源问题：     出现问题的 原句：
 
select * from stock where  leechdomId 
   in
    (
      select leechdomId from collect 
         where id 
            not in
              (
              select collectId from quote 
                  where type=0 and date >'2010-06-01 10:00:02'
               ) and date>'2010-06-01 10:00:02'
     )  and date>'2010-06-01 10:00:02'
    in  嵌套 not in  加 三个where  有两个and   运行多次 一般需要 7秒；
-------------------------------------------------------------------------------  in 里面的句子   select leechdomId from collect 
         where id 
        not in
              (
              select collectId from quote 
                  where type=0 and date >'2010-06-01 10:00:02'
               ) and date>'2010-06-01 10:00:02'   也就是 在in 里 嵌套的句子同样需要 7秒
--------------------------------------------------------------------------
  not in  最深层的句子   select collectId from quote 
                  where type=0 and date >'2010-06-01 10:00:02'     运行 显示为 0秒
--------------------------------------------------------------------------
第二层  in 里面的句子 去掉一个条件
   去掉时间判断：select leechdomId from collect 
         where id 
        not in
              (
              select collectId from quote 
                  where type=0 and date >'2010-06-01 10:00:02'
               )   或者去掉 not in：  select leechdomId from collect 
         where  date>'2010-06-01 10:00:02'  时间都会显示为 0秒
------------------------------------------------------------------------   综合以上的结果来看，in 并没有显现降低效率(费解)，如果把第二层的 not in  和判断时间的语句 放在一起的话 会降低效率，去掉一个就不会产生效率问题(同样费解)。
  费解中。 求指教，怎么样才能解决掉这种效率问题，还有今后怎么去避免这种问题，现在的数据是 大约是 3万条

Answer 1

id 和 date 是单独建的索引。有没有id,date的联合索引

Answer 2

  77 的意思是说 建一个 id,date的联合索引？？

Answer 3

 n-o-t-  -e-x-i-s-t-s-  -需-要-5-s ....  -而-且-还-查-不-出-结-果-来-。-   买-火-车-票-去-了-。  回-来-继-续-等-答-案-。。
您的回复正文中有非法词或词组！
回复太快，请先休息一下

Answer 4

set statistics profile on把执行计划贴出来看看。

Answer 5

in , not in 查询很慢.建议改为exists , not exists.
不过exists , not exists也比较慢,只是比in,not in稍好一些.其实查询速度的快慢原则取决于你的需求,如果你的需求如此,则没有好的办法.

Answer 6

   执行计划 ：
   374 1 select * from stock where  leechdomId      in      (        select leechdomId from collect            where id           not in                (                select collectId from quote                     where type=0 and date >'2010-06-01 10:00:02'                ) and date>'2010-06-01 10:00:02')  and date>'2010-06-01 10:00:02' 1 1 0 NULL NULL NULL NULL 1.421209 NULL NULL NULL 0.3708893 NULL NULL SELECT 0 NULL
374 1   |--Hash Match(Right Semi Join, HASH:([ynLee].[dbo].[collect].[leechdomId])=([ynLee].[dbo].[stock].[leechdomId])) 1 2 1 Hash Match Right Semi Join HASH:([ynLee].[dbo].[collect].[leechdomId])=([ynLee].[dbo].[stock].[leechdomId]) NULL 1.421209 0 0.02578049 32 0.3708893 [ynLee].[dbo].[stock].[id], [ynLee].[dbo].[stock].[hospitalId], [ynLee].[dbo].[stock].[leechdomId], [ynLee].[dbo].[stock].[number], [ynLee].[dbo].[stock].[date], [ynLee].[dbo].[stock].[type] NULL PLAN_ROW 0 1
176 1        |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([ynLee].[dbo].[collect].[id])) 1 3 2 Nested Loops Left Anti Semi Join OUTER REFERENCES:([ynLee].[dbo].[collect].[id]) NULL 1 0 4.18E-06 11 0.2407371 [ynLee].[dbo].[collect].[leechdomId] NULL PLAN_ROW 0 1
1411 1        |    |--Clustered Index Scan(OBJECT:([ynLee].[dbo].[collect].[PK_collect]), WHERE:([ynLee].[dbo].[collect].[date]>'2010-06-01 10:00:02.000')) 1 4 3 Clustered Index Scan Clustered Index Scan OBJECT:([ynLee].[dbo].[collect].[PK_collect]), WHERE:([ynLee].[dbo].[collect].[date]>'2010-06-01 10:00:02.000') [ynLee].[dbo].[collect].[id], [ynLee].[dbo].[collect].[leechdomId] 1 0.02164352 0.007637 23 0.02928052 [ynLee].[dbo].[collect].[id], [ynLee].[dbo].[collect].[leechdomId] NULL PLAN_ROW 0 1
1235 1411        |    |--Top(TOP EXPRESSION:((1))) 1 6 3 Top Top TOP EXPRESSION:((1)) NULL 1 0 1E-07 9 0.2081883 NULL NULL PLAN_ROW 0 1
1235 1411        |         |--Clustered Index Scan(OBJECT:([ynLee].[dbo].

Quote:

.[PK_quote]), WHERE:([ynLee].[dbo].[collect].[id]=[ynLee].[dbo].

Quote:

.[collectId] AND [ynLee].[dbo].

Quote:

.[date]>'2010-06-01 10:00:02.000' AND CONVERT_IMPLICIT(int,[ynLee].[dbo].

Quote:

.[type],0)=(0))) 1 8 6 Clustered Index Scan Clustered Index Scan OBJECT:([ynLee].[dbo].

Quote:

.[PK_quote]), WHERE:([ynLee].[dbo].[collect].[id]=[ynLee].[dbo].

Quote:

.[collectId] AND [ynLee].[dbo].

Quote:

.[date]>'2010-06-01 10:00:02.000' AND CONVERT_IMPLICIT(int,[ynLee].[dbo].

Quote:

.[type],0)=(0)) NULL 1 0.1386806 0.0418437 20 0.1641589 NULL NULL PLAN_ROW 0 1
4713 1        |--Clustered Index Scan(OBJECT:([ynLee].[dbo].[stock].[PK_stock]), WHERE:([ynLee].[dbo].[stock].[date]>'2010-06-01 10:00:02.000')) 1 12 2 Clustered Index Scan Clustered Index Scan OBJECT:([ynLee].[dbo].[stock].[PK_stock]), WHERE:([ynLee].[dbo].[stock].[date]>'2010-06-01 10:00:02.000') [ynLee].[dbo].[stock].[id], [ynLee].[dbo].[stock].[hospitalId], [ynLee].[dbo].[stock].[leechdomId], [ynLee].[dbo].[stock].[number], [ynLee].[dbo].[stock].[date], [ynLee].[dbo].[stock].[type] 1752.929 0.0705324 0.0236046 32 0.09413701 [ynLee].[dbo].[stock].[id], [ynLee].[dbo].[stock].[hospitalId], [ynLee].[dbo].[stock].[leechdomId], [ynLee].[dbo].[stock].[number], [ynLee].[dbo].[stock].[date], [ynLee].[dbo].[stock].[type] NULL PLAN_ROW 0 1

Answer 7

用exists/not exists试试select * from stock where   
   exists
    (
      select 1 from collect 
         where 
            not exists
              (
              select 1 from quote 
                  where type=0 and date >'2010-06-01 10:00:02' and collect.id=collectId
               ) and date>'2010-06-01 10:00:02'  and leechdomId=stock.leechdomId
     )  and date>'2010-06-01 10:00:02'
估计出在索引没有建，或不合理。楼主看看执行计划是否没有用到索引

Answer 8

  |--Hash Match有这个说明没有合适的覆盖的索引, |--Clustered Index Scan(OBJECT:([ynLee].[dbo].[stock].[PK_stock]), WHERE:([ynLee].[dbo].[stock].[date]>'2010-06-01 10:00:02.000'))    1    12    2    Clustered Index Scan    Clustered Index Scan    OBJECT:([ynLee].[dbo].[stock].[PK_stock]), WHERE:([ynLee].[dbo].[stock].[date]>'2010-06-01 10:00:02.000')    [ynLee].[dbo].[stock].[id], [ynLee].[dbo].[stock].[hospitalId], [ynLee].[dbo].[stock].[leechdomId], [ynLee].[dbo].[stock].[number], [ynLee].[dbo].[stock].[date], [ynLee].[dbo].[stock].[type]    1752.929    0.0705324    0.0236046    32    0.09413701    [ynLee].[dbo].[stock].[id], [ynLee].[dbo].[stock].[hospitalId], [ynLee].[dbo].[stock].[leechdomId], [ynLee].[dbo].[stock].[number], [ynLee].[dbo].[stock].[date], [ynLee].[dbo].[stock].[type]    NULL    PLAN_ROW    0    1
:([ynLee].[dbo].[stock].[PK_stock]), 看你这里就是主键的聚集索引扫描,把表全扫了,加非聚集索引吧,在连接字段和条件字段

Answer 9

  建索引之前：  
374 1 select * from stock where  leechdomId      in      (        select leechdomId from collect            where id           not in                (                select collectId from quote                     where type=0 and date >'2010-06-01 10:00:02'                ) and date>'2010-06-01 10:00:02')  and date>'2010-06-01 10:00:02' 1 1 0 NULL NULL NULL NULL 1.421209 NULL NULL NULL 0.3708893 NULL NULL SELECT 0 NULL
374 1   |--Hash Match(Right Semi Join, HASH:([ynLee].[dbo].[collect].[leechdomId])=([ynLee].[dbo].[stock].[leechdomId])) 1 2 1 Hash Match Right Semi Join HASH:([ynLee].[dbo].[collect].[leechdomId])=([ynLee].[dbo].[stock].[leechdomId]) NULL 1.421209 0 0.02578049 32 0.3708893 [ynLee].[dbo].[stock].[id], [ynLee].[dbo].[stock].[hospitalId], [ynLee].[dbo].[stock].[leechdomId], [ynLee].[dbo].[stock].[number], [ynLee].[dbo].[stock].[date], [ynLee].[dbo].[stock].[type] NULL PLAN_ROW 0 1
176 1        |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([ynLee].[dbo].[collect].[id])) 1 3 2 Nested Loops Left Anti Semi Join OUTER REFERENCES:([ynLee].[dbo].[collect].[id]) NULL 1 0 4.18E-06 11 0.2407371 [ynLee].[dbo].[collect].[leechdomId] NULL PLAN_ROW 0 1
1411 1        |    |--Clustered Index Scan(OBJECT:([ynLee].[dbo].[collect].[PK_collect]), WHERE:([ynLee].[dbo].[collect].[date]>'2010-06-01 10:00:02.000')) 1 4 3 Clustered Index Scan Clustered Index Scan OBJECT:([ynLee].[dbo].[collect].[PK_collect]), WHERE:([ynLee].[dbo].[collect].[date]>'2010-06-01 10:00:02.000') [ynLee].[dbo].[collect].[id], [ynLee].[dbo].[collect].[leechdomId] 1 0.02164352 0.007637 23 0.02928052 [ynLee].[dbo].[collect].[id], [ynLee].[dbo].[collect].[leechdomId] NULL PLAN_ROW 0 1
1235 1411        |    |--Top(TOP EXPRESSION:((1))) 1 6 3 Top Top TOP EXPRESSION:((1)) NULL 1 0 1E-07 9 0.2081883 NULL NULL PLAN_ROW 0 1
1235 1411        |         |--Clustered Index Scan(OBJECT:([ynLee].[dbo].

Quote:

.[PK_quote]), WHERE:([ynLee].[dbo].[collect].[id]=[ynLee].[dbo].

Quote:

.[collectId] AND [ynLee].[dbo].

Quote:

.[date]>'2010-06-01 10:00:02.000' AND CONVERT_IMPLICIT(int,[ynLee].[dbo].

Quote:

.[type],0)=(0))) 1 8 6 Clustered Index Scan Clustered Index Scan OBJECT:([ynLee].[dbo].

Quote:

.[PK_quote]), WHERE:([ynLee].[dbo].[collect].[id]=[ynLee].[dbo].

Quote:

.[collectId] AND [ynLee].[dbo].

Quote:

.[date]>'2010-06-01 10:00:02.000' AND CONVERT_IMPLICIT(int,[ynLee].[dbo].

Quote:

.[type],0)=(0)) NULL 1 0.1386806 0.0418437 20 0.1641589 NULL NULL PLAN_ROW 0 1
4713 1        |--Clustered Index Scan(OBJECT:([ynLee].[dbo].[stock].[PK_stock]), WHERE:([ynLee].[dbo].[stock].[date]>'2010-06-01 10:00:02.000')) 1 12 2 Clustered Index Scan Clustered Index Scan OBJECT:([ynLee].[dbo].[stock].[PK_stock]), WHERE:([ynLee].[dbo].[stock].[date]>'2010-06-01 10:00:02.000') [ynLee].[dbo].[stock].[id], [ynLee].[dbo].[stock].[hospitalId], [ynLee].[dbo].[stock].[leechdomId], [ynLee].[dbo].[stock].[number], [ynLee].[dbo].[stock].[date], [ynLee].[dbo].[stock].[type] 1752.929 0.0705324 0.0236046 32 0.09413701 [ynLee].[dbo].[stock].[id], [ynLee].[dbo].[stock].[hospitalId], [ynLee].[dbo].[stock].[leechdomId], [ynLee].[dbo].[stock].[number], [ynLee].[dbo].[stock].[date], [ynLee].[dbo].[stock].[type] NULL PLAN_ROW 0 1
 之后：374 1 select * from stock where  leechdomId      in      (        select leechdomId from collect            where id           not in                (                select collectId from quote                     where type=0 and date >'2010-06-01 10:00:02'                ) and date>'2010-06-01 10:00:02')  and date>'2010-06-01 10:00:02' 1 1 0 NULL NULL NULL NULL 456.2018 NULL NULL NULL 0.4210421 NULL NULL SELECT 0 NULL
374 1   |--Hash Match(Right Semi Join, HASH:([ynLee].[dbo].[collect].[leechdomId])=([ynLee].[dbo].[stock].[leechdomId])) 1 2 1 Hash Match Right Semi Join HASH:([ynLee].[dbo].[collect].[leechdomId])=([ynLee].[dbo].[stock].[leechdomId]) NULL 456.2018 0 0.02883573 32 0.4210421 [ynLee].[dbo].[stock].[id], [ynLee].[dbo].[stock].[hospitalId], [ynLee].[dbo].[stock].[leechdomId], [ynLee].[dbo].[stock].[number], [ynLee].[dbo].[stock].[date], [ynLee].[dbo].[stock].[type] NULL PLAN_ROW 0 1
176 1        |--Hash Match(Right Anti Semi Join, HASH:([ynLee].[dbo].

Quote:

.[collectId])=([ynLee].[dbo].[collect].[id])) 1 3 2 Hash Match Right Anti Semi Join HASH:([ynLee].[dbo].

Quote:

.[collectId])=([ynLee].[dbo].[collect].[id]) NULL 278.6284 0 0.03762385 11 0.2878347 [ynLee].[dbo].[collect].[leechdomId] NULL PLAN_ROW 0 1
1235 1        |    |--Clustered Index Scan(OBJECT:([ynLee].[dbo].

Quote:

.[PK_quote]), WHERE:([ynLee].[dbo].

Quote:

.[date]>'2010-06-01 10:00:02.000' AND CONVERT_IMPLICIT(int,[ynLee].[dbo].

Quote:

.[type],0)=(0))) 1 5 3 Clustered Index Scan Clustered Index Scan OBJECT:([ynLee].[dbo].

Quote:

.[PK_quote]), WHERE:([ynLee].[dbo].

Quote:

.[date]>'2010-06-01 10:00:02.000' AND CONVERT_IMPLICIT(int,[ynLee].[dbo].

Quote:

.[type],0)=(0)) [ynLee].[dbo].

Quote:

.[collectId] 1354.369 0.1386806 0.0418437 20 0.1805243 [ynLee].[dbo].

Quote:

.[collectId] NULL PLAN_ROW 0 1
1411 1        |    |--Clustered Index Scan(OBJECT:([ynLee].[dbo].[collect].[PK_collect]), WHERE:([ynLee].[dbo].[collect].[date]>'2010-06-01 10:00:02.000')) 1 7 3 Clustered Index Scan Clustered Index Scan OBJECT:([ynLee].[dbo].[collect].[PK_collect]), WHERE:([ynLee].[dbo].[collect].[date]>'2010-06-01 10:00:02.000') [ynLee].[dbo].[collect].[id], [ynLee].[dbo].[collect].[leechdomId] 1411 0.02164352 0.007637 23 0.02928052 [ynLee].[dbo].[collect].[id], [ynLee].[dbo].[collect].[leechdomId] NULL PLAN_ROW 0 1
4713 1        |--Clustered Index Scan(OBJECT:([ynLee].[dbo].[stock].[PK_stock]), WHERE:([ynLee].[dbo].[stock].[date]>'2010-06-01 10:00:02.000')) 1 9 2 Clustered Index Scan Clustered Index Scan OBJECT:([ynLee].[dbo].[stock].[PK_stock]), WHERE:([ynLee].[dbo].[stock].[date]>'2010-06-01 10:00:02.000') [ynLee].[dbo].[stock].[id], [ynLee].[dbo].[stock].[hospitalId], [ynLee].[dbo].[stock].[leechdomId], [ynLee].[dbo].[stock].[number], [ynLee].[dbo].[stock].[date], [ynLee].[dbo].[stock].[type] 1752.929 0.0705324 0.0236046 32 0.09413701 [ynLee].[dbo].[stock].[id], [ynLee].[dbo].[stock].[hospitalId], [ynLee].[dbo].[stock].[leechdomId], [ynLee].[dbo].[stock].[number], [ynLee].[dbo].[stock].[date], [ynLee].[dbo].[stock].[type] NULL PLAN_ROW 0 1
   怎么会少了一条呢。。 

Answer 10

诡异 啊   刚才 的时候  建索引之前 381条  之后 380条 刚笔记本没电自动休眠了   插上电 之后 索引前 后都是 374条了    
  终于知道  增删改查  非要把增说成插入了  原来 sql 就是欠X   邪恶中 。

Answer 11

楼主这个sql语句 使用3个表 内联试试

Answer 12

select * from collect 
1164 35460 11000 2010-01-27 08:39:20.000 1
1165 17900 900 2010-01-27 08:39:20.000 1
1166 18110 30 2010-01-27 08:39:20.000 1
1167 10400 120 2010-01-27 08:39:20.000 1
1168 27710 20 2010-01-27 08:39:20.000 1
1169 35080 11000 2010-01-27 08:39:20.000 1select * from  stock
3275 123 24710 10 2010-01-25 09:32:57.000 1
3276 123 28990 80 2010-01-25 09:35:53.000 1
3277 115 16450 100 2010-01-25 09:38:13.000 1
3278 115 16920 50 2010-01-25 09:38:32.000 1
3279 115 16410 30 2010-01-25 09:39:17.000 1
3280 115 15550 100 2010-01-25 09:40:49.000 1select * from quote
8887 1436 808 13.00 2010-01-27 09:04:51.000 1
8888 2009 808 25.00 2010-01-27 09:05:33.000 1
8889 1707 808 20.00 2010-01-27 09:06:19.000 1
8890 1718 808 3.50 2010-01-27 09:09:01.000 0
8891 1789 808 9.70 2010-01-27 09:09:01.000 1

Answer 13

  - -  collect 
id  leechdomId  number date  typestock
id hpId leechdomId number date typequote
id collectId supplyId price date type

Answer 14

SELECT s.* 
FROM stock s
INNER JOIN collect c ON s.leechdomId = c.collect
left JOIN (select collectId from quote 
                  where type=0 and date >'2010-06-01 10:00:02') q 
                  ON c.id = q.collectId
WHERE  s.date>'2010-06-01 10:00:02'
AND c.date>'2010-06-01 10:00:02'
AND q.collectId IS null楼主发的数据 没有什么用。我发了个这个不知道能不能用

Answer 15

SELECT s.* 
FROM stock s
    INNER JOIN collect c ON s.leechdomId = c.leechdomId
    left JOIN (select collectId from quote 
                  where type=0 and date >'2010-06-01 10:00:02') q 
                  ON c.id = q.collectId
WHERE  s.date>'2010-06-01 10:00:02'
    AND c.date>'2010-06-01 10:00:02'
    AND q.collectId IS null