优化大表的SQL语句

Question

我有一个包含以下表的数据库：

Courses(course varchar(10));
Prerequisite(course varchar(10), prereq varchar(10));
StudentRecord(student varchar(10), course varchar(10), PRIMARY KEY (student, course));

课程包含数据库中的所有课程。必备条件包含给定课程的所有必备条件。StudentRecord拥有所有完成给定课程的学生。

我提出了以下查询，以根据学生已经完成的先决条件查找他/她可以选修的课程：

SELECT DISTINCT s.student, c.course from StudentRecord s, Courses c 
WHERE NOT EXISTS(SELECT * FROM Prerequisite p where p.course = c.course AND
                 p.prereq NOT IN(SELECT course from StudentRecord 
                 WHERE student = s.student)) 
                 AND NOT EXISTS(SELECT * FROM StudentRecord s2 WHERE 
                 s2.student = s.student AND s2.course = c.course);

这个查询做它应该做的事情，并根据完成的先决条件返回学生可以选修的课程的列表。然而，当"StudentRecord“是一个包含过多500+条目的大表时，我的查询就会卡住，并且永远不能运行。有没有运行得更快的替代查询？我可以优化我当前的查询来运行得更快吗？任何帮助都是非常感谢的。

Answer 1

如果您不知道要为查询创建哪些索引，一种方法是查看数据库认为最好的索引。

首先，创建所有可能的索引列组合：

CREATE INDEX c_c ON Courses(course);
CREATE INDEX p_c ON Prerequisite(course);
CREATE INDEX p_cp ON Prerequisite(course,prereq);
CREATE INDEX p_p ON Prerequisite(prereq);
CREATE INDEX p_pc ON Prerequisite(prereq,course);
CREATE INDEX sr_s ON StudentRecord(student);
CREATE INDEX sr_sc ON StudentRecord(student,course);
CREATE INDEX sr_c ON StudentRecord(course);
CREATE INDEX sr_cs ON StudentRecord(course,student);

然后查看查询的EXPLAIN QUERY PLAN输出：

> EXPLAIN QUERY PLAN SELECT DISTINCT s.student.....;
0|0|0|SCAN TABLE StudentRecord AS s
0|1|1|SCAN TABLE Courses AS c
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE Prerequisite AS p USING COVERING INDEX p_cp (course=?)
1|0|0|EXECUTE CORRELATED LIST SUBQUERY 2
2|0|0|SEARCH TABLE StudentRecord USING COVERING INDEX sr_sc (student=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SEARCH TABLE StudentRecord AS s2 USING COVERING INDEX sr_cs (course=? AND student=?)
0|0|0|USE TEMP B-TREE FOR DISTINCT

因此，您实际上只需要p_cp、sr_sc和sr_cs索引；删除所有其他索引。

(如果你知道你在做什么，你可以进一步优化。例如，看起来DISTINCT或sr_cs索引似乎都不是必需的。)