SQL Server 2005 数据库=====索引学习

索引是以表列为基础的数据对象，它保存着表中排序的索引列，并且记录了索引列在数据表中的物理存储位置，实现了表中数据的逻辑排序，主要是提高 SQL SERVE 系统的性能,加快数据的查询速度和减少系统的响应时间;除了可以提高查询表内数据的速度以外，还可以使表和表之间的连接速度加快;

使用索引的代价，创建索引和维护索引都会消耗时间，当对表中的数据进行增加，删除和修改的操作时，索引就要进行维护，否则索引的作用就会下降，另外每个索引都会占用一定的物理空间，如果占用的空间过多就会影响整个 SQL SERVE 系统的性能。

建立索引的原则：

（1）定义有主键的数据列一定要建立索引，因为主键可以加速定位表表中的某一行;

（2）定义有外键的数据列一定要建立索引，外键列通常用于表与表之间的连接，在其上创建索引可以加快表间的连接

（3）对于经常查询的数据列最好建立索引；

①:对于需要在指定范围内快速或频繁查询的数据列，因为索引已经排序，其指定的范围是连续的，查询可以利用索引的排序，加快查询 的时间

②:经常用在 where 子句中的数据列，将索引建立在 where 子句的集合过程中，对于需要加速或频繁检索的数据列，可以让这些经常参 与查询的数据列按照索引的排序进行查询，加快查询的时间

（4）对于那些查询中很少涉及到的列，重复值比较多的列不要建立索引，如：在查询中很少使用的列，有无索引并不能提高查询速度相反曾 加了系统维护时间和消耗了系统空间；

（5）对于定义为 text,image,bit 数据类型的列不要建立索引，因为这些数据类型的数据死的数据量要么很大，要么很小，不利于使用索引

索引的分类

有3种索引类型：聚集索引、非聚集索引和唯一索引 如果表中存在聚集索引，则非聚集索引使用聚集索引来加快数据查询

1)聚集索引

聚集索引会对表和视图进行物理排序，所以这种索引对查询非常有效，在表和视图中只能有一个聚集索引；当你建立主键约束时，如果表中 没有聚集索引，SQL 会用主键列作为一个聚集索引，可以在表任何列或列的组合上建立索引，实际中一般为定义成主键约束的列建立聚集索 引

2)非聚集索引

非聚集索引不会对表和视图进行物理排序，如果表中不存在聚集索引，则表是未排序的，在表或视图中，最多可以建立250个非聚集索引，或者249介非聚集索引和1个聚集索引；

3)唯一索引

唯一索引根据数据库的功能，可在数据库设

计器中创建3种类型的索引----唯一索引、主题索引、和聚集索引；

唯一索引[不允许两行具有相同的索引值，如：在表中的姓名字段上建立了唯一索引，就不能有相同姓名的存在]

聚集索引和非聚集索引都可以是唯一的，因此只要列中数据是唯一的，就可以在同一个表上创建一个唯一的聚集索引；如果必须实施唯一性以确保数据的完整性，则应在列上创建 unique 或 primarykey 约束，而不要创建唯一索引；

创建 unique 或 primarykey 约束 会在表中指定的列上自动创建唯一索引，创建 unique 约束与手动创建唯一索引没有明显区别，进行数据查询的方式相同，而且查询优化器不区分唯一索引是由约束创建还是手动创建，如果存在重复的键值刚无法创建；

唯一索引和 unique 约束，在同一个列上创建唯一索引而不是非唯一索引，可为查询优器提供附加信息，所以最好创建唯一索引；

建立和管理索引

sql server management studio-> 对象管理器-> 选择表-> 选择索引右击-> 新建索引-> 输入索引名-> 添加-> 确定 完成

使用T-SQL 建立索引:

create [unique][clustered | nonclustered] index index_name ON {table_name | view_name} [WITH [index_propery[,...n]]]

说明：unique建立唯一索引

clustered建立聚集索引

nonclustered建立非聚集索引

index_name索引所在的表名称

view_name索引所在的视图名称 注意：只有使用 schemabinding 定义的视图才能在视图上创建索引，且在视图上必须创建了唯一聚集索引之后，才能在视图上创建非聚集索引

index_property索引属性，如 drop_existing 表示先删除存在的索引(如果不存在则有错误提示信息)

例：使用 Transact-SQL 在book数据库中的book1表创建名为 LX_book1 的聚集，唯一，简单索引，该索引基于“编号”列创建

use book

GO

create unique clustered index IX_book1 ON book1(编号)只有表的所有者才能执行 create index 语句来创建索引

GO

用户在创建和使用唯一索引时应注意如下事项：

(1)unique 索引既可以采用至今索引的结构，也可以采用非聚集索引的结构，如果不指明clustered选项那么SQL索引默认采用非聚集索引结构

(2)建立unique索引的表在执行insert语句或update语句时，SQL-SERVER 将自动检验新的数据中是否存在重复值，如果存在，则SQL-SERVER在每一个重复值处取消语句，并返回错误提示信息；

(3)具有相同组合列，不同组合顺序的复合索引彼此是不同的

(4)如果表中已有数据，那么在创建UNIQUE索引时，SQL-SERVER将自动检验是否存在重复值，若有重复值，则不能创建UNIQUE索引

删除索引

在sql server management studio-> 对象管理器-> 选择表前面的+ -> 选择索引右击->删除相关索引

使用T-SQL 删除建

立索引:

DROP INDEX

Table_name.index_name[,table_name.index_name...]

其中：table_name索引所在的表名称 index_name要删除的索引的名称

例：删除刚才建立的索引

