北邮数据库实验六数据查询分析实验精要

1、实验六 数据查询分析实验1、实验目的通过对不同情况下查询语句的执行分析，稳固和加深对查询和查询优化相关理论知识的理解，提高优化数据库系统的实践能力，熟悉了解kingbase中查询分析器的使用，并进一步提高编写复杂查询的SQL 程序的能力。2、实验环境操作系统：MicrosoftWindows7旗舰版32位。数据库版本：MySQL6.23、实验内容3.1 索引对查询的影响对结果集只有一个元组的查询分三种情况进行执行比方查询一个具体学生的信息：不建立索引学号上建立非聚集索引学号上建立聚集索引用查询分析器的执行步骤和结果对执行进行分析比拟。对结果集中有多个元组的查询例如查看某门成绩的成绩表分类似1的

2、三种情况进行执行比拟。对查询条件为一个连续的范围的查询例如查看学号在某个范围内的学生的选课情况分类似1的三种情况进行执行比拟，注意系统处理的选择。索引代价。在有索引和无索引的情况下插入数据例如在选课情况表SC 上插入数据，比拟插入的执行效率。3.2对相同查询功能不同查询语句的执行比拟分析1有和没有group by，比拟其查询效率，并分析。group byselect avg(grade)from scgroup by cnohaving cno =100select avg(grade)from scwhere cno = 1002重写后的查询一定比原始查询更优吗？通过执行分析结果。selec

3、t sno，sname，agefrom student s1where age =(select max(age)from student s2where s1.dept = s2.dept)select dept ，max(age) as maxAge into tmpfrom studentgroup by dept;select sno, sname , agefrom student，tmpwhere student.age = tmp.maxAge and tmp.dept=student.deptdrop table tmp;3对下面两个查询进行比拟select sname，age

4、from studentwhere dept != 10 and age all(select agefrom studentwhere dept = 10)select sname ,agefrom studentwhere dept != 10 and age (select max(age)from studentwhere dept = 10)3.3 查询优化除了建立适当索引，对SQL语句重写外，还有其他手段来进行查询调优，例如调整缓冲区大小，事先建立视图等。设计实现以下查询，使之运行效率最高。写出你的查询形式，以及调优过程；并说明最优情况下的运行时间。1查找选修了每一门课的学生。2查

5、找至少选修了课程数据库原理和操作系统的学生的学号。4、实验过程4.1 索引对查询的影响对结果集只有一个元组的查询分三种情况进行执行比方查询一个具体学生的信息：不建立索引在创立表是由于设置了primary key， mySQL自动生成主键索引即聚集索引，为了测试不建立索引的情况下查询学生信息的运行时间，首先将主键索引删除。查找学号为sno=31406的学生信息，结果为空集。查找语句的执行时间为：学号上建立非聚集索引同样先建立非聚集索引即普通索引，然后进行同样的查询操作。查找学号为sno=31406的学生信息，结果为空集。查找语句的执行时间为：学号上建立聚集索引重新设置该表的主键，会自动生成主键索

6、引，即聚集索引，然后进行同样的查询操作。查找学号为sno=31406的学生信息，结果为空集。查找语句的执行时间为：执行步骤及运行时间结果如下，首先删除student表的主键，在无索引的情况下查找学生信息；之后设立主键为sno学号，生成sno学号的聚集索引，查找学生信息；最后删除主键，建立index_sno非聚集索引，查找学生信息。三种查询语句的时间比照方下：查询方法时间比拟不建立索引0.08761150时间最长学号上建立非聚集索引0.00050150时间较短学号上建立聚集索引0.00038750时间最短对结果集中有多个元组的查询例如查看某门成绩的成绩表分类似1的三种情况进行执行比拟。不建立索引

7、学号上、课程号上建立非聚集索引学号上、课程号上建立聚集索引执行步骤及运行时间结果如下，首先删除sc表的主键，在无索引的情况下查找c01课程的成绩表；之后在课程号和学号上建立非聚集索引，查询c01课程的成绩表，并删除该索引；最后设立主键为cno课程号，sno学号，生成学号和课程号上的聚集索引，查找c01课程的成绩表。三种查询语句的时间比照方下：查询方法时间比拟不建立索引0.00167800时间最长建立非聚集索引0.00082550时间较短建立聚集索引0.00060400时间最短对查询条件为一个连续的范围的查询例如查看学号在某个范围内的学生的选课情况分类似1的三种情况进行执行比拟，注意系统处理的选

