建索引sqlserver,建索引需要考虑的因素

sqlserver怎么建立索引

什么是索引

成都创新互联公司是一家专注于成都网站设计、做网站与策划设计,阳春网站建设哪家好?成都创新互联公司做网站,专注于网站建设10余年,网设计领域的专业建站公司;建站业务涵盖:阳春等地区。阳春做网站价格咨询:18982081108

拿汉语字典的目录页（索引）打比方：正如汉语字典中的汉字按页存放一样，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在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长

sqlserver 怎么建索引

操作 1、

创建聚集索引

方法 1、

alter table table_name add constraint cons_name priamry key(columnName ASC|DESC,[.....]) with (drop_existing = on);

alter table Employee

add constraint PK_for_Employee primary key clustered (ID);

go

这个是一种特别的方法，因为在定义主键的时候，会自动添加索引，好在加的是聚集索引还是非聚集索引是我们人为可以控制的。

通过sp_helpindex 可以查看表中的索引

execute sp_helpindex @objname = 'Employee';

如何创建SqlServer视图的索引

这样一来，可从索引视图检索数据而无需重新编码，由此带来的高效率也使现有查询获益。在视图上创建的第一个索引必须是唯一聚集索引。在创建唯一聚集索引后，可创建其它非聚集索引。视图上的索引命名规则与表上的索引命名规则相同。唯一区别是表名由视图名替换。(Sql Server联机帮助)

语句：Create VIEW vXXX WITH SCHEMABINDING AS……

Create UNIQUE CLUSTERED INDEX idxXXX ON vXXX(cXXX)

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

A．视图必须使用With Schemabinding选项来创建；

如果创建视图时没有with Schemabinding，试图创建视图时就会报错：……因为该视图未绑定到架构

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

C．视图所用到的基本表必须和视图属于同一个所有者；

D．视图只能链接同一个数据库中的表；

E．视图不能包含一个外部链接或自链接，也就是说在链接表时只能使用INNER JOIN并且INNER JOIN前后不能使同一个表，不能使用LEFT（RIGHT） JOIN 或者 LEFT （RIGHT） OUTER JOIN ；

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

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

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

I． 视图不能包含Text、ntext、image类型的列；

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

K． 视图中的所有标和用户自定义的函数都必须使用两段式名来引用，即所有者.表或函数名称；

L． 所有的基本表和视图都必须使用 Set Ansi_Nulls On 创建；

M． 在创建索引时或创建索引后执行IUD时，必须显示或隐式地执行：

Set ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

SET NUMERIC_ROUNDABORT OFF

各个选项的有关信息或意义，可以查阅SQL Server的联机丛书，这里就不再介绍了；

N． 索引视图只有在SQL Server2000的企业版或开发版或者更高的版本中才能创建。