SQL为什么是SELECT COUNT(*),MIN(col),MAX(col)快于SELECT MIN(c

慢查询

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 ms,已用时间= 1955 ms.

快速查询

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

表’表’.扫描计数1,逻辑读取5803,lob预读读取0.

SQL Server执行时间：CPU时间= 0 ms,已用时间= 9 ms.

题

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

更新
基于作为评论的问题的一点更新：

执行顺序或重复执行不会改变性能.
没有使用额外的参数,(测试)数据库在执行过程中没有做任何其他的事情.

慢查询

|--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,[Expr1009]) WITH ORDERED PREFETCH)
                  |--Index Scan(OBJECT:([DBTest].[dbo].[table].[startdate]),ORDERED BACKWARD)
                  |--Clustered Index Seek(OBJECT:([DBTest].[dbo].[table].[PK_table]),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,[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]),WHERE:([DBTest].[dbo].[table].[status]<'A' OR [DBTest].[dbo].[table].[status]>'A') LOOKUP ORDERED FORWARD)

回答

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

添加一个Count(*),使用(FORCESCAN)的查询提示或startdate,FK和status列上的组合索引修复了性能问题.

解决方法

• 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.

07000

表中有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谓词,并且那些行失败状态为<> “A”部分总共1,891匹配,需要聚合.

您也有两个索引,两个索引都不包括整个查询.

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

从SELECT列表中删除COUNT(*)时,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)上执行计划中突出显示的建议的缺失索引,以避免此问题.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!