山东大学软件学院数据库系统实验——基于华为RDS for MySQL数据库的实验

一、实验时间

2021年6月1日-2021年6月30日

二、实验准备

实验需要在华为云数据库上购买实例并创建对应的表，导入相应的数据，具体的数据由老师的Excel表格中给出。具体购买和建表的过程可以参考隔壁班gydl的这篇文章

如何用华为云RDS创建数据库实例并导入学生数据

将Excel表格转换为sql语句可以使用navicat完成

三、实验内容

3.1 关于索引的思考

请思考以下SQL 是否需要索引以及如何建立索引以及如何建立索引。给出不建立索引、建立不同索引时SQL 的执行时间。

1、 查询学号为“200900140169”的学生选修的所有课程名称和考试成绩。

不使用索引时

SELECT cname,score
FROM student_course , course 
WHERE student_course.cno = course.cno AND student_course.sno = '200900140169'

运行结果：

执行耗时

执行耗时为4ms

使用索引时

在student_course 的sno属性上建立一个索引

CREATE INDEX snoIndex on student_course(sno)

重新查询，耗时依然为4ms，因此对于这个查询，创建索引的影响不大

2、 查询“软件学院”学生的姓名。

不使用索引时

SELECT name
FROM student,department 
WHERE student.dno = department.dno and department.dname = '软件学院'

运行结果

执行耗时

使用索引时

在department的dno上建立索引

CREATE INDEX dnameIndex ON department(dname)

重新执行，耗时相同，因此对于这个查询，创建索引的影响不大

3、 查询“软件学院”的学生学号和选修课程的编号。

使用索引

由于上一题中已经在dname属性上建了索引，这里不需要再建一次

SELECT student_course.sno,cno
FROM student,student_course,department
WHERE student.sno = student_course.sno AND student.dno = department.dno AND department.dname = '软件学院'

运行结果

执行耗时

不使用索引时

将上一题建的索引删除后的执行时间

未见明显差异

4、 查询学生姓名、所选课程和考试成绩。（仅包含选课的学生）

SELECT name,cname,score
FROM student,student_course,course
WHERE student_course.cno = course.cno AND student.sno = student_course.sno AND student.sno IN (SELECT sno FROM student_course)

查询结果

执行时间

执行时间较短，不需要建索引

5、 查询学生姓名、所选课程和考试成绩。（包含所有的学生）

SELECT name,cname,score
FROM (select student.sno,student.name,student_course.cno,student_course.score from student LEFT OUTER JOIN student_course on student.sno = student_course.sno) AS a,course
WHERE a.cno = course.cno

查询结果

查询耗时

耗时较短，不需要建索引

6、 查询每门课(仅限于被学生选修的课程)的课程名称、平均成绩、最高成绩、最低成绩和选修学生人数。

不使用索引

SELECT cname,avg(score) as avg_score,max(score) as max_score,min(score) as min_score,count(sno) as number
from student_course,course 
WHERE student_course.cno = course.cno
GROUP BY student_course.cno,cname

执行结果

执行耗时

耗时较长，考虑建索引

使用索引

由于很多数据设计对score的操作，因此考虑在student_course的score表上建立索引

CREATE INDEX scoreIndex on student_course(score)

重新执行后耗时为

创建后执行时间不增反减，考虑再在student_course的cno上建立索引

CREATE INDEX cnoIndex on student_course(cno)

再次执行，执行耗时如下

执行时间更长了，好奇怪

7、 查询每门课(包括没有学生选修的课程)的课程名称、平均成绩、最高成绩、最低成绩和选修学生人数。

不使用索引时

SELECT cname,avg(score) as avg_score,max(score) as max_score,min(score) as min_score,count(sno) as number
from course LEFT OUTER JOIN student_course 
on course.cno = student_course.cno
GROUP BY course.cno,cname

耗时为

创建索引后

在score属性上创建索引，执行速度提升了一点

再在student_course的cno上创建索引，执行耗时为

耗时反而更长？

8、 查询选课学生的平均学时和平均学分。

不使用索引时

SELECT avg(hour) as avg_hour,avg(credit) as avg_credit
FROM course,student_course 
WHERE course.cno = student_course.cno

结果为

使用索引时

在student_course的cno属性上建索引后，执行时间为

查询时间明显下降

9、 查询全体学生的平均选课数量（包括未选课的学生）。

第一步，找到所有学生的选课数量
第二步，求这些选课数量的平均值

SELECT avg(t.lecture_number) as avg_lecture_number
FROM (SELECT count(cno) as lecture_number
	FROM student LEFT OUTER JOIN student_course
	on student.sno = student_course.sno 
	GROUP BY student.sno) as t

结果

执行时长

考虑在student_course的sno上创建索引，创建后，执行的时长为

执行时长没有明显变化

3.2 使用对比体会

请撰写华为RDS for MySQL 数据库与Oracle 数据库在使用方面的对比体会。

由于本次华为云数据库使用的是MySQL数据库，和之前的Oracle数据库在某些语法上有一定的区别。比如MySQL数据库中在命名别名时可以使用as关键字，但是Oracle数据库中不允许使用as。

华为RDS for MySQL数据库需要自己手动建表并导入数据，我们之前实验用的Oracle数据库中的数据已经提前写好了。

此外，华为RDS for MySQL数据库在建表时有一个”存储引擎“的概念，这个”存储引擎“在Oracle数据库中没有。

在执行时会发现，第一次执行一条新的sql语句时，耗时会略长，之后执行sql语句时，所用时长降低

四、总结

这次华为数据库的实验是老师联系华为的工作人员给我们发代金券做的，这样可以购买数据库实例。这个实验并不是每个班都做，并且没有交卷验证这一步，因此上面的sql语句可能有错误的地方没有被发现。此外，这次实验中还出现了建索引之后执行速度更慢的情况。

总之，通过这次试验，第一次体验了购买数据库实例，在云数据库中创建表，并完成相应的实验，还是有一定的收获的

如有错误，还望指出