Question

SQL子查询怎么提高查询效率

要查询几张表，有用户表 用户信息详细表，还有个用户照片表。要查询在一定条件范围的用户是有头像的并且该用户有照片的也要查询出来。V_User_AboutMe_ByMain 是用户表和用户详细表的视图。dbo.T_User_Photo是用户照片表。他们关联的就是UserKeyID。 查询语句如下select * from V_User_AboutMe_ByMain  where  1=1  and Sex='男'  and Age between '22' and '58'  and Height between '160' and '186'  and headIsState='1'  or UserKeyID in( select UserKeyID from dbo.T_User_Photo where UserKeyID in (select UserKeyID from V_User_AboutMe_ByMain where  1=1  and Sex='男'  and Age between '22' and '58'  and Height between '160' and '186' ) group by UserKeyID) 这样写查询效率极慢，有时查询还超时。有什么方法可以优化啊？

Answer 1

我晕，这样in都套了两层了，肯定慢了啊，你可以联表啊，通过userkeyid这个枣者字段，把两个表联合起来，然后在根据下面这个语句就可以查询了，如果有照片的话，那么p.照片这个字段凳乱薯就不为空，要是还要筛选的话，可以再套一层，判断下照片是否为空就可以了，尽量避免套那么多层，相当于好几个循环，用联表的方陪瞎式或者是子查询的方式应该都是可以实现的，db2里面使用子查询速度快点，oracle的话联表比较快

select
t.身高,
t.性别,
t.其它信息,
p.照片
from v_user_aboutme_bymain t ,t_user_photo p
where t.userkeyid=p.userkeyid
and t.sex='男'
and t.age between '22' and '58'
and t.height between '160' and '186'
and t.headisstate='1'

Answer 2

select *
left join V_User_AboutMe_ByMain
on dbo.T_User_Photo.UserKeyID=V_User_AboutMe_ByMain.UserKeyID
where 1=1 and Sex='男'
and Age between '22' and '58'桐慧
and Height between '160' and '186'
and headIsState='1'
and （局坦答信蔽dbo.T_User_Photo的photo字段）is not null

Answer 3

UserKeyID in( select UserKeyID from dbo.T_User_Photo where UserKeyID in (select UserKeyID from V_User_AboutMe_ByMain where 1=1 and Sex='男' and Age between '22'肆兆 and '58' and Height between '160' and '186' )

楼主的这个桐埋条件是最耗局雹蚂时的，全表扫描