為什麼此查詢中沒有使用主（集群）鍵？

我有一個 SQL Server 2008 R2 表，其架構結構如下所示：

CREATE TABLE [dbo].[CDSIM_BE]
(
   [ID] [bigint] NOT NULL,
   [EquipmentID] [varchar](50) NOT NULL,
   [SerialNumber] [varchar](50) NULL,
   [PyrID] [varchar](50) NULL,
   [MeasMode] [varchar](50) NULL,
   [ReadTime] [datetime] NOT NULL,
   [SubID] [varchar](15) NULL,
   [ProbePosition] [float] NULL,
   [DataPoint] [int] NULL,

   CONSTRAINT [PK_CDSIM_BE] 
   PRIMARY KEY CLUSTERED ([ID] ASC, [EquipmentID] ASC, [ReadTime] ASC)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
              IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
              ALLOW_PAGE_LOCKS = ON) ON [MonthlyArchiveScheme9]([ReadTime])
) ON [MonthlyArchiveScheme9]([ReadTime])

CREATE NONCLUSTERED INDEX [idx_CDSIM_BE__SubID_ProbePosition] 
ON [dbo].[CDSIM_BE] ([SubID] ASC, [ProbePosition] ASC)
INCLUDE ([EquipmentID], [ReadTime], [BECorr]) 
   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
         SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
         ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MonthlyArchiveScheme9]([ReadTime])

CREATE NONCLUSTERED INDEX [IX_CDSIM_BE_ProbePosition] 
ON [dbo].[CDSIM_BE] ([ProbePosition] ASC)
INCLUDE ([SerialNumber], [SubID]) 
   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
         SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
         ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MonthlyArchiveScheme9]([ReadTime])

CREATE NONCLUSTERED INDEX [IX_CSDIM_Readtime] 
ON [dbo].[CDSIM_BE]([ReadTime] ASC)
INCLUDE ([EquipmentID]) 
   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
         SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
         ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MonthlyArchiveScheme9]([ReadTime])

我正在執行這個簡單的查詢：

Select Max(Id)
From dbo.CDSIM_BE

表中有 ~2.5B 行。

查詢計劃顯示正在對IX_CdSIM_BE_ProbePosition索引執行索引掃描。我想知道為什麼 SQL Server 根本不會使用聚集（和主）索引並立即轉到表中的最後一行並檢索 Id 值，因為它必須是最大值。

聚集索引已分區，ReadTime因此無法使用您描述的 PK。它需要找到Max(Id)每個分區，然後找到其中的最大值。但是*，*可以重寫查詢以獲得這樣的計劃。

使用基於此處文章的範例，可能的重寫可能是

SELECT MAX(ID) AS ID
FROM   sys.partitions AS P
      CROSS APPLY (SELECT MAX(ID) AS ID
                   FROM   [dbo].[CDSIM_BE]
                   WHERE  $PARTITION.MonthlyArchiveFunction9(ReadTime) 
                                                   = P.partition_number) AS A
WHERE  P.object_id = OBJECT_ID('dbo.CDSIM_BE')
      AND P.index_id <= 1; 

依次處理每個分區。

請注意，該計劃仍然有一個掃描（使用搜尋謂詞來選擇分區），但這不是對分區的完整掃描。

掃描按索引順序，方向為“BACKWARD”。迭代器可以在TOP收到第一個行後停止從掃描中請求行。

引用自：https://dba.stackexchange.com/questions/104610