《北邮 大三下 数据库实验六 mysql版本.docx》由会员分享,可在线阅读,更多相关《北邮 大三下 数据库实验六 mysql版本.docx(11页珍藏版)》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、实验报告学 院: 计算机学院 课程名称: 数据库系统 实验名称: 实验六 数据查询分析实验 班 级: 姓 名: schnee 学 号: 实验六 数据查询分析实验1. 实验目的通过对不同情况下查询语句的执行分析,巩固和加深对查询和查询优化相关理论知识的理解,提高优化数据库系统的实践能力,熟悉了解MySQL Server 5.5中查询分析器的使用,并进一步提高编写复杂查询的SQL 程序的能力。2. 实验环境操作系统:Microsoft Windows 7旗舰版 (32位)。软件:数据库版本:MySQL 5.5 3. 实验内容及过程3.1. 索引对查询的影响3.1的四个小题,由于MySQL没有聚集索
2、引与非聚集索引之分,所以无法验证,直接给出讨论结果。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、;的执行细节则为:3.1.1.2. 在学号上建立非聚集索引3.1.1.3. 在学号上建立聚集索引先解释下索引。索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。聚集索引又叫主索引,其索引的排序方式和正文的排序方式一致。每个表只能有一个聚集索引,因为目录只能按照一种方法进行排序。用聚合索引比用不是聚合索引的主键速度快;用聚合索引比
4、用一般的主键作order by时速度快,特别是在小数据量情况下。相对应的,非聚集索引也叫辅助索引。当数据量很小的时候,用聚集索引作为排序列要比使用非聚集索引速度快得明显的多;而数据量如果很大的话,如10万以上,则二者的速度差别不明显。此外,聚集索引插入数据时速度要慢(时间花费在“物理存储的排序”上,也就是首先要找到位置然后插入)。但是查询数据比非聚集数据的速度快。由于学号是student的主键,所以根据以上分析,我们可以知道,当只有一个查询结果时,这三种方法的快慢排序为:非聚集索引 快于聚集索引 快于 无索引。(附:同学在SQL Server下测试的执行时间结果为1ms : 2ms : 21m
5、s)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.的三种情况进行执行比较,注意系统处理的选择。对
6、于查询条件为一个连续的范围的查询的情况,非聚集索引与无索引差别不是很大,不过聚集索引明显会快很多。总体上, 还是聚集索引 快于 非聚集索引 快于 无索引。(附:同学在SQL Server下测试的执行时间结果为11ms : 26ms : 29ms)3.1.4. 索引代价。在有索引和无索引的情况下插入数据(例如在选课情况表SC 上插入数据),比较插入的执行效率。并非所有的情况索引都是会使速度变快的,比如我们往sc表里插入一条记录(001,C01,100),同学在SQL Server下测试的执行时间结果为无索引用时2ms , 但是有索引38ms!慢了非常多!3.2. 对相同查询功能不同查询语句的执行
7、比较分析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.s,而没有group by(即查询3)的用时为0.s,也就是在此例子里没有group by会稍快一点。我们查看具体执行情况,发现差别最大的是checking permissions 0.00036 : 0.00011。3.2.2. 比较以下两个查询,重写后的查询一定比原始
8、查询更优吗,通过执行分析结果。(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 maxBdate from studentgroup by dept);select sno, sname, bdatefrom student, tmpwhere student.bdate=tmp.maxBdate and tmp.dept=
9、student.dept;drop table tmp;如下面的截图所示,我们可以看到没有重写时用时为0.s。而重写用时为0.s,如果加上创建table用时0.和删除tmp用时0.,则总共用时为0.s。可见重写后,单纯的查询会比没重写的查询用时要少很多。甚至差了一个数量级。但是,如果没有对这个新的表进行多次相关的查询的话,那么建表时间会使得这个优化得不偿失。3.2.3. 比较以下两个语句的效率,并分析(1) select sname, bdate from studentwhere dept!=计算机 and bdateall(select bdate from student where d
10、ept=计算机);(2) select sname, bdate from studentwhere dept!=计算机 and bdate(select max(bdate) from student where dept=计算机);当查询结果为空集时运行时间比较为0. : 0.很明显的第二个语句要快于第一个语句。甚至差别两个数量级!现在我们插入一行使查询结果不为空,然后再次执行比较。这次的查询结果为执行时间比较如下图,可见这次的执行时间虽然还是第二个语句快于第一个语句,但是相差不大。3.3. 查询优化除了建立适当索引,对SQL 语句重写外,还有其他手段来进行查询调优,例如调整缓冲区大小,事
11、先建立视图等。设计实现下列查询,使之运行效率最高。写出你的查询形式,以及调优过程;并说明最优情况下的运行时间。下面先列出各种查询形式,最后再列表比较分析。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.gradefr
12、om 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);建表用时为:这
13、次的查询结果为:再次执行上面两种查询,这次的执行时间为:3.3.1.2. 嵌套子查询(1) 有查询结果且按课程号排序(2) 有查询结果且按学号排序(3) 查询结果为空集且按课程号排序(4) 查询结果为空集且按学号排序详细SQL语言见截图。具体执行时间也见截图。3.3.1.3. 列表比较,分析说明方法结果cnosno比较结果建表有结果0.0.sno快空集0.0.sno快嵌套有结果0.sno快空集0.0.sno快比较有结果建表快建表快空集嵌套快建表快从上表可以看出,在有查询结果时,事先建表的方法会比嵌套子查询的方法快很多,查询结果为空集时则不是很明显。而在几种情况下,按照sno排序都会比cno排序
14、快。无论查询结果为不为空,最快的方法都是:按照sno排序并事先建表查询。3.3.2. 查找至少选修了课程数据库原理和操作系统的学生的学号3.3.2.1. 事先创建table总用时0. 。3.3.2.2. 事先创建view总用时0.3.3.2.3. 清空缓存后事先创建table总用时0.3.3.2.4. 清空缓存后事先创建view总用时0.3.3.2.5. 列表分析比较tableview比较未清空缓存0.0.table快清空缓存0.0.view快比较清空缓存快清空缓存快table和view的差别不大,但是清空缓存会比不清空缓存快得多。注:在此,只是用重启mysql的方式清空缓存。用dbcc清空缓
15、存的方法虽然在SQL Server使用顺利,但是在mysql中却出错了。4. 实验小结实验中遇到的问题:实验中,由于MySQL本身的问题导致纠结了很久。比如MySQL不支持聚集索引和非聚集索引,导致第一题没法完成,而只能通过同学电脑上的SQL Server的运行结果来分析。再比如,第三题查询优化部分,有不少语句(比如复杂查询的with语句)MySQL也不支持,导致只能通过建立视图或是新table来实现,实验的方案不是那么丰富,没能从更多的方案来优化效率。实验心得: 虽然实验过程中也遇到了不少问题,但是最终都得到了一定的解决或是解释。并在实验中通过对不同情况下查询语句的执行分析,巩固和加深对查询和查询优化相关理论知识的理解,提高优化数据库系统的实践能力,熟悉了解MySQL Server 5.5中查看查询执行时间的方法,并进一步提高编写复杂查询的SQL 程序的能力。实验目的基本达到,实验过程虽然历时约一天,但是收获不少,还是很值的。