数据库系统概论——查询优化实验报告
数据库实验报告
题目: 查询优化 姓名: 李军毅 日期:2016-5-14 实验目的 1. 明确查询优化的重要性; 2. 理解代数优化与物理优化方法; 3. 学习在查询中使用较优的方法。
实验平台 1. OS: Windows XP 2. DBMS: SQLServer2008、VC6、0(或者 visio studio) 3. IDE: Eclipse 实验用时: 两次上机 实验内容
一、 数据库的恢复操作( 导入数据)
1. 在【程序】中打开 Microsoft SQL Server Management Studio 。新建数据库“ Foodma rtII ”
2. 在数据库 FoodmartII 上右键单击, , 选择【任务】【导入数据】。
3. 在“导入与导出向导”对话框中, , 数据源选择“ Microsoft Access ”, , 单击“文
件名”后面的【浏览】按钮, , 按您的存储路径找到 Foodmart 、 mdb 文件。单击【下一步】。
4 4 、在“选择目标”部分, , 注意目标数据库的名称应为刚才建立的“ FoodmartII。
”。
5 5 、选择复制一个或多个数据库表。
6 6 、在接下来的对话框中选择可能用到的数据表, , 根据需要勾选。单击【下一步】并“立即执行”, , 成功导入数据后可以瞧到如下对话框。单击【关 闭】按钮。的 观察数据库引擎中的 FoodmartII, 瞧一瞧数据库中有哪些表, , 表中有哪些数据, , 就是否包含索引, , 就是否建立了视图？
二、理解索引对查询的影响 1 1 、新建查询, , 在查询窗口中输入一个查询命令。
2 2 、在【查询】菜单中选择【显示估计的查询计划】, , 注意观察查询窗口下面的执行计划窗口。执行该查询( ( 使用工具栏上的“执行”按钮或者【查询】菜单上的“执行”命令 ), 观察右侧【属性】窗口中“返回的行数”“占用时间”等关键信息。
3 3为 、为 Customer 立 表建立索引。建立 Customer_id 列的非聚集索引。执行查询, ,在【 属性】窗口中观察查询时间。
三、 分析查询条件对查询执行的影响 1 1 、新建查询, , 输入查询命令, , 再按上面的步骤, , 观察“估计的查询计划”与“占用时间”时间等信息, , 比较查询条件对查询执行的影响。
2 2 、观察查询命令, ,在 在 emplyee 立 表建立 salary 列的非聚集索引。再次观察上面这个查询命令的查询计划与执行情况。
四、 分析连接条件对连接操作的影响 1 1 、对比下面查询的查询计划与查询执行情况
2 2 、在 employee 表上对 employee_id 列建立聚集索引、观察查询计划与执行情况的变化、
五、 视图的使用 1. 执行下面的查询命令, , 观察 查询计划与执行情况。
2. 建立视图“ cust_prod_sales ”, ,由 由 product,customer , sales_fact_1998 三
个表组成, , 其中包含查询常用的列( (询 选取的列可以多于查询 Q51), 再执行下面的查询, , 比较两个查询的执行情况。
六、 查询优化测试 1. 数据准备, , 导入 TPCH 数据集。数据导入方法同前面 Footmark 的导入类似。
2. 对以下查询进行优化, , 写出您的优化方法 、
实际执行这个查询 , 记录您的执行时间( ( 毫秒) ) 、
实验中出现的问题 实验内容
一、数据库的恢复操作( 导入数据)
1 1 、在【程序】中打开 Microsoft SQL Server Management Studio 。新建数据库“ FoodmartII ”
打开 Microsoft SQL Server Management Studio, 如图: :
新建数据库“ FoodmartII ”, , 如图: :
2. 在数据库 FoodmartII 上右键单击, , 选择【任务】【导入数据】。
如图: :
3. 在“导入与导出向导”对话框中, , 数据源选择“ Microsoft Access ”, , 单击“文件名”后面的【浏览】按钮, , 按您的存储路径找到 Foodmart、 、 mdb 文件。单击【下一步】。
如图, , 选择“ Microsoft Access ”, , 找到 Foodmart 、 mdb 文件: :
4. 在“选择目标”部分, , 注意目标数据库的名称应为刚才建立的“ FoodmartII。
”。
如图, , 选择我刚刚建立的“ FoodmartII ”数据库: :
5. 选择复制一个或多个数据库表。
如图, , 勾选“复制一个或多个数据库表”: :
在接下来的对话框中选择可能用到的数据表, , 根据需要勾选。我选择了全部的数据表, , 并单击下一步, , 如图: :
单击【下一步】后, , 选择“立即执行”, , 如图: :
如下图, 可瞧到导入成功, 单击【关闭】按钮:
观察数据库引擎中的 FoodmartII, 我们可以瞧到数据库中有哪些表, , 例如t account 表y ,category 表y ,currency 表等, , 如图: :
我们点击 y cureency 表中的索引, , 可以瞧到初始时并没有任何索引, , 如图: :
右键 y cuurency 表, , 选择“编辑前 0 200 行”, , 可以瞧到表中的数据, , 如图: :
二、理解索引对查询的影响 1 1 、新建查询, , 在查询窗口中输入一个查询命令。
select customer_id from customer where customer_id>6000 2. 在【查询】菜单 中选择【显示估计的查询计划】, , 注意观察查询窗口下面的执行计划窗口。
如图, 表扫描占100%:
执行该查询( ( 使用工具栏上的“执行”按钮或者【查询】菜单上的“执行”命令 ),观察右侧【属性】窗口中“返回的行数”“占用时间”等关键信息。
如图, , 我们可以瞧到返回的行数为 1 4281 行, , 占用的时间大约为 2 2 秒多: :
3.为 为 Customer 表建立索引。建立 Customer_id 列的非聚集索引, , 如下图所示。
输入命令: :
create index ID_nonclus
on customer(customer_id); 建立非聚集索引: : 在 在 r customer 表中查瞧索引, , 可以瞧到我们已经建立好的非聚集索引, , 如图: :
建立好索引后, , 仍使用如下查询命令: :
select customer_id
from customer where customer_id>6000 在菜单栏中的“查询”下点击“显示估计的执行计划”, , 观察新的查询计划, , 如图, , 新的执行计划索引查找占 100%:
执行该查询, , 在【属性】窗口中观察查询时间。如图, , 我们可以瞧到, , 建立好索引再进行查询, , 占用时间减少到不足 1 1 秒: :
三、分析查询条件对查询执行的影 响 1 1 、新建查询, , 输入查询命令, , 再按上面的步骤, , 观察“估计的查询计划”与“占用时间”时间等信息, , 比较查询条件对查询执行的影响。
Q1:
select customer_id
from customer
where customer_id=2621; 初始情况下未建立索引, , 输入命令后, , 在菜单栏中的“查询”项下选择“显示估计的执行计划”, , 表扫描占 100%:
然后点击执行, , 在属性栏中可以瞧到, ,为 返回的行数为 1,为 占用的时间为 7 7 秒多, , 如图: :
然后建立非聚集索引, , 在新建查询中输入上述命令, , 选择“显示估 计的执行计划”, ,如图, , 索引查找占 100%:
” 点击“执行”, , 在属性栏中可以瞧到, , 返回的行数为 1, 占用的时间为 2 2 秒多, , 如图: :
再把 where 条件分别改写为: : customer_id> 2621 与
customer_id<> 2621, 观察她们有什么异同。总结查询命令书写的经验。
Q2:
select customer_id
from customer
where customer_id>2621; 显示估计的执行计划, , 表扫描占 100%:
点击“执行”, , 在属性 栏中可以瞧到, , 返回的行数为 0 7650 行, , 占用的时间为 3 3 秒多, , 如图: :
建立非聚集索引后, , 显示估计的执行计划, , 可以瞧到, , 索引查找占 100%:
点击“执行”后, , 在属性栏中可以瞧到返回的行数为 0 7650 行, , 占用的时间为 2 2 秒多, , 如图: :
Q3:
select customer_id
from customer
where customer_id!=2621; 这里我使用的就是 != 而不就是 <>, 显示估计的执行计划, , 表扫描占 100%, 如图: :
点击“执行”, , 在属性栏中可以瞧到, , 返 回的行数为 0 10260 行, , 占用时间为 3 3 秒多, ,如图: :
建立索引后, , 显示估计的执行计划, , 可以瞧到, , 索引扫描占100%:
点击“执行”, , 属性栏中可以瞧到, , 返回的行数为 0 10260 行, , 占用的时间为 2 2 秒多, ,如图: :
可以知道, , 不等于操作符就是永远用不到索引的, , 索引只能告诉什么存在于表中, ,而不能告诉什么不存在于表中, , 当数据库遇到“！= = ”, , “ <> ”时, , 会转而用全表扫描, ,对 对 0 a<>0 的条件应写为 a<0 or a>0 、
2. 观察下面的查询命令: :
select full_name,salary
from employee
where salary>30000; 在未建立索引的情况显示估计的执行计划, , 表扫描占 100%, 如图: :
返回行数为 8 8 行, , 时间大约 3 3 秒多, , 如图: :
在 在 emplyee 立 表建立 salary 列的非聚集索引。再次观察上面这个查询命令的查询计划与执行情况。D RID 查找占 87%, 索引查找占 13%, 如图: :
执行后, , 返回行数为 8, 占用时间为 2 2 秒多, , 如图: :
（1 1 ）
请写出您对以上内容的分析或得到的经验。
尽量少用不等于查询条件
当需要查找的数据特别多时, , 使用全表扫描或许比索引扫描还要好
( ( 2) 试一试 , 您还能得到哪些查询命令书写的经验 ? ( 不同查询语句导致不同查询计划) )
量 当插入的数据为数据表的记录数量 10% 以上时, 首先需要删除该表的索引来提高数据的插入效率, 当数据全部插入后再建立索引。
避免在索引列上使用函数或计算, 在where 子句中, 如果索引列就是函数的一部分, 优化器将不使用索引而使用全表扫描, 举例: 低效:select * from table where salary*12>25000 高效:select * from table where salary>25000/12
索引列上用>= 替代>, 举例: 高效:select * from table where Deptno>=4 低效:select * from table where Deptno>3 四、分析连接条件对连接操作的影响 1 1 、对比下面查询的查询计划与查询执行情况
Q41: Select employee、employee_id,full_name,employee、salary,pay_date,
salary_paid from employee,salary 显示估计的执行计划, , 如图, , 嵌套循环 96%, , 表假脱机 4%:
Q42: select employee、employee_id,full_name,employee、salary,pay_date, salary_paid
from employee,salary where employee、employee_id=salary、employee_id 显示估计的执行计划, , 哈希匹配 50%, 表扫描各占 41%与 与 9%:
点击“执行”, , 返回行数为 2 21252 行, , 占用时间 3 3 秒多: :
Q43: Select employee、employee_id,full_name,employee、salary,pay_date,salary_paid from employee,salary where employee、employee_id>salary、employee_id 显示估计的执行计划, , 嵌套循环占 73%, 索引假脱机 27%:
但就是, 点击“执行”, 因为数据溢出, 无法完成。
2.在 在 employee 对 表上对 employee_id 列建立聚集索引、观察查询计划与执行情况的变化、
create CLUSTERED index ID_clus on employee(employee_id); 如图: :
Q41: select employee、employee_id,full_name,employee、salary,pay_date, salary_paid from employee,salary 显示估计的执行计划, , 嵌套循环占 96%, 表假脱机 4%:
Q42: select employee、employee_id,full_name,employee、salary,pay_date, salary_paid from employee,salary where employee、employee_id=salary、employee_id 显示估计的执行计划, , 哈希匹配 50%, 聚集索引扫描 9%, 表扫描 41%:
点击“执行”, , 返回行数为 2 21252 行, , 占用时间为 0 0 、0 320 秒: :
Q43: select employee、employee_id,full_name,employee、salary,pay_date,salary_paid
from employee,salary where employee、employee_id>salary、employee_id 显示估计的执行计划, , 嵌套循环 73%, 索引假脱机 27%:
同样因为数据溢出无法完成执行。
分析以上内容, , 总结您的查询优化经验。
索引分为聚集索引与非聚集索引两种。
聚集索引就就是物理索引, , 也就就是数据的物理存储顺序, , 聚集索引的叶子节点就就是数据行本身, , 含有聚集索引的表, , 它的数据行的组织方式, , 就是跟聚集索引的顺序就是一致的, , 一张表里, , 只能有一个聚集索引, , 决定着数据行的组织方式。
非聚集索引就是逻辑索引, , 它跟数据的组织顺序就是毫无关系的, , 用一系列指针来指向数据行, , 从而描述数据行的位置。
聚集 索引的最大优势就就是大范围数据查询有着较高的速率, , 能以最快的速度缩小查询范围, , 以最快的速度进行字段排序。聚集索引字段选择优先级: : 时间字段 >> 会进行大范围查询的列 >> 具有唯一值的有实际意义的字段 >> 自增列ID 。
1 1 、时间字段: : 若表里面有时间列, , 并且时间就是按照数据插入顺序增长时
( ( 时间无需唯一即可有重复值, , 哪怕就是大范围重复 ), 建议采用时间列作为聚集索引的第一选择。理由: : 聚集索引有一个巨大的优势就就是进行大范围数据查找, ,而且这个优势会随着数据量的增加而越来越明显, , 一般来说我们需要进行大数据量范围查询时都会用时间 列围作为筛选条件, , 由于聚集索引不存在书签查找而且可以进行连续扫描, , 因此查询速度会非常快。时间列数据最好就是顺序插入的这样可以尽量减少磁盘碎片, , 就是数据存储相对集中, , 便于连续数据读取。
2 2 、会进行大范围查询的列: : 若表里面没有时间字段或者时间字段不适合做聚集索引, , 可以选择那些在建表时就明确知道会经常进行大范围数据筛选的列, ,而且最好就是选择性较低的列( ( 即有较多重复值的列, , 性别这种列不算啦 ), 如有必要可以使用组合索引。理由: : 聚集索引在数据查询的优势主要在于范围数据查找, , 把聚集索引弄成唯一的把这个大好优势给白白浪费了 。
3 3 、具有唯一值的有实际意义的字段: :件 若找不到适合条件 1 1 、2 2 的列, , 那还就是乖乖的把聚集索引列建立在唯一列上吧, , 最好找那种有实际意义的具有唯一性的列, , 比如订单表可以用订单号作聚集索引, , 订单明细表使用订单号与产品编号做联合聚集索引。理由: : 找不到合适的时间字段与较低选择性字段的话, , 把主键建成聚集索引就是我们大多情况下的选择。
这里建议把唯一性的聚集索引顺便建成主键, , 与编码时方法、变量命名一样, ,推荐列名自解释, , 即瞧到列名就知道它就就是主键, , 省得您再去猜, , 比如订单表您来个自增 D ID 列做主键, , 再建一个 e OrderCode 列 做订单号, , 用这个表时您得怀疑个 这个 e OrderCode 就是不就是唯一的呢, , 有没有建立唯一约束呢, , 同理在订单明细表来个自增列 D ID 也会产生如此疑问, , 产生疑问还就是小事, , 若就是您忘记了在应该唯一的列上建立约束, , 没准哪天程序控制不好给您个巨大的惊喜。
4. 自增列 ID: 前面3 3 中条件都找不到合适的列了还就是使用我们的神器自增列 列 D ID 吧, , 自增列 D ID 也就是我们使用最多的主键( ( 顺便也就成聚集索引了 ), 而且能较好满足我们大多数需求。自增 D ID 列堪称无所不能t ,int 类型只占用 4 4 个字节完全满足窄索引要求, , 绝对的顺序存储可以有效降低索引碎片, , 完 全符合我们的见表习惯, , 有用没用来个自增 D ID 列做主键总就是没错的。
与聚集索引不同, , 非聚集索引可以建立多个, , 这也给我们带来了很大的灵活, ,毕竟聚集索引就那么一个不可能靠它满足所有需求, , 更多的我们得依赖非聚集索引。但就是, , 建立索引就是有代价的, , 任何涉及到索引列的数据修改都会导致索引的修改, , 索引越多数据的曾、删、改的额外代价也就越大。对于非聚集索引来说, , 我们的目标就是用尽可能少的索引覆盖尽可能多的查询。
非聚集索引的列选择顺序( ( 组合索引 ): 经常被使用为查询条件列 >> 具有较高选择性的列( ( 选择性越高越好, , 唯一最好 )>> 经常排序的列
1 1 、经常被使用为查询条件列: : 我们的查询千变万化, , 建立索引时要首先考虑有哪些列被经常性的用于各种查询, , 把使用频率较高的列作为组合索引的第一列( ( 先导列 ), 若一个查询中没有用到组合索引中的先导列, , 多数情况下这个索引就不会被使用, , 因此为了尽可能多的复用组合索引把使用较多的查询列作为组合索引的第一列吧。( ( 关于这点对于聚集索引的组合索引同样适用) )
2 2 、具有较高选择性的列: : 这点很简单尽量使用高选择性列作为先导列, , 如果可以通过第一个条件过滤( ( 随便什么判定逻辑= = 、> > 、< < 、 like), 只要 能大幅减少数据范围, , 就把它作为先导列。
3 3件 、条件 1 1 、2 2 、3 3 都确定不了时那就用经常被排序的列吧, , 我们的很多操作都需要先进行排序才可以进行进一步查询, , 比如 e group by,like 等操作都就是要先进行排序操作才可以完成下一步查询。
五、视图的使用 1 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 product、product_id=sales_fact_1998、product_id and sales_fact_1998、customer_id=9143 显示估计的执行计划, , 哈希匹配 7%, 表扫描 67%, 嵌套循环 1%, 表扫描 23%, 表扫描2%:
点击“执行”, , 返回的行数为 7 147 行, , 占用时间为 2 2 秒多: :
2. 建立视图“ cust_prod_sales ”, ,由 由 product,customer , sales_fact_1998 三个 表组成, , 其中包含查询常用的列( ( 选取的列可以多于查询 Q51), 再执行下面的查询。
建立视图: :
create view cust_prod_sales
as select lname,fname,brand_name,product_name,customer、customer_id from sales_fact_1998,product,customer; 输入查询命令: Q52: select lname,fname,brand_name,product_name from cust_prod_sales where customer_id=9143 显示估计的执行计划, , 嵌套循环 98%, 行计数假脱机 2%:
同样因为数据溢出, 无法完成执行。
请写出您对以上内容的分析与得到的经验。
建立普通的视图对查询并没有太大的作用, 因为对视图的查询最终也要转化为对基本表的查询, 视图的使用只就是可以把表隐藏起来, 但就是, 在视图上建立索引却可以加快查询速度, 但会增加开销。
六、查询优化测试 1 1 、数据准备, , 导入 TPCH 数据集。数据导入方法同前面 Footmark 的导入类似。
立 建立 TPCH 数据库, 如图:
右键单击 H TPCH 数据库, , 选择任务中的导入数据库: :
导入数据时, , “数据源”选择“平面文件”, , 通过浏览指定文件夹与文件名( ( 类型选择”所有文件” ), 如图: :
单击左侧“数据源”列表中“列”项目, , 指定”
列分隔符”为“竖线”, , 单击”重置列”按钮, , 观察”预览行”窗口显示的数据格式就是否正确。如下图: :
如下图, , 导入 R CUSTOMER 表: :
导入成功: 在管理栏中可以瞧到 R CUSTOMER 表的各列名及其属性: :
导入 M LINEITEM 表: :
导入成功: :
在管理栏中可以瞧到 M LINEITEM 表的各列名及其属性: :
导入 NAN TION 表: :
导入成功:
在管理栏中可以瞧到 N NATION 表的各列名及其属性: :
导入 R ORDER 表: :
导入成功:
在管理栏中可以瞧到 R ORDER 表的各列名及其属性: :
导入 T PART 表: :
导入成功:
在管理栏中可以瞧到 T PART 表的各列名及其属性: :
导入 P PARTSUPP 表: :
导入成功:
在管理栏中可以瞧到 P PARTSUPP 表的各列名及其属性: :
导入 N REGION 表: :
导入成功:
在管理栏中可以瞧到 N REGION 表的各列名及其属性: :
导入 R SUPPLIER 表: :
导入 成功:
在管理栏中可以瞧到 R SUPPLIER 表的各列名及其属性: :
2. 对以下查询进行优化, , 写出您的优化方法、
实际执行这个查询 , 记录您的执行时间( ( 毫秒) ) 、
Q1: select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty,
sum(l_extendedprice) 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_order from lineitem where l_shipdate <= "1998-12-01" group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus; 首先在未对表进行任何操作的情况下执行,为 返回行数为 4 行,为 占用时间为 6 秒多:
然后, ,在 在 m lineitem 表的 s l_returnflag,l_linestatus 列上建立非聚集索引: :
create index lndex_l on lineitem(l_returnflag,l_linestatus); 执行查询, , 返回行数为4 4 列, , 占用时间为5 5 秒多: :
对这个查询, 我尝试了建立临时表, 建立聚集索引的方法,。
均会导致总时间更多。
Q2:
select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer,orders,lineitem,supplier,nation,region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = "ASIA" and o_orderdate >= "1994-01-01" and o_orderdate < "1995-01-01" group by n_name order by revenue desc; 执行查询, , 返回行数为5 5 行, , 占用时间为3 3 秒多: :
然后在各表上建立索引: create index index_c on customer(c_custkey,c_nationkey); create index index_o on orders(o_custkey,o_orderkey,o_orderdate); create index index_l on lineitem(l_orderkey,l_suppkey); create index index_s on supplier(s_suppkey,s_nationkey); create index index_n on nation(n_nationkey,n_regionkey); create index index_r on region(r_regionkey,r_name);
执行查询, , 返回行数为5 5 行, , 占用时间为1 1 秒多: :
Q3: select 100、00 * sum(case when p_type like "PROMO%" then l_extendedprice*(1-l_discount) else 0 end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue from lineitem,part where l_partkey = p_partkey
and l_shipdate >= "1995-09-01" and l_shipdate < "1995-10-1" 执行查询, , 返回行数为1 1 行, , 占用时间为2 2 秒多: :
然后在各表的相应列上建立索引: create index index_l On lineitem(l_extendedprice,
l_discount,
l_partkey,
l_shipdate
) create index index_p On part(p_type,
p_partkey
) 执行查询, , 返回行数为1 1 行, , 占用时间不到1 1秒: :
实验中出 现的问题 1. 在导入数据表, , 修改列名及属性时, , 字符串类型默认为宽度 50, 忘记修改, , 导致数据导入失败
2. 不知道如何建立临时表, , 后经过查询得知
3. 有几个查询因为数据溢出导致执行无法完成
上一篇：计算机维修实验报告
下一篇：电子商务案例分析实验报告