数据库.docx

数据库.docx

《数据库.docx》由会员分享，可在线阅读，更多相关《数据库.docx（12页珍藏版）》请在冰点文库上搜索。

数据库

实验六数据查询分析优化实验

一、实验目的

1．熟悉了解Sybase数据库中查询分析器的使用，理解数据库查询优化的基本概念。

2．结合文档“数据库物理设计及查询优化-v1-110320.doc”，通过对不同情况下查询语句的执行情况的对比分析，巩固加深查询优化的理解，并进行书写优化SQL语句的初步训练，提高编写高效SQL语句进行数据查询的能力。

二、实验环境

操作系统：

windows7

数据库软件：

采用Sybase数据库管理系统作为实验平台。

Sybase数据库采用SybaseAdaptiveServerEnterprise（ASE）。

或SybaseAdaptiveServerEnterprise（ASE）。

三、实验内容

实验中要进行表中记录数多少、结果集大小、有无索引、不同书写方式的等效SQL、多表连接查询等情况进行查询计划分析，并比较各种查询计划的效率优劣。

四、实验步骤

（一）、查询执行计划观察

从“实验四数据查询与修改实验”中，选取涉及多表查询的select查询语句，执行该语句，利用DBMS查询分析器，观察该语句的查询执行计划，分析查询执行计划包含的各项基本关系代数操作和查询代价。

索引对查询、插入、删除、更新的影响

1．单表查询（针对GSM数据库）

针对表BTS，在BTS经度上建立非簇集索引（必须使用Createindex语句），进行下列查询：

建立索引：

createindexindex1onBTS（Longitude）

（1）查询BTS经度位于121.089335和121.142595之间的BTS基本信息。

查询语句：

selectBTS.*

fromBTS（indexindex1）

whereLongitudebetween121.089335and121.142595

查询结果分析：

（2）对海拔查询一个范围内的所有记录（例如大于30，小于60）。

查询语句：

selectBTS.*

fromBTS（indexBtsName）

whereAltitudebetween30and60

查询结果分析：

（3）对BTS经度进行大范围查询（就是结果集包括几乎所有记

录）。

查询语句：

selectBTS.*

fromBTS（indexindex1）

whereLongitudebetween121.089335and121.185335

查询结果分析：

分析三种情况下的查询计划有何不同？

由于是在BTS经度上建立非簇集索引，第1个和第三个查询先在索引项中查找符合要求的值，再得到表BTS中其他需要的列。

而由于没有海拔上的索引，所以查询二直接扫描BTS表，得到结果。

（1）表中记录数多少的影响：

如果BTS表中只有一条记录，重复上面的三个查询。

（2）不同索引类型对查询的影响

a．在BTS经度上建立簇集索引（必须使用Alterindex语句），重复上面的三个查询。

如果没有不同，可能是建立簇集索引不立即导致表中记录重新排列的缘故，如何启动这种重组过程？

b．撤销在BTS经度上建立的任何索引（必须使用Dropindex语句），重复上面的三个查询，比较在有非簇集索引、簇集索引和无索引的情况下，查询效率的不同。

撤销索引语句：

dropindexBTS.index1

建立非簇集索引时效率：

建立簇集索引时效率：

createclusteredindexindex2onBTS（Longitude）WITHALLOW_DUP_ROW

无任何索引时效率：

dropindexBTS.index2

总结：

三种情况相比，无索引效率最低，有非簇集索引会加快查询效率，簇集索引效率最高。

（3）索引代价：

在有一般索引、簇集索引和无索引的情况下插入、删除、更新数据，通过执行计划比较每个操作的执行效率。

注意不要只对一条记录操作，应该插入、删除、更新一批（比如200条索引键值比较集中的记录）记录，这样才能测出真实的效率。

总结：

三种情况相比，无索引效率最高，有非簇集索引次之，簇集索引效率最低。

2．多表查询

针对表小区话务数据、在（CellID、rate、callcongs）上建立非簇集索引，进行下列查询：

建立非簇集索引语句：

createindexindex4onTRAFFIC（CELLID,rate,callcongs）

（1）查询小区ID位于9132和9543之间的每个小区，半速率话务量比例的最大值、呼叫拥塞率的最大值及其管理小区的BTS名称。

查询语句：

createviewMAX1as（

selectCELLID,max（rate）asMAXRATE,max（callcongs）asMAXCALLCONGS

fromTRAFFIC

whereCELLIDbetween9132and9543

groupbyCELLID

）

selectMAX1.*

fromMAX1（indexCELLID）,cell（indexCellID）

whereMAX1.CELLID=cell.CellID

dropviewMAX1

查询结果分析：

效率：

针对表小区话务数据、在（CellID、rate、callcongs、thtraff）上建立非簇集索引，进行下列查询：

建立非簇集索引语句：

createindexindex5onTRAFFIC（CELLID,rate,callcongs,thtraff）

（2）查询小区ID位于9132和9543之间的每个小区，半速率话务量比例的最大值、呼叫拥塞率的最大值、全速率话务量的最小值及其管辖小区的BSCID，MSCID。

查询语句：

createviewMAX2as（

selectCELLID,max（rate）asMAXRATE,max（callcongs）asMAXCALLCONGS,min（thtraff）asMINTHTRAFF

