mysql - 优化复杂的 SQL 查询 - 避免经常运行子查询

Question

我编写了一个非常低效的查询，并且我拼命地试图从中获得最佳性能。(我的MySQL技术不是很好，请大家谅解……)

首先:我只有数据库的只读访问权限。不幸的是，我无法创建临时表(我认为这是一个很好的解决方案)。

我的查询的目标是列出每一篇文章(表格艺术)，同时将过去三年的销售数量作为右侧的单独列。所有文档标题都列在“dok”中，所有行项目都列在“pos”中。

这些是表结构(仅提及相关字段):

艺术:id;重量;价格;...

多克:ID；日期；...

位置:id;dok_id;art_id;数量;...

为了简单起见，所有表名称都进行了更改，真正的表和字段具有非常复杂的名称。

我的第一次尝试是这样的(三个子查询):

select art.id,
art.weight,
art.price,
(select sum(pos.qty) from pos inner join dok on pos.dok_id = dok.id where year(dok.date) = 2013 and pos.art_id = art.id),
(select sum(pos.qty) from pos inner join dok on pos.dok_id = dok.id where year(dok.date) = 2014 and pos.art_id = art.id),
(select sum(pos.qty) from pos inner join dok on pos.dok_id = dok.id where year(dok.date) = 2015 and pos.art_id = art.id)

from art;

这是非常低效的。

因此，我编写了一个查询，给出了以下结果集(其中并非所有文章都包含在内，因为并非所有文章都已售出):

商品编号、年份、数量

将其用作表格以左连接，如下所示:

select art.id,
art.weight,
art.price,
t1.qty,
t2.qty,
t3.qty

from (art)

left join (select art.id, year(dok.date) as yr, sum(pos.qty) as qty from pos inner join dok on pos.dok_id = dok.id inner join art on pos.art_id = art.id where year(dok.date) in >= 2013 group by art.id, year(dok.date)) t1 on t1.id = art.id
left join (select art.id, year(dok.date) as yr, sum(pos.qty) as qty from pos inner join dok on pos.dok_id = dok.id inner join art on pos.art_id = art.id where year(dok.date) in >= 2013 group by art.id, year(dok.date)) t2 on t2.id = art.id
left join (select art.id, year(dok.date) as yr, sum(pos.qty) as qty from pos inner join dok on pos.dok_id = dok.id inner join art on pos.art_id = art.id where year(dok.date) in >= 2013 group by art.id, year(dok.date)) t3 on t3.id = art.id

where t1.yr= 2013 and t2.yr= 2014 and t3.yr= 2015;

此查询仅持续了第一个查询的 50% 左右。这更好，但没有我认为的那么好，它可以利用你们中的一些人的想法。使用子查询作为表的性能更好，但我认为还是做了三遍。

是否有一种方法可以让 MySQL 仅执行一次子查询，并将其用于所有三个连接？或者有完全不同的方法吗？

我期待您的想法。

来自德国的问候，

斯特凡

Answer 1

您可以使用单个子查询，在 SUM 中使用 IF 来仅对该字段中特定年份的行的值进行求和。像这样的东西(未经测试):-

SELECT art.id,
    art.weight,
    art.price,
    t1.qty_2013,
    t1.qty_2014,
    t1.qty_2015
FROM (art)
LEFT JOIN 
(
    SELECT art.id, 
            SUM(IF(year(dok.date) = 2013, pos.qty, 0)) AS qty_2013
            SUM(IF(year(dok.date) = 2014, pos.qty, 0)) AS qty_2014
            SUM(IF(year(dok.date) = 2015, pos.qty, 0)) AS qty_2015
    FROM pos 
    INNER JOIN dok ON pos.dok_id = dok.id 
    INNER JOIN art ON pos.art_id = art.id 
    WHERE YEAR(dok.date) IN (2013, 2014, 2015)
    GROUP BY art.id
) t1 ON t1.id = art.id