USE book

GO

DROP INDEX book1.IX_book1

GO

在用DROP INDEX 语句删除索引时，需要注册如下事项:

(1)不能用 dorp index 语句删除由 primary key 约束或 unique 约束创建的索引，要删除这些索引必须先删除 primary key 约束或说 unique 约束

(2)在删除聚集索引时，表中的所有非聚集索引都将被重建；

索引相关的操作

在建立索引后，可以对索引信息进行查询，下面是两种查询方法：

1）：在sql server management studio-> 对象管理器->使用与创建索引相同的方法右键属性 可以查看相应的信息

2）：使用系统存储过程 sp_helpindex 能查看指定表的索引信息

例子：使用系统存储过程 sp_helpindex 查看 book 数据库中 book1 表的索引信息

USE book

GO

EXEC sp_helpindex book1

GO

这样就给出了 book1 表上所有索引的名称，类型和建立索引的列

重新命名索引，有以下两种方法:

1）在sql server management studio-> 对象管理器->使用与创建索引相同的方法右键属性-》重命名

2）exec sp_rename table_name.old_index_name,new_index_name

其中：table_name 索引所在的表名称 old_index_name 要重新命名的索引的名称 new_index_name 新的索引名称

例子：使用T-SQL 将 book 数据库的 book1 表的索引 IX_book1 重新命名为 IX_booklnew:

USE book

GO

EXEC sp_rename 'book1.IX_book1','IX_book1new'

GO

索引的分析与维护

分析:建立索引的目的是希望提高SQL-SERVER 数据检索的速度，如果利用索引查询的速度还不如扫描表的速度，SQL就会采用扫描表而不是通过索引的方法来检索数据；因此在建立索引后，应该根据应用系统的需要，也就是实际可能出现的哪些数据检索，来对查询进行分析，以判定其是否能提高sql 的数据检索速度；

SQL-SERVER 提供了多种分析索引和查询性能的方法，下面有常用的两种 showplan_all 和 statisticsio 两种命令

1)SHOWPLAN_ALL 显示查询计划是 SQL-server 将显示在执行查询的过程中连接表时所采取的每个步骤以及是否选择及选择了哪个索引，从而 帮助用户分析哪些索引被系统采用;通常在查询语句中设置 showplan_all 选项 可以选择是否让sql-server 显示查询计划，设置是否显示查询计划的命令为: set showplan_all on|off 或 set showplan_text on|off

例如：在book数据库中的book1表上查询 “编号=‘ybzt2406‘”书的信息，并分析哪些索引被系统采用

USE BOOK

GO

SET SHOWPLAN_ALL ON

GO

SELECT * FROM book1 WHERE 编号=‘ybzt2406’

GO

SET SHOWPLAN_ALL OFF

GO

2)statisticsio

数据检索语句所花费的磁盘活动量也是用户比较关心的性能之一

，通过设置 statisticsio 选项可以使用SQL-server 显示磁盘IO信息

例如：在book数据库中的book1表上查询 “编号=‘ybzt2406‘”书的信息，并分析执行该数据检索所花费的磁盘活动量的信息:

USE book

GO

SET statistics IO ON

GO

SELECT * FROM bool1 WHERE 编号=‘ybzt2406’

GO

SET statisticsIO OFF

GO

索引的维护：

在创建索引后，为了得到最佳的性能，必须对索引进行维护，SQL-server 提供了多种工作帮助用户进行索引的维护，通常有以下几种方式：

1)统计信息更新:在创建索引时，SQL会自动存储有磁的统计信息，查询优化器会利用索引统计信息估算使用该索引进行查询的成本，然而随着数据的不断变化，索引和列的统计信息可能已经过时，从而导致查询优化器选择的查询处理方法不是最佳的，因些，有必要对数据库中的这些统计信息进行更新；而且应该频繁的进行索引统计的更新，特别应避免在数据库操作比较集中的时间段内更新统计；

例如：使用UPDATE STATISTICS 命令更新 book 数据库中的 book1 表的 IX_book1 索引的统计信息

USE book

GO

UPDATE STATISTICS book1 IX_book1

GO

2)使用 DBCC SHOWCONTIG 语句扫描表;对表进行数据操作可能会导致表碎片的产生，而表碎片会导致读取额外页，从而造成数据查询性能的降低，此时，用户可以通过使用 dbcc showcontig 语句来扫描表，并通过其返回的值确定该索引页是否已经严重不连续;

例如：利用 DBCC showcontig 获取 book 数据库的book1表的 IX_book1 索引的碎片信息

use book

go

dbcc showcontig (book1,IX_book1)

go

运行结果的返回统计信息中，需要注意扫描密度，其理想值为100% 如果比较低，就需要清理表上的碎片了

3)使用 dbcc indexdefrag 语句进行碎片整理:当表或视图上的聚集索引和非聚集索引页级上存在碎片时，可以通过 DBCC INDEXDEFRAG 对其进行碎片整理

例如：用DBCC INDEXDEFRAG 命令对book数据库中book1表的IX_book1索引进行碎片整理:

use book

go

dbcc indexdefrag(book,book1,IX_book1)

go

====== 学习重点 ====================================================================================================

了解索引的使用

学会使用对象资源管理器或查询分析器来创建索引

学会创建唯一、聚集索引、复合索引

学会查看和修改索引选项，以及给索引改名和删除索引

引用索引的主要目的

创建索引有优缺点有哪些

删除索引时所对应的数据表是不会删除

如果查看索引碎片

SQL-SERVER 中聚集索引和非聚集索引的区别

碎片的整理