8、择。不建立索引建立非聚集索引建立聚集索引执行步骤及运行时间结果如下，首先删除sc表的主键，在无索引的情况下查找学号为30201-30210之间的学生选课信息；之后在课程号和学号上建立非聚集索引，查找学号为30201-30210之间的学生选课信息，并删除该索引；最后设立主键为cno课程号，sno学号，生成学号和课程号上的聚集索引，查找学号为30201-30210之间的学生选课信息。三种查询语句的时间比照方下：查询方法时间比拟不建立索引0.00090150时间最长建立非聚集索引0.00041675时间最短建立聚集索引0.00055325时间较短索引代价。在有索引和无索引的情况下插入数据例如在选课情

9、况表SC上插入数据，比拟插入的执行效率。不建立索引在学号，课程号上建立非聚集索引在学号，课程号上建立聚集索引执行步骤及运行时间结果如下，首先删除sc表的主键，在无索引的情况下插入一行学生成绩；之后在课程号和学号上建立非聚集索引，插入一行学生成绩，并删除该索引；最后设立主键为cno课程号，sno学号，生成学号和课程号上的聚集索引，插入一行学生成绩。三种查询语句的时间比照方下：查询方法时间比拟不建立索引0.09039100时间最长建立非聚集索引0.05449350时间较短建立聚集索引0.00133125时间最短4.2对相同查询功能不同查询语句的执行比拟分析4.2.1 有和没有group by，比拟

10、其查询效率，并分析。1select avg(grade)from scgroup by cnohaving cno =C012select avg(grade)from scwhere cno = C01两条语句的运行结果是相同的，但是时间相差一个数量级。如以下图。比拟两条语句的运行时间，有group by的语句总时间为0.00139200；没有group by的语句总时间为0.00067600；没有group by的语句运行时间明显要比另一条时间短。查询语句时间比拟有 group by0.00139200长没有 group by0.00067600短 重写后的查询一定比原始查询更优吗？通过执

11、行分析结果。(1) select sno,sname,bdatefrom student as s1where bdate =(select max(bdate)from student as s2where s1.dept = s2.dept)(2) create table tmp as (select dept,max(bdate) as maxBdatefrom student group by dept);select sno, sname , bdatefrom student,tmpwhere student.bdate = tmp.maxBdate and tmp.dept=s

12、tudent.dept;drop table tmp;两条语句的运行结果是相同的，如以下图：第一种查询方法只包含一条语句，运行时间为0.00292650s；第二种查询方法包含三条语句，运行时间分别为0.07077225s，0.00039900s，0.03445975s。其中，单纯的查询时间为0.00039900s。重写后，单纯的查询会比没重写的查询用时要少很多。甚至差了一个数量级。但是，重写后的查询需要建表和删除表，如果没有对这个新的表进行屡次相关的查询的话，那么建表时间会使得这个优化得不偿失。查询语句时间比拟直接查询0.00292650s长事先建表查询0.07077225s建表 单纯的查询时

13、间要比直接查询少一个数量级，但是建表和删表花费了更多的时间。0.00039900s查询0.03445975s删表 对下面两个查询进行比拟(1) select sname,bdatefrom 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=计算机);两个语句的运

14、行结果相同，且查询结果为空集。比拟两个语句的执行时间， 第二条语句的执行时间少于第一条语句。查询语句时间比拟直接比拟0.00090500长选择比拟0.00072375短4.3 查询优化除了建立适当索引，对SQL语句重写外，还有其他手段来进行查询调优，例如调整缓冲区大小，事先建立视图等。设计实现以下查询，使之运行效率最高。写出你的查询形式，以及调优过程；并说明最优情况下的运行时间。查找选修了每一门课的学生。1方法一：直接查询select sname from student where sno in(select sno from sc group by snohaving count(*) =

15、 (select count(*) from course);运行时间：2方法二：事先创立tablecreate table student1 as (select distinct sno as sno1,count(cno) as accountfrom scgroup by sno);select sname from student1,studentwhere student1.account=5 and student1.sno1=student.sno ;drop table student1;运行时间：3方法三：事先创立viewcreate view stu1 as(select

16、 distinct sno as sno1,count(cno) as accountfrom scgroup by sno);select sname from stu1, studentwhere stu1.account =5 and stu1.sno1=student.sno;drop view stu1;运行时间：4嵌套子查询select sname from studentwhere NOT exists (select * from coursewhere NOT exists (select * from SC whereSno = student.sno and cno =

17、Course o)运行时间：方法直接查询Create tableCreate view嵌套子查询时间0.001257000.104937250.009923500.00309200结论最短最长第三第二结论是，在建表，视图和嵌套子查询三种选择下，嵌套子查询的时间最少，但直接查询更加快捷。查找至少选修了课程数据库原理和操作系统的学生的学号。1方法一：事先创立tablecreate table tmp as(select sc.sno as sno,cnamefrom sc,coursewhere sc o=course o and (course ame=数据库原理 or course ame=操作系统);select snofrom tmp;drop table tmp;运行时间：2方法二：事先创立viewcreate view tmpv as (select sc.sno as sno