聚集索引和非聚集索引

      数据库的索引，听起来挺神秘的，仔细想想。这些索引，其实就是平时咱们查东西时候常用的两种手段。无非就是为了提高我们找东西的效率而已。那么我们平时又是怎么查东西呢？ 

聚集索引：
       聚集索引，来源于生活尝试。这中索引可以说是按照数据的物理存储进行划分的。对于一堆记录来说，使用聚集索引就是对这堆记录 进行 堆划分。即主要描述的是物理上的存储。
举个例子：
       比如图书馆新进了一批书。那么这些书需要放到图书馆内。书如何放呢？一般都有一个规则，杂志类的放到101房间，文学类的放到102房间，理工类的放到103房间等等。这些存储的规则决定了每本书应该放到哪里。而这个例子中聚集索引为书的类别。 
       正式因为这种存储规则，才导致 聚集索引的唯一性。
误区：
       有的人认为，聚集索引的字段是唯一的。这是因为sql server 中添加主键的时候，自动给主键所在的字段生成一个聚集索引。所以人们会认为聚集索引所加的字段是唯一的。 
       思考一下上面这个问题。杂志类的书放到101房间。那么如果杂志类的书太多，一个101房间存放不下。那么可能101，201两个房间来存放杂志类的书籍。如果这样分析的话，那么一个杂志类对应多个房间。放到表存储的话，那么这个类别字段 就不是唯一的了。 

非聚集索引：
       非聚集索引，也可以从生活中找到映射。非聚集索引强调的是逻辑分类。可以说是定义了一套存储规则，而需要有一块控件来维护这个规则，这个被称之为索引表。
继续使用上述提到的例子：
       同学如果想去图书馆找一本书，而不知道这本书在哪里？那么这个同学首先应该找的就是 检索室吧。对于要查找一本书来说，在检索室查是一个非常快捷的的途径了吧。但是，在检索室中你查到了该书在XX室XX书架的信息。你的查询结束了吗？没有吧。你仅仅找到了目的书的位置信息，你还要去该位置去取书。
对于这种方式来说，你需要两个步骤： 
       1、查询该记录所在的位置。 
       2、通过该位置去取要找的记录。
区别：
       聚集索引：可以帮助把很大的范围，迅速减小范围。但是查找该记录，就要从这个小范围中Scan了。 

       非聚集索引：把一个很大的范围，转换成一个小的地图。你需要在这个小地图中找你要寻找的信息的位置。然后通过这个位置，再去找你所需要的记录。

索引与主键的区别

       主键：主键是唯一的，用于快速定位一条记录。 
       聚集索引：聚集索引也是唯一的。（因为聚集索引的划分依据是物理存储）。而聚集索引的主要是为了快速的缩小查找范围，即记录数目未定。 
       主键和索引没有关系。他们的用途相近。如果聚集索引加上唯一性约束之后，他们的作用就一样了。
使用场景

基于上述的两种规则，那么在什么时候适合聚集索引，什么时候适合非聚集索引？ 

创建方法

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name   
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] )   
    [ WITH <backward_compatible_index_option> [ ,...n ] ]  
    [ ON { filegroup_name | "default" } ]  
  
<object> ::=  
{  
    [ database_name. [ owner_name ] . | owner_name. ]   
    table_or_view_name  
}  
  
<backward_compatible_index_option> ::=  
{   
    PAD_INDEX  
  | FILLFACTOR = fillfactor  
  | SORT_IN_TEMPDB  
  | IGNORE_DUP_KEY  
  | STATISTICS_NORECOMPUTE   
  | DROP_EXISTING   
}

on filegroup_name：为指定文件组创建指定索引。 如果未指定位置且表或视图尚未分区，则索引将与基础表或视图使用相同的文件组。 该文件组必须已存在。
on default：为默认文件组创建指定索引。

PAD_INDEX = {ON |OFF }：指定是否索引填充。默认为 OFF。

　　ON 通过指定的可用空间的百分比fillfactor应用于索引中间级别页。
　　OFF 或 fillfactor 未指定，考虑到中间级页上的键集，将中间级页填充到接近其容量的程度，以留出足够的空间，使之至少能够容纳索引的最大的一行。
　　PAD_INDEX 选项只有在指定了 FILLFACTOR 时才有用，因为 PAD_INDEX 使用由 FILLFACTOR 指定的百分比。
       FILLFACTOR = fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor 的值为1到100。
SORT_IN_TEMPDB = {ON |OFF }：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。 默认为 OFF。
　　ON 用于生成索引的中间排序结果存储在tempdb。 这可能会降低仅当创建索引所需的时间tempdb位于不同的与用户数据库的磁盘集。 
　　OFF 中间排序结果与索引存储在同一数据库中。
IGNORE_DUP_KEY = {ON |OFF }：指定在插入操作尝试向唯一索引插入重复键值时的错误响应。默认为 OFF。
　　ON 向唯一索引插入重复键值时将出现警告消息。 只有违反唯一性约束的行才会失败。
　　OFF 向唯一索引插入重复键值时将出现错误消息。 整个 INSERT 操作将被回滚。
STATISTICS_NORECOMPUTE = {ON |OFF}：用于指定过期的索引统计是否自动重新计算。 默认为 OFF。
　　ON 不会自动重新计算过时的统计信息。
　　OFF 启用统计信息自动更新功能。
DROP_EXISTING = {ON |OFF }：表示如果这个索引还在表上就 drop 掉然后在 create 一个新的。 默认为 OFF。
　　ON 指定要删除并重新生成现有索引，其必须具有相同名称作为参数 index_name。
　　OFF 指定不删除和重新生成现有的索引。 如果指定的索引名称已经存在，SQL Server 将显示一个错误。
ONLINE = {ON |OFF}：表示建立索引时是否允许正常访问，即是否对表进行锁定。默认为 OFF。
　　ON 它将强制表对于一般的访问保持有效，并且不创建任何阻止用户使用索引和/表的锁。

