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

2022-01-07 00:00:00 statistics performance sql correlation sql-server

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

慢查询

SELECT MIN(col) AS Firstdate, MAX(col) AS Lastdate从表 WHERE 状态 = '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从表 WHERE 状态 = 'OK' AND fk = 4193

表'表'.扫描计数1，逻辑读5803，物理读0，预读0，lob逻辑读0，lob物理读0，lob预读0.

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

问题

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

更新基于评论中给出的问题的一些更新:

执行顺序或重复执行对性能没有任何影响.没有使用额外的参数，并且(测试)数据库在执行期间没有做任何其他事情.

慢查询

|--嵌套循环(内连接)|--Stream Aggregate(DEFINE:([Expr1003]=MIN([DBTest].[dbo].[table].[startdate])))||--顶(顶表达式:((1)))||--嵌套循环(内连接，外引用:([DBTest].[dbo].[table].[id], [Expr1008]) WITH ORDERED PREFETCH)||--索引扫描(OBJECT:([DBTest].[dbo].[table].[startdate]), ORDERED FORWARD)||--聚集索引 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') 向前查找)|--Stream Aggregate(DEFINE:([Expr1004]=MAX([DBTest].[dbo].[table].[startdate])))|--顶(顶表达式:((1)))|--嵌套循环(内部连接，外部引用:([DBTest].[dbo].[table].[id], [Expr1009]) WITH ORDERED PREFETCH)|--索引扫描(OBJECT:([DBTest].[dbo].[table].[startdate]), ORDERED BACKWARD)|--聚集索引 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') 向前查找)

快速查询

 |--计算标量(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1012],0)))|--Stream Aggregate(DEFINE:([Expr1012]=Count(*), [Expr1004]=MIN([DBTest].[dbo].[table].[startdate]),[Expr1005]=MAX([DBTest].[dbo].[表].[开始日期])))|--嵌套循环(内连接，外引用:([DBTest].[dbo].[table].[id], [Expr1011]) WITH UNORDERED PREFETCH)|--Index Seek(OBJECT:([DBTest].[dbo].[table].[FK]), SEEK:([DBTest].[dbo].[table].[FK]=(5806)) ORDERED向前)|--聚集索引 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].[状态]>'A') 查找顺序转发)

回答

Martin Smith 下面给出的答案似乎解释了这个问题.超短版本是 MS-SQL 查询分析器在慢查询中错误地使用了查询计划，导致全表扫描.

在 startdate、FK 和 status 列上添加 Count(*)、查询提示 with(FORCESCAN) 或组合索引修复了性能问题.

解决方案

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

<块引用>

独立性:除非相关信息可用，否则不同列上的数据分布是独立的.
均匀性:在每个统计对象直方图步骤中，不同的值均匀分布并且每个值具有相同的频率.

来源

表中有 810,064 行.

您有查询

SELECT COUNT(*),MIN(startdate) AS Firstdate,MAX(开始日期)AS 最后日期发件人表WHERE 状态 <>'一个'与 fk = 4193

1,893 (0.23%) 行满足 fk = 4193 谓词，其中两个不符合 status <>'A' 部分，所以总共有 1,891 个匹配，需要聚合.

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

为了您的快速查询，它使用 fk 上的索引来直接查找 fk = 4193 然后需要执行 1,893 key lookups 查找聚集索引中的每一行以检查 status 谓词并检索startdate 用于聚合.

当您从 SELECT 列表中删除 COUNT(*) 时，SQL Server 不再处理每个符合条件的行.因此，它考虑了另一种选择.

您在 startdate 上有一个索引，因此它可以从头开始扫描，对基表进行键查找，一旦找到第一个匹配的行停止

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 次键查找.

这可以在下面复制.

创建表T(id int identity(1,1) 主键，开始日期时间，fk int,[状态] char(1),填料炭(2000))在 T(开始日期)上创建非聚集索引 ix插入到 T选择顶部 810064 Getdate() - 1,4192,'乙',''从 sys.all_columns c1,sys.all_columns c2更新时间SET fk = 4193, 开始日期 = GETDATE()WHERE id 介于 801859 和 803748 之间或 id = 810064更新时间SET 开始日期 = GETDATE() + 1WHERE id >810064/* 两个查询给出相同的计划.使用全扫描更新统计信息 T没有区别*/SELECT MIN(startdate) AS Firstdate,MAX(开始日期)AS 最后日期从TWHERE 状态 <>'A' 和 fk = 4192SELECT MIN(startdate) AS Firstdate,MAX(开始日期)AS 最后日期从TWHERE 状态 <>'A' 和 fk = 4193

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

We're seeing a huge difference between these queries.

The slow query

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

Table 'table'. Scan count 2, logical reads 2458969, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 1966 ms, elapsed time = 1955 ms.

The fast query

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

Table 'table'. Scan count 1, logical reads 5803, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 9 ms.

Question

What is the reason between the huge performance difference between the queries?

Update A little update based on questions given as comments:

The order of execution or repeated execution changes nothing performance wise. There are no extra parameters used and the (test)database is not doing anything else during execution.

Slow query

|--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)

Fast query

 |--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)

Answer

The answer given below by Martin Smith seems to explain the problem. The super short version is that the MS-SQL query-analyser wrongly uses a query plan in the slow query which causes a complete table scan.

Adding a Count(*), the query hint with(FORCESCAN) or a combined index on the startdate,FK and status columns fixes the performance issue.

解决方案

The SQL Server cardinality estimator makes various modelling assumptions such as

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

There are 810,064 rows in the table.

You have the query

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

1,893 (0.23%) rows meet the fk = 4193 predicate, and of those two fail the status <> 'A' part so overall 1,891 match and need to be aggregated.

You also have two indexes neither of which cover the whole query.

For your fast query it uses an index on fk to directly find rows where fk = 4193 then needs to do 1,893 key lookups to find each row in the clustered index to check the status predicate and retrieve the startdate for aggregation.

When you remove the COUNT(*) from the SELECT list SQL Server no longer has to process every qualifying row. As a result it considers another option.

You have an index on startdate so it could start scanning that from the beginning, doing key lookups back to the base table and as soon as it finds the first matching row stop as it has found the MIN(startdate), Similarly the MAX can be found with another scan starting the other end of the index and working backwards.

SQL Server estimates that each of these scans will end up processing 590 rows before they hit upon one that matches the predicate. Giving 1,180 total lookups vs 1,893 so it chooses this plan.

The 590 figure is just table_size / estimated_number_of_rows_that_match. i.e. the cardinality estimator assumes that the matching rows will be evenly distributed throughout the table.

Unfortunately the 1,891 rows that meet the predicate are not randomly distributed with respect to startdate. In fact they are all condensed into a single 8,205 row segment towards the end of the index meaning that the scan to get to the MIN(startdate) ends up doing 801,859 key lookups before it can stop.

This can be reproduced below.

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

You could consider using query hints to force the plan to use the index on fk rather than startdate or add the suggested missing index highlighted in the execution plan on (fk,status) INCLUDE (startdate) to avoid this issue.

相关文章