数据库系统概论查询优化实验报告.docx

1、数据库系统概论查询优化实验报告数据库实验报告 题目：查询优化 姓名： 李军毅 日期：2016-5-14实验目的1.明确查询优化的重要性；2.理解代数优化与物理优化方法；3.学习在查询中使用较优的方法。实验平台1.OS：Windows XP2.DBMS：SQLServer2008、VC6.0（或者visio studio）3.IDE：Eclipse实验用时：两次上机实验内容一、数据库的恢复操作（导入数据）1.在【程序】中打开Microsoft SQL Server Management Studio。新建数据库“FoodmartII”2.在数据库FoodmartII 上右键单击，选择【任务】【导

2、入数据】。3.在“导入和导出向导”对话框中，数据源选择“Microsoft Access”，单击“文件名”后面的【浏览】按钮，按你的存储路径找到Foodmart.mdb 文件。单击【下一步】。4.在“选择目标”部分，注意目标数据库的名称应为刚才建立的“FoodmartII”。5.选择复制一个或多个数据库表。6.在接下来的对话框中选择可能用到的数据表，根据需要勾选。单击【下一步】并“立即执行”，成功导入数据后可以看到如下对话框。单击【关闭】按钮。观察数据库引擎中的FoodmartII，看一看数据库中有哪些表，表中有哪些数据，是否包含索引，是否建立了视图？二、理解索引对查询的影响1.新建查询，在查

3、询窗口中输入一个查询命令。2.在【查询】菜单中选择【显示估计的查询计划】，注意观察查询窗口下面的执行计划窗口。执行该查询（使用工具栏上的“执行”按钮或者【查询】菜单上的“执行”命令），观察右侧【属性】窗口中“返回的行数”“占用时间”等关键信息。3.为Customer 表建立索引。建立Customer_id 列的非聚集索引。执行查询，在【属性】窗口中观察查询时间。三、分析查询条件对查询执行的影响1.新建查询，输入查询命令，再按上面的步骤，观察“估计的查询计划”和“占用时间”时间等信息，比较查询条件对查询执行的影响。2.观察查询命令，在emplyee 表建立salary 列的非聚集索引。再次观察上

4、面这个查询命令的查询计划和执行情况。四、分析连接条件对连接操作的影响1.对比下面查询的查询计划和查询执行情况2.在employee 表上对employee_id 列建立聚集索引.观察查询计划和执行情况的变化.五、视图的使用1.执行下面的查询命令，观察查询计划和执行情况。2.建立视图“cust_prod_sales”，由product，customer ， sales_fact_1998 三个表组成，其中包含查询常用的列（选取的列可以多于查询Q51），再执行下面的查询，比较两个查询的执行情况。六、查询优化测试1.数据准备，导入TPCH 数据集。数据导入方法同前面Footmark 的导入类似。2.

5、对以下查询进行优化,写出你的优化方法. 实际执行这个查询, 记录你的执行时间(毫秒). 实验中出现的问题实验内容一、数据库的恢复操作（导入数据）1.在【程序】中打开Microsoft SQL Server Management Studio。新建数据库“FoodmartII”打开Microsoft SQL Server Management Studio，如图：新建数据库“FoodmartII”，如图：2.在数据库FoodmartII 上右键单击，选择【任务】【导入数据】。 如图：3.在“导入和导出向导”对话框中，数据源选择“Microsoft Access”，单击“文件名”后面的【浏览】按钮

6、，按你的存储路径找到Foodmart.mdb 文件。单击【下一步】。 如图，选择“Microsoft Access”，找到Foodmart.mdb 文件：4.在“选择目标”部分，注意目标数据库的名称应为刚才建立的“FoodmartII”。如图，选择我刚刚建立的“FoodmartII”数据库：5.选择复制一个或多个数据库表。 如图，勾选“复制一个或多个数据库表”：在接下来的对话框中选择可能用到的数据表，根据需要勾选。我选择了全部的数据表，并单击下一步，如图：单击【下一步】后，选择“立即执行”，如图：如下图，可看到导入成功，单击【关闭】按钮： 观察数据库引擎中的FoodmartII，我们可以看到数

7、据库中有哪些表，例如account表,category表，currency表等，如图：我们点击cureency表中的索引，可以看到初始时并没有任何索引，如图：右键cuurency表，选择“编辑前200行”，可以看到表中的数据，如图：二、理解索引对查询的影响1.新建查询，在查询窗口中输入一个查询命令。select customer_idfrom customerwhere customer_id60002.在【查询】菜单中选择【显示估计的查询计划】，注意观察查询窗口下面的执行计划窗口。如图，表扫描占100%：执行该查询（使用工具栏上的“执行”按钮或者【查询】菜单上的“执行”命令），观察右侧【属性

