SQL SERVER数据库索引查询.docx

SQL SERVER数据库索引查询.docx

《SQL SERVER数据库索引查询.docx》由会员分享，可在线阅读，更多相关《SQL SERVER数据库索引查询.docx（12页珍藏版）》请在冰豆网上搜索。

SQLSERVER数据库索引查询

实验二SQLSERVER数据库索引和查询

一、实验目的

1．掌握数据库中建立索引。

2．掌握在数据库中根据不同的要求进行不同查询的操作。

二、实验平台

1．操作系统：

Windows2000或者WindowsXP。

2．数据库管理系统：

SQLserver2000个人版。

三、预备知识

SQLServer的性能受许多因素的影响。

有效地设计索引可以提高性能。

索引和书的目录类似。

如果把表的数据看作书的内容，则索引就是书的目录。

书的目录指向了书的内容（通过页码），同样，索引是表的关键值，它提供了指向表中行（记录）的指针。

目录中的页码是到达书内容的直接路径，而索引也是到达表数据的直接路径，从而可更高效地访问数据。

可以利用索引快速访问数据库表中的特定信息。

索引是对数据库表中一个或多个列的值进行排序的结构。

索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。

数据库使用索引的方式与使用书的目录很相似：

通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。

提示：

通常情况下，只有当经常查询索引列中的数据时，才需要在表上创建索引。

索引将占用磁盘空间，并且降低添加、删除和更新行的速度。

不过在多数情况下，索引所带来的数据检索速度的优势大大超过它的不足之处．然而，如果应用程序非常频繁地更新数据，或磁盘空间有限，那么最好限制索引的数量。

创建索引应考虑的主要因素有：

∙一个表如果建有大量索引会影响INSERT、UPDATE和DELETE语句的性能，因为在表中的数据更改时，所有索引都须进行适当的调整。

另一方面，对于不需要修改数据的查询（SELECT语句），大量索引有助于提高性能，因为SQLServer有更多的索引可供选择，以便确定以最快速度访问数据的最佳方法。

∙覆盖的查询可以提高性能。

覆盖的查询是指查询中所有指定的列都包含在同一个索引中。

例如，如果在一个表的a、b和c列上创建了组合索引，则从该表中检索a和b列的查询被视为覆盖的查询。

创建一个覆盖查询的索引可以提高性能，因为该查询的所有数据都包含在索引自身当中；检索数据时只需引用表的索引页，不必引用数据页，因而减少了I／O总量。

尽管给索引添加列以覆盖查询可以提高性能，但在索引中额外维护更多的列会产生更新和存储成本。

∙对小型表进行索引可能不会产生优化效果，因为SQLServer在遍历索引以搜索数据时，花费的时间可能会比简单的表扫描还长。

（我们在实验过程中用的表很小，但是为了熟悉索引概念本身，就在小表上建立索引）

∙应使用SQL事件探查器和索引优化向导帮助分析查询，确定要创建的索引。

为数据库及其工作负荷选择正确的索引是非常复杂的，需要在查询速度和更新成本之间取得平衡。

窄索引（搜索关键字中只有很少的列的索引）需要的磁盘空间和维护开销都更少。

而另一方面，宽索引可以覆盖更多的查询。

确定正确的索引集没有简便的规则。

经验丰富的数据库管理员常常能够设计出很好的索引集，但是，即使对于不特别复杂的数据库和工作负荷来说，这项任务也十分复杂，费时和易出错。

可以使用索引优化向导使这项任务自动化。

∙一般来说，对表的查询都是通过主键来进行的，因此，首先应该考虑在主键上建立索引。

另外，对于连接中频繁使用的列（包括外键）也应作为建立索引的考虑选项。

由于建立索引需要一定的开销，而且，当使用INSERT或者UPDATE对数据进行插入和更新操作时，维护索引也是需要花费时间和空间的。

因此，没有必要对表中所有的列建立索引。

下面的情况则不考虑建立索引：

∙从来不或者很少在查询中引用的列。

∙只有两个或若干个值的列，例如性别（男或女），也得不到建立索引的好处。

∙记录数目很少的表。

SQLServer提供了两种方法来创建索引：

∙直接创建索引使用CREATEINDEX语句或者企业管理器来直接创建索引。

∙间接创建索引使用CREATETABLE语句创建表时，或者使用ALTERTABLE语句修改表时，如果指定PRIMARYKEY约束或者UNIQUE约束，则SQLServer自动为这些约束创建索引。

我们主要介绍直接创建索引的方法，包括使用SQL语言和使用企业管理器来创建索引。

（同学们在学习以下几种索引创建方法时，暂时不进行实际的创建动作，仅仅是学习方法。

）

1、通过企业管理器创建索引：

（1）打开企业管理器，并展开相应的服务器组和数据库，在要创建索引的表上（这里选择Student表作为要创建索引的表。

）右击鼠标，将鼠标指向“所有任务”，然后选择“管理索引”命令，如图3－1所示。

（2）此时，打开“管理索引”对话框，如图3－2所示。

该对话框用来对索引进行管理。

顶部的“数据库”和“表／视图”文本框用来选择数据库和要创建索引的表，下面的列表框显示了选中表的索引，下部的按钮用来对索引进行编辑、删除等操作。

在图3－2中，显示PK_Student索引，这是在创建Student表时设置的索引。

（3）单击“新建”按钮，打开“新建索引”对话框，如图3－3所示。

这里在“索引名称”文本框中输入索引名称，并选择用于创建索引的列，即可创建索引。

“排序次序”选项如果处于选中状态，表示索引以降序排列。

