索引及其应用

6.1索引

1.索引的用途
索是是以表列为基础的数据库对象,它保存表中排序的索引列,记录索引列在数据库中的物理存储位置,实现表中数据的逻辑排序。  可加快数据的查询速度和减少系统的响应时间。

2.使使索引的代价
创建与维护消耗时间,数据变化时,索引也要发生改变。同时占用一定的物理空间。

3.建立索引原则
对“主键”(会自动创建“唯一索引”),“外键”,“常查询的数据列”可建立索引。

4.索引的分类
1)聚集索引
会对表和视图进行物理排序。

2)非聚集索引
不会对表和视图进行物理排序。最多250个非聚集索引.

3)唯一索引
它不允许两行具有相同的索引值。


有些人可能对主键和聚集索引有所混淆,其实这两个是不同的概念,下面是一个简单的描述。不想看绕口文字者,直接看两者的对比表。尤其是最后一项的比较。
主键(PRIMARY KEY
来自MSDN的描述:
表通常具有包含唯一标识表中每一行的值的一列或一组列。这样的一列或多列称为表的主键 (PK),用于强制表的实体完整性。在创建或修改表时,您可以通过定义 PRIMARY KEY 约束来创建主键。
一个表只能有一个 PRIMARY KEY 约束,并且 PRIMARY KEY 约束中的列不能接受空值。由于 PRIMARY KEY 约束可保证数据的唯一性,因此经常对标识列定义这种约束。
如果为表指定了 PRIMARY KEY 约束,则 SQL Server 2005 数据库引擎 将通过为主键列创建唯一索引来强制数据的唯一性。当在查询中使用主键时,此索引还可用来对数据进行快速访问。因此,所选的主键必须遵守创建唯一索引的规则。 

创建主键时,数据库引擎 会自动创建唯一的索引来强制实施 PRIMARY KEY 约束的唯一性要求。如果表中不存在聚集索引或未显式指定非聚集索引,则将创建唯一的聚集索引以强制实施 PRIMARY KEY 约束。
 
聚集索引
聚集索引基于数据行的键值在表内排序和存储这些数据行。每个表只能有一个聚集索引,因为数据行本身只能按一个顺序存储。
每个表几乎都对列定义聚集索引来实现下列功能:
  • 可用于经常使用的查询。
  • 提供高度唯一性。
两者的比较
下面是一个简单的比较表
 
主键
聚集索引
用途
强制表的实体完整性
对数据行的排序,方便查询用
一个表多少个
一个表最多一个主键
一个表最多一个聚集索引
是否允许多个字段来定义
一个主键可以多个字段来定义
一个索引可以多个字段来定义
 
 
 
是否允许 null 数据行出现
如果要创建的数据列中数据存在null,无法建立主键。
创建表时指定的 PRIMARY KEY 约束列隐式转换为 NOT NULL
没有限制建立聚集索引的列一定必须 not null .
也就是可以列的数据是 null
参看最后一项比较
是否要求数据必须唯一
要求数据必须唯一
数据即可以唯一,也可以不唯一。看你定义这个索引的 UNIQUE 设置。
(这一点需要看后面的一个比较,虽然你的数据列可能不唯一,但是系统会替你产生一个你看不到的唯一列)
 
 
 
创建的逻辑
数据库在创建主键同时,会自动建立一个唯一索引。
如果这个表之前没有聚集索引,同时建立主键时候没有强制指定使用非聚集索引,则建立主键时候,同时建立一个唯一的聚集索引
如果未使用 UNIQUE 属性创建聚集索引,数据库引擎 将向表自动添加一个四字节 uniqueifier 列。
必要时,数据库引擎 将向行自动添加一个 uniqueifier 值,使每个键唯一。此列和列值供内部使用,用户不能查看或访问。
 



6.2建立和管理索引

1.建立索引
语法格式:
CREATE
[UNIQUE]
[CLUSTERED | NONCLUSTERED]
INDEX index_name
ON (table_name|view_name)
[WITH [index_property [,...n] ]

其中:
(1)UNIQUE:建立唯一索引。
(2)CLUSTERED:建立聚集索引。
(3)NONCLUSTERED:建立非聚集索引
(4)index_name:索引名称
(5)table_name:索引所在的表名称
(6)view_name:索引所在的视图名称。
注意:只有使用SCHEMABINDING定义的视图才能在视图上创建索引,且在视图上必须创建了唯一聚集索引之后,才能在视图上创建非聚集索引。

(7)index_property:索引属性。

例1:在BOOK数据库的BOOK1表上创建名为IX_book1的聚集,唯一,简单索引,该索引基于“编号‘列.
use book
go
CREATE UNIQUE CLUSTERED
INDEX IX_book1 ON book1(编号)
go

注:不指明clustered时,将默认为NONCLUSTERED

指定按索引页查询
select * from book with
  (index=IX_book1)

2.删除索引

语法格式:
DROP INDEX
Table_name.index_name[,table_name.index_name...]

例3.删除上列的索引 IX_book1
use book
go
DROP INDEX book1.IX_book1
go

使用DROP INDEX删除索引时,注意:
(1)不能用DROP INDEX删除PRIMARY KEY或UNIQUE约束创建的索引,首先应删PRIMARY KEY或UNIQUE约束才能用它来删除。
(2)删除聚集索引时,表中的所有非聚集索引都将被重建。

3.显示索引信息
例5.查看book数据库中book1表的索引信息
use book
go
EXEC sp_helpindex book1
go

4.重新命名索引
命令格式:
EXEC sp_rename table_name,old_index_name,new_index_name

把book数据库中的book1表的索引ix_book1重新命名为ix_book1new

use book
go
EXEC sp_rename 'book1.ix_book1','ix_book1new'
go


6.2.5 索引的分析与维护
1.索引的分析

如果索引的方式不如扫描的方式速度快,则系统自动用扫描的方式检索数据。因此,我们有必要分析建立的索引是否有意义。

两种分析索引和查询性能的方法:

1)SHOWPLAN_ALL

语法格式:
SET SHOWPLAN_ALL ON|OFF 或 SET SHOWPLAN_TEXT ON|OFF

例7 在book中的book1表上查询“编号='YBZT2406',并分析哪些索引被系统采用
use book
go
set showplan_all on
go
select *
from book1
where 编号='YBZT2406'
GO
set showplan_all off
go

2)STATISTICSIO
让SQL显示磁盘I0信息

