sql-server – 集群索引和非集群索引之间的性能差异

聚集索引 – 它包含数据页面.这意味着完整的行
信息将出现在聚集索引列中.

非聚集索引 – 它仅包含行中的行定位器信息
聚合索引列的形式(如果可用)或文件标识符
页码总页数.这意味着查询引擎必须采取额外步骤才能找到实际数据.

查询 – 如何在实际示例的帮助下检查性能差异,因为我们知道该表只能有一个Clustered Index并且在Clustered Index Column和Non Clustered Index提供排序不提供排序并且可以支持999 Non SQL Server 2008中的聚簇索引和SQL Server 2005中的249.

解决方法

首先当你看到聚集索引思考表时.在SQL Server中,如果表不包含聚簇索引,则它是堆.在表上创建聚簇索引实际上将表转换为b树类型结构.您的聚簇索引是您的表,它不与表分开

有没有想过为什么你只能有一个聚集索引？好吧,如果我们有两个聚簇索引,我们需要两个表的副本.它毕竟包含数据.

我将尝试使用一个简单的例子来解释这一点.

注意：我在此示例中创建了表,并填充了超过300万个随机条目.然后运行实际查询并在此处粘贴执行计划.

你真正需要掌握的是O符号或操作效率.让我们假设您有下表.

CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,[CustomerName] [varchar](100) NOT NULL,[CustomerSurname] [varchar](100) NOT NULL,CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
[CustomerID] ASC
)WITH (PAD_INDEX  = OFF,STATISTICS_NORECOMPUTE  = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS  = ON,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

所以这里我们在CustomerID上有一个带有聚簇键的基本表(默认情况下,主键是聚类的).因此,基于主键CustomerID来排列/排序该表.中间级别将包含CustomerID值.数据页将包含整行,因此它是表行.

我们还将在CustomerName字段上创建非聚集索引.以下代码将执行此操作.

CREATE NONCLUSTERED INDEX [ix_Customer_CustomerName] ON [dbo].[Customer] 
 (
[CustomerName] ASC
 )WITH (PAD_INDEX  = OFF,SORT_IN_TEMPDB = OFF,DROP_EXISTING = OFF,ONLINE = OFF,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

因此,在此索引中,您将在数据页/叶级节点上找到指向聚簇索引中的中间级别的指针.索引按CustomerName字段排列/排序.因此,中间级别包含CustomerName值,叶级别将包含指针(这些指针值实际上是主键值或CustomerID列).

是的,如果我们执行以下查询：

SELECT * FROM Customer WHERE CustomerID =1

SQL可能会通过搜索操作读取聚簇索引.搜索操作是二进制搜索,其比作为顺序搜索的扫描更有效.因此,在上面的示例中,读取索引并使用二进制搜索SQL可以消除与我们要查找的条件不匹配的数据.请参阅查询计划的附加屏幕截图.

因此,搜索操作的操作数或O表示法如下：

>通过将搜索到的值与中间级别的值进行比较,对聚集索引进行二进制搜索.
>返回匹配的值(记住,因为聚簇索引中的所有数据都可以返回索引中的所有列,因为它是行数据)

所以这是两个操作.但是,如果我们执行以下查询：

SELECT * FROM Customer WHERE CustomerName ='John'

SQL现在将使用CustomerName上的非聚集索引进行搜索.但是,由于这是一个非聚集索引,因此它不包含行中的所有数据.

因此,SQL将在中间级别上进行搜索以查找匹配的记录,然后使用返回的值执行查找,以对聚簇索引(也称为表)执行另一次搜索以检索实际数据.这听起来令人困惑我知道但是继续阅读并且一切都会变得清晰.

由于我们的非聚集索引仅包含CustomerName字段(存储在中间节点中的索引字段值)和指向CustomerID的数据的指针,因此索引没有CustomerSurname的记录.必须从聚簇索引或表中提取CustomerSurname.

运行此查询时,我得到以下执行计划：

在上面的屏幕截图中,您需要注意两件重要的事情

> SQL说我缺少索引(绿色文本). SQL建议我在CustomerName上创建一个包含CustomerID和CustomerSurname的索引.
>您还将看到查询的99％用于对主键索引/聚簇索引执行键查找.

为什么SQL会再次建议CustomerName上的索引？好了,因为索引只包含CustomerID和CustomerName,SQL仍然必须从表/聚簇索引中找到CustomerSurname.

如果我们创建了索引并且我们在索引中包含了CustomerSurname列,则只需读取非聚集索引就可以满足整个查询.这就是为什么SQL建议我改变我的非聚集索引.

在这里,您可以看到SQL需要执行的额外操作,以便从群集密钥中获取CustomerSurname列

因此操作次数如下：

>通过将搜索到的值与中间级别的值进行比较,对非聚集索引进行二进制搜索
>对于匹配的节点,读取叶级节点,该节级将包含聚簇索引中数据的指针(叶级节点将按顺序包含主键值).
>对于返回的每个值,在聚集索引(表)上读取以获取行值,我们将读取CustomerSurname.
>返回匹配的行

这是4个操作来获取值.与读取聚簇索引相比,需要两倍的操作量.向您展示您的聚簇索引是最强大的索引,因为它包含所有数据.

所以只是为了澄清最后一点.为什么我说非聚集索引中的指针是主键值？为了证明非聚集索引的叶级节点包含主键值,我将查询更改为：

SELECT CustomerID
 FROM Customer
 WHERE CustomerName='Jane'

在此查询中,SQL可以从非聚集索引中读取CustomerID.它不需要对聚集索引进行查找.您可以通过看起来像这样的执行计划看到.

请注意此查询与上一个查询之间的区别.没有查找. SQL可以找到非聚集索引中的所有数据

希望您可以开始了解聚簇索引是表和非聚集索引DONT包含所有数据.索引将加速选择,因为可以完成二进制搜索,但只有聚簇索引包含所有数据.因此,对非聚集索引的搜索几乎总是会导致从聚簇索引加载值.这些额外操作使非聚簇索引的效率低于聚簇索引.

希望这可以解决问题.如果有任何意义没有意义,请发表评论,我会尽力澄清.现在已经很晚了,我的大脑感觉有点扁平.红牛的时间.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!