在SQL查询中，WHERE子句的替代方法

Question

我们正在使用SQL Server 2012企业版通过存储过程运行以下查询：

declare @TopX int = 1000
declare @stores Table (Store varchar(5), LastDate datetime, LastId int, RangeEnd datetime)
insert into @stores 
  select * 
  from (select SourceStore, '2014-01-01' as i, null as ii, '2014-01-08' as iii 
        from StoreConfig.dbo.Version 
        group by SourceStore
       ) t 
  where (ABS(CAST((BINARY_CHECKSUM(*) * RAND()) as int)) % 100) < 50

IF OBJECT_ID('tempdb..#agreements') IS NOT NULL
DROP TABLE #agreements
IF OBJECT_ID('tempdb..#stores') IS NOT NULL
DROP TABLE #stores

select Store, 
       isnull(LastDate, '1899-01-01') StartDate, 
       isnull(LastId, -1) LastId, 
       isnull(RangeEnd, getdate()) RangeEnd
into #stores
from @stores
update #stores set StartDate = '2015-07-01', RangeEnd='2015-07-08'


-- THIS IS NOT FAST.. :(

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--grab the @TopX agreements from primary customers
--note: only grabbing the columns in our index to prevent RID lookups 
  --on every agreement before we sort and take a relatively tiny subset
select top 1000
       a.SourceStore
       ,a.AgreementId
       ,isnull(a.ModifiedDate, a.CreatedDate) as ModifiedDate
       ,ca.CustomerId
from #stores s
inner join StoreOps.POSREPL3Agreement.Agreement a on a.SourceStore = s.Store
inner join StoreOps.Customer.CustomerAgreement ca on ca.SourceStore = a.SourceStore 
                                                 and ca.AgreementId = a.AgreementId 
                                                 and ca.IsPrimary = 1
where ( (a.ModifiedDate between s.StartDate and s.RangeEnd) 
       or ( a.ModifiedDate is null 
        and a.CreatedDate between s.StartDate and s.RangeEnd)
      ) 
  and ( isnull(a.ModifiedDate, a.CreatedDate) > s.StartDate 
     or a.AgreementId > s.LastId
      )
order by isnull(a.ModifiedDate, a.CreatedDate), a.AgreementId

我们对于表[Customer].[CustomerAgreement]有以下索引：

CREATE NONCLUSTERED INDEX [IX_CustomerAgreement_SourceStore_AgreementId] 
ON [Customer].[CustomerAgreement]
([SourceStore] ASC, [AgreementId] ASC, [IsPrimary] ASC)
INCLUDE ([CustomerId]) 
ON [PRIMARY]
GO

这是[POSREPL3Agreement].[Agreement]表的索引：

CREATE NONCLUSTERED INDEX [IX_Agreement_SourceStore_ModifiedDate]  
ON [POSREPL3Agreement].[Agreement]
([SourceStore] ASC, [ModifiedDate] ASC, [CreatedDate] ASC)
INCLUDE ([AgreementId]) 
ON [PRIMARY]
GO

如果我们移除WHERE子句，索引会按预期工作，并且我们可以从两个表中看到1000条记录，但是当我们添加列出的WHERE子句[Customer].[CustomerAgreement]时，[CustomerAgreement]估计了所有记录，而不仅仅是1000条。 我们如何改进WHERE子句或索引，使得[Agreement]表与[CustomerAgreement]表对齐，以便[CustomerAgreement]下的估计行数不是所有记录？

表定义

CREATE TABLE [Customer].[CustomerAgreement](
    [CustomerAgreementId] [int] NOT NULL,
    [CustomerId] [int] NOT NULL,
    [AgreementId] [int] NOT NULL,
    [IsPrimary] [bit] NOT NULL,
    [Store] [varchar](5) NOT NULL,
    [SourceStore] [varchar](5) NOT NULL,
    [RowGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [Repl_ID] [tinyint] NOT NULL,
 CONSTRAINT [PK_Customer_CustomerAgreementID_SourceStore] PRIMARY KEY NONCLUSTERED 
(
    [CustomerAgreementId] ASC,
    [SourceStore] ASC,
    [Repl_ID] ASC,
    [RowGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [POSREPL3Agreement].[Agreement](
    [AgreementId] [int] NOT NULL,
    [QuoteId] [int] NULL,
    [AgreementNumber] [varchar](11) NOT NULL,
    [AgreementStatusId] [tinyint] NOT NULL,
    [AgreementPrinted] [bit] NOT NULL,
    [IsNewPOSCreated] [bit] NOT NULL,
    [LeaseFrequencyId] [tinyint] NOT NULL,
    [DeferredLateFeeAmount] [decimal](8, 2) NOT NULL,
    [InHomeVisitFeeAmount] [decimal](8, 2) NOT NULL,
    [IsASPTaxable] [bit] NOT NULL,
    [ServicePlusRate] [decimal](7, 5) NOT NULL,
    [ServicePlusFloor] [decimal](5, 2) NOT NULL,
    [TaxRatePercentage] [decimal](7, 5) NOT NULL,
    [IgnoreTaxRateChange] [bit] NOT NULL,
    [Balance] [decimal](8, 2) NOT NULL,
    [AmountPaidToDate] [decimal](10, 2) NOT NULL,
    [Deposit] [decimal](8, 2) NOT NULL,
    [DeliveryFee] [decimal](8, 2) NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [DueDay] [int] NOT NULL,
    [DueDayTypeId] [tinyint] NULL,
    [PaidThroughDate] [datetime] NOT NULL,
    [PayOutDate] [datetime] NOT NULL,
    [FinalDate] [datetime] NULL,
    [IsNSFOutstanding] [bit] NOT NULL,
    [LeadSourceId] [tinyint] NOT NULL,
    [AgreementTypeId] [tinyint] NOT NULL,
    [AcquisitionAgreementTypeId] [tinyint] NULL,
    [SameAsCashDuration] [int] NOT NULL,
    [SameAsCashDurationType] [int] NOT NULL,
    [MinimumPercentageOfCashPriceForFinalPayment] [decimal](3, 2) NOT NULL,
    [EarlyPayoutLeaseAmountRate] [decimal](3, 2) NOT NULL,
    [EarlyPayout] [decimal](10, 2) NULL,
    [FinalPaymentAdditionalFee] [decimal](8, 2) NOT NULL,
    [FinalPaymentProrateAmount] [decimal](10, 2) NOT NULL,
    [CreditedAssociateId] [int] NULL,
    [NonRenewalGracePeriod] [int] NOT NULL,
    [LastAgreementTransactionId] [int] NULL,
    [CanPayout] [bit] NOT NULL,
    [IsServicePlusIncludedInPayout] [bit] NOT NULL,
    [IsProrateEnabled] [bit] NOT NULL,
    [AgreementDocument] [varbinary](max) NULL,
    [CreatedDate] [datetime] NOT NULL,
    [CreatedBy] [int] NULL,
    [ModifiedDate] [datetime] NULL,
    [ModifiedBy] [int] NULL,
    [Store] [varchar](5) NOT NULL,
    [SourceStore] [varchar](5) NOT NULL,
    [RowGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [Repl_ID] [tinyint] NOT NULL,
    [ECommerceDeliveredDate] [datetime] NULL,
    [DDEStatusId] [tinyint] NULL,
    [DDEAmount] [decimal](8, 2) NULL,
    [OrderMethodTypeId] [int] NULL,
    [SemiMonthlyUpcharge] [decimal](8, 2) NULL,
    [DefaultNonRenewalFee] [decimal](8, 2) NULL,
    [DefaultInHomeVisitFee] [decimal](8, 2) NULL,
    [NonRenewalSemiMonthlyFee] [decimal](8, 2) NULL,
    [NonRenewalSemiMonthlyFeeGracePeriod] [int] NULL,
    [NonRenewalFeeTypeId] [int] NULL,
    [NonRenewalSemiMonthlyRate] [decimal](8, 4) NULL,
    [NonRenewalMonthlyRate] [decimal](8, 4) NULL,
    [NonRenewalWeeklyFeeGracePeriod] [int] NULL,
    [NonRenewalWeeklyRate] [decimal](8, 4) NULL,
    [NonRenewalWeeklyFee] [decimal](8, 2) NULL,
    [DefaultNSFFee] [decimal](8, 2) NULL,
    [WeeklyUpcharge] [decimal](8, 2) NULL,
    [IsInHomeFeeEnabled] [bit] NULL,
    [CanChargeInHomeFeeAndNonRenewalFeeInSamePeriod] [bit] NULL,
    [ExtensionBalance] [decimal](8, 2) NOT NULL,
 CONSTRAINT [PK_Agreement_AgreementID_SourceStore] PRIMARY KEY NONCLUSTERED 
(
    [AgreementId] ASC,
    [SourceStore] ASC,
    [Repl_ID] ASC,
    [RowGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

执行计划

点击这里下载Showplan XML（pastebin）

- NismoGTR05

Answer 1

如果我们去掉WHERE子句，索引的工作就如预期一样，我们可以从两个表中看到1000条记录，但是当我们添加了列出的WHERE子句[Customer].[CustomerAgreement]时，估计所有记录而不是1000条。 简单来说，如果在WHERE子句中没有对行进行任何过滤，查询优化器会估计只需要从每个表中读取1000行即可产生所需的1000行结果。 由于您没有提供此查询的执行计划，所以我无法多说。在这种情况下，很可能您的查询也省略了ORDER BY子句，否则可能需要进行排序，这通常需要读取其子树中的所有行。 有了WHERE子句，优化器预计在第1000行可返回给客户端之前需要读取更多的行（由于预期的过滤效果）。估计读取的行数与实际行数之间的差异是由于从可用统计数据中估计复杂谓词的选择性的问题。即使假设统计数据代表了数据，这个问题仍然存在。从根本上说：这太难了，优化器错误地估计了基数。

1. 索引计算列解决方案

你可能会发现以下变化是值得的：

1. 对 #stores 临时表创建索引:

   CREATE UNIQUE CLUSTERED INDEX index_name 
   ON #stores 
   (
       Store,
       StartDate
   );
   

2. 向 Agreements 表添加一个计算列。这不会占用存储空间，并且是一个非常快速的仅元数据操作:

   ALTER TABLE POSREPL3Agreement.Agreement
   ADD ComputedModifiedDate
   AS ISNULL(ModifiedDate, CreatedDate);
   

3. 创建（或修改现有的）索引以使用该计算列。这将满足 ORDER BY 子句。

   CREATE INDEX index_name 
   ON POSREPL3Agreement.Agreement
   (
       ComputedModifiedDate,
       AgreementId
   )
   INCLUDE (SourceStore);
   

4. 简化查询以直接引用计算列:

   SELECT TOP (1000)
       A.SourceStore,
       A.AgreementId,
       ModifiedDate = A.ComputedModifiedDate
   FROM #stores AS S
   JOIN POSREPL3Agreement.Agreement AS A
       ON A.SourceStore = S.Store
   JOIN Customer.CustomerAgreement AS CA
       ON CA.SourceStore = A.SourceStore
       AND CA.AgreementId = A.AgreementId
   WHERE
       CA.IsPrimary = 1
       AND A.ComputedModifiedDate BETWEEN S.StartDate AND S.RangeEnd
       AND 
       ( 
           A.ComputedModifiedDate > S.StartDate
           OR A.AgreementId > S.LastId
       )
   ORDER BY
       A.ComputedModifiedDate, 
       A.AgreementId;
   

如果你无法按照所示重新编写查询，那么出于技术原因，计算列索引将需要包括额外的两列。

CREATE INDEX index_name 
ON POSREPL3Agreement.Agreement
(
    ComputedModifiedDate,
    AgreementId
)
INCLUDE 
(
    SourceStore,
    CreatedDate,
    ModifiedDate
)
WITH DROP_EXISTING;

预期的执行计划仍然会显示不准确的估计（因为优化器对于扫描有序计算列索引的速度过于乐观），但是（Top N 和 Distinct）排序被消除，应该仍然表现更好。

2. 全面的索引视图解决方案 如果您无法添加计算列和索引，您可以考虑使用索引视图代替：

CREATE VIEW dbo.ViewName
WITH SCHEMABINDING
AS
SELECT
    A.SourceStore,
    A.AgreementId,
    ComputedModifiedDate = ISNULL(A.ModifiedDate, A.CreatedDate)
FROM POSREPL3Agreement.Agreement AS A
JOIN Customer.CustomerAgreement AS CA
    ON CA.SourceStore = A.SourceStore
    AND CA.AgreementId = A.AgreementId
WHERE
    CA.IsPrimary = 1;
GO
CREATE UNIQUE CLUSTERED INDEX index_name
ON dbo.ViewName
(
    ComputedModifiedDate,
    AgreementId,
    SourceStore
);

查询变成了这样：

SELECT TOP (1000)
    VN.SourceStore,
    VN.AgreementId,
    ModifiedDate = VN.ComputedModifiedDate 
FROM #stores AS S
JOIN dbo.ViewName AS VN
    WITH (NOEXPAND)
    ON VN.SourceStore = S.Store
WHERE
    VN.ComputedModifiedDate BETWEEN S.StartDate AND S.RangeEnd
    AND 
    ( 
        VN.ComputedModifiedDate > S.StartDate
        OR VN.AgreementId > S.LastId
    )
ORDER BY
    VN.ComputedModifiedDate, 
    VN.AgreementId;

3. 更简单的索引视图解决方案

也可以通过索引视图更直接地反映计算列的解决方案，尽管这个想法并没有消除连接操作：

CREATE VIEW dbo.ViewName
WITH SCHEMABINDING
AS
SELECT
    A.SourceStore,
    A.AgreementId,
    ComputedModifiedDate = ISNULL(A.ModifiedDate, A.CreatedDate)
FROM POSREPL3Agreement.Agreement AS A;
GO
CREATE UNIQUE CLUSTERED INDEX index_name
ON dbo.ViewName
(
    ComputedModifiedDate,
    AgreementId,
    SourceStore
);

这次的查询变成了：

SELECT TOP (1000)
    VN.SourceStore,
    VN.AgreementId,
    ModifiedDate = VN.ComputedModifiedDate
FROM #stores AS S
JOIN dbo.ViewName AS VN
    WITH (NOEXPAND)
    ON VN.SourceStore = S.Store
JOIN Customer.CustomerAgreement AS CA
    ON CA.SourceStore = VN.SourceStore
    AND CA.AgreementId = VN.AgreementId
WHERE
    CA.IsPrimary = 1
    AND VN.ComputedModifiedDate BETWEEN S.StartDate AND S.RangeEnd
    AND 
    ( 
        VN.ComputedModifiedDate > S.StartDate
        OR VN.AgreementId > S.LastId
    )
ORDER BY
    VN.ComputedModifiedDate, 
    VN.AgreementId;

我不得不猜测在视图中哪些列是唯一的。如果你实施了其中一种解决方案，请考虑你更广泛的工作负载，并根据需要从基本表中添加唯一列，以使视图的聚集索引唯一。这里的[RowGUID]列看起来很适合这个目的。

---

你应该仔细测试以评估索引视图对基表数据变化的影响，以及所需的存储空间量。 使用任何这些解决方案时，如果临时表包含非常早期的日期范围，或者存在一个没有最近创建或修改日期的商店，性能可能不如预期。你应该进行测试，并观察它在真实数据和需求下的表现。 如果将隔离级别设置为“READ UNCOMMITTED”只是出于迫切提高性能的尝试，请删除该语句。请参阅我的文章了解详细信息。 此外，我注意到两个表目前都是堆。你应该知道，大多数表都受益于具有聚集索引，至少是出于空间管理的原因。如果堆经历了删除操作，你可能需要定期重建它们，以回收自动释放的空白页。 顺便说一下，在你的@stores查询中，RAND并不会产生你可能认为的效果。它会为每一行生成相同的值（一个运行时常量）。

- Paul White