大数据查询分析报告实验.docx

1、大数据查询分析报告实验1. 实验六 数据查询分析实验1.1实验目的1.通过对不同情况下查询语句的执行分析，巩固和加深对查询和查询优化相关理论知识的理解，提高优化数据库系统的实践能力；2.熟悉了解Mysql中查询分析器的使用，并进一步提高编写复杂查询的SQL 程序的能力。1.2实验内容1.2.1索引对查询的影响（1）对结果集只有一个元组的查询分三种情况进展执行必如查询一个具体学生的信息：不建立索引，学号上建立非聚集索引，学号上建立聚集索引。用查询分析器的执行步骤和结果对执行进展分析比拟。（2）对结果集中有多个元组的查询例如查看某门成绩的成绩表分类似1的三种情况进展执行比拟。（3）对查询条件为一个

2、连续的X围的查询例如查看学号在某个X围内的学生的选课情况分类似1的三种情况进展执行比拟，注意系统处理的选择。（4）索引代价。在有索引和无索引的情况下插入数据例如在选课情况表SC 上插入数据，比拟插入的执行效率。1.2.2对一样查询功能不同查询语句的执行比拟分析（1）select avg(grade) from sc group by o having o =100;select avg(grade) from sc where o = 100;有和没有group by，比拟其查询效率，并分析。2 select sno,sname from student s1 where sno =(sele

3、ct max(sno) from student s2 where s1.dept = s2.dept );另一个：create table student1 as(select max(sno) as maxsno ,deptfrom student group by dept);select sno, sname from student,student1where student.sno = student1.maxsno and student1.dept=student.dept;drop table student1;重写后的查询一定比原始查询更优吗？通过执行分析结果。3 对下面两

4、个查询进展比拟select sno,sname from student where dept != 电信 and sno all(select sno from student where dept = 电信 );另：select sname ,sno from student where dept != 电信 and sno ( select max(sno) from student where dept = 电信 );1.2.3查询优化除了建立适当索引，对SQL 语句重写外，还有其他手段来进展查询调优，例如调整缓冲区大小，事先建立视图等。设计实现如下查询，使之运行效率最高。写出你的查询

5、形式，以与调优过程；并说明最优情况下的运行时间。1查找选修了每一门课的学生。2查找至少选修了课程数据库原理和操作系统的学生的学号。1.3实验环境Window8操作系统Mysql数据库MysqlMysql命令行编辑器1.4实验步骤与结果分析索引对查询的影响1.4.1单元组查询对结果集只有一个元组的查询分三种情况进展执行必如查询一个具体学生的信息：不建立索引，学号上建立非聚集索引，学号上建立聚集索引。用查询分析器的执行步骤和结果对执行进展分析比拟。1.没有建立索引的情况查询sno=30203的学生2.建立非聚集索引的情况查询sno=30203的学生3.建立聚集索引的情况查询sno=30203的学生

6、4.查询三种情况下的时间耗用但是发现，profiles没有记录信息，在网上查阅后进展了profiling权限开启查询，发现profiling未开启。 我们开启之后再进展时间查询：最终成功 5.效率比拟查询方法时间ms比拟不建立索引较短非聚集索引最长聚集索引最短由表的数据可看出，在查询某个学生信息得过程中，聚集索引效率最高，无索引次之，非聚集索引最差。这样的结果产生的原因可能有：1. 电脑的不稳定性，2. 数据库大小不适宜，3. 非聚集索引查询过程较复杂1.4.2多元组查询对结果集中有多个元组的查询例如查看某门成绩的成绩表分类似1.4.1的三种情况进展执行比拟。1.无索引查询查询课程号为c01的

7、情况2.非聚集索引查询查询课程号为c01的情况3.聚集索引查询查询课程号为c01的情况4.耗用时间展示5.结果统计与比拟查询方法时间ms比拟不建立索引较短非聚集索引最长聚集索引最短1.4.3X围查询对查询条件为一个连续的X围的查询例如查看学号在某个X围内的学生的选课情况分类似1.4.1的三种情况进展执行比拟，注意系统处理的选择。1.无索引查询查询学号在30201到30203之间的学生2.非聚集索引 查询学号在30201到30203之间的学生3.聚集索引查询学号在30201到30203之间的学生4.耗用时间展示5.效率统计比拟查询方法时间ms比拟不建立索引最长非聚集索引较短聚集索引最短由上述结果

8、来看，在X围查询中，无索引查询效率最低；索引查询效率相对较高，这种比拟在数据量大的时候更加明显，其中聚集索引较非聚集索引效率率更高。因为聚集索引将sno直接排序查找，速度较快；而非聚集索引查找块然后再进展细化数据查找，速度相对慢一些。1.4.4索引代价索引代价。在有索引和无索引的情况下插入数据例如在选课情况表SC 上插入数据，比拟插入的执行效率。为保证每次插入的位置和数据一样，我们每次插入都要进展删除重新插入，即控制变量保证一致1.无索引插入2.非聚集索引3.聚集索引4.耗用时间展示5.效率统计比拟查询方法时间ms比拟不建立索引最短非聚集索引最长聚集索引较短由数据来看，建立索引的导致查询的效率

