SQL Server中的索引.docx

SQL Server中的索引.docx

《SQL Server中的索引.docx》由会员分享，可在线阅读，更多相关《SQL Server中的索引.docx（11页珍藏版）》请在冰豆网上搜索。

SQLServer中的索引

1SQLServer中的索引

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

索引包含由表或视图中的一列或多列生成的键。

这些键存储在一个结构（B树）中，使SQLServer可以快速有效地查找与键值关联的行。

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

　　聚集索引

　　聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。

索引定义中包含聚集索引列。

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

　　只有当表包含聚集索引时，表中的数据行才按排序顺序存储。

如果表具有聚集索引，则该表称为聚集表。

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

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

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

　　2、提供高度唯一性。

　　在创建聚集索引之前，应先了解数据是如何被访问的。

考虑对具有以下特点的查询使用聚集索引：

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

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

例如，如果某个查询在一系列采购订单号间检索记录，PurchaseOrderNumber列的聚集索引可快速定位包含起始采购订单号的行，然后检索表中所有连续的行，直到检索到最后的采购订单号。

　　返回大型结果集。

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

　　使用ORDERBY或GROUPBY子句。

　　在ORDERBY或GROUPBY子句中指定的列的索引，可以使数据库引擎不必对数据进行排序，因为这些行已经排序。

这样可以提高查询性能。

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

　　频繁更改的列

　　这将导致整行移动，因为数据库引擎必须按物理顺序保留行中的数据值。

这一点要特别注意，因为在大容量事务处理系统中数据通常是可变的。

　　宽键

　　宽键是若干列或若干大型列的组合。

所有非聚集索引将聚集索引中的键值用作查找键。

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

　非聚集索引

　　非聚集索引具有独立于数据行的结构。

非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。

　　从非聚集索引中的索引行指向数据行的指针称为行定位器。

行定位器的结构取决于数据页是存储在堆中还是聚集表中。

对于堆，行定位器是指向行的指针。

对于聚集表，行定位器是聚集索引键。

　　在SQLServer2005中，可以向非聚集索引的叶级别添加非键列以跳过现有的索引键限制（900字节和16键列），并执行完整范围内的索引查询。

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

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

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

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

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

　　决策支持系统应用程序和主要包含只读数据的数据库可以从许多非聚集索引中获益。

查询优化器具有更多可供选择的索引用来确定最快的访问方法，并且数据库的低更新特征意味着索引维护不会降低性能。

　　联机事务处理应用程序和包含大量更新表的数据库应避免使用过多的索引。

此外，索引应该是窄的，即列越少越好。

　　一个表如果建有大量索引会影响INSERT、UPDATE和DELETE语句的性能，因为所有索引都必须随表中数据的更改进行相应的调整。

　　唯一索引

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

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

　　包含性列索引

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

　　索引涵盖

　　指查询中的SELECT与WHERE子句的所用列同时也属于非聚集索引的情况。

这样就可以更快检索数据，因为所有信息都可以直接来自于索引页，从而SQLServer可以避免访问数据页。

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

　　请看如下表示例：

　　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

　　无论何时对基础数据执行插入、更新或删除操作，SQLServer2005数据库引擎都会自动维护索引。

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

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

碎片非常多的索引可能会降低查询性能，导致应用程序响应缓慢。

这个时候，我们需要做得就是重新组织和重新生成索引。

重新生成索引将删除该索引并创建一个新索引。

此过程中将删除碎片，通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间，并在连续页中对索引行重新排序（根据需要分配新页）。

这样可以减少获取所请求数据所需的页读取数，从而提高磁盘性能。

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

　　带REBUILD子句的ALTERINDEX。

此语句将替换DBCCDBREINDEX语句。

　　带DROP_EXISTING子句的CREATEINDEX。

　　示例如下：

　　A.重新生成索引

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

     USEAdventureWorks;

　　GO

　　ALTERINDEXPK_Employee_EmployeeIDONHumanResources.Employee

　　REBUILD;

　　GO

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

　　下面的示例指定了ALL关键字。

这将重新生成与表相关联的所有索引。

其中指定了三个选项。

     ALTERINDEXALLONProduction.Product

　　REBUILDWITH（FILLFACTOR=80,SORT_IN_TEMPDB=ON,

　　STATISTICS_NORECOMPUTE=ON）;

　　GO

     2Oracle中的索引

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

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

　　1）大表

　　2）主键（自动索引）

　　3）单键列（自动索引）

　　4）外键列（自动索引）

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

　　6）ORDERBY或者GROUPBY子句中使用的列。

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

　　8）不包含null值的列。

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

　　B*树索引：

