MySQL连接和和性能问题

Question

我对MySQL连接和SUM有个问题。因为我的JOIN有多个匹配项，所以我将在联接中做一个选择，所以它不会加到多少。

问题：

我的查询非常慢。在下面的测试设置中，它只在1ms内运行，没有任何索引。但是在我的生产数据库中，当只选择一个记录(WHERE id = X)时，这个查询需要4秒。

表A有~700.000项记录

表b有800.000条记录

表c有45.000条记录

表a在id上有索引

表b有id，a_id的索引

表c有id、a_id的索引

下面的简化表和使用的查询。

表a

id
---
1

表b

id | a_id | amount | price
--------------------------
 1 |    1 |      1 |    25
 2 |    1 |      1 |    20

表c

id | a_id | amount | price
--------------------------
 1 |    1 |      1 |    23
 2 |    1 |      1 |    20

查询：

现在，当我运行这个查询以获得表b和c中有引用的和时，a。

SELECT id, bPrice, cPrice
FROM a
LEFT JOIN (SELECT a_id, SUM(amount * price) AS bPrice FROM b GROUP BY a_id) bb ON a.id = bb.a_id
LEFT JOIN (SELECT a_id, SUM(amount * price) AS cPrice FROM c GROUP BY a_id) cc ON a.id = cc.a_id

错误结果但速度快

SELECT 
    a.id,
    SUM(b.amount * b.price) AS bPrice,
    SUM(c.amount * c.price) AS cPrice
FROM a
JOIN b ON a.id = b.a_id
JOIN c ON a.id = c.a_id;

SQL示例

Answer 1

MySQL将很难将索引从要连接的子查询中提取出来，并且怀疑您正在从子查询中加入许多行。

作为第一步，您可以尝试删除其中一个子查询，将其更改为对主表的联接。

SELECT id, 
        SUM(b.amount * b.price) AS bPrice, 
        cPrice
FROM a
LEFT OUTER JOIN b ON a.id = b.a_id
LEFT OUTER JOIN 
(
    SELECT a_id, 
            SUM(amount * price) AS cPrice 
    FROM c 
    GROUP BY a_id
) cc ON a.id = cc.a_id
GROUP BY a.id,
        cc.cPrice

Answer 2

尝试以下查询：

select set1.id,set1.bPrice,set2.cPrice from (SELECT a.id,SUM(b.amount * b.price) AS bPrice FROM a JOIN b ON a.id = b.a_id) as set1
JOIN
(SELECT a.id,SUM(c.amount * c.price) AS cPrice FROM a JOIN c ON a.id = c.a_id) as set2 ON set1.id = set2.id

Answer 3

可能值得一试子查询--有时它们可能会更快：

SELECT 
    id, 
    (SELECT SUM(amount * price) AS bPrice FROM b where b.a_id = a.id) AS bPrice, 
    (SELECT SUM(amount * price) AS cPrice FROM c where c.a_id = a.id) AS cPrice
FROM a