搜索
数据库实验报告题目：查询优化姓名：李军毅日期：2016-5-14实验目的1.明确查询优化的重要性；2.理解代数优化与物理优化方法；3.学习在查询中使用较优的方法。实验平台1.OS：WindowsXP2.DBMS：SQLServer2008、VC6.0（或者visiostudio）3.IDE：Eclipse实验用时：两次上机实验内容一、数据库的恢复操作（导入数据）1.在【程序】中打开MicrosoftSQLServerManagementStudio。新建数据库“FoodmartII”2.在数据库FoodmartII上右键单击，选择【任务】【导入数据】。3.在“导入和导出向导”对话框中，数据源选择“MicrosoftAccess”，单击“文件名”后面的【浏览】按钮，按你的存储路径找到Foodmart.mdb文件。单击【下一步】。4.在“选择目标”部分，注意目标数据库的名称应为刚才建立的“FoodmartII”。5.选择复制一个或多个数据库表。6.在接下来的对话框中选择可能用到的数据表，根据需要勾选。单击【下一步】并“立即执行”，成功导入数据后可以看到如下对话框。单击【关闭】按钮。观察数据库引擎中的FoodmartII，看一看数据库中有哪些表，表中有哪些数据，是否包含索引，是否建立了视图？二、理解索引对查询的影响1.新建查询，在查询窗口中输入一个查询命令。2.在【查询】菜单中选择【显示估计的查询计划】，注意观察查询窗口下面的执行计划窗口。执行该查询（使用工具栏上的“执行”按钮或者【查询】菜单上的“执行”命令），观察右侧【属性】窗口中“返回的行数”“占用时间”等关键信息。3.为Customer表建立索引。建立Customer_id列的非聚集索引。执行查询，在【属性】窗口中观察查询时间。三、分析查询条件对查询执行的影响1.新建查询，输入查询命令，再按上面的步骤，观察“估计的查询计划”和“占用时间”时间等信息，比较查询条件对查询执行的影响。2.观察查询命令，在emplyee表建立salary列的非聚集索引。再次观察上面这个查询命令的查询计划和执行情况。四、分析连接条件对连接操作的影响1.对比下面查询的查询计划和查询执行情况2.在employee表上对employee_id列建立聚集索引.观察查询计划和执行情况的变化.五、视图的使用1.执行下面的查询命令，观察查询计划和执行情况。2.建立视图“cust_prod_sales”，由product，customer，sales_fact_1998三个表组成，其中包含查询常用的列（选取的列可以多于查询Q51），再执行下面的查询，比较两个查询的执行情况。六、查询优化测试1.数据准备，导入TPCH数据集。数据导入方法同前面Footmark的导入类似。2.对以下查询进行优化,写出你的优化方法.实际执行这个查询,记录你的执行时间(毫秒).实验中出现的问题实验内容一、数据库的恢复操作（导入数据）1.在【程序】中打开MicrosoftSQLServerManagementStudio。新建数据库“FoodmartII”打开MicrosoftSQLServerManagementStudio，如图：新建数据库“FoodmartII”，如图：2.在数据库FoodmartII上右键单击，选择【任务】【导入数据】。如图：3.在“导入和导出向导”对话框中，数据源选择“MicrosoftAccess”，单击“文件名”后面的【浏览】按钮，按你的存储路径找到Foodmart.mdb文件。单击【下一步】。如图，选择“MicrosoftAccess”，找到Foodmart.mdb文件：4.在“选择目标”部分，注意目标数据库的名称应为刚才建立的“FoodmartII”。如图，选择我刚刚建立的“FoodmartII”数据库：5.选择复制一个或多个数据库表。如图，勾选“复制一个或多个数据库表”：在接下来的对话框中选择可能用到的数据表，根据需要勾选。我选择了全部的数据表，并单击下一步，如图：单击【下一步】后，选择“立即执行”，如图：如下图，可看到导入成功，单击【关闭】按钮：观察数据库引擎中的FoodmartII，我们可以看到数据库中有哪些表，例如account表,category表，currency表等，如图：我们点击cureency表中的索引，可以看到初始时并没有任何索引，如图：右键cuurency表，选择“编辑前200行”，可以看到表中的数据，如图：二、理解索引对查询的影响1.新建查询，在查询窗口中输入一个查询命令。selectcustomer_idfromcustomerwherecustomer_id60002.在【查询】菜单中选择【显示估计的查询计划】，注意观察查询窗口下面的执行计划窗口。如图，表扫描占100%：执行该查询（使用工具栏上的“执行”按钮或者【查询】菜单上的“执行”命令），观察右侧【属性】窗口中“返回的行数”“占用时间”等关键信息。如图，我们可以看到返回的行数为4281行，占用的时间大约为2秒多：3.为Customer表建立索引。建立Customer_id列的非聚集索引，如下图所示。输入命令：createindexID_nonclusoncustomer(customer_id);建立非聚集索引：在customer表中查看索引，可以看到我们已经建立好的非聚集索引，如图：建立好索引后，仍使用如下查询命令：selectcustomer_idfromcustomerwherecustomer_id6000在菜单栏中的“查询”下点击“显示估计的执行计划”，观察新的查询计划，如图，新的执行计划索引查找占100%：执行该查询，在【属性】窗口中观察查询时间。如图，我们可以看到，建立好索引再进行查询，占用时间减少到不足1秒：三、分析查询条件对查询执行的影响1.新建查询，输入查询命令，再按上面的步骤，观察“估计的查询计划”和“占用时间”时间等信息，比较查询条件对查询执行的影响。Q1：selectcustomer_idfromcustomerwherecustomer_id=2621;初始情况下未建立索引，输入命令后，在菜单栏中的“查询”项下选择“显示估计的执行计划”，表扫描占100%：然后点击执行，在属性栏中可以看到，返回的行数为1，占用的时间为7秒多，如图：然后建立非聚集索引，在新建查询中输入上述命令，选择“显示估计的执行计划”，如图，索引查找占100%：点击“执行”，在属性栏中可以看到，返回的行数为1，占用的时间为2秒多，如图：再把where条件分别改写为：customer_id2621和customer_id2621，观察他们有什么异同。总结查询命令书写的经验。Q2：selectcustomer_idfromcustomerwherecustomer_id2621;显示估计的执行计划，表扫描占100%：点击“执行”，在属性栏中可以看到，返回的行数为7650行，占用的时间为3秒多，如图：建立非聚集索引后，显示估计的执行计划，可以看到，索引查找占100%：点击“执行”后，在属性栏中可以看到返回的行数为7650行，占用的时间为2秒多，如图：Q3:selectcustomer_idfromcustomerwherecustomer_id!=2621;这里我使用的是!=而不是，显示估计的执行计划，表扫描占100%，如图：点击“执行”，在属性栏中可以看到，返回的行数为10260行，占用时间为3秒多，如图：建立索引后，显示估计的执行计划，可以看到，索引扫描占100%：点击“执行”，属性栏中可以看到，返回的行数为10260行，占用的时间为2秒多，如图：可以知道，不等于操作符是永远用不到索引的，索引只能告诉什么存在于表中，而不能告诉什么不存在于表中，当数据库遇到“！=”，“”时，会转而用全表扫描，对a0的条件应写为a0ora0.2.观察下面的查询命令：selectfull_name,salaryfromemployeewheresalary30000;在未建立索引的情况显示估计的执行计划，表扫描占100%，如图：返回行数为8行，时间大约3秒多，如图：在emplyee表建立salary列的非聚集索引。再次观察上面这个查询命令的查询计划和执行情况。RID查找占87%，索引查找占13%，如图：执行后，返回行数为8，占用时间为2秒多，如图：（1）请写出你对以上内容的分析或得到的经验。尽量少用不等于查询条件当需要查找的数据特别多时，使用全表扫描或许比索引扫描还要好（2）试一试,你还能得到哪些查询命令书写的经验?(不同查询语句导致不同查询计划)当插入的数据为数据表的记录数量10%以上时，首先需要删除该表的索引来提高数据的插入效率，当数据全部插入后再建立索引。避免在索引列上使用函数或计算，在where子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描，举例：低效：select*fromtablewheresalary*1225000高效：select*fromtablewheresalary25000/12索引列上用=替代，举例：高效：select*fromtablewhereDeptno=4低效：select*fromtablewhereDeptno3四、分析连接条件对连接操作的影响1.对比下面查询的查询计划和查询执行情况Q41:Selectemployee.employee_id,full_name,employee.salary,pay_date,salary_paidfromemployee,salary显示估计的执行计划，如图，嵌套循环96%,表假脱机4%：Q42:selectemployee.employee_id,full_name,employee.salary,pay_date,salary_paidfromemployee,salarywhereemployee.employee_id=salary.employee_id显示估计的执行计划，哈希匹配50%，表扫描各占41%和9%：点击“执行”，返回行数为21252行，占用时间3秒多：Q43：Selectemployee.employee_id,full_name,employee.salary,pay_date,salary_paidfromemployee,salarywhereemployee.employee_idsalary.employee_id显示估计的执行计划，嵌套循环占73%，索引假脱机27%：但是，点击“执行”，因为数据溢出，无法完成。2.在employee表上对employee_id列建立聚集索引.观察查询计划和执行情况的变化.createCLUSTEREDindexID_clusonemployee(employee_id);如图：Q41:selectemployee.employee_id,full_name,employee.salary,pay_date,salary_paidfromemployee,salary显示估计的执行计划，嵌套循环占96%，表假脱机4%：Q42:selectemployee.employee_id,full_name,employee.salary,pay_date,salary_paidfromemployee,salarywhereemployee.employee_id=salary.employee_id显示估计的执行计划，哈希匹配50%，聚集索引扫描9%，表扫描41%：点击“执行”，返回行数为21252行，占用时间为0.320秒：Q43:selectemployee.employee_id,full_name,employee.salary,pay_date,salary_paidfromemployee,salarywhereemployee.employee_idsalary.employee_id显示估计的执行计划，嵌套循环73%，索引