SQL Server基础之索引

 (2).避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。

 (3).数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

 (4).在条件表达式中经常用到的、不同值较多的列上建立索引，在不同值较少的列上不要建立索引、比如在学生表的【性别】

字段上只有【男】或【女】两个不同值，因此就无需建立索引，如果建立索引，不但不会提高查询效率，反而会严重降低更新速度。 

  (5).当唯一性是某种数据本身的特征时，指定唯一索引，使用唯一索引能够确保定义的列的数据完整性，提高查询速度。

 (6).在频繁进行排序或分组（即进行GROUP BY 或ORDER BY操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

四：创建索引

 在了解了不同索引类型后，下面开始介绍如何使用T_SQL创建索引:

  CREATE INDEX命令既可以创建一个改变表的物理顺序的聚集索引，也可以创建提高查询性能的非聚集索引。语法如下：

  CREATE [UNIQUE] [CLUSTERED   |    NONCLUSTERRED]

  INDEX index_name ON {table | view }(column [ASC | DESC ]  [,...n])

  [ INCLUDE ( column_name [ ,...n])]

  [with

   (

    PAD_INDEX={ON | OFF}

    | FILLFACTOR =fillfactor

    | SORT_IN_TEMPDB={ ON | OFF}

    |  IGNORE_DUP_KEY ={ ON  | OFF}

    |  STATISRICS_NORECOMPUTE ={ ON | OFF}

    |   DROP_EXISTING = { ON | OFF}

    |  ONLINE ={ ON | OFF}

    |  ALLOW_ROW_LOCKS ={ ON | OFF}

    |  ALLOW_PAGE_LOCKS={ ON   | OFF}

    |  MAXDOP =max_degree_of_parallelism     

   ) [,...n] ]

UNIQUE:表示在表或视图上创建唯一索引。唯一索引不允许两行具有相同的索引键值。视图的聚集索引必须唯一。

CLUSTERED:表示创建聚集索引，在创建任何非聚集索引之前创建聚集索引，创建聚集索引时会重新生成表中现有的非聚集索引。如果没有指定CLUSTERED，则创建非聚集索引。

NONCLUSTERED：表示创建一个非聚集索引，非聚集索引数据行的物理顺序独立于与索引排序，每个表最多可包含999个非聚集索引，NONCLUSTERED是CREATE INDEX的默认值。

index_name:指定索引的名称，索引名称在表或视图中必须唯一，但在数据库中可不必唯一。

ON{table | view }:指定索引所属的表或视图。

Column：指定索引基于一列或者多列。指定两个或者多个列名，可为指定的列的组合值创建组合索引，{table | view}后的括号中，按排序优先级列出组合索引要包括的列。一个组合索引键中最多可组合16列。组合索引键中的所有列必须在同一个表或视图中。

[ASC | DESC]:指定索引列的升序或降序排序方向，默认为ASC。

INCLUDE（column[,...n]）：指定要添加到非聚集索引的叶级别的非键列。

PAD_INDEX:表示指定索引填充，默认值为OFF。ON值表示fillfactor指定的可用空间百分比应用于索引的中间级页。

FILLFACTOR=fillfactor:制定一个百分比，表示在索引创建或重新生成过程中数据库引擎应使每个索引页的叶级别达到的填充程度。fillfactor必须为介于1至100直接的整数值，默认值为0；

SORT_IN_TESTDB:指定是否在tempdb中存储临时排序结果，默认值为OFF，ON值表示在tempdb中存储用于生成索引的中间排序结果，OFF表示中间排序结果与索引存储在同一数据库中。

IGNORE_DUP_KEY:指定唯一聚集索引或唯一非聚集索引执行多行插入操作时，出现重复键值的错误响应。默认值为OFF。ON表示发出一跳警告消息，但只有违反了唯一索引的行才会失败，OFF表示发出错误消息，并回滚整个INSERT事物。

STATISRICS_NORECOMPUTE:指定是否重新计算分发统计信息，默认值为OFF。ON表示不会自动重新计算过时的统计信息，OFF表示启用统计信息自动更新功能。