8、】窗口中“返回的行数”“占用时间”等关键信息。如图，我们可以看到返回的行数为4281行，占用的时间大约为2秒多：3.为Customer 表建立索引。建立Customer_id 列的非聚集索引，如下图所示。输入命令： create index ID_nonclus on customer(customer_id);建立非聚集索引：在customer表中查看索引，可以看到我们已经建立好的非聚集索引，如图：建立好索引后，仍使用如下查询命令：select customer_idfrom customerwhere customer_id6000在菜单栏中的“查询”下点击“显示估计的执行计划”，观察新的

9、查询计划，如图，新的执行计划索引查找占100%：执行该查询，在【属性】窗口中观察查询时间。如图，我们可以看到，建立好索引再进行查询，占用时间减少到不足1秒：三、分析查询条件对查询执行的影响1.新建查询，输入查询命令，再按上面的步骤，观察“估计的查询计划”和“占用时间”时间等信息，比较查询条件对查询执行的影响。Q1： select customer_id from customer where customer_id=2621;初始情况下未建立索引，输入命令后，在菜单栏中的“查询”项下选择“显示估计的执行计划”，表扫描占100%：然后点击执行，在属性栏中可以看到，返回的行数为1，占用的时间为7秒

10、多，如图：然后建立非聚集索引，在新建查询中输入上述命令，选择“显示估计的执行计划”，如图，索引查找占100%：点击“执行”，在属性栏中可以看到，返回的行数为1，占用的时间为2秒多，如图：再把where 条件分别改写为：customer_id2621 和 customer_id2621，观察他们有什么异同。总结查询命令书写的经验。 Q2： select customer_id from customer where customer_id2621;显示估计的执行计划，表扫描占100%：点击“执行”，在属性栏中可以看到，返回的行数为7650行，占用的时间为3秒多，如图：建立非聚集索引后，显示估计的

11、执行计划，可以看到，索引查找占100%：点击“执行”后，在属性栏中可以看到返回的行数为7650行，占用的时间为2秒多，如图： Q3: select customer_id from customer where customer_id!=2621;这里我使用的是!=而不是，显示估计的执行计划，表扫描占100%，如图：点击“执行”，在属性栏中可以看到，返回的行数为10260行，占用时间为3秒多，如图：建立索引后，显示估计的执行计划，可以看到，索引扫描占100%：点击“执行”，属性栏中可以看到，返回的行数为10260行，占用的时间为2秒多，如图：可以知道，不等于操作符是永远用不到索引的，索引只能告

12、诉什么存在于表中，而不能告诉什么不存在于表中，当数据库遇到“！=”，“”时，会转而用全表扫描，对a0的条件应写为a0.2.观察下面的查询命令： select full_name,salary from employee where salary30000;在未建立索引的情况显示估计的执行计划，表扫描占100%，如图：返回行数为8行，时间大约3秒多，如图：在emplyee 表建立salary 列的非聚集索引。再次观察上面这个查询命令的查询计划和执行情况。RID查找占87%，索引查找占13%，如图：执行后，返回行数为8，占用时间为2秒多，如图：（1）请写出你对以上内容的分析或得到的经验。 尽量少用

13、不等于查询条件 当需要查找的数据特别多时，使用全表扫描或许比索引扫描还要好（2）试一试, 你还能得到哪些查询命令书写的经验? (不同查询语句导致不同查询计划) 当插入的数据为数据表的记录数量10%以上时，首先需要删除该表的索引来提高数据的插入效率，当数据全部插入后再建立索引。避免在索引列上使用函数或计算，在where子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描，举例：低效：select * from table where salary*1225000高效：select * from table where salary25000/12索引列上用=替代，举例：高效：sel

14、ect * from table where Deptno=4低效：select * from table where Deptno3四、分析连接条件对连接操作的影响1.对比下面查询的查询计划和查询执行情况Q41:Select employee.employee_id,full_name,employee.salary,pay_date, salary_paidfrom employee,salary显示估计的执行计划，如图，嵌套循环96%,表假脱机4%：Q42:select employee.employee_id,full_name,employee.salary,pay_date,sal

15、ary_paidfrom employee,salarywhere employee.employee_id=salary.employee_id显示估计的执行计划，哈希匹配50%，表扫描各占41%和9%：点击“执行”，返回行数为21252行，占用时间3秒多：Q43：Select employee.employee_id,full_name,employee.salary,pay_date,salary_paidfrom employee,salarywhere employee.employee_idsalary.employee_id显示估计的执行计划，嵌套循环占73%，索引假脱机27%：

16、但是，点击“执行”，因为数据溢出，无法完成。2.在employee 表上对employee_id 列建立聚集索引.观察查询计划和执行情况的变化.create CLUSTERED index ID_cluson employee(employee_id);如图：Q41:select employee.employee_id,full_name,employee.salary,pay_date,salary_paidfrom employee,salary显示估计的执行计划，嵌套循环占96%，表假脱机4%：Q42:select employee.employee_id,full_name,empl

