SQL数据库查询代价问题

Question

教学管理数据库包含了学院表College（CollegeID，CollegeName）和教师表Teacher（TeacherID，TeacherName，CollegeID）。学院表College中已有20个学院信息，教师表Teacher中已有4000名教师信息。假定需要查询输出“软件学院”的教师名单，其查询SQL语句如下：

SELECT  A.CollegeName,  B.TeacherID,  B.TeacherName
FROM  College  AS  A,  Teacher  AS  B
WHERE  A.CollegeID=B.CollegeID AND A.CollegeName=’软件学院’

在执行处理该查询语句时，可以采用如下3种等价的关系运算表达式来解析该查询语句。

Q1=ΠA.CollegeName,B.TeacherID,B.TeacherName(σA.CollegeID=B.CollegeID L A.CollegeName=’软件学院’(College×Teacher))
Q2=ΠA.CollegeName,B.TeacherID,B.TeacherName(σA.CollegeName=’软件学院’(College∞Teacher))
Q3=ΠA.CollegeName,B.TeacherID,B.TeacherName(Teacher∞(σA.CollegeName=’软件学院’(College)))

1）研究分析Q1、Q2、Q3三种查询方案的执行代价，请选出一个最优查询方案。
2）在以上最优查询方案基础上，对College表和Teacher表的主外键列创建了索引，并且CollegeName列也创建了索引，其查询代价又如何？

Answer 1

针对以上三种查询方案的执行代价，可以进行以下分析：

Q1：首先需要对两个表中的数据进行 JOIN 操作，由于表的大小都不是很大，因此可以使用 Nested Loop 连接算法。如果在 College 表的 CollegeName 列上创建索引，则可以加快筛选的速度。因此，可以认为 Q1 是一种较优的查询方案。
Q2：首先对 College 表进行筛选，然后通过 CollegeID 列进行 JOIN 操作，这一步操作可以使用 Index Scan 算法，然后再对 TeacherName 列进行筛选。但是这种查询方案会多进行一次表的访问操作，不如 Q1 方案直接对两个表进行 JOIN 操作来得优秀。
Q3：首先对 Teacher 表进行筛选，然后通过 CollegeID 列与 College 表进行 JOIN 操作，最后再对 CollegeName 列进行筛选。这种查询方案与 Q1 方案类似，但是先进行一次表的筛选操作，因此比 Q1 方案稍微劣一些。
因此，可以认为 Q1 是最优查询方案。

当对 College 和 Teacher 表的主外键列创建索引并且对 CollegeName 列也创建索引后，查询代价会进一步降低。具体的查询代价取决于索引的实现和数据分布情况，但是可以预期，这些索引的加入可以提高查询效率，特别是在 CollegeName 列上创建索引，因为它是查询条件中的一个关键字。