请教一个SQL查询优化的问题，有点类似集合求差

Question

A、B、C、D是table的四个列，table表很大select D from table
where A=1 and (B,C) not in
(select B,C from table where A=2)上面这条语句的执行速度在A=1和A=2两个集合都比较大的时候会很慢，应该怎么写才能更快的查询呢？
ps:最好是MySQL支持的语法

Answer 1

select D from table t
where A=1 
and not exists(select 1 from table where A=2 and b=t.b and c=t.c)

Answer 2

先谢谢josy
我之前也尝试过像下面的查询语句那样 用not exists来写，但好像查询效率差别不大
select D from table s where s.A=1 and not exists
(select 1 from table t where (t.B,t.C)=(s.B,s.C) and t.A=2)目前table的大小在1万条记录左右，A=1的集合在150左右，A=2的集合在400左右
查询时间已经需要4秒
我希望查询时间在1秒左右，最好在1秒以内我尝试过创建临时表tmp将select B,C from table where A=2的查询结果暂存起来
然后再对临时表做查询
select D from table where A=1 and (B,C) not in
(select * from tmp)
这样的查询速度是可以达到1秒以内的，但使用的资源也多了，而且可能还有其他问题我没想到

Answer 3

假设数据库用MYSQL
select D from table A
LEFT JOIN 
(SELECT B,C from table where A=2) B
ON A.B=B.B AND A.C=B.C
where A=1 and B.C IS NULL

Answer 4

select d 
from table t1 left join (select b,c from table where a=2) t2 
on t1.b=t2.b and t1.c=t2.c 
where a=1 and t2.b is null 看一下你的执行计划。mySQL会根据你的表的情况做出一定的优化，虽然有时候这种优化未必是你所期待的。

Answer 5

感谢楼上两位，试了下，用left join查询速度很快 看来我还得再学学外连接的使用再请教如果我要进一步用D列为条件来更新另一个table x的E字段，那么该怎么写update语句呢？
table x包含D和E两个列
我现在的语句是
update x set E=E+1
where D in(子查询为上面对D的select)

Answer 6

将上述SQL语句存为 VIEW
update x ,VIEW  
set E=E+1 
WHERE X.D=VIEW.D

Answer 7

update x ,
(select D from tT A
LEFT JOIN
(SELECT B,C from tT where A=2) B
ON A.B=B.B AND A.C=B.C
where A=1 and B.C IS NULL ) C1
set E=E+1 
WHERE X.D=C1.DOR
update x INNER JOIN
(select D from tT A
LEFT JOIN
(SELECT B,C from tT where A=2) B
ON A.B=B.B AND A.C=B.C
where A=1 and B.C IS NULL ) C1
ON X.D=C1.D
set E=E+1 
 

Answer 8

直接套进去就行了。update x set E=E+1 
where D in(
select d 
from table t1 left join (select b,c from table where a=2) t2 
    on t1.b=t2.b and t1.c=t2.c 
where a=1 and t2.b is null 
)

Answer 9

也可以试试，速度或许会快上一点儿。前提是X表中为主表，有主键。
update x inner join (
table t1 left join (select b,c from table where a=2) t2 
    on t1.b=t2.b and t1.c=t2.c 
where a=1 and t2.b is null 
) on x.d=t1.d
set E=E+1 

Answer 10

非常感谢wwwwb 、ACMAIN_CHM 、WWWWA 的指教！让我受益匪浅！
来结帖晚了点不好意思