SQL Server和Oracle中的索引

【51CTO.com独家特稿】本文介绍了SQL Server和Oracle的索引及常见的索引类型，希望通过比较的方法，加深对二种数据库索引应用的理解。

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;GO CREATE INDEX IX_ProductVendor_VendorID     ON Purchasing.ProductVendor (VendorID); GO

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

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD    ON Sales.SalesPerson (SalesQuota, SalesYTD);GO

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

USE AdventureWorks;GO CREATE UNIQUE INDEX AK_UnitMeasure_Name     ON Production.UnitMeasure(Name);GO

 

无论何时对基础数据执行插入、更新或删除操作，SQL Server 2005 数据库引擎都会自动维护索引。随着时间的推移，这些修改可能会导致索引中的信息分散在数据库中（含有碎片）。当索引包含的页中的逻辑排序（基于键值）与数据文件中的物理排序不匹配时，就存在碎片。碎片非常多的索引可能会降低查询性能，导致应用程序响应缓慢。这个时候，我们需要做得就是重新组织和重新生成索引。重新生成索引将删除该索引并创建一个新索引。此过程中将删除碎片，通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间，并在连续页中对索引行重新排序（根据需要分配新页）。这样可以减少获取所请求数据所需的页读取数，从而提高磁盘性能。

可以使用下列方法重新生成聚集索引和非聚集索引：
带 REBUILD 子句的 ALTER INDEX。此语句将替换 DBCC DBREINDEX 语句。
带 DROP_EXISTING 子句的 CREATE INDEX。
 
示例如下：

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

USE AdventureWorks;GOALTER INDEX PK_Employee_EmployeeID ON HumanResources.EmployeeREBUILD;GO

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

ALTER INDEX ALL ON Production.ProductREBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,              STATISTICS_NORECOMPUTE = ON);GO

2  Oracle 中的索引

  索引是Oracle使用的加速表中数据检索的数据库对象。
  下面的情况，可以考虑使用索引：
1） 大表
2） 主键（自动索引）
3） 单键列（自动索引）
4） 外键列（自动索引）
5） 大表上WHERE子句常用的列
6） ORDER BY 或者GROUP BY子句中使用的列。
7） 至少返回表中20%行的查询
8） 不包含null值的列。
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*树索引有以下子类型：

复合索引

复合索引也是一种B*树索引,它由多列组成。当我们拥有使用两列或超过两列的频繁查询时，就使用B*树复合索引，而其所使用的两列或多列在where子句中and逻辑操作符连接。

因为复合索引中列的顺序很重要，所以确信以最有效的索引能排列他们，可以参考用作列排序的下面的两个准则 ：

1） 前导列应该是查询中使用最频繁的列。
2） 前导列应该是选择最多的列，这意味着它比后面的列具有更高的基数。

复合索引在下列情况中具有优势：
 
1）假定在WHERE子句中频繁使用下面的条件：order_status_id = 1 和order_date = ‘dd-mon-yyyy’。如果为每一列创建一个索引，那么为了搜索列的值，两个索引都要被读取，但是如果为两列都创建一个复合索引，那么只有一个索引被读取，这样无疑比两个索引要求更少的I/O。

2) 使用前面例子中同样的条件，如果创建一个复合索引，将更快地检索行，因为你正在排除了所有order_status_id 不是1的行，从而减少了搜索order_date的行数。

反向键索引

B*树索引的另一个特点是能够将索引键“反转”。首先，你可以问问自己“为什么想这么做？” B*树索引是为特定的环境、特定的问题而设计的。实现B*树索引的目的是为了减少“右侧”索引中对索引叶子块的竞争，比如在一个Oracle RAC 环境中，某些列用一个序列值或时间戳填充，这些列上建立的索引就属于“右侧”（right-hand-side）索引。
 
RAC 是一种Oracle 配置，其中多个实例可以装载和打开同一个数据库。如果两个实例需要同时修改同一个数据块，它们会通过一个硬件互连（interconnect）来回传递这个块来实现共享，互连是两个（或多个）机器之间的一条专用网络连接。如果某个利用一个序列填充，这个列上有一个主键索引 ，那么每个人插入新值时，都会视图修改目前索引结构右侧的左块（见本文图一，其中显示出索引中较高的值都放在右侧，而较低的值放在左侧）。如果对用序列填充的列上的索引进行修改，就会聚集在很少的一组叶子块上。倘若将索引的键反转，对索引进行插入时，就能在索引中的所有叶子键上分布开（不过这往往会使索引不能得到充分地填充）。
反向键索引创建语句语法如下：

CREATE INDEX index_name on table_name(column_name) REVERSE ;

降序索引

降序索引（descending index）是oracle 8i引入的，用以扩展B*树索引的功能，它允许在索引中以降序（从大到小的顺序）存储一列。在oracle8i及以上版本中，DESC关键字确实会改变创建和使用索引的的方式。

我们可以这样创建降序索引:

CREATE INDEX IDX_jobs_title on hr.jobs (job_title DESC);SET autotrace traceonly EXPLAIN;SELECT  * FROM  hr.jobs WHERE job_title Between 'a' AND 'ZZZZZZZZZZZ ';Execution Plan----------------------------------------------------------   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=33)   1    0   FILTER   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'JOBS' (Cost=1 Card=1 B          ytes=33)   3    2       INDEX (RANGE SCAN) OF 'IDX_JOBS_TITLE' (NON-UNIQUE) (C          ost=2 Card=1)SQL> SELECT * from hr.jobs   2  WHERE job_title between 'a' and 'ZZZZZZZZZZZ ';Execution Plan----------------------------------------------------------   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=33)   1    0   FILTER   2    1     TABLE ACCESS (FULL) OF 'JOBS' (Cost=2 Card=1 Bytes=33)SQL>   DROP INDEX IDX_jobs_title ;SQL>   CREATE INDEX IDX_jobs_title on hr.jobs (job_title ); SQL> Select * FROM hr.jobs   2  Where job_title between 'a' and 'ZZZZZZZZZZZ ';Execution Plan----------------------------------------------------------   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=33)   1    0   FILTER   2    1     TABLE ACCESS (FULL) OF 'JOBS' (Cost=2 Card=1 Bytes=33)

位图索引

位图索引（bitmap index）是从Oracle7.3 版本开始引入的。目前Oracle 企业版和个人版都支持位图索引，但标准版不支持。位图索引是为数据仓库/在线分析查询环境设计的，在此所有查询要求的数据在系统实现时根本不知道。位图索引特别不适用于OLTP 系统，如果系统中的数据会由多个并发会话频繁地更新，这种系统也不适用位图索引。

位图索引是这样一种结构，其中用一个索引键条目存储指向多行的指针；这与B*树结构不同，在B*树结构中，索引键和表中的行存在着对应关系。在位图索引中，可能只有很少的索引条目，每个索引条目指向多行。而在传统的B*树中，一个索引条目就指向一行。

B*树索引一般来讲应当是选择性的。与之相反，位图索引不应是选择性的，一般来讲它们应该“没有选择性“。如果有大量在线分析查询，特别是查询以一种即席方式引用了多列或者会生成诸如COUNT 之类的聚合，在这样的环境中，位图索引就特别有用 。位图索引使用 CREATE BITMAP INDEX index_name ON table_name(column_name1,column_name2) TABLESPACE tablespace_name命令语法创建。