mssql大全(中）

设计、创建和维护索引(index)（上）

1:索引的基本概念:

2:索引的设计准则:

3:索引的类型:

4:聚集索引和非聚集索引:

5:唯一索引和非唯一索引:

A:索引的基本概念

索引在关系型数据库中扮演着极其重要的角色,它最主要的作用是:

1:确保数据的唯一性:2:提高数据查询速度

提高数据的查询速度

为什么可以提高数据的访问速度:索引可以把表中的数据进行排序.

举例:创建一个work表最好有80万行.执行:select * from work where 年龄 1:[不建索引]观察所用时间

2:[创建一个索引]观察所用时间

1:表扫描:一行一行把表的数据全部扫描到.[tabscan]

2:使用索引:把表的数据进行排序,从而可以提高数据的查询效率

3:索引的结构:索引页,数据页

举列说明:

A:select * from work where 职工号=\'001\'.如果事先能够为职工号建立索引.则可以提高查询速度

B:select * from work where 基本工资>1200 and 基本工资 C:select * from work order by 基本工资.若能够事先为基本工资创建索引也能够提高查询效率

sql server在什么时候要使用索引:

不要以为只要为某个字段创建索引之后,sql server在查询的时候就一定要使用它.事实上sql server在执行查询之前会通过一个叫“查询优化器”的对像来判断是采用表扫描,还是使用索引,如果利用索引不能使查询得到优化,则不会使用索引mssql查看数据库,而改用表扫描.

确保数据的唯一性:

索引中有一类索引叫唯一索引(unique),利用它可以保证以该字段为索引的值不会出现重复但它可以为空.与主键有区别,当然如果你创建的索引不要求保持数据唯一性,只是为了提高查询效率你完全可以不建唯一索引,建一个一般的索引就行了.当然确保数据唯一性的方法有很多,比如可以创立主键等.索引只是确保数据唯一性的其中一种方法.

B:设计索引的准则

1:经常被用来查询数据记录的字段.

说明:经常被用来查询数据记录的字段最好是识别度较高的字段(也就是数据内容重复性低).

比如:使用员工编号或身份证号来作索引是非常恰当的.但如果使用家庭住址或使用基本工资来寻找员工就显然不恰当.家庭住址一般有很多字符,会使索引过于庞大,基本工资识别性太低.

2:主键所定义的字段一般应作索引,不过sql server自动帮我们作好了

3:在查询中用来连接表的字段:

4:经常用作排序的字段:

说明:不能为bit,text,image等数据类型的字段创建索引.

索引的负面影响:

那既然索引可以加快查询效率,那我们就把索引建多,越多越好,这一种说法完全错误.

理由:

1:索引显然会占用磁盘空间,创建不必要的索引只会浪费空间[任务面板中查看]

一个索引的大小绝对大于以该字段作为索引的大小乘以记录条数.

2:以某个字段创建一个索引之后,当以后修改或添加该字段时,为了让索引能够实时反映表的动态更新必须有相关的索引.[索引页]

所以得到一个结论:如果一个表是作为引用性的,也就是说它仅仅只作为查询,或者说它的改动非常少,多几个索引倒也无所谓,但是一个经常性修改的表则请谨慎的创建必须的索引[举列说明]

A:在work中执行update work set 年龄=年龄+1 where 年龄=20[观察使用时间]

B:在work中创建一个索引以年龄为索引,再执行同样的语句[观察使用的时间]

3:数据记录越多,索引提高数据访问效率越明显,反过来如果表内含有数据记录很少,建个索引是不会有任何作用的,所以不主张对一个记录很少的表创建索引

C:索引的类型

1:按存储结构区分:聚集索引(clustered index),非聚集索引(nonclustered index)

2:按数据唯一性分:唯一索引(unique index)和普通索引

3:以键列的个数分:单列索引与多列索引.

D:聚集索引和非聚集索引

聚集索引:数据页内容排列的次序与索引页的索引字段的值相同.即:聚集索引会改变表原先的排列顺序使之按照索引升序或降序排列.

正由于聚集索引会使得值相同的数据记录排列在一起,因此要搜索介于某范围的数据值时将会特别有效.因为一但使用聚集索引找到第一条符合条件的记录,则同范围之后的记录保证是相邻排列的,也就是说把这一范围找到之后就不会往下面找了.

怎么使用聚集索引

一般而言下列的数据访问操作非常适合使用聚集索引:

1:如果某个字段所包含的有差别的数据较少,则非常适合以该字段创建聚集索引.比如:员工表中部门字段用来存放员工所在的部门.但一个单位的部门不会超过十个,则以部门创建聚集索引最好.[为什么？]

2:使用betwwen,>,>=,自动更新统计信息

手动更新:

请注意:sql server在对“统计信息”进行更新时,它是不会分析表的所有数据的,而是会从表中随机抽样,这样可以极大地减少这种统计信息的自动更新所带来的成本.所以有可能在某些情况下,统计采样无法获得表中精确的“统计信息”,这个时候我们可以手动更新,比如:我们可以指定对表中所有数据进行抽样以取得精确的“统计信息”,或者指定对表的多少行进行采样.

如何手动更新:

update statistics 表名 索引名 [with fullscan或者sample 数值 percent或者rows]

说明:

1:如果省略索引名将对表的所有索引进行手动更新

2:如果使用fullscan关键字,则表示对表中所有行进行采样,这样会费很长时间,但会得到更优的查询

3:如果使用sample 数值 percent则表示对整个表行的百分之几进行采样

4:如果使用sample 数值 rows则表示对整个表的只取多少行进行采样.

比如: 在work表创立两个索引 年龄,基本工资

如果执行:

1:update statistics work with fullscan[表示什么意思]

2:update statistics work with sample 10 percent[表示什么意思]

3:update statistics work with sample 10 rows[表示什么意思]

什么时候需要手动更新:如果索引列中添加、更改或删除大量数据请使用update statistics手动更新

I:创建索引

1:通过企业管理器:

A:索引文件组:可以把索引也放在数据库的文件组中.你可以把关于一个表中创建的索引与表放在一个文件组中,也可以把基于一个表的索引放在不同的文件组中,这样可以在查询时由表所在的驱动器与该表索引所在的驱动器同时并行查询,从而提高查询效率.

B:但是一个聚集索引只能够放在primary组中.

C:不自动重新计算:如果选择该项则sql server不会自动更新“统计信息”,当用户在对表作了大量的修改之后再查询时因为“优化器”使用的是“过时的统计的信息”,所以可能得不到最优的查询,除非用户手动更新.

2:通过sql语句.

格式:create [unique][clustered] index 索引名 on 表名(列[asc|desc]) with fillfactor=整数

说明:

A:如果省略unique则是非唯一索引

B:如果省略clustered表示一个非聚集索引

C:在列后面可以指明是升序还是降序,如果省略则为asc(升序),否则必须指明desc(降序)

D:如果要创建多列索引可以写多个列,列与列之间用逗号隔开.

例如:

A:create unique clustered index 年龄 on work(年龄 desc)

B:create unique clustered index 年龄,基本工资 on work(年龄 desc,基本工资)

思考:

假定你要创建一个聚集索引和一个非聚集索引,你是先创建非聚集索引还是先创建聚集索引

结论:

sql server会在删除或创建任何聚集索引的时候重新创建非聚集索引,所以在创建任何非聚集索引之前创建聚集索引.

J:删除索引:

注意以下两点:

1:当一个索引不再需要,可以将其从数据库中删除,以回收它当前使用的存储空间.这些回收的空间可以由数据库中任何对象使用.

2:当你把一个聚集索引删除后,sql servr必须把以前的同一个表上的所有非聚集索引重新创建一次,所以删除一个聚集索引比删除一个非聚集索引要花费更多的时间.

3:只有表的所有者或sa可以删除其索引.

方法:

A:通过企业管理器:打开表的设计窗口,单右->索引->删除

B:通过sql语句:格式:drop index 表名.索引名

设计、创建和维护索引(index)（下）

11:索引的维护操作:

12:索引的评估和分析:

13:为计算字段创建索引:

14:为视图创建索引:

15:索引优化向导.

K:索引的维护

你创建了一个索引之后,用户可能要对表进行修改,而为了让索引能发挥它的最大用途,必须对它进行维护.

我们通常对索引有下面的维护:

1:重建索引

一个索引已经创建好了,为什么还要去重建索引呢？

A:为什么要重建索引:

在数据库中创建索引时,查询所使用的索引信息存储在索引页中,随着用户对表进行不断的修改,填充因子会不断的改变,这样就会生成很多碎片,这显然不利于查询.我们可以重建索引以重新组织索引数据来达到清除碎片的目的.

B:怎么重建索引:

思考:假设你已经为work表作了一个“年龄”聚集索引,随着用户不断更新该表,使它产生很多碎片,现在要重建你该怎么办:[首先删除原先建的年龄索引,再来创建一个年龄索引,行吗]

结论:这样做虽然可以,但会花费更多的成本,因为我们已经知道,每用户删除一个聚集索引,时sql server会把该表所有的非聚集索引全部删除之后再重新创建,显然不划算,那么可以在不删除原有索引的基础上来新建一个与原索引一样的索引吗？当然可以.

那么可以看到不删除原有的索引而重新创建一个索引的好处,有以下两点:

1:免了删除和重新创建非聚集索引这些不必要的工作

2:可以使用现有索引中的数据排序次序,从而避免了对数据重新排序.这对于聚集索引十分有用,可以显著减少重建索引的成本.

方法:只需要在创建索引的时候加上with drop_existing关键字就行了.

比如:create unique clustered index 年龄 on work(年龄 desc) with drop_existing

思考:是否执行了这个命令之后就意味着所有的非聚集索引不会删除之后再创建了呢？

结果:当建的新的聚集索引与原聚集索引的列相同时,才不会删除非聚集索引,否则还是会删除非聚集索引之后再创建.

2:查看索引:

在创建索引之后我们可能需要

A:查看某个索引类型

B:一个表的全部索引

C:某个索引使用的数据库空间总量.

方法:

1:查看表中的索引

格式:sp_helpindex 表名

2:查看数据库中的全部索引及使用的空间

通过:任务面板

3:碎片整理

我们已经知道,当用户对一个表创建一个索引之后,由于用户对表不断的改变,会导致产生很多碎片.你既可以重建索引,也可以选择不重建索引,而直接对索引进行碎片整理.

方法:dbcc indexdefrag(数据库名,表名,表中的索引名)

比如:dbcc indexdefrag(陈文杰,work,年龄)[对数据库陈文杰表work里面的年龄索引进行碎片整理]

显示的信息会给出:有多少页扫描了,有多少页被移动了,有多少页被删除了

注意:若索引的碎片相对较少,则整理该索引的速度比生成一个新索引要快,这是因为碎片整理所需的时间与碎片的数量有关.对碎片太多的索引进行整理可能要比重建花更多的时间.

4:更名

方法:sp_rename \'表名.原索引名\',\'新索引名\',\'index\'

例如:sp_rename \'work.年龄\',\'年\',\'index\'[把work表“年龄”索引名改为“年”]

5:索引管理器:可以方便对选择的数据库中相应的索引进行修改,删除,还可以方便进行索引的新建删除等.

操作方法:在企业管理器中选择某个表->单右->管理索引

L:索引的评估和分析:

实际上评估一个查询是不是最优查询,并不是由时间来决定的,因为相同的一个查询在计算机很忙的时候运行所花费的时间绝对要大于计算机很空闲的时候运行所花费的时间,因为当计算机很忙的时候cpu要把时间分给其它的程序,这显然会延长一个查询所花的时间,而当计算机很闲时,相当于把cpu的时间全部给这个查询,可以减少运行时间,所以仅凭运行时间来判定一个查询是否最优是不精确的,那么我们可以根据这个查询所花费的成本来决定一个查询的优劣.可以通过“执行计划”来察看

方法:显示执行计划,快捷方式:ctrl+k

A:查询分析器是采用表扫描还是采用索引来执行查询.

B:如果采用索引查询,是用的哪个索引

C:查询所花费的成本.

i/o成本:用于操作的所有I/O活动的预计成本(I/O成本).该值应尽可能低.

成本:查询优化器执行此操作的成本占查询总成本的百分比.由于查询引擎选择最高效的操作执行查询或执行语句,因此该值应尽可能低.

M:为计算字段创建索引

计算列虽然不是表的的字段,但是可以参与查询,所以为了加快查询效率,我们有时候希望在计算列上创建索引,在计算列上创建索引的方法与在非计算列中创建索引一样,但是需要注意二点:

在创建表的时候[不是在表创建好之后],既在执行create table的时候必须满足:

1:为计算列的表达式上使用的表的字段类型不能有float型,被称为不确定或不精确

2:ANSI_NULLS:必须设置为on 方法:SET ANSI_NULLS on[一般情况下]

说明:默认为off,表示可以在查询中使用=null或null条件,否则不允许使用

N:为视图创建索引

我们知道视图并没有存储数据,而是存储定义,所以当视图在刚刚打开的一瞬间会根据定义从相关的表中收集数据,相当于从相关表中做一个查询,然后再把数据导入到视图中,也就是要动态生成与视图定义相关的结果集.

于是用户要对一个视图进行查询时,将经过两步查询:

表----------->视图---------->用户

查询 查询

但是对一个很大的表来说,如果用户在查询之前都要从基本表是导入数据,将是很费时的事,如果我们把定义视图的数据直接存储在视图中这样当用户去查询时,就会直接从视图中搜索数据,这显然可提高效率.具备有这种特点的视图被称为索引视图.

视图(包括数据)-------->用户

查询

思考:怎么验证当创建索引视图之后,确实把数据存储到视图里面去了.[任务板]

说明:

1:在视图上创建的第一个索引必须是唯一聚集索引.

2:在创建唯一聚集索引后,可创建其它非聚集索引,也就是说在视图上不能创建单一的聚集索引.

方法:

A:通过企业管理器:

单右-->设计视图---->单右---->索引管理:创建索引

B:sql语句:

格式:create [unique][clustered] index 索引名 on 视图名 (列[asc|desc])

很显然:除去视图上的聚集索引将删除存储的结果集.[如何验证:删除索引视图的聚集索引]

索引优化向导

可以这样说,没有哪个人能够真正的设计一个非常优秀的索引,因为这需要深入了解SQL Server的数据库结构、工作负荷和内部原理.即使是微软高级工程师也不敢说自己创建的索引是最完美的,况且你只是一个一般的使用者呢？考虑到这些,微软为我们提供了一个能够让大多数用户操作,而又不需要去深入了解sql server内部结构、原理的操作平台,去做一个最好的索引,这就是“索引优化向导”.

说明:

要想让sql server建更好的索引,必须要指明:

A:指明一个服务器以及该服务器中一个数据库,以及该数据库中一个或多个表.[因为索引是对表而言的]

B:由于要创建索引,而我们知道创建什么样的索引是和所执行的sql语句有关的,所以必须要让sql server执行一个包括许多sql语句的一个sql脚本文件,我们把它叫工作负荷,为了让创建的索引能够真实的帮助你提高查询效率,要把与你工作相关的经常执行的sql语句放在一个脚本文件里面

C:假设你在使用“优化向导”之前已经创建一些索引,那么你可以选择让sql server是保留原有索引还是让sql server“优化向导”通过分析来决定是如何对现在索引进行更正,而增加新的索引.很显然如果你选择“要保留现有索引”,也就是说不管你以前的索引做得好还是坏,sql server都不会对你以前的索引提出真实的建议,这也许会使sql server“优化向导”没有办法提出新的建议,因为有可能某些确实可以提高查询效率的的建议是与你以前的索引情况相反的,而你要选择了“保留了现有索引”,所以不主张保留“现有索引”.

D:在sql server索引优化向导为你建议了新的索引之后会对以前的索引作一些操作比如:修改或删除,而创建新建议的索引

E:同时为了让用户能够对使用“优化向导”之前与使用“优化向导”之后的结果作一个对比会给出一个对比列表

方法:向导---------->优化向导

说明:

A:优化模式:优化模式决定了“优化向导”建议索引的准确性.

快速:建议的索引最优性不是最好的,但可以节约时间.

彻底:建议的索引比起“快速”与“适中”而言更准确.

建议:使用彻底

B:将要抽样的工作负荷查询的数目限制为指定要抽样的工作负荷查询的数目.默认值为200个查询.从指定的工作负荷文件中随机选取查询.如果设置值超过工作负荷文件中的查询数,则优化所有查询.

C:用于建议索引的最大空间(MB)

“优化向导”建议的所有索引的最大空间(以MB为单位),默认空间大小是当前数据大小的三倍.

D:每个索引的最大列数

指定索引优化向导建议的多列索引中的列的最大数.允许的最大值是16,这是默认值.

E:报表

A:索引使用报表:给出在使用“优化向导”之前与建议之后的每个表中每个索引的使用率和大小.

B:查询关系报表:给出在使用“优化向导”之前与建义之后每个查询使用的索引的情况.

C:优化摘要表:给出在使用“优化向导”之后,有多少个索引被优化,删除了多少个以前的索引,新建了多少建议的索引,优化了多少个查询,分析的时间.

试验:做一个work表:职工号,姓名,性别,年龄,学历,基本工资.有180万行.

在查询分析器中写上:

select * from work where 年龄 select * from work where 基本工资 保存作为工作负荷

分析:不用优化向导,要对提高这个查询效率而设置索引

讨论方法:并按讨论的方法在work表创建索引.

并运行:记下成本.

通过“优化索引向导”来创建索引

说明:在运行“优化索引向导”的时候,先后选择“保留索引”和“不保留索引”,看优化索引向导所提出的建议是否一样,执行完“优化索引向导”以后,再运行:记下成本.比较两次的成本. 2004-3-11 08:11

冒顿

（编辑：拼字网 - 核心网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!