mysql - 使用 UNION ALL 进行慢速查询

Question

我有一个查询，它给了我想要的结果，但需要很长时间才能执行。谁能给我任何建议来加速这个查询？

这是查询:

SELECT StudentDB.studentid,
    ClassDB.classID,
    ClassDB.class_level,
    ClassDB.class_title,
    ClassDB.TIME,
    ClassDB.teacher,
    StudentDB.first_name,
    StudentDB.last_name
FROM StudentDB
INNER JOIN AttendanceDB
    ON StudentDB.studentid = AttendanceDB.studentid
INNER JOIN ClassDB
    ON AttendanceDB.classid = ClassDB.classID
WHERE StudentDB.studentid NOT IN (
        SELECT studentID
        FROM AttendanceDB
        WHERE (
                class_time >= '$todaysdate1'
                AND class_time < '$tomorrowsdate1'
                )
            AND (
                furikae = '0'
                OR furikae = '2'
                OR furikae = '1'
                )
        )
    AND DATE (AttendanceDB.class_time) = '$datecheck'
    AND AttendanceDB.furikae = '3'

UNION ALL

SELECT StudentDB.studentid,
    ClassDB.classID,
    ClassDB.class_level,
    ClassDB.class_title,
    ClassDB.TIME,
    ClassDB.teacher,
    StudentDB.first_name,
    StudentDB.last_name
FROM StudentDB
INNER JOIN RegDB
    ON StudentDB.studentID = RegDB.studentid
INNER JOIN ClassDB
    ON ClassDB.classID = RegDB.classid
WHERE StudentDB.studentid NOT IN (
        SELECT studentID
        FROM AttendanceDB
        WHERE (
                class_time >= '$todaysdate1'
                AND class_time < '$tomorrowsdate1'
                )
            AND (
                furikae = '0'
                OR furikae = '2'
                OR furikae = '1'
                )
        )
    AND ClassDB.day = '$dayofweek'
ORDER BY TIME ASC,
    class_title ASC

基本上我的数据库结构是这样的:

StudentDB
ClassDB
RegDB
AttendanceDB

StudentDB(这个有很多列，其中包含学生电话号码等不相关的数据，我将删除以缩写它)

studentid   int(11)         No  None    AUTO_INCREMENT  
first_name  text    latin1_swedish_ci       No  None    
last_name   text    latin1_swedish_ci       No  None        
class_level text    latin1_swedish_ci       No  None    

类数据库

classID int(11)         No  None    AUTO_INCREMENT  
class_title text    latin1_swedish_ci       No  None
class_level text    latin1_swedish_ci       No  None    
day text    latin1_swedish_ci       No  None        
time    time            No  None        
teacher text    latin1_swedish_ci       No  None    

RegDB

regid   int(11)         No  None    AUTO_INCREMENT  
classid int(11)         No  None    
studentid   int(11)         No  None    

考勤数据库

attendanceID    int(11)         No  None    AUTO_INCREMENT
studentid   int(11)         No  None        
classid int(11)         No  None        
class_time  timestamp       on update CURRENT_TIMESTAMP No  CURRENT_TIMESTAMP   ON UPDATE CURRENT_TIMESTAMP     
furikae int(11)         No  None    

RegDB将StudentDB链接到ClassDB，哪个学生参加哪个类(class)。AttentionDB 记录哪个学生实际参加哪个类(class)，以及记录哪些学生(将来)将加入非常规类(class)的类(class)。

我想要通过此 SELECT 执行的操作是获取所有已注册类(class)或有不定期预约的学生，减去已标记为当天已登录的学生。

正如我所说，这会输出正确的结果，但速度非常慢。

Answer 1

更多详细问题我可以稍后用解决方案进行编辑。为了更好地理解您的数据结构，让我为自己(和任何其他人)澄清以下内容。

StudentDB - 显而易见。每个学生一条记录。

ClassDB - 提供的所有可能类(class)的列表，并且可以有同一类(class)的多个实例，例如编程 101，但可能在不同日期、不同学期、谁正在教授特定实例等。

RegDB - 学生注册特定类(class)的所有条目的列表。因此，每条记录对于学生:类(class)都是 1:1。

AttendanceDB - 每个学生的出勤情况:类(class):日期。我假设出勤表也有出勤的类(class) ID。如果学生有 5 个类(class)，他们可能会在 1-3 类(class)上课，并在 4-5 类(class)提前离开。如果这是公立学校 K-12，并且一名学生要提前离开去参加博士预约等事件，您就知道他们会在那里上课，但不是全部。如果是大学，同样，不能保证他们在某一天有多个类(class)，但可以参加一个类(class)，但不一定参加所有类(class)。

所以，从这里开始，我会这样看待它。首先，提供符合相关日期条件的所有类(class) (ClassDB)。然后到报名表，然后到注册的学生。这些始终是有效的 JOIN。由此，根据细微差别/状态对出勤表进行条件连接

如果有助于查看表的结构，即使是缩写以确认查询关联/所需的列。我希望类(class)表具有可能适用的日期范围...例如从 2015 年 9 月 1 日到 2015 年 12 月 18 日的类(class)。

因此我的临时解决方案。我添加了 Furikae 字段，以便您可以获得所有结果并查看结果作为此查询的基准考虑因素。

SELECT 
      S.studentid,
      C.classID,
      C.class_level,
      C.class_title,
      C.TIME,
      C.teacher,
      S.first_name,
      S.last_name,
      ADB.furikae
   FROM 
      ClassDB C
         JOIN RegDB R
            on C.ClassID = R.ClassID
            JOIN StudentDB S
               ON R.studentid = S.studentID
               LEFT JOIN AttendanceDB ADB
                  on C.ClassID = ADB.ClassID
                 AND S.StudentID = ADB.StudentID
                 AND ADB.class_time >= '$todaysdate1'
                 AND ADB.class_time < '$tomorrowsdate1'
   WHERE
          '$datecheck' BETWEEN C.StartDate and C.EndDate
      AND C.day = '$dayofweek'
      AND (    ADB.Furikae IS NULL
            OR ADB.Furikae = '3' )

现在，您所要做的就是根据 Furikae 字段应用一个附加的 AND 子句。我输入了此类标准的示例，因为我不知道此 Furikae 状态字段的用途。此外，出勤确认是专门寻找当前日期基础的，因为学生可以在一周前参加，但不能根据“今天”参加。

对于索引，我建议如下。同样，不知道类(class)表是否有从/到日期的考虑

table       indexed on
ClassDB      (classID, StartDate, EndDate )
RegDB        (classID, StudentID )
StudentDB    (StudentID) -- would expect as it is primary key
AttendanceDB (classID, StudentID, class_time, Furikae )