如何优化 SQL 查询

Question

我是一个会计出身的编程新手。我写了这个查询，它可以工作，但非常缓慢。我想知道是否有一种方法可以优化它的速度。

我要从以下表中获取数据：

主表

事件表

结果表

认证表

以下是查询：

SELECT a.lgName AS lgname
  , a.wardName AS wardName
  , a.pUnitName AS pUnitName
  , SUM(a.pvc_collected) AS pvc
  , SUM(a.voter_reg_no) AS purvs
  , (   SELECT COUNT(pUnitName)
        FROM master
        WHERE wardName = a.wardName) AS No_Poll_Unitss
  , (   SELECT COUNT(reportedpu)
        FROM master
        WHERE wardName = a.wardName
        AND reportedpu = 1) AS reportedpu
  , (   SELECT COUNT(pUnitName)
        FROM master
        WHERE pUnitName = a.pUnitName) AS No_Poll_Unitss
  , (   SELECT COUNT(reportedpu)
        FROM master
        WHERE pUnitName = a.pUnitName
        AND reportedpu = 1) AS reportedpu
  , (   SELECT SUM(total)
        FROM accreditation
        WHERE pu_name IN (   SELECT pUnitName
                             FROM master
                             WHERE pUnitName = a.pUnitName)) AS acr
  , (   SELECT SUM(cno)
        FROM res
        WHERE pUnitName IN (   SELECT pUnitName
                               FROM master
                               WHERE pUnitName = a.pUnitName)
        AND category = 'tvc') AS tvc
  , (   SELECT SUM(cno)
        FROM res
        WHERE pUnitName IN (   SELECT pUnitName
                               FROM master
                               WHERE pUnitName = a.pUnitName)
        AND category = 'apc') AS apc
  , (   SELECT SUM(cno)
        FROM res
        WHERE pUnitName IN (   SELECT pUnitName
                               FROM master
                               WHERE pUnitName = a.pUnitName)
        AND category = 'ivc') AS ivc
  , (   SELECT SUM(cno)
        FROM res
        WHERE pUnitName IN (   SELECT pUnitName
                               FROM master
                               WHERE pUnitName = a.pUnitName)
        AND category = 'pdp') AS pdp
  , (   SELECT SUM(cno)
        FROM res
        WHERE pUnitName IN (   SELECT pUnitName
                               FROM master
                               WHERE pUnitName = a.pUnitName)
        AND category = 'lp') AS lp
  , (   SELECT SUM(cno)
        FROM res
        WHERE pUnitName IN (   SELECT pUnitName
                               FROM master
                               WHERE pUnitName = a.pUnitName)
        AND category = 'adc') AS adc
  , (   SELECT SUM(cno)
        FROM res
        WHERE pUnitName IN (   SELECT pUnitName
                               FROM master
                               WHERE pUnitName = a.pUnitName)
        AND category = 'sdp') AS sdp
  , (   SELECT SUM(cno)
        FROM res
        WHERE pUnitName IN (   SELECT pUnitName
                               FROM master
                               WHERE pUnitName = a.pUnitName)
        AND category = 'adp') AS adp
  , (   SELECT SUM(cno)
        FROM res
        WHERE pUnitName IN (   SELECT pUnitName
                               FROM master
                               WHERE pUnitName = a.pUnitName)
        AND category = 'other') AS oth
FROM master AS a
GROUP BY a.pUnitName, a.userCode
ORDER BY a.userCode ASC;

- wealth ouseinfo

---

你需要提供相关的表结构，说明你想要实现什么，一些样本数据，期望的输出以及当前查询的EXPLAIN语句结果！ - Madhur Bhaiya

乍一看，这个查询很差，因为它包含太多子查询，而每一行可能会运行多次。因此，根据您的最小可重现示例（MVCE），完全可以提出另一种解决方案！ - Madhur Bhaiya

@MadhurBhaiya，我已经完成了。 - wealth ouseinfo

Answer 1

查询操作有些棘手，但我首先尝试的是将所有这些子查询放入一个子查询中，方法如下：

SELECT *
FROM master AS A
CROSS JOIN (   SELECT SUM(IF(category = 'adp', cno, 0)) AS adp
                 -- All other conditions
                 , SUM(IF(category = 'other', cno, 0)) AS other
               FROM res AS R
               WHERE pUnitName IN (   SELECT pUnitName
                                      FROM master
                                      WHERE pUnitName = A.pUnitName)
               AND category = 'other') AS oth;

这应该只运行一次从res表中选择，并为您想要的每个条件计算总和。此外，肯定可以从子查询中删除pUnitName条件，但这需要更多了解您的数据集。

- Evaldas Buinauskas

Answer 2

我们这种情况下最好的方法是在执行连接操作之前进行聚合。您需要四个不同的聚合：

• 按 pUnitName 聚合 master
• 按 WardName 聚合 master
• 按 pUnitName 聚合 accreditation
• 按 pUnitName 聚合 res

然后将它们使用 LEFT JOIN 连接起来：

SELECT m.pUnitName, m.userCode,
       SUM(m.pvc_collected) as pvc,
       SUM(m.voter_reg_no) AS purvs,
       mw.Num_Poll_Units, mw.reportedpu,
       mu.Num_Poll_Units, mu.reportedpu,
       a.acr,
       r.tvc, r.apc, . . .
FROM master m LEFT JOIN
     (SELECT m2.wardName, COUNT(*) as Num_Poll_Units,
             SUM(m2.reportedpu = 1) as reportedpu
      FROM master m2
      GROUP BY m2.wardName
     ) mw
     USING (wardname) LEFT JOIN
     (SELECT m2.pUnitName, COUNT(*) as Num_Poll_Units,
             SUM(m2.reportedpu = 1) as reportedpu
      FROM master m2
      GROUP BY m2.pUnitName
     ) mu
     USING (pUnitName) LEFT JOIN
     (SELECT a.pu_name, SUM(a.total) as acr
      FROM accreditation a
      GROUP BY a.pu_name
     ) a
     ON a.pu_name = m.pUnitName LEFT JOIN
     (SELECT r.pUnitName,
             SUM(CASE WHEN category = 'tvc' THEN cno ELSE 0 END) as tvc,
             SUM(CASE WHEN category = 'apc' THEN cno ELSE 0 END) as apc,
             . . .
      FROM res r
      GROUP BY r.pUnitNmae
     ) r
     USING (pUnitName)
GROUP BY m.pUnitName, m.userCode,
         mw.Num_Poll_Units, mw.reportedpu,
         mu.Num_Poll_Units, mu.reportedpu,
         a.acr,
         r.tvc, r.apc, . . .
ORDER BY m.userCode ASC;

注意：最外层查询没有按WardName聚合。不清楚您真正想要这些列的内容。您可能需要执行以下操作：

• 从GROUP BY中删除mw.Num_Poll_Units和mw.reportedpu。
• 将SELECT更改为SUM(mw.Num_Poll_Units)或AVG(mw.Num_Poll_Units)或适当的函数。

- Gordon Linoff

Answer 3

需要完全优化查询。以下是一些使其优化的要点：

1. 使用交叉应用而非子查询
2. 使用窗口函数
3. 您还可以创建一些索引

您可以在互联网上搜索相关主题，或者提出您的问题。

- mohabbati