SQl浅谈 索引

1、索引的工作原理

我给大家推荐一个别人的总结。

http://blog.csdn.net/NightManHAHA/article/details/5648579

2、索引的设计原则

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

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

主键列：系统一般会自动建立聚集索引。

非主键列：有大量重复值并且经常进行条件查询、排序、分组的列，或者经常频繁访问的列，考虑建立聚集索引。

如果在一个经常做插入操作的表中建立索引，应使用fillfactor(填充因子)来减少页分裂，同时提高并发度降低死锁的发生。如果在表为只读表，填充因子可设为100。

另外我们在选择索引键的时候，尽量采用小数据类型（最好是整数）的列作为索引键，这样每个索引页能尽可能多的容纳索引键和指针，用整数的好处是因为整数的访问速度最快。

3、使用索引的注意事项

动作描述	使用聚集索引	使用非聚集索引
外键列	应	应
主键列	应	应
列经常被分组排序(order by)	应	应
返回某范围内的数据(BETWEEN、>、>=、< 和 <=)	应
小数目的不同值	应
大数目的不同值		应
频繁更新的列		应
频繁修改索引列		应
一个或极少不同值

4、索引的分类

按存储结构区分：

“聚集索引（又称聚类索引，簇集索引）”，“分聚集索引（非聚类索引，非簇集索引）”

聚集索引

每个表只能有一个聚集索引，默认情况下主键默认就是聚集索引。聚集索引确定表中数据的物理顺序。就好比字典中按拼音查找一样。

定义聚集索引时使用的列越少越好。

聚集索引不适用于:频繁更改的列（这将导致整行移动（因为 SQL Server 必须按物理顺序保留行中的数据值。因为在大数据量事务处理系统中数据是易失的）

聚集索引的约束是唯一性，并不是指字段也要是唯一的。

创建聚集索引语法：create  CLUSTERED  Index  索引名称 on 表名(需要创建索引列)

非聚集索引

一个表如果没有聚集索引时,理论上可以建立249个非聚集索引。每个非聚集索引提供访问数据的不同排序顺序。

如果创建索引时不加索引关键字，默认创建的就是非聚集索引。

数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。就好比字典中按偏旁查找一样。

创建非聚集索引语法：create NONCLUSTERED index 索引名 on 表名（创建索引列）

按数据唯一性区分：

“唯一索引”，“非唯一索引”

唯一键索引

建立唯一键约束（默认非聚集索引，实际上唯一键约束是用唯一索引来约束的）

创建唯一键约束,同时创建同名的唯一非聚集索引, 同时创建同名统计信息; 唯一键约束靠唯一索引来约束。

唯一键约束的索引不能像正常的索引使用太多的索引参数，因为唯一键约束与其索引同在。而单独创建的唯一索引可以设置更多的参数。

创建唯一键约束语法：  alter TABLE 表名 add  constraint   索引名称 unique(需要创建的列)（删除唯一键索引的语句跟删除主键聚集索引一样）

唯一索引

唯一索索引跟唯一键约束的作用是一样的，都是来检测数据的唯一性。

不管是建立唯一索引还是唯一约束，被创建的列都不允许有重复数据,重复的NULL值也不可以。

唯一索引创建语法：CREATE unique index ix_RowID  on TABLE(RowID)删除语句：drop index 索引名

唯一键索引与唯一索引对比

功能一样，唯一键索引比唯一索引多验证 unique key。

唯一键索引没有唯一索引灵活。

按键列个数区分：

“单列索引”，“多列索引”。

千万数据量时。多列索引会比多个单列索引速度快很多。

索引视图

索引视图是具体化的视图,它的结果集是经过计算的,并且存储在数据库中。

索引视图更适合在OLAP（读取较多，更新较少）的数据库中使用，不适合在OLTP（记录即时的增、删、改、查）的数据库中使用 。

一个标准视图转换为一个索引视图必须遵守以下规则：  

1．视图必须使用With Schemabinding选项来创建。如果创建视图时没有with Schemabinding，试图创建视图时就会报错，因为该视图未绑定到架构。注意： schemabinding建立索引的时候必须先创建唯一聚集索引。  

2．在这个视图中不能使用其他视图、导出表、行集函数或自查询，也就是说只能使用表。

