如何提高SQL Server Select查询的性能？

Question

下面的T-SQL查询需要54秒才能执行：

SELECT top (3)
   a.c1, b.c2, c.c3, d.c4 
FROM 
   table1 as a WITH (NOLOCK) 
JOIN 
   table2 as b WITH (NOLOCK) ON a.c1 = b.c4 
LEFT JOIN 
   table3 as c WITH (NOLOCK) ON a.c1 = c.c4 
LEFT JOIN 
   table4 as d WITH (NOLOCK) ON b.c3 = d.c1 
WHERE 
   b.source = '8R' 
ORDER BY 
   b.RecvdDate ASC

相同的查询按降序排序，执行速度在一秒钟内完成。

SELECT top (3)
   a.c1, b.c2, c.c3, d.c4 
FROM 
   table1 as a WITH (NOLOCK) 
JOIN 
   table2 as b WITH (NOLOCK) ON a.c1 = b.c4 
LEFT JOIN 
   table3 as c WITH (NOLOCK) ON a.c1 = c.c4 
LEFT JOIN 
   table4 as d WITH (NOLOCK) ON b.c3 = d.c1 
WHERE 
   b.source = '8R' 
ORDER BY 
   b.RecvdDate DESC

那么我如何提高查询在两种排序方式下的性能呢？

我正在使用 Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: )。

使用 SET STATISTICS PROFILE ON 的执行计划如下：

