和/组性能和主键
我有一个表myTable，它有myGuid (unique标识符)、myValues (浮点数)、myGroup (整数)和一些现在不重要的其他字段。我想做一些简单的事情，比如：
SELECT SUM(myValues)
FROM myTable
WHERE myGuid IN (SELECT * FROM ##test)
GROUP BY myGroup##test只是一个临时表，它只包含一个字段(guid_filter)，其中包含一组containing。
现在奇怪的是：
- 当我以myTable作为主键(这似乎是显而易见的事情)创建myGuid时，查询速度很慢(8-12秒)。
- 当我使用myTable作为主键创建myAutoInc (一个整数自动增量字段)时，查询速度很快(~2s)，尽管WHERE子句仍然由myGuid筛选。(在这种情况下，myGuid只有一个“正常”的非聚集索引。)
这有什么合乎逻辑的解释吗？我(天真)的假设是，第一个选项更快，因为Server可以使用guid来查找myValues，而不必通过guid -> myAutoInc -> myValues。所以，结果对我来说是非常令人惊讶的。
这是SHOWPLAN_TEXT输出。慢场景(XML查询计划)：(编辑:更新，感谢Remus注意到在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.快速场景(XML查询计划)：
|--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.回答 2
Stack Overflow用户
发布于 2009-11-09 20:45:37
从“慢速”的情况来看，它显示了查询对索引[myDB].[dbo].[myTable].[myGuid]进行查找，然后在[myDB].[dbo].[myTable].[PK__myTable__2334397B]上进行聚集索引查找。这只有在您已经在myTable(myGuid)上创建了一个非聚集索引并将myTable(myGuid)声明为聚集索引键(从聚集索引对象“PK_.”的典型“主键”声明自动生成的名称命名约定中判断)时才有意义。
除此之外，他们的计划是非常相似的，他们都很糟糕，因为他们包括了一种。在第一种情况下，autoInc列的宽度与非聚集索引的潜在更大宽度所涉及的GUID之间的差异可能解释了这种差异，但我怀疑这是完全不同的。
请重做测试，确保您在myGuid上有一个聚集键，并且在同一键上没有一个非聚集索引。该计划应该只包含一个在myTable上的搜索，使用聚集索引来准确地比较您想要比较的情况。
此外，显然，确保您比较相同的##test内容和缓冲池缓存在这两种情况下是相同的。在每次测试之前运行DBCC FREESYSTEMCACHE('All')，然后至少运行5次查询，取消第一次运行(运行将使缓冲池升温)。
此外，正如亚瑟已经指出的，在##test上有订单担保(即。(集群键)可以加快速度，因为如果##test内容足够大，可以用合并连接替换嵌套循环。如果##temp只有几行，那么嵌套的循环更好，顺序没有什么不同。
Stack Overflow用户
发布于 2009-11-09 19:54:41
GUID是聚集索引的糟糕选择，因为它太大了。使用整数字段允许数据库将更多的信息打包到“页面”中，因此任何给定查询都需要从磁盘中获取更少的页面。
还请注意，群集键存储在每个非聚集索引中(因为这是用来定位数据的)，这使问题更加复杂。
https://stackoverflow.com/questions/1703303
复制相似问题
腾讯云开发者
