MySQL:查询优化

Question

SELECT a.FundIDRecv,a.SubscribeDt, b.FundName, 
(
    SELECT SUM(c.PricePerWeek) 
    FROM tbl_Hive c
    WHERE c.FundID IN 
    (
        SELECT FundID from tbl_FundStatic 
        WHERE FundID IN
        (
            SELECT FundIDSend
            FROM tbl_FundSubscriptions 
            WHERE FundIDRecv = a.FundIDRecv
            AND SubscribeDt >= subdate(CURDATE(), INTERVAL weekday(CURDATE()) DAY)
        )
        AND UserID = '14'
    )
) as Price
FROM tbl_FundSubscriptions a, tbl_Hive b
WHERE a.FundIDRecv = b.FundID
AND a.SubscribeDt >= subdate(CURDATE(), INTERVAL weekday(CURDATE()) DAY)
AND a.FundIDRecv IN
(
    SELECT FundIDRecv
    FROM tbl_FundSubscriptions
    WHERE FundIDSend IN (
        SELECT FundID
        FROM tbl_FundStatic
        WHERE UserID = '14'
    )
)
GROUP BY FundIDRecv

此查询需要大量时间来获取数据。

我如何优化此查询以使其执行和获取结果的速度比它更快？

Answer 1

用 JOIN 子句替换您的嵌套选择。以此为例:

AND a.FundIDRecv IN
(
    SELECT FundIDRecv
    FROM tbl_FundSubscriptions
    WHERE FundIDSend IN (
        SELECT FundID
        FROM tbl_FundStatic
        WHERE UserID = '14'
    )
)

为什么不像这样将 tbl_FundStatic 连接到外部查询

FROM tbl_FundSubscriptions a
JOIN tbl_FundStatic s ON (a.FundIDSend = s.FundID)
WHERE s.UserID = '14'

当然，我不知道这是否仍然正确，但它会给你一个想法。此外，您应该避免在 SELECT 子句本身中进行嵌套选择。最好加入tbl_FundStatic，然后从中选择字段