数据库 优化查询 实验报告

【实验目的】

1. 了解数据库查询优化方法和查询计划的概念。

2. 学会分析查询的代价。

【实验内容及步骤】

针对单表查询、连接查询、嵌套查询这三种SQL操作，查看查询分析器给出的查询计划，分析优化效果。

1．    单表查询（针对GSM数据库）

针对表BTS，在BTS经度上建立非簇集索引（必须使用Create index语句），进行下列查询：

（1）    查询BTS经度位于121.0335和121.142595之间的BTS基本信息。

select BTS.*

from BTS

where LONGITUDE between 121.0335 and 121.142595

（2）    对海拔查询一个范围内的所有记录（例如大于30，小于60）。

SQL语句为：

select BTS.*

from BTS

where ALTITUDE between 30 and 60

（3）    对BTS经度进行大范围查询（就是结果集包括几乎所有记录）。

select BTS.*

from BTS

where LONGITUDE between 121.0335 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 BTS

values('JIANHANG1',42217,121.137365,41.112287,45,'Datang',5);

create index index2 on BTS(LONGITUDE);

（1） 查询BTS经度位于121.0335和121.142595之间的BTS基本信息。

SQL语句为：

select BTS.*

from BTS

where LONGITUDE between 121.0335 and 121.142595

（2） 对海拔查询一个范围内的所有记录（例如大于30，小于60）

SQL语句为：

select BTS.*

from BTS

where ALTITUDE between 30 and 60

（3） 对BTS经度进行大范围查询（就是结果集包括几乎所有记录）

SQL语句为：

select BTS.*

from BTS

where LONGITUDE between 121.0335 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.0335和121.142595之间的BTS基本信息。

SQL语句为：

select BTS.*

from BTS

where LONGITUDE between 121.0335 and 121.142595

（2） 对海拔查询一个范围内的所有记录（例如大于30，小于60）。

SQL语句为：

select BTS.*

from BTS

where ALTITUDE between 30 and 60

（3） 对BTS经度进行大范围查询（就是结果集包括几乎所有记录）。

SQL语句为：

select BTS.*

from BTS

where LONGITUDE between 121.0335 and 121.185335

b．    撤销在BTS经度上建立的任何索引（必须使用Drop index语句），重复上面的三个查询，比较在有非簇集索引、簇集索引和无索引的情况下，查询效率的不同。

SQL语句：

drop index index1

结论：三种情况相比，无索引效率最低，有非簇集索引会加快查询效率，簇集索引效率最高。

（3）    索引代价：

在有一般索引、簇集索引和无索引的情况下插入、删除、更新数据，通过执行计划比较每个操作的执行效率。

注意不要只对一条记录操作，应该插入、删除、更新一批（比如200条索引键值比较集中的记录）记录，这样才能测出真实的效率。

插入数据，SQL语句如下：

insert into BTS

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.0335,41.120217,50,'Huawei',5),

('KAIFAQU5',42216,121.0335,41.120217,50,'Huawei',5),

('KAIFAQU6',42216,121.0335,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.1349,41.129033,50,'Datang',5),

('YIZHUAN6',42217,121.1349,41.129033,35,'Datang',5),

('YIZHUAN7',42217,121.1349,41.129033,35,'Datang',5),

('YIZHUAN8',42217,121.1349,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.1494,41.127283,40,'Siemens',5),

('BIANJINGHOTEL5',42218,121.1494,41.127283,25,'Siemens',5),

('BIANJINGHOTEL6',42218,121.1494,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.1234,39,'Siemens',5),

('SHUILIJU5',42218,121.108283,41.1234,39,'Siemens',5),

('SHUILIJU6',42218,121.108283,41.1234,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.1234,30,'Huawei',5),

('HUAYUANXIAOQU5',42221,121.174305,41.1234,50,'Huawei',5),

('HUAYUANXIAOQU6',42221,121.174305,41.1234,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.1467,42,'Huawei',5),

('RENHETUN5',42221,121.171785,41.1467,35,'Huawei',5),

('RENHETUN6',42221,121.171785,41.1467,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.csv" OF DEL METHOD P (1, 2, 3, 4, 5, 6, 7) MESSAGES "1" INSERT INTO BTS (BTSNAME, BSCID, 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 BTS

where LONGITUDE between 121.0335 and 121.185335

（1）在一般索引的情况下，执行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 BTS

set LONGITUDE=121.0362

where LONGITUDE=121.0335

（1）在一般索引的情况下，执行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;

【实验总结】

通过这次实验，一个列的标签同时在主查询和WHERE子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。当然还有更多优化的sql语句的方法 ， 我了解到了不同的查询方式，所需要的执行时间的差别，虽然现在只是几毫秒的差别的，但是对于计算机的执行速度来说，已经算大的了，而且依次长久下去，计算机的执行速度将越来越慢，由此可以看出查询优化的重要性。