索引---最直接的切入点（MSSQL个人笔记之数据库优化之路 一）

索引和书的中的索引差不多意思，也就是书的目录。

1.聚集索引 ：在聚集索引中，表中各行的物理顺序与键值的逻辑顺序相同。一个表中只可以有一个聚集索引。  如果表中有聚集索引，则该表为聚集表，如果没有则为堆的无序结构表。

2.非聚集索引：具有独立数据行的结构。包含非聚集索引键值，并且每个索引键值都有指向包含键值的数据行的指针。

创建索引

drop table tb
create table tb(id int,name varchar(50))
create unique clustered index  Clus_uniq_index_id on  dbo.tb (id)

查看索引

 

exec sp_helpindex tb

select * from sys.indexes where name='Clus_uniq_index_id'

修改索引

alter index Clus_uniq_index_id on tb disable

删除索引
DORP INDEX 表名.索引名

 

系统表连接查询

select o.name,i.name 
from sys.objects o 
join
 sys.indexes i
   on o.object_id=i.object_id 
    where o.name='tb'

    name            name                                                                                                               
    tb            Clus_uniq_index_id

 

统计信息是对索引的补充

     执行查询的时候，有时候查询优化器很难确定使用哪个索引，在sqlser中可以创建指定列或索引的数据分布情况统计信息，利用这个信息可以帮助sqlserver确定最佳的插叙计划。。

查看统计信息

select * from sys.stats s   
 join   
  sys.objects  o                                                                                   
on s.object_id=o.object_id
 where o.name='tb'

使用DBCC SHOW_STATISTICS命令

如下图，在执行DBCC SHOW_STATISTICS命令 有三个结果集。

第一个结果集合显示了统计信息名称，上次更新的统计信息的日期 表中记录的数量，统计信息的抽样行数，和所有索引列的平均长度。

第二个结果集合显示了 索引列前缀集使用的频繁性，。。。

第三个显示统计直方图的信息。。。

 

利用sp_autostats存储过程查看自动创建的统计信息

exec sp_autostats 'tb'

 

Global statistics settings for [ceshi]:
  Automatic update statistics: ON
  Automatic create statistics: ON
 
settings for table [tb]

Index Name                              AUTOSTATS                 Last Updated

[Clus_uniq_index_id]                ON                               2012-07-23 11:18:49.420

 

 创建统计信息

 统计信息是为优化查询提供参考依据，在创建索引的时候，系统自动创建了统计信息。

在数据插叙和数据操作的时候自动创建统计信息，数据库选项AUTO_CREATE_STATISTICS 用户控制是否自动创建统计信息，默认是ON

关闭自动生成的统计信息选项

ALTER DATEABASE 数据库名 SET AUTO_CREATE_STATISTICS OFF 

创建统计信息

create statistics ix_id on tb(id)

使用存储过程SP_CREATESTATS为当前数据库所有用户表的合格列和内部表创建单列的统计信息。

exec sp_createstats

 

更新统计信息

 

 更新tb表的所有统计信息

update  statistics tb

更新tb表指定的统计信息

update  statistics tb Clus_uniq_index_id

对表进行全表扫描，更新表tb的统计信息Clus_uniq_index_id

update  statistics tb (Clus_uniq_index_id) with fullscan

删除统计信息

DROP STATISTICS 表名.统计信息名

drop statistics tb.ix_id

 

使用索引优化数据库效率

 

 

不宜创建索引的情形

1.对经常插入，修改，删除的数据表 不宜创建过多的索引。

2.对数据量比较小的表不必需创建索引

 

 

适合建索引的情况

1.为where子句中出现的列创建索引

2.创建组合索引。

如果多个select语句中的where子句涉及多个列可以创建一个由多个列组成的索引。

3.group不要子句出现的列创建索引

 

 

聚集索引的设计原则

 

1.该列的数值是唯一的。或者说是很少有重复的

2.经常出现between....and  按顺序排序的列

3.定义为identity的唯一列

4.经常用于对数据进行排序的列

 

无法使用索引的Select语句

 及时正确的创建了索引，在select的时候也要正确的使用。否则就是可能无法在查询过程中应用索引。

1.对索引列应用了函数。就不会走索引了

  select * from tb where abs(id)=20

2.对索引列使用了like ‘%1’

select * from tb where id like '%1xxxx22' 就不会用到索引了

通配符用到后面就可以使用到索引 了

select * from tb where id like '1xxxx22%'

3.where子句对列进行了类型转换 是无法使用到索引的

select * from tb where  cast(name as varchar(20))='scott';

4.组合索引的顺序很重要

在组合查询中多个条件查询只有在组合索引的第一列索引查会使用 例如：

有一个组合索引

create index  id_name_fsex_index on test(name,sex,id)
select name from test where   name='mrzhou' and sex='男'

这样才会有索引的使用

如果

create index  id_name_sex_index on test(id,name,sex)

则复合索引就不会被使用。也就是说在使用复合索引的时候 要想在多条件查询走索引的话，要根据查询的条件设置索引的顺序。第一个索引列要是经常使用的。否则是不会被使用的。

 

5.where子句使用in关键字的情况

select * from Consume where cardno in ('3322','2323')  可以使用到cardno上的索引

而如果跟上子查询就会导致无法使用索引 如

select * from consume where cardno in (select cardno from creditcard where maxconsume>3000);

 

 

重新组织和生成索引

数据库中的数据经常发生变化，当插入修改删除数据的时候 数据库引擎会自动对索引维护， 久而久之这些修改会导致索引分散在数据库中，形成碎片。

select * from sys.dm_db_index_operational_stats(DB_ID(),OBJECT_ID(N'ceshi.tb'),null,null);

查询数据库ceshi 中tb表所有的索引统计信息

 

 

重新组织索引

   1》索引 右键-》 重新组织

   2》

use ceshi 
go
alter index Clus_uniq_index_id on tb reorganize;
go

 

重新生成索引

    1》索引 右键-》 重新生成索引

    2》

use ceshi 
go
alter index Clus_uniq_index_id on tb rebuild;
go

 

 

索引今天差不多就温习到这里，希望大家继续关注！！！！

 

 *作者：Stephenzhou(阿蒙)     
 *日期: 2012.07.31     
 *Mail:szstephenzhou@163.com     
 *另外：转载请著名出处。
 *博客地址：http://blog.csdn.net/szstephenzhou