北邮计算机学院数据库实验报告,北邮大三下第6次数据库实验报告--mysql.doc

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

11Catherine2015实验六 数据查询分析实验一、实验内容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 profiling1;show profiles显示出来之前输入的命令的执行时间。show profile for query n查询细节;(1)不建立索引，在student表中查询学号为“31401”的学生select * from student where sno31401;的执行删除主索引ALTER TABLE table_name DROP PRIMARY KEY 显示当前表格student无索引执行命令(2)(学号上)建立聚集索引并显示执行命令(3)建立非聚集索引(普通索引)显示索引执行时间(4)比较无索引时执行select * from student where sno31401;耗时0.00042125秒，建立聚集索引耗时0.00092075秒，非聚集索引耗时0.00065700s，每次执行的时间都不太一样，所以结果集只有一个元组的查询三种情况下耗时差不多一样。41.2 对结果集中有多个元组的查询分类似(1)的三种情况进行执行比较。Select* from sc where grade96(1)无索引(2)聚集索引 (3)非聚集索引执行(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 values33331,陈菊,女,1980-1-2 0000,电信,3022 ;insert into student values33332,李元,女,1980-1-2 0000,电信,3022;insert into student values33333,郭莉,女,1980-1-2 0000,电信,3022;insert into student values33334,王倩为,女,1980-1-2 0000,电信,3022;insert into student values33335,张园,男,1980-1-2 0000,计算机,3146;(1)无索引(2)聚集索引(3)非聚集索引(4)比较无索引耗时0.2870995s，聚集索引耗时0.259401s，非聚集索引耗时0.441546s，表明在插入数据的情况下，索引没有优化作用，并非所有的情况索引都是会使速度变快。4.2对相同查询功能不同查询语句的执行比较分析4.2.1 信息如下分析有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 studentwhere dept计算机 and bdateallselect bdate from student where dept计算机;2 select sname, bdate from studentwhere dept计算机 and bdateselect maxbdate from student where dept计算机;(3)分析查询结果为空集，查询1耗时0.00128s，查询2耗时0.00025900s，查询2明显快于查询1。4.3 查询优化除了建立适当索引，对SQL 语句重写外，还有其他手段来进行查询调优，例如调整缓冲区大小，事先建立视图等。设计实现下列查询，使之运行效率最高。写出你的查询形式，以及调优过程；并说明最优情况下的运行时间。4.3.1 查找选修了每一门课的学生。1事先创建表create table tmp1 asselect snofrom sc group by snohaving count*select count* from course查找select student.sname,tmp1.snofrom student,tmp1where student.snotmp1.sno(2)事先建立视图create view tmpv asselect snofrom sc group by snohaving count*select count* from course查找select student.sname,tmpv.snofrom student,tmpvwhere student.snotmpv.sno(3)嵌套子查询select sname from student where not exists select * from course where not exists select * from sc where cno o and snostudent.sno(4)比较事先创建表查询耗时0.25515625s0.00073275s0.255889s，事先建立视图查询耗时0.10380425s0.01565350s0.11945775s，嵌套子查询耗时0.00363275s，表明建表后的查询时间是最快的，但是建表耗时太多，得不偿失。而建立视图这种方式的视图建立和查询都耗时较多。4.3.2查找至少选修了课程数据库原理和操作系统的学生的学号。1事先创建tablecreate table tmp asselect sc.sno as sno,cnamefrom sc,coursewhere o and ame数据库原理 or ame操作系统select snofrom tmp group by snohaving countcname2(2)事先创建viewcreate view tmp1 asselect sc.sno as sno,cnamefrom sc,coursewhere o and ame数据库原理 or ame操作系统select snofrom tmp1 group by snohaving countcname2(3)清空缓存后事先创建table(clear)(4)清空缓存后事先创建view(5)比较，事先创建table耗时0.48625750s0.001169400s0.4874269s，清空缓存后创建table耗时0.37801800s0.00114500s0.379163s，事先创建view耗时0.08131075s0.0020655s0.083373，清空缓存后创建view耗时0.07034775s0.00175s0.07209775s.利用clear清空缓存。由执行时间可以看出，清空缓存比不清空缓存快，而且事先创建视图查询比事先创建表格查询快。五、实验总结mysql支持聚集索引和非聚集索引。聚集索引是主索引，在设置逐渐的时候就已经默认设置为主索引；非聚集索引就是mysql的普通索引。采用控制变量法验证各种优化方式的有效性，这样得出的实验结果更有说服力。不过实验过程中，有些相同的命令我执行多次，每次执行的时间都不一样，有时候相差很大，这可能是由于我的电脑的问题，不过这导致实验结果应该会有很大的bug。所以本来想以多次实验取平均值这种方法来增强实验的准确性，额，不过数据太难处理我就放弃了。不过总体来说，我的实验结果和理论差不多，我也就放心了。当然这些实验在命令行和查询分析器里面都可以做，命令行可以的查询分析器也可以，不过查询分析器里面也要设置profiling的值哦。比如看来还是我对mysql了解不够，随着实验的进行，真的觉得sql越来越强大了。每次的实验总结都在瞎说，呵呵哒