sql-server - 为什么 SQL Server 查询优化器有时会忽略明显的聚簇主键？

Question

我一直在为这个问题挠头。

我在 id 作为集群整数主键 的表上运行简单的 select count(id) 并且 SQL 优化器完全忽略了查询执行计划中的主键，有利于日期字段上的索引.... ???

实际表:

CREATE TABLE [dbo].[msgr](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [dt] [datetime2](3) NOT NULL CONSTRAINT [DF_msgr_dt]  DEFAULT (sysdatetime()),
    [uid] [int] NOT NULL,
    [msg] [varchar](7000) NOT NULL CONSTRAINT [DF_msgr_msg]  DEFAULT (''),
    [type] [tinyint] NOT NULL,
    [cid] [int] NOT NULL CONSTRAINT [DF_msgr_cid]  DEFAULT ((0)),
    [via] [tinyint] NOT NULL,
    [msg_id] [bigint] NOT NULL,
 CONSTRAINT [PK_msgr] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

请问这是什么原因？

Answer 1

1) 在我看来，这里的关键点是对于聚簇表(具有聚簇索引的表 = 主要数据结构 = 存储表数据的数据结构 = 聚簇索引是表本身)每个非聚集索引还包括聚集索引的键。这意味着

CREATE [UNIQUE] NONCLUSTERED INDEX bla 
ON [dbo].[msgr] (uid) 

与

基本相同

CREATE [UNIQUE] NONCLUSTERED INDEX bla 
ON [dbo].[msgr] (uid) 
INCLUDE (id) -- id = key of clustered index

因此，对于此类表，叶页上非聚集索引的每条记录还包括聚集索引的键。这样，在每个非聚集索引和每个叶记录中，SQL Server 还存储某种指向主数据结构的指针。

2) 这意味着 SELECT COUNT(id) FROM dbo.msgr 可以使用 CI 执行，但也可以使用 NCI，因为两个索引都包含 id(集群的键索引)列。

作为本主题中的次要说明，因为 IDENTITY 属性(对于 id 列)意味着强制列(NOT NULL)，COUNT(id) 与 COUNT(*) 相同。此外，这意味着 COUNT(msg_id)(也是必需的/NOT NULL)列与 COUNT(*) 相同。因此，SELECT COUNT(msg_id) FROM dbo.msgr 的执行计划很可能会使用相同的 NCI(例如 bla)。

3) 非聚集索引的大小比聚集索引小。这也意味着更少的 IO => 从性能的角度来看，使用 NCI 比使用 CI 更好。

我会做以下简单测试:

SET STATISTICS IO ON;
GO

SELECT COUNT(id)
FROM dbo.[dbo].[msgr] WITH(INDEX=[bla]) -- It forces usage of NCI
GO

SELECT COUNT(id)
FROM dbo.[dbo].[msgr] WITH(INDEX=[PK_msgr]) -- It forces usage of CI
GO

SET STATISTICS IO OFF;
GO

如果 msgr 表中有大量数据，那么 STATISTICS IO 将显示不同的 LIO(逻辑 IO)，与 用于 NCI 查询的 LIO 更少。