MySQL:如何简化 "IN"查询

Question

我知道我必须避免使用“IN”子句，但我不知道在这种情况下该怎么做。场景是这样的:我从 PHP 获取一个值列表，然后构造一个如下查询:

SELECT id FROM
    ( SELECT * FROM `tips` r 
      LEFT JOIN (SELECT tip FROM `tips_showed` WHERE user='8') AS a ON r.id=a.tip
      WHERE a.tip IS NULL ) AS t
WHERE t.id IN
    ('3','4','5','2','6','7','8','9','10','18',
     '11','12','13','14','15','16','17','20') LIMIT 1

这使得我的网站非常慢(因为每次用户访问它时都会执行它)。如何才能让它更快？

Answer 1

我相信您可以将查询简化为:

SELECT t.id
    FROM `tips` t
        LEFT JOIN `tips_showed` a
            ON t.id = a.tip
                AND a.user = '8'
    WHERE a.tip IS NULL
        AND t.id IN ('3','4','5','2','6','7','8','9','10','18',
                     '11','12','13','14','15','16','17','20')
    LIMIT 1

使用 NOT EXISTS 代替 LEFT JOIN 也可以为您带来一些额外的性能。逐一尝试并进行比较。

 SELECT t.id
     FROM `tips` t
     WHERE t.id IN ('3','4','5','2','6','7','8','9','10','18',
                    '11','12','13','14','15','16','17','20')
        AND NOT EXISTS(SELECT NULL 
                           FROM `tips_showed` a 
                           WHERE a.tip = t.id
                               AND a.user = '8')
    LIMIT 1