1、1 实验二 SQL SERVER 数据库索引和查询一、实验目的1掌握数据库中建立索引。2掌握在数据库中根据不同的要求进行不同查询的操作。二、实验平台1操作系统： Windows 2000 或者 Windows XP。2数据库管理系统： SQL server 2000 个人版。三、预备知识SQL Server 的性能受许多因素的影响。有效地设计索引可以提高性能。索引和书的目录类似。如果把表的数据看作书的内容，则索引就是书的目录。书的目录指向了书的内容(通过页码)，同样，索引是表的关键值，它提供了指向表中行( 记录)的指针。目录中的页码是到达书内容的直接路径，而索引也是到达表数据的直接路径，从而可
2、更高效地访问数据。可以利用索引快速访问数据库表中的特定信息。索引是对数据库表中一个或多个列的值进行排序的结构。索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。数据库使用索引的方式与使用书的目录很相似：通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。提示：通常情况下，只有当经常查询索引列中的数据时，才需要在表上创建索引。索引将占用磁盘空间，并且降低添加、 删除和更新行的速度。不过在多数情况下，索引所带来的数据检索速度的优势大大超过它的不足之处然而，如果应用程序非常频繁地更新数据，或磁盘空间有限，那么最好限制索引的数量。2 创建索引应考虑的主要因素有： 一个
3、表如果建有大量索引会影响 INSERT、UPDATE 和 DELETE 语句的性能，因为在表中的数据更改时，所有索引都须进行适当的调整。另一方面，对于不需要修改数据的查询(SELECT 语句)，大量索引有助于提高性能，因为 SQL Server 有更多的索引可供选择，以便确定以最快速度访问数据的最佳方法。 覆盖的查询可以提高性能。覆盖的查询是指查询中所有指定的列都包含在同一个索引中。例如，如果在一个表的 a、b 和 c 列上创建了组合索引，则从该表中检索 a 和 b 列的查询被视为覆盖的查询。创建一个覆盖查询的索引可以提高性能，因为该查询的所有数据都包含在索引自身当中；检索数据时只需引用表的索
4、引页，不必引用数据页，因而减少了IO 总量。尽管给索引添加列以覆盖查询可以提高性能，但在索引中额外维护更多的列会产生更新和存储成本。 对小型表进行索引可能不会产生优化效果，因为 SQL Server 在遍历索引以搜索数据时，花费的时间可能会比简单的表扫描还长。（我们在实验过程中用的表很小，但是为了熟悉索引概念本身，就在小表上建立索引） 应使用 SQL 事件探查器和索引优化向导帮助分析查询，确定要创建的索引。为数据库及其工作负荷选择正确的索引是非常复杂的，需要在查询速度和更新成本之间取得平衡。窄索引(搜索关键字中只有很少的列的索引)需要的磁盘空间和维护开销都更少。而另一方面，宽索引可以覆盖更多的
5、查询。确定正确的索引集没有简便的规则。经验丰富的数据库管理员常常能够设计出很好的索引集，但是，即使对于不特别复杂的数据库和工作负荷来说，这项任务也十分复杂，费时和易出错。可以使用索引优化向导使这项任务自动化。 一般来说，对表的查询都是通过主键来进行的，因此，首先应该考虑在主键上建立索引。另外，对于连接中频繁使用的列(包括外键)也应作为建立索引的考虑选项。3 由于建立索引需要一定的开销，而且，当使用 INSERT 或者 UPDATE 对数据进行插入和更新操作时，维护索引也是需要花费时间和空间的。因此，没有必要对表中所有的列建立索引。下面的情况则不考虑建立索引： 从来不或者很少在查询中引用的列。
6、只有两个或若干个值的列，例如性别(男或女)，也得不到建立索引的好处。 记录数目很少的表。SQL Server 提供了两种方法来创建索引： 直接创建索引 使用 CREATE INDEX 语句或者企业管理器来直接创建索引。 间接创建索引 使用 CREATE TABLE 语句创建表时，或者使用ALTER TABLE 语句修改表时，如果指定 PRIMARY KEY 约束或者UNIQUE 约束，则 SQL Server 自动为这些约束创建索引。我们主要介绍直接创建索引的方法，包括使用 SQL 语言和使用企业管理器来创建索引。 （同学们在学习以下几种索引创建方法时，暂时不进行实际的创建动作，仅仅是学习方法
7、。 ）1、通过企业管理器创建索引：(1) 打开企业管理器，并展开相应的服务器组和数据库，在要创建索引的表上(这里选择 Student 表作为要创建索引的表。)右击鼠标，将鼠标指向“所有任务” ，然后选择“管理索引”命令，如图 31 所示。(2) 此时，打开“管理索引”对话框，如图 32 所示。该对话框用来对索引进行管理。顶部的“数据库”和“表视图”文本框用来选择数据库和要创建索引的表，下面的列表框显示了选中表的索引，下部的按钮用来对索引进行编辑、删除等操作。在图 32 中，显示 PK_Student 索引，这是在创建 Student表时设置的索引。(3) 单击“新建”按钮，打开“新建索引”对话
8、框，如图 33 所示。这里在“索引名称”文本框中输入索引名称，并选择用于创建索引的列，即可创建4 索引。 “排序次序”选项如果处于选中状态，表示索引以降序排列。图 31 选择“管理索引”命令图 32“管理索引”对话框5 图 33“新建索引”对话框在“索引选项”选项组中显示了索引的各个特征选项，其含义如下： 聚集索引 选中表示创建聚集索引；未选中表示创建非聚集索引。 惟一值 选中表示创建惟一索引。 忽略重复的值 当“唯一值”选中时，该选项才可用。若该选项处于选中状态，表示在表中加入一笔重复此索引字段的值时，则 INSERT 语句会被执行，但是会自动取消这笔新加入的记录；如果不选择该选项，加入一笔
9、重复此索引字段的 INSERT 语句将会出错。 不重新计算统计 当建立索引时，SQL Server 会默认建立该索引字段的统计数据，以提高检索的效率。当记录改变时，原来该字段的统计数据就不是最新的，则 SQL Server 会自动重新统计。如果选择此复选框，表示不让 SQL Server 自动重新计算该索引字段的统计数据，则在检索数据时，可能导致效率降低。因此推荐不选择此复选框。 文件组 用来存放数据库的文件组，在建立数据库时，每个数据库都有一个默认的 PRIMARY 文件组，用户也可以自定义文件组。 填充索引 指定填充索引。填充索引在索引的每个内部节点上留出空格。 填充因子 指定 SQL S
10、erver 在创建索引过程中，对各索引页的叶级所进行填充的程度。 除去 指定在创建新索引之前删除任何现有的同名索引。(4) 设置完成后，单击“确定”按钮（注：只有填写好“索引名称”后，“确定”按钮才会被激活）即可创建一个新的索引。也可以单击“编辑 SQL”按钮，出现如图 34 所示的“编辑 Transact-SQL 脚本”对话框，单击其中的“执行”按钮则创建一个新的索引。 （这里没有填写好索引名称，在分析该代码时会出错）6 图 34 “编辑 Transact-SQL 脚本”对话框2、通过向导来创建索引(1) 在企业管理器中，展开“数据库”文件夹，执行“工具”菜单中的“向导”命令，打开“选择向导
11、”对话框。然后选择“数据库”“创建索引向导” ，如图 35 所示。图 35“选择向导”对话框(2) 单击“确定”按钮，出现创建索引的欢迎使用向导界面，如图 36 所示。(3) 单击“下一步”按钮，弹出对话框如图 37 所示。在“数据库名称”文本框中，可选择数据库。在“对象名”文本框中，可选择要创建索引的表或者视图。7 图 36 欢迎使用向导界面图 37 选择数据库和表 /视图(4) 选择完成后，单击 “下一步”按钮，打开所选择表的现有索引信息对话框，如图 38 所示。其中列出已有索引的信息，其中 PK_Student 是在建立表时创建的。 （以“_WA ”开头的索引是系统建立的。 ）图 38
12、查看当前的索引信息(5) 单击“下一步 ”按钮，出现选择列的对话框，如图 39 所示。(6) 选择要创建索引的列后，单击“下一步”按钮，打开“指定索引选项”页，如图 310 所示。8 在此对话框中，可以设置索引的各个选项，包括聚集索引、惟一性和填充因子等。其含义和上面介绍的相同。图 39 选择要创建索引的列图 310 指定索引选项(7) 设置索引属性选项后，单击“下一步”按钮，即可进入下一步。对话框中显示了该索引的名称以及创建索引的列，如图 311 所示。用户可以通过“上移”和“下移”按钮来设置列的优先级。图 311 显示索引信息9 (8) 设置完成后，单击“完成”按钮，系统弹出开始创建索引，
13、完成后弹出一个提示对话框，提示完成新索引的创建。3、使用 SQL 语言创建索引（1）在“开始”“程序”中选择 Microsoft SQL Server，打开“查询分析器” ，如图 312 所示。图 312 打开“查询分析器”（2）如图 313，在“连接到 SQL Server”对话框中点击 按钮，出现选择服务器窗口，选择本地服务(local)，点击确定按钮。选择身份验证方式（安装SQL Server 时的设置）进入 SQL 查询分析器主界面 ，如图 314。图 313 连接到 SQL Server10 图 314 SQL 查询分析器（3）选择“查询”菜单，点击“更改数据库”，如图 315 所示
14、。图 315 更改数据库（4）选择想要操作的数据库，点击“确定” ，如图 316。图 316 选择数据库11 （5）输入相应的 SQL 代码，点击 按钮执行该 SQL 语句，即可进行针对该数据库的操作。四、实验内容1利用上次实验建立的表，分别在 Student 表的 Sdept 列、Course 表的Cno 列、 SC 表的 Grade 列上建立索引。2建立如下的查询：1)年龄大于 18 的所有学生的姓名学号；2)年龄大于 18 的 CS 系的学生的姓名学号；3)选择课程号为 3 的学生姓名和学号；4)选择课程号为 3 的学生姓名、学号和该课程的课程名与先修课程。五、实验过程 索引的建立：Cr
15、eate unique index stusno on student(Sdept)Create unique index ctucno on course(cno)Create unique index scga on sc(grade) 查询的建立：1)select Sname,Snofrom Studentwhere Sage182)select Sname,Snofrom Student where Sage18 and Sdept=CS3)select Sname,Student.Snofrom Student,SC12 where Student.Sno=SC.Sno and SC.Cno=34)select Sname,Student.Sno,Course.Cname,Cpnofrom Student,SC,Coursewhere Student.Sno=SC.Sno and SC.Cno= Course.Cno and SC.Cno=3还可以进行其他查询操作。
