SQL_Server和Oracle中的索引.txt

jenk

267

5页

0次

2021-02-22

40墨值下载

1 SQL

Server 中的索引

索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或

多列生成的键。这些键存储在一个结构（B 树）中，使 SQL

Server 可以快速有效地查找与键值关联的行。

表或视图可以包含以下类型的索引：

聚集索引

聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有

一个聚集索引，因为数据行本身只能按一个顺序排序。

只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表具有聚集索引，则该表称为聚集表。如果

表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。

每个表几乎都对列定义聚集索引来实现下列功能：

1、可用于经常使用的查询。

2、提供高度唯一性。

在创建聚集索引之前，应先了解数据是如何被访问的。考虑对具有以下特点的查询使用聚集索引：

使用运算符（如 BETWEEN、>、>=、< 和 <=）返回一系列值。

使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行物理相邻。例如，如果某个查询在一

系列采购订单号间检索记录，PurchaseOrderNumber

列的聚集索引可快速定位包含起始采购订单号的行，然后检索表中所有连续的行，直到检索到最后的采购订单

号。

返回大型结果集。

使用 JOIN 子句；一般情况下，使用该子句的是外键列。

使用 ORDER BY 或 GROUP BY 子句。

在 ORDER BY 或 GROUP BY 子句中指定的列的索引，可以使数据库引擎不必对数据进行排序，因为这些行

已经排序。这样可以提高查询性能。

聚集索引不适用于具有下列属性的列：

频繁更改的列

这将导致整行移动，因为数据库引擎必须按物理顺序保留行中的数据值。这一点要特别注意，因为在大容量

事务处理系统中数据通常是可变的。

宽键

宽键是若干列或若干大型列的组合。所有非聚集索引将聚集索引中的键值用作查找键。为同一表定义的任何非

聚集索引都将增大许多，这是因为非聚集索引项包含聚集键，同时也包含为此非聚集索引定义的键列。

非聚集索引

非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值

的数据行的指针。

从非聚集索引中的索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页是存储在堆中还是聚

集表中。对于堆，行定位器是指向行的指针。对于聚集表，行定位器是聚集索引键。

在 SQL Server 2005 中，可以向非聚集索引的叶级别添加非键列以跳过现有的索引键限制（900 字节和

16 键列），并执行完整范围内的索引查询。

非聚集索引与聚集索引具有相同的 B 树结构，它们之间的显著差别在于以下两点：

1、基础表的数据行不按非聚集键的顺序排序和存储。

2、非聚集索引的叶层是由索引页而不是由数据页组成。

设计非聚集索引时需要注意数据库的特征:

更新要求较低但包含大量数据的数据库或表可以从许多非聚集索引中获益从而改善查询性能。

决策支持系统应用程序和主要包含只读数据的数据库可以从许多非聚集索引中获益。查询优化器具有更多可供

选择的索引用来确定最快的访问方法，并且数据库的低更新特征意味着索引维护不会降低性能。

联机事务处理应用程序和包含大量更新表的数据库应避免使用过多的索引。此外，索引应该是窄的，即列越少

越好。

一个表如果建有大量索引会影响 INSERT、UPDATE 和 DELETE 语句的性能，因为所有索引都必须随表中数

据的更改进行相应的调整。

唯一索引

唯一索引确保索引键不包含重复的值，因此，表或视图中的每一行在某种程度上是唯一的。

聚集索引和非聚集索引都可以是唯一索引。

包含性列索引

一种非聚集索引，它扩展后不仅包含键列，还包含非键列。

索引涵盖

指查询中的 SELECT 与 WHERE 子句的所用列同时也属于非聚集索引的情况。这样就可以更快检索数据，因为

所有信息都可以直接来自于索引页，从而 SQL

Server 可以避免访问数据页。加上独立的索引文件组，可以用最快速度访问数据。

请看如下表示例：

A.创建简单非聚集索引

以下示例为 Purchasing.ProductVendor 表的 VendorID 列创建非聚集索引。

USE AdventureWorks;

CREATE INDEX IX_ProductVendor_VendorID

ON Purchasing.ProductVendor (VendorID);

B. 创建简单非聚集组合索引

以下示例为 Sales.SalesPerson 表的 SalesQuota 和 SalesYTD 列创建非聚集组合索引。

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD

ON Sales.SalesPerson (SalesQuota, SalesYTD);

C. 创建唯一非聚集索引

以下示例为 Production.UnitMeasure 表的 Name 列创建唯一的非聚集索引。该索引将强制插入 Name

列中的数据具有唯一性。

USE AdventureWorks;

CREATE UNIQUE INDEX AK_UnitMeasure_Name

ON Production.UnitMeasure(Name);

无论何时对基础数据执行插入、更新或删除操作，SQL Server 2005

数据库引擎都会自动维护索引。随着时间的推移，这些修改可能会导致索引中的信息分散在数据库中（含有碎

片）。当索引包含的页中的逻辑排序（基于键值）与数据文件中的物理排序不匹配时，就存在碎片。碎片非常

多的索引可能会降低查询性能，导致应用程序响应缓慢。这个时候，我们需要做得就是重新组织和重新生成索

引。重新生成索引将删除该索引并创建一个新索引。此过程中将删除碎片，通过使用指定的或现有的填充因子

设置压缩页来回收磁盘空间，并在连续页中对索引行重新排序（根据需要分配新页）。这样可以减少获取所请

求数据所需的页读取数，从而提高磁盘性能。

可以使用下列方法重新生成聚集索引和非聚集索引：

带 REBUILD 子句的 ALTER INDEX。此语句将替换 DBCC DBREINDEX 语句。

带 DROP_EXISTING 子句的 CREATE INDEX。

示例如下：

A. 重新生成索引

