mysql - 如何优化下面使用三级 select 语句的查询？

Question

如何优化以下查询:

我有两个表，'calendar_table' 和 'consumption'，在这里我使用这个查询来计算每年的月度消费。

日历表有 2005 - 2009 年的日、月和年，消费表有每月账单周期的账单消费数据。此查询将计算每个账单的天数，并使用它来查找每个月的消耗量。

SELECT id,
        date_from as bill_start_date,
        theYear as Year,
        MONTHNAME(STR_TO_DATE(theMonth, '%m')) as month, 
        sum(DaysOnBill),
        TotalDaysInTheMonth,
        sum(perDayConsumption *  DaysOnBill) as EstimatedConsumption 
    FROM
    ( 
     SELECT
        id,
        date_from,
        theYear,
        theMonth,  # use theMonth for displaying the month as a number
        COUNT(*) AS DaysOnBill,
        TotalDaysInTheMonth, 
        perDayConsumption
    FROM
        (
        SELECT
            c.id,
            c.date_from as date_from,
            ct.dt,
            y AS theYear,
            month AS theMonth,
            DAY(LAST_DAY(ct.dt)) as TotalDaysInTheMonth,
            perDayConsumption
        FROM
            consumption AS c
            INNER JOIN
            calendar_table AS ct
                ON ct.dt >= c.date_from
                    AND ct.dt<= c.date_to
        ) AS allDates
    GROUP BY
        id,
        date_from,
        theYear,
        theMonth ) AS estimates
GROUP BY
        id,
        theYear,
        theMonth;       

浏览大约 100 万条记录大约需要 1000 秒。可以做些什么来让它更快吗？

Answer 1

查询有点可疑，假装先进行一个分组，然后在此基础上构建另一个分组，但实际上并非如此。

首先，该法案将与它的所有日子结合在一起。然后我们按账单加上月份和年份分组，从而获得数据的月度 View 。这可以一次完成，但查询首先加入，然后将结果用作聚合的派生表。最后再次获取结果并构建“另一个”组，这实际上与之前相同(账单加上月份和年份)并完成一些伪聚合(例如 sum(perDayConsumption * DaysOnBill) 与 perDayConsumption * 相同DaysOnBill，因为 SUM 仅在此处对一条记录求和)。

这可以简单地写成:

SELECT
  c.id,
  c.date_from as bill_start_date,
  ct.y AS Year,
  MONTHNAME(STR_TO_DATE(ct.month, '%m')) as month,
  COUNT(*) AS DaysOnBill,
  DAY(LAST_DAY(ct.dt)) as TotalDaysInTheMonth,
  SUM(c.perDayConsumption) as EstimatedConsumption
FROM consumption AS c
INNER JOIN calendar_table AS ct ON ct.dt BETWEEN c.date_from AND c.date_to
GROUP BY
  c.id,
  ct.y,
  ct.month;

我不知道这是否会更快，或者 MySQL 的优化器是否没有看穿您的查询本身并将其归结为这个。