SQL Server总结之——索引

 

概念：

索引是一种特殊的数据库对象。它使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。可以简单的理解为：目录！

优缺点：

 优点：

在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。

利用索引可以大大提高系统的性能。

表现在：

1. 通过创建惟一索引，可以保证数据记录的惟一性。
2. 大大加快数据检索速度
3. 加强表与表之间的连接

 

缺点：

1. 需要占用数据表以外的物理储存空间。
2. 创建和维护索引需要花费一定的时间。
3. 因为对表进行更新操作时，索引需要被重建，降低了数据的维护速度。

原则：

    建立索引原则：

1. 主键列上一定要建立索引.（系统默认为聚集索引）
2.  外键列可以建立索引。
3.  经常查询的字段上最好建立索引
4. 查询中很少涉及的列和重复值比较多的列不要建立索引
5. 对于定义为text、image、和bit数据类型的列不要定义索引。

使用索引原则：

1.  一个表中如果建有大量索引会影响insert、update、和delete语句的性能，因为在表中的数据更改时，所有的索引都需要进行适当的调整。
2. 避免对更新过多的表进行过多的索引，而且应尽量保持索引较窄，也就是说，列要尽可能的少。
3. 使用多个索引可以提高更新少而数据量大的查询的性能，因为查询优化器有更多的索引可供选择。
4. 对数据量小的表索引可能不会产生优化作用，因为对有索引的表，查询优化器会先遍历数据索引页，可能会比直接查询花费更多的时间。

分类：

根据储存结构的不同分为：

一：聚集索引.

指物理存储顺序与索引顺序完全相同（按索引列进行排序），它由上下两层组成，上层为索引页，下层为数据页，只有一种排序方式，因此每个表中只能创建一个聚集索引。

 



·优点：查找数据很快以

·缺点：完全重排数据，要相当于数据所占用空间的120%!

注意：

1. 由于一个表只能有一种实际的存储顺序，因此，一个表只能有一个聚集索引。
2. 创建非聚集索引之前要先创建聚集索引。
3. 关键值的惟一性使用UNIQUE或标识符明确维护。
4. 保证有足够的临时空间来创建聚集索引。

 

二：非聚集索引（默认的）

非聚集索引具有完全独立于数据行的结构。使用非聚集索引不用将物理数据页中的数据按列排序。通俗地说，不会影响数据表中记录的实际存储顺序。



 

因此，可以在一个表中创建多个非聚集索引。非聚集索引需要更多的空间，检索效率也较低。一个表中除建立一个聚集索引外，还可以建立249个非聚集索引。

 

无论是聚集索引还是非聚集索引：

根据索引键值是否重复，可以判定为是否为唯一索引。

若希望在表中创建唯一索引，则该字段或字段组合的值在表中必须具有唯一性。

注意：

1. 建立唯一索引的字段最后也设置为notnu11，因为两个nun值将被认为是重复的字段值。
2. 添加数据时，如果该列创建了唯一索引，那么添加的数据就不能重复，否则就会提示错误。

根据索引字段的组成情况，可以判定是否为复合索引。

概念：若基于多个字段的组合创建索引，则称该索引为复合索引。

注意：符合索引既可以是唯一索引，也可以不是唯一索引：即使是唯一索引，这个字段的组合的取值不能重复，但是单独的字段值依然可以重复。

 

操作：

一：创建索引。



例如：为“学生”表创建一个基于“系部代码”、“专业代码”的唯一、聚集、复合索引。

 

use student   go  create UNIQUE CLUSTERED INDEX xbzy_index  on 学生(系部代码,专业代码)  go

 

二查询

[EXEC] sp_helpindex [@objname=] name

其中 [@objname=] name是当前数据库中表或视图的名称。

例如：查看Student数据库中“class_info”表的索引信息。

Use studentGoExec sp_helpindex class_infoGo


三：删除

DROP INDEX 表名.索引名[,n……]

例如：删除Student数据库中Class_Info的Classno_index的索引。

Use studentGoDROP Index class_info.classno_indexGo

 

需要注意的是：

1. DROP INDEX不能指定系统表中的索引。
2. 删除聚集索引时，表中索引非聚集索引都会被重建。
3. 删除表时，表中的索引都会被删除。
4. 除去为实现PRIMARY KRY或UNIQUE约束而创建的索引，必须先除去约束。

分析与维护

一：分析。

 1.SHOWPLAN语句

该语句用来显示查询语句的执行信息，包括查询过程所选择的哪个索引。

语法格式：SETSHOWPLAN_ALL{ON|OFF}和SETSHOWPLAN_TEXT{ON|OFF}

其中：on为显示执行信息，off为不显示（系统默认）

例如：在Student数据库中的“student_info”表上查询所有男同学的学号和班级，并显示查询处理过程。

Use studentGoSet showplan_all onGoSelect student_id,class_no from student_info where性别="男"Go


 

2.STATISTICS  IO语句

该语句用来显示执行数据检索语句所花费的磁盘活动量信息，从而确定是否重新设计索引

语法：STATISTICS  IO{on|off}

用法与SHOWPLAN相同。

二：维护。

1.DBCC　SHOWCONTIG语句(查看锁片信息）

扫描密度为100%时，表示不存在碎片。

语法：DBCC  SHOWCONTIG[{table_name|tabel_id|view_name|view,index_name|index_id}]]

例如：

Use studentGoDBCC　SHOWCONTIGGo

 

2.DBCC　INDEXDEFFRAG语句（整理碎片）

对索引的叶级进行碎片整理，使页的物理顺序与叶结点逻辑顺序匹配，从而提高扫描性能。

压缩索引页，并将压缩后产生的空白页删除。

语法：

DBCC INDEXDEFRAG           ({database_name|database_di|0}             ,tabel_name|table_id|'view_name'|view_id}              ,{index_name|index_id})             [WITH NO_INFOMSGS]

 

说明：{database_name|database_di|0}进行碎片整理的数据库，如果是0，则使用当前的数据库。

             [WITH NO_INFOMSGS]进行显示所有信息性的消息（0~10的严重级别）。

例如：

Use studentGoDBCC INDEXDEFRAG(student,class_info,class_no_index)Go


 

最后：索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。了解索引的分类、作用和优缺点可以让我们合理的利用索引。同样索引也存在的“增删改查”都是基本的操作，唯一不同的是因为数据库经常的变更，我们需要对索引进行分析和维护。