查询优化实验

1、实验六 查询优化一、实验目的： 加深对代数优化和物理优化的当方法的当然理解，明确查询优化的重要性，在学习的过程中使用较优的方法。二、实验内容： 观察和分析系统查询优化方法，总结查询书写经验。 三、实验步骤： （一）数据库的恢复操作-数据的导入1.在【程序】中打开 Microsoft SQL Server Management Studio。新建数据库“FoodmartII”。 2.在数据库 FoodmartII上右键单击，选择【任务】【导入数据】。 3.在“导入和导出向导”对话框中，数据源选择“Microsoft Access”，单击“文件名”后面的【浏览】按钮，存储路径找到 Foodmart

2、.mdb文件。单击【下一步】。 4.在“选择目标”部分，注意目标数据库的名称应为刚才建立的“FoodmartII”。 5.选择复制一个或多个数据库表。 在接下来的对话框中选择可能用到的数据表，包括：account, category, currency, days, department, employee, inventory_fact, position, product, promotion, region, salary, sales_fact_1998, store, warehouse 等。 单击【下一步】并“立即执行”，成功导入数据后可以看到如下对话框。 单击【关闭】按钮。观察数

3、据库引擎中的 FoodmartII，看一看数据库中有哪些表，表中有哪些数据，哪些索引，哪些视图？ 数据库中的当然表见下图：查看表中的数据、索引、视图（以account表为例） 经查看发现所有表中都没有索引和视图。（二）理解索引对查询的影响 注意：初始状态下，当前数据库中没有任何索引、视图等。 1.新建查询，在查询窗口中输入一个查询命令： select customer_id from customer where customer_id6000 2.在【查询】菜单中选择【显示估计的查询计划】，注意观察查询窗口下面的执行计划窗口。 执行该查询（使用工具栏上的“执行”按钮或者【查询】菜单上的“执行

4、”命令），观察右侧【属性】窗口中“返回的行数”“占用时间”等关键信息。 3.为Customer表建立索引。建立 Customer_id列的非聚集索引，如下图所示。 仍使用如下查询命令： select customer_id from customer where customer_id6000 观察新的查询计划： 执行该查询，在【属性】窗口中观察查询时间。 注意，可以多次执行查询命令，将有利于查询时间减少。（原因：数据被存储于内存中，加快了访问速度。） 建立索引后第一次执行查询，可见查询执行时间：第二次执行：第三次执行：请分析索引的使用原则和索引对查询的影响。 索引的使用原则：第一，索引并非越

5、多越好，一个表中如果有大量的索引，不仅占用大量的磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能。因为当表中数据更改的同时，索引也会进行调整和更新。 第二，避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。第三，数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。第四，在条件表达式中经常用到的、不同值较多的列上建立索引，在不同值少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引。如果建立索引，不

6、但不会提高查询效率，反而会严重降低更新速度。索引对查询的影响：索引可以快速的定位表中的某条记录。使用索引可以提高数据库查询的速度，从而提高数据库的性能。如果查询时不使用索引，查询语句将查询表中的所有字段。这样查询的速度会很慢。如果使用索引进行查询，查询语句只查询索引字段。这样可以减少查询的记录数，达到提高查询速度的目的。（三）分析查询条件对查询执行的影响 1.新建查询，入查询命令，再按上面的步骤，观察“估计的查询计划”和“占用时间”时间等信息，比较查询条件对查询执行的影响。 Q1： select customer_id from customer where customer_id=2621

7、估计的查询计划：执行结果：再把 where 条件分别改写为：customer_id2621 和 customer_id2621，观察他们有什么异同。总结查询命令书写的经验。 【例：查询命令中尽量不要使用”的条件，这会导致查询不能使用索引扫描。】 customer_id2621估计的查询计划：customer_id2621执行结果：customer_id2621估计的查询计划：customer_id2621执行结果：2.观察下面的查询命令： select full_name,salary from employee where salary30000 查询计划：执行结果：在emplyee表建立s

8、alary列的非聚集索引。再次观察上面这个查询命令的查询计划和执行情况。 查询计划：执行结果：（1）请写出你对以上内容的分析和得到的经验。 答：建立了索引以后，执行时间缩短，说明建立合适的索引可以加快查询速度，提高效率。（2）试一试, 你还能得到哪些查询命令书写的经验? 答：不同的查询语句会导致不同的查询计划，进而影响查询时间。（四）分析连接条件对连接操作的影响 1. 对比下面两个查询的查询计划和查询执行情况 Q41 select employee.employee_id,full_name,employee.salary,pay_date,salary_paid from employee,

9、salary 查询计划：执行结果：Q42 select employee.employee_id,full_name,employee.salary,pay_date,salary_paid from employee,salary where employee.employee_id=salary.employee_id 查询计划：执行结果：2.在 employee 表上对employee_id 列建立聚集索引.观察查询计划和执行情况的变化. 建立索引：Q1查询计划：Q1执行结果：Q2查询计划：Q2执行结果：请写出你对以上内容的分析和得到的经验。对于Q1，建立索引后查询计划由哈希匹配变成了嵌

10、套查询，非常耗费系统资源，执行时间也增长到7分23秒。说明此索引不适合Q1。对于Q2，查询计划变动不大，但执行时间缩短，说明此索引适合Q2的查询。由此可以得出，索引不一定会缩短查询时间，不合适的索引反而会增加系统负担。（五）视图的使用 1. 执行下面的查询命令，观察查询计划和执行情况。 Q51 select lname,fname,brand_name,product_name from sales_fact_1998,product,customer where customer.customer_id=sales_fact_1998.customer_id and d

11、uct_id=sales_fact_1998.product_id and sales_fact_1998.customer_id=9143 查询计划：执行情况：2. 建立视图“cust_prod_sales”，由 product，customer ， sales_fact_1998 三个表组成，其中包含查询常用的列（选取的列可以多于查询Q51），再执行下面的查询 Q52 select lname,fname,brand_name,product_name from cust_prod_sales where customer_id=9143 建立视图：执行结果：比较两个查询的执行情况。 请写

12、出你对以上内容的分析和得到的经验。 答：分析可知，嵌套查询非常占用系统内存，导致Q2因内存溢出无法完成查询。而之前的步骤四也反映出，嵌套查询会显著增加查询时间。因此在查询中应尽量避免使用嵌套查询。 参照你学过的查询优化知识，还有哪些其他方面？请对比不同的查询方法和查询结果，写出你的经验。答：第一询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。第二避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确

13、保表中num列没有null值，然后这样查询：select id from t where num=0第三量避免在 where 子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描。第四避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10union allselect id from t where num=20第五.in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t wher