Rows                 Executes             StmtText                                                                                                                                                                                                                                                         StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                                                                                                                         DefinedValues                                                                         EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                                                                                                       Warnings                        Type                                                             Parallel EstimateExecutions
-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ---------------------------------------------------------------- -------- ------------------
3                    1                    SELECT top 3 a.c1, b.c2, c.c3, d.c4  
FROM table1 as a WITH (NOLOCK) JOIN table2 as b WITH (NOLOCK) ON a.c1 = b.c1 LEFT JOIN table3 as c WITH (NOLOCK) ON 1           1           0           NULL                           NULL                           NULL                                                                                                                                                                                                                                                             NULL                                                                                  3             NULL          NULL          NULL        0.1093194        NULL                                                                                                                             NULL                            SELECT                                                           0        NULL
3                    1                      |--Top(TOP EXPRESSION:((3)))                                                                                                                                                                                                                                   1           2           1           Top                            Top                            TOP EXPRESSION:((3))                                                                                                                                                                                                                                             NULL                                                                                  3             0             3E-07         93          0.1093194        [a].[c1], [table2].[c2], [c].[c3], [d].[c4]                                                     NULL                            PLAN_ROW                                                         0        1
3                    1                           |--Nested Loops(Left Outer Join, WHERE:([DB].[dbo].[table3].[c3] as [table3].[c3]=[DB].[dbo].[table4].[c1] as [d].[c1]))                                                                                1           3           2           Nested Loops                   Left Outer Join                WHERE:([DB].[dbo].[table2].[c3] as [table2].[c3]=[DB].[dbo].[table4].[id] as [d].[id])                                                                                                                         NULL                                                                                  3             0             221.8893      101         0.1093191        [a].[c1], [table2].[c2], [table2].[RecvdDate], [c].[c3], [d].[c4]               NULL                            PLAN_ROW                                                         0        1
3                    1                                |--Nested Loops(Left Outer Join, OUTER REFERENCES:([a].[c1], [Expr1012]) WITH ORDERED PREFETCH)                                                                                                                                          1           4           3           Nested Loops                   Left Outer Join                OUTER REFERENCES:([a].[c1], [Expr1012]) WITH ORDERED PREFETCH                                                                                                                                                                                        NULL                                                                                  3             0             18.49077      99          0.1057252        [a].[c1], [table2].[c2], [table2].[RecvdDate], [table2].[C3], [c].[c3]  NULL                            PLAN_ROW                                                         0        1
3                    1                                |    |--Nested Loops(Inner Join, OUTER REFERENCES:([table2].[c4], [Expr1011]) WITH ORDERED PREFETCH)                                                                                                                                  1           6           4           Nested Loops                   Inner Join                     OUTER REFERENCES:([table2].[c4], [Expr1011]) WITH ORDERED PREFETCH                                                                                                                                                                                NULL                                                                                  3             0             18.78465      31          0.09258068       [a].[c1], [table2].[c2], [table2].[RecvdDate], [table2].[C3]               NULL                            PLAN_ROW                                                         0        1
27                   1                                |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([table2].[c1], [Expr1010]) WITH ORDERED PREFETCH)                                                                                                                            1           8           6           Nested Loops                   Inner Join                     OUTER REFERENCES:([table2].[c1], [Expr1010]) WITH ORDERED PREFETCH                                                                                                                                                                               NULL                                                                                  22.09678      0             136.1955      41          0.07926979       [table2].[c4], [table2].[c2], [table2].[RecvdDate], [table2].[C3]       NULL                            PLAN_ROW                                                         0        1
19928681             1                                |    |    |    |--Index Scan(OBJECT:([DB].[dbo].[table2].[IX_datereceived] AS [table2]), ORDERED BACKWARD)                                                                                                     1           10          8           Index Scan                     Index Scan                     OBJECT:([DB].[dbo].[table2].[IX_datereceived] AS [table2]), ORDERED BACKWARD                                                                                                                                               [table2].[c1], [table2].[RecvdDate]                   22.09678      66.78609      35.84109      19          0.003349548      [table2].[c1], [table2].[RecvdDate]                                                              NULL                            PLAN_ROW                                                         0        1
27                   19928681                         |    |    |    |--Clustered Index Seek(OBJECT:([DB].[dbo].[table2].[PK_c1] AS [table2]), SEEK:([table2].[c1]=[DB].[dbo].[table2].[c1] as [table2].[c1]),   1           12          8           Clustered Index Seek           Clustered Index Seek           OBJECT:([DB].[dbo].[table2].[PK_c1] AS [table2]), SEEK:([table2].[c1]=[DB].[dbo].[table2].[c1] as [table2].[c1]),  WHERE:([DB].[dbo].[table2].[Source]  [table2].[c4], [table2].[c2], [table2].[C3]  3.047679      0.003125      0.0001581     77          0.07582787       [table2].[c4], [table2].[c2], [table2].[C3]                                             NULL                            PLAN_ROW                                                         0        23.09678
3                    3                                |    |    |--Index Seek(OBJECT:([DB].[dbo].[table1].[PK_c1] AS [a]), SEEK:([a].[c1]=[DB].[dbo].[table2].[c4] as [table2].[c4]) ORDERED FORWARD)                                  1           20          6           Index Seek                     Index Seek                     OBJECT:([DB].[dbo].[table1].[PK_c1] AS [a]), SEEK:([a].[c1]=[DB].[dbo].[table2].[c4] as [table2].[c4]) ORDERED FORWARD                                                                       [a].[c1]                                                                  1             0.003125      0.0001581     11          0.02297952       [a].[c1]                                                                                                             NULL                            PLAN_ROW                                                         0        7.000085
3                    3                                |    |--Clustered Index Seek(OBJECT:([DB].[dbo].[table3].[table3_PK] AS [c]), SEEK:([c].[c4]=[DB].[dbo].[table1].[c1] as [a].[c1]) ORDERED FORWARD)                                    1           21          4           Clustered Index Seek           Clustered Index Seek           OBJECT:([DB].[dbo].[table3].[table3_PK] AS [c]), SEEK:([c].[c4]=[DB].[dbo].[table1].[c1] as [a].[c1]) ORDERED FORWARD                                                                              [c].[c3]                                                                           1             0.003125      0.0001581     75          0.01313197       [c].[c3]                                                                                                                      NULL                            PLAN_ROW                                                         0        4
36                   3                                |--Table Scan(OBJECT:([DB].[dbo].[table4] AS [d]))                                                                                                                                                                                    1           22          3           Table Scan                     Table Scan                     OBJECT:([DB].[dbo].[table4] AS [d])                                                                                                                                                                                                               [d].[id], [d].[c4]                                                            12            0.0032035     9.17E-05      17          0.0035703        [d].[id], [d].[c4]                                                                                                       NO STATS:([table4].[c1])  PLAN_ROW                                                         0        3.999999

(11 row(s) affected)

链接到执行计划的xml格式：

- Leejoy

---

我们需要查看查询计划（实际查询计划比估算查询计划更好）。将它们保存为*.SQLPLAN文件并存储到一些可通过互联网访问的存储空间中，然后在此处添加链接。 - RBarryYoung

包括所有可用索引的表定义。此外，还需要提供数据行为描述（如实体关系图中所示）。以及您能够提供的任何其他信息。 - MatBailie

如果你只选择前三行，你能否添加另一个 where 条件来限制 RecvdDate，例如最近5天？这样可以丢弃大多数不必要的行。 - David R Tribble

Answer 1

table2共有32,582,700行。

估计的执行计划如下所示。

您在RecvdDate上有一个非覆盖索引，并且计划需要进行键查找以检索缺失的列并评估source = '8R'的谓词。

SQL Server估计需要进行22-23次这样的查找才能找到前3个匹配的行并退出。

这假设与日期相关的符合8R谓词的行是均匀分布的。在您的情况下，它们不是这样的，它们都在较晚的日期上。实际上，它进行了19,928,681次查找（来自问题中的STATISTICS PROFILE输出），而不是估计的20左右。

最简单的解决方法是提供一个source，RecvdDate的索引。就此查询而言，索引方向无关紧要。任何四种可能性都可以工作。

source asc, RecvdDate asc
source asc, RecvdDate desc
source desc, RecvdDate asc
source desc, RecvdDate desc

它仍然可以在source上执行等式搜索，并根据需要向前或向后遍历匹配行，以获取RecvdDate ASC或RecvdDate DESC。

- Martin Smith

---

我按照你的建议提供了一个索引，这帮助我解决了问题。非常感谢你.. :) - Leejoy

Answer 2

这个性能是不是一致的？由于差异如此之大，我猜测你在使用复合索引。

source, RecvdDate DESC

这意味着在按RecvdDate升序查询时无法使用。

您可以为source和RecvdDate添加单独的索引，这将提高性能（单列索引顺序不重要），或者添加另一个带有RecvdDate按升序排列的组合索引。

- D Stanley

---

发现了一个验证这个理论的问题。https://dev59.com/1nRB5IYBdhLWcg3wCjYV - TTeeple

@Leejoy 你是否有类似我所想的复合索引？ - D Stanley

@DStanley - 这是一个非常普遍的问题，其中存在一个非覆盖索引，并且它选择了一个带有查找操作的计划，低估了在达到“TOP N”之前所需的查找次数。它假设匹配的行将均匀分散在索引中。如果它们全部聚集在一端，那么估计的查找次数可能会大大偏离。 - Martin Smith

@Leejoy - 如果你想得到除了猜测以外的东西，请发布执行计划。 - Martin Smith

@Leejoy - 你能上传XML版本吗？那个版本很难读，因为对齐有问题，而且与XML相比，信息还不够完整。 - Martin Smith