快捷搜索:

SQL Server和Oracle中的索引(1)

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、非凑集索引的叶层是由索引页而不是由数据页组成。

设计非凑集索引时必要留意数据库的特性:

更新要求较低但包孕大年夜量数据的数据库或表可以从许多非凑集索引中获益从而改良查询机能。

决策支持系统利用法度榜样和主要包孕只读数据的数据库可以从许多非凑集索引中获益。查询优化用具有更多可供选择的索引用来确定最快的造访措施,并且数据库的低更新特性意味着索引掩护不会低落机能。

联机事务处置惩罚利用法度榜样和包孕大年夜量更新表的数据库应避免应用过多的索引。此外,索引应该是窄的,即列越少越好。

可以应用下列措施从新天生凑集索引和非凑集索引:

带 REBUILD 子句的 alter INDEX。此语句将调换 DBCC DBREINDEX 语句。

带 drop_EXISTING 子句的 create INDEX。

示例如下:

A. 从新天生索引

以下示例将从新天生单个索引。

USEAdventureWorks;

GO

alterINDEXPK_Employee_EmployeeIDONHumanResources.Employee

REBUILD;

GO

B.从新天生表的所有索引并指定选项

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

alterINDEXALLONProduction.Product

REBUILDWITH(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, 它是行的位置;第二列是正被索引列的值。

由于复合索引中列的顺序很紧张,以是确信以最有效的索引能排列他们,可以参考用作列排序的下面的两个准则 :

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)往返通报这个块来实现共享,互连是两个(或多个)机械之间的一条专用收集连接。假如某个使用一个序列添补,这个列上有一个主键索引 ,那么每小我插入新值时,都邑视图改动今朝索引布局右侧的左块(见本文图一,此中显示出索引中较高的值都放在右侧,而较低的值放在左侧)。假如对用序列添补的列上的索引进行改动,就会凑集在很少的一组叶子块上。假使将索引的键反转,对索引进行插入时,就能在索引中的所有叶子键上散播开(不过这每每会使索引不能获得充分地添补)。

您可能还会对下面的文章感兴趣: