65.9K
CodeProject 正在变化。 阅读更多。
Home

计算列 - 索引和性能

pinaldave
starIconstarIconstarIconstarIconstarIcon

5.00/5 (13投票s)

2010年8月24日

CPOL

4分钟阅读

viewsIcon

37253

关于计算列、存储和性能系列的最终篇

这是我撰写的计算列系列的最后一篇文章。 以下是之前的文章。

SQL SERVER – 计算列 – PERSISTED 和存储

本文讨论了如何创建计算列以及为什么它们比以前占用更多的存储空间。

SQL SERVER – 计算列 – PERSISTED 和性能

本文讨论了 PERSISTED 列如何比非 PERSISTED 列提供更好的性能。

SQL SERVER – 计算列 – PERSISTED 和性能 – 第 2 部分

本文讨论了非 PERSISTED 列如何比 PERSISTED 列提供更好的性能。

SQL SERVER – 计算列和性能 – 第 3 部分

本文讨论了索引如何提高计算列的性能。

SQL SERVER – 计算列 – PERSISTED 和存储 – 第 2 部分

本文讨论了在计算列上创建索引不会增加表的行长度。

SQL SERVER – 计算列 – 索引和性能

本文总结了所有与计算列相关的文章。

在今天的文章中，我们将看到如何使用计算列获得更好的性能。以下是我们即将遵循的几个步骤。首先，我们将创建一个常规表并填充一些数据。填充数据后，我们将尝试查询数据。由于没有表，我们将得到表扫描。之后，我们将创建索引，并看到由于计算而不是索引查找（这是理想的），我们仍然会得到索引扫描。这些操作之后将创建计算列和计算列上的索引。我们可以立即看到性能的提高，因为将应用索引查找。让我们通过一个例子来理解这一点。