9、提高，但同时也使得数据表的更新操作效率变低。因为对于索引来说，每次插入数据不仅要考虑数据插入的情况，还要考虑索引的变更。1.5实验步骤与结果分析对一样查询功能不同查询语句的执行比拟分析1.5.1比拟11.命令功能：查找课程号o=100的课程平均成绩2.Mysql语句1：select avg(grade) from sc group by o having o =100;3.Mysql语句2：select avg(grade) from sc where o = 100;4.两条语句执行结果：5.两条语句用时展示6.效率统计比拟查询方法时间ms比拟用group by长不用group by短由于g

10、roup需要将我们不需要的内容进展分组，因此增加了是叫消耗，效率相对较低。但是对于大规模的分类处理来说，group by更加直观方便。1.5.2比拟21.命令功能：查询各个dept中学号sno最大的学生的学号某某2.Mysql语句1：直接比拟查询select sno,sname from student s1 where sno = (select max(sno) from student s2 where s1.dept = s2.dept );3.Mysql语句2：先创建表格，再查询create table student1 as(select max(sno) as maxsno ,d

11、eptfrom student group by dept);select sno, sname from student,student1where student.sno = student1.maxsno and student1.dept=student.dept;4.两条语句耗时展示：5.效率统计比拟查询方法时间ms比拟不创表查询较短创表耗时长创表查询短由数据比照可得：创建表格查询相比不创表查询，速度有很大的提高，但是在创建表的过程中需要消耗大量的时间。因此，对以后的查询处理要进展适宜的选择：对于查询次数较少的情况，我们可以直接查询，因为创表消耗太多。对于查询次数较多，需要较长时间使

12、用的情况，我们可以创建表，在以后都会使用。1.5.3比拟31.命令功能：查询所有非电信学院中，学号比所有电信学院学号都大的学生学号某某。2.Mysql语句1：使用allselect sno,sname from student where dept != 电信 and sno all(select sno from student where dept = 电信 );3.Mysql语句2：使用maxselect sname ,sno from student where dept != 电信 and sno ( select max(sno) from student where dept =

13、 电信 );4.两条语句耗时展示：5.效率统计比拟查询方法时间ms比拟外部使用all短内部使用max长由数据可得：在此次比拟中，在外部使用的all耗时较少，因为我们在内部查询出所有sno之后只需要在外部进展all所有的比拟即可。而是用max，需要首先在内部自行比拟产生最大者，在让最大者和外部比拟，增多了比拟次数1.6实验步骤与结果分析查询优化1.6.1查找选修了每一门课的学生1.直接用count查询2.嵌套查询3.创建表查询4.耗用时间展示5.统计比拟查询方法时间ms比拟count查询较短嵌套查询长表查询短1.6.2查找至少选修了课程数据库原理和操作系统的学生的学号1.思路1：使用嵌套查询，在

14、选择数据库原理的根底上搜索选了操作系统的学生。2.思路2：先将所有选了数据库原理或操作系统的人选出来，然后挑选出里面选课数为2的学生。这样就保证两门课都选了。3.思路3：使用视图方法完成思路14.思路4：使用视图方法完成思路25.耗用时间展示不使用视图：使用视图：6.效率总结比拟查询方法时间ms比拟思路1短思路2最短思路3创建视图思路3查询最长思路4创建视图思路4查询较长在不考虑电脑机器效率误差的情况下，创建视图使得查询效率相对较慢，不如直接查询快速。而采用嵌套一层一层查询比直接group查询更慢一些，因为嵌套会导致循环的增加。1.7实验总结1.7.1实验遇到的问题1.本次实验遇到的第一个问题

15、就是如何建立索引的问题，在之前实验的根底上开始做，发现设置了主键之后，数据库会默认以主键为搜索码建立聚集索引，而且无法删除索引也不好更改，最后只能删除主键来作为无索引、创建非聚集索引、添加主键 2.实验遇到的第二个问题是在进展第二步实验的时候,原表格中没有age这一属性,因此实验中我将ege改为了sno属性进展查询3.采用控制变量法验证各种优化方式的有效性，这样得出的实验结果更有说服力。不过实验过程中，有些一样的命令我执行屡次，每次执行的时间都不一样，有时候相差很大，这可能是由于我的电脑的问题，不过这导致实验结果应该会有很大的bug。1.7.2收获经过本次实验，我学会了非聚集索引、聚集索引的创建；在索引方面让我对理论知识得到了更深的理解；同时，在嵌套方面我也有了深入的提高，充分了解查询的细节过程。尤其是对exists的分析，让我受益匪浅；最重要的是我们经过查询的效率分析，对查询优化有了更深深的印象。也让我对实验的控制变量的使用更加到位。