图3－1选择“管理索引”命令

图3－2“管理索引”对话框

图3－3“新建索引”对话框

在“索引选项”选项组中显示了索引的各个特征选项，其含义如下：

∙聚集索引选中表示创建聚集索引；未选中表示创建非聚集索引。

∙惟一值选中表示创建惟一索引。

∙忽略重复的值当“唯一值”选中时，该选项才可用。

若该选项处于选中状态，表示在表中加入一笔重复此索引字段的值时，则INSERT语句会被执行，但是会自动取消这笔新加入的记录；如果不选择该选项，加入一笔重复此索引字段的INSERT语句将会出错。

∙不重新计算统计当建立索引时，SQLServer会默认建立该索引字段的统计数据，以提高检索的效率。

当记录改变时，原来该字段的统计数据就不是最新的，则SQLServer会自动重新统计。

如果选择此复选框，表示不让SQLServer自动重新计算该索引字段的统计数据，则在检索数据时，可能导致效率降低。

因此推荐不选择此复选框。

∙文件组用来存放数据库的文件组，在建立数据库时，每个数据库都有一个默认的PRIMARY文件组，用户也可以自定义文件组。

∙填充索引指定填充索引。

填充索引在索引的每个内部节点上留出空格。

∙填充因子指定SQLServer在创建索引过程中，对各索引页的叶级所进行填充的程度。

∙除去指定在创建新索引之前删除任何现有的同名索引。

（4）设置完成后，单击“确定”按钮（注：

只有填写好“索引名称”后，“确定”按钮才会被激活）即可创建一个新的索引。

也可以单击“编辑SQL”按钮，出现如图3－4所示的“编辑Transact-SQL脚本”对话框，单击其中的“执行”按钮则创建一个新的索引。

（这里没有填写好索引名称，在分析该代码时会出错）

图3－4“编辑Transact-SQL脚本”对话框

2、通过向导来创建索引

（1）在企业管理器中，展开“数据库”文件夹，执行“工具”菜单中的“向导”命令，打开“选择向导”对话框。

然后选择“数据库”→“创建索引向导”，如图3－5所示。

图3－5“选择向导”对话框

（2）单击“确定”按钮，出现创建索引的欢迎使用向导界面，如图3－6所示。

（3）单击“下一步”按钮，弹出对话框如图3－7所示。

在“数据库名称”文本框中，可选择数据库。

在“对象名”文本框中，可选择要创建索引的表或者视图。

图3－6欢迎使用向导界面

图3－7选择数据库和表/视图

（4）选择完成后，单击“下一步”按钮，打开所选择表的现有索引信息对话框，如图3－8所示。

其中列出已有索引的信息，其中PK_Student是在建立表时创建的。

（以“_WA”开头的索引是系统建立的。

）

图3－8查看当前的索引信息

（5）单击“下一步”按钮，出现选择列的对话框，如图3－9所示。

（6）选择要创建索引的列后，单击“下一步”按钮，打开“指定索引选项”页，如图3－10所示。

在此对话框中，可以设置索引的各个选项，包括聚集索引、惟一性和填充因子等。

其含义和上面介绍的相同。

图3－9选择要创建索引的列

图3－10指定索引选项

（7）设置索引属性选项后，单击“下一步”按钮，即可进入下一步。

对话框中显示了该索引的名称以及创建索引的列，如图3－11所示。

用户可以通过“上移”和“下移”按钮来设置列的优先级。

图3－11显示索引信息

（8）设置完成后，单击“完成”按钮，系统弹出开始创建索引，完成后弹出一个提示对话框，提示完成新索引的创建。

3、使用SQL语言创建索引

（1）在“开始”→“程序”中选择MicrosoftSQLServer，打开“查询分析器”，如图3－12所示。

图3－12打开“查询分析器”

（2）如图3－13，在“连接到SQLServer”对话框中点击

按钮，出现选择服务器窗口，选择本地服务（local），点击确定按钮。

选择身份验证方式（安装SQLServer时的设置）进入SQL查询分析器主界面，如图3－14。

图3－13连接到SQLServer

图3－14SQL查询分析器

（3）选择“查询”菜单，点击“更改数据库…”，如图3－15所示。

图3－15更改数据库

（4）选择想要操作的数据库，点击“确定”，如图3－16。

图3－16选择数据库

（5）输入相应的SQL代码，点击

按钮执行该SQL语句，即可进行针对该数据库的操作。

四、实验内容

1．利用上次实验建立的表，分别在Student表的Sdept列、Course表的Cno列、SC表的Grade列上建立索引。

2．建立如下的查询：

1）年龄大于18的所有学生的姓名学号；

2）年龄大于18的CS系的学生的姓名学号；

3）选择课程号为3的学生姓名和学号；

4）选择课程号为3的学生姓名、学号和该课程的课程名与先修课程。

五、实验过程

1．索引的建立：

Createuniqueindexstusnoonstudent（Sdept）

Createuniqueindexctucnooncourse（cno）

Createuniqueindexscgaonsc（grade）

2．查询的建立：

1）selectSname,Sno

fromStudent

whereSage>18

2）selectSname,Sno

fromStudent

whereSage>18andSdept='CS'

3）selectSname,Student.Sno

fromStudent,SC

whereStudent.Sno=SC.SnoandSC.Cno='3'

4）selectSname,Student.Sno,Course.Cname,Cpno

fromStudent,SC,Course

whereStudent.Sno=SC.SnoandSC.Cno=Course.CnoandSC.Cno='3'

还可以进行其他查询操作。