MySQL LEFT JOIN 查询非常慢

Question

SELECT bi.id, 
       bi.location, 
       bi.expense_group, 
       bi.level, 
       bi.is_active, 
       bi.type, 
       full_name, 
       Sum(DISTINCT bl.amount)                                      AS 
       BudgetAmount, 
       Sum(DISTINCT Ifnull(( bl.amount * 6 ) - ( + bal1.amount + bal2.amount + 
                                                 bal3.amount 
                                                 + bal4.amount + bal5.amount + 
                                                 bal6.amount ), 0)) AS 
       Difference, 
       Sum(DISTINCT Ifnull(Round(( + bal1.amount + bal2.amount + bal3.amount 
                                   + bal4.amount + bal5.amount + bal6.amount ) / 
                                 6), 0) 
       )                                                            AS Average, 
       Sum(DISTINCT bal1.amount)                                    AS BAL1, 
       Sum(DISTINCT bal2.amount)                                    AS BAL2, 
       Sum(DISTINCT bal3.amount)                                    AS BAL3, 
       Sum(DISTINCT bal4.amount)                                    AS BAL4, 
       Sum(DISTINCT bal5.amount)                                    AS BAL5, 
       Sum(DISTINCT bal6.amount)                                    AS BAL6 
FROM   (SELECT * 
        FROM   budget_items 
        WHERE  bi.location IS NOT NULL) AS bi 
       LEFT JOIN budget_lines AS bl 
              ON bi.id = bl.budget_item_id 
                 AND bl.budget_id = 5983 
       LEFT JOIN balance_lines AS bal1 
              ON bi.id = bal1.budget_item_id 
                 AND bal1.balance_id = 28839 
       LEFT JOIN balance_lines AS bal2 
              ON bi.id = bal2.budget_item_id 
                 AND bal2.balance_id = 28633 
       LEFT JOIN balance_lines AS bal3 
              ON bi.id = bal3.budget_item_id 
                 AND bal3.balance_id = 26664 
       LEFT JOIN balance_lines AS bal4 
              ON bi.id = bal4.budget_item_id 
                 AND bal4.balance_id = 14500 
       LEFT JOIN balance_lines AS bal5 
              ON bi.id = bal5.budget_item_id 
                 AND bal5.balance_id = 10199 
       LEFT JOIN balance_lines AS bal6 
              ON bi.id = bal6.budget_item_id 
                 AND bal6.balance_id = 7204 
GROUP  BY bi.id 
ORDER  BY bi.position 

如您所见，实际上只有 3 个表，但我需要为每个余额查询其中一个表。这需要很多时间。我的错误在哪里？

---

在与@Gordon Linoff 的帖子作斗争后，我尝试自己修复它。 @Gordon Linoff 是你的意思吗？

     SELECT bi.id, 
       bi.location, 
       bi.expense_group, 
       bi.level, 
       bi.is_active, 
       bi.type, 
       full_name, 
       (bl.bud_amount) AS BudgetAmount, 
       (coalesce(( bl.bud_amount * 6 ) - (bal.bal_amount1 + bal.bal_amount2 + bal.bal_amount3 + bal.bal_amount4 + bal.bal_amount5 + bal.bal_amount6),
                    0)) AS Difference, 
       (coalesce(Round(( bal.bal_amount1 + bal.bal_amount2 + bal.bal_amount3 + bal.bal_amount4 + bal.bal_amount5 + bal.bal_amount6 ) / 
                                 6), 0) 
           ) AS Average, 
       bal.bal_amount1                                    AS BAL1, 
       bal.bal_amount2                                    AS BAL2, 
       bal.bal_amount3                                    AS BAL3, 
       bal.bal_amount4                                    AS BAL4, 
       bal.bal_amount5                                    AS BAL5, 
       bal.bal_amount6                                    AS BAL6 
