搜索
数据库系统实验报告专业网络工程班级13级网工本1班学号20130081132姓名刘芳提交日期2015.6.12实验八查询优化【实验目的】1.了解数据库查询优化方法和查询计划的概念。2.学会分析查询的代价。【实验内容及步骤】针对单表查询、连接查询、嵌套查询这三种SQL操作，查看查询分析器给出的查询计划，分析优化效果。1．单表查询（针对GSM数据库）针对表BTS，在BTS经度上建立非簇集索引（必须使用Createindex语句），进行下列查询：（1）查询BTS经度位于121.089335和121.142595之间的BTS基本信息。selectBTS.*fromBTSwhereLONGITUDEbetween121.089335and121.142595（2）对海拔查询一个范围内的所有记录（例如大于30，小于60）。SQL语句为：selectBTS.*fromBTSwhereALTITUDEbetween30and60（3）对BTS经度进行大范围查询（就是结果集包括几乎所有记录）。selectBTS.*fromBTSwhereLONGITUDEbetween121.089335and121.185335分析三种情况下的查询计划有何不同？（1）表中记录数多少的影响：如果BTS表中只有一条记录，重复上面的三个查询。执行SQL语句DROPTABLEBTS;CREATETABLEBTS(BTSNAMECHARACTER(20)NOTNULL,BSCIDINTEGERNOTNULL,LONGITUDEDECIMAL(9,6),LATITUDEDECIMAL(8,6),ALTITUDEINTEGER,BTSCOMPANYCHARACTER(10),BTSPOWERDECIMAL(2,1),PRIMARYKEY(BTSNAME),FOREIGNKEY(BSCID)REFERENCESBSC(BSCID)ONDELETENOACTIONONUPDATENOACTIONENFORCEDENABLEQUERYOPTIMIZATION);insertintoBTSvalues('JIANHANG1',42217,121.137365,41.112287,45,'Datang',5);createindexindex2onBTS(LONGITUDE);（1）查询BTS经度位于121.089335和121.142595之间的BTS基本信息。SQL语句为：selectBTS.*fromBTSwhereLONGITUDEbetween121.089335and121.142595（2）对海拔查询一个范围内的所有记录（例如大于30，小于60）SQL语句为：selectBTS.*fromBTSwhereALTITUDEbetween30and60（3）对BTS经度进行大范围查询（就是结果集包括几乎所有记录）SQL语句为：selectBTS.*fromBTSwhereLONGITUDEbetween121.089335and121.185335（2）不同索引类型对查询的影响a．在BTS经度上建立簇集索引（必须使用Alterindex语句），重复上面的三个查询。如果没有不同，可能是建立簇集索引不立即导致表中记录重新排列的缘故，如何启动这种重组过程？执行SQL语句DROPTABLEBTS;CREATETABLEBTS(BTSNAMECHARACTER(20)NOTNULL,BSCIDINTEGERNOTNULL,LONGITUDEDECIMAL(9,6),LATITUDEDECIMAL(8,6),ALTITUDEINTEGER,BTSCOMPANYCHARACTER(10),BTSPOWERDECIMAL(2,1),PRIMARYKEY(BTSNAME),FOREIGNKEY(BSCID)REFERENCESBSC(BSCID)ONDELETENOACTIONONUPDATENOACTIONENFORCEDENABLEQUERYOPTIMIZATION);importfromC:\bts.csvOFDELMETHODP(1,2,3,4,5,6,7)MESSAGES1INSERTINTOBTS(BTSNAME,BSCID,LONGITUDE,LATITUDE,ALTITUDE,BTSCOMPANY,BTSPOWER);dropindexindex1;createindexindex1onBTS(LONGITUDE)CLUSTER;REORGTABLEBTSINDEXINDEX1INPLACEALLOWWRITEACCESSSTART;（1）查询BTS经度位于121.089335和121.142595之间的BTS基本信息。SQL语句为：selectBTS.*fromBTSwhereLONGITUDEbetween121.089335and121.142595（2）对海拔查询一个范围内的所有记录（例如大于30，小于60）。SQL语句为：selectBTS.*fromBTSwhereALTITUDEbetween30and60（3）对BTS经度进行大范围查询（就是结果集包括几乎所有记录）。SQL语句为：selectBTS.*fromBTSwhereLONGITUDEbetween121.089335and121.185335b．撤销在BTS经度上建立的任何索引（必须使用Dropindex语句），重复上面的三个查询，比较在有非簇集索引、簇集索引和无索引的情况下，查询效率的不同。SQL语句：dropindexindex1结论：三种情况相比，无索引效率最低，有非簇集索引会加快查询效率，簇集索引效率最高。（3）索引代价：在有一般索引、簇集索引和无索引的情况下插入、删除、更新数据，通过执行计划比较每个操作的执行效率。注意不要只对一条记录操作，应该插入、删除、更新一批（比如200条索引键值比较集中的记录）记录，这样才能测出真实的效率。插入数据，SQL语句如下：insertintoBTSvalues('XUEYUANMEN4',42215,121.149885,41.120547,40,'Bell',5),('XUEYUANMEN5',42215,121.149885,41.120547,40,'Bell',5),('XUEYUANMEN6',42215,121.149885,41.120547,40,'Bell',5),('XINSONGLU4',42215,121.179905,41.112977,50,'Bell',5),('XINSONGLU5',42215,121.179905,41.112977,50,'Bell',5),('XINSONGLU6',42215,121.179905,41.112977,50,'Bell',5),('BINHELU5',42215,121.120565,41.112757,90,'Bell',5),('BINHELU6',42215,121.120565,41.112757,90,'Bell',5),('BINHELU7',42215,121.120565,41.112757,55,'Bell',5),('BINHELU8',42215,121.120565,41.112757,50,'Bell',5),('KAIFAQU4',42216,121.089335,41.120217,50,'Huawei',5),('KAIFAQU5',42216,121.089335,41.120217,50,'Huawei',5),('KAIFAQU6',42216,121.089335,41.120217,50,'Huawei',5),('PINGGUOYUAN4',42216,121.162695,41.137144,25,'Huawei',5),('PINGGUOYUAN5',42216,121.162695,41.137144,25,'Huawei',5),('PINGGUOYUAN6',42216,121.162695,41.137144,25,'Huawei',5),('JIANHANG4',42217,121.137365,41.112287,45,'Datang',5),('JIANHANG5',42217,121.137365,41.112287,25,'Datang',5),('JIANHANG6',42217,121.137365,41.112287,30,'Datang',5),('YIZHUAN5',42217,121.136449,41.129033,50,'Datang',5),('YIZHUAN6',42217,121.136449,41.129033,35,'Datang',5),('YIZHUAN7',42217,121.136449,41.129033,35,'Datang',5),('YIZHUAN8',42217,121.136449,41.129033,30,'Datang',5),('PAOTUAN4',42217,121.177965,41.127767,30,'Datang',5),('PAOTUAN5',42217,121.177965,41.127767,30,'Datang',5),('PAOTUAN6',42217,121.177965,41.127767,30,'Datang',5),('DIANYEJU4',42217,121.128727,41.103949,35,'Datang',5),('DIANYEJU5',42217,121.128727,41.103949,35,'Datang',5),('DIANYEJU6',42217,121.128727,41.103949,40,'Datang',5),('ERZHIGAO4',42217,121.157705,41.107277,40,'Datang',5),('ERZHIGAO5',42217,121.157705,41.107277,40,'Datang',5),('ERZHIGAO6',42217,121.157705,41.107277,40,'Datang',5),('ZHONGFANGGS4',42217,121.126305,41.122877,40,'Datang',5),('ZHONGFANGGS5',42217,121.126305,41.122877,40,'Datang',5),('ZHONGFANGGS6',42217,121.126305,41.122877,40,'Datang',5),('BIANJINGHOTEL4',42218,121.149644,41.127283,40,'Siemens',5),('BIANJINGHOTEL5',42218,121.149644,41.127283,25,'Siemens',5),('BIANJINGHOTEL6',42218,121.149644,41.127283,25,'Siemens',5),('GONGANJU4',42218,121.139235,41.121667,50,'Siemens',5),('GONGANJU5',42218,121.139235,41.121667,45,'Siemens',5),('GONGANJU6',42218,121.139235,41.121667,35,'Siemens',5),('PIJIUCHANG4',42218,121.122705,41.092677,30,'Siemens',5),('PIJIUCHANG5',42218,121.122705,41.092677,35,'Siemens',5),('PIJIUCHANG6',42218,121.122705,41.092677,30,'Siemens',5),('JUANYANCHANG4',42218,121.151205,41.092877,