这是Oracle中最常用的索引，它的构造类似于二叉树，能根据键提供一行或一个行集的快速访问，通常只需要很少的读操作就能找到正确的行。

B*树索引由两列组成，第一列是ROWID,它是行的位置;第二列是正被索引列的值。

　　图:

典型的B*树索引布局

　　这个树底层的块称为叶子节点（leafnode）或（leafblock）,其中分别包含各个索引键以及一个rowid（它是指向所索引的行）。

叶子节点之上的内部块称为分支块（branchblock），这些节点用于实现导航。

例如，如果想在索引中找到值20，要从树顶开始，找到左分支，我们检查这个块，并发现需要找到范围"20..25"的块，这个块将是叶子块，其中会指示包含数20的行。

索引的叶子节点实际上构成了一个双向链表。

一旦发现要从叶子节点中的那里开始，执行值的有序扫描（indexrangescan）就会很容易，我们就不必再在索引结构中导航：

而只需根据叶子节点向前或向后扫描就可以了。

　　B*树的特点之一是：

所有叶子块都应该在树的同一层上，这一层称之为索引的高度,它说明所有从索引的根块到叶子块的遍历都会访问同样数目的块。

也就是说，对于形如"SELECTINDEX_columnFROMTABLEWHEREINXDEX_column=:

X"的索引，要达到叶子块来获取第一行，不论使用的:

X值是什么，都会执行同样数目的I/O,由此可见B*树的B代表的是balanced，所谓的"Heightbalanced"。

大多数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*树索引的目的是为了减少“右侧”索引中对索引叶子块的竞争，比如在一个OracleRAC环境中，某些列用一个序列值或时间戳填充，这些列上建立的索引就属于“右侧”（right-hand-side）索引。

　　RAC是一种Oracle配置，其中多个实例可以装载和打开同一个数据库。

如果两个实例需要同时修改同一个数据块，它们会通过一个硬件互连（interconnect）来回传递这个块来实现共享，互连是两个（或多个）机器之间的一条专用网络连接。

如果某个利用一个序列填充，这个列上有一个主键索引，那么每个人插入新值时，都会视图修改目前索引结构右侧的左块（见本文图一，其中显示出索引中较高的值都放在右侧，而较低的值放在左侧）。

如果对用序列填充的列上的索引进行修改，就会聚集在很少的一组叶子块上。

倘若将索引的键反转，对索引进行插入时，就能在索引中的所有叶子键上分布开（不过这往往会使索引不能得到充分地填充）。

　　反向键索引创建语句语法如下：

     CREATE INDEX index_name on table_name（column_name） REVERSE ;

　　降序索引

　　降序索引（descendingindex）是oracle8i引入的，用以扩展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）

　　位图索引

　　位图索引（bitmapindex）是从Oracle7.3版本开始引入的。

目前Oracle企业版和个人版都支持位图索引，但标准版不支持。

位图索引是为数据仓库/在线分析查询环境设计的，在此所有查询要求的数据在系统实现时根本不知道。

位图索引特别不适用于OLTP系统，如果系统中的数据会由多个并发会话频繁地更新，这种系统也不适用位图索引。

　　位图索引是这样一种结构，其中用一个索引键条目存储指向多行的指针;这与B*树结构不同，在B*树结构中，索引键和表中的行存在着对应关系。

在位图索引中，可能只有很少的索引条目，每个索引条目指向多行。

而在传统的B*树中，一个索引条目就指向一行。

　　B*树索引一般来讲应当是选择性的。

与之相反，位图索引不应是选择性的，一般来讲它们应该“没有选择性“。

如果有大量在线分析查询，特别是查询以一种即席方式引用了多列或者会生成诸如COUNT之类的聚合，在这样的环境中，位图索引就特别有用。

位图索引使用CREATEBITMAPINDEXindex_nameONtable_name（column_name1,column_name2）TABLESPACEtablespace_name命令语法创建。

关于SQLServer中索引使用及维护简介

2009-11-25    作者：

  转载自：

赛迪网技术社区    编辑：

乐乐  点击进入论坛

关键词：

SQLServer  索引

　　在应用系统中，尤其在联机事务处理系统中,对数据查询及处理速度已成为衡量应用系统成败的标准。

而采用索引来加快数据处理速度也成为广大数据库用户所接受的优化方法。

　　在良好的数据库设计基础上，能有效地使用索引是SQLServer取得高性能的基础，SQLServer采用基于代价的优化模型，它对每一个提交的有关表的查询，决定是否使用索引或用哪一个索引。

因为查询执行的大部分开销是磁盘I/O，使用索引提高性能的一个主要目标是避免全表扫描，因为全表扫描需要从磁盘上读表的每一个数据页，如果有索引指向数据值，则查询只需读几次磁盘就可以了。