　　OFF 对索引操作将对表进行表锁，以便对表进行完全和有效的访问。

/*
---------------------------------------------------------
-------------创建唯一聚集索引-------------------------- 
---------------------------------------------------------
*/ 
create unique clustered        --表示创建唯一聚集索引
index UQ_Clu_StuNo        --索引名称
on Student(S_StuNo)        --数据表名称（建立索引的列名）
with 
(
    pad_index=on,    --表示使用填充
    fillfactor=50,    --表示填充因子为50%
    ignore_dup_key=on,    --表示向唯一索引插入重复值会忽略重复值
    statistics_norecompute=off    --表示启用统计信息自动更新功能
)
/*
---------------------------------------------------------
-------------创建唯一非聚集索引-------------------------- 
---------------------------------------------------------
*/  
-- 创建唯一非聚集索引
create unique nonclustered        --表示创建唯一非聚集索引
index UQ_NonClu_StuNo        --索引名称
on Student(S_StuNo)        --数据表名称（建立索引的列名）
with 
(
    pad_index=on,    --表示使用填充
    fillfactor=50,    --表示填充因子为50%
    ignore_dup_key=on,    --表示向唯一索引插入重复值会忽略重复值
    statistics_norecompute=off    --表示启用统计信息自动更新功能
)

--创建聚集索引
create clustered index Clu_Index
on Student(S_StuNo)
with (drop_existing=on)    

--创建非聚集索引
create nonclustered index NonClu_Index
on Student(S_StuNo)
with (drop_existing=on)    

--创建唯一索引
create unique index NonClu_Index
on Student(S_StuNo)
with (drop_existing=on)
--PS：当 create index 时，如果未指定 clustered 和 nonclustered，那么默认为 nonclustered。
 
/*
---------------------------------------------------------
-------------创建非聚集复合索引-------------------------- 
---------------------------------------------------------
*/ 
 
--创建非聚集复合索引
create nonclustered index Index_StuNo_SName
on Student(S_StuNo,S_Name)
with(drop_existing=on)
--创建非聚集复合索引，未指定默认为非聚集索引
create index Index_StuNo_SName
on Student(S_StuNo,S_Name)
with(drop_existing=on)

--
/*
PS：聚集索引不能创建包含非键列的索引。
在 CREATE INDEX 语句中使用 INCLUDE 子句，可以在创建索引时定义包含的非键列（即覆盖索引），其语法结构如下：

  CREATE NONCLUSTERED INDEX 索引名
  ON { 表名| 视图名 } ( 列名 [ ASC | DESC ] [ ,...n ] )
  INCLUDE (<列名1>, <列名2>, [,… n])
  */ 

--创建非聚集覆盖索引
create nonclustered index NonClu_Index
on Student(S_StuNo)
include (S_Name,S_Height)
with(drop_existing=on)

--创建非聚集覆盖索引，未指定默认为非聚集索引
create index NonClu_Index
on Student(S_StuNo)
include (S_Name,S_Height)
with(drop_existing=on)


--创建非聚集筛选索引
create nonclustered index Index_StuNo_SName
on Student(S_StuNo)
where S_StuNo >= 001 and S_StuNo <= 020
with(drop_existing=on)

--创建非聚集筛选索引，未指定默认为非聚集索引
create index Index_StuNo_SName
on Student(S_StuNo)
where S_StuNo >= 001 and S_StuNo <= 020
with(drop_existing=on)

PS：

1、当 create index 时，如果未指定 clustered 和 nonclustered，那么默认为 nonclustered。

2、聚集索引不能创建包含非键列的索引。

总结：

  1.什么是索引：数据库中的索引是某个表中一列或多列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
  2.分类：
     唯一索引(UNIQUE)：不允许两行具有相同的索引值（创建了唯一约束，系统将自动创建唯一索引）
     主键索引：主键索引要求主键中的每个值是唯一的，（创建主键自动创建主键索引）
     聚集索引(CLUSTERED)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，表中只能包含一个聚集索引，主键列默认为聚集索引
     非聚集索引(NONCLUSTERED)：表中各行的物理顺序与键值的逻辑（索引）顺序不匹配，表中可以有249个非聚集索引
  3.创建索引的标准：用语频繁搜索的列；用语对数据进行排序的列
注意：如果表中仅有几行，或列中只包含几个不同的值，不推荐创建索引，因为SQL Server 在小型表中用索引搜索数据所花的时间比逐行搜索更长。