mysql - 为什么这个查询需要这么长时间才能执行

Question

我几个月前写了这个查询。它运行良好。但是，这个查询的执行速度一天比一天慢。

除了同表执行之外，此查询还在多个表中检查账单历史记录。

这是查询-

SELECT * FROM (
            SELECT *,                
            (SELECT username FROM users WHERE id = u_bills.UserId) AS username,
            (SELECT first_name FROM users WHERE id = u_bills.UserId) AS first_name,
            (SELECT last_name FROM users WHERE id = u_bills.UserId) AS last_name,
            (SELECT phone FROM users WHERE id = u_bills.UserId) AS phone,
            (SELECT email FROM users WHERE id = u_bills.UserId) AS email,
            (SELECT CPRate FROM cpt WHERE UserId = u_bills.UserId ORDER BY AddedDate DESC LIMIT 0,1) AS cprate,
            (SELECT (SELECT PopName FROM pops WHERE PopId = p.PopName) AS PopFullName FROM u_setupinfos AS p 
            WHERE UserId = u_bills.UserId) AS popname,
            (SELECT active FROM users WHERE id = u_bills.UserId) AS active,                
            (SELECT SUM(PaidAmount) AS PaidAmount FROM u_billhistory 
            WHERE UserId = u_bills.UserId AND MONTH(AddedDate) = MONTH(PaidDate)) AS PaidAmount,
            (SELECT PaidDate FROM u_billhistory 
             WHERE UserId = u_bills.UserId AND MONTH(AddedDate) = MONTH(PaidDate) 
             GROUP BY MONTH(PaidDate)) AS PaidDate,
            (SELECT PaymentMedia FROM u_billhistory 
            WHERE UserId = u_bills.UserId AND MONTH(AddedDate) = MONTH(PaidDate) 
            GROUP BY MONTH(PaidDate)) AS PaymentMedia,   
    (SELECT TransactionId FROM u_billhistory 
            WHERE UserId = u_bills.UserId AND MONTH(AddedDate) = MONTH(PaidDate) GROUP BY MONTH(PaidDate)) AS TransactionId,
            (SELECT GROUP_CONCAT(CONCAT(`BillHisId`,';',`PaidAmount`,';',`PaidDate`) separator '|') AS vals 
FROM u_billhistory 
WHERE UserId = u_bills.UserId 
AND YEAR(PaidDate) = YEAR(CURDATE())) AS TotalPaids
            FROM u_bills) AS m
        WHERE m.username = 'abc'

请检查图像中的EXPLAIN报告-

我想组织这个查询。我需要建议让这个查询更快，比如 3 到 5 秒而不是 1 分半钟或更多。我在这个查询中犯了什么错误？

重要提示-

我几乎为查询中使用的每一列编制了索引

Answer 1

它很慢，因为您要让它对结果中每条记录的每个字段运行一个单独的查找到另一个表中……有时不止一个。您应该使用 JOIN 而不是嵌套的 SELECT。对于某些分组结果，您可以加入一个 SELECT 语句，该语句执行它自己的 GROUP BY。

---

此外，单独列上的单独索引不要帮助!!

将索引想象成电话簿。一个简单的电话簿对姓氏和名字使用聚集索引。然后它还会列出每件商品的地址和电话号码。这就是聚簇索引:只是数据存储的顺序。

附加索引就像补充。 “电话号码”的非聚集索引就像书末的补充，按顺序列出电话号码，然后显示姓氏和名字(但不显示地址)。 “地址”上的索引会做同样的事情:一个单独的补充，按顺序列出地址，然后给你名字。

有了这些索引补充，如果你有电话号码想知道地址，你必须先在补充中查找电话号码才能找到姓名，然后在主簿中查找姓名才能得到地址。在此示例中，电话补充确实有帮助，因为它比电话簿的完整扫描更好。

但是，这里需要注意的重要一点是地址补充_does not有助于此搜索，即使地址是您想要查找的确切字段。它可能对其他搜索有用，但在这种情况下没有帮助。此外，请记住，您的图书的每次更新不仅需要更新图书，还需要更新所有的增刊。不仅仅是存储空间，还有更新每个索引所花费的时间。你需要考虑是否值得。如果您有一堆未使用的索引，最好不要使用它们。

---

回到您的具体问题，唯一真正能帮助您的索引是 u_bills.username、user.UserId(希望它的集群表)和 u_billhistory.UserId 和 u_billhistory.PaidDate 的单个索引。

这是重构此查询的初步尝试:

SELECT ub.*, u.username, u.first_name, u.last_name, u.phone, u.email, u.active,
    m.PaidAmount, m.PaymentMedia, m.TransactionId, 
    y.TotalPaids, p.PopName As PopFullName, cpt2.CPRate       

FROM u_bills ub
INNER JOIN users u on u.id = ub.UserId
INNER JOIN u_setupinfos usi on usi.UserId = ub.UserID
INNER JOIN pops p ON p.PopId = usi.PopName
INNER JOIN (
    SELECT UserId, MIN(AddedDate) As cptDate
    FROM cpt
    GROUP BY UserID        
) cpt1 ON cp1.UserID = ub.UserID
INNER JOIN (
    SELECT UserID, AddedDate, MIN(CPRate) AS CPRate
    FROM cpt
    GROUP BY UserID, AddedDate
) cpt2 ON cpt2.UserID = ub.UserID AND cpt2.AddedDate = cpt1.ctpDate
INNER JOIN (
    SELECT UserID, MONTH(PaidDate) PaidMonth, SUM(PaidAmount) AS PaidAmount,
        PaymentMedia, TransactionId --these two fields need an aggregate function of some type!
    FROM u_billhistory
    GROUP BY UserId, MONTH(PaidDate)
) m ON m.UserID = ub.UserId AND m.PaidMonth = MONTH(ub.AddedDate)
INNER JOIN (
    SELECT UserID, YEAR(PaidDate) As PaidYear, 
        GROUP_CONCAT(CONCAT(`BillHisId`,';',`PaidAmount`,';',`PaidDate`) separator '|') AS TotalPaids
    FROM u_billhistory
    WHERE YEAR(PaidDate) = YEAR(CurDate())
    GROUP BY UserId, YEAR(PaidDate)
) y ON Y.UserId = ub.UserId 
WHERE username='abc'

请注意，它还不够完整，因为我们没有关于您的架构和数据的所有信息，而且似乎缺少一个 GROUP BY 和两个缺少的聚合函数. MySql 在这方面很糟糕……在您修复这些问题之前，更好的数据库不会允许您的查询运行。