MySQL选择查询中子选择的效率

Question

我试图从数据库表的集合中产生一个报告，我发现我写的查询超慢。我意识到在选择查询中使用多个子查询会增加查询完成所需的时间，因为每行都需要对数据库进行多个子查询，但我能做些什么来使查询更快，更高效？

我试过在准备好的语句中包装它，它没有什么作用，有没有一种方法可以让我更快地提取所需信息？

SELECT Contractor.id, 
Contractor.Unique_ID, 
Contractor.FName, 
Contractor.SName, 
Contractor.MIName, 
Contractor.NI_Number, 
Contractor.Address, 
Contractor.Phone_Main, 
Contractor.Phone_Mobile, 
Contractor.Email, 
Contractor.Bank_Name, 
Contractor.Bank_Account_Name, 
Contractor.Bank_Account_Sort_Code, 
Contractor.Bank_Account_Number, 
Contractor.DOB, 
Contractors.DateTime_Account_Created, 
Contractor.Payment_Frequency, 
Contractor.Saving_Tax_Pref, 
Contractor.Holiday_Savings_Pref, 
Contractor.UTR, 
Contractor.DateTime_Last_Logged_In, 
Contractor.Leaving_Date, 
Agency.Name, 
(
    SELECT ROUND(SUM(ContractorSavings.value - ContractorSavings.paidOutValue), 2) 
    FROM ContractorSavings 
    LEFT JOIN SavingsToPayJunction ON SavingsToPayJunction.SavingsId = ContractorSavings.id 
    WHERE fullyPaid = 0 
    AND ContractorSavings.type_id = 2 
    AND SavingsToPayJunction.ContractorId = Contractor.id 
) AS 'Tax_Saving_Balance', 
(
    SELECT ROUND(SUM(ContractorSavings.value - ContractorSavings.paidOutValue), 2) 
    FROM ContractorSavings 
    LEFT JOIN SavingsToPayJunction ON SavingsToPayJunction.SavingsId = ContractorSavings.id 
    WHERE fullyPaid = 0 
    AND ContractorSavings.type_id = 2 
    AND SavingsToPayJunction.ContractorId = Contractor.id 
) AS 'Holiday_Saving_Balance', 
(
    SELECT ROUND(SUM(Expense_Value),2) 
    FROM Contractor_Expenses 
    WHERE Contractor_ID = Contractor.id 
    AND authorised = 0 
)AS 'Expenses_Claimed', 
(
    SELECT ROUND(SUM(Expense_Value - amountAllocated),2) 
    FROM Contractor_Expenses 
    WHERE Contractor_ID = Contractor.id 
    AND authorised = 1 
    AND fullyAllocated = 0 
)AS 'Expenses_Authorised', 
(
    SELECT MAX(Contractor_Pay.Date_Earned) 
    FROM Contractor_Pay 
    WHERE Contractor_Pay.Contractor_ID = Contractor.Unique_ID 
)AS 'Last_Pay_Date' 
FROM Contractor 
LEFT JOIN Agency ON Agency.id = Contractor.Agency_ID 

笨拙的查询转储道歉。我期待着你的回应。

谢谢

*邮政的答案编辑*

对于那些关心时限等，答案降低我的查询时间缩短从超过3:00分钟到45秒。

Answer 1

你可以在每个那些在FROM子句（与GRO​​UP BY Contractor_ID）子选择的就加入他们了：

SELECT 
    Contractor.id, 
    Contractor.Unique_ID, 
    Contractor.FName, 
    Contractor.SName, 
    Contractor.MIName, 
    Contractor.NI_Number, 
    Contractor.Address, 
    Contractor.Phone_Main, 
    Contractor.Phone_Mobile, 
    Contractor.Email, 
    Contractor.Bank_Name, 
    Contractor.Bank_Account_Name, 
    Contractor.Bank_Account_Sort_Code, 
    Contractor.Bank_Account_Number, 
    Contractor.DOB, 
    Contractors.DateTime_Account_Created, 
    Contractor.Payment_Frequency, 
    Contractor.Saving_Tax_Pref, 
    Contractor.Holiday_Savings_Pref, 
    Contractor.UTR, 
    Contractor.DateTime_Last_Logged_In, 
    Contractor.Leaving_Date, 
    Agency.Name, 
    Tax_Saving_Balance.val as Tax_Saving_Balance, 
    Holiday_Saving_Balance.val as Holiday_Saving_Balance, 
    Expenses_Claimed.val as Expenses_Claimed, 
    Expenses_Authorised.val as Expenses_Authorised, 
    Last_Pay_Date.val as Last_Pay_Date 
FROM Contractor 
LEFT JOIN Agency ON Agency.id = Contractor.Agency_ID 
LEFT JOIN (
    SELECT SavingsToPayJunction.ContractorId, ROUND(SUM(ContractorSavings.value - ContractorSavings.paidOutValue), 2) as val, 
    FROM ContractorSavings 
    LEFT JOIN SavingsToPayJunction ON SavingsToPayJunction.SavingsId = ContractorSavings.id 
    WHERE fullyPaid = 0 
    AND ContractorSavings.type_id = 2 
    GROUP BY SavingsToPayJunction.ContractorId 
) as Tax_Saving_Balance ON Tax_Saving_Balance.ContractorId = Contractor.id 
LEFT JOIN (
    SELECT SavingsToPayJunction.ContractorId, ROUND(SUM(ContractorSavings.value - ContractorSavings.paidOutValue), 2) as val 
    FROM ContractorSavings 
    LEFT JOIN SavingsToPayJunction ON SavingsToPayJunction.SavingsId = ContractorSavings.id 
    WHERE fullyPaid = 0 
    AND ContractorSavings.type_id = 2 
    GROUP BY SavingsToPayJunction.ContractorId 
) as Holiday_Saving_Balance ON Holiday_Saving_Balance.ContractorId = Contractor.id 
LEFT JOIN (
    SELECT Contractor_ID, ROUND(SUM(Expense_Value),2) as val 
    FROM Contractor_Expenses 
    WHERE authorised = 0 
    GROUP BY Contractor_ID 
) as Expenses_Claimed ON Expenses_Claimed.Contractor_ID = Contractor.id 
LEFT JOIN (
    SELECT Contractor_ID, ROUND(SUM(Expense_Value - amountAllocated),2) as val 
    FROM Contractor_Expenses 
    WHERE authorised = 1 
    AND fullyAllocated = 0 
    GROUP BY Contractor_ID 
) as Expenses_Authorised ON Expenses_Authorised.Contractor_ID = Contractor.id 
LEFT JOIN (
    SELECT Contractor_ID, MAX(Contractor_Pay.Date_Earned) 
    FROM Contractor_Pay 
    GROUP BY Contractor_ID 
) as Last_Pay_Date ON Last_Pay_Date.Contractor_ID = Contractor.Unique_ID