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

《北邮 大三下 数据库实验六 mysql版本.docx》由会员分享，可在线阅读，更多相关《北邮 大三下 数据库实验六 mysql版本.docx（11页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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 程序的能力。实验目的基本达到，实验过程虽然历时约一天，但是收获不少，还是很值的。