更快地运行 SQL 查询

Question

   SELECT projectID, urlID, COUNT(1) AS totalClicks, projectPage,
   (SELECT COUNT(1)
     FROM   tblStatSessionRoutes, tblStatSessions
     WHERE  tblStatSessionRoutes.statSessionID = tblStatSessions.ID
     AND    tblStatSessions.projectID = tblAdClicks.projectID
     AND    (tblStatSessionRoutes.leftPageID = tblAdClicks.projectPage OR
           tblStatSessionRoutes.rightPageID = tblAdClicks.projectPage)) AS totalViews
   FROM   tblAdClicks
   WHERE  projectID IN (SELECT projectID FROM tblProjects WHERE userID = 5)
   GROUP  BY projectID, urlID, projectPage
   ORDER  BY CASE projectID
          WHEN 170 THEN
           1
          ELSE
           0
      END, projectID

这绝不是一个特别复杂的查询，但由于数据库已规范化到良好的水平，并且我们正在处理大量数据，因此该查询对于用户来说可能非常慢。

有人有关于如何提高速度的建议吗？如果我策略性地对数据库的某些部分进行非规范化，这会有帮助吗？在存储过程中运行它会带来显着的改进吗？

我处理数据的方式在我的代码中是有效的，瓶颈确实在于这个查询。

谢谢！

Answer 1

对数据库进行非规范化应该是最后的手段，因为（仅选择一个原因）您不想鼓励非规范化所允许的数据不一致。

首先是看看能否从查询执行计划中得到一些线索。例如，可能是您的子选择成本过高，最好先将其放入临时表中，然后将其加入到主查询中。

此外，如果您看到大量表扫描，您可以从改进的索引中受益。

如果您还没有这样做，您应该花几分钟重新格式化您的查询以提高可读性。令人惊奇的是，在执行此操作时，明显的优化经常会突然出现在您面前。

Answer 2

我会尝试打破它

projectID IN (SELECT projectID FROM tblProjects WHERE userID = 5)

并使用 JOIN 代替：

 SELECT 
     projectID, urlID, COUNT(1) AS totalClicks, projectPage,
     (SELECT COUNT(1) ....) AS totalViews
 FROM
     dbo.tblAdClicks a
 INNER JOIN 
     dbo.tblProjects p ON a.ProjectID = p.ProjectID
 WHERE 
     p.UserID = 5
 GROUP BY 
     a.projectID, a.urlID, a.projectPage
 ORDER BY 
     CASE a.projectID
        WHEN 170 THEN 1
        ELSE 0
     END, a.projectID

不确定这会有多大帮助 - 我希望应该有所帮助！

除此之外，我会检查您是否在相关列上有索引，例如在 a.ProjectID （以帮助 JOIN）上，也许在 a.urlID 上以及a.ProjectPage（帮助使用GROUP BY）

Answer 3

如果您的 dbms 有一个可以解释其查询计划的工具，请首先使用该工具。 （您的第一个相关子查询可能每行运行一次。）然后确保 WHERE 子句中引用的每个列都有一个索引。

这个子查询——WHERE projectID IN (SELECT projectID FROM tblProjects WHERE userID = 5)——肯定可以从被剪切并实现为视图中受益。然后加入到视图中。

将点击流数据视为数据仓库应用程序并不罕见。如果您需要走这条路，我通常会实现一个单独的数据仓库，而不是对设计良好的 OLTP 数据库进行非规范化。

我怀疑将其作为存储过程运行会对您有所帮助。

Answer 4

我会尝试删除相关子查询（内部 (SELECT COUNT(1) ...)）。必须加入左侧页面或右侧页面匹配的会话路由会使事情变得有点棘手。大致如下（但我还没有测试过这一点）：

SELECT tblAdClicks.projectID, tblAdClicks.urlID, COUNT(1) AS totalClicks, tblAdClicks.projectPage,
       SUM(CASE WHEN leftRoute.statSessionID IS NOT NULL OR rightRoute.statSessionID IS NOT NULL THEN 1 ELSE 0 END) AS totalViews
FROM tblAdClicks
     JOIN tblProjects ON tblProjects.projectID = tblAdClicks.projectID
     LEFT JOIN tblStatSessions ON tblStatSessions.projectID = tblAdClicks.projectID
     LEFT JOIN tblStatSessionRoutes leftRoute ON leftRoute.statSessionID = tblStatSessions.ID AND leftRoute.leftPageID = tblAdClicks.projectPage
     LEFT JOIN tblStatSessionRoutes rightRoute ON rightRoute.statSessionID = tblStatSessions.ID AND rightRoute.rightPageID = tblAdClicks.projectPage
WHERE tblProjects.userID = 5
GROUP BY tblAdClicks.projectID, tblAdClicks.urlID, tblAdClicks.projectPage
ORDER BY CASE tblAdClicks.projectID WHEN 170 THEN 1 ELSE 0 END, tblAdClicks.projectID

如果我要添加一些缓存表来帮助实现这一点，正如我所指出的，我会尝试将左页和右页对 tblStatSessionRoutes 的两个查询减少到单个查询询问。例如，如果您知道 leftPageID 永远不会等于 rightPageID，则应该可以简单地使用触发器来填充另一个表，其中左视图和右视图位于不同的行中。