语法格式:
SET STATISTICS IO ON|OFF

例8 在BOOK中的BOOK1表上查询“编号='YBZT2406'书的信息,并分析执行该数据检索所花费的磁盘活动量信息.

use book
go
set statistics io on
go
select *
from book1
where 编号='YBZT2406'
GO
SET STATISTICS TO OFF
GO

在“行运结果”的“消息”处可以看到结果


2.索引的维护
  由于数据的插入,更新和删除使数据杂乱无序,使索引性能下降。SQL提供了多种工具.

1)统计信息更新

  创建索引时,SQL会自动存储有关的统计信息,查询优化器利用索引统计信息估算使用该索引进行查询的成本。但随着数据的更新,这些统计信息已过时,因此需要更新。  

例9 使用UPDATE STATISTICS更新BOOK中的BOOK1表的IX_book1索引的统计信息.
use book
go
update statistics book1 ix_book1
go

2)使用DBCC SHOWCONTIG语句扫描表
  对表操作可能产生表碎片,表碎片导致读取额外页,造成数据查询性能降低。通过扫描来确定该索引面是否已经严重不连续.

利用DBCC SHOWCONTIG获取BOOK中BOOK1表的IX_book1索引的碎片信息.

use book
go
dbcc showcontig (book1,ix_book1)
go

3)使用DBCC INDEXDEFRAG语句进行碎片整理

例11 用DBCC INDEXDEFRAG对BOOK中BOOK1表的IX_book1索引进行碎片整理

use book
go
DBCC INDEXDEFRAG(book,book1,ix_book1)
go