关于SQL 数据库表中的聚集索引和非聚集索引等

 

今天看SQL方面的书，看到关于 表索引方面的讲解不太详细，弄的一头雾水，似懂非懂，特别是聚集索引和非聚集索引。深知索引对提高数据库查询速度的重要性，所以就花了半个下午的时间在网上Google了一些这方面的文章看了一下，总算对索引有了较深的认识，其实多数文章都写的不错，只是介绍的不太全面。这里把几篇文章的内容做了综合，从概念、性质、sql语法和内部实现机制对数据库表索引做一个相对全面一点的解释，一来加深自己对数据库表索引的理解，二来方便大家参考，错误和疏漏之处还请不吝指教。
         废话少说，开始正文————
         
         1.概念
         索引是在数据库表或者视图上创建的对象，目的是为了加快对表或视图的查询的速度（简单理解）。
         索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单（深刻理解）。

          按照存储方式分为：聚集与非聚集索引（需要重视和区别的概念，后面详解）
         按照维护与管理索引角度分为：唯一索引、复合索引和系统自动创建的索引（相对简单，如下解释：）
         1).唯一索引：惟一索引可以确保索引列不包含重复的值.
         可以用多个列，但是索引可以确保索引列 中每个值组合都是唯一的，
         即下面的姓不能有重复，同时名也不能有重复：
                                             姓      名
                                             李      二
                                             张      三
                                             王      五
          语法： create unique index idxempid on emp(姓,名)

          2).复合索引：如果在两上以上的列 上创建一个索引，则称为复合索引。
          那么，不可能有两行的姓和名是重复的，即上面的表没有两行其姓和名的组合是一样的。
          语法： create index indxfullname on emp(姓,名)

          3).系统自建的索引：在使用T_sql语句创建表的时候使用PRIMARY KEY或UNIQUE约束时，会在表上
          自动创建一个惟一索引，自动创建的索引是无法删除的。
           语法：
                   create table ABC
                   ( empID int PRIMARY KEY,
                      firstname varchar(50) UNIQUE,
                      lastname  varchar(50) UNIQUE,
                     )    /*这样的结果就出来了三个索引，但只有一个聚集索引empID*/

         索引的结构是由：根节点--->非叶节点--->非叶节点--->叶节点（注意索引在数据库引擎中所用的
         内部数据结构一般是B+树，参考后文）

          聚集索引和非聚集索引——   
         用一个现实中的例子说明以助理解。我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。
　　我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

