大数据查询分析报告实验.docx
《大数据查询分析报告实验.docx》由会员分享，可在线阅读，更多相关《大数据查询分析报告实验.docx（17页珍藏版）》请在冰豆网上搜索。
大数据查询分析报告实验
1.实验六数据查询分析实验
1.1实验目的
1.通过对不同情况下查询语句的执行分析，巩固和加深对查询和查询优化相关理论知识的理解，提高优化数据库系统的实践能力；
2.熟悉了解Mysql中查询分析器的使用，并进一步提高编写复杂查询的SQL程序的能力。
1.2实验内容
1.2.1索引对查询的影响
（1）对结果集只有一个元组的查询分三种情况进展执行〔必如查询一个具体学生的信息〕：
不建立索引，〔学号上〕建立非聚集索引，〔学号上〕建立聚集索引。
用查询分析器的执行步骤和结果对执行进展分析比拟。
（2）对结果集中有多个元组的查询〔例如查看某门成绩的成绩表〕分类似〔1〕的三种情况进展执行比拟。
（3）对查询条件为一个连续的X围的查询〔例如查看学号在某个X围内的学生的选课情况〕分类似〔1〕的三种情况进展执行比拟，注意系统处理的选择。
（4）索引代价。
在有索引和无索引的情况下插入数据〔例如在选课情况表SC上插入数据〕，比拟插入的执行效率。
1.2.2对一样查询功能不同查询语句的执行比拟分析
（1）selectavg（grade）fromsc
groupbyohavingo=100;
selectavg（grade）fromscwhereo=100;
有和没有groupby，比拟其查询效率，并分析。
〔2〕selectsno,snamefromstudents1wheresno=
（selectmax（sno）fromstudents2wheres1.dept=s2.dept）;
另一个：
createtablestudent1as（
selectmax（sno）asmaxsno,dept
fromstudentgroupbydept）;
selectsno,snamefromstudent,student1
wherestudent.sno=student1.maxsnoandstudent1.dept=student.dept;
droptablestudent1;
重写后的查询一定比原始查询更优吗？
通过执行分析结果。
〔3〕对下面两个查询进展比拟
selectsno,snamefromstudentwheredept!
='电信'andsno>all
（selectsnofromstudentwheredept='电信'）;
另：
selectsname,snofromstudentwheredept!
='电信'andsno>
（selectmax（sno）fromstudentwheredept='电信'）;
1.2.3查询优化
除了建立适当索引，对SQL语句重写外，还有其他手段来进展查询调优，例如调整缓冲区大小，事先建立视图等。
设计实现如下查询，使之运行效率最高。
写出你的查询形式，以与调优过程；并说明最优情况下的运行时间。
〔1〕查找选修了每一门课的学生。
〔2〕查找至少选修了课程数据库原理和操作系统的学生的学号。
1.3实验环境
Window8操作系统
Mysql数据库
Mysql
Mysql命令行编辑器
1.4实验步骤与结果分析——索引对查询的影响
1.4.1单元组查询
对结果集只有一个元组的查询分三种情况进展执行〔必如查询一个具体学生的信息〕：
不建立索引，〔学号上〕建立非聚集索引，〔学号上〕建立聚集索引。
用查询分析器的执行步骤和结果对执行进展分析比拟。
1.没有建立索引的情况〔查询sno=30203的学生〕
2.建立非聚集索引的情况〔查询sno=30203的学生〕
3.建立聚集索引的情况〔查询sno=30203的学生〕
4.查询三种情况下的时间耗用
但是发现，profiles没有记录信息，在网上查阅后进展了profiling权限开启查询，发现profiling未开启。
我们开启之后再进展时间查询：
最终成功
5.效率比拟
查询方法
时间〔ms〕
比拟
不建立索引
较短
非聚集索引
最长
聚集索引
最短
由表的数据可看出，在查询某个学生信息得过程中，聚集索引效率最高，无索引次之，非聚集索引最差。
这样的结果产生的原因可能有：
1.电脑的不稳定性，2.数据库大小不适宜，3.非聚集索引查询过程较复杂
1.4.2多元组查询
对结果集中有多个元组的查询〔例如查看某门成绩的成绩表〕分类似1.4.1的三种情况进展执行比拟。
1.无索引查询〔查询课程号为c01的情况〕
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〕
比拟
不建立索引
最长
非聚集索引
较短
聚集索引
最短
由上述结果来看，在X围查询中，无索引查询效率最低；索引查询效率相对较高，这种比拟在数据量大的时候更加明显，其中聚集索引较非聚集索引效率率更高。
因为聚集索引将sno直接排序查找，速度较快；而非聚集索引查找块然后再进展细化数据查找，速度相对慢一些。
1.4.4索引代价
索引代价。
在有索引和无索引的情况下插入数据〔例如在选课情况表SC上插入数据〕，比拟插入的执行效率。
〔为保证每次插入的位置和数据一样，我们每次插入都要进展删除重新插入，即控制变量保证一致〕
1.无索引插入
2.非聚集索引
3.聚集索引
4.耗用时间展示
5.效率统计比拟
查询方法
时间〔ms〕
比拟
不建立索引
最短
非聚集索引
最长
聚集索引
较短
由数据来看，建立索引的导致查询的效率提高，但同时也使得数据表的更新操作效率变低。
因为对于索引来说，每次插入数据不仅要考虑数据插入的情况，还要考虑索引的变更。
1.5实验步骤与结果分析——对一样查询功能不同查询语句的执行比拟分析
1.5.1比拟1
1.命令功能：
查找课程号o=100的课程平均成绩
2.Mysql语句1：
selectavg（grade）fromscgroupbyohavingo=100;
3.Mysql语句2：
selectavg（grade）fromscwhereo=100;
4.两条语句执行结果：
5.两条语句用时展示
6.效率统计比拟
查询方法
时间〔ms〕
比拟
用groupby
长
不用groupby
短
由于group需要将我们不需要的内容进展分组，因此增加了是叫消耗，效率相对较低。
但是对于大规模的分类处理来说，groupby更加直观方便。
1.5.2比拟2
1.命令功能：
查询各个dept中学号sno最大的学生的学号某某
2.Mysql语句1：
〔直接比拟查询〕
selectsno,snamefromstudents1wheresno=
（selectmax（sno）fromstudents2wheres1.dept=s2.dept）;
3.Mysql语句2：
〔先创建表格，再查询〕
createtablestudent1as（selectmax（sno）asmaxsno,deptfromstudentgroupbydept）;
selectsno,snamefromstudent,student1
wherestudent.sno=student1.maxsnoandstudent1.dept=student.dept;
4.两条语句耗时展示：
5.效率统计比拟
查询方法
时间〔ms〕
比拟
不创表查询
较短
创表耗时
长
创表查询
短
由数据比照可得：
创建表格查询相比不创表查询，速度有很大的提高，但是在创建表的过程中需要消耗大量的时间。
因此，对以后的查询处理要进展适宜的选择：
对于查询次数较少的情况，我们可以直接查询，因为创表消耗太多。
对于查询次数较多，需要较长时间使用的情况，我们可以创建表，在以后都会使用。
1.5.3比拟3
1.命令功能：
查询所有非电信学院中，学号比所有电信学院学号都大的学生学号某某。
2.Mysql语句1：
〔使用all〕
selectsno,snamefromstudentwheredept!
='电信'andsno>all
（selectsnofromstudentwheredept='电信'）;
3.Mysql语句2：
〔使用max〕
selectsname,snofromstudentwheredept!
='电信'andsno>
（selectmax（sno）fromstudentwheredept='电信'）;
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：
使用嵌套查询，在选择数据库原理的根底上搜索选了操作系统的学生。
2.思路2：
先将所有选了数据库原理或操作系统的人选出来，然后挑选出里面选课数为2的学生。
这样就保证两门课都选了。
3.思路3：
使用视图方法完成思路1
4.思路4：
使用视图方法完成思路2
5.耗用时间展示
不使用视图：
使用视图：
6.效率总结比拟
查询方法
时间〔ms〕
比拟
思路1
短
思路2
最短
思路3创建视图
思路3查询
最长
思路4创建视图
思路4查询
较长
在不考虑电脑机器效率误差的情况下，创建视图使得查询效率相对较慢，不如直接查询快速。
而采用嵌套一层一层查询比直接group查询更慢一些，因为嵌套会导致循环的增加。
1.7实验总结
1.7.1实验遇到的问题
1.本次实验遇到的第一个问题就是如何建立索引的问题，在之前实验的根底上开始做，发现设置了主键之后，数据库会默认以主键为搜索码建立聚集索引，而且无法删除索引也不好更改，最后只能删除主键来作为无索引、创建非聚集索引、添加主键
2.实验遇到的第二个问题是在进展第二步实验的时候,原表格中没有age这一属性,因此实验中我将ege改为了sno属性进展查询
3.采用控制变量法验证各种优化方式的有效性，这样得出的实验结果更有说服力。
不过实验过程中，有些一样的命令我执行屡次，每次执行的时间都不一样，有时候相差很大，这可能是由于我的电脑的问题，不过这导致实验结果应该会有很大的bug。
1.7.2收获
经过本次实验，我学会了非聚集索引、聚集索引的创建；在索引方面让我对理论知识得到了更深的理解；
同时，在嵌套方面我也有了深入的提高，充分了解查询的细节过程。
尤其是对exists的分析，让我受益匪浅；
最重要的是我们经过查询的效率分析，对查询优化有了更深深的印象。
也让我对实验的控制变量的使用更加到位。
升级会员 