java - 从 MySQL 中超过 100 万条记录创建报告并在 Java JSP 页面中显示

Question

我正在开发一个包含 3 个表的 MySQL 数据库——workout_data、exercises 和 set 表。我面临与根据这三个表生成报告相关的问题。

为了添加更多信息，多个练习组成一个练习，多个练习将成为一个锻炼。我目前拥有要根据这些表中的数据生成报告的指标。我必须生成过去 42 天(包括本周)的报告。当我通过连接这些表获得报告时，查询会运行很长时间。

例如 - 仅过去 42 天，sets 表就有超过 100 万条记录。该表中的id是exercise表中的excercise_id。 excercise表的id是workout_data表中的workout_id。

我正在运行此查询，需要 10 多分钟才能获取数据。我必须准备一份报告并将其在浏览器中显示给用户。但由于此长时间运行的查询，网页超时，用户无法查看报告。

关于如何实现这一目标有什么建议吗？

        SELECT REPORTSETS.USER_ID,REPORTSETS.WORKOUT_LOG_ID,
               REPORTSETS.SET_DATE,REPORTSETS.EXCERCISE_ID,REPORTSETS.SET_NUMBER 
          FROM EXCERCISES 
    INNER JOIN REPORTSETS ON EXCERCISES.ID=REPORTSETS.EXCERCISE_ID 
         where user_id=(select id from users where email='testuser1@gmail.com') 
           and substr(set_date,1,10)='2013-10-29' 
      GROUP BY REPORTSETS.USER_ID,REPORTSETS.WORKOUT_LOG_ID,
               REPORTSETS.SET_DATE,REPORTSETS.EXCERCISE_ID,REPORTSETS.SET_NUMBER

Answer 1

有两件事:

首先，您可以使用以下 WHERE 子句项来提取一天的数据。

  AND substr(set_date,1,10)='2013-10-29'

这绝对无法在日期上使用索引。如果您的 set_date 列具有 DATETIME 数据类型，那么您想要的是

  AND set_date >= `2013-10-09`
  AND set date <  `2013-10-09` + INTERVAL 1 DAY

这将允许对 set_date 上的索引使用范围扫描。在我看来，您可能需要在 (user_id, set_date) 上建立复合索引。但你应该仔细研究一下 EXPLAIN 来弄清楚这是否正确。

其次，您滥用了GROUP BY。除非查询中具有某种汇总函数，例如 SUM() 或 GROUP_CONCAT()，否则该子句毫无意义。您想要ORDER BY吗？