SqlServer索引的原理与应用详解(2)-木庄网络博客

由于聚集索引是给数据排序，不可能有多种排法，所以一个表只能建立一个聚集索引。科学统计建立这样的索引需要至少相当与该表120%的附加空间，用来存放该表的副本和索引中间页，但是他的性能几乎总是比其它索引要快。

由于在聚集索引下，数据在物理上是按序排列在数据页上的，重复值也排在一起，因而包含范围检查（bentween,<,><=,>=）或使用group by 或order by的查询时，一旦找到第一个键值的行，后面都将是连在一起，不必在进一步的搜索，避免啦大范围的扫描，可以大大提高查询速度。

非聚集索引

sqlserver默认情况下建立的索引是非聚集索引，他不重新组织表中的数据，而是对每一行存储索引列值并用一个指针指向数据所在的页面。他像汉语字典中的根据‘偏旁部首'查找要找的字，即便对数据不排序，然而他拥有的目录更像是目录，对查取数据的效率也是具有的提升空间，而不需要全表扫描。

一个表可以拥有多个非聚集索引，每个非聚集索引根据索引列的不同提供不同的排序顺序。

创建索引

语法

CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED ]
INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[with[PAD_INDEX][[,]FILLFACTOR=fillfactor]
[[,]IGNORE_DUP_KEY]
[[,]DROP_EXISTING]
[[,]STATISTICS_NORECOMPUTE]
[[,]SORT_IN_TEMPDB]
]
[ ON filegroup ]

CREATE INDEX命令创建索引各参数说明如下：

UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。

CLUSTERED：用于指定创建的索引为聚集索引。

NONCLUSTERED：用于指定创建的索引为非聚集索引。

index_name：用于指定所创建的索引的名称。

table：用于指定创建索引的表的名称。

view：用于指定创建索引的视图的名称。

ASC|DESC：用于指定具体某个索引列的升序或降序排序方向。

Column：用于指定被索引的列。

PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间。

FILLFACTOR = fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。

IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL Server所作的反应。

DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。

STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。

SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。

ON filegroup：用于指定存放索引的文件组。

例子：

--表bigdata创建一个名为idx_mobiel的非聚集索引，索引字段为mobiel
create index idx_mobiel
on bigdata(mobiel) 


--表bigdata创建一个名为idx_id的唯一聚集索引，索引字段为id
--要求成批插入数据时忽略重复值，不重新计算统计信息，填充因子为40
create unique clustered index idx_id
on bigdata(id) 
with pad_index,
fillfactor=40,
ignore_dup_key,
statistics_norecompute

管理索引

Exec sp_helpindex BigData  --查看索引定义
Exec sp_rename 'BigData.idx_mobiel','idx_big_mobiel' --将索引名由'idx_mobiel' 改为'idx_big_mobiel'
drop index BigData.idx_big_mobiel --删除bigdata表中的idx_big_mobiel索引
dbcc showcontig(bigdata,idx_mobiel) --检查bigdata表中索引idx_mobiel的碎片信息
dbcc indexdefrag(Test,bigdata,idx_mobiel) --整理test数据库中bigdata表的索引idx_mobiel上的碎片
update statistics bigdata --更新bigdata表中的全部索引的统计信息

索引的设计原则

对于一张表来说索引的有无和建立什么样的索引，要取决与where字句和Join表达式中。

一般来说建立索引的原则包括以下内容：

系统一般会给逐渐字段自动建立聚集索引。
有大量重复值且经常有范围查询和排序、分组的列，或者经常频繁访问的列，考虑建立聚集索引。
在一个经常做插入操作的表中建立索引，应使用fillfactor(填充因子)来减少页分裂，同时提高并发度降低死锁的发生。如果在表为只读表，填充因子可设为100.
在选择索引键时，尽可能采用小数据类型的列作为键以使每个索引页能容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必需遍历的索引页面降低到最小，此外，尽可能的使用整数做为键值，因为整数的访问速度最快。

标签：SQL

返回前面的内容