表结构如下:
CREATE TABLE [dbo].[Categories]
(
[Id] INT NOT NULL IDENTITY CONSTRAINT [PK_Categories] PRIMARY KEY,
[Key] CHAR(10) CONSTRAINT [UK_Categories_Key] UNIQUE,
[Caption] NVARCHAR(500) NOT NULL,
[Description] NVARCHAR(4000) NULL
)
GO
CREATE TABLE [dbo].[Posts]
(
[Id] INT NOT NULL IDENTITY CONSTRAINT [PK_Posts] PRIMARY KEY,
[CategoryId] INT NOT NULL CONSTRAINT [FK_Posts_Category] FOREIGN KEY REFERENCES [dbo].[Categories] ([Id]),
[Key] CHAR(10) CONSTRAINT [UK_Post_Key] UNIQUE,
[Text] NVARCHAR(4000) NULL,
[SummaryText] AS
CASE WHEN LEN([Text]) <= 400
THEN CAST([Text] AS NVARCHAR(400))
ELSE CAST(SUBSTRING([Text], 0, 399) + NCHAR(8230) AS NVARCHAR(400)) --First 399 characters and ellipsis
END
PERSISTED
)
GO
CREATE TABLE [dbo].[Scores] (
[Id] INT NOT NULL IDENTITY CONSTRAINT [PK_Scores] PRIMARY KEY,
[CategoryId] INT NOT NULL CONSTRAINT [FK_Scores_Category] FOREIGN KEY REFERENCES [dbo].[Categories] ([Id]),
[PostId] INT NOT NULL CONSTRAINT [FK_Scores_Post] FOREIGN KEY REFERENCES [dbo].[Posts] ([Id]),
[Value] INT NOT NULL
)
GO
CREATE INDEX [IX_Scores_CategoryId_Value_PostId]
ON [dbo].[Scores] ([CategoryId], [Value] DESC, [PostId])
GO
我现在可以使用一个视图来获取每个类别的前十篇帖子:
CREATE VIEW [dbo].[TopPosts]
AS
SELECT c.Id AS [CategoryId], cp.PostId, p.[Key], p.SummaryText, cp.Value AS [Score]
FROM [dbo].[Categories] c
CROSS APPLY (
SELECT TOP 10 s.PostId, s.Value
FROM [dbo].[Scores] s
WHERE s.CategoryId = c.Id
ORDER BY s.Value DESC
) AS cp
INNER JOIN [dbo].[Posts] p ON cp.PostId = p.Id
我知道
CROSS APPLY
将使用覆盖索引IX_Scores_CategoryId_Value_PostId
,因为它包含类别ID(用于WHERE
),值(用于ORDER BY
和SELECT
)以及文章ID(用于SELECT
),因此查询速度会比较快。
现在的问题是:INNER JOIN
呢?Join谓词使用的是文章ID,这是Post
表的聚集索引(主键)的关键字。当我创建一个包括SELECT
所有字段的覆盖索引(见下文)时,即使访问聚集索引已经是一个相当快的操作,能否显著提高查询性能(通过更好的执行计划、减少I/O、索引缓存等)?
覆盖索引应该长这样:
CREATE INDEX [IX_Posts_Covering]
ON [dbo].[Posts] ([Id], [Key], [SummaryText])
GO
更新:
由于我的问题方向似乎不是很清晰,让我更详细地阐述一下我的想法。我想知道覆盖索引(或包含列的索引)是否会因为以下原因而更快(并且性能提升值得):
- 硬盘访问。 第二个索引将比聚集索引小得多,SQL Server需要在硬盘上浏览较少的页面,这将产生更好的读取性能。这是正确的吗?您会看到差异吗?
- 内存消耗。 为了将数据加载到内存中,我认为SQL Server必须将整行加载到内存中,然后选择所需的列。这会增加内存消耗吗?
- CPU。 我的假设是,您不会看到CPU使用率的可测量差异,因为从列中提取行本身不是一个CPU操作。对吗?
- 缓存。 我的理解是,您不会看到太多缓存方面的差异,因为SQL Server只会缓存返回的数据,而不是整行数据。或者我错了吗?
这些基本上是(或多或少有教养的)假设。如果有人能让我对这个确实非常特定的问题有所启发,我将不胜感激。