Question

请高手指导，sql如何提高嵌套查询效率

sql语句如下
select * from dh_w where num in (select picture_id from dh_product where id not in(select sm_product_id from dh_had_upload where shop_cr='IU') and id not in(select sm_product_id from dh_arrange_upload where shop_cr='IU')) order by id limit 0,90
影响的数据栏: 0
时间: 41.072ms
执行时间太长了。有木有改善方法？请高手指导。谢谢！！

Answer 1

给shop_cr 字段加索引
not改成左外连接 + 空值判断

select a.* from dh_w a,(
select c.picture_id from dh_product c left outer join (
SELECT sm_product_id FROM dh_had_upload WHERE shop_cr='IU'
union
SELECT sm_product_id FROM dh_arrange_upload WHERE shop_cr='IU'
) d on c.id = d.sm_product_id
where d.sm_product_id is null
)b where a.num =b.picture_id
ORDER BY a.id limit 0,90

Answer 2

可以一部分一部分筛选吧，

Answer 3

尽量少用in，使用EXISTS；另外，必要时给连接的列加上索引