SQL Server索引概要(2)-非聚集索引(Non-Clustered Index)

标签：EmpID Index Non 聚集 Server 索引 SQL Employee

介绍
在上一篇SQL Server 聚集索引概述 中，我们探讨了 SQL Server 中索引和聚集索引的要求。

在我们继续之前，让我们快速总结一下 SQL Server 聚集索引：

• 它根据聚集索引键对数据进行物理排序
• 每个表只能有一个聚集索引
• 没有聚集索引的表是堆，可能会导致性能问题
• SQL Server自动为主键列创建聚集索引
• 一个聚集索引以b-tree格式存储，包含叶子节点中的数据页

非聚集索引对于查询性能和优化也很有用，具体取决于查询工作负载。在本文中，让我们探索非聚集索引及其内部结构。

非聚集索引概述

在非聚集索引中，叶节点不包含实际数据。它由一个指向实际数据的指针组成。

• 如果表包含聚集索引，叶节点指向包含实际数据的聚集索引数据页
• 如果表是堆（没有聚集索引），叶节点指向堆页

在下图中，我们可以查看指向聚集索引中数据页的非聚集索引的叶节点：

 

 

我们可以在 SQL 表中有多个非聚集索引，因为它是一个逻辑索引，与聚集索引相比，它不会对数据进行物理排序。

让我们通过一个例子来了解 SQL Server 中的非聚集索引。

• 创建一个没有任何索引的Employee表

CREATE TABLE dbo.Employee
(EmpID            INT, 
 EMpName          VARCHAR(50), 
 EmpAge           INT, 
 EmpContactNumber VARCHAR(10)
);

• 在其中插入几条记录

Insert into Employee values(1,'Raj',32,8474563217)
Insert into Employee values(2,'kusum',30,9874563210)
Insert into Employee values(3,'Akshita',28,9632547120)

• 搜索 EmpID 2 并查找它的实际执行计划

Select * from Employee where EmpID=2

• 1

它执行表扫描(Table Scan)，因为我们在该表上没有任何索引：

• 在EmpID列上创建唯一的聚集索引

CREATE UNIQUE CLUSTERED INDEX IX_Clustered_Empployee ON dbo.Employee(EmpID);

• 1

• 搜索 EmpID 2 并查找它的实际执行计划

在这个执行计划中，我们可以注意到表扫描(Table Scan)变成了聚集索引查找(Cluster Index Seek)：

让我们执行另一个 SQL 查询来搜索具有特定联系号码的 Employee：

Select * from Employee where EmpContactNumber='9874563210'

• 1

我们在EmpContactNumber列上没有索引，因此查询优化器使用聚集索引，但它扫描整个索引以检索记录：

 

 

右键单击执行计划并选择Show Execution Plan XML：

它在新查询窗口中打开 XML 执行计划。在这里，我们注意到它使用聚集索引键并读取各个行以检索结果：

让我们使用以下脚本在 Employee 表中插入更多记录：

Insert into Employee values(4,'Manoj',38,7892145637)
Insert into Employee values(5,'John',33,7900654123)
Insert into Employee values(6,'Priya',18,9603214569)

• 1
• 2
• 3

我们在这个表中有六个员工的记录。现在，再次执行 select 语句以检索具有特定联系号码的员工记录：

 

 

它再次根据指定的条件扫描所有六行的结果。想象一下，表中有数百万条记录。如果 SQL Server 必须读取所有索引键行，这将是一项资源和耗时的任务。

我们可以按照下图以 B 树格式表示聚集索引（不是实际表示）：

 

 

在前面的查询中，SQL Server 读取根节点页并检索每个叶节点页和行以进行数据检索。

现在让我们在 SQL Server的EmpContactNumber列上的Employee表上 创建一个唯一的非聚集索引作为索引键：

CREATE UNIQUE NONCLUSTERED INDEX IX_NonClustered_Employee ON dbo.Employee(EmpContactNumber);

• 1

在我们解释这个索引之前，重新运行 SELECT 语句并查看实际的执行计划：

 

 

在这个执行计划中，我们可以看到两个组件：

• 索引查找（非聚集) - Index Seek(NonClustered)
• 键查找（集群） - Key Lookup(Clustered)

要了解这些组件，我们需要查看 SQL Server 设计中的非聚集索引。在这里，您可以看到叶节点包含非聚集索引键（EmpContactNumber）和聚集索引键（EmpID）：

 

 

