北邮大三下第6次数据库实验报告--mysql

北邮大三下计算机学院第6次数据库实验报告,基于mysql5.6,windows7系统

实验六 数据查询分析实验

一、实验内容

1、索引对查询的影响

1

（1） 对结果集只有一个元组的查询分三种情况进行执行（必如查询一个具体学

生的信息）： 不建立索引，（学号上）建立非聚集索引，（学号上）建立聚集索引。 用查询分析器的执行步骤和结果对执行进行分析比较。

（2） 对结果集中有多个元组的查询（例如查看某门成绩的成绩表）分类似（1）

的三种情况进行执行比较。

（3） 对查询条件为一个连续的范围的查询（例如查看学号在某个范围内的学生

的选课情况）分类似（1）的三种情况进行执行比较，注意系统处理的选择。

（4）索引代价。在有索引和无索引的情况下插入数据（例如在选课情况表SC 上

插入数据），比较插入的执行效率。

2 、对相同查询功能不同查询语句的执行比较分析 3 、查询优化

除了建立适当索引，对SQL 语句重写外，还有其他手段来进行查询调优，例如调整缓冲区大小，事先建立视图等。设计实现下列查询，使之运行效率最高。 写出你的查询形式，以及调优过程；并说明最优情况下的运行时间。 （1） 查找选修了每一门课的学生。

（2）查找至少选修了课程数据库原理和操作系统的学生的学号。

二、实验要求

(1) 用SQL语句完成以上操作 (2) 要求学生独立完成以上内容。

(3) 实验完成后完成要求的实验报告内容。

三、实验环境

系统：windows 7 软件：mysql5.6

四、实验步骤及结果分析

4.1. 索引对查询的影响

4.1.1 对结果集只有一个元组的查询分三种情况进行执行（必如查询一个具体

学生的信息）：

登录mysql后输入set profiling=1;

show profiles显示出来之前输入的命令的执行时间。 show profile for query n查询细节;

（1）不建立索引，在student表中查询学号为“31401”的学生：

北邮大三下计算机学院第6次数据库实验报告,基于mysql5.6,windows7系统

select * from student where sno=’31401’;的执行：

删除主索引：ALTER TABLE table_name DROP PRIMARY KEY

2

显示当前表格

student无索引：

执行命令：

（2

）（学号上）建立聚集索引并显示：

执行命令：

北邮大三下计算机学院第6次数据库实验报告,基于mysql5.6,windows7系统

（

3）建立非聚集索引（普通索引）：

3

显示索引：

执行时间：

（4）比较：无索引时执行select * from student where sno=’31401’;耗时0.00042125秒，建立聚集索引耗时：0.00092075秒，非聚集索引耗时0.00065700s，每次执行的时间都不太一样，所以结果集只有一个元组的查询三种情况下耗时差不多一样。

4．1.2 对结果集中有多个元组的查询分类似（1）的三种情况进行执行比较。

Select* from sc

where grade>=96

（1）无索引：

北邮大三下计算机学院第6次数据库实验报告,基于mysql5.6,windows7系统

（2

）聚集索引：

（

3）非聚集索引：

执行：

北邮大三下计算机学院第6次数据库实验报告,基于mysql5.6,windows7系统

（4）比较：无索引耗时：0.00145075秒，聚集索引耗时：0.00079200，非聚集索引耗时0.000874s，所以结果集中有多个元组的查询的情况下聚集索引速度快。聚集索引>非聚集索引 > 无索引。

4.1.3 对查询条件为一个连续的范围的查询（例如查看学号在某个范围内的学生的选课情况）分类似（1）的三种情况进行执行比较，注意系统处理的选择。 （

1）无索引：

（

2）聚集索引：

（

3）非聚集索引：

（4）比较：无索引时耗时：0.00077350s，聚集索引耗时0.00061975s，非聚集索引耗时0.00061975s，说明查询条件为一个连续的范围的查询的情况下聚集索引快, 聚集索引>非聚集索引 > 无索引。

4.1.4 索引代价。在有索引和无索引的情况下插入数据（例如在选课情况表SC

上插入数据），比较插入的执行效率。

insert into student values('33331','陈菊','女','1980-1-2 00:00','电信','3022') ;

insert into student values('33332','李元','女','1980-1-2 00:00','电信','3022'); insert into student values('33333','郭莉','女','1980-1-2 00:00','电信','3022'); insert into student values('33334','王倩为','女','1980-1-2 00:00','电信','3022'); insert into student values('33335','张园','男','1980-1-2 00:00','计算机','3146'); （1）无索引：

北邮大三下计算机学院第6次数据库实验报告,基于mysql5.6,windows7系统

（2

）聚集索引：

（3

）非聚集索引：

（4）比较：无索引耗时：0.2870995s，聚集索引耗时：0.259401s，非聚集索引耗

时：0.441546s，表明在插入数据的情况下，索引没有优化作用，并非所有的情况索引都是会使速度变快。

4.2对相同查询功能不同查询语句的执行比较分析

4.2.1 信息如下：

北邮大三下计算机学院第6次数据库实验报告,基于mysql5.6,windows7系统

分析：有group by耗时：0.00131325，无group by耗时：0.00067050，没有group by速度快。

4.2.2信息如下

如上图，分析可知，重写前耗时：0.00526375s，，重写后的查询耗时：0.00189025s.说么

重写后，单纯的查询会比没重写的查询用时要少很多。甚至差了一个数量级。

但是建立表格和删除表格比较耗时。

4.2.3 对下面两个查询进行比较

(1) select sname, bdate from student

where dept!=’计算机’ and bdate>all

(select bdate from student where dept=’计算机’);

(2) select sname, bdate from student

where dept!=’计算机’ and bdate>

(select max(bdate) from student where dept=’计算机

’);)

（3）分析：查询结果为空集，查询1耗时0.00128s，查询2耗时：0.00025900s，查询2明显快于查询1。

4.3 查询优化

除了建立适当索引，对SQL 语句重写外，还有其他手段来进行查询调优，例如调整缓冲区大小，事先建立视图等。设计实现下列查询，使之运行效率最高。 写出你的查询形式，以及调优过程；并说明最优情况下的运行时间。

4.3.1 查找选修了每一门课的学生。 (1)事先创建表

北邮大三下计算机学院第6次数据库实验报告,基于mysql5.6,windows7系统

create table tmp1 as( select sno from sc

group by sno

having count(*)=(select count(*) from course)) 查找：

select student.sname,tmp1.sno from student,tmp1

where student.sno=tmp1.sno

8

（2）事先建立视图

create view tmpv as( select sno from sc

group by sno

…… 此处隐藏：3657字，全部文档内容请下载后查看。喜欢就下载吧 ……