查询优化实验

实验六查询优化

一、实验目的：

加深对代数优化和物理优化的当方法的当然理解，明确查询优化的重要性，在学习的过程中使用较优的方法。

二、实验内容：

观察和分析系统查询优化方法，总结查询书写经验。

三、实验步骤：

（一）数据库的恢复操作-数据的导入

1.在【程序】中打开Microsoft SQL Server Management Studio。新建数据库“FoodmartII”。

2.在数据库FoodmartII上右键单击，选择【任务】【导入数据】。

3.在“导入和导出向导”对话框中，数据源选择“Microsoft Access”，单击“文件名”后面的【浏览】按钮，存储路径找到Foodmart.mdb 文件。单击【下一步】。

4.在“选择目标”部分，注意目标数据库的名称应为刚才建立的“FoodmartII”。

5.选择复制一个或多个数据库表。

在接下来的对话框中选择可能用到的数据表，包括：account,

category, currency, days, department, employee, inventory_fact, position, product, promotion, region, salary, sales_fact_1998, store, warehouse 等。

单击【下一步】并“立即执行”，成功导入数据后可以看到如下对话框。

单击【关闭】按钮。观察数据库引擎中的FoodmartII，看一看数据库中有哪些表，表中有哪些数据，哪些索引，哪些视图？

数据库中的当然表见下图：

查看表中的数据、索引、视图（以account表为例）

经查看发现所有表中都没有索引和视图。

（二）理解索引对查询的影响

注意：初始状态下，当前数据库中没有任何索引、视图等。

1.新建查询，在查询窗口中输入一个查询命令：

select customer_id

from customer

where customer_id>6000

2.在【查询】菜单中选择【显示估计的查询计划】，注意观察查询窗口下面的执行计划窗口。

执行该查询（使用工具栏上的“执行”按钮或者【查询】菜单上的“执行”命令），观察右侧【属性】窗口中“返回的行数”“占用时间”等关键信息。

3.为Customer表建立索引。建立Customer_id列的非聚集索引，如下图所示。

仍使用如下查询命令：

select customer_id

from customer

where customer_id>6000 观察新的查询计划：

执行该查询，在【属性】窗口中观察查询时间。

注意，可以多次执行查询命令，将有利于查询时间减少。（原因：数据被存储于内存中，加快了访问速度。）

建立索引后第一次执行查询，可见查询执行时间：

第二次执行：

第三次执行：

请分析索引的使用原则和索引对查询的影响。

索引的使用原则：

第一，索引并非越多越好，一个表中如果有大量的索引，不仅占用大量的磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能。因为当表中数据更改的同时，索引也会进行调整和更新。

第二，避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。

第三，数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

第四，在条件表达式中经常用到的、不同值较多的列上建立索引，在不同值少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低更新速度。

索引对查询的影响：

索引可以快速的定位表中的某条记录。使用索引可以提高数据库查询的速度，从而提高数据库的性能。如果查询时不使用索引，查询语句将查询表中的所有字段。这样查询的速度会很慢。如果使用索引进行查询，查询语句只查询索引字段。这样可以减少查询的记录数，达到提高查询速度的目的。

（三）分析查询条件对查询执行的影响

1.新建查询，入查询命令，再按上面的步骤，观察“估计的查询计划”

和“占用时间”时间等信息，比较查询条件对查询执行的影响。Q1：

select customer_id

from customer

where customer_id=2621

估计的查询计划：

执行结果：

再把where 条件分别改写为：customer_id<2621 和

customer_id<>2621，观察他们有什么异同。总结查询命令书写的经验。【例：查询命令中尽量不要使用”<>”的条件，这会导致查询不能使用索引扫描。】

customer_id<2621估计的查询计划：

customer_id<2621执行结果：

customer_id<>2621估计的查询计划：

customer_id<>2621执行结果：

2.观察下面的查询命令：select full_name,salary from employee

where salary>30000

查询计划：

执行结果：

在emplyee表建立salary列的非聚集索引。再次观察上面这个查询命令的查询计划和执行情况。

查询计划：

执行结果：

（1）请写出你对以上内容的分析和得到的经验。

答：建立了索引以后，执行时间缩短，说明建立合适的索引可以加快查询速度，提高效率。

（2）试一试, 你还能得到哪些查询命令书写的经验?

答：不同的查询语句会导致不同的查询计划，进而影响查询时间。（四）分析连接条件对连接操作的影响

1. 对比下面两个查询的查询计划和查询执行情况

Q41 select

employee.employee_id,full_name,employee.salary,pay_date,salary_paid from employee,salary

查询计划：