数据库索引简介（三）sqlserver索引类型
1、索引类型
1）唯⼀索引（UNIQUE）：唯⼀索引不允许两⾏具有相同的索引值；
2）主键索引：为表定义⼀个主键将⾃动创建主键索引，主键索引是唯⼀索引的特殊类型。主键索引要求主键中的每个值是唯⼀的，并且不能为空；
3）聚集索引(Clustered)：表中各⾏的物理顺序与键值的逻辑（索引）顺序相同，每个表最多只能有⼀个；
4）⾮聚集索引(NonClustered)：⾮聚集索引指定表的逻辑顺序。数据存储在⼀个位置，索引存储在另⼀个位置，索引中包含指向数据存储位置的指针。可以有多个，⼩于249个。
唯⼀索引（UNIQUE）：
唯⼀索引不允许两⾏具有相同的索引值。如果现有数据中存在重复的键值，则⼤多数数据库都不允许将新创建的唯⼀索引与表⼀起保存。当新数据将使表中的键值重复时，数据库也拒绝接受此数据。
提⽰：创建了唯⼀约束，将⾃动创建唯⼀索引。尽管唯⼀索引有助于到信息，但为了获得最佳性能，建议使⽤主键约束或唯⼀约束。
主键索引：
在数据库关系图中为表定义⼀个主键将⾃动创建主键索引，主键索引是唯⼀索引的特殊类型。主键索引要求主键中的每个值是唯⼀的。当在查询中使⽤主键索引时，它还允许快速访问数据。
聚集索引（clustered index）：
在聚集索引中，表中各⾏的物理顺序与键值的逻辑（索引）顺序相同。表只能包含⼀个聚集索引。例如：汉语字（词）典默认按拼⾳排序编排字典中的每页页码。拼⾳字母a，b，c，d……x，y，z就是索引的逻辑顺序，⽽页码1，2，3……就是物理顺序。默认按拼⾳排序的字典，其索引顺序和逻辑顺序是⼀致的。即拼⾳顺序较后的字（词）对应的页码也较⼤。如拼⾳“ha”对应的字(词)页码就⽐拼⾳“ba” 对应的字(词)页码靠后。
⾮聚集索引(NonClustered)：
如果不是聚集索引，表中各⾏的物理顺序与键值的逻辑顺序不匹配。聚集索引⽐⾮聚集索引（nonclustered index）有更快的数据访问速度。例如，按笔画排序的索引就是⾮聚集索引，“1”画的字
（词）对应的页码可能⽐“3”画的字（词）对应的页码⼤（靠后）。
提⽰：SQL Server中，⼀个表只能创建1个聚集索引，多个⾮聚集索引。设置某列为主键，该列就默认为聚集索引
2、何时使⽤聚集索引或⾮聚集索引
下⾯的表总结了何时使⽤聚集索引或⾮聚集索引（很重要）：
动作描述使⽤聚集索引使⽤⾮聚集索引
列经常被分组排序应应
返回某范围内的数据应不应
⼀个或极少不同值不应不应
⼩数⽬的不同值应不应
⼤数⽬的不同值不应应
频繁更新的列不应应
外键列应应
主键列应应
频繁修改索引列不应应
事实上，我们可以通过前⾯聚集索引和⾮聚集索引的定义的例⼦来理解上表。如：返回某范围内的数据⼀项。⽐如您的某个表有⼀个时间列，恰好您把聚合索引建⽴在了该列，这时您查询2004年1⽉1⽇⾄2004年10⽉1⽇之间的全部数据时，这个速度就将是很快的，因为您的这本字典正⽂是按⽇期进⾏排序的，聚类索引只需要到要检索的所有数据中的开头和结尾数据即可；⽽不像⾮聚集索引，必须先查到⽬录中查到每⼀项数据对应的页码，然后再根据页码查到具体内容。
3、如何创建和删除索引
创建索引有多种⽅法，这些⽅法包括直接创建索引的⽅法和间接创建索引的⽅法。
第⼀，  直接创建索引，例如使⽤CREATE INDEX语句或者使⽤创建索引向导。
第⼆，  间接创建索引，例如在表中定义主键约束或者唯⼀性键约束时，同时也创建了索引。
虽然，这两种⽅法都可以创建索引，但是，它们创建索引的具体内容是有区别的。
使⽤CREATE INDEX语句或者使⽤创建索引向导来创建索引，这是最基本的索引创建⽅式，并且这种⽅法最具有柔性，可以定制创建出符合⾃⼰需要的索引。在使⽤这种⽅式创建索引时，可以使⽤许多选项，例如指定数据页的充满度、进⾏排序、整理统计信息等，这样可以优化索引。使⽤这种⽅法，可以指定索引的类型、唯⼀性和复合性，也就是说，既可以创建聚簇索引，也可以创建⾮聚簇索引，既可以在⼀个列上创建索引，也可以在两个或者两个以上的列上创建索引。
通过定义主键约束或者唯⼀性键约束，也可以间接创建索引。主键约束是⼀种保持数据完整性的逻辑，它限制表中的记录有相同的主键记录。在创建主键约束时，系统⾃动创建了⼀个唯⼀性的聚簇索引。虽然，在逻辑上，主键约束是⼀种重要的结构，但是，在物理结构上，与主键约束相对应的结构是唯⼀性的聚簇索引。换句话说，在物理实现上，不存在主键约束，⽽只存在唯⼀性的聚簇索引。同样，在创建唯⼀性键约束时，也同时创建了索引，这种索引则是唯⼀性的⾮聚簇索引。因此，当使⽤约束创建索引时，索引的类型和特征基本上都已经确定了，由⽤户定制的余地⽐较⼩。
当在表上定义主键或者唯⼀性键约束时，如果表中已经有了使⽤CREATE INDEX语句创建的标准索引时，那么主键约束或者唯⼀性键约束创建的索引覆盖以前创建的标准索引。也就是说，主键约束或者唯⼀性键约束创建的索引的优先级⾼于使⽤CREATE INDEX语句创建的索引。
以直接⽅法为例：
1） 使⽤T-SQL语句创建索引的语法：
a、创建单⼀索引：
-- 判断索引是否存在，如果不存在，则创建。注意：表的索引信息是存在系统表 sysindexes 中的
IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = ' index_name ')
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]
INDEX  index_name
ON table_name (column_name…)
[WITH FILLFACTOR=x]
注： UNIQUE表⽰唯⼀索引，可选；
CLUSTERED、NONCLUSTERED表⽰聚集索引还是⾮聚集索引，可选；
FILLFACTOR表⽰填充因⼦，指定⼀个0到100之间的值，该值指⽰索引页填满的空间所占的百分⽐，设置FILLFACTOR值时，应考虑如下因素：
l  填充因⼦的值是从 0 到 100 之间的百分⽐数值，⽤来指定在创建索引后对数据页的填充⽐例。
l  值为 100 时表⽰页将填满，所留出的存储空间量最⼩。只有当不会对数据进⾏更改时（例如，在只读表中）才会使⽤此设
置。
l  值越⼩则数据页上的空闲空间越⼤，这样可以减少在索引增长过程中对数据页进⾏拆分的需要，但需要更多的存储空间。当表中数据会发⽣更改时，这种设置更为适当。
l  使⽤sp_configure系统存储过程可以在服务器级别设置默认的填充因⼦。
l  填充因⼦只在创建索引时执⾏；索引创建后，当表中进⾏数据的添加、删除或更新时，不会保持填充因⼦。
b、创建符合索引:
创建索引
create index idx1 on table1(col1,col2,col3)
2） 使⽤T-SQL语句删除索引的语法：
以单⼀索引为例：
-- 判断索引是否存在，如果存在，则删除。注意：表的索引信息是存在系统表 sysindexes 中的
IF EXISTS (SELECT name FROM sysindexes WHERE name = ' index_name ')
创建唯一约束sql语句DROP INDEX  table_name. index_name;
4、索引更名
其语法格式如下：
sp_rename [ @objname = ] 'object_name' ,
[ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]
例：将STUDENT数据库中“课程”表的pk_kc索引名称更改为pk_kecheng。其程序清单如下：
USE  STUDENT
GO
EXEC  sp_rename  'pk_kc‘ , 'pk_kecheng'
GO
5、索引的特征
索引有两个特征，即唯⼀性索引和复合索引。
唯⼀性索引保证在索引列中的全部数据是唯⼀的，不会包含冗余数据。如果表中已经有⼀个主键约束或者唯⼀性键约束，那么当创建表或者修改表时，SQL Server⾃动创建⼀个唯⼀性索引。然⽽，如果必须保证唯⼀性，那么应该创建主键约束或者唯⼀性键约束，⽽不是创建⼀个唯⼀性索引。当创建唯⼀性索引时，应该认真考虑这些规则：当在表中创建主键约束或者唯⼀性键约束时，SQL Server⾃动创建⼀个唯⼀性索引；如果表中已经包含有数据，那么当创建索引时，SQL Server检查表中已有数据的冗余性；每当使⽤插⼊语句插⼊数据或者使⽤修改语句修改数据时，SQL Server检查数据的冗余性：如果有冗余值，那么SQL Server取消该语句的执⾏，并且返回⼀个错误消息；确保表中的每⼀⾏数据都有⼀个唯⼀值，这样可以确保每⼀个实体都可以唯⼀确认；只能在可以保证实体完整性的列上
创建唯⼀性索引，例如，不能在⼈事表中的姓名列上创建唯⼀性索引，因为⼈们可以有相同的姓名。
复合索引就是⼀个索引创建在两个列或者多个列上。在搜索时，当两个或者多个列作为⼀个关键值时，最好在这些列上创建复合索引。
当创建复合索引时，应该考虑这些规则：最多可以把16个列合并成⼀个单独的复合索引，构成复合索引的列的总长度不能超过900字节，也就是说复合列的长度不能太长；在复合索引中，所有的列必须来⾃同⼀个表中，不能跨表建⽴复合列；在复合索引中，列的排列顺序是⾮常重要的，因此要认真排列列的顺序，原则上，应该⾸先定义最唯⼀的列，例如在（COL1，COL2）上的索引与在
（COL2，COL1）上的索引是不相同的，因为两个索引的列的顺序不同；为了使查询优化器使⽤复合索引，查询语句中的WHERE⼦句必须参考复合索引中第⼀个列；当表中有多个关键列时，复合索引是⾮常有⽤的；使⽤复合索引可以提⾼查询性能，减少在⼀个表中所创建的索引数量。
6、索引的优缺点
1）优点：
加快访问速度
加强⾏的唯⼀性
2）缺点：
带索引的表在数据库中需要更多的存储空间
7、其他书上没有的索引使⽤经验总结
注意：这是别⼈的经验，是否适⽤还得看具体情况⽽定
1）、⽤聚合索引⽐⽤不是聚合索引的主键速度快；
2）、⽤聚合索引⽐⽤⼀般的主键作order by时速度快，特别是在⼩数据量情况下；
3）、使⽤聚合索引内的时间段，搜索时间会按数据占整个数据表的百分⽐成⽐例减少，⽽⽆论聚合索引使⽤了多少个；4）、⽇期列不会因为有分秒的输⼊⽽减慢查询速度。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。