数据库 优化查询 实验报告.docx
《数据库 优化查询 实验报告.docx》由会员分享，可在线阅读，更多相关《数据库 优化查询 实验报告.docx（13页珍藏版）》请在冰豆网上搜索。
数据库优化查询实验报告
数据库系统实验报告
专业
网络工程
班级
13级网工本1班
学号
20130081132
姓名
刘芳
提交日期
2015.6.12
实验八查询优化
【实验目的】
1.了解数据库查询优化方法和查询计划的概念。
2.学会分析查询的代价。
【实验内容及步骤】
针对单表查询、连接查询、嵌套查询这三种SQL操作，查看查询分析器给出的查询计划，分析优化效果。
1．单表查询（针对GSM数据库）
针对表BTS，在BTS经度上建立非簇集索引（必须使用Createindex语句），进行下列查询：
（1）查询BTS经度位于121.089335和121.142595之间的BTS基本信息。
selectBTS.*
fromBTS
whereLONGITUDEbetween121.089335and121.142595
（2）对海拔查询一个范围内的所有记录（例如大于30，小于60）。
SQL语句为：
selectBTS.*
fromBTS
whereALTITUDEbetween30and60
（3）对BTS经度进行大范围查询（就是结果集包括几乎所有记录）。
selectBTS.*
fromBTS
whereLONGITUDEbetween121.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）;
insertintoBTS
values（'JIANHANG1',42217,121.137365,41.112287,45,'Datang',5）;
createindexindex2onBTS（LONGITUDE）;
（1）查询BTS经度位于121.089335和121.142595之间的BTS基本信息。
SQL语句为：
selectBTS.*
fromBTS
whereLONGITUDEbetween121.089335and121.142595
（2）对海拔查询一个范围内的所有记录（例如大于30，小于60）
SQL语句为：
selectBTS.*
fromBTS
whereALTITUDEbetween30and60
（3）对BTS经度进行大范围查询（就是结果集包括几乎所有记录）
SQL语句为：
selectBTS.*
fromBTS
whereLONGITUDEbetween121.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）;
importfrom"C:
\bts.csv"OFDELMETHODP（1,2,3,4,5,6,7）MESSAGES"1"INSERTINTOBTS（BTSNAME,BSCID,LONGITUDE,LATITUDE,ALTITUDE,BTSCOMPANY,BTSPOWER）;
dropindexindex1;
createindexindex1onBTS（LONGITUDE）CLUSTER;
REORGTABLEBTSINDEXINDEX1INPLACEALLOWWRITEACCESSSTART;
（1）查询BTS经度位于121.089335和121.142595之间的BTS基本信息。
SQL语句为：
selectBTS.*
fromBTS
whereLONGITUDEbetween121.089335and121.142595
（2）对海拔查询一个范围内的所有记录（例如大于30，小于60）。
SQL语句为：
selectBTS.*
fromBTS
whereALTITUDEbetween30and60
（3）对BTS经度进行大范围查询（就是结果集包括几乎所有记录）。
SQL语句为：
selectBTS.*
fromBTS
whereLONGITUDEbetween121.089335and121.185335
b．撤销在BTS经度上建立的任何索引（必须使用Dropindex语句），重复上面的三个查询，比较在有非簇集索引、簇集索引和无索引的情况下，查询效率的不同。
SQL语句：
dropindexindex1
结论：
三种情况相比，无索引效率最低，有非簇集索引会加快查询效率，簇集索引效率最高。
（3）索引代价：
在有一般索引、簇集索引和无索引的情况下插入、删除、更新数据，通过执行计划比较每个操作的执行效率。
注意不要只对一条记录操作，应该插入、删除、更新一批（比如200条索引键值比较集中的记录）记录，这样才能测出真实的效率。
插入数据，SQL语句如下：
insertintoBTS
values（'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,30,'Siemens',5）,
（'JUANYANCHANG5',42218,121.151205,41.092877,30,'Siemens',5）,
（'JUANYANCHANG6',42218,121.151205,41.092877,45,'Siemens',5）,
（'SHUNTIANDASHA4',42218,121.119805,41.127977,45,'Siemens',5）,
（'SHUNTIANDASHA5',42218,121.119805,41.127977,45,'Siemens',5）,
（'SHUNTIANDASHA6',42218,121.119805,41.127977,50,'Siemens',5）,
（'YANFUYUAN4',42218,121.141095,41.143977,39,'Siemens',5）,
（'YANFUYUAN5',42218,121.141095,41.143977,35,'Siemens',5）,
（'YANFUYUAN6',42218,121.141095,41.143977,30,'Siemens',5）,
（'ERSHIYIZHONG4',42218,121.169505,41.128033,30,'Siemens',5）,
（'ERSHIYIZHONG5',42218,121.169505,41.128033,25,'Siemens',5）,
（'ERSHIYIZHONG6',42218,121.169505,41.128033,39,'Siemens',5）,
（'SHUILIJU4',42218,121.108283,41.123644,39,'Siemens',5）,
（'SHUILIJU5',42218,121.108283,41.123644,39,'Siemens',5）,
（'SHUILIJU6',42218,121.108283,41.123644,50,'Siemens',5）,
（'ERSHUIXIAO4',42219,121.094185,41.135247,55,'Bell',5）,
（'ERSHUIXIAO5',42219,121.094185,41.135247,55,'Bell',5）,
（'ERSHUIXIAO6',42219,121.094185,41.135247,30,'Bell',5）,
（'ZHENHESHANGSHA4',42219,121.144125,41.121327,30,'Bell',5）,
（'ZHENHESHANGSHA5',42219,121.144125,41.121327,30,'Bell',5）,
（'ZHENHESHANGSHA6',42219,121.144125,41.121327,25,'Bell',5）,
（'YIYAODASHA4',42219,121.161033,41.119171,25,'Bell',5）,
（'YIYAODASHA5',42219,121.161033,41.119171,90,'Bell',5）,
（'YIYAODASHA6',42219,121.161033,41.119171,90,'Bell',5）,
（'QIAONANJIE4',42219,121.151085,41.114307,55,'Bell',5）,
（'QIAONANJIE5',42219,121.151085,41.114307,25,'Bell',5）,
（'QIAONANJIE6',42219,121.151085,41.114307,25,'Bell',5）,
（'GONGMAO4',42220,121.143835,41.099387,50,'Huawei',5）,
（'GONGMAO5',42220,121.143835,41.099387,35,'Huawei',5）,
（'GONGMAO6',42220,121.143835,41.099387,35,'Huawei',5）,
（'ERSHIFAN4',42220,121.122305,41.139177,35,'Huawei',5）,
（'ERSHIFAN5',42220,121.122305,41.139177,25,'Huawei',5）,
（'ERSHIFAN6',42220,121.122305,41.139177,20,'Huawei',5）,
（'HUAYUANXIAOQU4',42221,121.174305,41.123894,30,'Huawei',5）,
（'HUAYUANXIAOQU5',42221,121.174305,41.123894,50,'Huawei',5）,
（'HUAYUANXIAOQU6',42221,121.174305,41.123894,45,'Huawei',5）,
（'JIAOTONGSCHOOL4',42221,121.166065,41.099017,20,'Huawei',5）,
（'JIAOTONGSCHOOL5',42221,121.166065,41.099017,42,'Huawei',5）,
（'JIAOTONGSCHOOL6',42221,121.166065,41.099017,42,'Huawei',5）,
（'RENHETUN4',42221,121.171785,41.146647,42,'Huawei',5）,
（'RENHETUN5',42221,121.171785,41.146647,35,'Huawei',5）,
（'RENHETUN6',42221,121.171785,41.146647,35,'Huawei',5）,
（'JIANYU4',42222,121.111405,41.145957,35,'Siemens',5）,
（'JIANYU5',42222,121.111405,41.145957,35,'Siemens',5）,
（'JIANYU6',42222,121.111405,41.145957,30,'Siemens',5）,
（'FUZHUANGCHANG4',42222,121.140595,41.116377,30,'Siemens',5）,
（'FUZHUANGCHANG5',42222,121.140595,41.116377,45,'Siemens',5）,
（'FUZHUANGCHANG6',42222,121.140595,41.116377,25,'Siemens',5）,
（'HANGTIANHOTEL4',42222,121.107765,41.129667,25,'Siemens',5）,
（'HANGTIANHOTEL5',42222,121.107765,41.129667,50,'Siemens',5）,
（'HANGTIANHOTEL6',42222,121.107765,41.129667,50,'Siemens',5）,
（'XIQUGONGSHANG4',42222,121.095565,41.126137,55,'Siemens',5）,
（'XIQUGONGSHANG5',42222,121.095565,41.126137,50,'Siemens',5）,
（'XIQUGONGSHANG6',42222,121.095565,41.126137,40,'Siemens',5）,
（'LUHUAGANG4',42222,121.115405,41.071177,90,'Siemens',5）,
（'LUHUAGANG5',42222,121.115405,41.071177,50,'Siemens',5）,
（'LUHUAGANG6',42222,121.115405,41.071177,40,'Siemens',5）,
（'SONGCHENG4',42222,121.097538,41.114077,90,'Siemens',5）,
（'SONGCHENG5',42222,121.097538,41.114077,25,'Siemens',5）,
（'SONGCHENG6',42222,121.097538,41.114077,50,'Siemens',5）,
（'KAIHUA4',42222,121.184525,41.116237,45,'Siemens',5）,
（'KAIHUA5',42222,121.184525,41.116237,30,'Siemens',5）,
（'JINGXIAO4',42222,121.135672,41.150097,50,'Siemens',5）,
（'JINGXIAO5',42222,121.135672,41.150097,50,'Siemens',5）,
（'JINGXIAO6',42222,121.135672,41.150097,50,'Siemens',5）,
（'LONGTINGQUWEI4',42223,121.143235,41.127247,50,'Datang',5）,
（'LONGTINGQUWEI5',42223,121.143235,41.127247,25,'Datang',5）,
（'LONGTINGQUWEI6',42223,121.143235,41.127247,30,'Datang',5）,
（'ERBO4',42223,121.162595,41.102167,50,'Datang',5）,
（'ERBO5',42223,121.162595,41.102167,50,'Datang',5）,
（'ERBO6',42223,121.162595,41.102167,30,'Datang',5）,
（'BINHEJIAYUAN4',42223,121.129005,41.11006,30,'Datang',5）,
（'BINHEJIAYUAN5',42223,121.129005,41.11006,25,'Datang',5）,
（'BINHEJIAYUAN6',42223,121.129005,41.11006,30,'Datang',5）,
（'YGMIAOJIE4',42223,121.155005,41.13311,30,'Datang',5）,
（'YGMIAOJIE
升级会员 