mysql - 如何提高这个跨越 4 个表并具有多个嵌套语句的 SQL 查询的效率

Question

SELECT MIN(tL.id) AS classLessonID,
       tC.class AS className,
       CONCAT((SELECT term
                 FROM hl_classes_term
                WHERE active = '1'),
              LPAD((SELECT COUNT(*) as num
                      FROM hl_classes_lessons
                     WHERE class_id = tC.id
                       AND id <= MIN(tL.id)), 2, '0')) AS classLessonNum
  FROM hl_classes_lessons tL, hl_classes tC
 WHERE tL.id NOT IN (SELECT lesson_id
                       FROM hl_classes_answers
                      WHERE student = '".USER_ID."')
   AND tL.term_id = (SELECT id
                       FROM hl_classes_term
                      WHERE active = '1')
   AND tL.class_id = tC.id
 GROUP BY tL.class_id

光是看着我就知道我让你畏缩了。我怎样才能使该 SQL 查询更有效率。现在它运行得很快，但那是因为我的表中没有很多行。我想要回答这个问题，您需要了解更多关于我的数据库的信息。

我正在使用 MySQL。我有四个表:

• hl_classes (int id, int professor, varchar class, text description)
• hl_classes_lessons(int id、int class_id、int term_id、varchar lessonTitle、varchar lexiconLink、文本 lessonData)
• hl_classes_answers(int id、int lesson_id、int student、text submit_answer、int percent)
• hl_classes_terms(int id、datetime createtime、varchar label、int term、int active)

hl_classes 存储网站上的所有类。

类(class)是每个类(class)的单独类(class)。一个类可以有无限的课。每节课都有特定的学期。

hl_classes_terms 存储所有术语的列表，当前术语的字段 active = '1'。

当用户提交类(class)答案时，答案将存储在 hl_classes_answers 中。一个用户只能回答每节课一次。类(class)必须按顺序回答。

回到我的查询，它应该执行以下操作:选择用户 (USER_ID) 必须在当前学期为每个类(class)回答的下一课。仅选择下一课，获取唯一的类(class) ID，获取类(class)名称，然后是我们用来向用户显示“类(class)”的特殊编号。该数字是 term +(左填充 0)第 n 课的组合。即，第一学期第一课是101。第四学期第十二课是412。

目前这个查询有效，但我想知道如何让它更有效地运行。

Answer 1

我发现有两件事可能会对查询效率产生重大影响:

1. SELECT 中的子查询 - 该子查询正在针对结果集中的每一行运行。这可能加起来很多。最好创建一个可以作为表连接到主查询的子查询:

    SELECT ...    FROM hl_classes_lessons tL    JOIN hl_classes tC ON tL.class_id = tC.id    JOIN (    SELECT id, class_id COUNT(*) AS num    FROM hl_classes_lessons    ) AS awesome_join ON awesome_join.class_id = tC.id AND awesome_join.id [less than or equal to] MIN(tL.id)    WHERE ...

1. Poor FROM clause - avoid using the SELECT ... FROM table1,table2,table3 syntax. Instead. do something like

    FROM hl_classes_lessons tL
    JOIN hl_classes tC ON tL.class_id = tC.id
    WHERE   tL.id NOT IN (  SELECT lesson_id
               FROM hl_classes_answers
               WHERE student = ".USER_ID." )
    AND tL.term_id = ( SELECT id
               FROM hl_classes_term
               WHERE active = 1 )

修复这些问题后，我将验证相关列是否具有索引(hl_classes_lessons.class_id、hl_classes.id)。使用 EXPLAIN 运行查询查看是否有应该使用但由于某种原因未使用的索引。

最后，我会 avoid enclosing integers in single quotes .