Server 2008 R2:调优查询

Question

我有下表，有10亿份记录。

create table PfTest
(
    cola int,
    colb int,
    colc date,
    cold varchar(10),
    ID int
);

现在我想显示的记录是在一个特定的日期，而不是在特定的日期。

对于这些查询，我使用了以下两种类型的查询：

查询1：

select DISTINCT cola, colb, colc, cold, ID
from PfTest
WHERE colc In ('2014-01-01') 
  AND cold NOT IN (SELECT cold 
                   FROM PfTest 
                   WHERE ID = 1 
                     AND colc IN ('2014-01-02', '2014-01-03', 
                                  '2014-01-04', '2014-01-05', '2014-01-06'));

查询2：

WITH cte AS
(
    SELECT DISTINCT cola, colb, colc, cold, ID
    FROM PfTest
    WHERE cold NOT IN (SELECT cold FROM PfTest 
                       WHERE ID = 1 
                         AND colc IN('2014-01-02', '2014-01-03',
                                     '2014-01-04', '2014-01-05', '2014-01-06'))
) 
SELECT cola, colb, colc, cold, ID
FROM cte 
WHERE colc IN ('2014-01-01');   

以上两个查询计划在执行时都是相同的。两个人都花了很长时间来执行死刑。我能为这种情况写一些更好的查询吗？

Answer 1

这是您的查询，没有DISTINCT (这似乎是不必要的)：

select cola, colb, colc, cold, ID
from PfTest
WHERE colc In ('2014-01-01') AND 
      cold NOT IN (SELECT cold
                   from PfTest
                   WHERE ID = 1 AND
                         colc IN ('2014-01-02', '2014-01-03', '2014-01-04', '2014-01-05', '2014-01-06')
                  );

我会从索引开始。PFTest(colc, cold)和PFTest(id, colc, cold).

如果子查询返回大量数据--比如数百万行--那么使用临时表可能会更好。我会先试试这个指数。如果这样做不起作用，在cold上设置索引的临时表可能会工作。另外，尽管它对性能的影响很小，但我会使用NOT EXISTS而不是NOT IN来表示查询。

select cola, colb, colc, cold, ID
from PfTest t
WHERE colc In ('2014-01-01') AND 
      NOT EXISTS (SELECT 1
                  from PfTest t2
                  WHERE t2.cold = t1.cold AND t2.ID = 1 AND
                        t2.colc IN ('2014-01-02', '2014-01-03', '2014-01-04', '2014-01-05', '2014-01-06')
                 );

对于这个版本，最好的索引是PfTest(cold, id, colc)。

当匹配的列具有NULL值时，NULL具有更直观的行为。