SQL Server索引概要(1)-聚集索引(Clustered Index)

介绍

索引在 SQL Server 查询性能中起着至关重要的作用。考虑一个拥有数千本书的图书馆。

您想搜索标题中包含关键字“冒险”的特定书籍。图书馆里的书杂乱无章。

• 您需要从书架上取下每本书，阅读它，如果它不满足您的要求，则将其放回原处
• 在大型图书馆中，您可能需要几天时间才能找到一本书

假设这个图书馆是一个 SQL 表，所以在 SQL Server 术语中，扫描图书馆中的所有书籍称为表（索引）扫描。

假设您找到了两本关于“adventure”关键字的书，现在您有兴趣在该书中搜索另一个关键字。每本书包含 2,000 页。

您是否可以在不阅读书籍的情况下在书中搜索特定关键字？

在一本书中，我们使用索引来定位您可以找到关键字的页面。在 SQL Server 术语中，我们称其为索引查找操作。

我们可以按照下图可视化表扫描和索引查找操作：

我们在 SQL Server 中主要有两种类型的索引：

• 聚集索引（Cluster Index）
• 非聚集索引(NonClustered Index)

SQL Server 中的聚集索引(Clustered Index)概述

SQL Server 在 B 树结构中创建索引。SQL Server 以这种结构组织数据以快速搜索所需的数据，而不是进行表扫描。

如下图所示，我们在一个 B 树结构索引中有三个层次：

根节点(Root Node)
它是一个顶级节点，由一个指向中间索引页或叶节点（数据页）的指针组成。

中间节点(Intermediate Node)
它由索引键值以及指向下一个中间级别页面或叶数据页面的指针组成。它取决于中间级别数的数据大小。

叶节点(Leaf Node)
它由实际数据页组成。将此视为实际数据存储在 SQL Server 中的聚集索引中的一个点。

让我们为我们的图书馆示例想象这个 b 树。假设我们图书馆有 10,000 本书，并在图书馆表的 bookid 列上创建了聚集索引(CI)：

如上图所示，我们在 library 表的bookid列上定义了索引。现在，如果我们想检索一个特定的书，比如 bookid 7579，SQL Server 会读取以下页面：

• 根节点：根页告诉SQL Server，5001到10000 的中间节点的指针
• 中间节点： SQL Server读取中间索引页并获取叶节点中实际数据页的指针
• 叶节点： SQL Server 根据我们的要求读取叶节点中的数据页

如前所述，SQL Server 读取索引页并返回信息。此过程称为索引查找(Index Seek)。在某些情况下，SQL Server 读取所有叶级数据页。它被称为索引扫描(Index Scan)。毫无疑问，索引查找比索引扫描更快。

让我们创建一个测试表并插入几条数据：

CREATE TABLE dbo.bookstore
(book_id   INT NOT NULL, 
 book_name VARCHAR(100)
);
Insert into dbo.bookstore values(1,'Learn ABC of SQL Server')
Insert into dbo.bookstore values(2,'Advanced troubleshooting step SQL Server')

我们在表上没有任何索引。没有**聚集索引(CI)**的表称为堆表(Heap Table)。

然后我们再给测试表创建一个聚集索引：

CREATE UNIQUE CLUSTERED INDEX [IX_bookstore_clustered] ON [dbo].[bookstore]
(
    [book_id] ASC
);

检查聚集索引级别

我们可以使用 DMV sys.dm_db_index_physical_stats来检查索引碎片级别，CI 的索引级别。在表中插入几条记录并执行以下查询：

SELECT  
    avg_page_space_used_in_percent, 
    avg_fragmentation_in_percent, 
    index_level, 
    record_count, 
    page_count, 
    fragment_count, 
    avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('SQLShack'), OBJECT_ID('bookstore'), NULL, NULL, 'DETAILED');
GO

在下面的屏幕截图中，我们有两个级别的索引：

查看带有聚集索引的查询的执行计划

让我们执行以下查询并查看实际执行计划：

SELECT *
INTO books_1
FROM bookstore;

SQL Server 使用索引查找从书店表中检索记录：

让我们使用以下查询创建一个备份表：

SELECT *
INTO books_1
FROM bookstore;

此备份表与主表具有相似的结构和数据。唯一的区别是备份表没有任何索引，而我们的主表在books_ID列上包含 CI 。

让我们在单独的窗口中执行以下查询并比较执行计划：

SELECT *
FROM book_1
WHERE book_ID = 10000;
    
SELECT *
FROM bookstore
WHERE book_ID = 10000;

在上面的屏幕截图中，请注意以下差异。我们可以快速算出堆和带有聚集索引的表的性能对比：

	堆表(无索引)	在查询中包含了聚集索引的表
逻辑运算	Table Scan	Cluster Index
逻辑读取	1820	3
物理读取	1688	0
估计要读取的行数	734725	0
估计操作成本	2.15889	0.0032831
估计IO成本	1.35061	0.003125
估计CPU成本	0.808276	0.0001581
读取的行数	1688	0

结论

在本文中，我们探讨了 SQL Server 中的聚集索引以及创建它的 t-SQL 方法。我们还学习了堆和 CI 键表之间的性能比较。您应该根据更好的查询性能要求定义 CI。

原文地址