3．视图只能链接同一个数据库中的表并且链接表时只能使用INNER JOIN。 INNER JOIN前后不能使同一个表，不能使用LEFT（RIGHT） JOIN 或者 LEFT （RIGHT） OUTER JOIN 。

4．视图不能包含UNION子句、TOP子句、ORDER BY子句、Having子句、Rollup子句、Cube子句、compute子句、Compute By子句或Distinct关键字。

5．视图不允许使用某些集合函数，如：Count（*）可以使用count_big（*）代替、avg()、max()、min()、stdev()、stdevp()、var()或varp()等。

6．视图不能使用Select * 这样的语句，也就是说视图的所有字段都必须显示指定。

7．视图不能包含Text、ntext、image类型的列。

8．如果视图包含一个Group By子句，那么他必须在Select列中包含count_big(*)。

位图索引（慎用）

位图索引适用于低基数的列，比如说“性别”列，数据仓库中的维表的主键，等等。理论上来说，他们都适合应用位图索引。但是这并不是使用位图索引唯一的条件。滥用位图索引会导致严重的错误，而且这些错误往往是很隐蔽的，不易被发现的错误。

位图索引的原理：

采用位图索引，一个键指向多行，有时候是数以百计甚至更多。如果更新了一个位图索引键，那么这个键指向的数以百计的记录会与你实际更新的那一行一同被锁定。

5、索引的查找

数据库中有一个名为sysindexes的系统表，专门管理索引。查看一张表的索引属性，可以在查询分析器中使用以下命令：select * from sysindexes where id=object_id(‘A)；而要查看表的索引所占空间的大小，可以使用系统存储过程命令：sp_spaceused A，其中参数A为被索引的表名。

6、索引语法

 [ UNIQUE ]唯一索引    [CLUSTERED]聚集索引   [NONCLUSTERED ] 非聚集索引

CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED ]

INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )

[with[PAD_INDEX][[,]FILLFACTOR=fillfactor]

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

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

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

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

]

[ ON filegroup ] //用于指定存放索引的文件组。

 

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

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

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

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

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

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

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

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

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

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

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

7、示例

--表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

8、清理索引

先分析表的索引：当你发现，扫描密度行，最佳计数和实际计数的比例已经严重失调。逻辑扫描碎片占了非常大的百分比，每页平均可用字节数非常大时，就说明你的索引需要重新整理一下了。

当索引碎片太多的时候，就会很严重地影响到查询的速度。检查索引碎片 DBCC SHOWCONTIG(表)。

这时候我们可以采取两种方法来解决：

一种时整理索引碎片（DBCC INDEXDEFRAG），另一种是重建索引（DBCC DBREINDEX）。

DBCC INDEXDEFRAG 只有在该命令正在运行时才可用。而且可以在不丢失已完成工作的情况下中断该操作。这种方法的缺点是在重新组织数据方面没有聚集索引的除去/重新创建操作有效。

重新创建聚集索引将对数据进行重新组织，其结果是使数据页填满。填满程度可以使用 FILLFACTOR 选项进行配置。这种方法的缺点是索引在除去/重新创建周期内为脱机状态，并且操作属原子级。如果中断索引创建，则不会重新创建该索引。

也就是说，要想获得好的效果，还是得用重建索引。

DBCC DBREINDEX(表，索引名，填充因子）

第一个参数，可以是表名，也可以是表ID。

第二个参数，如果是''，表示影响该表的所有索引。

第三个参数，填充因子，即索引页的数据填充程度。如果是100（这里是%），表示每一个索引页都全部填满，此时select效率最高，但以后要插入索引时，就得移动后面的所有页，效率很低。如果是0，表示使用先前的填充因子值。

DBCC DBREINDEX(A，''，100)

9、管理索引

select * from sysindexes where id=object_id('A')// 查看A表的索引属性

exec sp_spaceused A //查看表的索引所占空间的大小A为被索引的表名

 

--查看索引定义

Exec sp_helpindex A  

--将索引名由'id' 改为'idx'

Exec sp_rename A.id','idx' 

--删除A表中的idx索引

drop index A.idx

--检查A表中索引id的碎片信息

dbcc showcontig(A,id)

--整理test数据库中A表的索引id上的碎片

dbcc indexdefrag(Test,A,id)

--更新A表中的全部索引的统计信息

update statistics A