数据库 优化查询 实验报告

1、数据库系统实验报告专业网络工程班级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 LONGI

2、TUDE 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 TAB

3、LE 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 UPDAT

4、E 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.14259

5、5（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语句），重复上面的三个查询。如果没有不同，可能是建立簇集索引不立即导致表中记录重新排列的缘故，如何启动这种重组

6、过程？执行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

7、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

8、;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经度进行大范围查询（就是结果集

9、包括几乎所有记录）。SQL语句为：select BTS.*from BTSwhere LONGITUDE between 121.089335 and 121.185335b撤销在BTS经度上建立的任何索引（必须使用Drop index语句），重复上面的三个查询，比较在有非簇集索引、簇集索引和无索引的情况下，查询效率的不同。SQL语句：drop index index1结论：三种情况相比，无索引效率最低，有非簇集索引会加快查询效率，簇集索引效率最高。（3）索引代价：在有一般索引、簇集索引和无索引的情况下插入、删除、更新数据，通过执行计划比较每个操作的执行效率。注意不要只对一条记录操作，应该插入

10、、删除、更新一批（比如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),('XINSO

11、NGLU4',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.120

12、565,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,'Huaw

13、ei',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',

14、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

15、',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.1779

16、65,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,&

17、#39;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

18、9;,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&

19、#39;,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)

20、,('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),(

21、'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'

22、;,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)

23、,('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)

24、,('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),('ERSHUIX

25、IAO6',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

26、',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.

27、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,&

28、#39;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&

29、#39;,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,'H

30、uawei',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'

31、,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),('FUZ

32、HUANGCHANG5',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),(

33、'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&#

34、39;,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),(

35、'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

36、',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

37、9;,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.

38、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,

39、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,'

40、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