SQL Server中索引

一、SQL Server中数据行的存储方式

     在SQL Server中存放数据的文件会以8KB的大小分页。每一页可以是数据、索引以及其他SQL Server数据库需要为其维护数据文件的数据类型。大多数页是数据页或索引页。页是SQL Server读、写数据文件的单元。每一页只包括一个对象的数据或索引信息，所以在每一个数据页上只能找到一个对象的数据，同样在每一个索引页上页只能找到一个索引的信息。

     在SQL Server2000中，将一个数据行存储在多个页上是不可能的，这意味着一个数据行必须在一页上，这会导致一个数据行有8KB大小的限制（大型数据对象除外）。

     在SQL Server2005 中，对于变长数据类型，例如nvarchar、varbinary，CLR等，数据行8KB的大小限制就不存在了，所以变长数据类型的数据行可以跨越多页。但是对于定长数据类型，数据行任然必须在一页上。

 

二、认识SQL Server中的索引

     索引分为聚集索引和非聚集索引。

     聚集索引（Clustered Index）

Ø  聚集索引的叶节点就是实际的数据页。

Ø  在数据页中数据按照索引顺序存储。

Ø  行的物理位置和行在索引中的位置是相同的。

Ø  每个表只能有一个聚集索引。

Ø  聚集索引的平均大小为表大小的5%。

一个聚集索引决定了一个表中数据的物理存储顺序。一个聚集索引与一个电话目录相似，电话目录是按照last name来存放。因为聚集索引决定一张表中数据的物理存放顺序，所以一张表只能有个聚集索引,一个聚集索引可以包含多个列（复合索引），就像电话目录一样按照last name 和 first name记录一样，聚集索引与Oracle中的IOT'S(Index-Organized Tables)相似。

 

一个聚集索引对范围查询非常有效率efficient on columns that are often searched for ranges of values。当用聚集索引把第一个行检索出来之后，后续行一定能保证在物理上是相邻的。例如，应用的某个查询需要频繁执行一个范围查询，聚集索引可以快速定位到满足条件的第一个数据，然后再检索表中与之相邻的记录直到最后一条记录。这样可以调高这类查询的性能。另外，如果某列经常用来对表中的数据进行排序（sort），该情况下也可利用聚集索引来节省每次排序的时间。
     当索引值唯一时，需要查找一个指定行，此时聚集索引也是高效率的。例如，用最快的方式来找到一个指定empoyee ID的employee记录就是在emp_id列上创建一个聚集索引。

 

 

  非聚集索引（Nonclustered Index）

Ø  非聚集索引中的页，不是数据，而是指向数据页的页。

Ø  若未指定索引类型，则默认为非聚集索引。

Ø  叶节点页的次序和表的物理存储次序不一致。

Ø  每个表最多可以建249个非聚集索引。

Ø  在非聚集索引建立前创建聚集索引，否则会引发索引重建

一个非聚集索引与一本书的索引相似。数据存储在一个地方，索引存储在另外一个地方，可以通过索引中的指针来访问存储的数据。索引中的条目是按照索引码的值按序存储，但是表中的信息可以按照不同的顺序存储（如可以按照聚集索引存储）。如果表中没有创建聚集索引，那么表中的记录就不能保证按照某种特定的顺序。

 

 

与你用一本书的索引方式一样，SQL Server2000也是先通过非聚集索引检索到查找数据在表的位置，然后通过该位置来检索数据。这使得非聚集索引非常适合精确匹配查询（This makes nonclustered indexes the optimal choice for exact match queries）,因为索引条目中包含了你需要查找数据的位置信息。如果当前的表是以聚集索引方式存储，那么非聚集索引的位置信息就是聚集索引的索引码（index key）；否则，位置信息就是row ID（RID)，每个RID由file number、page number和 slot number of row（每行记录的槽号）。比如，要在一个表中检索某个employee ID（emp_id），该表已经有在emp_id列上创建了非聚集索引，SQL Server查找索引树，找到一个索引条目包含你需要查找的emp_id,然后利用其中RID来访问到对应数据页中的值。

 

三、 注意事项

非聚集索引使用于以下情况

Ø  列中包含大量的不同值，如last name 和 first name 构成的复合索引（假如已用另外列创建的聚集索引） 。如果某列中只有很少的不同值，如0或者1，大多数查询不会利用该索引的，因为一个表扫描通常更有效率。

Ø  不返回大量结果集的查询 Queries that not return large result sets

Ø  经常被包含在一个查询条件语句（WHERE clause）的列,且该查询返回精确配备（return  exact matches）

Ø  决策支持系统中经常需要表之间的关联（join）和聚集（group）。在被包含在join和grouping操作的列上建立非聚集索引，和在外键列上建立聚集索引。

Ø  一个给定的查询包含了表中所有的列，这样可以减少对表或聚集索引的访问。（Covering all columns from one table in a given query. This eliminates accessing the table or clustered index altogether.）我的理解就是覆盖索引。

 

    在创建聚集索引时，索引列应该尽量少，这一点很重要。如果定义一个大的索引码，那么该表中的任何非聚集索引就会显著的增大，因为每个非聚集索引叶级索引条目都包含了一个聚集索引码。
  聚集索引适用于以下场景：

Ø  列中包含大量的不同值

Ø  返回一个范围记录的查询，像BETWEEN, >, >=, <, and <=.的操作；

Ø  顺序访问的列

Ø  返回大量记录的查询

Ø  在查询中某列被频繁的包含在join或group语句中，尤其该列也是该表的外键。在ORDER BY或 GROUP BY语句的列上建立聚集索引可以减少SQL Server对数据的排序，因为表中行已经是有序的了，这样可提高查询的性能。

Ø  在OLTP类的应用中经常需要快速查找某行记录，尤其是一主键的来查找，此时可在主键上创建一个聚集索引。

 

 聚集索引不适合以下场景：

Ø  频繁变化的列。这样造成了表中行经常移动，

Ø  宽键（wide keys）聚集索引的索引码被所有的非聚集索引来用来检索，所被存储在每个非聚集索引的叶级索引条目中。