数据库 优化查询 实验报告.docx

数据库系统实验报告专业网络工程班级13级网工本1班学号20130081132姓名刘芳提交日期2015.6.12实验八 查询优化【实验目的】1. 了解数据库查询优化方法和查询计划的概念。2. 学会分析查询的代价。【实验内容及步骤】针对单表查询、连接查询、嵌套查询这三种SQL操作，查看查询分析器给出的查询计划，分析优化效果。1单表查询（针对GSM数据库）针对表BTS，在BTS经度上建立非簇集索引（必须使用Create index语句），进行下列查询：（1）查询BTS经度位于121.089335和121.142595之间的BTS基本信息。select BTS.*from BTSwhere LONGITUDE between 121.089335 and 121.142595（2）对海拔查询一个范围内的所有记录（例如大于30，小于60）。SQL语句为：select BTS.*from BTSwhere ALTITUDE between 30 and 60（3）对BTS经度进行大范围查询（就是结果集包括几乎所有记录）。select BTS.*from BTSwhere LONGITUDE between 121.089335 and 121.185335分析三种情况下的查询计划有何不同？（1）表中记录数多少的影响：如果BTS表中只有一条记录，重复上面的三个查询。执行SQL语句DROP TABLE BTS;CREATE TABLE BTS ( BTSNAME CHARACTER (20) NOT NULL , BSCID INTEGER NOT NULL , LONGITUDE DECIMAL (9, 6), LATITUDE DECIMAL (8, 6), ALTITUDE INTEGER, BTSCOMPANY CHARACTER (10), BTSPOWER DECIMAL (2,1), PRIMARY KEY (BTSNAME) , FOREIGN KEY (BSCID) REFERENCES BSC (BSCID) ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION ) ;insert into BTSvalues(JIANHANG1,42217,121.137365,41.112287,45,Datang,5);create index index2 on BTS(LONGITUDE);（1） 查询BTS经度位于121.089335和121.142595之间的BTS基本信息。SQL语句为：select BTS.*from BTSwhere LONGITUDE between 121.089335 and 121.142595（2） 对海拔查询一个范围内的所有记录（例如大于30，小于60）SQL语句为：select BTS.*from BTSwhere ALTITUDE between 30 and 60（3） 对BTS经度进行大范围查询（就是结果集包括几乎所有记录）SQL语句为：select BTS.*from BTSwhere LONGITUDE between 121.089335 and 121.185335（2）不同索引类型对查询的影响a在BTS经度上建立簇集索引（必须使用Alter index语句），重复上面的三个查询。如果没有不同，可能是建立簇集索引不立即导致表中记录重新排列的缘故，如何启动这种重组过程？执行SQL语句DROP TABLE BTS;CREATE TABLE BTS ( BTSNAME CHARACTER (20) NOT NULL , BSCID INTEGER NOT NULL , LONGITUDE DECIMAL (9, 6), LATITUDE DECIMAL (8, 6), ALTITUDE INTEGER, BTSCOMPANY CHARACTER (10), BTSPOWER DECIMAL (2,1), PRIMARY KEY (BTSNAME) , FOREIGN KEY (BSCID) REFERENCES BSC (BSCID) ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION ) ;import from C:bts.csv OF DEL METHOD P (1,2,3,4,5,6,7) MESSAGES 1 INSERT INTO BTS (BTSNAME,BSCID,LONGITUDE,LATITUDE,ALTITUDE,BTSCOMPANY,BTSPOWER);drop index index1;create index index1 on BTS(LONGITUDE) CLUSTER;REORG TABLE BTS INDEX INDEX1 INPLACE ALLOW WRITE ACCESS START ;（1） 查询BTS经度位于121.089335和121.142595之间的BTS基本信息。SQL语句为：select BTS.*from BTSwhere LONGITUDE between 121.089335 and 121.142595（2） 对海拔查询一个范围内的所有记录（例如大于30，小于60）。SQL语句为：select BTS.*from BTSwhere ALTITUDE between 30 and 60（3） 对BTS经度进行大范围查询（就是结果集包括几乎所有记录）。SQL语句为：select BTS.*from BTSwhere LONGITUDE between 121.089335 and 121.185335b撤销在BTS经度上建立的任何索引（必须使用Drop index语句），重复上面的三个查询，比较在有非簇集索引、簇集索引和无索引的情况下，查询效率的不同。SQL语句：drop index index1结论：三种情况相比，无索引效率最低，有非簇集索引会加快查询效率，簇集索引效率最高。（3）索引代价：在有一般索引、簇集索引和无索引的情况下插入、删除、更新数据，通过执行计划比较每个操作的执行效率。注意不要只对一条记录操作，应该插入、删除、更新一批（比如200条索引键值比较集中的记录）记录，这样才能测出真实的效率。插入数据，SQL语句如下：insert into BTSvalues(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),(YGMIAOJIE5,42223,121.155005,41.13311,35,Datang,5),(YGMIAOJIE6,42223,121.155005,41.13311,35,Datang,5),(ZHUANYEFENJU4,42223,121.120955,41.121694,50,Datang,5),(ZHUANYEFENJU5,42223,121.120955,41.121694,50,Datang,5),(ZHUANYEFENJU6,42223,121.120955,41.121694,25,Datang,5),(SHENGSIJIAN4,42223,121.185335,41.104287,40,Datang,5),(SHENGSIJIAN5,42223,121.185335,41.104287,40,Datang,5),(SHENGSIJIAN6,42223,121.185335,41.104287,40,Datang,5);（1）在一般索引的情况下，执行SQL语句：DROP TABLE BTS;CREATE TABLE BTS ( BTSNAME CHARACTER (20) NOT NULL , BSCID INTEGER NOT NULL , LONGITUDE DECIMAL (9, 6), LATITUDE DECIMAL (8, 6), ALTITUDE INTEGER, BTSCOMPANY CHARACTER (10), BTSPOWER DECIMAL (2,1), PRIMARY KEY (BTSNAME) , FOREIGN KEY (BSCID) REFERENCES BSC (BSCID);IMPORT FROM C:bts.c