17、oyee.salary,pay_date,salary_paidfrom employee,salarywhere employee.employee_id=salary.employee_id显示估计的执行计划，哈希匹配50%，聚集索引扫描9%，表扫描41%：点击“执行”，返回行数为21252行，占用时间为0.320秒：Q43:select employee.employee_id,full_name,employee.salary,pay_date,salary_paidfrom employee,salarywhere employee.employee_idsalary.employe

18、e_id显示估计的执行计划，嵌套循环73%，索引假脱机27%：同样因为数据溢出无法完成执行。分析以上内容，总结你的查询优化经验。索引分为聚集索引和非聚集索引两种。聚集索引就是物理索引，也就是数据的物理存储顺序，聚集索引的叶子节点就是数据行本身，含有聚集索引的表，它的数据行的组织方式，是跟聚集索引的顺序是一致的，一张表里，只能有一个聚集索引，决定着数据行的组织方式。非聚集索引是逻辑索引，它跟数据的组织顺序是毫无关系的，用一系列指针来指向数据行，从而描述数据行的位置。聚集索引的最大优势就是大范围数据查询有着较高的速率，能以最快的速度缩小查询范围，以最快的速度进行字段排序。聚集索引字段选择优先级：时

19、间字段会进行大范围查询的列具有唯一值的有实际意义的字段自增列ID。1.时间字段：若表里面有时间列，并且时间是按照数据插入顺序增长时（时间无需唯一即可有重复值，哪怕是大范围重复），建议采用时间列作为聚集索引的第一选择。理由：聚集索引有一个巨大的优势就是进行大范围数据查找，而且这个优势会随着数据量的增加而越来越明显，一般来说我们需要进行大数据量范围查询时都会用时间列围作为筛选条件，由于聚集索引不存在书签查找而且可以进行连续扫描，因此查询速度会非常快。时间列数据最好是顺序插入的这样可以尽量减少磁盘碎片，是数据存储相对集中，便于连续数据读取。2.会进行大范围查询的列：若表里面没有时间字段或者时间字段不

20、适合做聚集索引，可以选择那些在建表时就明确知道会经常进行大范围数据筛选的列，而且最好是选择性较低的列(即有较多重复值的列，性别这种列不算啦)，如有必要可以使用组合索引。理由：聚集索引在数据查询的优势主要在于范围数据查找，把聚集索引弄成唯一的把这个大好优势给白白浪费了。3.具有唯一值的有实际意义的字段：若找不到适合条件1、2的列，那还是乖乖的把聚集索引列建立在唯一列上吧，最好找那种有实际意义的具有唯一性的列，比如订单表可以用订单号作聚集索引，订单明细表使用订单号和产品编号做联合聚集索引。理由：找不到合适的时间字段和较低选择性字段的话，把主键建成聚集索引是我们大多情况下的选择。这里建议把唯一性的聚

21、集索引顺便建成主键，和编码时方法、变量命名一样，推荐列名自解释，即看到列名就知道它就是主键，省得你再去猜，比如订单表你来个自增ID列做主键，再建一个OrderCode列做订单号，用这个表时你得怀疑这个OrderCode是不是唯一的呢，有没有建立唯一约束呢，同理在订单明细表来个自增列ID也会产生如此疑问，产生疑问还是小事，若是你忘记了在应该唯一的列上建立约束，没准哪天程序控制不好给你个巨大的惊喜。4.自增列ID：前面3中条件都找不到合适的列了还是使用我们的神器自增列ID吧，自增列ID也是我们使用最多的主键(顺便也就成聚集索引了)，而且能较好满足我们大多数需求。自增ID列堪称无所不能，int类型只

22、占用4个字节完全满足窄索引要求，绝对的顺序存储可以有效降低索引碎片，完全符合我们的见表习惯，有用没用来个自增ID列做主键总是没错的。 与聚集索引不同，非聚集索引可以建立多个，这也给我们带来了很大的灵活，毕竟聚集索引就那么一个不可能靠它满足所有需求，更多的我们得依赖非聚集索引。但是，建立索引是有代价的，任何涉及到索引列的数据修改都会导致索引的修改，索引越多数据的曾、删、改的额外代价也就越大。对于非聚集索引来说，我们的目标是用尽可能少的索引覆盖尽可能多的查询。 非聚集索引的列选择顺序(组合索引):经常被使用为查询条件列具有较高选择性的列(选择性越高越好，唯一最好)经常排序的列1.经常被使用为查询条

