mysql - 查找 SQL 查询瓶颈

Question

我正在学习 SQL。为了提高我的技能，我在 Coderloop.com 上发现了一个 SQL 难题。 (编程难题的好网站，顺便说一句)。我目前正在解决这个难题:

http://www.coderloop.com/puzzles/university

我非常接近解决方案。我只需要优化我的代码。该难题要求您设计一个类(class)目录数据库，以及诸如“将学生添加到类(class)”或“获取类(class)详细信息”之类的查询。我认为，我有一个可行的解决方案。我用自己的数据测试过，似乎表现不错。但是 Coderloop 使用数以千计的查询来对我的数据库模式和查询进行压力测试。不幸的是，由于大约 4 分钟后超时，我的解决方案失败了。我检查了我的架构和查询，但找不到瓶颈。关于如何重新设计我的数据库或查询以加快速度的任何想法？

注意事项:

• 在 MySQL 服务器上执行
• ?s 是 Coderloop 机器人传递的测试数据的参数。请参阅此 page 的底部.

我的灵魂

设置数据库

CREATE TABLE students (
    sid int NOT NULL,
    name varchar(255),
    surname varchar(255),
    email varchar(255),
    faculty varchar(255),
    matriculation int,
    PRIMARY KEY (sid)
    );

CREATE INDEX stu_index ON students (sid);   
CREATE TABLE professors (
    pid int NOT NULL,
    name varchar(255),
    surname varchar(255),
    email varchar(255),
    faculty varchar(255),
    telephone varchar(255),
    PRIMARY KEY (pid)
);
CREATE INDEX pro_index ON professors (pid); 
CREATE TABLE courses (
    course_name varchar(255) NOT NULL,
    pid int,
    credits int,
    subject varchar(255),
    PRIMARY KEY (course_name)
);
CREATE INDEX cou_index ON courses (course_name);    
CREATE TABLE enrollment (
    course_name varchar(255),
    sid int
);

添加一位新教授

INSERT INTO professors (name, surname, email, faculty, telephone, pid)  VALUES (?, ?, ?, ?, ?, ?);

添加新学生

INSERT INTO students (name, surname, email, faculty, matriculation, sid)  VALUES (?, ?, ?, ?, ?, ?);

添加新类(class)

INSERT INTO courses (course_name, subject, credits)  VALUES (?, ?, ?);

将现有教授添加到现有类(class)

(这种疯狂的语法是为了匹配传递给 ?s 的参数的顺序)

Update courses Set pid = Case When course_name = ? Then ? Else pid End;

将现有学生添加到现有类(class)

INSERT INTO enrollment (course_name, sid) VALUES (?, ?);

获取参加类(class)的学生列表

SELECT name, surname FROM students JOIN enrollment ON students.sid=enrollment.sid WHERE course_name=?;

让教授拥有一门类(class)

SELECT name, surname, professors.pid FROM professors JOIN courses ON professors.pid=courses.pid WHERE course_name=?;

获取类(class)详情

SELECT subject, credits FROM courses WHERE course_name=?;

获取教授详细信息

SELECT name, surname, email, faculty FROM professors WHERE pid=?;

获取学生详细信息

SELECT name, surname, email, faculty, matriculation FROM students WHERE sid=?;

从类(class)中删除学生

DELETE FROM enrollment WHERE sid=? AND course_name=?;

更换类(class)中的教授

UPDATE courses SET pid=? WHERE course_name=?;

Answer 1

在主键和外键字段上添加索引。这会有很大帮助。

此外，请确保您在 WHERE 和 ORDER BY 子句中涉及的字段上有索引(除了 FK 字段)

最终:您可能需要将范围缩小到执行缓慢的特定查询，以便您可以将精力集中在那里。