sql - 有没有办法让 SQL NOT IN 查询更快？

Question

我想获取每天已记录到数据库但从未出现在日志中的唯一手机条目的数量。我认为这是一个微不足道的查询，但当查询在一个包含大约 900K 条目的表上花费了 10 分钟时，我感到震惊。 Select 示例获取 2015 年 4 月 9 日登录且之前从未登录过的唯一手机的数量。这就像了解谁是您网站在特定日期的真正新访问者。 SQL Fiddle Link

SELECT COUNT(DISTINCT mobile_number)
FROM log_entries
WHERE created_at BETWEEN '2015-04-09 00:00:00'
    AND '2015-04-09 23:59:59'
    AND mobile_number NOT IN (
        SELECT mobile_number
        FROM log_entries
        WHERE created_at < '2015-04-09 00:00:00'
        )

我在 created_at 和 mobile_number 上有单独的索引。

有办法让它更快吗？我看到一个非常相似的问题here on SO但这是在两个表上工作的。

Answer 1

一个NOT IN可以重写为 NOT EXISTS查询通常更快(不幸的是 Postgres 优化器不够智能，无法检测到这一点)。

SELECT COUNT(DISTINCT l1.mobile_number) 
FROM log_entries as l1
WHERE l1.created_at >= '2015-04-09 00:00:00' 
  AND l1.created_at <= '2015-04-09 23:59:59' 
  AND NOT EXISTS (SELECT * 
                  FROM log_entries l2
                  WHERE l2.created_at < '2015-04-09 00:00:00'
                    AND l2.mobile_number = l1.mobile_number);

(mobile_number, created_at) 上的索引应进一步提高性能。

<小时/>

附注:created_at <= '2015-04-09 23:59:59'将不包括带有小数秒的行，例如2015-04-09 23:59:59.789 。处理时间戳时，最好对“下一天”使用“低于”，而不是对相关日期使用“低于或等于”。

所以最好使用:created_at < '2015-04-10 00:00:00'相反，也用小数秒“捕获”当天的行。