概念:
- 索引是以表列为基础的数据库对象,它保存这表中排序的索引列,并记录了索引在数据表中的位置,实现表中数据的逻辑排序,其主要目的是提高SQL Server系统性能,加快数据查询和减少系统响应时间。
代价:
- 创建索引和维护索引都会消耗时间,当对表中的数据进行了增删改操作时,索引要进行维护。另外索引都会占用一定的物理空间。
分类:
- 聚集索引
- 聚集索引会对表和视图进行物理排序,所以这种索引对查询非常有效,在表和视图中只能有一个聚集索引。SQL Server会为主键建立聚集索引,可以在任何列建立索引,一般定义为主键列建立聚集索引。
- 非聚集索引
- 非聚集索引并不在物理上排列数据,即索引中的逻辑顺序并不等于表中行的物理顺序,索引仅仅记录指向表中行的位置的指针,这些指针本身是有序的,通过这些指针可以在表中快速地定位数据。最多可以建立250个非聚集索引,或者249个非聚集索引和1个聚集索引。
- 唯一索引
- 唯一索引不允许两行具有相同的索引值,聚集索引和非聚集索引都可以是唯一的,因此只要列中数据是唯一的,就可以在一个表上创建一个唯一的聚集索引。
- 创建primary key 约束和unique约束会自动创建唯一索引。
- 唯一索引与手动创建唯一索引没有区别。
原则:
- 在创建索引时,是否适合创建索引,需要考虑,具体原则:
- 定义主键数据列一定要创建索引
- 定义外键数据列一定要创建索引
- 对于经常查询的数据列最好建立索引
- 对于需要在指定范围内快速或者频繁查询的数据列
- 经常用在where子句中的数据列
- 对于那些查询中很少涉及的列,重复值较多的列不要建立索引
- 对于定义为text,image和bit数据类型的列不要建立索引
建立和管理索引
- 设计索引是,应考虑以下数据库准则:
- 一个表如果建有大量索引,会影响insert,update和delete语句的性能。
- 避免对经常更新的表进行过多的索引,并且索引应保持较窄。
- 使用多个索引可以提高更新少而数据量大的查询的性能。
- 对小表进行索引可能不会产生优化效果
- 试图包含聚集函数,连接或聚集函数和连接的组合时,视图的索引可以显著提高性能。
使用Tracsact-SQL语句创建索引
- 语法格式
create [unique][clustered][nonclustered]
index 索引名
on 表名(列名[asc | desc],[列名])
- unique 唯一索引
- clustered 聚集索引
- nonclustered 非聚集索引
删除索引:
drop index 表名.索引名
- 注意:不能使用drop index删除primary key约束或unique约束创建的索引,要先删除约束。删除聚集索引时,表中所有的非聚集索引将被重建。
相关操作:
- 显示索引信息
- 在SSMS对象资源管理器中,选择索引名属性。
- 使用系统存储过程sp_helpindex
- 例如:exec sp_helpindex 表名
- 索引重命名
- 在SSMS对象资源管理器中,选择索引,右击,选择“重命名”。
- 使用命令
Exec sp_rename table_name.old_index_name,new_index_name
例如:exec sp_rename 'userinfo.ix_name','userinfo.ix_xingming'
索引的分析与维护
- 1.索引分析
- 建立索引的目的是希望提高SQL Server数据检索的速度,如果利用索引查询速度还不如扫描表的数据,则SQL会采用扫描表的方式,因此应根据应用系统的需要,也就是实际可能出现哪些数据检索,来对查询进行分析,以判断是否能提高检索速度。
- 分析方法:showplan_all和statistics io
- 语法:
use 数据库名
set showplan_all/statistics io
select 查询语句
- 2.索引维护
- 统计信息的更新
- 创建索引时,SQL Server会自动存储索引的统计信息,查询优化器利用索引的统计信息该索引进行查询的成本,但随着数据的不断变化,统计信息已经过时,从而导致查询优化器选择的查询处理方法不是最佳,所以有必要对数据库中的这些统计信息进行更新。
- 例如:使用update statistics命令更新索引统计信息
- 统计信息的更新
use 数据库名
update statistics 索引名
go
-
- 使用dbcc showcontig语句扫描表
- 对表进行数据操作可能会导致表碎片的产生,而碎片会导致读取额外页,从而造成数据查询性能的降低,可以使用dbcc showcontig语句扫描表,并对其返回值确定索引页是否严重不连续。
- 例如;
- 使用dbcc showcontig语句扫描表
use 数据库名
dbcc showcontig(userinfo,ix_name)
- 结果:返回统计信息,查看扫描浓度,理想为100%,如果较低,就需要清理表上的碎片了。