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

1、数据库系统实验报告专业 网络工程 班级 13 级网工本 1 班学号 20130081132 姓名 刘芳提交日期 2015.6.12实验八 查询优化【实验目的】1. 了解数据库查询优化方法和查询计划的概念。2. 学会分析查询的代价。【实验内容及步骤】针对单表查询、连接查询、嵌套查询这三种 SQL 操作，查看查询分析器给出的查询计划，分析优化效果。1 单表查询（针对 GSM 数据库）针对表 BTS，在 BTS 经度上建立非簇集索引（必须使用 Create index 语句），进行下列查询：（1） 查询 BTS 经度位于 121.089335 和 121.142595 之间的 BTS 基本信息。se

2、lect 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） 表中记录数多少的影响：如果

3、 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 (BSC

4、ID) 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

5、.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 语句），重复上面的三个查询。如果没有不同，可

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) REFEREN

7、CES 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) C

8、LUSTER;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） 对

9、 BTS 经度进行大范围查询（就是结果集包括几乎所有记录）。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),(XINSONGLU4,42215,121.179905,41.112977,5

11、0,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

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

13、,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.129

14、033,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.1

15、03949,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

16、.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,Sie

17、mens,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.1

18、51205,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,5

19、0,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,4

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

21、RSHUIXIAO6,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.11

22、9171,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.

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

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

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

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

27、ens,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,4222

28、2,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),(JIN

29、GXIAO4,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.

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

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

32、,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 IN

33、TEGER 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.csv“ OF DEL METHOD P (1, 2, 3, 4, 5, 6, 7) MESSAGES “1“ INSERT INTO BTS (BTSNAME, BS

34、CID, LONGITUDE, LATITUDE, ALTITUDE, BTSCOMPANY, BTSPOWER);create index index1 on BTS(LONGITUDE);（2）在有簇集索引的情况下，执行 SQL 语句：drop index index1;create index index1 on BTS(LONGITUDE) CLUSTER;（3）在有无索引的情况下，执行 SQL 语句：drop index index1;删除语句为：delete from BTSwhere LONGITUDE between 121.089335 and 121.185335（1）在一

35、般索引的情况下，执行 SQL 语句：drop index index1;create index index1 on BTS(LONGITUDE);（2）在有簇集索引的情况下，执行 SQL 语句：drop index index1;create index index1 on BTS(LONGITUDE) CLUSTER;（3）在有无索引的情况下，执行 SQL 语句：drop index index1;更新语句为：update BTSset LONGITUDE=121.089362where LONGITUDE=121.089335（1）在一般索引的情况下，执行 SQL 语句：drop ind

36、ex index1;create index index1 on BTS(LONGITUDE);（2）在有簇集索引的情况下，执行 SQL 语句：drop index index1;create index index1 on BTS(LONGITUDE) CLUSTER;（3）在有无索引的情况下，执行 SQL 语句：drop index index1;【实验总结】通过这次实验，一个列的标签同时在主查询和 WHERE 子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。当然还有更多优化的 sql 语句的方法 ，我了解到了不同的查询方式，所需要的执行时间的差别，虽然现在只是几毫秒的差别的，但是对于计算机的执行速度来说，已经算大的了，而且依次长久下去，计算机的执行速度将越来越慢，由此可以看出查询优化的重要性。