索引学习研究

概念：

• 索引是以表列为基础的数据库对象，它保存这表中排序的索引列，并记录了索引在数据表中的位置，实现表中数据的逻辑排序，其主要目的是提高SQL Server系统性能，加快数据查询和减少系统响应时间。

代价：

• 创建索引和维护索引都会消耗时间，当对表中的数据进行了增删改操作时，索引要进行维护。另外索引都会占用一定的物理空间。

分类：

• 聚集索引
  • 聚集索引会对表和视图进行物理排序，所以这种索引对查询非常有效，在表和视图中只能有一个聚集索引。SQL Server会为主键建立聚集索引，可以在任何列建立索引，一般定义为主键列建立聚集索引。
• 非聚集索引
  • 非聚集索引并不在物理上排列数据，即索引中的逻辑顺序并不等于表中行的物理顺序，索引仅仅记录指向表中行的位置的指针，这些指针本身是有序的，通过这些指针可以在表中快速地定位数据。最多可以建立250个非聚集索引，或者249个非聚集索引和1个聚集索引。
• 唯一索引
  • 唯一索引不允许两行具有相同的索引值，聚集索引和非聚集索引都可以是唯一的，因此只要列中数据是唯一的，就可以在一个表上创建一个唯一的聚集索引。
  • 创建primary key 约束和unique约束会自动创建唯一索引。
  • 唯一索引与手动创建唯一索引没有区别。

原则：

• 在创建索引时，是否适合创建索引，需要考虑，具体原则：
  • 定义主键数据列一定要创建索引
  • 定义外键数据列一定要创建索引
  • 对于经常查询的数据列最好建立索引
  • 对于需要在指定范围内快速或者频繁查询的数据列
  • 经常用在where子句中的数据列
  • 对于那些查询中很少涉及的列，重复值较多的列不要建立索引
  • 对于定义为text，image和bit数据类型的列不要建立索引

建立和管理索引

1. 设计索引是，应考虑以下数据库准则：
   1. 一个表如果建有大量索引，会影响insert，update和delete语句的性能。
   2. 避免对经常更新的表进行过多的索引，并且索引应保持较窄。
   3. 使用多个索引可以提高更新少而数据量大的查询的性能。
   4. 对小表进行索引可能不会产生优化效果
   5. 试图包含聚集函数，连接或聚集函数和连接的组合时，视图的索引可以显著提高性能。

使用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语句扫描表，并对其返回值确定索引页是否严重不连续。
    • 例如;

use 数据库名
dbcc showcontig(userinfo,ix_name)

• 结果：返回统计信息，查看扫描浓度，理想为100%，如果较低，就需要清理表上的碎片了。