数据查询分析优化实验

北京邮电大学

实验报告

课程名称数据库系统原理

实验名称数据查询分析优化实验

计算机学院网络工程11班

薛玥

指导教师吴起凡

成绩

2014-5-20

目录

实验目的 (2)

实验环境 (2)

实验内容 (2)

实验步骤 (2)

实验问题及感想 (40)

遇到的问题 (40)

感想 (41)

实验目的

1．熟悉了解SQL SERVER数据库中查询优化的使用，理解数据库查询优化的基本概念。2．结合文档“数据库物理设计及查询优化-v1-110320.doc”，通过对不同情况下查询语句的执行情况的对比分析，巩固加深查询优化的理解，并进行书写优化SQL语句的初步训练，提高编写高效SQL语句进行数据查询的能力。

实验环境

众所周知，SQL查询需要进行优化，好的优化甚至可能提高效率几个数量级。SQL SERVER在执行查询时分为两个步骤：第一步是编译查询，生成查询计划，第二步执行该计划。编译查询分为分析、代数化和优化三个阶段，完成编译后系统将把计划保存在缓存中，以后执行该查询时可直接调用，而省略重新编译过程。然后执行引擎将计划复制为可执行形式并执行之。采用SQL SERVER数据库管理系统作为实验平台，可以采用SQL SERVER 2005、2008或2012，并使用其各种版本。

实验内容

实验中要进行表中记录数多少、结果集大小、有无索引、不同书写方式的等效SQL、多表连接查询等情况进行查询计划分析，并比较各种查询计划的效率优劣。

实验步骤

一、查询执行计划观察

从“实验四数据查询与修改实验”中，选取涉及多表查询的select查询语句，执行该语句，利用Microsoft SQL Server Management Studio(Express)，就可以观察该语句的查询执行计划，分析查询执行计划包含的各项基本关系代数操作和查询代价。

二、索引对查询、插入、删除、更新的影响

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

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

首先在longitude上面建立索引。如下图所示。

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

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

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

Q:分析三种情况下的查询计划有何不同？

A:由于是在BTS的Longitude上建立的非聚集索引，在1、3产讯现在所印象中查找符合要求的值，再找到BTS中找到其他需要的列。而由于没有还把上的索引，2直接扫描BTS表的所有项得到结果。

（1）表中记录数多少的影响：

如果BTS表中只有一条记录，重复上面的三个查询。

向表中插入一条记录：

①查询BTS经度位于121.089335和121.142595之间的BTS基本信息。（类比的做）

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

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

（2）不同索引类型对查询的影响

a．在BTS经度上建立簇集索引，重复上面的三个查询。

①.查询BTS经度位于121.089335和121.142595之间的BTS基本信息。（类比的做）

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

select BTS.*

from BTS

where altitude between30and60

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

通过上面的图片和前面的图片进行比较发现，执行效率没有不同。原因是建立簇集索引不立即导致表中记录重新排列的缘故。

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

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

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

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

三中情况相比，没有建立索引的情况效率最低，有索引能够提高查询效率。聚集索引的效率最高。

（3）索引代价：

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

①无索引

②普通索引

③聚集索引

2．多表查询

针对表小区话务数据、在(CellID、rate、callcongs)上建立非簇集索引，进行下列查询：

（1）查询小区ID位于9132和9543之间的每个小区，半速率话务量比例的最大值、呼叫拥塞率的最大值及其管理小区的BTS名称。

针对表小区话务数据、在(CellID、rate、callcongs、thtraff)上建立非簇集索引，进行下列查询：（首先建立索引。）

查询小区ID位于9132和9543之间的每个小区，半速率话务量比例的最大值、呼叫拥塞率的最大值、全速率话务量的最小值及其管辖小区的BSCID，MSCID。

（1）不同索引类型对查询的影响

a．在(CellID、rate、callcongs)上建立簇集索引，重复查询（1）。

b．撤销在(CellID、rate、callcongs)上建立的任何索引，重复查询（1），比较在有非c．

b.撤销在(CellID、rate、callcongs)上建立的任何索引，重复查询（1），比较在有非簇集索引、簇集索引和无索引的情况下，查询效率的不同。