1、实验六 数据查询分析实验1、实验目的通过对不同情况下查询语句的执行分析，巩固和加深对查询和查询优化相关理论知识的理解，提高优化数据库系统的实践能力，熟悉了解kingbase中查询分析器的使用，并进一步提高编写复杂查询的SQL 程序的能力。2、实验环境操作系统：Microsoft Windows 7 旗舰版 （32 位） 。 数据库版本：MySQL 6.23、实验内容3.1 索引对查询的影响（1） 对结果集只有一个元组的查询分三种情况进行执行（比如查询一个具体学生的信息）：a) 不建立索引b) （学号上）建立非聚集索引c) （学号上）建立聚集索引用查询分析器的执行步骤和结果对执行进行分析比较。（
2、2） 对结果集中有多个元组的查询（例如查看某门成绩的成绩表）分类似（1）的三种情况进行执行比较。（3） 对查询条件为一个连续的范围的查询（例如查看学号在某个范围内的学生的选课情况）分类似（1）的三种情况进行执行比较，注意系统处理的选择。（4） 索引代价。在有索引和无索引的情况下插入数据（例如在选课情况表SC 上插入数据），比较插入的执行效率。3.2 对相同查询功能不同查询语句的执行比较分析（1）有和没有group by，比较其查询效率，并分析。group byselect avg(grade)from scgroup by cnohaving cno =100select avg(grade)
3、from scwhere cno = 100（2）重写后的查询一定比原始查询更优吗？通过执行分析结果。select 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
4、=student.deptdrop table tmp;（3）对下面两个查询进行比较select sname，agefrom 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 语句重写外，还有其他手段来进行查询调优，例如调整缓冲区大小，事先建立视图等。设计实现
5、下列查询，使之运行效率最高。写出你的查询形式，以及调优过程；并说明最优情况下的运行时间。（1）查找选修了每一门课的学生。（2）查找至少选修了课程数据库原理和操作系统的学生的学号。4、实验过程4.1 索引对查询的影响4.1.1对结果集只有一个元组的查询分三种情况进行执行（比如查询一个具体学生的信息）：a) 不建立索引在创建表是由于设置了primary key， mySQL自动生成主键索引即聚集索引，为了测试不建立索引的情况下查询学生信息的运行时间，首先将主键索引删除。查找学号为sno=31406的学生信息，结果为空集。查找语句的执行时间为：b) （学号上）建立非聚集索引同样先建立非聚集索引即普通
6、索引，然后进行同样的查询操作。查找学号为sno=31406的学生信息，结果为空集。查找语句的执行时间为：c) （学号上）建立聚集索引重新设置该表的主键，会自动生成主键索引，即聚集索引，然后进行同样的查询操作。查找学号为sno=31406的学生信息，结果为空集。查找语句的执行时间为：执行步骤及运行时间结果如下，首先删除student表的主键，在无索引的情况下查找学生信息；之后设立主键为sno（学号），生成sno（学号）的聚集索引，查找学生信息；最后删除主键，建立index_sno非聚集索引，查找学生信息。三种查询语句的时间对比如下：查询方法 时间 比较不建立索引 0.08761150 时间最长（
7、学号上）建立非聚集索引 0.00050150 时间较短（学号上）建立聚集索引 0.00038750 时间最短4.1.2对结果集中有多个元组的查询（例如查看某门成绩的成绩表）分类似（1）的三种情况进行执行比较。a) 不建立索引b) （学号上、课程号上）建立非聚集索引c) （学号上、课程号上）建立聚集索引执行步骤及运行时间结果如下，首先删除sc表的主键，在无索引的情况下查找c01课程的成绩表；之后在课程号和学号上建立非聚集索引，查询c01课程的成绩表，并删除该索引；最后设立主键为cno（课程号），sno（学号），生成（学号和课程号上的）聚集索引，查找c01课程的成绩表。三种查询语句的时间对比如下：
8、查询方法 时间 比较不建立索引 0.00167800 时间最长建立非聚集索引 0.00082550 时间较短建立聚集索引 0.00060400 时间最短4.1.3对查询条件为一个连续的范围的查询（例如查看学号在某个范围内的学生的选课情况）分类似（1）的三种情况进行执行比较，注意系统处理的选择。a) 不建立索引b) 建立非聚集索引c) 建立聚集索引执行步骤及运行时间结果如下，首先删除sc表的主键，在无索引的情况下查找学号为30201-30210之间的学生选课信息；之后在课程号和学号上建立非聚集索引，查找学号为30201-30210之间的学生选课信息，并删除该索引；最后设立主键为cno（课程号），
9、sno（学号），生成（学号和课程号上的）聚集索引，查找学号为30201-30210之间的学生选课信息。三种查询语句的时间对比如下：查询方法 时间 比较不建立索引 0.00090150 时间最长建立非聚集索引 0.00041675 时间最短建立聚集索引 0.00055325 时间较短4.1.4索引代价。在有索引和无索引的情况下插入数据（例如在选课情况表SC上插入数据），比较插入的执行效率。a) 不建立索引b) （在学号，课程号上）建立非聚集索引c) （在学号，课程号上）建立聚集索引执行步骤及运行时间结果如下，首先删除sc表的主键，在无索引的情况下插入一行学生成绩；之后在课程号和学号上建立非聚集索
10、引，插入一行学生成绩，并删除该索引；最后设立主键为cno（课程号），sno（学号），生成（学号和课程号上的）聚集索引，插入一行学生成绩。三种查询语句的时间对比如下：查询方法 时间 比较不建立索引 0.09039100 时间最长建立非聚集索引 0.05449350 时间较短建立聚集索引 0.00133125 时间最短4.2 对相同查询功能不同查询语句的执行比较分析4.2.1 有和没有group by，比较其查询效率，并分析。（1 ） select avg(grade)from scgroup by cnohaving cno =C01（2 ） select avg(grade)from scwh
11、ere cno = C01两条语句的运行结果是相同的，但是时间相差一个数量级。如下图。比较两条语句的运行时间，有group by的语句总时间为0.00139200；没有group by的语句总时间为0.00067600；没有group by的语句运行时间明显要比另一条时间短。查询语句 时间 比较有 group by 0.00139200 长没有 group by 0.00067600 短4.2.2 重写后的查询一定比原始查询更优吗？通过执行分析结果。(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=student.dept;drop table tmp;两条语句的运行结果是相同的，如下图：
