代码之家 › 专栏 › 技术社区 › user6824563

使用SUM()的MySQL查询速度较慢

query-performance mysql

user6824563 · 技术社区 · 6 年前

我必须在代码中运行两个查询才能获得租户余额。但是,这些查询速度太慢。

第一个查询,我得到所有租户及其单位名称:

SELECT t.TenantID 
  FROM Tenants t
  JOIN Units u
    ON t.UnitID = u.UnitID
 Where t.Prospect = 2 
   AND t.PropertyID = 8
 ORDER 
    BY CONCAT(Left(Replace(UnitName,'-',''),2),
              REPEAT('0', (10-CHAR_LENGTH(UnitName))),
              Right(Replace(UnitName,'-',''),
                    CHAR_LENGTH(Replace(UnitName,'-',''))-2
             )     )

它返回500行

然后我得到了4种条件下的余额。此查询将位于第一个查询循环内:

Select
        SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID != 6 THEN TransactionAmount ELSE 0 END) AS TotalDebit,
        SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID = 6 THEN TransactionAmount ELSE 0 END) AS HousingDebit,
        SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID != 6 THEN TransactionAmount ELSE 0 END) AS TotalCredit,
        SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID = 6 THEN TransactionAmount ELSE 0 END) AS HousingCredit
    From TenantTransactions
    Where TenantID= FirstQuery.TenantID

我的查询是否做错了?跑步大约需要1分钟。

2 回复 | 直到 6 年前

O. Jones 6 年前

使用在单个查询中执行此操作 GROUP BY .

尝试以下操作:

SELECT t.TenantID, TotalDebit, HousingDebit, TotalCredit, HousingCredit
  FROM Tenants t
  JOIN Units u ON t.UnitID = u.UnitID
  LEFT JOIN (
        Select
          TenantID,
          SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID != 6 THEN TransactionAmount ELSE 0 END) AS TotalDebit,
          SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID = 6 THEN TransactionAmount ELSE 0 END) AS HousingDebit,
          SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID != 6 THEN TransactionAmount ELSE 0 END) AS TotalCredit,
          SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID = 6 THEN TransactionAmount ELSE 0 END) AS HousingCredit
        From TenantTransactions
       Group By TenantID
       ) sums ON sums.TenantID = t.TenantID
 Where t.Prospect = 2 
   AND t.PropertyID = 8
 ORDER 
    BY CONCAT(Left(Replace(UnitName,'-',''),2),REPEAT('0', (10-CHAR_LENGTH(UnitName))),Right(Replace(UnitName,'-',''),CHAR_LENGTH(Replace(UnitName,'-',''))-2))

内部查询可能仍会运行一段时间,但只会运行一次。

在上尝试复合覆盖索引 TenantTransactions 包含以下列: (TenantID, TransactionTypeID, ChargeTypeID, TransactionAmount) 使用其中的和优化查询。

在上尝试复合索引 Tenants 使用列 (PropertyID, Prospect) 在里面。

Ctznkane525 6 年前

这里有另一种使用子查询的方法。您知道,性能问题可能不是数据库性能,而是数据库和应用程序服务器之间的来回。因此,这是一个单一查询将有所帮助的地方。

SELECT t.TenantID,
  (SELECT SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID != 6 THEN TransactionAmount ELSE 0 END) From TenantTransactions TT WHERE TT.TenantID=t.TenantID) AS TotalDebit,
  (SELECT SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID = 6 THEN TransactionAmount ELSE 0 END) From TenantTransactions TT WHERE TT.TenantID=t.TenantID) AS HousingDebit,
  (SELECT SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID != 6 THEN TransactionAmount ELSE 0 END) From TenantTransactions TT WHERE TT.TenantID=t.TenantID) AS TotalCredit,
  (SELECT SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID = 6 THEN TransactionAmount ELSE 0 END) From TenantTransactions TT WHERE TT.TenantID=t.TenantID) AS HousingCredit  
  FROM Tenants t
  JOIN Units u
    ON t.UnitID = u.UnitID
 Where t.Prospect = 2 
   AND t.PropertyID = 8
 ORDER 
    BY CONCAT(Left(Replace(UnitName,'-',''),2),REPEAT('0', (10-CHAR_LENGTH(UnitName))),Right(Replace(UnitName,'-',''),CHAR_LENGTH(Replace(UnitName,'-',''))-2))