现在，如果重新运行 SELECT 语句，它将使用非聚集索引键遍历并指向具有聚集索引键的页面：

 

 

它表明它使用聚集索引键和非聚集索引键的组合检索记录。您可以看到 SELECT 语句的完整逻辑，如下所示：

 

 

1. 用户执行 select 语句以查找与指定联系人号码匹配的员工记录
2. 查询优化器使用非聚集索引键并找出页码 1001
3. 该页面由聚集索引键组成。您可以在上图中看到 EmpID
4. SQL Server 使用聚集索引键找出由 EmpID 1 记录组成的第101 页
5. 它读取匹配的行并将输出返回给用户

之前，我们看到它读取六行来检索匹配的行并在输出中返回一行。让我们看一下使用非聚集索引的执行计划：

 

 

非唯一非聚集索引

我们可以在一个 SQL 表中有多个非聚集索引。以前，我们在 EmpContactNumber 列上创建了唯一的非聚集索引。

在创建索引之前，执行以下查询，以便我们在EmpAge列中有重复的值 ：

Update Employee set EmpAge=32 where EmpID=2
Update Employee set EmpAge=38 where EmpID=6
Update Employee set EmpAge=38 where EmpID=3

• 1
• 2
• 3

让我们对非唯一非聚集索引执行以下查询。在查询语法中，我们没有指定唯一关键字，它告诉 SQL Server 创建一个非唯一索引：

CREATE NONCLUSTERED INDEX NCIX_Employee_EmpAge ON dbo.Employee(EmpAge);

• 1

众所周知，索引的键应该是唯一的。在这种情况下，我们要添加一个非唯一键。问题出现了：SQL Server 如何使这个键成为唯一的？

SQL Server 为它做以下事情：

• 它在非唯一非聚集索引的叶子页和非叶子页中添加聚集索引键
• 如果聚集索引键也是非唯一的，则添加一个 4 字节的 uniquifier，以便索引键是唯一的
  

   

   

在非聚集索引中包含非键列

让我们再次查看以下查询的以下实际执行计划：

Select * from Employee
where EmpContactNumber='8474563217'

• 1
• 2

 

 

它包括索引查找和键查找运算符，如上图所示：

1. 索引查找：SQL 查询优化器在非聚集索引上使用**索引查找(Index Seek)**并获取 EmpID、EmpContactNumber列
2. 在这一步中，查询优化器在聚集索引上使用键查(Key Lookup)找并获取EmpName和EmpAge列的值
3. 

    

    

4. 在这一步中，查询优化器对非聚集索引的每一行输出使用**嵌套循环(Nested Loops)**来匹配聚集索引行
   

    

    

对于大型表，嵌套循环可能是一个代价高昂的运算符。我们可以使用非聚集索引非键列来降低成本。我们使用 索引子句指定非聚集索引中的非键列。

让我们使用包含数据列的方式重新创建非聚集索引：

DROP INDEX [IX_NonClustered_Employee] ON [dbo].[Employee]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_NonClustered_Employee] ON [dbo].[Employee]
(
    [EmpContactNumber] ASC
)
INCLUDE(EmpName,EmpAge)

• 1
• 2
• 3
• 4
• 5
• 6
• 7

包含的列是索引树中叶节点的一部分。它有助于从索引本身获取数据，而不是进一步遍历以检索数据。

在下图中，我们将包含的列EmpName和EmpAge作为叶节点的一部分：

 

 

现在重新执行SELECT语句，查看实际执行计划。我们在这个执行计划中没有键查找(Index Seek)和嵌套循环(Nested Loops)：

 

 

让我们将光标悬停在索引查找上并查看输出列列表。SQL Server 可以使用此非聚集索引查找找到所有列：

 

 

借助包含的非键数据列，我们可以使用覆盖索引提高查询性能。然而，这并不意味着我们应该在索引定义中所有非键列。我们在设计索引时要小心，在生产环境中部署之前应该测试索引行为。

结论

在本文中，我们探讨了 SQL Server 中的非聚集索引及其与聚集索引的结合使用。我们应该根据工作负载和查询行为仔细设计索引。

 

转 https://blog.csdn.net/albatross76/article/details/117645595

标签：EmpID,Index,Non,聚集,Server,索引,SQL,Employee
From： https://www.cnblogs.com/wl-blog/p/17221279.html