北邮 大三下 数据库实验六 mysql版本

实验报告学院：计算机学院课程名称：数据库系统实验名称：实验六数据查询分析实验班级：2009211311姓名：schnee学号：实验六数据查询分析实验1.实验目的通过对不同情况下查询语句的执行分析，巩固和加深对查询和查询优化相关理论知识的理解，提高优化数据库系统的实践能力，熟悉了解MySQL Server 5.5中查询分析器的使用，并进一步提高编写复杂查询的SQL 程序的能力。

2.实验环境操作系统：Microsoft Windows 7旗舰版（32位）。

软件：数据库版本：MySQL 5.53.实验内容及过程3.1.索引对查询的影响3.1的四个小题，由于MySQL没有聚集索引与非聚集索引之分，所以无法验证，直接给出讨论结果。

3.1.1.对结果集只有一个元组的查询分三种情况进行执行（比如查询一个具体学生的信息），用查询分析器的执行步骤和结果对执行进行分析比较。

3.1.1.1.不建立索引，在student表中查询学号为“31427”的学生登录mysql后输入set profiling=1;然后执行语句，则show profiles就显示出来之前输入的命令的执行时间。

如果查询细节的话则show profile for query 2; 2即语句的顺序。

详见截图，则执行时间约为60毫秒：详细的select * from student where sno=’31427’;的执行细节则为：3.1.1.2.在学号上建立非聚集索引3.1.1.3.在学号上建立聚集索引先解释下索引。

索引用来快速地寻找那些具有特定值的记录，所有MySQL 索引都以B-树的形式保存。

如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。

表里面的记录数量越多，这个操作的代价就越高。

如果作为搜索条件的列上已经创建了索引，MySQL 无需扫描任何记录即可迅速得到目标记录所在的位置。

聚集索引又叫主索引，其索引的排序方式和正文的排序方式一致。

每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。

用聚合索引比用不是聚合索引的主键速度快；用聚合索引比用一般的主键作order by时速度快，特别是在小数据量情况下。

相对应的，非聚集索引也叫辅助索引。

当数据量很小的时候，用聚集索引作为排序列要比使用非聚集索引速度快得明显的多；而数据量如果很大的话，如10万以上，则二者的速度差别不明显。

此外，聚集索引插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入）。

但是查询数据比非聚集数据的速度快。

由于学号是student的主键，所以根据以上分析，我们可以知道，当只有一个查询结果时，这三种方法的快慢排序为：非聚集索引快于聚集索引快于无索引。

（附：同学在SQL Server下测试的执行时间结果为1ms : 2ms : 21ms）3.1.2.对结果集中有多个元组的查询（例如查看某门成绩的成绩表，在此，以查看sc表的“C01”成绩为例）分类似3.1.1的三种情况进行执行比较。

对于有多个元组的查询情况，差别不如3.1.1那么明显。

三种情况差别都不大。

不过，还是聚集索引快于非聚集索引快于无索引。

（附：同学在SQL Server下测试的执行时间结果为26ms : 29ms : 32ms）3.1.3.对查询条件为一个连续的范围的查询（例如查看学号在某个范围内的学生的选课情况，在此以查询sc表中学号在’31404’和’31420’之间的学生的学号和课程号为例）分类似3.1.1.的三种情况进行执行比较，注意系统处理的选择。

对于查询条件为一个连续的范围的查询的情况，非聚集索引与无索引差别不是很大，不过聚集索引明显会快很多。

总体上，还是聚集索引快于非聚集索引快于无索引。

（附：同学在SQL Server下测试的执行时间结果为11ms : 26ms : 29ms）3.1.4.索引代价。

在有索引和无索引的情况下插入数据（例如在选课情况表SC上插入数据），比较插入的执行效率。

并非所有的情况索引都是会使速度变快的，比如我们往sc表里插入一条记录(‘001’,’C01’,’100’)，同学在SQL Server下测试的执行时间结果为无索引用时2ms ，但是有索引38ms！！！慢了非常多！3.2.对相同查询功能不同查询语句的执行比较分析3.2.1.比较有和没有group by的查询效率并分析。

(1)select avg(grade)from scgroup by cnohaving cno=’C01’;(2)select avg(grade)from scwhere cno=’C01’;运行时间如下图，我们看到有group by的用时（即查询2）为0.00014225s，而没有group by（即查询3）的用时为0.00012725s，也就是在此例子里没有group by会稍快一点。

我们查看具体执行情况，发现差别最大的是checking permissions 0.00036 : 0.00011。