23、件列：我们的查询千变万化，建立索引时要首先考虑有哪些列被经常性的用于各种查询，把使用频率较高的列作为组合索引的第一列(先导列)，若一个查询中没有用到组合索引中的先导列，多数情况下这个索引就不会被使用，因此为了尽可能多的复用组合索引把使用较多的查询列作为组合索引的第一列吧。(关于这点对于聚集索引的组合索引同样适用)2.具有较高选择性的列：这点很简单尽量使用高选择性列作为先导列，如果可以通过第一个条件过滤(随便什么判定逻辑=、like)，只要能大幅减少数据范围，就把它作为先导列。3.条件1、2、3都确定不了时那就用经常被排序的列吧，我们的很多操作都需要先进行排序才可以进行进一步查询，比如group

24、 by，like等操作都是要先进行排序操作才可以完成下一步查询。五、视图的使用1.执行下面的查询命令，观察查询计划和执行情况。Q51:select lname,fname,brand_name,product_namefrom sales_fact_1998,product,customerwhere customer.customer_id=sales_fact_1998.customer_idand product.product_id=sales_fact_1998.product_idand sales_fact_1998.customer_id=9143显示估计的执行计划，哈希匹配7

25、%，表扫描67%，嵌套循环1%，表扫描23%，表扫描2%：点击“执行”，返回的行数为147行，占用时间为2秒多：2. 建立视图“cust_prod_sales”，由product，customer ， sales_fact_1998 三个表组成，其中包含查询常用的列（选取的列可以多于查询Q51），再执行下面的查询。建立视图：create view cust_prod_salesasselect lname,fname,brand_name,product_name,customer.customer_idfrom sales_fact_1998,product,customer;输入查询命令：

26、Q52:select lname,fname,brand_name,product_namefrom cust_prod_saleswhere customer_id=9143显示估计的执行计划，嵌套循环98%，行计数假脱机2%：同样因为数据溢出，无法完成执行。请写出你对以上内容的分析和得到的经验。建立普通的视图对查询并没有太大的作用，因为对视图的查询最终也要转化为对基本表的查询，视图的使用只是可以把表隐藏起来，但是，在视图上建立索引却可以加快查询速度，但会增加开销。六、查询优化测试1.数据准备，导入TPCH 数据集。数据导入方法同前面Footmark 的导入类似。建立TPCH数据库，如图：右

27、键单击TPCH数据库，选择任务中的导入数据库：导入数据时，“数据源”选择“平面文件”，通过浏览指定文件夹和文件名(类型选择”所有文件”)，如图：单击左侧“数据源”列表中“列”项目，指定” 列分隔符”为“竖线”，单击”重置列”按钮，观察”预览行”窗口显示的数据格式是否正确。如下图：如下图，导入CUSTOMER表：导入成功：在管理栏中可以看到CUSTOMER表的各列名及其属性：导入LINEITEM表：导入成功：在管理栏中可以看到LINEITEM表的各列名及其属性：导入NATION表：导入成功：在管理栏中可以看到NATION表的各列名及其属性：导入ORDER表：导入成功：在管理栏中可以看到ORDER

28、表的各列名及其属性：导入PART表：导入成功：在管理栏中可以看到PART表的各列名及其属性：导入PARTSUPP表：导入成功：在管理栏中可以看到PARTSUPP表的各列名及其属性：导入REGION表：导入成功：在管理栏中可以看到REGION表的各列名及其属性：导入SUPPLIER表：导入成功：在管理栏中可以看到SUPPLIER表的各列名及其属性：2. 对以下查询进行优化,写出你的优化方法. 实际执行这个查询, 记录你的执行时间(毫秒).Q1:selectl_returnflag,l_linestatus,sum(l_quantity) as sum_qty,sum(l_extendedpric

29、e) as sum_base_price,sum(l_extendedprice*(1-l_discount) as sum_disc_price,sum(l_extendedprice*(1-l_discount)*(1+l_tax) as sum_charge,avg(l_quantity) as avg_qty,avg(l_extendedprice) as avg_price,avg(l_discount) as avg_disc,count(*) as count_orderfromlineitemwherel_shipdate = 1998-12-01group byl_retur

30、nflag,l_linestatusorder byl_returnflag,l_linestatus;首先在未对表进行任何操作的情况下执行，返回行数为4行，占用时间为6秒多：然后，在lineitem表的l_returnflag,l_linestatus列上建立非聚集索引：create index lndex_lon lineitem(l_returnflag,l_linestatus);执行查询，返回行数为4列，占用时间为5秒多：对这个查询，我尝试了建立临时表，建立聚集索引的方法，均会导致总时间更多。Q2:select n_name, sum(l_extendedprice * (1 - l_discount) as revenuefrom customer,orders,lineitem,supplier,nation,regionwhere c_custkey = o_custkeyand l_orderkey = o_orderkeyand l_suppkey = s_suppkeyand c_nationkey = s_nationkeyand s_nationkey = n_nationkeyand n_region