数据查询分析实验.doc

1. 实验六 数据查询分析实验1.1 实验目的1. 通过对不同情况下查询语句的执行分析，巩固和加深对查询和查询优化相关理论知识的理解，提高优化数据库系统的实践能力；2. 熟悉了解Mysql中查询分析器的使用，并进一步提高编写复杂查询的SQL 程序的能力。1.2 实验内容1.2.1 索引对查询的影响（1） 对结果集只有一个元组的查询分三种情况进行执行（必如查询一个具体学生的信息）：不建立索引，（学号上）建立非聚集索引，（学号上）建立聚集索引。用查询分析器的执行步骤和结果对执行进行分析比较。（2） 对结果集中有多个元组的查询（例如查看某门成绩的成绩表）分类似（1）的三种情况进行执行比较。（3） 对查询条件为一个连续的范围的查询（例如查看学号在某个范围内的学生的选课情况）分类似（1）的三种情况进行执行比较，注意系统处理的选择。（4） 索引代价。在有索引和无索引的情况下插入数据（例如在选课情况表SC 上插入数据），比较插入的执行效率。1.2.2 对相同查询功能不同查询语句的执行比较分析（1） select avg(grade) from sc group by cno having cno =100;select avg(grade) from sc where cno = 100;有和没有group by，比较其查询效率，并分析。（2） select sno,sname from student s1 where sno =(select 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） 对下面两个查询进行比较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 语句重写外，还有其他手段来进行查询调优，例如调整缓冲区大小，事先建立视图等。设计实现下列查询，使之运行效率最高。写出你的查询形式，以及调优过程；并说明最优情况下的运行时间。（1）查找选修了每一门课的学生。（2）查找至少选修了课程数据库原理和操作系统的学生的学号。1.3 实验环境Window8操作系统Mysql 8.0版本数据库Mysql workbench 8.0可视化工具Mysql命令行编辑器1.4 实验步骤及结果分析索引对查询的影响1.4.1 单元组查询对结果集只有一个元组的查询分三种情况进行执行（必如查询一个具体学生的信息）：不建立索引，（学号上）建立非聚集索引，（学号上）建立聚集索引。用查询分析器的执行步骤和结果对执行进行分析比较。1. 没有建立索引的情况（查询sno=30203的学生）2. 建立非聚集索引的情况（查询sno=30203的学生）3. 建立聚集索引的情况（查询sno=30203的学生）4. 查询三种情况下的时间耗用但是发现，profiles没有记录信息，在网上查阅后进行了profiling权限开启查询，发现profiling未开启。 我们开启之后再进行时间查询：最终成功 5. 效率比较查询方法时间（ms）比较不建立索引0.3365较短非聚集索引0.4315最长聚集索引0.32125最短由表的数据可看出，在查询某个学生信息得过程中，聚集索引效率最高，无索引次之，非聚集索引最差。这样的结果产生的原因可能有：1. 电脑的不稳定性，2. 数据库大小不合适，3. 非聚集索引查询过程较复杂1.4.2 多元组查询对结果集中有多个元组的查询（例如查看某门成绩的成绩表）分类似1.4.1的三种情况进行执行比较。1. 无索引查询（查询课程号为c01的情况）2. 非聚集索引查询（查询课程号为c01的情况）3. 聚集索引查询（查询课程号为c01的情况）4. 耗用时间展示5. 结果统计及比较查询方法时间（ms）比较不建立索引0.382较短非聚集索引0.397最长聚集索引0.35575最短1.4.3 范围查询对查询条件为一个连续的范围的查询（例如查看学号在某个范围内的学生的选课情况）分类似1.4.1的三种情况进行执行比较，注意系统处理的选择。1. 无索引查询（查询学号在30201到30203之间的学生）2. 非聚集索引 （查询学号在30201到30203之间的学生）3. 聚集索引（查询学号在30201到30203之间的学生）4. 耗用时间展示5. 效率统计比较查询方法时间（ms）比较不建立索引0.4995最长非聚集索引0.46125较短聚集索引0.385最短由上述结果来看，在范围查询中，无索引查询效率最低；索引查询效率相对较高，这种比较在数据量大的时候更加明显，其中聚集索引较非聚集索引效率率更高。因为聚集索引将sno直接排序查找，速度较快；而非聚集索引查找块然后再进行细化数据查找，速度相对慢一些。1.4.4 索引代价索引代价。在有索引和无索引的情况下插入数据（例如在选课情况表SC 上插入数据），比较插入的执行效率。（为保证每次插入的位置和数据相同，我们每次插入都要进行删除重新插入，即控制变量保证一致）1. 无索引插入2. 非聚集索引3. 聚集索引4. 耗用时间展示5. 效率统计比较查询方法时间（ms）比较不建立索引67.031最短非聚集索引119.127最长聚集索引95.419较短由数据来看，建立索引的导致查询的效率提高，但同时也使得数据表的更新操作效率变低。因为对于索引来说，每次插入数据不仅要考虑数据插入的情况，还要考虑索引的变更。1.5 实验步骤及结果分析对相同查询功能不同查询语句的执行比较分析1.5.1 比较11. 命令功能：查找课程号cno=100的课程平均成绩2. Mysql语句1：select avg(grade) from sc group by cno having cno =100;3. Mysql语句2：select avg(grade) from sc where cno = 100;4. 两条语句执行结果：5. 两条语句用时展示6. 效率统计比较查询方法时间（ms）比较用group by0.74825长不用group by0.57475短由于group需要将我们不需要的内容进行分组，因此增加了是叫消耗，效率相对较低。但是对于大规模的分类处理来说，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 ,deptfrom student group by dept);select sno, sname from student,student1where student.sno = student1.maxsno and student1.dept=student.dept;4. 两条语句耗时展示：5. 效率统计比较查询方法时间（ms）比较不创表查询2.6405较短创表耗时432.95925长创表查询0.8195短由数据对比可得：创建表格查询相比不创表查询，速度有很大的提高，但是在创建表的过程中需要消耗大量的时间。因此，对以后的查询处理要进行合适的选择：对于查询次数较少的情况，我们可以直接查询，因为创表消耗太多。对于查询次数较多，需要较长时间使用的情况，我们可以创建表，在以后都会使用。1.5.3 比较31. 命令功能：查询所有非电信学院中，学号比所有电信学院学号都大的学生学号姓名。2. Mysql语句1：（使用all）select sno,sname from student where dept != 电信 and sno all(select sno from student where dept = 电信 );3. Mysql语句2：（使用max）select sname ,sno from student where dept != 电信 and sno ( select max(sno) from student where dept = 电信 );4. 两条语句耗时展示：5. 效率统计比较查询方法时间（ms）比较外部使用all0.42875短内部使用max0.696长由数据可得：在此次比较中，在外部使用的all耗时较少，因为我们在内部查询出所有sno之后只需要在外部进行all所有的比较即可。而是用max，需要首先在内部自行比较产生最大者，在让最大者和外部比较，增多了比较次数1.6 实验步骤及结果分析查询优化1.6.1 查找选修了每一门课的学生1. 直接用count查询2. 嵌套查询3. 创建表查询4. 耗用时间展示5. 统计比较查询方法时间（ms）比较count查询0.8825较短嵌套查询1.77525长表查询0.4525短1.6.2 查找至少选修了课程数据库原理和操作系统的学生的学号1. 思路1：使用嵌套查询，在选择数据库原理的基础上搜索选了操作系统的学生。2. 思路2：先将所有选了数据库原理或操作系统的人选出来，然后挑选出里面选课数为2的学生。这样就保证两门课都选了。3. 思路3：使用视图方法完成思路14. 思路4：使用视图方法完成思路25. 耗用时间展示不使用视图：使用视图：6. 效率总结比较查询方法时间（ms）比较思路11.06短思路20.5525最短思路3创建视图118.23725思路3查询11.6825最长思路4创建视图355.128思路4查询1.224425较长在不考虑电脑机器效率误差的情况下，创建视图使得查询效率相对较慢，不如直接查询快速。而采用嵌套一层一层查询比直接group查询更慢一些，因为嵌套会导致循环的增加。1.7 实验总结1.7.1 实验遇到的问题1. 本次实验遇到的第一个问题就是如何建立索引的问题，在之前实验的基础上开始做，发现设置了主键之后，数据库会默认以主键为搜索码建立聚集索引，而且无法删除索引也不好更改，最后只能删除主键来作为无索引、创建非聚集索引、添加主键 2. 实验遇到的第二个问题是在进行第二步实验的时候,原表格中没有age这一属性,因此实验中我将ege改为了sno属性进行查询3. 采用控制变量法验证各种优化方式的