MySQL:对大型内部连接求和

Question

我有三个表:

mail_info 包含 CustomerID、MailID、Opened 列

mail_ids 包含 MailID、MailType、SendDate 列

mail_data 包含 CustomerID、Item、Sales、PurchaseDate 列

我想对每个 CustomerID 的 Sales 求和，按每个 PurchaseDate 分组，并显示二进制 Opened 每个客户/日期对的数据。

派生的 basetable 是将每个 MailID 与其对应的日期相关联，以在 mail_data 中使用。

这是我的查询:

SELECT CustomerID, Opened, SendDate, SUM(mail_data.Sales) FROM
(SELECT 
    mail_info.CustomerID,
    mail_info.Opened,
    mail_ids.SendDate
    FROM mail_info
    INNER JOIN mail_ids ON mail_info.MailID = mail_ids.MailID
    WHERE mail_ids.MailType = 'E'
) AS basetable
INNER JOIN mail_data ON mail_data.PurchaseDate = basetable.SendDate
GROUP BY CustomerID, SendDate
ORDER BY CustomerID, SendDate ASC;

和期望的输出示例:

# CustomerID, Opened, SendDate, SUM
1, 1, 2017-01-03, 5.68
1, 0, 2017-01-04, 4.92
1, 0, 2017-01-05, 43.23
2, 1, 2017-01-03, 12.65
2, 1, 2017-01-04, 283.24
2, 0, 2017-01-05, 74.23

我可以立即运行基表 SELECT，但是当我将 SUM 函数和 INNER JOIN mail_data 添加到派生表时，查询运行直到服务器超时(许多小时)。

mail_data 表大约有 6 亿行，其他表相对较小。

我在 mail_data.PurchaseDate 上添加了一个索引，EXPLAIN 表明该索引正在查询中使用。我还增加了缓冲区大小并将数据移动到 SSD。

这可能是服务器或数据库优化的问题，还是我的查询只是写错了？

谢谢!

Answer 1

在MySQL中，除非需要，否则最好不要使用子查询(派生表)。它们往往会具体化——作为临时表保存在磁盘上——这会影响性能。

那么，试试这个:

SELECT minf.CustomerID, minf.Opened, mi.SendDate, SUM(md.Sales)
FROM mail_info minf INNER JOIN
     mail_ids mi
     ON minf.MailID = mi.MailID INNER JOIN
     mail_data md
     ON md.PurchaseDate = mi.SendDate and
        md.CustomerID = mi.CustomerID
WHERE mi.MailType = 'E'
GROUP BY minf.CustomerID, mi.SendDate
ORDER BY minf.CustomerID, mi.SendDate ASC;

我在 CustomerID 上添加了一个额外的 JOIN 条件。这似乎是合理的。

您的表正在处理大量行。您可能认为 basetable 查询返回得很快——但它可能只返回结果集中的第一行。 GROUP BY 需要读取所有数据，然后做更多的处理，所以需要更长的时间。