8.2 SQL Server聚集索引
SQL Server Clustered Indexes(聚集索引)
简介
下面创建一个名为production.parts
(产品部件)的新表:
--产品部件 CREATE TABLE production.parts( part_id INT NOT NULL, --部件Id part_name VARCHAR(100)--部件名 );
然后插入几行:
INSERT INTO production.parts(part_id, part_name) VALUES (1,'Frame'),--框架 (2,'Head Tube'),--头管 (3,'Handlebar Grip'),--把手 (4,'Shock Absorber'),--减震器 (5,'Fork');
由于没有给表production.parts
添加主键,所以,SQL Server将数据行存储在称为堆(heap)的无序结构中。
当从此中查询数据时,SQL Server 查询优化器需要扫描整张表进行搜索。
比如,查询Id为3的部件:
SELECT part_id, part_name FROM production.parts WHERE part_id = 5;
如果在SQL Server Management Studio中打开执行计划,将看到SQL Server如何提出以下查询计划:
打开执行计划方式
- 1.选中要执行的代码
- 2.点击左上角执行计划图标(或者Ctrl+L)
此查询的执行计划如下:
由于表中只有5行数据,查询执行速度非常快。但是,如果表包含大量行,则需要花费大量时间和资源来搜索数据。
为了解决这个问题,SQLServer提供了一个名为索引
(index)的专用结构来加快从表中检索行的速度。
SQL Server有两种类型的索引:聚集索引
(clustered index)和非聚集索引
(non-clustered index)。在本篇中,我将重点介绍聚集索引。
聚集索引根据键值,对数据行进行有序存储。每个表只有一个聚集索引,因为数据行只能按一个顺序排序。具有聚集索引的表称为聚集表。
下图说明了聚集索引的结构:
数据行是通过聚集键排序过的。
聚集索引使用一种所谓B+树(或平衡树)特殊结构的来组织数据,该树可以在对查询、插入、更新和删除的时间上进行一定的对数平摊。
在这种结构中,B+树的顶部节点称为根节点。底层的节点称为叶节点。根节点和叶节点之间的索引层称为中间层。
在B+树中,根节点和中间层节点包含保存索引行的索引页。叶节点包含基础表的数据页。索引同一层级中的每个页面都使用双向链表进行链接。
SQL Server聚集索引和主键约束
使用主键创建表时,SQL Server会自动创建包含主键列的聚集索引。
创建一个名为production.part_prices
(部件价格)的表,同时创建主键,主键含有两列:part_id
和valid_from
--产品部件价格 CREATE TABLE production.part_prices( part_id int,--Id valid_from date,--有效期自 price decimal(18,4) not null,--价格 PRIMARY KEY(part_id, valid_from)--添加主键约束 );
如果向没有聚集索引的现有表添加主键约束,SQL Server将强制给主键添加聚集索引:
给production.parts
表添加主键约束:
ALTER TABLE production.parts ADD PRIMARY KEY(part_id);
使用SQL Server CREATE CLUSTERED INDEX
语句创建聚集索引
简介
当表没有主键(这种情况非常罕见)时,可以使用CREATE CLUSTERED INDEX
语句向其添加聚集索引。
语法:
CREATE CLUSTERED INDEX index_name ON schema_name.table_name (column_list);
此语法中:
- 首先在
CREATE CLUSTERED INDEX
子句后指定索引名字。 - 然后再指定要在其上创建索引的表名。
- 第三,列出索引中要包含的一个或多个列。
示例
首先删除前面给production.parts
表创建的主键约束,聚集索引也被同时删除。
然后再给production.parts
表添加聚集索引:
CREATE CLUSTERED INDEX ix_parts_id ON production.parts (part_id);
如果在表名下打开Indexes
节点,您将看到新的索引名ix_parts_id
,类型为Clustered
(聚集)。
此时再执行以下语句时,SQL Server直接遍历索引(聚集索引查找)以定位具体行,这比扫描整个表快得多。
SELECT part_id, part_name FROM production.parts WHERE part_id = 5;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek “源神”启动!「GitHub 热点速览」
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器