所以如果建立了合理的索引，优化器就能利用索引加速数据的查询过程。

但是，索引并不总是提高系统的性能，在增、删、改操作中索引的存在会增加一定的工作量，因此，在适当的地方增加适当的索引并从不合理的地方删除次优的索引，将有助于优化那些性能较差的SQLServer应用。

实践表明，合理的索引设计是建立在对各种查询的分析和预测上的，只有正确地使索引与程序结合起来,才能产生最佳的优化方案。

本文就SQLServer索引的性能问题进行了一些分析和实践。

　　一、聚簇索引（clusteredindexes）的使用

　　聚簇索引是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。

由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。

每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。

建立聚簇索引的思想是：

　　1、大多数表都应该有聚簇索引或使用分区来降低对表尾页的竞争，在一个高事务的环境中，对最后一页的封锁严重影响系统的吞吐量。

　　2、在聚簇索引下，数据在物理上按顺序排在数据页上，重复值也排在一起，因而在那些包含范围检查（between、<、<=、>、>=）或使用groupby或orderby的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行保证物理上毗连在一起而不必进一步搜索，避免了大范围扫描，可以大大提高查询速度。

　　3、在一个频繁发生插入操作的表上建立聚簇索引时，不要建在具有单调上升值的列（如IDENTITY）上，否则会经常引起封锁冲突。

　　4、在聚簇索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置。

　　5、选择聚簇索引应基于where子句和连接操作的类型。

　　聚簇索引的侯选列是：

　　1、主键列，该列在where子句中使用并且插入是随机的。

　　2、按范围存取的列，如pri_order>100andpri_order<200。

　　3、在groupby或orderby中使用的列。

　　4、不经常修改的列。

　　5、在连接操作中使用的列。

　　二、非聚簇索引（nonclusteredindexes）的使用

　　SQLServer缺省情况下建立的索引是非聚簇索引，由于非聚簇索引不重新组织表中的数据，而是对每一行存储索引列值并用一个指针指向数据所在的页面。

换句话说非聚簇索引具有在索引结构和数据本身之间的一个额外级。

一个表如果没有聚簇索引时,可有250个非聚簇索引。

每个非聚簇索引提供访问数据的不同排序顺序。

在建立非聚簇索引时，要权衡索引对查询速度的加快与降低修改速度之间的利弊。

另外，还要考虑这些问题：

　　1、索引需要使用多少空间。

　　2、合适的列是否稳定。

　　3、索引键是如何选择的，扫描效果是否更佳。

　　4、是否有许多重复值。

　　对更新频繁的表来说，表上的非聚簇索引比聚簇索引和根本没有索引需要更多的额外开销。

对移到新页的每一行而言，指向该数据的每个非聚簇索引的页级行也必须更新，有时可能还需要索引页的分理。

从一个页面删除数据的进程也会有类似的开销，另外，删除进程还必须把数据移到页面上部，以保证数据的连续性。

所以，建立非聚簇索引要非常慎重。

非聚簇索引常被用在以下情况:

　　1、某列常用于集合函数（如Sum,....）。

　　2、某列常用于join,orderby,groupby。

　　3、查寻出的数据不超过表中数据量的20%。

　　三、覆盖索引（coveringindexes）的使用

　　覆盖索引是指那些索引项中包含查寻所需要的全部信息的非聚簇索引，这种索引之所以比较快也正是因为索引页中包含了查寻所必须的数据,不需去访问数据页。

如果非聚簇索引中包含结果数据,那么它的查询速度将快于聚簇索引。

　　但是由于覆盖索引的索引项比较多,要占用比较大的空间。

而且update操作会引起索引值改变。

所以如果潜在的覆盖查询并不常用或不太关键，则覆盖索引的增加反而会降低性能。

　　四、索引的选择技术

　　p_detail是住房公积金管理系统中记录个人明细的表，有890000行，观察在不同索引下的查询运行效果，测试在C/S环境下进行，客户机是IBMPII350（内存64M）,服务器是DECAlpha1000A（内存128M）,数据库为SYBASE11.0.3。

　　1、selectcount（*）fromp_detailwhereop_date>’19990101’andop_date<’19991231’andpri_surplus1>3002、selectcount（*）,sum（pri_surplus1）fromp_detailwhereop_date>’19990101’andpay_monthbetween‘199908’and’199912’不建任何索引查询11分15秒查询21分7秒在op_date上建非聚簇索引查询157秒查询257秒在op_date上建聚簇索引查询1<1秒查询252秒在pay_month、op_date、pri_surplus1上建索引查询134秒查询2<1秒在op_date、pay_month、pri_surplus1上建索引查询1<1秒查询2<1秒

　　从以上查询效果分析，索引的有无