FROM (SELECT * 
      FROM   budget_items bi
      WHERE  bi.location IS NOT NULL
     ) AS bi

     LEFT JOIN
     (select budget_item_id, Sum(case when budget_id = 5983 then amount end) AS bud_amount  
     from budget_lines
      group by budget_item_id
    ) AS bl
    on bl.budget_item_id = bi.id

     JOIN
     (select budget_item_id,
             IFNULL(Sum(case when balance_id = 28839 then  amount end), 0) AS bal_amount1, 
             IFNULL(Sum(case when balance_id = 28633 then  amount end), 0) AS bal_amount2, 
             IFNULL(Sum(case when balance_id = 26664 then  amount end), 0) AS bal_amount3, 
             IFNULL(Sum(case when balance_id = 14500 then  amount end), 0) AS bal_amount4,
             IFNULL(Sum(case when balance_id = 10199 then  amount end), 0) AS bal_amount5,
             IFNULL(Sum(case when balance_id = 7204 then  amount end), 0) AS bal_amount6 
      from balance_lines
      group by budget_item_id
    ) AS bal
    on bal.budget_item_id = bi.id
ORDER BY bi.location 

Answer 1

只要你有 sum(distinct . . . ) 你就有问题了。您在左外连接上得到笛卡尔积，如果有两条线的数量相同，您的总体结果将不准确。

我想你想用这样的子查询替换所有那些左外连接:

select budget_item_id,
       Sum(case when budget_id = 5983 then  amount end) AS BAL, 
       Sum(case when budget_id = 28839 then  amount end) AS BAL1, 
       Sum(case when budget_id = 28633 then  amount end) AS BAL2, 
       Sum(case when budget_id = 26664 then  amount end) AS BAL3, 
       Sum(case when budget_id = 14500 then  amount end) AS BAL4,
       Sum(case when budget_id = 10199 then  amount end) AS BAL5,
       Sum(case when budget_id = 7204 then  amount end) AS BAL6 
from balance_lines
group by budget_item_id

然后修复查询的其余部分。

这是编写查询的尝试。这无疑有语法错误:

SELECT bi.id, 
       bi.location, 
       bi.expense_group, 
       bi.level, 
       bi.is_active, 
       bi.type, 
       full_name, 
       (bl.bal) AS BudgetAmount, 
       (coalesce(( bl.bal * 6 ) - (bl.bal1 + bl.bal2 + bl.bal3 + bl.bal4 + bl.bal5 + bl.bal6),
                    0)) AS Difference, 
       (coalesce(Round(( bl.bal1 + bl.bal2 + bl.bal3 + bl.bal4 + bl.bal5 + bl.bal6 ) / 
                                 6), 0) 
           ) AS Average, 
       bl.bal1                                    AS BAL1, 
       bl.bal2                                    AS BAL2, 
       bl.bal3                                    AS BAL3, 
       bl.bal4                                    AS BAL4, 
       bl.bal5                                    AS BAL5, 
       bl.bal6                                    AS BAL6 
FROM (SELECT * 
      FROM   budget_items bi
      WHERE  bi.location IS NOT NULL
     ) bi left outer join
     (select budget_item_id,
             Sum(case when budget_id = 5983 then  amount end) AS BAL, 
             Sum(case when budget_id = 28839 then  amount end) AS BAL1, 
             Sum(case when budget_id = 28633 then  amount end) AS BAL2, 
             Sum(case when budget_id = 26664 then  amount end) AS BAL3, 
             Sum(case when budget_id = 14500 then  amount end) AS BAL4,
             Sum(case when budget_id = 10199 then  amount end) AS BAL5,
             Sum(case when budget_id = 7204 then  amount end) AS BAL6 
      from balance_lines
      group by budget_item_id
    ) bal
    on bal.budget_item_id = bi.id
ORDER  BY bi.position 

请注意，我完全删除了外部 group by，假设 bi.id 是预算项目表中的唯一键。