3.2.2.比较以下两个查询，重写后的查询一定比原始查询更优吗，通过执行分析结果。

(1)select sno, sname, bdatefrom student s1where bdate=(select max(bdate)from student s2where s1.dept=s2.dept);(2)create table tmp as(select dept, max(bdate) as maxBdatefrom studentgroup by dept);select sno, sname, bdatefrom student, tmpwhere student.bdate=tmp.maxBdate and tmp.dept=student.dept;drop table tmp;如下面的截图所示，我们可以看到没有重写时用时为0.02123850s。

而重写用时为0.00326500s，如果加上创建table用时0.19003250和删除tmp 用时0.08236775，则总共用时为0.27566525s。

可见重写后，单纯的查询会比没重写的查询用时要少很多。

甚至差了一个数量级。

但是，如果没有对这个新的表进行多次相关的查询的话，那么建表时间会使得这个优化得不偿失。

3.2.3.比较以下两个语句的效率，并分析(1)select sname, bdate from studentwhere dept!=’计算机’ and bdate>all(select bdate from student where dept=’计算机’);(2)select sname, bdate from studentwhere dept!=’计算机’ and bdate>(select max(bdate) from student where dept=’计算机’);当查询结果为空集时运行时间比较为0.06770325 : 0.00056675很明显的第二个语句要快于第一个语句。

甚至差别两个数量级！！现在我们插入一行使查询结果不为空，然后再次执行比较。

这次的查询结果为执行时间比较如下图，可见这次的执行时间虽然还是第二个语句快于第一个语句，但是相差不大。

3.3.查询优化除了建立适当索引，对SQL 语句重写外，还有其他手段来进行查询调优，例如调整缓冲区大小，事先建立视图等。

设计实现下列查询，使之运行效率最高。

写出你的查询形式，以及调优过程；并说明最优情况下的运行时间。

下面先列出各种查询形式，最后再列表比较分析。

3.3.1.查找选修了每一门课的学生，其成绩等于该课程的平均分。

先按照课程升序排序，再按照学号升序排序。

3.3.1.1.事先建立table的方法(1)事先建立表格create table tmp as(select avg(grade) as Avggrade, cno from scgroup by cno);这样得出的平均分是浮点数，查询时结果为空集。

(2)按照课程号排序查询select sc.sno, o, sc.gradefrom sc, tmpwhere sc.grade=tmp.Avggrade and o=oorder by cno;(3)按照学号排序查询select sc.sno, o, sc.gradefrom sc, tmpwhere sc.grade=tmp.Avggrade and o=oorder by sno;(4)建立四舍五入的平均分表的情况由于上面的查询结果是空集，所以我们重新建一个tmp，看看结果非空集的情况。

create table tmp as(select round(avg(grade)) as Avggrade, cno from scgroup by cno);建表用时为：这次的查询结果为：再次执行上面两种查询，这次的执行时间为：3.3.1.2.嵌套子查询(1)有查询结果且按课程号排序(2)有查询结果且按学号排序(3)查询结果为空集且按课程号排序(4)查询结果为空集且按学号排序详细SQL语言见截图。

具体执行时间也见截图。

3.3.1.3.列表比较，分析说明从上表可以看出，在有查询结果时，事先建表的方法会比嵌套子查询的方法快很多，查询结果为空集时则不是很明显。

而在几种情况下，按照sno排序都会比cno排序快。

无论查询结果为不为空，最快的方法都是：按照sno排序并事先建表查询。

3.3.2.查找至少选修了课程数据库原理和操作系统的学生的学号3.3.2.1.事先创建table总用时0.17416900 。

3.3.2.2.事先创建view总用时0.2110865009211311班3.3.2.3.清空缓存后事先创建table总用时0.087030003.3.2.4.清空缓存后事先创建view总用时0.07798475table和view的差别不大，但是清空缓存会比不清空缓存快得多。

注：在此，只是用重启mysql的方式清空缓存。

用dbcc清空缓存的方法虽然在SQL Server使用顺利，但是在mysql中却出错了。

4.实验小结实验中遇到的问题：实验中，由于MySQL本身的问题导致纠结了很久。

比如MySQL不支持聚集索引和非聚集索引，导致第一题没法完成，而只能通过同学电脑上的SQL Server的运行结果来分析。

再比如，第三题查询优化部分，有不少语句（比如复杂查询的with语句）MySQL也不支持，导致只能通过建立视图或是新table来实现，实验的方案不是那么丰富，没能从更多的方案来优化效率。

实验心得：虽然实验过程中也遇到了不少问题，但是最终都得到了一定的解决或是解释。