SQL Server总结之——索引

ddsdjn

概念：
索引是一种特殊的数据库对象。它使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。可以简单的理解为：目录！
优缺点：
优点：
在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。
利用索引可以大大提高系统的性能。
表现在：

• 
  通过创建惟一索引，可以保证数据记录的惟一性。
  

• 大大加快数据检索速度
  

• 加强表与表之间的连接
  

缺点：

• 需要占用数据表以外的物理储存空间。
  

• 创建和维护索引需要花费一定的时间。
  

• 因为对表进行更新操作时，索引需要被重建，降低了数据的维护速度。
  

原则：
建立索引原则：

• 
  主键列上一定要建立索引.（系统默认为聚集索引）
  

• 
  外键列可以建立索引。
  

• 
  经常查询的字段上最好建立索引
  

• 查询中很少涉及的列和重复值比较多的列不要建立索引
  

• 
  对于定义为text、image、和bit数据类型的列不要定义索引。
  

使用索引原则：

• 
  一个表中如果建有大量索引会影响insert、update、和delete语句的性能，因为在表中的数据更改时，所有的索引都需要进行适当的调整。
  

• 避免对更新过多的表进行过多的索引，而且应尽量保持索引较窄，也就是说，列要尽可能的少。
  

• 使用多个索引可以提高更新少而数据量大的查询的性能，因为查询优化器有更多的索引可供选择。
  

• 对数据量小的表索引可能不会产生优化作用，因为对有索引的表，查询优化器会先遍历数据索引页，可能会比直接查询花费更多的时间。
  

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


·优点：查找数据很快以
·缺点：完全重排数据，要相当于数据所占用空间的120%!
注意：

• 
  由于一个表只能有一种实际的存储顺序，因此，一个表只能有一个聚集索引。
  
• 
  创建非聚集索引之前要先创建聚集索引。
  

• 
  关键值的惟一性使用UNIQUE或标识符明确维护。
  

• 
  保证有足够的临时空间来创建聚集索引。
  

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


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

无论是聚集索引还是非聚集索引：
根据索引键值是否重复，可以判定为是否为唯一索引。
若希望在表中创建唯一索引，则该字段或字段组合的值在表中必须具有唯一性。
注意：

• 
  建立唯一索引的字段最后也设置为notnu11，因为两个nun值将被认为是重复的字段值。
  

• 添加数据时，如果该列创建了唯一索引，那么添加的数据就不能重复，否则就会提示错误。
  

根据索引字段的组成情况，可以判定是否为复合索引。
概念：若基于多个字段的组合创建索引，则称该索引为复合索引。
注意：符合索引既可以是唯一索引，也可以不是唯一索引：即使是唯一索引，这个字段的组合的取值不能重复，但是单独的字段值依然可以重复。

操作：
一：创建索引。

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


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


二查询
[EXEC] sp_helpindex [@objname=] name
其中 [@objname=] name是当前数据库中表或视图的名称。
例如：查看Student数据库中“class_info”表的索引信息。

Use student
Go
Exec sp_helpindex class_info
Go


三：删除
DROP INDEX表名.索引名[,n……]
例如：删除Student数据库中Class_Info的Classno_index的索引。

Use student
Go
DROP Index class_info.classno_index
Go


需要注意的是：

• 
  DROP INDEX不能指定系统表中的索引。
  

• 删除聚集索引时，表中索引非聚集索引都会被重建。
  
• 删除表时，表中的索引都会被删除。
  
• 
  除去为实现PRIMARY KRY或UNIQUE约束而创建的索引，必须先除去约束。
  

分析与维护
一：分析。
1.SHOWPLAN语句
该语句用来显示查询语句的执行信息，包括查询过程所选择的哪个索引。
语法格式：SETSHOWPLAN_ALL{ON|OFF}和SETSHOWPLAN_TEXT{ON|OFF}
其中：on为显示执行信息，off为不显示（系统默认）
例如：在Student数据库中的“student_info”表上查询所有男同学的学号和班级，并显示查询处理过程。

Use student
Go
Set showplan_all on
Go
Select student_id,class_no from student_info where性别="男"
Go



2.STATISTICSIO语句
该语句用来显示执行数据检索语句所花费的磁盘活动量信息，从而确定是否重新设计索引
语法：STATISTICSIO{on|off}
用法与SHOWPLAN相同。
二：维护。
1.DBCC　SHOWCONTIG语句(查看锁片信息）
扫描密度为100%时，表示不存在碎片。
语法：DBCCSHOWCONTIG[{table_name|tabel_id|view_name|view,index_name|index_id}]]
例如：

Use student
Go
DBCC　SHOWCONTIG
Go


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 student
Go
DBCC INDEXDEFRAG(student,class_info,class_no_index)
Go



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