mysql:涉及日历表的RIGHT JOIN查询速度问题

Question

更新:看来问题(正如很多人指出的那样)是将查询中的日期时间字段更改为日期字段。

使用DATE( all_griefs_tbl.actioned_date 太慢了，是否有更快的方法而不将actioned_date更改为日期字段或将其拆分为日期和时间字段？

我有 2 个表，一个包含大量具有状态和日期时间字段的记录，另一个是包含 2008 年至 2015 年日期的日历表。

我想要得到的是一个时间段内的每个日期以及每天“接受”的记录数 - 即使该计数为零 - 如下所示:

| Date      | number_accepted |
 ----------------------------
 2012-03-01     723
 2012-03-02     723
 2012-03-03     1055
 2012-03-04     1069
 2012-03-05     0
 2012-03-06     615
 2012-03-07     0
 2012-03-08     1072
 2012-03-09     664
 2012-03-10     859
 2012-03-11     0
 2012-03-12     778
 2012-03-13     987

我已经尝试了以下方法，但它仅在小数据样本(-1000 行)上足够快。我需要在至少 600k 行上运行良好的东西

SELECT calendar.datefield AS Date, 
       COUNT( all_griefs_tbl.actioned_status ) AS total_griefs
FROM all_griefs_tbl
RIGHT JOIN calendar 
   ON ( DATE( all_griefs_tbl.actioned_date ) = calendar.datefield )
   AND all_griefs_tbl.actioned_status = 'accepted'
WHERE calendar.datefield < CURDATE( )
GROUP BY calendar.datefield

谢谢

编辑:按要求执行计划

 id select_type     table           type    possible_keys     key               key_len     ref     rows    Extra
 1  SIMPLE          calendar        range   PRIMARY           PRIMARY           3           NULL    1576    Using where; Using index
 1  SIMPLE          all_griefs_tbl  ref     actioned_status   actioned_status   153         const   294975  

Answer 1

一些想法...

首先，尽管您声明您希望数据库查询中没有返回任何值，但实际上我会在处理结果集时对结果集进行此检查。每当您进行联接时，都会使查询变得更加复杂，并且需要更多内存来处理它们。在这种情况下，我不会认为您对日历表的使用是对关系数据库的特别好的使用。

编辑:澄清一下，查询是如何被调用的？即是否有一些程序(您正在开发)访问数据库、运行查询并显示结果？如果是这样，我建议让这个程序在演示之前处理结果。

其次，如果您致力于“联接”，那么您确实应该在 all_griefs_tbl.actioned_date 上有一个索引，因为这是您要进行联接的列。或者，您可以在 calendar.datefield 上指定外键。

第三，是否需要使用函数DATE(all_griefs_tbl.actioned_date)？这不是已经约会了吗？ (不确定您的数据类型，但如果这和calendar.datefield不是相同的数据类型，这看起来像是糟糕的数据库设计。)

编辑:根据您所说的，您可能需要将 all_griefs_tbl.actioned_date 分成两列:日期列 all_griefs_tbl.actioned_date 和时间戳列 all_griefs_tbl.actioned_time。目前，您正在 all_griefs_tbl 中的每一行运行这个 DATE() 函数来进行连接 - 这很快就会使查询变得缓慢。这还允许您在 date 和 time 列上添加索引，这也将提高联接的性能(鉴于您当前的数据库设计，我不令人惊讶的是 actioned_date 上的索引没有帮助 - 我宁愿期望，由于 DATE() 函数，如果您重新运行 EXPLAIN当前在 actioned_date 列上有一个索引，则不会在 all_griefs_tbl 上使用此索引来显示它。)

第四，您可能需要考虑 all_griefs_tbl.actioned_status 中存储了哪些类型的信息。这可以用 bool 值代替吗？这在存储和处理数据方面会更加有效。 (尽管如此，这取决于您的数据库设计。)

编辑:您可以考虑将 all_griefs_tbl.action_status 更改为较小的数据类型 - 我希望它当前是 varchar，但您可以轻松地将其更改为单个(或小)char 数据类型，甚至更改为一些 bool 值。但是，我不认为这是主要的性能开销，实际上是一个更复杂的数据库设计决策，具体取决于项目的需求。