SUM/GROUP 性能和主键 [英] SUM/GROUP performance and the Primary Key

问题描述

我有一个表 myTable 带有 myGuid(唯一标识符)、myValues(浮点数)、myGroup(整数) 和其他一些现在不重要的字段.我想做一些简单的事情:

SELECT SUM(myValues)从我的表在哪里 myGuid (SELECT * FROM ##test)按我的组分组

##test 只是一个带有单个字段的临时表 (guid_filter)包含一堆唯一标识符.

现在奇怪的是:

• 当我使用 myGuid 作为主键创建 myTable 时(这似乎是就像显而易见的事情一样)，查询很慢(8-12s</EDIT>).

• 当我用 myAutoInc 创建 myTable 时，一个整数自动递增字段，作为主键，查询速度很快(~2s)，即使WHERE 子句仍按 myGuid 过滤.(myGuid 只有一个正常"在这种情况下是非聚集索引.)

这有什么合乎逻辑的解释吗?我的(天真的)假设是第一个选项更快，因为 SQL Server 可以使用 guid查找 myValues 而不必通过 guid -> myAutoInc ->我的价值观.所以，结果让我非常惊讶.

这是 SHOWPLAN_TEXT 输出.缓慢的场景(XML 查询计划):(更新，感谢 Remus 的注意myGuid 上有一个不必要的额外非聚集索引)

|--计算标量(DEFINE:([Expr1007]=CASE WHEN [Expr1015]=(0) THEN NULL ELSE [Expr1016] END))|--Stream Aggregate(GROUP BY:([myDB].[dbo].[myTable].[myGroup]) DEFINE:([Expr1015]=COUNT_BIG([myDB].[dbo].[myTable].[myValues]), [Expr1016]=SUM([myDB].[dbo].[myTable].[myValues])))|--Sort(DISTINCT ORDER BY:([myDB].[dbo].[myTable].[myGroup] ASC, [myDB].[dbo].[myTable].[myGuid] ASC))|--嵌套循环(内部连接，外部引用:([tempdb].[dbo].[##test].[guid_filter]，[Expr1014])优化无序预取)|--表扫描(对象:([tempdb].[dbo].[##test]))|--聚簇索引查找(OBJECT:([myDB].[dbo].[myTable].[PK__myTable__2334397B]), SEEK:([myDB].[dbo].[myTable].[myGuid]=[tempdb].[dbo].[##test].[guid_filter]) ORDERED FORWARD)表'myTable'.扫描计数 0，逻辑读 38046，物理读 1，预读 6914，lob 逻辑读 0，lob 物理读 0，lob 预读 0.表'##test'.扫描计数 1，逻辑读取 23，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0.

快速方案(XML 查询计划):

|--计算标量(DEFINE:([Expr1007]=CASE WHEN [globalagg1009]=(0) THEN NULL ELSE [globalagg1011] END))|--Stream Aggregate(GROUP BY:([myDB].[dbo].[myTable].[myGroup]) DEFINE:([globalagg1009]=SUM([partialagg1008]), [globalagg1011]=SUM([partialagg1010])))|--Parallelism(Gather Streams, ORDER BY:([myDB].[dbo].[myTable].[myGroup] ASC))|--Stream Aggregate(GROUP BY:([myDB].[dbo].[myTable].[myGroup]) DEFINE:([partialagg1008]=COUNT_BIG([myDB].[dbo].[myTable].[myValues]), [partialagg1010]=SUM([myDB].[dbo].[myTable].[myValues])))|--Sort(DISTINCT ORDER BY:([myDB].[dbo].[myTable].[myGroup] ASC, [myDB].[dbo].[myTable].[myAutoInc] ASC))|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([myDB].[dbo].[myTable].[myGroup], [myDB].[dbo].[myTable].[myAutoInc]))|--嵌套循环(内部连接，外部引用:([myDB].[dbo].[myTable].[myAutoInc]，[Expr1017])优化无序预取)|--嵌套循环(内部连接，外部引用:([tempdb].[dbo].[##test].[guid_filter]，[Expr1016])优化无序预取)||--表扫描(对象:([tempdb].[dbo].[##test]))||--Index Seek(OBJECT:([myDB].[dbo].[myTable].[myGuid]), SEEK:([myDB].[dbo].[myTable].[myGuid]=[tempdb].[dbo].[##test].[guid_filter]) ORDERED FORWARD)|--聚集索引查找(OBJECT:([myDB].[dbo].[myTable].[PK__myTable__2334397B]), SEEK:([myDB].[dbo].[myTable].[myAutoInc]=[myDB].[dbo].[myTable].[myAutoInc]) 向前查找)表'myTable'.扫描计数0，逻辑读66988，物理读48，预读2515，lob逻辑读0，lob物理读0，lob预读0.表'##test'.扫描计数 5，逻辑读取 23，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0.表工作台".扫描计数 0，逻辑读 0，物理读 0，预读 0，lob 逻辑读 0，lob 物理读 0，lob 预读 0.

解决方案

查看慢"情况下的计划，它表明查询在索引 [myDB].[dbo].[myTable].[myGuid] 后跟对 [myDB].[dbo].[myTable].[PK__myTable__2334397B] 的聚集索引查找.仅当您在 myTable(myGuid) 上创建了非聚集索引并且还将 myTable(myGuid) 声明为聚集索引键时，这才有意义(从典型的PRIMARY KEY"声明自动生成的名称命名约定来看，似乎如此聚集索引对象PK_...").

除此之外，这些计划非常相似，而且都非常糟糕，因为它们都包含 SORT.在第一种情况下，autoInc 列的宽度与涉及非聚集索引的潜在更大宽度的 GUID 的差异可以解释这种差异，但我怀疑这就是全部.>

请重做测试，确保您在 myGuid 上有聚集键，并且在同一键上没有非聚集索引.该计划应仅包括对 myTable 的一次搜索，使用聚集索引来准确比较您想要比较的案例.

另外，显然，请确保您比较相同的 ##test 内容，并且缓冲池缓存在两种情况下都以相同的方式预热.在每次测试之前运行 DBCC FREESYSTEMCACHE('All') 然后运行查询至少 5 次，忽略第一次运行(它将是预热缓冲池的运行).

此外，正如 Arthur 已经指出的那样，如果 ##test 内容足够大，对 ##test(即聚集键)的顺序保证可以加快速度，因为嵌套循环可以用合并连接替换.如果##temp 只有几行，那么嵌套循环更好，顺序没有区别.

I have a table myTable with myGuid (uniqueidentifier), myValues (float), myGroup (integer) and a bunch of other fields which are not important right now. I want to do something as simple as:

SELECT SUM(myValues)
  FROM myTable
 WHERE myGuid IN (SELECT * FROM ##test)
 GROUP BY myGroup

##test is just a temporary table with a single field (guid_filter) containing a bunch of uniqueidentifiers.

Now here's the strange thing:

• When I create myTable with myGuid as the Primary Key (which seems like the obvious thing to do), the query is slow (<EDIT>8-12s</EDIT>).

• When I create myTable with myAutoInc, an integer auto-increment field, as the Primary Key, the query is fast (~2s), even though the WHERE clause still filters by myGuid. (myGuid has just a "normal" non-clustered index in this scenario.)

Is there any logical explanation to this? My (naive) assumption was that the first option is faster, since SQL Server can use the guid to look up myValues rather than having to go through guid -> myAutoInc -> myValues. So, the result was very surprising for me.

Here's the SHOWPLAN_TEXT output. Slow scenario (XML query plan): (EDIT: Updated, thanks to Remus for noticing that there was an unnecessary additional non-clustered index on myGuid)

|--Compute Scalar(DEFINE:([Expr1007]=CASE WHEN [Expr1015]=(0) THEN NULL ELSE [Expr1016] END))
     |--Stream Aggregate(GROUP BY:([myDB].[dbo].[myTable].[myGroup]) DEFINE:([Expr1015]=COUNT_BIG([myDB].[dbo].[myTable].[myValues]), [Expr1016]=SUM([myDB].[dbo].[myTable].[myValues])))
          |--Sort(DISTINCT ORDER BY:([myDB].[dbo].[myTable].[myGroup] ASC, [myDB].[dbo].[myTable].[myGuid] ASC))
               |--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[##test].[guid_filter], [Expr1014]) OPTIMIZED WITH UNORDERED PREFETCH)
                    |--Table Scan(OBJECT:([tempdb].[dbo].[##test]))
                    |--Clustered Index Seek(OBJECT:([myDB].[dbo].[myTable].[PK__myTable__2334397B]), SEEK:([myDB].[dbo].[myTable].[myGuid]=[tempdb].[dbo].[##test].[guid_filter]) ORDERED FORWARD)

Table 'myTable'. Scan count 0, logical reads 38046, physical reads 1, read-ahead reads 6914, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '##test'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Fast scenario (XML query plan):

|--Compute Scalar(DEFINE:([Expr1007]=CASE WHEN [globalagg1009]=(0) THEN NULL ELSE [globalagg1011] END))
     |--Stream Aggregate(GROUP BY:([myDB].[dbo].[myTable].[myGroup]) DEFINE:([globalagg1009]=SUM([partialagg1008]), [globalagg1011]=SUM([partialagg1010])))
          |--Parallelism(Gather Streams, ORDER BY:([myDB].[dbo].[myTable].[myGroup] ASC))
               |--Stream Aggregate(GROUP BY:([myDB].[dbo].[myTable].[myGroup]) DEFINE:([partialagg1008]=COUNT_BIG([myDB].[dbo].[myTable].[myValues]), [partialagg1010]=SUM([myDB].[dbo].[myTable].[myValues])))
                    |--Sort(DISTINCT ORDER BY:([myDB].[dbo].[myTable].[myGroup] ASC, [myDB].[dbo].[myTable].[myAutoInc] ASC))
                         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([myDB].[dbo].[myTable].[myGroup], [myDB].[dbo].[myTable].[myAutoInc]))
                              |--Nested Loops(Inner Join, OUTER REFERENCES:([myDB].[dbo].[myTable].[myAutoInc], [Expr1017]) OPTIMIZED WITH UNORDERED PREFETCH)
                                   |--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[##test].[guid_filter], [Expr1016]) OPTIMIZED WITH UNORDERED PREFETCH)
                                   |    |--Table Scan(OBJECT:([tempdb].[dbo].[##test]))
                                   |    |--Index Seek(OBJECT:([myDB].[dbo].[myTable].[myGuid]), SEEK:([myDB].[dbo].[myTable].[myGuid]=[tempdb].[dbo].[##test].[guid_filter]) ORDERED FORWARD)
                                   |--Clustered Index Seek(OBJECT:([myDB].[dbo].[myTable].[PK__myTable__2334397B]), SEEK:([myDB].[dbo].[myTable].[myAutoInc]=[myDB].[dbo].[myTable].[myAutoInc]) LOOKUP ORDERED FORWARD)

Table 'myTable'. Scan count 0, logical reads 66988, physical reads 48, read-ahead reads 2515, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '##test'. Scan count 5, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

解决方案

Looking at the plan in the 'slow' case it shows that the query does a seek on the index [myDB].[dbo].[myTable].[myGuid] followed by a clustered index seek on [myDB].[dbo].[myTable].[PK__myTable__2334397B]. This only makes sense if you have created both a non-clustered index on myTable(myGuid) and also declared myTable(myGuid) as clustered index key (it appears so, judging from the typical 'PRIMARY KEY' declaration auto-generated name naming convention of the clustered index object 'PK_...').

Other than that, the plans are very similar and they're both quite bad in that they include a SORT. The difference in width of the autoInc column vs. the GUID involved in the potential larger width of the non-clustered index in the first case may explain the difference, but I doubt is the full story.

Please redo the test making sure that you have a clustered key on myGuid and that you do NOT have also a non-clustered index on the same key. The plan should include only one single seek on myTable, using the clustered index, to compare exactly the cases you wanted to compare.

Also, obviously, make sure you compare the same ##test content and the buffer pool cache is warmed up in both case identically. Run DBCC FREESYSTEMCACHE('All') before each test then run the query at least 5 times, negleting the first run (it will be the run that warms up the buffer pool).

Also, as Arthur already noted, having an order guarantee on ##test (ie. a clustered key) could speed up things as the nested loops can be replaced with a merge join, if ##test content is large enough. If the ##temp has only few rows, then the nested loop is better and order makes no difference.

这篇关于SUM/GROUP 性能和主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！