6.1索引
1.索引的用途
索是是以表列为基础的数据库对象,它保存表中排序的索引列,记录索引列在数据库中的物理存储位置,实现表中数据的逻辑排序。 可加快数据的查询速度和减少系统的响应时间。
2.使使索引的代价
创建与维护消耗时间,数据变化时,索引也要发生改变。同时占用一定的物理空间。
3.建立索引原则
对“主键”(会自动创建“唯一索引”),“外键”,“常查询的数据列”可建立索引。
4.索引的分类
1)聚集索引
会对表和视图进行物理排序。
2)非聚集索引
不会对表和视图进行物理排序。最多250个非聚集索引.
3)唯一索引
它不允许两行具有相同的索引值。
创建主键时,数据库引擎 会自动创建唯一的索引来强制实施 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