DROP_EXISTING:指定删除并重新生成已命名的先前存在的聚集或非聚集索引。默认值为OFF。ON表示删除并重新生成现有索引。指定的索引名称必须与当前现有的索引相同，但可以修改索引定义，例如：可以指定不同的列，排序顺序，分区方案或索引选项。OFF表示如果指定的索引名已存在，则会显示一条错误。

ONLINE={ON | OFF}:指定在索引操作期间，基础表和关联的索引是否用于查询和数据修改操纵，默认值为OFF。

ALLOW_ROW_LOCKS:指定是否允许行锁，默认值为ON，ON表示在访问索引时允许行所。数据库引擎确定何时使用行锁。OFF表示未使用行锁。

ALLOW_PAGE_LOCKS:指定是否允许页锁，默认值为ON，ON表示在访问索引时允许页锁。数据库引擎确定何时使用页锁。ＯＦＦ表示未使用页锁。

MAXDOP:指定在索引操作期间，覆盖【最大并行度】配置选项，使用MAXDOP可以限制在执行并行计划的过程中使用的处理器数量，最大数量为64.

【例1】在teacher表中的t_phone列上，创建一个名称为Idx_phone的唯一聚集索引，降序排列，填充因子为30%，输入语句如下:

 CREATE UNIQUE  CLOSTERED INDEX Idx_phone

 ON  teacher (t_phone  DESC)

 WITH

 FILLFACTOR=30;

【例2】 --在teacher表中的t_name和t_gender列上，创建一个名称为Idx_nameAndgender的-唯一非聚集组合索引，升序排列，填充因子为10%。如下：

--首先创建teacher表

   CREATE TABLE teacher(

     t_id int IDENTITY(1,1) PRIMARY KEY ,

     t_name varchar(20),

     t_phone varchar(11),

     t_gender char(2)

   )

--创建索引

CREATE UNIQUE NONCLUSTERED INDEX Idx_nameAndgender

ON teacher(t_name,t_gender)

WITH

FILLFACTOR=10;

注意：如果teacher表中定义了主键，则不能执行【例1】中的脚本，因为每个表只能有一个聚集索引，数据行本身只能按一个顺序存储。并且主键本身就是一个聚集索引。

五:管理和维护索引

 索引创建后可以根据需要对数据库中的索引进行管理，例如在数据表中进行增加，删除或者更新操作，会使索引页出现碎块，为了提高系统的性能，必须对索引进行维护管理，这些管理包括显示索引信息、索引的性能分析和维护，以及删除索引等。

1.显示索引信息

 (1).用系统存储过程查看索引信息

   系统存储过程sp_helpindex可以返回某个表或视图中的索引信息，语法如下：

   sp_helpindex [@objectname= ]  'name';

   [@objectname=] 'name':用户定义的表或视图的限定或非限定名称。仅当指定限定的表或视图名称时，才需要使用引号。如果提供了完全限定的名称，包括数据库名称，则该数据库名称必须是当前数据库的名称。

 【例】使用存储过程查看是 sample_db数据库中teacher表中定义的索引信息，输入语句如下。

  USE  sample_db;

　 GO

  exec sp_helpindex 'teacher';

 由执行结果可以看到，这里显示了authors表中的索引信息：

 index_name:指定索引名称.

 index_description:包含索引的描述信息，例如唯一性索引，聚集索引等。

 index_keys:包含了索引所在表中的列.

(2).查看索引的统计信息

 索引信息还包括统计信息，这些信息可以用来分析索引性能，更好的维护索引，索引统计信息是查询优化器用来分析和评估查询、制定最优查询方式的基础数据，用户可以用图形界面化工具来查看索引信息，也可以使用DBCC SHOW_STATISTICS命令来查看索引的信息。

【例】使用DBCC SHOW_STATISTICS命令来查看teacher表中Idx_phone索引的统计信息，如下：

  DBCC SHOW_STATISTICS ('sample_db.dbo.teacher',Idx_phone);