　　如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。
        我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

         2.性质及使用方法
         1)聚集索引： 表中存储的数据按照索引的顺序存储，检索效率比普通索引高，索引占用硬盘
          存储空间小（1%左右），但对数据新增/修改/删除的速度影响比较大（降低）。
          特点：
                  (1) 无索引，数据无序
                  (2) 有索引，数据与索引同序 
                  (3) 数据会根据索引键的顺序重新排列数据
                  (4) 一个表只能有一个索引
                  (5) 叶节点的指针指向的数据也在同一位置存储
         语法：create CLUSTERED INDEX idxempID ON emp(empID)

         2)非聚集索引 ：不影响表中的数据存储顺序，检索效率比聚集索引低，索引占用硬盘存储
         空间大（30%~40%），对数据新增/修改/删除的影响很少。
         特点：
               (1) 一个表可以最多可以创建249个非聚集索引
               (2) 先建聚集索引才能创建非聚集索引
               (3) 非聚集索引数据与索引不同序
               (4) 数据与非聚集索引在不同位置
               (5) 非聚集索引在叶节点上存储，在叶节点上有一个“指针”直接指向要查询的数据区域
               (6) 数据不会根据非聚集索引键的顺序重新排列数据
         语法：create NONCLUSTERED INDEX idximpID ON emp(empID)
         
         创建索引的方法：
         1)企业管理器中
               (1)右击某个表，所有任务---管理索引，打开管理索引，单击“新建”就可以创建索引
               (2)在设计表中进行设计表，管理索引/键
               (3)在关系图中，添加表后右击关系图中的某个表，就有“索引/键”
               (4)通过向导,数据库---创建索引向导
               (5)通过T-SQL语句
         2)能过“索引优化向导”来优化索引的向导，通过它可以决定选择哪些列做为索引列
         
         何时应使用聚集索引或非聚集索引

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	应	应
返回某范围内的数据	应	不应
一个或极少不同值	不应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
外键列	应	应
主键列	应	应
频繁修改索引列	不应	应

         3.数据库引擎中索引的内部结构
         有必要先说明一下数据库引擎，
         这部分是较深的内容，需要有一定的数据库理论知识和数据结构与算法知识，数据结构和算法告诉我们，对索引关键字进行快速查找时要使用树形数据结构，在数据库引擎中，索引通常用B+树来表示，google发现这方面的文章较少，后面找到相关详细资料会补充。

         4.主键、索引、聚集索引和非聚集索引
         1)主键   (PK)   
                唯一标识表中的所有行的一个列或一组列。主键不允许空值。不能存在具有相同的主键值的两个
         行，因此主键值总是唯一标识单个行。表中可以有不止一个键唯一标识行，每个键都称作候选键。只有
         一个候选键可以选作表的主键，所有其它候选键称作备用键。尽管表不要求具有主键，但定义主键是很
         好的做法。   在规范化的表中，每行中的所有数据值都完全依赖于主键。例如，在以   EmployeeID   作为
         主键的规范化的   employee   表中，所有列都应包含与某个特定职员相关的数据。该表不具有   
         DepartmentName 列，因为部门的名称依赖于部门   ID，而不是职员   ID。   

         2)索引   
               关系数据库中基于键值提供对表的行中数据的快速访问的数据库对象。索引还可以在表的行上强制唯
         一性。SQL   Server   支持聚集索引和非聚集索引。对表的主键自动进行索引。在全文搜索中，全文索引
         存储关于重要词和这些词在给定列中的位置的信息。   
               如果某列有多行包含   NULL   值，则不能在该列上创建唯一索引。同样，如果列的组合中有多行包
         含   NULL   值，则不能在多个列上创建唯一索引。在创建索引时，这些被视为重复的值。   

        3)聚集索引   
               在创建聚集索引时，将会对表进行复制，对表中的数据进行排序，然后删除原始的表。因此，数据库
         上必须有足够的空闲空间，以容纳数据复本。默认情况下，表中的数据在创建索引时排序。但是，如果
         因聚集索引已经存在，且正在使用同一名称和列重新创建，而数据已经排序，则会重建索引，而不是从
         头创建该索引，以自动跳过排序操作。重建操作会检查行是否在生成索引时进行了排序。如果有任何行
         排序不正确，即会取消操作，不创建索引。   

         4)非聚集索引   
               非聚集索引与课本中的索引类似。数据存储在一个地方，索引存储在另一个地方，索引带有指针指向
         数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚
         集索引规定）。如果在表中未创建聚集索引，则无法保证这些行具有任何特定的顺序。 

         打开设计表界面里面有个钥匙就是主键的意思，当你声明一列为主键的时候数据库实际上就是生成一个
唯一的索引，查询优化器实际上是根据列上有没有唯一索引来保证列的唯一性而不是根据列是否被声明为主键。   
         聚集索引一个表只有一个，实际上它的叶子节点就是数据页，比非聚集索引速度快，占用的空间小，大概只有表的1％左右。如果在声明的时候没有选择UNIQUE选项，则在插入数据的时候会自动生成一个唯一标示符。   
         非聚集索引一个表可以有多个，一个3层的非聚簇索引要查询6次才可以找到真实数据，因为其叶子节点并不是真实数据，而是标识（如果表上有聚集索引则为聚集索引，如没有，则为实际数据的页号），非聚集索引通常占用空间比较大，表的30－40％。 

 

本文转载自：http://www.cppblog.com/zuoyinbo/archive/2008/06/03/52075.html