mysql - LEFT JOIN执行时间太长

Question

我有两张 table 。第一个是日历，第二个是final_registration，如下所示:

*--------------------------*
| calender_id |  datefield | 
*--------------------------*
|     1       | 2015-07-13 |
|     2       | 2015-07-14 |
|     3       | 2015-07-15 |
|     4       | 2015-07-16 |
|     -       | ---------- |
|     -       | ---------- |
|     -       | ---------- |
|     5647    | 2030-12-28 |
|     5648    | 2030-12-29 |
|     5649    | 2030-12-30 |
|     5650    | 2030-12-31 |
*--------------------------*

所以我的第一个表大约有 5650 条记录。

现在第二个表是我的注册表，我在其中存储用户信息和预订日期

*--------------------------------------------------*
| id |  name |     booking_date    | ticket_status | 
*--------------------------------------------------*
|  1 |  RAM  | 2018-12-24 12:54:53 |    active     |
|  2 |  RAO  | 2018-12-24 12:54:53 |    active     |
|  3 |  RAT  | 2018-12-24 12:54:53 |    active     |
|  4 |  PAL  | 2018-11-24 12:54:53 |    active     |
|  5 |  TOM  | 2018-10-24 12:54:53 |    active     |
|  6 |  SAM  | 2018-10-24 12:54:53 |    active     |
|  7 |  RAT  | 2018-09-24 12:54:53 |    active     |
|  8 |  MAT  | 2019-12-24 12:54:53 |    active     |
|  9 |  NOT  | 2019-12-24 12:54:53 |    active     |
| 10 |  RAM  | 2019-12-24 12:54:53 |    active     |
*--------------------------------------------------*

现在我想统计一下2018年哪本书的注册量按月份划分。

| booking_date | countT |
|   2018-01    |   0    |
|   2018-02    |   0    |
|   2018-03    |   0    |
|   2018-04    |   0    |
|   2018-05    |   0    |
|   2018-06    |   0    |
|   2018-07    |   0    |
|   2018-08    |   0    |
|   2018-09    |   1    |
|   2018-10    |   2    |
|   2018-11    |   1    |
|   2018-12    |   3    |

我正在使用以下查询，我的查询给出了正确的输出，但问题是执行时间。执行时间太长，至少 10 分钟。

SELECT 
  DATE_FORMAT(calendar.datefield, '%Y-%m') AS booking_date, 
  COUNT(final_registration.booking_date) AS countT 
FROM calendar 
LEFT JOIN final_registration ON DATE_FORMAT(final_registration.booking_date, '%Y-%m-%d') = 
    DATE_FORMAT(calendar.datefield, '%Y-%m-%d') 
  AND final_registration.ticket_status IN ('active', 'cancelled') 
WHERE DATE_FORMAT(calendar.datefield, '%Y') = $year 
GROUP BY DATE_FORMAT(calendar.datefield, '%Y-%m')

Answer 1

我会推荐一个相关的子查询和索引:

SELECT yyyymm, 
       (SELECT COUNT(*)
        FROM final_registration fr
        WHERE fr.status IN ('active', 'cancelled') AND 
              fr.booking_date >= c.month_start AND
              fr.booking_date < c.month_start + interval 1 month
       ) as countT
FROM (SELECT DATE_FORMAT(c.datefield, '%Y-%m') as yyyymm,
             MIN(c.datefield) as month_start
      FROM calendar c
      WHERE YEAR(c.datefield) = ?  -- PASS IN AS PARAMETER!!!
      GROUP BY yyyymm
     ) c  
ORDER BY c.yyyymm;

您想要的索引位于final_registration(datefield, status)。

这比您的查询有几个好处:

• 可以使用索引进行日期比较，因为第二个日期的日期列没有使用任何函数。
• 它避免了昂贵的外部GROUP BY。

还要注意参数的使用，而不是使用文字值来修改查询。