　　返回的统计信息包括三个部分：统计标题信息，统计密度信息和统计直方信息。统计标题信息主要包括表中的行数，统计抽样行数，索引列的平均长度等。统计密度信息主要包括索引列前缀集选择性，平均长度等信息，统计直方图信息即为显示直方图时的信息。

(3).重命名索引

 系统存储过程sp_rename可以用于更改索引的名称，其语法如下：

 sp_rename 'object_name','new_name' 'object_type'

object_name:用户对象或数据类型的当前限定或非限定名称。此对象可以是表，索引，列，别名数据库类型或用户定义类型。

new_name:指定对象的新名称；

object_type:指定修改的对象类型。下表列出了对象类型的取值：

               sp_rename函数可重命名的对象

 【例】将teacher表中的索引名称idx_nameAndgender更改为multi_index,输入如下:

USE  sample_db;

GO

exec sp_rename  'teacher.idx_nameAndgender' ,'multi_index', index;

(4)删除索引

 当不再需要某个索引时，可以将其删除，DROP INDEX命令可以删除一个或者多个当前数据库中的索引，语法如下：

DROP INDEX '[table | view ].index' [,...n]

或者

DROP INDEX ' index'  ON '[table | view ].index' 

[table | view ]:用于指定索引列所在的表或视图；

index:用于指定要删除的索引名称。

注意：DROP INDEX 命令不能删除由 CREATE TABLE或者ALTER TABLE命令创建的主键（PRIMARY KEY）或者唯一性（UNIQUE）约束索引，也不能删除系统表重的索引。

【例】删除表teacher中的索引 multi_index，如下：

USE  sample_db;

GO

exec sp_helpindex 'teacher'

DROP INDEX teacher.multi_index

exec sp_helpindex 'teacher';

LZ在一次面试中遇到的面试题:

1.索引对数据库性能如此重要，应该如何使用它？

答:为数据库选择正确的索引是一项复杂的任务，如果索引较少，则需要的磁盘空间和维护开销都较少，如果在一个大表上创建了多种组合索引，索引文件会膨胀的很快，而另一方面，索引较多则可覆盖更多的查询，可能需要试验若干不同的设计，才能找到最有效的索引，可以添加、修改和删除索引而不影响数据库架构或应用程序设计。因此，应该尝试多个不同的索引，从而建立最优的索引。

2.为什么要使用短索引？

答：对字符类型的字段进行索引，如果可能应该指定一个前缀长度；例如：如果有一个varchar(255)的列，如果在前10个或者30个字符内，多数值是唯一的，则不需要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和减少I/O操作。

 以上例子的整体代码：

--索引--创建teacher表create database sample_db;CREATE TABLE teacher(  t_id int IDENTITY(1,1),  t_name varchar(20),  t_phone varchar(11),  t_gender char(2))--【例1】在teacher表中的t_phone列上，创建一个名称为Idx_phone的唯一聚集索引，--降序排列，填充因子为30%，输入语句如下:CREATE UNIQUE NONCLUSTERED INDEX Idx_phoneON teacher (t_phone DESC)WITHFILLFACTOR=30;--【例2】在teacher表中的t_name和t_gender列上，创建一个名称为Idx_nameAndgender的--唯一非聚集组合索引，升序排列，填充因子为10%。如下：CREATE UNIQUE NONCLUSTERED INDEX Idx_nameAndgenderON teacher(t_name,t_gender)WITHFILLFACTOR=10;--用系统存储过程查看索引信息USE sample_db;GOexec sp_helpindex 'teacher';--查看索引统计信息DBCC SHOW_STATISTICS ('sample_db.dbo.teacher',Idx_phone);--【例】将teacher表中的索引名称Idx_nameAndgender更改为multi_index,--输入如下:USE sample_db;GOexec sp_rename 'teacher.Idx_nameAndgender' ,'multi_index', 'index';--【例】删除表teacher中的索引 multi_index，如下：USE sample_db;GOexec sp_helpindex 'teacher'DROP INDEX teacher.multi_indexexec sp_helpindex 'teacher';

原标题：SQL Server基础之索引

关键词：sql