fromTRAFFIC

whereCELLIDbetween9132and9543

groupbyCELLID

）

selectMAX2.*

fromMAX2（indexCELLID）,cell（indexCellID）

whereMAX2.CELLID=cell.CellID

dropviewMAX2

查询结果分析：

查询效率：

分析两种情况下的查询计划有何不同？

（1）不同索引类型对查询的影响

a．在（CellID、rate、callcongs）上建立簇集索引，重复查询

（1）。

建立簇集索引语句：

createclusteredindexindex6onTRAFFIC（CELLID,rate,callcongs）

b．撤销在（CellID、rate、callcongs）上建立的任何索引，重复查询

（1），比较在有非簇集索引、簇集索引和无索引的情况下，查询效率的不同。

c．针对查询

（1），撤销在（callcongs）上建立的任何索引，重复查询

（1），比较在callcongss上有索引、无索引的情况下，查询效率的不同。

总结：

三种情况相比，无索引效率最低，有非簇集索引会加快查询效率，簇集索引效率最高。

（2）索引代价：

在有非簇集索引、簇集索引和无索引的情况下插入、删除、更新数据，通过执行计划比较每个操作的执行效率。

总结：

三种情况相比，无索引效率最高，有非簇集索引次之，簇集索引效率最低。

a．在小区话务数据表中，插入一批记录。

提示：

可以先在小区话务数据中选择CellID为9573的一批话务数据，然后固定小区CellID为9553将其插入。

b．在小区话务数据表中，删除半速率话务量比例为0的数据。

c．在小区话务数据表中，将拥塞数为0的小区的拥塞数更新为callnum*0.1。

一、对等价的查询语句的执行比较分析

（1）selectavg（Latitude）

fromBSC

groupbyBscName

havingBscName=‘XXX’

和

selectavg（Latitude）

fromBSC

whereBscName==‘XXX’

有和没有groupby，比较其查询效率，并分析。

（2）deletefromBTS

wherelongtitudebetween121.089335and121.1432350

和

deletefromBTS

wherelongtitudein

（selectlongtitude

fromBTS

wherelongtitudebetween121.089335and121.1432350）

比较上面两个查询的效率。

（3）selectCellID,Cell.BtsName,BtsCompany

fromCell,BTS

whereCell.BtsName=BTS.BtsName

和

（selectCellID,Cell.BtsName,BtsCompany

fromCell,BTS）

except

（selectCellID,Cell.BtsName,BtsCompany

fromCell,BTS

whereCell.BtsName!

=BTS.BtsName）

（4）select*

fromTraffData

whereCellIDin

（selectCellID

fromMesureData）

和

select*

fromTraffData

whereexsits

（select*

fromMesureData

whereTraffData.CellID=MesureData.CellID）

非相关子查询和相关子查询的比较

（5）select

from

where….（xxbetween?

?

?

and?

?

?

）

与select

from

where….（xx≥?

?

?

）and（xx≤?

?

?

）

（二）、查询优化

除了建立适当索引，对SQL语句重写外，还有其他手段来进行查询调优，例如调整缓冲区大小，事先建立视图等。

设计实现下列查询，使之运行效率最高。

写出你的查询形式，以及调优过程；并说明最优情况下的运行时间。

（1）将BSC分组查其下的所有BTS并将所有BTS按BscID排序。

（2）将BtsName也进行排序。

（3）查找BSC下的任意一个BTS的BtsName。

（三）、关于查看查询计划

1．本次实验需要访问执行计划，以下给出如何在Sybase数据库中访问执行计划的两种方式

（1）在InteractiveSQL中访问执行计划

InteractiveSQL中提供了下列类型的计划：

a．简要文本计划

b．详细文本计划

c．含或不包含统计信息的图形式计划“图形式计划”

若要选择不同的计划类型，请单击[工具]►[选项]，单击[计划]选项卡，然后选择计划类型。

若要查看计划，请执行一个查询，然后单击位于InteractiveSQL窗口底部的[计划]选项卡。

（2）使用SQL函数访问执行计划

a．要访问简要计划，使用“EXPLANATION函数”。

EXPLANATION函数返回SQL语句的计划优化策略。

b．要访问详细计划，使用“PLAN函数”。

PLAN函数以字符串形式返回SQL语句的详细计划优化策略。

c．要访问图形式计划，使用“GRAPHICAL_PLAN函数”。

GRAPHICAL_PLAN函数以字符串的形式返回XML格式的SQL语句的计划优化策略。

六、实验总结

这次实验的主要任务是通过查看查询计划来比较建立索引和不建立索引的效率的高低。

在建立索引时，可以分为建立簇集索引和非簇集索引来比较。

通过这次实验了解了建立、删除索引的方法。

通过createindex索引名on表名（属性名）来建立索引。

通过drop表名.索引名来删除索引。

并且知道了在建立表时sybase已经建立以主键为索引的簇集索引。

一个表上只能建立一个簇集索引。

通过索引的适当使用可以增加查询的效率。

但是建立索引也会带来额外的开销。

要权衡两者的利弊来选择是否使用索引。

通过比较发现查询时簇集索引的效率高于非簇集索引大于不建立索引的效率。