mysql - 重写查询以避免 not in 并提高性能

Question

我想选择所有没有关联样式的图片。我有一个样式表，可以将样式与其图片进行映射。这是我现在的查询:

SELECT picture.id, picture.caption, picture.lowresimageurl, picture.medresimageurl
                    FROM instagram_shop_picture picture 
                    INNER JOIN instagram_shop shop ON shop.id = picture.shop_id
                    WHERE picture.deletedAt IS NULL
                        AND picture.isLocked = 0
                        AND picture.isShown = 1
                        AND picture.isTestimonial = 0
                        AND shop.deletedAt IS NULL
                        AND shop.isLocked = 0
                        AND shop.expirydate IS NOT NULL 
                        AND shop.expirydate > now()
                        AND picture.id NOT IN (SELECT style.picture_id FROM instagram_picture_style style)

由于某种原因，此查询运行缓慢。我怎样才能重写它而不必使用 NOT IN

Answer 1

SELECT picture.id, picture.caption, picture.lowresimageurl, picture.medresimageurl
                    FROM instagram_shop_picture picture 
                    INNER JOIN instagram_shop shop ON shop.id = picture.shop_id
                    left join instagram_picture_style style on style.picture_id = picture.id
                    WHERE picture.deletedAt IS NULL 
                        AND picture.isLocked = 0
                        AND picture.isShown = 1
                        AND picture.isTestimonial = 0
                        AND shop.deletedAt IS NULL
                        AND shop.isLocked = 0
                        AND shop.expirydate IS NOT NULL 
                        AND shop.expirydate > now()
                        and style.picture_id is null 

将 instagram_picture_style 表的子查询改为左联接并添加新条件 style.picture_id is null

加入 will be faster than 子查询