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;

 
分类: SQL Server

posted on 2025-04-21 16:47  漫思  阅读(75)  评论(0)    收藏  举报

导航