USE [tempdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = _
		OBJECT_ID(N'[dbo].[CompCol]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[CompCol]
GO
CREATE TABLE [dbo].[CompCol](
[ID] [int] NOT NULL,
[FirstName] [varchar](100) NULL,
[LastName] [varchar](100) NULL,
[BirthDate] [datetime] NULL,
CONSTRAINT [PK_CompCol] PRIMARY KEY CLUSTERED
([ID] ASC)
)
GO

现在让我们在表中插入几行。

-- Insert One Hundred Thousand Records
INSERT INTO CompCol (ID,FirstName,LastName, BirthDate)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 _
	THEN DATEADD(yy,-2, '2010-08-13 14:20:24.853')
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%3 = 1 _
	THEN DATEADD(yy,-3, '2010-08-13 14:20:24.853')
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%5 = 1 _
	THEN DATEADD(yy,-5, '2010-08-13 14:20:24.853')
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%7 = 1 _
	THEN DATEADD(yy,-7, '2010-08-13 14:20:24.853')
ELSE GETDATE() END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

现在我们将对该表应用一个简单的 SELECT 语句。

-- Select specific year data
SELECT ID, FirstName
FROM CompCol
WHERE MONTH(BirthDate) = 8
GO

从 resultset 可以清楚地看到查询正在执行索引扫描。 这是很自然的，因为表上没有索引。 让我们在 WHERE 子句中使用的字段上创建一个索引。

-- Create non clustered index on regular column
CREATE NONCLUSTERED INDEX IX_CompCol_BrithDate
ON dbo.CompCol (BirthDate, FirstName)
GO

创建索引后，让我们再次运行原始的 SELECT 语句。 您会注意到索引扫描仍然存在。 由于我们在 WHERE 条件中寻找特定值，因此理想的是索引查找。

现在，让我们通过保留我们在 WHERE 条件中的内容来创建计算列。 我们可以立即将月份的值存储在一个单独的列中。

-- Add Computed Column
ALTER TABLE dbo.CompCol ADD
BirthMonth AS MONTH(BirthDate)
GO

但是，正如之前的文章中提到的，计算列是在运行时物化的。 由于相同的原因，它不能单独提高性能，并且 SELECT 语句将提供与以前非常相似的性能。

-- Select specific year data
SELECT ID, FirstName
FROM CompCol
WHERE BirthMonth = 8
GO

现在让我们在我们刚刚创建的计算列上创建一个索引。

-- Create non clustered index on Computed Column
CREATE NONCLUSTERED INDEX IX_CompCol_BirthMonth
ON dbo.CompCol (BirthMonth, FirstName)
GO

创建索引后，让我们再次运行两个 T-SQL 查询。 我们运行的第一个查询是在 WHERE 条件中包含计算列的查询。

-- Select specific year data
SELECT ID, FirstName
FROM CompCol
WHERE BirthMonth = 8
GO

我们将观察到现在使用了新创建的索引，并且之前的索引扫描现在已转换为索引查找。

现在让我们运行第一个脚本，其中我们在 WHERE 条件中使用了 MONTH 函数。

-- Compare above query with original Query
SELECT ID, FirstName
FROM CompCol
WHERE MONTH(BirthDate) = 8
GO

我们现在可以看到，新创建的索引也适用于此处，并且索引扫描也已转换为索引查找。

我们绝对可以看到索引扫描已转换为索引查找，但这真的能提高性能吗？

我们可以将之前使用不同索引（强制索引扫描）的查询与新更改的索引查找查询进行比较。 为了重新创建此场景，我们将必须使用先前使用的索引的索引提示。

-- Compare above query with original Query
SELECT ID, FirstName
FROM CompCol
WHERE MONTH(BirthDate) = 8
GO
-- Compare above query with original Query
-- with hint of original index use
SELECT ID, FirstName
FROM CompCol WITH (INDEX(IX_CompCol_BrithDate))
WHERE MONTH(BirthDate) = 8
GO

您可以启用执行计划并清楚地观察到，在计算列上创建新索引后，性能得到了提高。

您可以运行以下命令进行清理。

-- Clean up Database
DROP TABLE CompCol
GO

总而言之，这篇博文清楚地表明，即使您不使用计算列，创建了索引的计算列也可以立即发挥作用。 SQL Server 引擎足够智能，可以做出正确的选择。

您可以从此处复制完整的代码

USE [tempdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = _
		OBJECT_ID(N'[dbo].[CompCol]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[CompCol]
GO
CREATE TABLE [dbo].[CompCol](
[ID] [int] NOT NULL,
[FirstName] [varchar](100) NULL,
[LastName] [varchar](100) NULL,
[BirthDate] [datetime] NULL,
CONSTRAINT [PK_CompCol] PRIMARY KEY CLUSTERED
([ID] ASC)
)
GO
-- Insert One Hundred Thousand Records
INSERT INTO CompCol (ID,FirstName,LastName, BirthDate)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 _
	THEN DATEADD(yy,-2, '2010-08-13 14:20:24.853')
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%3 = 1 _
	THEN DATEADD(yy,-3, '2010-08-13 14:20:24.853')
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%5 = 1 _
	THEN DATEADD(yy,-5, '2010-08-13 14:20:24.853')
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%7 = 1 _
	THEN DATEADD(yy,-7, '2010-08-13 14:20:24.853')
ELSE GETDATE() END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Select specific year data
SELECT ID, FirstName
FROM CompCol
WHERE MONTH(BirthDate) = 8
GO
-- Create non clustered index on regular column
CREATE NONCLUSTERED INDEX IX_CompCol_BrithDate
ON dbo.CompCol (BirthDate, FirstName)
GO
-- Select specific year data
SELECT ID, FirstName
FROM CompCol
WHERE MONTH(BirthDate) = 8
GO
-- Add Computed Column
ALTER TABLE dbo.CompCol ADD
BirthMonth AS MONTH(BirthDate)
GO
-- Select specific year data
SELECT ID, FirstName
FROM CompCol
WHERE BirthMonth = 8
GO
-- Create non clustered index on Computed Column
CREATE NONCLUSTERED INDEX IX_CompCol_BirthMonth
ON dbo.CompCol (BirthMonth, FirstName)
GO
-- Select specific year data
SELECT ID, FirstName
FROM CompCol
WHERE BirthMonth = 8
GO
-- Compare above query with original Query
SELECT ID, FirstName
FROM CompCol
WHERE MONTH(BirthDate) = 8
GO
-- Compare above query with original Query
-- with hint of original index use
SELECT ID, FirstName
FROM CompCol WITH (INDEX(IX_CompCol_BrithDate))
WHERE MONTH(BirthDate) = 8
GO
-- Clean up Database
DROP TABLE CompCol
GO

参考：Pinal Dave (http://blog.SQLAuthority.com)

历史

  • 2010 年 8 月 24 日：初始帖子
© . All rights reserved.