MySQL 查询太慢，需要 500 多秒，还有其他建议吗

Question

SELECT `mi`.`Item_Description` AS `Item_Description`,
       `psi`.`Item_ID` AS `Item_ID`,
       `mt`.`Type_Description` AS `Type_Description`,
       `psi`.`Item_Type_Id` AS `Item_Type_Id`,
       `psi`.`Item_Unit` AS `Item_Unit`,
       (Select Sum(Item_Quantity_Delivered) From project_store_input Where Item_ID = psi.Item_Id) AS `Total_Input`,
       (Select Sum(Output_Quantity) From project_store_output Where Item_Id = psi.Item_Id) as Total_Output,
       ((Select Sum(Item_Quantity_Delivered) From project_store_input Where Item_ID = psi.Item_Id) - (Select Sum(Output_Quantity) From project_store_output Where Item_Id = psi.Item_Id)) as Balance

FROM (`project_store_input` `psi`
     LEFT JOIN `materials_items` `mi` on(`mi`.`Item_ID` = `psi`.`Item_ID`)
     LEFT JOIN `materials_types` `mt` on(`mt`.`Type_ID` = `psi`.`Item_Type_Id`)
     LEFT JOIN `suppliers` `s` on(`s`.`Supplier_ID` = `psi`.`Supplier_ID`)
     LEFT JOIN `project_store_output` as `pso` on (pso.Item_Id = psi.Item_ID))
GROUP BY `psi`.`Item_ID`

此 SQL 查询用于计算结果应浏览的两个表“project_store_input”和“project_store_output”的余额(Item Name、Item ID、Total_Input、Total_Output、Balance)

有什么建议请提前致谢...

Answer 1

无需编写子查询。您可以直接添加数量。

试试这个:

SELECT mi.Item_Description AS Item_Description,
       psi.Item_ID AS Item_ID,
       mt.Type_Description AS Type_Description,
       psi.Item_Type_Id AS Item_Type_Id,
       psi.Item_Unit AS Item_Unit,
       psi1.DeliveredQty AS Total_Input,
       pso1.OutputQty AS Total_Output,
       (IFNULL(psi1.DeliveredQty, 0) - IFNULL(pso1.OutputQty, 0)) AS Balance
FROM project_store_input psi
LEFT JOIN materials_items mi ON mi.Item_ID = psi.Item_ID
LEFT JOIN materials_types mt ON mt.Type_ID = psi.Item_Type_Id
LEFT JOIN suppliers s ON s.Supplier_ID = psi.Supplier_ID
LEFT JOIN project_store_output AS pso ON pso.Item_Id = psi.Item_ID
LEFT JOIN (SELECT Item_ID, SUM(Item_Quantity_Delivered) DeliveredQty FROM project_store_input GROUP BY Item_ID) psi1 ON psi.Item_ID = psi.Item_Id 
LEFT JOIN (SELECT Item_Id, SUM(Output_Quantity) OutputQty FROM project_store_output GROUP BY Item_Id) pso1 ON psi.Item_Id = pso1.Item_Id
GROUP BY BY psi.Item_ID

或

SELECT mi.Item_Description AS Item_Description,
       psi.Item_ID AS Item_ID,
       mt.Type_Description AS Type_Description,
       psi.Item_Type_Id AS Item_Type_Id,
       psi.Item_Unit AS Item_Unit,
       SUM(Item_Quantity_Delivered) AS Total_Input,
       SUM(Output_Quantity) AS Total_Output,
       (SUM(Item_Quantity_Delivered) - SUM(Output_Quantity)) AS Balance
FROM project_store_input psi
LEFT JOIN materials_items mi ON mi.Item_ID = psi.Item_ID
LEFT JOIN materials_types mt ON mt.Type_ID = psi.Item_Type_Id
LEFT JOIN suppliers s ON s.Supplier_ID = psi.Supplier_ID
LEFT JOIN project_store_output AS pso ON pso.Item_Id = psi.Item_ID
GROUP BY BY psi.Item_ID