SQL 为什么 SELECT COUNT(*) , MIN(col), MAX(col) 比 SELECT MIN(col), MAX(col) 更快

Question

我们发现这些查询之间存在巨大差异。

查询速度慢

SELECT MIN(col) AS Firstdate, MAX(col) AS Lastdate 
FROM table WHERE status = 'OK' AND fk = 4193

表“表”。扫描计数 2，逻辑读取 2458969，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

SQL Server 执行时间:CPU 时间 = 1966 毫秒，运行时间 = 1955 毫秒。

快速查询

SELECT count(*), MIN(col) AS Firstdate, MAX(col) AS Lastdate 
FROM table WHERE status = 'OK' AND fk = 4193

表“表”。扫描计数 1，逻辑读取 5803，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

SQL Server 执行时间:CPU 时间 = 0 毫秒，运行时间 = 9 毫秒。

问题

查询之间巨大的性能差异的原因是什么？

更新根据评论中提出的问题进行一点更新:

执行顺序或重复执行不会改变性能。没有使用额外的参数，并且(测试)数据库在执行期间没有执行任何其他操作。

查询速度慢

|--Nested Loops(Inner Join)
 |--Stream Aggregate(DEFINE:([Expr1003]=MIN([DBTest].[dbo].[table].[startdate])))
   |    |--Top(TOP EXPRESSION:((1)))
   |         |--Nested Loops(Inner Join, OUTER REFERENCES:([DBTest].[dbo].[table].[id], [Expr1008]) WITH ORDERED PREFETCH)
   |              |--Index Scan(OBJECT:([DBTest].[dbo].[table].[startdate]), ORDERED FORWARD)
   |              |--Clustered Index Seek(OBJECT:([DBTest].[dbo].[table].[PK_table]), SEEK:([DBTest].[dbo].[table].[id]=[DBTest].[dbo].[table].[id]),  WHERE:([DBTest].[dbo].[table].[FK]=(5806) AND [DBTest].[dbo].[table].[status]<>'A') LOOKUP ORDERED FORWARD)
   |--Stream Aggregate(DEFINE:([Expr1004]=MAX([DBTest].[dbo].[table].[startdate])))
        |--Top(TOP EXPRESSION:((1)))
             |--Nested Loops(Inner Join, OUTER REFERENCES:([DBTest].[dbo].[table].[id], [Expr1009]) WITH ORDERED PREFETCH)
                  |--Index Scan(OBJECT:([DBTest].[dbo].[table].[startdate]), ORDERED BACKWARD)
                  |--Clustered Index Seek(OBJECT:([DBTest].[dbo].[table].[PK_table]), SEEK:([DBTest].[dbo].[table].[id]=[DBTest].[dbo].[table].[id]),  WHERE:([DBTest].[dbo].[table].[FK]=(5806) AND [DBTest].[dbo].[table].[status]<>'A') LOOKUP ORDERED FORWARD)

快速查询

 |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1012],0)))
   |--Stream Aggregate(DEFINE:([Expr1012]=Count(*), [Expr1004]=MIN([DBTest].[dbo].[table].[startdate]), [Expr1005]=MAX([DBTest].[dbo].[table].[startdate])))
        |--Nested Loops(Inner Join, OUTER REFERENCES:([DBTest].[dbo].[table].[id], [Expr1011]) WITH UNORDERED PREFETCH)
             |--Index Seek(OBJECT:([DBTest].[dbo].[table].[FK]), SEEK:([DBTest].[dbo].[table].[FK]=(5806)) ORDERED FORWARD)
             |--Clustered Index Seek(OBJECT:([DBTest].[dbo].[table].[PK_table]), SEEK:([DBTest].[dbo].[table].[id]=[DBTest].[dbo].[table].[id]),  WHERE:([DBTest].[dbo].[table].[status]<'A' OR [DBTest].[dbo].[table].[status]>'A') LOOKUP ORDERED FORWARD)

回答

下面马丁·史密斯给出的答案似乎可以解释这个问题。超短版本是 MS-SQL 查询分析器在慢查询中错误地使用查询计划，从而导致完整的表扫描。

添加 Count(*)、带有 (FORCESCAN) 的查询提示或开始日期、FK 和状态列上的组合索引可修复性能问题。

Answer 1

SQL Server 基数估计器做出各种建模假设，例如

• Independence: Data distributions on different columns are independent unless correlation information is available.
• Uniformity: Within each statistics object histogram step, distinct values are evenly spread and each value has the same frequency.

Source

表中有 810,064 行。

您有疑问

SELECT COUNT(*),
       MIN(startdate) AS Firstdate,
       MAX(startdate) AS Lastdate
FROM   table
WHERE  status <> 'A'
       AND fk = 4193 

1,893 (0.23%) 行满足 fk = 4193谓词，并且这两个失败 status <> 'A'因此总共 1,891 个匹配项需要聚合。

您还有两个索引，它们都不覆盖整个查询。

为了快速查询，它使用 fk 上的索引直接查找 fk = 4193 所在的行那么需要做 1,893 key lookups查找聚集索引中的每一行以检查 status谓词并检索startdate用于聚合。

当您删除COUNT(*)时来自SELECT list SQL Server 不再必须处理每个符合条件的行。因此，它考虑了另一种选择。

您的索引位于 startdate因此它可以从头开始扫描，对基表进行键查找，一旦找到第一个匹配行就停止，因为它找到了 MIN(startdate) ，类似地MAX可以通过从索引的另一端开始并向后进行另一次扫描来找到。

SQL Server 估计每次扫描最终都会处理 590 行，然后才能找到与谓词匹配的行。总查找次数为 1,180 次，而查找次数为 1,893 次，因此它选择了此计划。

590这个数字就是table_size / estimated_number_of_rows_that_match 。即基数估计器假设匹配的行将均匀分布在整个表中。

不幸的是，满足谓词的 1,891 行不是随机分布于 startdate 。事实上，它们都在索引末尾压缩为一个 8,205 行段，这意味着扫描将到达 MIN(startdate)最终在停止之前进行了 801,859 个键查找。

这可以在下面复制。

CREATE TABLE T
(
id int identity(1,1) primary key,
startdate datetime,
fk int,
[status] char(1),
Filler char(2000)
)

CREATE NONCLUSTERED INDEX ix ON T(startdate)

INSERT INTO T
SELECT TOP 810064 Getdate() - 1,
                  4192,
                  'B',
                  ''
FROM   sys.all_columns c1,
       sys.all_columns c2  


UPDATE T 
SET fk = 4193, startdate = GETDATE()
WHERE id BETWEEN 801859 and 803748 or id = 810064

UPDATE T 
SET  startdate = GETDATE() + 1
WHERE id > 810064


/*Both queries give the same plan. 
UPDATE STATISTICS T WITH FULLSCAN
makes no difference*/

SELECT MIN(startdate) AS Firstdate, 
       MAX(startdate) AS Lastdate 
FROM T
WHERE status <> 'A' AND fk = 4192


SELECT MIN(startdate) AS Firstdate, 
       MAX(startdate) AS Lastdate 
FROM T
WHERE status <> 'A' AND fk = 4193

您可以考虑使用查询提示来强制计划使用 fk 上的索引而不是startdate或者添加执行计划中突出显示的建议缺失索引 (fk,status) INCLUDE (startdate)以避免这个问题。