MYSQL --如何优化3个左边的连接表？

Question

问题:当每个表的行数很少(例如少于1000行)时，我的查询工作得非常好。，但当我试图用虚拟数据填充db时，页面将无休止地加载.

我使用三个表连接：

1. 学生-它保存学生的基本信息(7,000+行) 索引栏：student_id、年级和
2. 旷课-这保存缺勤记录(31,000+行) 索引列：absent_id，student_id，免费
3. 迟缓-这保存了延迟的记录(19,000+行) 索引列：tardi_id，student_id，免费

输出表如下所示：

Name       | Grade | Section | Sex | Age | Tardiness Count | Absence Count 
____________________________________________________________________
John Smith | 7     | E05-A   | Male| 18  | 6               | 5

这是我的查询：

SELECT students.student_id , students.name_l, students.name_f, students.name_m, students.grade, students.section, students.age_s, students.sex, 

(SELECT COUNT(distinct absenteeism.absent_id) FROM absenteeism 
 WHERE absenteeism.excused=0 AND absenteeism.student_id = students.student_id ) AS absents, 

(SELECT COUNT(distinct tardiness.tardi_id) FROM tardiness 
 WHERE tardiness.excused=0 AND tardiness.student_id = students.student_id ) AS tardi 

FROM students 
LEFT JOIN absenteeism ON absenteeism.student_id = students.student_id 
LEFT JOIN tardiness ON tardiness.student_id = students.student_id 
GROUP BY students.student_id 
ORDER BY name_l ASC LIMIT 0, 15

我的查询只是从学生表中获取我需要显示的所有数据，然后计算缺勤、和迟到表中的所有记录，这些记录没有被对应的student_id标记为每一行。

下面是查询的扩展解释：

id  select_type         table       type    possible_keys       key         key_len ref                         rows    filtered    extra

1   PRIMARY             students    index   NULL                PRIMARY     34      NULL                        6900    100.00      Using temporary; Using filesort 
1   PRIMARY             absenteeism ref     student_id          student_id  34      lnhs.students.student_id    1583    100.00      Using index 
1   PRIMARY             tardiness   ref     student_id          student_id  34      lnhs.students.student_id    3851    100.00      Using index 
3   DEPENDENT SUBQUERY  tardiness   ref     student_id,excused  student_id  34      func                        3851    100.00      Using where 
2   DEPENDENT SUBQUERY  absenteeism ref     student_id,excused  student_id  34      func                        1583    100.00      Using where 

Answer 1

可能导致您死亡的是SELECT子句中出现的两个相关聚合查询。相反，在单独的真正子查询中进行聚合，然后将它们连接到您的students表中。

SELECT
    s.*,      -- replace with whatever columns you actually want
    COALESCE(t1.num_absent, 0) AS num_absent,
    COALESCE(t2.num_tardy, 0) AS num_tardy
FROM students s
LEFT JOIN
(
    SELECT student_id, COUNT(DISTINCT absent_id) AS num_absent
    FROM absenteeism
    WHERE excused = 0
    GROUP BY student_id
) t1
    ON s.student_id = t1.student_id
LEFT JOIN
(
    SELECT student_id, COUNT(DISTINCT tardi_id) AS num_tardy
    FROM tardiness
    WHERE excused = 0
    GROUP BY student_id
) t2
    ON s.student_id = t2.student_id
ORDER BY
    s.name_l
LIMIT 0, 15

请注意，我对每个学生缺课和迟到的次数使用COALESCE()。造成这种情况的原因，以及LEFT JOIN (明智地)被使用的原因可能是，给定的学生既不缺勤也不迟到。INNER JOIN会将该学生从结果集中移除。相反，COALESCE允许检测学生既没有出现在缺席的子查询中，也没有出现在迟来的子查询中，在这些情况下，我们可以为相应的数字报告零。