以下示例将重新生成单个索引。

1 SQL

Server 中的索引

索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或

多列生成的键。这些键存储在一个结构（B 树）中，使 SQL

Server 可以快速有效地查找与键值关联的行。

表或视图可以包含以下类型的索引：

聚集索引

聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有

一个聚集索引，因为数据行本身只能按一个顺序排序。

只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表具有聚集索引，则该表称为聚集表。如果

表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。

每个表几乎都对列定义聚集索引来实现下列功能：

1、可用于经常使用的查询。

2、提供高度唯一性。

在创建聚集索引之前，应先了解数据是如何被访问的。考虑对具有以下特点的查询使用聚集索引：

使用运算符（如 BETWEEN、>、>=、< 和 <=）返回一系列值。

使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行物理相邻。例如，如果某个查询在一

系列采购订单号间检索记录，PurchaseOrderNumber

列的聚集索引可快速定位包含起始采购订单号的行，然后检索表中所有连续的行，直到检索到最后的采购订单

号。

返回大型结果集。

使用 JOIN 子句；一般情况下，使用该子句的是外键列。

使用 ORDER BY 或 GROUP BY 子句。

在 ORDER BY 或 GROUP BY 子句中指定的列的索引，可以使数据库引擎不必对数据进行排序，因为这些行

已经排序。这样可以提高查询性能。

聚集索引不适用于具有下列属性的列：

频繁更改的列

这将导致整行移动，因为数据库引擎必须按物理顺序保留行中的数据值。这一点要特别注意，因为在大容量

事务处理系统中数据通常是可变的。

宽键

宽键是若干列或若干大型列的组合。所有非聚集索引将聚集索引中的键值用作查找键。为同一表定义的任何非

聚集索引都将增大许多，这是因为非聚集索引项包含聚集键，同时也包含为此非聚集索引定义的键列。

非聚集索引

非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值

的数据行的指针。

从非聚集索引中的索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页是存储在堆中还是聚

集表中。对于堆，行定位器是指向行的指针。对于聚集表，行定位器是聚集索引键。

在 SQL Server 2005 中，可以向非聚集索引的叶级别添加非键列以跳过现有的索引键限制（900 字节和

16 键列），并执行完整范围内的索引查询。

非聚集索引与聚集索引具有相同的 B 树结构，它们之间的显著差别在于以下两点：

1、基础表的数据行不按非聚集键的顺序排序和存储。

2、非聚集索引的叶层是由索引页而不是由数据页组成。

设计非聚集索引时需要注意数据库的特征:

更新要求较低但包含大量数据的数据库或表可以从许多非聚集索引中获益从而改善查询性能。

决策支持系统应用程序和主要包含只读数据的数据库可以从许多非聚集索引中获益。查询优化器具有更多可供

选择的索引用来确定最快的访问方法，并且数据库的低更新特征意味着索引维护不会降低性能。

联机事务处理应用程序和包含大量更新表的数据库应避免使用过多的索引。此外，索引应该是窄的，即列越少

越好。

一个表如果建有大量索引会影响 INSERT、UPDATE 和 DELETE 语句的性能，因为所有索引都必须随表中数

据的更改进行相应的调整。

唯一索引

唯一索引确保索引键不包含重复的值，因此，表或视图中的每一行在某种程度上是唯一的。

聚集索引和非聚集索引都可以是唯一索引。

包含性列索引

一种非聚集索引，它扩展后不仅包含键列，还包含非键列。

索引涵盖

指查询中的 SELECT 与 WHERE 子句的所用列同时也属于非聚集索引的情况。这样就可以更快检索数据，因为

所有信息都可以直接来自于索引页，从而 SQL

Server 可以避免访问数据页。加上独立的索引文件组，可以用最快速度访问数据。

请看如下表示例：

A.创建简单非聚集索引

以下示例为 Purchasing.ProductVendor 表的 VendorID 列创建非聚集索引。

USE AdventureWorks;

CREATE INDEX IX_ProductVendor_VendorID

ON Purchasing.ProductVendor (VendorID);

B. 创建简单非聚集组合索引

以下示例为 Sales.SalesPerson 表的 SalesQuota 和 SalesYTD 列创建非聚集组合索引。

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD

ON Sales.SalesPerson (SalesQuota, SalesYTD);

C. 创建唯一非聚集索引

以下示例为 Production.UnitMeasure 表的 Name 列创建唯一的非聚集索引。该索引将强制插入 Name

列中的数据具有唯一性。

USE AdventureWorks;

CREATE UNIQUE INDEX AK_UnitMeasure_Name

ON Production.UnitMeasure(Name);

无论何时对基础数据执行插入、更新或删除操作，SQL Server 2005

数据库引擎都会自动维护索引。随着时间的推移，这些修改可能会导致索引中的信息分散在数据库中（含有碎

片）。当索引包含的页中的逻辑排序（基于键值）与数据文件中的物理排序不匹配时，就存在碎片。碎片非常

多的索引可能会降低查询性能，导致应用程序响应缓慢。这个时候，我们需要做得就是重新组织和重新生成索

引。重新生成索引将删除该索引并创建一个新索引。此过程中将删除碎片，通过使用指定的或现有的填充因子

设置压缩页来回收磁盘空间，并在连续页中对索引行重新排序（根据需要分配新页）。这样可以减少获取所请

求数据所需的页读取数，从而提高磁盘性能。

可以使用下列方法重新生成聚集索引和非聚集索引：

带 REBUILD 子句的 ALTER INDEX。此语句将替换 DBCC DBREINDEX 语句。

带 DROP_EXISTING 子句的 CREATE INDEX。

示例如下：

A. 重新生成索引

以下示例将重新生成单个索引。

of 5

40墨值下载

database

关注

评论