SQL Server和Oracle数据库索引介绍

您当前所在位置：首页 → 数据库 → Oracle → SQL Server和Oracle数据库索引介绍

SQL Server和Oracle数据库索引介绍 时间：2015/6/28来源：绿色软件之家作者：网管联盟我要评论(0)

　　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. 重新生成索引

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

　　USE AdventureWorks;

　　ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee

　　REBUILD;

　　B.重新生成表的所有索引并指定选项

　　下面的示例指定了 ALL 关键字。这将重新生成与表相关联的所有索引。其中指定了三个选项。

　　ALTER INDEX ALL ON Production.Product

　　REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,

　　STATISTICS_NORECOMPUTE = ON);

　　2 oracle 中的索引

　　索引是Oracle使用的加速表中数据检索的数据库对象。

　　下面的情况，可以考虑使用索引：

　　1) 大表

　　2) 主键(自动索引)

　　3) 单键列(自动索引)

　　4) 外键列(自动索引)

　　5) 大表上WHERE子句常用的列

　　6) ORDER BY 或者GROUP BY子句中使用的列。

　　7) 至少返回表中20%行的查询

　　8) 不包含null值的列。

#p#副标题#e#

　　Oracle中的索引包含有如下几种类型：

　　B*树索引：这是Oracle中最常用的索引，它的构造类似于二叉树，能根据键提供一行或一个行集的快速访问，通常只需要很少的读操作就能找到正确的行。B*树索引由两列组成，第一列是ROWID, 它是行的位置;第二列是正被索引列的值。

图:典型的B*树索引布局

　　这个树底层的块称为叶子节点(leaf node) 或(leaf block),其中分别包含各个索引键以及一个rowid(它是指向所索引的行)。叶子节点之上的内部块称为分支块(branch block)，这些节点用于实现导航。例如，如果想在索引中找到值20，要从树顶开始，找到左分支，我们检查这个块，并发现需要找到范围"20..25" 的块，这个块将是叶子块，其中会指示包含数20的行。索引的叶子节点实际上构成了一个双向链表。一旦发现要从叶子节点中的那里开始，执行值的有序扫描 (index range scan)就会很容易，我们就不必再在索引结构中导航：而只需根据叶子节点向前或向后扫描就可以了。

　　B*树的特点之一是：所有叶子块都应该在树的同一层上，这一层称之为索引的高度, 它说明所有从索引的根块到叶子块的遍历都会访问同样数目的块。也就是说，对于形如"SELECT INDEX_column FROM TABLE WHERE INXDEX_column =:X"的索引，要达到叶子块来获取第一行，不论使用的:X值是什么，都会执行同样数目的I/O,由此可见B*树的B代表的是balanced，所谓的"Height balanced"。大多数B*树索引的高度都是2或3，即使索引中有数百万行记录也是如此，这说明，一般而言，在索引中找到一个键只需要2到3次I/O , 这确实不错。

　　B*树是一个极佳的通用索引机制，无论是大表还是小表都很适用，随着底层表大小增长，获取数据的性能仅会稍有恶化。

　　比如，我们为customers表建立一个常见的B*树索引:

　　CREATE INDEX IDX_Cus_City on customers(city)

　　B*树索引有以下子类型：

　　复合

关键词标签：SQL Server,Oracle数据

相关阅读

文章评论

查看所有0条评论>>