索引

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

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

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

创建索引

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

查看索引

 

1. exec sp_helpindex tb  

 

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

修改索引

1. alter index Clus_uniq_index_id on tb disable  

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

 

系统表连接查询

1. select o.name,i.name   
2. from sys.objects o   
3. join  
4.  sys.indexes i  
5.    on o.object_id=i.object_id   
6.     where o.name='tb'  

    name            name                                                                                                               
    tb            Clus_uniq_index_id

 

统计信息是对索引的补充

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

查看统计信息

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

使用DBCC SHOW_STATISTICS命令

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

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

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

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

 

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

1. exec sp_autostats 'tb'  

1. Global statistics settings for [ceshi]:  
2.   Automatic update statistics: ON  
3.   Automatic create statistics: ON  
4.    
5. 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

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

1. ALTER DATEABASE 数据库名 SET AUTO_CREATE_STATISTICS OFF   

创建统计信息

1. create statistics ix_id on tb(id)

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

1. exec sp_createstats

更新统计信息

 

 更新tb表的所有统计信息

1. <strong>update  statistics tb</strong>  

更新tb表指定的统计信息

1. update  statistics tb Clus_uniq_index_id

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

1. update  statistics tb (Clus_uniq_index_id) with fullscan

删除统计信息

DROP STATISTICS 表名.统计信息名

1. 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.组合索引的顺序很重要

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

有一个组合索引

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

这样才会有索引的使用

如果

1. 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);

 

重新组织和生成索引

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

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

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

 

重新组织索引

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

   2》

1. use ceshi
2. go
3. alter index Clus_uniq_index_id on tb reorganize;  
4. go  

重新生成索引

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

    2》

1. use ceshi   
2. go  
3. alter index Clus_uniq_index_id on tb rebuild;  
4. go