SQL Server和Oracle数据库索引介绍

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 列创建非聚集索引。　

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

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

C. 创建唯一非聚集索引

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

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

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

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

带 DROP_EXISTING 子句的CREATE INDEX。

示例如下：

A. 重新生成索引 

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

      USE AdventureWorks; 　　GO 　　ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee　　REBUILD; 　　GO 

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

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

      ALTER INDEX ALL ON Production.Product 　　REBUILD 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*树索引:　

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)来回传递这 个块来实现共享，互连是两个(或多个)机器之间的一条专用网络连接。如果某个利用一个序列填充，这个列上有一个主键索引 ，那么每个人插入新值时，都会视图修改目前索引结构右侧的左块(见本文图一，其中显示出索引中较高的值都放在右侧，而较低的值放在左侧)。如果对用序列填 充的列上的索引进行修改，就会聚集在很少的一组叶子块上。倘若将索引的键反转，对索引进行插入时，就能在索引中的所有叶子键上分布开(不过这往往会使索引 不能得到充分地填充)。

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

降序索引

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

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

位图索引

位图索引(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命令语法创建。

SQL Server查询优化技术及索引

From: http://www.cnblogs.com/lovewindy/archive/2005/02/19/105959.html

在《数据库原理》里面，对聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。正式因为如此，所以一个表最多只能有一个聚簇索引。

不过这个定义太抽象了。在SQL Server中，索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。如下图：

 非聚簇索引 

 聚簇索引 

聚簇索引与非聚簇索引的本质区别到底是什么？什么时候用聚簇索引，什么时候用非聚簇索引？ 

这是一个很复杂的问题，很难用三言两语说清楚。我在这里从SQL Server索引优化查询的角度简单谈谈(如果对这方面感兴趣的话，可以读一读微软出版的《Microsoft SQL Server 2000数据库编程》第3单元的数据结构引论以及第6、13、14单元)。

一、索引块与数据块的区别 

大家都知道，索引可以提高检索效率，因为它的二叉树结构以及占用空间小，所以访问速度块。让我们来算一道数学题：如果表中的一条记录在磁盘上占用1000字节的话，我们对其中10字节的一个字段建立索引，那么该记录对应的索引块的大小只有10字节。我们知道，SQL Server的最小空间分配单元是“页（Page）”，一个页在磁盘上占用8K空间，那么这一个页可以存储上述记录8条，但可以存储索引800条。现在我们要从一个有8000条记录的表中检索符合某个条件的记录，如果没有索引的话，我们可能需要遍历8000条×1000字节/8K字节=1000个页面才能够找到结果。如果在检索字段上有上述索引的话，那么我们可以在8000条×10字节/8K字节=10个页面中就检索到满足条件的索引块，然后根据索引块上的指针逐一找到结果数据块，这样IO访问量要少的多。

二、索引优化技术 

是不是有索引就一定检索的快呢？答案是否。有些时候用索引还不如不用索引快。比如说我们要检索上述表中的所有记录，如果不用索引，需要访问8000条×1000字节/8K字节=1000个页面，如果使用索引的话，首先检索索引，访问8000条×10字节/8K字节=10个页面得到索引检索结果，再根据索引检索结果去对应数据页面，由于是检索所有数据，所以需要再访问8000条×1000字节/8K字节=1000个页面将全部数据读取出来，一共访问了1010个页面，这显然不如不用索引快。

SQL Server内部有一套完整的数据检索优化技术，在上述情况下，SQL Server的查询计划（Search Plan）会自动使用表扫描的方式检索数据而不会使用任何索引。那么SQL Server是怎么知道什么时候用索引，什么时候不用索引的呢？SQL Server除了日常维护数据信息外，还维护着数据统计信息，下图是数据库属性页面的一个截图：

从图中我们可以看到，SQL Server自动维护统计信息，这些统计信息包括数据密度信息以及数据分布信息，这些信息帮助SQL Server决定如何制定查询计划以及查询是是否使用索引以及使用什么样的索引（这里就不再解释它们到底如何帮助SQL Server建立查询计划的了）。我们还是来做个实验。建立一张表：tabTest(ID, unqValue，intValue)，其中ID是整形自动编号主索引，unqValue是uniqueidentifier类型，在上面建立普通索引，intValue是整形，不建立索引。之所以挂上一个没有索引的intValue字段，就是防止SQL Server使用索引覆盖查询优化技术，这样实验就起不到作用了。向表中录入10000条随机记录，代码如下： 

 CREATE TABLE [dbo].[tabTest] (  [ID] [int] IDENTITY (1, 1) NOT NULL ,  [unqValue] [uniqueidentifier] NOT NULL ,  [intValue] [int] NOT NULL  ) ON [PRIMARY] GO   ALTER TABLE [dbo].[tabTest] WITH NOCHECK ADD   CONSTRAINT [PK_tabTest] PRIMARY KEY  CLUSTERED   (    [ID]   )  ON [PRIMARY]  GO   ALTER TABLE [dbo].[tabTest] ADD   CONSTRAINT [DF_tabTest_unqValue] DEFAULT (newid()) FOR [unqValue] GO   CREATE  INDEX [IX_tabTest_unqValue] ON [dbo].[tabTest]([unqValue]) ON [PRIMARY] GO   declare @i int declare @v int  set @i=0 while @i<10000 begin      set @v=rand()*1000         insert into tabTest ([intValue]) values (@v)     set @i=@i+1 end   

然后我们执行两个查询并查看执行计划，如图：（在查询分析器的查询菜单中可以打开查询计划，同时图上第一个查询的GUID是我从数据库中找的，大家做实验的时候可以根据自己数据库中的值来定）：

 从图中可以看出，在第一个查询中，SQL Server使用了IX_tabTest_unqValue索引，根据箭头方向，计算机先在索引范围内找，找到后，使用Bookmark Lookup将索引节点映射到数据节点上，最后给出SELECT结果。在第二个查询中，系统直接遍历表给出结果，不过它使用了聚簇索引，为什么呢？不要忘了，聚簇索引的页节点就是数据节点！这样使用聚簇索引会更快一些（不受数据删除、更新留下的存储空洞的影响，直接遍历数据是要跳过这些空洞的）。 

下面，我们在SQL Server中将ID字段的聚簇索引更改为非聚簇索引，然后再执行select * from tabTest，这回我们看到的执行计划变成了：

SQL Server没有使用任何索引，而是直接执行了Table Scan，因为只有这样，检索效率才是最高的。

三、聚簇索引与非聚簇索引的本质区别 

现在可以讨论聚簇索引与非聚簇索引的本质区别了。正如本文最前面的两个图所示，聚簇索引的叶节点就是数据节点，而非聚簇索引的页节点仍然是索引检点，并保留一个链接指向对应数据块。

还是通过一道数学题来看看它们的区别吧：假设有一8000条记录的表，表中每条记录在磁盘上占用1000字节，如果在一个10字节长的字段上建立非聚簇索引主键，需要二叉树节点16000个（这16000个节点中有8000个叶节点，每个页节点都指向一个数据记录），这样数据将占用8000条×1000字节/8K字节=1000个页面；索引将占用16000个节点×10字节/8K字节=20个页面，共计1020个页面。

同样一张表，如果我们在对应字段上建立聚簇索引主键，由于聚簇索引的页节点就是数据节点，所以索引节点仅有8000个，占用10个页面，数据仍然占有1000个页面。

下面我们看看在执行插入操作时，非聚簇索引的主键为什么比聚簇索引主键要快。主键约束要求主键不能出现重复，那么SQL Server是怎么知道不出现重复的呢？唯一的方法就是检索。对于非聚簇索引，只需要检索20个页面中的16000个节点就知道是否有重复，因为所有主键键值在这16000个索引节点中都包含了。但对于聚簇索引，索引节点仅仅包含了8000个中间节点，至于会不会出现重复必须检索另外1000个页数据节点才知道，那么相当于检索10+1000=1010个页面才知道是否有重复。所以聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。

让我们再来看看数据检索的效率，如果对上述两表进行检索，在使用索引的情况下（有些时候SQL Server执行计划会选择不使用索引，不过我们这里姑且假设一定使用索引），对于聚簇索引检索，我们可能会访问10个索引页面外加1000个数据页面得到结果（实际情况要比这个好），而对于非聚簇索引，系统会从20个页面中找到符合条件的节点，再映射到1000个数据页面上（这也是最糟糕的情况），比较一下，一个访问了1010个页面而另一个访问了1020个页面，可见检索效率差异并不是很大。所以不管非聚簇索引也好还是聚簇索引也好，都适合排序，聚簇索引仅仅比非聚簇索引快一点。

结语 

好了，写了半天，手都累了。关于聚簇索引与非聚簇索引效率问题的实验就不做了，感兴趣的话可以自己使用查询分析器对查询计划进行分析。SQL Server是一个很复杂的系统，尤其是索引以及查询优化技术，Oracle就更复杂了。了解索引以及查询背后的事情不是什么坏事，它可以帮助我们更为深刻的了解我们的系统。

SQL Server基础知识之:设计和实现视图 

设计和实现视图可谓是数据库物理设计中的一个非常重要的步骤。从一般意义上说，设计和实现视图应该遵循下面的一些建议和原则。

以下内容摘在文档，我对某些重点进行了补充说明（红色部分）

?  只能在当前数据库中创建视图。 但是，如果使用分布式查询定义视图，则新视图所引用的表和视图可以存在于其他数据库甚至其他服务器中。 

?  视图名称必须遵循标识符的规则，且对每个架构都必须唯一。 此外，该名称不得与该架构包含的任何表的名称相同。 

?  您可以对其他视图创建视图。Microsoft SQL Server允许嵌套视图。但嵌套不得超过 32 层。 根据视图的复杂性及可用内存，视图嵌套的实际限制可能低于该值。 

?  不能将规则或 DEFAULT定义与视图相关联。 

?  不能将 AFTER触发器与视图相关联，只有 INSTEAD OF 触发器可以与之相关联。 

?  定义视图的查询不能包含 COMPUTE子句、COMPUTE BY 子句或INTO 关键字。 

该特性不能用于视图，但可以直接用于查询

?  定义视图的查询不能包含 ORDER BY子句，除非在 SELECT 语句的选择列表中还有一个TOP 子句。 

?  定义视图的查询不能包含指定查询提示的 OPTION 子句。 

?  定义视图的查询不能包含 TABLESAMPLE子句。 

?  不能为视图定义全文索引定义。 

?  不能创建临时视图，也不能对临时表创建视图。 

?  不能删除参与到使用 SCHEMABINDING子句创建的视图中的视图、表或函数，除非该视图已被删除或更改而不再具有架构绑定。 另外，如果对参与具有架构绑定的视图的表执行ALTER TABLE 语句，而这些语句又会影响该视图的定义，则这些语句将会失败。 

?  尽管查询引用一个已配置全文索引的表时，视图定义可以包含全文查询，仍然不能对视图执行全文查询。 

?  下列情况下必须指定视图中每列的名称： 

若要创建视图，您必须获取由数据库所有者授予的此操作执行权限，如果使用 SCHEMABINDING子句创建视图，则必须对视图定义中引用的任何表或视图具有相应的权限。 

默认情况下，由于行通过视图进行添加或更新，当其不再符合定义视图的查询的条件时，它们即从视图范围中消失。 例如，创建一个定义视图的查询，该视图从表中检索员工的薪水低于$30,000 的所有行。如果员工的薪水涨到 $32,000，因其薪水不符合视图所设条件，查询时视图不再显示该特定员工。 但是，WITH CHECK OPTION子句强制所有数据修改语句均根据视图执行，以符合定义视图的 SELECT语句中所设条件。 如果使用该子句，则对行的修改不能导致行从视图中消失。 任何可能导致行消失的修改都会被取消，并显示错误。

本文由作者：陈希章 于2009/6/15 17:31:29 发布在：http://www.cnblogs.com/chenxizhang/本文版权归作者所有，可以转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。 更多博客文章，以及作者对于博客引用方面的完整声明以及合作方面的政策，请参考以下站点：陈希章的博客中心 

绿色通道：好文要顶关注我收藏该文与我联系 

RockYang关注- 6粉丝- 10

+加关注

0

0

(请您对文章做出评价)

?博主前一篇：Web Service?博主后一篇：[转]细说Sql Server中的视图

posted on 2009-12-09 14:48 RockYang 阅读(5330)评论(5) 编辑 收藏

FeedBack: 

#1楼[楼主] 2009-12-09 15:20 RockYang      

浅谈数据库索引数据库索引是为了增加查询速度而对表字段附加的一种标识。见过很多人机械的理解索引的概念，认为增加索引只有好处没有坏处。这里想把之前的索引学习笔记总结一下：首先明白为什么索引会增加速度，DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集 合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。那么在任何时候 都应该加索引么？这里有几个反例：1、如果每次都需要取到所有表记录，无论如何都必须进行全表扫描了，那么是否加索引也没有意义了。2、对非唯一的字段， 例如“性别”这种大量重复值的字段，增加索引也没有什么意义。3、对于记录比较少的表，增加索引不会带来速度的优化反而浪费了存储空间，因为索引是需要存 储空间的，而且有个致命缺点是对于update/insert/delete的每次执行，字段的索引都必须重新计算更新。那么在什么时候适合加上索引呢？我们看一个Mysql手册中举的例子，这里有一条sql语句：SELECT c.companyID, c.companyName FROM Companies c, User u WHERE c.companyID = u.fk_companyID AND c.numEmployees >= 0 AND c.companyName LIKE ‘%i%‘ AND u.groupID IN (SELECT g.groupID FROM Groups g WHERE g.groupLabel = ‘Executive‘)这条语句涉及3个表的联接，并且包括了许多搜索条件比如大小比较，Like匹配等。在没有索引的情况下Mysql需要执行的扫描行数是77721876行。而我们通过在companyID和groupLabel两个字段上加上索引之后，扫描的行数只需要 var cpro_id = "u6292429";

小编还为您整理了以下内容，可能对您也有帮助：

SQL SERVER中索引类型包括的三种类型分别是哪三种？

三种索引类型分别是：

1、主键索引：不允许具有索引值相同的行，从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值，并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。

2、聚集索引：指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况。

3、非聚集索引：索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。非聚集索引的叶层不包含数据页。 相反，叶节点包含索引行。

扩展资料

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。

例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。

频繁更改的列 这将导致整行移动，因为 SQL Server 必须按物理顺序保留行中的数据值。这一点要特别注意，因为在大数据量事务处理系统中数据是易失的。来自聚集索引的键值由所有非聚集索引作为查找键使用，因此存储在每个非聚集索引的叶条目内。

参考资料来源：百度百科-非聚集索引

参考资料来源：百度百科-聚集索引

参考资料来源：百度百科-唯一索引

sql server中索引是什么意思，其什么作用？34

索引是：数据库中的索引类似于一本书的目录，在一本书中使用目录可以快速找到你想要的信息，而不需要读完全书。在数据库中，数据库程序使用索引可以重啊到表中的数据，而不必扫描整个表。书中的目录是一个字词以及各字词所在的页码列表，数据库中的索引是表中的值以及各值存储位置的列表。
　　索引的用途：我们对数据查询及处理速度已成为衡量应用系统成败的标准，而采用索引来加快数据处理速度通常是最普遍采用的优化方法。
　　索引的利弊：查询执行的大部分开销是I/O，使用索引提高性能的一个主要目标是避免全表扫描，因为全表扫描需要从磁盘上读取表的每一个数据页，如果有索引指向数据值，则查询只需要读少数次的磁盘就行啦。所以合理的使用索引能加速数据的查询。但是索引并不总是提高系统的性能，带索引的表需要在数据库中占用更多的存储空间，同样用来增删数据的命令运行时间以及维护索引所需的处理时间会更长。所以要合理使用索引，及时更新去除次优索引。

sql server中索引是什么意思，其什么作用？34

索引是：数据库中的索引类似于一本书的目录，在一本书中使用目录可以快速找到你想要的信息，而不需要读完全书。在数据库中，数据库程序使用索引可以重啊到表中的数据，而不必扫描整个表。书中的目录是一个字词以及各字词所在的页码列表，数据库中的索引是表中的值以及各值存储位置的列表。
　　索引的用途：我们对数据查询及处理速度已成为衡量应用系统成败的标准，而采用索引来加快数据处理速度通常是最普遍采用的优化方法。
　　索引的利弊：查询执行的大部分开销是I/O，使用索引提高性能的一个主要目标是避免全表扫描，因为全表扫描需要从磁盘上读取表的每一个数据页，如果有索引指向数据值，则查询只需要读少数次的磁盘就行啦。所以合理的使用索引能加速数据的查询。但是索引并不总是提高系统的性能，带索引的表需要在数据库中占用更多的存储空间，同样用来增删数据的命令运行时间以及维护索引所需的处理时间会更长。所以要合理使用索引，及时更新去除次优索引。

Oracle数据库中的索引详解

　　 一 ROWID的概念

　　存储了row在数据文件中的具 *** 置 位编码的数据 A Z a z + 和 /

　　row在数据块中的存储方式

　　SELECT ROWID last_name FROM hr employees WHERE department_id = ;

　　比如 OOOOOOFFFBBBBBBRRR

　　OOOOOO data object number 对应dba_objects data_object_id

　　FFF file# 对应v$datafile file#

　　BBBBBB block#

　　RRR row#

　　Dbms_rowid包

　　SELECT dbms_rowid rowid_block_number( AAAGFqAABAAAIWEAAA ) from al;

　　具体到特定的物理文件

　　 二 索引的概念

　　 类似书的目录结构

　　 Oracle 的 索引 对象 与表关联的可选对象 提高SQL查询语句的速度

　　 索引直接指向包含所查询值的行的位置 减少磁盘I/O

　　 与所索引的表是相互的物理结构

　　 Oracle 自动使用并维护索引 插入 删除 更新表后 自动更新索引

　　 语法 CREATE INDEX index ON table (column[ column] );

　　 B tree结构(非bitmap)

　　[一]了解索引的工作原理

　　表 emp

　　目标 查询Frank的工资salary

　　建立索引 create index emp_name_idx on emp(name); 

　　 运行/rdbms/admin/utlxplan 脚本

　　 建立测试表

　　create table t as select * from dba_objects;

　　insert into t select * from t;

　　create table indextable

　　as select rownum id owner object_name subobject_name

　　object_id data_object_id object_type created

　　from t;

　　 set autotrace trace explain

　　 set timing on

　　 分析表 可以得到cost

　　 查询 object_name= DBA_INDEXES

　　 在object_name列上建立索引

　　 再查询

　　[思考]索引的代价

　　插入 更新

　　 　三 唯一索引

　　 何时创建 当某列任意两行的值都不相同

　　 当建立Primary Key(主键)或者Unique constraint(唯一约束)时 唯一索引将被自动建立

　　 语法 CREATE UNIQUE INDEX index ON table (column);

　　 演示

　　 四 组合索引

　　 何时创建 当两个或多个列经常一起出现在where条件中时 则在这些列上同时创建组合索引

　　 组合索引中列的顺序是任意的 也无需相邻 但是建议将最频繁访问的列放在列表的最前面

　　 演示(组合列 单独列)

　　 　五 位图索引

　　 何时创建

　　列中有非常多的重复的值时候 例如某列保存了 性别 信息

　　Where 条件中包含了很多OR操作符

　　较少的update操作 因为要相应的跟新所有的bitmap

　　 结构 位图索引使用位图作为键值 对于表中的每一数据行位图包含了TRUE( ) FALSE( ) 或NULL值

　　 优点 位图以一种压缩格式存放 因此占用的磁盘空间比标准索引要小得多

　　 语法 CREATE BITMAP INDEX index ON table (column[ column] );

　　 掩饰

　　create table bitmaptable as select * from indextable where owner in( SYS PUBLIC );

　　分析 查找 建立索引 查找

　　 　六 基于函数的索引

　　 何时创建 在WHERE条件语句中包含函数或者表达式时

　　 函数包括 算数表达式 PL/SQL函数 程序包函数 SQL函数 用户自定义函数

　　 语法 CREATE INDEX index ON table (FUNCTION(column));

　　 演示

　　必须要分析表 并且query_rewrite_enabled=TRUE

　　或者使用提示/*+ INDEX(ic_index)*/

　　 　　七 反向键索引

　　目的 比如索引值是一个自动增长的列

　　多个用户对集中在少数块上的索引行进行修改 容易引起资源的争用 比如对数据块的等待 此时建立反向索引

　　性能问题

　　语法

　　重建为标准索引 反之不行

　　 　八 键压缩索引

　　比如表landscp的数据如下

　　site feature job

　　Britten Park Rose Bed Prune

　　Britten Park Rose Bed Mulch

　　Britten Park Rose Bed Spray

　　Britten Park Shrub Bed Mulch

　　Britten Park Shrub Bed Weed

　　Britten Park Shrub Bed Hoe

　　……

　　查询时 以上 列均在where条件中同时出现 所以建立基于以上 列的组合索引 但是发现重复值很多 所以考虑压缩特性

　　Create index zip_idx

　　on landscp(site feature job)

　　press ;

　　将索引项分成前缀(prefix)和后缀(postfix)两部分 前两项被放置到前缀部分

　　Prefix : Britten Park Rose Bed

　　Prefix : Britten Park Shrub Bed

　　实际所以的结构为

　　 Prune

　　 Mulch

　　 Spray

　　 Mulch

　　 Weed

　　 Hoe

　　特点 组合索引的前缀部分具有非选择性时 考虑使用压缩 减少I/O 增加性能

　　 　　九 索引组织表(IOT)

　　将表中的数据按照索引的结构存储在索引中 提高查询速度

　　牺牲插入更新的性能 换取查询性能 通常用于数据仓库 提供大量的查询 极少的插入修改工作

　　必须指定主键 插入数据时 会根据主键列进行B树索引排序 写入磁盘

　　 　十 分区索引

　　簇:

　　A cluster is a group of tables that share the same data blocks because they share mon columns and are often used together

SQLServer 和 Oracle 有什么区别？

ORACLE与SQL SERVER的区别

体系结构

ORACLE的文件体系结构为：

数据文件 .DBF （真实数据）

日志文件 .RDO

控制文件 .CTL

参数文件 .ORA

SQL SERVER的文件体系结构为：

.MDF （数据字典）

.NDF （数据文件）

.LDF （日志文件）

ORACLE存储结构：

在ORACLE里有两个块参数PCTFREE(填充因子)和PCTUSED(复用因子),可控制块确定块本身何时有，何时没有足够的空间接受新信息(对块的存储情况的分析机制)

这样可降低数据行连接与行迁移的可能性。块的大小可设置(OLTP块和DSS块)

在ORACLE中，将连续的块组成区，可动态分配区(区的分配可以是等额的也可以是自增长的)可减少空间分配次数

在ORACLEl里表可以分为多个段，段由多个区组成，每个段可指定分配在哪个表空间里(段的类型分为：数据段、索引段、回滚段、临时段、CASH段。ORACLE里还可对表进行分区，可按照用户定义的业务规则、条件或规范，物理的分开磁盘上的数据。

这样大大降低了磁盘争用的可能性。

ORACLE有七个基本表空间：

SYSTEM表空间(存放数据字典和数据管理自身所需的信息)

RBS回滚表空间

TEMP临时表空间

TOOLS交互式表空间

USERS用户默认表空间

INDX索引表空间

DBSYS福数据表空间

不同的数据分别放在不同的表空间(数据字典与真实数据分开存放),在ORACLE里基表(存储系统参数信息)是加密存储，任何人都无法访问。只能通过用户可视视图查看。

SQL SERVER 存储结构

以页为最小分配单位，每个页为8K(不可控制，缺乏对页的存储情况的分析机制),

可将8个连续的页的组成一个‘扩展’，以进一步减少分配时所耗用的资源。(分配缺乏灵活性)，在SQL SERVER里数据以表的方式存放，而表是存放在数据库里。

SQL SERVER有五个基本数据库：

master(数据字典)

mode(存放样版)

tempdb(临时数据库)

msdb(存放调度信息和日志信息)

pubs(示例数据库)

真实数据与数据字典存放在一起。对系统参数信息无安全机制。

ORACLE登入管理：

SYSTEM/MANAGER (初始帐户)

SYS/CHANGE_ON_NSTALL

INSTALL/ORACLE(安装帐户)

SCOTT/TIGER(示例数据库,测试用)

在ORACLE里默认只有三个系统用户，ORACLE是通过用户登入。

SQL SERVER登入管理：

SQL SERVER身份验证

WINDOWS 身份验证

在SQL SERVER里是通过WINDOWS用户帐户或是用SQL SERVER身份验证连接数据库的。

SQL不是一种语言，而是对ORACLE数据库传输指令的一种方式。

SQL中NULL既不是字符也不是数字，它是缺省数据。ORACLE提供了NVL函数来解决。

ORACLE中的字符串连接为 string1‖string2 ，SQL中为string1+string2.

集合操作：在SQL中只有UNION（并操作），ORACLE中包含MINUS（差操作）、

INTERECT（交操作）、UNION（并操作）。

索引：SQL的索引分为聚集索引和非聚集索引，还包括全文索引；

ORACLE的索引包括：B+树索引，Bitmap位图索引，函数索引，反序索引，

主键索引，散列索引，本地索引。

ORACLE的数据类型比较复杂，有基本数据类型，衍生型，列对象型，表对象型，结构体型；SQL中的数据比较简单，只有一些简单的基本数据类型无法提供事务操作。

在SQL中如果一个事务从开始执行到结束时了错了，它就会回滚到开始之前；

在ORACLE中它采用的是定点回滚，就是该事务函数化和精确错误定位，用savepoint标记保存点，用Rollback标记回滚错误的保存点。

在SQL中循环只有WHILE一种这用起来不灵活，在ORACLE中有多种循环（LOOP循环、WHILE循环、FOR循环）。

在SQL中游标的使用比较复杂，不能同时打开一个以上的游标，因为它只有一个全局变量@@Fast_statues而且声明游标也麻烦，关闭游标时也不清除内存的；ORACLE中游标是以提高速度全部统一的单项游标，可以允许多用户异步读取，而且声明比较简单，就一句declare cursor游标名 is select 语句就可以了。

容错机制：SQL中的错误机制比较复杂，没有提供错误描述；ORACLE中容错类型有三种，一个是预定义错误，一个是非预定义错误，一个是用户自定义，其中在自定义错误中它有两个是在SQL中不有的，那就是SQLCODE 错误号、SQLERRM错误描述。

文件体系结构：SQL中有.MDF(主要数据文件)、.NDF（扩展文件，可以有多个）、

.LDF（日志文件，可以有多个，存放在联机重做日志数据，这里的日志文件有一个缺点就是如果日志文件已填小巧玲珑的话，SQL将自动停止运行并等待人工干预，所以要经常监控日志的情况保证系统的稳定运行）。ORACLE中有.DBF(主要数据文件)、.RDO（日志文件，用来做灾难性的数据备份）、.CTL（控制文件，将数据库的物理文件映射到了数据字典中的逻辑表空间和连机重做日志文件上去，确保数据的一致性）、.ORA（参数文件）。

. SQL只能是本机备份本机的数据库，无法联机备份，而且备份压缩很低，占用了大量空间；ORACLE提供了7种备份机制，具有联机备份功能，有志门的备份机子。

Oracle的日志默认有3个，先写入1号日志，满了后再写入2号日志，2号满了再写入3号日志，3号满了后Oracle将自动备分1号日志的内容，然后清空后写入新的日志信息，且Oracle的日志有多路复用功能，我们可以为日志创建多个镜像，把相同的日志信息同时写入多个日志文件中，这样可以防止磁盘损坏造成的数据丢失。

SQL Server对每个数据库只能分配一个日志文件。且当日志填满后，日志将停止工作，等待人工干预，而无法自动重用。

Oracle的控制文件记录了数据库文件的物理位置和有效性，在每次打开Oracle系统都将自动对数据进行验证，查看其有效性，防止数据的丢失，这体现了Oracle对数据管理的严密性。

SQL Server无此安全机制，只用在使用到了数据库的信息后，系统才去查找数据是否存在。

Oracle的参数文件init.ora是可调的，既我们可以根据数据库的规模调整Oracle对系统资源的使用情况，以达到最合理的资源分配，让Oracle达到最佳的性能。

SQL Server的配置参数是内定的不可调整，它对系统资源的分配是固定的，不受拥护控制，因此无法同时处理大量用户的需求，这了它只能作为中，小型数据库。

Oracle以块为最小存储单位，以区为单位分配空间，用户可以根据需要自己定义块的大小，且区可以按等额或递增进行分配，这可以大大减少系统的I/O操作提高数据库的性能。

SQL Server中以页为单位或使用扩展类型以8页为一单位进行空间分配而无法人工干预，当遇到频繁存储或大数据量存储时，系统将频繁进行I/O操作使工作效率低下。

Oracle中的SQL语句书写规范且提供了完整的函数和数据类型。Oracle提供了健全的错误机制，用户可以轻松的获得错误位置和错误原因而加以修改。用户还可以自己定义所须的错误类型，用以检查逻辑错误。Oracle中对数据类型的转换和游标的定义也十分方便。因此，我们对代码的书写和维护也比SQL Server方便许多。

SQL Server使用类C语言，不易维护和查看代码。SQL Server包含的数据类型太少，无法定义结构体，数组，对象。SQL Server无法定义流程机制，类型转换也极不方便，SQL Server中对游标定义十分复杂，且当用户定义的多个游标同时打开时系统却只有一个游标变量，这是SQL SERVER中一个严重的缺陷。

ORACLE中用户可以根据数据需要在创建块时定义填充因子(空闲空间的百分比)和复用因子(当块的存储数据所占的空间下降到这个百分比之下时，块才重新标记为可用)。用户可以根据记录变化频率和数据量的大小设置合适的填充因子和空闲因子。

SQL SERVER只有填充因子，而它的定义与ORACLE刚好相反，它定义的是剩余可用空间的百分比。而SQL SERVER中没有复用因子，当页中数据低于填充因子，但剩余的空间已经不可能再插入新的数据，但页还是标记为可用的，当系统要写入新数据都要访问这个页而浪费时间，这种页称为废页，将使系统插入新数据时浪费大量时间查找可用的页。

ORACLE在创建表时用户可以精确定义数据存放的表空间，甚至可以把一张表分开存放在多个表空间中，这样可以将数据量庞大的表按某些字段分开存放，这将给查询带来极高的效率。