Mysql - 帮助优化查询

Question

我有这个问题:

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 * 3 ) - ( 
                    + bal.bal_amount1 + bal.bal_amount2 
                    + bal.bal_amount3 ) ), 0) ) AS Difference, 
       ( COALESCE(Round(( + bal.bal_amount1 + bal.bal_amount2 
                          + bal.bal_amount3 ) / 3), 0) )     AS Average, 
       bal.bal_amount1                                       AS BAL1, 
       bal.bal_amount2                                       AS BAL2, 
       bal.bal_amount3                                       AS BAL3 
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 = 21491 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 = 12841 THEN amount 
                               END), 0) AS bal_amount1, 
                    Ifnull(Sum(CASE 
                                 WHEN balance_id = 18647 THEN amount 
                               END), 0) AS bal_amount2, 
                    Ifnull(Sum(CASE 
                                 WHEN balance_id = 18674 THEN amount 
                               END), 0) AS bal_amount3 
             FROM   balance_lines 
             GROUP  BY budget_item_id) AS bal 
         ON bal.budget_item_id = bi.id 
ORDER  BY bi.location 

这需要很多时间。在 budget_lines 和 balance_lines 表中，每个表都有超过 5,000,000 行。

我还附上了查询的 EXPLAIN，因此您将无法看到问题所在。每个表中的所有 ID 都已编入索引。是否有任何列可以被索引以加快查询速度？或者我可能需要更改它。

*** LEFT JOIN 是必要的，因为我需要从 nudget_items 中获取所有项目，即使它们不存在于 balance/budget_line 表中。

架构是:每个预算都有它的 budget_lines。每个余额都有其 balance_lines。该查询的目的是用一个表来汇总预算和多个余额之间的差异。

您可以在这里看到更大的图像:http://i.stack.imgur.com/dlF8V.png

编辑:@Sebas 回答后:

对于@sabes 的饥饿，我把描述放在这里:预算项目

budget_lines

平衡线

Answer 1

也许是这样的；但是没有样本数据和索引很难看

SELECT * 
FROM   budget_items bi 
WHERE  bi.location IS NOT NULL) AS bi 
INNER JOIN  --Added inner for clarity  -changed order I just like my inner's before my outers.
  (SELECT budget_item_id, Sum(CASE WHEN balance_id = 12841 THEN coalesce(amount,0) END), 0) AS bal_amount1, 
                          Sum(CASE WHEN balance_id = 18647 THEN coalesce(amount,0) END), 0) AS bal_amount2, 
                          Sum(CASE WHEN balance_id = 18674 THEN coalesce(amount,0) END), 0) AS bal_amount3 
   FROM   balance_lines 
   WHERE balance_ID in (12841, 18647, 18674) --This way balance_IDs which aren't in this list don't even get evaluated
   GROUP  BY budget_item_id) AS bal 
 ON bal.budget_item_id = bi.id 
LEFT JOIN 
 (SELECT budget_item_id, Sum(CASE WHEN budget_id = 21491 THEN coalesce(amount,0) END) AS bud_amount 
  FROM   budget_lines 
  WHERE budget_Id = 21491 --Again since we only care about anything but budget_ID 21491, we can limit the results to JUST that 
  GROUP  BY budget_item_id) AS bl 
 ON bl.budget_item_id = bi.id