------我的一些笔记(三)--------

--小F-- 2010-01-12 09:20:48

加精

--结合sys.indexes和sys.index_columns,sys.objects,sys.columns查询索引所属的表或视图的信息

select

  o.name as 表名,

  i.name as 索引名,

  c.name as 列名,

  i.type_desc as 类型描述,

  is_primary_key as 主键约束,

  is_unique_constraint as 唯一约束,

  is_disabled as 禁用

from

  sys.objects o 

inner join

  sys.indexes i

on

  i.object_id=o.object_id

inner join 

  sys.index_columns ic

on

  ic.index_id=i.index_id and ic.object_id=i.object_id

inner join

  sys.columns c

on

  ic.column_id=c.column_id and ic.object_id=c.object_id

go







--查询索引的键和列信息

select 

  o.name as 表名,

  i.name as 索引名,

  c.name as 字段编号,

from

  sysindexes i inner join sysobjects o 

on

  i.id=o.id

inner join

  sysindexkeys k 

on

  o.id=k.id and i.indid=k.indid

inner join

  syscolumns c 

on

  c.id=i.id and k.colid=c.colid

where

  o.name='表名'









--查询数据库db中表tb的所有索引的随片情况

use db

go

select 

  a.index_id,---索引编号

  b.name,---索引名称

  avg_fragmentation_in_percent---索引的逻辑碎片

from

  sys.dm_db_indx_physical_stats(db_id(),object_id(N'create.consume'),null,null,null) as a

join

  sys.indexes as b

on

  a.object_id=b.object_id 

and

  a.index_id=b.index_id

go



---解释下sys.dm_db_indx_physical_stats的参数

datebase_id: 数据库编号，可以使用db_id()函数获取指定数据库名对应的编号。

object_id: 该索引所属表或试图的编号

index_id: 该索引的编号

partition_number:对象中分区的编号

mode:模式名称,用于指定获取统计信息的扫描级别。





有关sys.dm_db_indx_physical_stats的结果集中的字段名去查下联机丛书。



---索引视图



索引视图是具体化的视图



--创建索引视图

create view 视图名 with schemabinding 

as

select 语句

go



---创建索引视图需要注意的几点

1. 创建索引视图的时候需要指定表所属的架构

--错误写法

create view v_f with schemabinding 

as

select

   a.a,a.b,b.a,b.b

from

   a join b 

on

   a.id=b.id

go



---正确写法：

create view v_f with schemabinding 

as

select

   a.a,a.b,b.a,b.b

from

   dbo.a join dbo.b 

on

   a.id=b.id

go





2.在创建索引视图的select语句时，不使用*,必须指定具体的列名

--错误写法

create view v_f with schemabinding 

as

select

   *

from

   dbo.a join dbo.b 

on

   a.id=b.id

go



---正确写法

create view v_f with schemabinding 

as

select

   a.a,a.b,b.a,b.b

from

   dbo.a join dbo.b 

on

   a.id=b.id

go



3.在创建索引视图的select 语句中，不能存在重复的列名，这个不举例了



4. 只能为索引视图创建唯一聚集索引

--正确的写法

create unique clustered index ix_uniquetb on v_tb

go



--错误的写法 

create clustered index ix_uniquetb on v_tb

go



---使用索引优化数据库查询效率

1.不宜创建索引的情形

(1)经常插入，修改和删除的表

(2)数据量比较小的表，因为查询优化器在搜索索引时所花费的时间可能会大于遍历全表的数据所需要的时间



2.适合创建索引的情形

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

(2)创建组合索引

(3)为group by 子句中出现的列创建索引



3.聚集索引的设计原则

(1)该列的数值是唯一的或者很少有重复的记录

(2)经常使用between ...and..按顺序查询的列

(3)定义identity的唯一列.

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



---无法使用索引的select语句

1.对索引列使用了函数,如：

select * from tb where max(id)=100



2.对索引列使用了'%xx'，如：

select * from tb where id like '%1'

需要注意的不是所有使用like关键字的select 语句都无法使用索引,比如

select * from tb where id like '1%'就可以使用索引



3.在where子句中对列进行类型转换(其实也是使用到了函数)



4.在组合索引的第1列不是使用最多的列,如在下面3个查询语句中建立组合索引,按顺序包含col2,col1,id列；

select * from tb where id='1' and col1='aa'

select id,sum(col1) from tb group by id

select * from tb where id='2' and col2='bb'

则第一句和第二句无法使用到索引 所以需要注意组合索引的顺序



5.在where 子句中使用in关键字的某些句子

当在in关键字后面使用嵌套的select语句，将无法使用在该列上定义的索引

如：

select 

 *

from

 ta 

where

 id 

in

 (select id from tb where ....)



--这样可以用到索引

select * from tb where id in('1','2')