【转】SQL Server创建索引

转自：http://blog.csdn.net/lenotang/article/details/3329501

 

什么是索引

拿汉语字典的目录页（索引）打比方 ： 正如汉语字典中的汉字按页存放一样， SQL Server 中的数据记录也是按页存放的，每页容量一般为 4K 。为了加快查找的速度，汉语字（词）典一般都有按拼音、笔画、偏旁部首等排序的目录（索引），我们可以选择按拼音或笔画查找方式，快速查找到需要的字（词）。

同理， SQL Server 允许用户在表中创建索引，指定按某列预先排序，从而大大提高查询速度。

•          SQL Server 中的数据也是按页（ 4KB ）存放

•          索引：是 SQL Server 编排数据的内部方法。它为 SQL Server 提供一种方法来编排查询数据 。

•          索引页：数据库中存储索引的数据页；索引页类似于汉语字（词）典中按拼音或笔画排序的目录页。

•          索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。

 

索引类型

•          唯一索引 ：唯一索引不允许两行具有相同的索引值

•          主键索引 ：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空

•          聚集索引 (Clustered ) ：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个

•          非聚集索引 (Non-clustered ) ：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于 249 个

 

索引类型：再次用汉语字典打比方，希望大家能够明白聚集索引和非聚集索引这两个概念。

 

唯一索引：

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

如果现有数据中存在重复的键值，则大多数数据库都不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时，数据库也拒绝接受此数据。例如，如果在 stuInfo 表中的学员员身份证号 (stuID) 列上创建了唯一索引，则所有学员的身份证号不能重复。

提示：创建了唯一约束，将自动创建唯一索引。尽管唯一索引有助于找到信息，但为了获得最佳性能，建议使用主键约束或唯一约束。

 

主键索引：

在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。

 

聚集索引（ clustered index ）

在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个聚集索引。例如：汉语字（词）典默认按拼音排序编排字典中的每页页码。拼音字母 a ， b ， c ， d……x ， y ， z 就是索引的逻辑顺序，而页码 1 ， 2 ， 3…… 就是物理顺序。默认按拼音排序的字典，其索引顺序和逻辑顺序是一致的。即拼音顺序较后的字（词）对应的页码也较大。如拼音 “ha” 对应的字 ( 词 ) 页码就比拼音 “ba” 对应的字 ( 词 ) 页码靠后。

 

非聚集索引 (Non-clustered )

如果不是聚集索引，表中各行的物理顺序与键值的逻辑顺序不匹配。聚集索引比非聚集索引（ nonclustered index ）有更快的数据访问速度。例如，按笔画排序的索引就是非聚集索引， “1” 画的字（词）对应的页码可能比 “3” 画的字（词）对应的页码大（靠后）。

提示： SQL Server 中，一个表只能创建 1 个聚集索引，多个非聚集索引。设置某列为主键，该列就默认为聚集索引

 

如何创建索引

使用 T-SQL 语句创建索引的语法：

CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] 

    INDEX   index_name

     ON table_name (column_name…)

      [WITH FILLFACTOR=x]

q       UNIQUE 表示唯一索引，可选

q       CLUSTERED 、 NONCLUSTERED 表示聚集索引还是非聚集索引，可选

q       FILLFACTOR 表示填充因子，指定一个 0 到 100 之间的值，该值指示索引页填满的空间所占的百分比

 

在 stuMarks 表的 writtenExam 列创建索引：

USE stuDB

GO

IF EXISTS (SELECT name FROM sysindexes

          WHERE name = 'IX_writtenExam')

   DROP INDEX stuMarks.IX_writtenExam 

/*-- 笔试列创建非聚集索引：填充因子为 30 ％ --*/

CREATE NONCLUSTERED INDEX IX_writtenExam

     ON stuMarks(writtenExam)

          WITH FILLFACTOR= 30

GO

/*----- 指定按索引 IX_writtenExam 查询 ----*/

SELECT * FROM stuMarks  (INDEX=IX_writtenExam)

    WHERE writtenExam BETWEEN 60 AND 90

虽然我们可以指定 SQL Server 按哪个索引进行数据查询，但一般不需要我们人工指定。 SQL Server 将会根据我们创建的索引，自动优化查询 。

 

索引的优缺点

•          优点

–         加快访问速度

–         加强行的唯一性

•          缺点

–         带索引的表在数据库中需要更多的存储空间

–         操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新

 

创建索引的指导原则

•          请按照下列标准选择建立索引的列。

–         该列用于频繁搜索

–         该列用于对数据进行排序

•          请不要使用下面的列创建索引：

–         列中仅包含几个不同的值。

–         表中仅包含几行。为小型表创建索引可能不太划算，因为 SQL Server 在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长

评论

2 楼 北极的。鱼 2011-08-09  

SQLSERVER 索引 填充因子

首先SQL SERVER里面有四种索引类型。

1.唯一索引：（创建唯一约束的时候自动生成唯一索引）

2.主键索引：（创建主键时自动生成索引，是一种特殊的唯一索引，和唯一索引的区别是【唯一允许NULL值，主键不允许为空】。并且他是特殊的聚集索引）

3.聚集索引：（自动会进行排序。每个表只能有一个。如果一个字段经常用来order by ,group by ,或者当作查询条件，那个这个字段有必设置成聚集索引）

4.非聚集索引：（非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个）

