主页 / dba / 问题 / 171476
Accepted
TheGameiswar
Asked: 2017-04-20 19:49:31 +0800 CST

查询显示排序成本，即使需要的索引可用

  • 772

这个问题来自 SO，如果将其标记为移至 DBA.SE ..但我可能看不到这种情况发生，由于 upvotes ..所以在这里发布

以下是测试数据： 

--Main Table

 CREATE TABLE [dbo].[LogTable]
    (
      [LogID] [int] NOT NULL
                    IDENTITY(1, 1) ,
      [DateSent] [datetime] NULL,
    )
 ON [PRIMARY]
GO
 ALTER TABLE [dbo].[LogTable] ADD CONSTRAINT [PK_LogTable] PRIMARY KEY CLUSTERED  ([LogID]) ON [PRIMARY]
GO
 CREATE NONCLUSTERED INDEX [IX_LogTable_DateSent] ON [dbo].[LogTable] ([DateSent] DESC) ON [PRIMARY]
GO
 CREATE NONCLUSTERED INDEX [IX_LogTable_DateSent_LogID] ON [dbo].[LogTable] ([DateSent] DESC) INCLUDE ([LogID]) ON [PRIMARY]
GO


--Cross table

 CREATE TABLE [dbo].[LogTable_Cross]
    (
      [LogID] [int] NOT NULL ,
      [UserID] [int] NOT NULL
    )
 ON [PRIMARY]
GO
 ALTER TABLE [dbo].[LogTable_Cross] WITH NOCHECK ADD CONSTRAINT [FK_LogTable_Cross_LogTable] FOREIGN KEY ([LogID]) REFERENCES [dbo].[LogTable] ([LogID])
GO
 CREATE NONCLUSTERED INDEX [IX_LogTable_Cross_UserID_LogID]
 ON [dbo].[LogTable_Cross] ([UserID])
 INCLUDE ([LogID])
GO


-- Script to populate them
 INSERT INTO [LogTable]
        SELECT TOP 100000
                DATEADD(day, ( ABS(CHECKSUM(NEWID())) % 65530 ), 0)
        FROM    sys.sysobjects
                CROSS JOIN sys.all_columns


 INSERT INTO [LogTable_Cross]
        SELECT  [LogID] ,
                1
        FROM    [LogTable]
        ORDER BY NEWID()

 INSERT INTO [LogTable_Cross]
        SELECT  [LogID] ,
                2
        FROM    [LogTable]
        ORDER BY NEWID()

 INSERT INTO [LogTable_Cross]
        SELECT  [LogID] ,
                3
        FROM    [LogTable]
        ORDER BY NEWID()


GO

当我们使用下面的简单查询时，

SELECT  DI.LogID              
FROM    LogTable DI              
        INNER JOIN LogTable_Cross DP ON DP.LogID = DI.LogID  
        WHERE  DP.UserID = 1  
ORDER BY DateSent DESC

查询显示排序

在此处输入图像描述

据我了解，应该避免排序成本，因为我们有所需的索引

 CREATE NONCLUSTERED INDEX [IX_LogTable_DateSent] ON [dbo].[LogTable] ([DateSent] DESC) ON [PRIMARY]

并且深入研究计划显示使用了相同的索引..

我的问题是：
1.为什么排序成本仍然存在
2.究竟是什么ordered property is true意思。

我所做的一些观察/工作：
1.Ordered 属性设置为 false

所以我重写了如下查询

   SELECT  DI.LogID              
    FROM    LogTable DI              
            INNER JOIN LogTable_Cross DP ON DP.LogID = DI.LogID  
            WHERE  DP.UserID = 1  and di.datesent is not null
    ORDER BY DateSent DESC

像上面那样重写，使有序属性为真，但仍然存在排序

最接近的是，我可以找到 Paul White 的这篇文章：两个索引提示的故事，在这篇文章中，下面的点阐明了为什么会发生这种情况

对于非常大的表，优化器可能会计算出 IAM 驱动的扫描可能会比额外排序所消耗的时间节省更多时间，并且会选择以无序扫描 + 排序为特色的计划。这是一种启发式优化：优化器对索引的实际碎片级别一无所知

但是这个查询需要排序，它不应该满足 IAM 扫描的条件

让我知道，如果您需要更多详细信息

sql-server performance

1 个回答

  1. Best Answer

    在查询计划中，ordered 属性设置为 true 意味着未完成 IAM 驱动的扫描。根据索引定义按逻辑顺序读取数据。

    散列连接不保留顺序。您的第一个查询有一个哈希连接，因此最后SORT需要显式连接。避免在查询中排序的一种方法是使用LogTable作为外部表的嵌套循环连接。在我的机器上，我可以通过各种提示来完成此操作：

    SELECT  DI.LogID              
FROM LogTable DI              
        INNER JOIN LogTable_Cross DP ON DP.LogID = DI.LogID  
        WHERE  DP.UserID = 1  
ORDER BY DateSent DESC
OPTION (MAXDOP 1, LOOP JOIN, FORCE ORDER, NO_PERFORMANCE_SPOOL);

    错误的查询计划

    LogTable_Cross这是一个非常低效的查询计划，因为没有在表上定义正确的索引。针对该表进行了搜索，但只有一个 UserId 列：

    不好的搜索

    过滤LogID是在连接本身中完成的。我可以创建一个索引来更好地支持我正在寻找的查询计划：

    CREATE INDEX IX_LogTable_Cross ON LogTable_Cross (LogID, UserID);

    该索引的创建并不能保证没有排序的查询计划。SQL Server 可能估计带有排序的计划具有较低的成本。但是，如果我消除除 the 之外的所有提示，LOOP JOIN那么我将获得一个相当有效的查询计划，而无需排序：

    好的查询计划

    • 5

相关问题