1, Experimental time

June 1, 2021 - June 30, 2021

2, Experimental preparation

In the experiment, you need to buy an instance on Huawei cloud database, create a corresponding table, and import the corresponding data. The specific data is given in the teacher's Excel table. For the specific process of purchasing and building tables, please refer to this article by gydl next door

How to use Huawei cloud RDS to create database instances and import student data

Converting Excel tables to sql statements can be done using navicat

3, Experimental content

3.1 thoughts on index

Please think about whether the following SQL needs to be indexed and how to build indexes and how to build indexes. Give the execution time of SQL when no index is established and different indexes are established.

1. Query all course names and test scores of students with student number "200900140169".

When indexes are not used

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

Operation results:

Execution time

The execution time is 4ms

When using indexes

In student_ Build an index on the sno attribute of course

CREATE INDEX snoIndex on student_course(sno)

The re query still takes 4ms, so the index creation has little impact on this query

2. Query the name of "software college" students.

When indexes are not used

SELECT name
FROM student,department 
WHERE student.dno = department.dno and department.dname = 'Software College'

Operation results

Execution time

When using indexes

Index on dno of department

CREATE INDEX dnameIndex ON department(dname)

Re execution takes the same time, so creating an index has little impact on this query

3. Query the student number and elective course number of "software college".

Use index

Since the index has been built on the dname attribute in the previous question, there is no need to build it again

SELECT student_course.sno,cno
FROM student,student_course,department
WHERE student.sno = student_course.sno AND student.dno = department.dno AND department.dname = 'Software College'

Operation results

Execution time

When indexes are not used

Execution time after deleting the index created in the previous question

No significant difference was found

4. Query students' names, selected courses and test scores. (only students who choose courses)

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)

Query results

execution time

The execution time is short and there is no need to build an index

5. Query students' names, selected courses and test scores. (including all students)

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

Query results

Query time

It takes less time and does not need to build an index

6. Query the course name, average score, maximum score, minimum score and the number of elective students of each course (only limited to the courses selected by students).

Do not use index

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

results of enforcement

Execution time

It takes a long time to build an index

Use index

Since many data designs operate on score, it is considered in student_ Index on the score table of course

CREATE INDEX scoreIndex on student_course(score)

The time taken after re execution is

After creation, the execution time will not increase but decrease, and it will be considered in the student_ Index on course's cno

CREATE INDEX cnoIndex on student_course(cno)

Execute again. The execution time is as follows

The execution time is longer. It's strange

7. Query the course name, average score, maximum score, minimum score and the number of elective students of each course (including those without elective courses).

When indexes are not used

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

Time consuming

After index creation

The index is created on the score attribute, and the execution speed is improved a little

Then in the student_ The index is created on course's cno, and the execution time is

It takes longer?

8. Query the average class hours and average credits of students who choose courses.

When indexes are not used

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

The result is

When using indexes

In student_ After the index is created on the cno attribute of course, the execution time is

The query time decreased significantly

9. Query the average number of courses selected by all students (including students who have not selected courses).

The first step is to find the number of courses selected by all students
The second step is to find the average of the number of courses selected

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

result

Execution duration

Consider in student_ Create an index on the sno of course. After creation, the execution time is

The execution time has not changed significantly

3.2 experience of application comparison

Please write the comparison between Huawei RDS for MySQL database and Oracle database.

Because the Huawei cloud database uses MySQL database this time, it is different from the previous Oracle database in some syntax. For example, the as keyword must be used when naming aliases in MySQL database, but not in Oracle database.

Huawei RDS for MySQL database needs to manually create tables and import data. The data in the Oracle database used in our previous experiment has been written in advance.

In addition, Huawei RDS for MySQL database has a concept of "storage engine" when creating tables. This "storage engine" does not exist in Oracle database.

4, Summary

The experiment of Huawei database was done by the teacher contacting Huawei staff to send us vouchers, so that we can buy database instances. This experiment is not done in every class, and there is no test paper to verify this step, so there may be errors in the above sql statements that have not been found. In addition, in this experiment, the execution speed is slower after indexing.

In short, through this experiment, I have experienced buying database instances for the first time, creating tables in cloud database and completing corresponding experiments, and I still have some harvest

If there is any error, please point it out