从两个连接表中对sum的大多数性能级查询

Question

我有三张桌子：

客户

id
name

service_type1

id 
customer_id 
price (one to many relation to the customer)

service_type2

id
customer_id 
price (one to many relation to the customer)

我想列出这些值：name、service_type1 – sum price、service_type2 – sum price

我创建了两个查询，它们可以做我想做的事情：

答：

SELECT
    name,
    SUM(price) AS sum_price,
    (SELECT SUM(price)
        FROM service_type2
        WHERE customer_id = u.id
    ) AS sum_price2 
FROM customer u
LEFT JOIN service_type1 a ON a.customer_id = u.id
GROUP BY u.id
ORDER BY u.id

B：

SELECT
    name,
    SUM(price) AS sum_price,
    p.sum_price AS sum_price2
FROM customer u
LEFT JOIN service_type1 a ON a.customer_id = u.id
LEFT JOIN
(
    SELECT SUM(price) AS sum_price, customer_id
    FROM service_type2
    GROUP BY customer_id
) p ON p.customer_id = u.id
GROUP BY u.id
ORDER BY u.id

我对它们进行了基准测试，它们的执行时间是相同的。是否有可能编写更多的性能方面的查询？

*这不是真的。"A“的速度要快得多，每个表中都有30k+记录。

Answer 1

我可能认为这个版本在适当的索引中会更好地工作：

SELECT u.name,
       (SELECT SUM(st.price)
        FROM service_type1 st
        WHERE st.customer_id = u.id
       ) as sum_service_type1,
       (SELECT SUM(price)
        FROM service_type2 st
        WHERE st.customer_id = u.id
       ) as sum_price  
FROM customer u
ORDER BY u.id;

最好的索引是customer(id, name)、service_type1(customer_id, price)和service_type2(customer_id, price)。

Answer 2

如果我的理解是正确的，请尝试如下：

SELECT id,sum_service_type1 INTO #T1 FROM service_type1 GROUP BY customer_id

SELECT id,sum_service_type2 INTO #T2 FROM service_type2 GROUP BY customer_id

SELECT name, sum_service_type1, sum_service_type2, FROM customer u LEFT JOIN #T1 a ON a.id = u.id LEFT JOIN #T2 b ON b.id = u.id

若要改进，可以创建聚集索引。

Answer 3

如何运行这两个查询，并合并两个结果集为一个使用合并排序在客户端。(它们已经按照u.id进行了排序，这样我们就可以很容易地合并它)。

SELECT
    name,
    SUM(price) AS sum_service_type1,
FROM customer u
    LEFT JOIN service_type1 a ON a.customer_id = u.id
GROUP BY u.id
ORDER BY u.id;

SELECT
    name,
    SUM(price) AS sum_service_type2,
FROM customer u
    LEFT JOIN service_type2 a ON a.customer_id = u.id
GROUP BY u.id
ORDER BY u.id;

我认为，如果两个查询的执行时间之和小于您的查询，那么在客户端实现是负担得起的。