后面还要指定一个填充因子。填充因子：数据经常变化，填充因子就设置的越大，如果数据基本不变化，填充因子相对设置较小。(一般情况下只有不对数据进行操作时才进行此设置)。

创建索引时，可以指定一个填充因子，以便在索引的每个叶级页上留出额外的间隙和保留一定百分比的空间，供将来表的数据存储容量进行扩充和减少页拆分的可能性。

填充因子的值是从   0   到   100   的百分比数值，指定在创建索引后对数据页的填充比例。值为   100   时表示页将填满，所留出的存储空间量最小。只有当不会对数据进行更改时（例如，在只读表中）才会使用此设置。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中对数据页进行拆分的需要，但需要更多的存储空间。当表中数据会发生更改时，这种设置更为适当。

填充因子越大,意味着一个索引页包含的索引记录越多,空闲空间越小.一般来说查询的效率越高,因为这样查询的时候,可以减少读取索引页的工作量和减少内存使用  
但这样导致的结果是数据变动导致的索引维护的工作量增加,因为索引页的空闲空间小,如果不能在本页内完成索引调整,就会引起调整其他索引页 。

数据页
当索引建立起来的时候，SQLServer就建立数据页（datapage），数据页是用以加速搜索的指针。当索引建立起来的时候，其对应的填充因子也即被设置。设置填充因子的目的是为了指示该索引中数据页的百分比。随着时间的推移，数据库的更新会消耗掉已有的空闲空间，这就会导致页被拆分。页拆分的后果是降低了索引的性能，因而使用该索引的查询会导致数据存储的支离破碎。当建立一个索引时，该索引的填充因子即被设置好了，因此填充因子不能动态维护。

为了更新数据页中的填充因子，我们可以停止旧有索引并重建索引，并重新设置填充因子（注意：这将影响到当前数据库的运行，在重要场合请谨慎使用）。DBCC INDEXDEFRAG和DBCC DBREINDEX是清除clustered和nonculstered索引碎片的两个命令。INDEXDEFRAG是一种在线操作（也就是说，它不会阻塞其它表格动作，如查询），而DBREINDEX则在物理上重建索引。在绝大多数情况下，重建索引可以更好的消除碎片，但是这个优点是以阻塞当前发生在该索引所在表格上其它动作为代价换取来得。当出现较大的碎片索引时，INDEXDEFRAG会花上一段比较长的时间，这是因为该命令的运行是基于小的交互块（transactional block）。

1 楼 北极的。鱼 2011-08-09  

use master
go
if db_id(N'zhangxu')is not null
drop database zhangxu
go
create database zhangxu
sp_helpdb zhangxu
use zhangxu
go
IF EXISTS (SELECT *FROM SYS.OBJECTS WHERE NAME = N'WORKER')
DROP TABLE WORKER
GO
create table worker
(
w_id int identity (1000,1) not null,
w_name Nvarchar(10) unique,
w_age SMALLINT CONSTRAINT CK_W_AGE CHECK(w_age>20 and w_age<150),
w_pay money DEFAULT 0,
CONSTRAINT PK_W_ID PRIMARY KEY(W_ID)
)
SELECT *FROM WORKER--用查询技术查看表信息
sp_help worker--利用存储过程查看表信息
/*
创建简单的非聚集索引
*/
USE ZHANGXU
GO
if exists(select name from sys.indexes where name = N'IX_ID_NAME')
DROP INDEX IX_ID_NAME on worker
go--检查是否存在索引，有则删除索引
create index IX_ID_NAME--创建索引
on worker(w_id,w_name)--在ID NAME 两个字段上创建非聚集索引
drop index worker.IX_ID_NAME--删除索引
select *from sys.indexes where name = 'IX_ID_NAME'--查看索引
/*
创建唯一非聚集索引
*/
USE ZHANGXU
GO
IF EXISTS(SELECT NAME FROM SYS.INDEXES WHERE NAME = N'IX_W_NAME')
DROP INDEX IX_W_NAME ON WORKER
GO
CREATE UNIQUE INDEX IX_W_NAME--唯一非聚集索引
ON WORKER(W_NAME)
/*
查看索引T-SQL脚本
*/
--IX_W_NAME 唯一 非聚集索引
USE [zhangxu]
GO
/****** 对象:  Index [IX_W_NAME]    脚本日期: 07/29/2007 16:54:53 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_W_NAME] ON [dbo].[worker]
(
[w_name] ASC
)
WITH
(
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
IGNORE_DUP_KEY = OFF,
ONLINE = OFF
)
ON [PRIMARY]

--PK_W_ID聚集索引
USE [zhangxu]
GO
/****** 对象:  Index [PK_W_ID]    脚本日期: 07/29/2007 16:56:45 ******/
ALTER TABLE [dbo].[worker]
ADD  CONSTRAINT [PK_W_ID] PRIMARY KEY CLUSTERED
(
[w_id] ASC
)
WITH
(
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
ONLINE = OFF
) ON [PRIMARY]

--UQ_WORKER 唯一，非聚集索引
USE [zhangxu]
GO
/****** 对象:  Index [UQ__worker__07020F21]    脚本日期: 07/29/2007 16:58:38 ******/
ALTER TABLE [dbo].[worker]
ADD UNIQUE NONCLUSTERED
(
[w_name] ASC
)
WITH
(SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
ONLINE = OFF
) ON [PRIMARY]
select *from worker
insert into worker(w_name,w_age,w_pay) values('王国龙',25,4500)