主页 / dba / 问题 / 48830
Accepted
jcalfee314
Asked: 2013-08-28 08:59:04 +0800 CST

使用 WHERE NOT IN 子选择子句提高性能

  • 772

在以下查询中，我必须计算每个客户的交易。但是，我必须从结果集中完全排除交易超过一年的客户。

查询优化器不应该足够聪明，只为每个客户评估一次存在吗？

--Count transactions on customers that are less than 1 year old

SELECT t1.CUSTID, COUNT(*)
FROM CUST_TRX t1
WHERE NOT EXISTS ( 
  SELECT FIRST 1 1 
  FROM CUST_TRX t2 
  WHERE 
    t2.CUSTID = t1.CUSTID AND
    t2.DATED < CURRENT_DATE - 365
  GROUP BY t2.CUSTID
)
GROUP BY t1.CUSTID

我的查询计划中没有自然。此查询的执行就像数据库为每个事务运行存在子句，而不是为每个客户运行它。GROUP BY如果我删除子查询中的 ，性能是相同的。

有没有更好的方法来做到这一点，以便我可以从数据库中获得更好的性能？如果可能的话，希望一个简单的SELECT查询能够避免 CTE（这会带来其他挑战）。

由于其他GROUP BY条件（此处未显示），我无法简单地检查MIN(DATED)，我真的需要执行另一个查询。

performance subquery

2 个回答

  1. Best Answer

    LEFT OUTER JOIN对于这样的查询，执行 a而不是NOT EXISTS样式检查通常更有效，它通常意味着完整的索引扫描（或没有正确索引的表扫描）但是在主表中有很多行，这会更少比大量的索引查找（主表返回的每一行的参考表上的一个）昂贵，否则会导致。一些查询计划者非常善于发现这种等价性并使用替代计划，这是更好的选择，但在您的情况下听起来并没有发生这种情况。

    尝试类似：

    SELECT t1.CUSTID, COUNT(*)
FROM   CUST_TRX t1
LEFT OUTER JOIN
       CUST_TRX t2 
ON     t2.CUSTID=t1.CUSTID 
AND    t2.DATED<CURRENT_DATE-365
WHERE  t2.CUSTID IS NULL
GROUP BY t1.CUSTID

    （注意：我不熟悉火鸟，所以上面的语法可能需要调整，但应该说明这一点）

    如果没有匹配中的每一WHERE t2.CUSTID IS NULL行，将为找到的每个匹配输出一次，而没有匹配的行将输出一次，但从该对象中选择的任何列都设置为 NULL。然后该子句筛选出匹配项。t1t2t2t2WHERE

    取决于数据库引擎的能力，特别是如果参考对象（CUST_TRX此处应用了过滤器）中的数据量很大，这可能比WHERE <something> NOT INor选项效率低得多WHERE NOT EXISTS，因此在使用该方法之前首先对实际数据集进行基准测试。在查询计划者没有注意到WHERE NOT IN可以更有效地执行这种安排的情况下，它通常与 MS SQL Server 一起工作效率更高。

    此外，如果您这样做，请在代码（和/或支持文档）中留下评论，说明您这样做是等效的，WHERE <something> NOT IN或者WHERE NOT EXISTS您希望更有效。您会记住它，并且有经验的 SQL 人员会识别该模式，但是查看代码的其他人可能不会立即理解其意图/原因并将其转回使用WHERE NOT EXISTS，因为这样读起来比英文句子更好。

    • 5

  2. 当您说“计算不到 1 岁的客户的交易”时，您的意思是：

    1. 计算所有不到一年的客户交易？
    2. 计算不到一年的新客户的所有交易？

    从示例代码中，我了解到 #1 是您想要的。在那种情况下，你真的需要 WHERE NOT EXISTS 吗？你能不能做这样的事情：

    SELECT t1.CUSTID, COUNT(*)
FROM CUST_TRX t1
WHERE t1.DATED>=CURRENT_DATE-365
GROUP BY t1.CUSTID
HAVING COUNT(*) > 0

    我不是 Firebird 用户，但我查找了 GROUP BY / HAVING 语法。

    [编辑]从结果集中排除交易超过一年的客户。

    好的，这里是聚合行以从选择中消除客户的其他方法。

    SELECT A.CUSTID, A.HowMany
FROM (SELECT t1.CUSTID, COUNT(*) HowMany, MIN(t1.DATED) OldestTran
    FROM CUST_TRX t1
    GROUP BY t1.CUSTID
    HAVING COUNT(*) > 0 AND MIN(t1.DATED) >=CURRENT_DATE-365) AS A

    [编辑] 好的，所以查询更复杂，可以体现在单个查询中。

    这意味着您可能需要使用与您第一次发布的模式非常相似的模式。请注意，EXISTS 意味着 DISTINCT，并且通常比来自 SELECT DISTINCT 的 JOIN 更快。但是您可以尝试不同的方法并比较行为、时间等。然后选择您最喜欢的一种。

    • 0

相关问题