mysql - MySQL - 简单的订单表现不佳

Question

表（innodb）：
学生-400.000线（35.000线未激活）
40000线
学生班-460000线
查询：

SELECT S.* 
FROM STUDENT S
JOIN CLASS_STUDENT CS ON CS.STUDENT_ID = S.STUDENT_ID 
JOIN CLASS C ON C.CLASS_ID = CS.CLASS_ID
WHERE S.ACTIVE = TRUE
GROUP BY S.STUDENT_ID --this suffices to fetch students only once
ORDER BY C.CLASS_DATE DESC --datetime field
LIMIT 0,5

执行时间 ORDER BY：3.2297秒
无 ORDER BY的执行时间：0.0015秒
在我的系统中，3秒是很长的一段时间，这会给我的用户带来不好的体验。有没有办法用 ORDER BY加速这个查询？ LIMIT用于分页。我是按描述排序的，因为我想先在我的分页结果中看到最近上课的学生。
我无法删除正在使用的连接。
谢谢！
编辑： CLASS_DATE两个查询：

edit2： EXPLAIN=4gb，我的服务器有16gb

Answer 1

如果这是我的项目，我只列出每个学生一次。不是多次，为注册（CLASS_STUDENT）表中的每一行重复学生。
我将认真考虑通过向STUDENT表中添加派生列来取消数据库实现的规范化，例如。

 ALTER TABLE STUDENT ADD latest_class_date DATE DEFAULT NULL;

并填充该列：

 UPDATE STUDENT t
   LEFT
   JOIN ( SELECT cs.student_id
               , MAX(c.class_date) AS latest_class_date
            FROM CLASS_STUDENT cs
            JOIN CLASS c
              ON c.class_id = cs.class_id
           GROUP BY cs.student_id
        ) s
     ON t.student_id = s.student_id   
    SET t.latest_class_date = s.latest_class_date

添加新列后，我可以创建一个适当的索引。

... ON STUDENT (active, latest_class_date, student_id)

那么我的问题就简单多了：

SELECT s.*
  FROM student s
 WHERE s.active = 1
 ORDER BY s.active DESC, s.latest_class_date DESC, student_id DESC
 LIMIT 5

我会将 student_id包含在 ORDER BY中，以使结果具有确定性。（如果没有，mysql可以按任意顺序返回任何 latest_class_date相同的行。）
我还将更有效地实现分页，保留上次检索行的值，并在查询中提供这些值。获取“下5行”：
（编辑：原来的分页查询模式明显错误。无论是在这里还是在后续的最后一个查询中，都已更正此问题。）

SELECT s.*
  FROM student s
 WHERE s.active = 1
   AND s.latest_class_date <= ?
   AND (s.latest_class_date < ? OR s.student_id < ? )
 ORDER BY s.active DESC, s.latest_class_date DESC, student_id DESC
 LIMIT 5

需要维护新 latest_class_date列的内容。有几个选择。
如果我能忍受一段时间，该列的内容可能不同步，那么
计划更新语句的执行以定期刷新该列的内容。
如果我需要保持该列的内容同步，则：
修改管理student、class和student_class表的应用程序，以确保每当从 latest_class_date和 CLASS表中添加/更改/删除行时，都会填充 STUDENT_CLASS列，或者
在表上添加触发器以保持该列已填充
BEFORE INSERT/UPDATE触发 STUDENT
AFTER INSERT/UPDATE/DELETE触发 CLASS_STUDENT
AFTER INSERT/UPDATE/AFTER DELETE触发 CLASS
（我需要记住触发器不是由外键操作触发的。例如，如果在deleted from CLASS_STUDENT中的行时，由于外键的级联操作而从 CLASS中删除行，则只会触发 CLASS表的触发器。这意味着我必须在 CLASS表的delete触发器中处理所需的操作。）
后续行动
如果你“需要所有的连接”是因为那些表中的其他信息，你“可能有一天会需要”，那么我上面的建议不会有多大帮助。在性能方面，美元到甜甜圈是“使用文件排序”操作在一个巨大的集上吃你的午餐。在我建议的查询中添加连接并不能避免“使用文件排序”操作。
如果在我建议的查询上有合理的性能，那么在进行连接之前，我们可以使用该查询作为内联视图来限制返回的行数。
但在执行此操作之前，我们必须首先处理Enrollment（ CLASS_STUDENT）表中有多行时返回的重复学生行。我们想让同一个学生多次返回吗？或者，我们是否要多次返回学生行，对于具有相同 CLASS_STUDENT的类，为 class_date中的每一行返回一次。或者，我们是否只想返回学生行一次，并且只返回一个 CLASS中的信息？如果我们为一个学生返回多行，是打算每页列出五个学生，还是每页列出五个学生班？
假设每页“分页”五行，我们是否期望这样的结果集？
第1行到第5行

student    class   class_date
-------    -----   ---------- 
Sam        phys    2016-02-12
Sam        calc    2016-02-12
Mary       lit     2016-02-12
Mahatma    art     2016-02-12
Paul       music   2016-02-11

第6行到第10行

Paul       engl    2016-02-11
Sam        art     2016-02-10
... 

我们编写的查询将由规范通知。
我们可以写很多可能的查询。但是如果没有一个规范，这些查询中的每一个都只是猜测。如果不知道我们想要返回什么结果集（在各种可能的条件下），我们就无法验证我们编写的查询是否正确。
我再次查看了您的查询，发现您确实有一个 GROUP BY student_id。（我们假设 student_id在 students表中是唯一的。）
如果学生的 CLASS_STUDENT表中有多行，并且这些相关的 CLASS行具有不同的 class_date值，则原始查询中返回的 class_date值是不确定的。mysql可以自由选择任何可能的 class_date值。（不仅仅是 class_date栏…… CLASS和 CLASS_STUDENT中的行的值是不确定的。）
根据最初的查询，不能保证拥有“最新”上课日期的学生会排在其他学生之前。例如，使用此集合：

student   class_date
-------   ----------
Sam       2016-02-22 
Sam       2015-07-17
Paul      2016-01-11

上面的查询可以在 Sam之前使用 Paul或 Sam之后使用 Paul对行进行排序。两个结果都对原始查询有效。而且不能保证每次运行查询的结果都是相同的。结果是不确定的。
其他数据库会对原始查询抛出一个错误，行为“select list中的non-aggregate不出现在group by子句中”。特定于mysql的扩展允许执行查询。通过在 ONLY_FULL_GROUP_BY中包含 sql_mode，可以使mysql的行为与其他数据库相同，并引发错误。

SELECT d.*
  FROM ( SELECT s.student_id
              , s.latest_class_date
           FROM student s
          WHERE s.active = 1
            AND s.latest_class_date <= ? 
            AND ( s.latest_class_date < ? OR s.student_id < ? )
          ORDER BY s.active DESC, s.latest_class_date DESC, student_id DESC
          LIMIT 5
       ) r
  JOIN student d
    ON d.student_id = r.student_id
  JOIN class_student e
    ON e.student_id = d.student_id
  JOIN class c
    ON c.class_id = e.class_id
   AND c.class_date = r.latest_class_date
 GROUP BY d.student_id