sql - BugZilla，在给定日期获取错误状态(等)的最佳(最快)方式

Question

我正在开发一个从各种来源获取数据并生成报告的应用程序。目前我正在更改它以根据历史上给定日期的数据生成报告，以前它只显示今天事物状态的数据。

我的数据源之一是 Bugzilla，所以我需要获取历史上给定日期的 Bugzilla 数据。我有一个到 Bugzilla 数据库的只读连接，但没有简单的方法可以对服务器执行任何其他操作(例如安装插件或将程序放入数据库中)。报告服务器和 Bugzilla 服务器之间的连接也很慢，所以我想在服务器上进行计算，而不是在报告服务器上获取数据和解决问题。

实际上，我的工作速度几乎可以接受，但我不确定我是否以最好的方式或“正确”的方式工作，我担心速度可能会随着我们添加更多问题而不再可接受数据库。

所以，我的解决方案如下——你会怎么做。

对于一些背景知识，Bugzilla 将所有错误的当前状态存储在一个表中(称为“bugs”)和一个表(“bugs_activity”)中每个字段的更改历史记录，如下所示:

fieldid   INTEGER,   -- References the fielddefs table
bug_when  TIMESTAMP, -- Time the change happend
added     TEXT,      -- New text for the field
removed   TEXT,      -- Old text for the field

Bugzilla 数据库是 MySQL。我认为正确的方法是使用存储过程或临时表，但我没有可用的选项。我知道还有用于 Bugzilla 的报告工具，但我无权安装它们，还有我生成的报告与来自其他来源的数据(并具有特定格式)相关联。

报告服务器上有一个本地 PostgreSQL 数据库，所以我可以定期将所有数据镜像到那里，但我真的不想这样做，因为在两个地方存储相同的数据似乎有点浪费。

我的解决方案是在子选择中构建一个看起来像正常错误表的表(对于我对给定报告感兴趣的数据)，然后使用此选择作为正常选择的源，其工作方式与查询相同报告基于今天的数据。

SELECT bug_status, bug_id, op_sys, resolution, rep_platform   
  FROM (SELECT bug_id, 
        IFNULL((SELECT removed FROM bugs_activity a, fielddefs f  
                 WHERE a.fieldid = f.id   
                   AND bug_id = b.bug_id AND f.name = 'bug_status' 
                   AND bug_when >= '2012-01-01 00:00:00'  
                 ORDER BY bug_when DESC LIMIT 1), bug_status) AS bug_status,
    -- Repeat IFNULL clause for op_sys, resolution and rep_platform
        FROM bugs b 
        WHERE b.creation_ts <= '2012-01-01 00:00:00' ) bug_subselect
        -- Some other filters to reduce the bugs (specific product, ect)
      )
    -- More filters based on the new values that have been derived
     ;

然后我将其用作计算不同状态等的选择的输入。

这个查询结果太慢了，我假设是因为它正在获取内部选择的全部结果，所以它可以订购然后给我最重要的结果。

我确实尝试通过将 bugs_activity 表左连接到 bugs 表几次，然后对结果执行 IFNULL 查询，这很快但在生成代码中维护有点复杂，因此将其调整为:

SELECT bug_status, bug_id, op_sys, resolution, rep_platform   
  FROM (SELECT bug_id,
    IFNULL((SELECT removed FROM bugs_activity a, fielddefs f 
             WHERE a.fieldid = f.id AND bug_id = b.bug_id AND f.name = 'bug_status'
               AND bug_when = (
                     SELECT MIN(bug_when) FROM bugs_activity a, fielddefs f 
                      WHERE a.fieldid = f.id 
                            AND bug_id = b.bug_id 
                            AND f.name = 'bug_status'
                        AND bug_when >= '2012-01-01 00:00:00' 
                          LIMIT 1 
                        )
             LIMIT 1), bug_status) AS bug_status,
        -- Repeat IFNULL clause for op_sys, resolution and rep_platform
        FROM bugs b 
        WHERE b.creation_ts <= '2012-01-01 00:00:00' ) bug_subselect
        -- Some other filters to reduce the bugs (specific product, ect)
      )
    -- More filters based on the new values that have been derived
      ;

您需要两个 LIMIT 1(我认为)，因为某些字段设法在同一时间戳上进行了两次更改(数据库故障，可能来自升级，或者两个用户编辑了相同的错误——我不确定，我只知道它在那里，我需要处理它)。

这在没有过滤器的情况下运行大约 3 秒以减少错误列表(这是最坏的情况，几乎永远不会发生)，并且使用过滤器运行得更快。 LEFT JOIN 版本的运行速度大致相同(稍慢)，所以我选择了上面的那个。目前还可以，但我可以看到它在 future 会变慢——我将在 GUI 中添加一条加载消息，并且已经有一条消息说这些报告可能需要更长的时间来生成，我只是想知道我是否'我错过了一些让它更快的技巧。

Answer 1

如果我正确理解你，你可以试试这个..

SET @tdate = '2012-01-01 00:00:00';

SELECT  
  b.bug_id
  ,CASE 
    WHEN s.removed IS NULL THEN b.bug_status
    ELSE s.removed
  END AS statusAtDate
  ,CASE 
    WHEN o.removed IS NULL THEN b.op_sys
    ELSE o.removed
  END AS apSysAtDate
FROM
  bugs AS b 
  LEFT OUTER JOIN (
    SELECT 
      a.bug_id
      ,a.bug_when
      ,a.removed
      ,a.bug_when
      ,@row_num := IF(@last=a.bug_id,@row_num+1,1) AS rnk
      ,@last:=a.bug_id
    FROM 
      bug_activity AS a
      INNER JOIN fielddefs AS f
        ON a.fieldid = f.id
          AND f.name = 'bug_status'
    WHERE
        a.bug_when <= @tdate
    ORDER BY 
      a.bug_id
      ,a.bug_when
    ) AS s
      ON b.bug_id = s.bug_id
      AND s.rnk=1
  LEFT OUTER JOIN (
    SELECT 
      a.bug_id
      ,a.bug_when
      ,a.removed
      ,a.bug_when
      ,@row_num := IF(@last=a.bug_id,@row_num+1,1) AS rnk
      ,@last:=a.bug_id
    FROM 
      bug_activity AS a
      INNER JOIN fielddefs AS f
        ON a.fieldid = f.id
          AND f.name = 'op_sys'
    WHERE
        a.bug_when <= @tdate
    ORDER BY 
      a.bug_id
      ,a.bug_when
    ) AS o
      ON b.bug_id = o.bug_id
      AND o.rnk=1

--repeat for resolution and rep_platform

抱歉，我这里没有数据库来验证代码，如果有拼写错误或类似内容，请见谅。

我不知道这是否是您之前进行左外连接的方式，但是如果您使用 session 变量进行重用，这是否有帮助/工作？

不确定这是否会有所帮助，因为您说您的左外连接方法无论如何都以相同的速度运行..也许 mysql 查询优化器可以找到更好的方法来做到这一点:/

顺便说一句，我不是优化专家(远非如此)。只是说除了在旅途中获取一些索引的好建议之外我会尝试什么。

编辑:

你可以尝试的另一件事..我认为这应该有效..

SELECT
  bug_id
  ,bug_status
  ,op_sys
  ,max(old_status)
  ,max(old_opSys)
(
SELECT  
  *
FROM
  bugs AS b 
  LEFT OUTER JOIN (
    SELECT 
      a.bug_id
      ,a.bug_when
      ,if(f.name = 'bug_status',a.removed,NULL) AS old_status
      ,if(f.name = 'op_sys',a.removed,NULL) AS old_opSys
      ,a.bug_when
      ,@row_num := IF(@last=a.bug_id AND@lastField=f.name ,@row_num+1,1) AS rnk
      ,@last:=a.bug_id
      ,@lastField:=f.name
    FROM 
      bug_activity AS a
      INNER JOIN fielddefs AS f
        ON a.fieldid = f.id

    WHERE
        a.bug_when <= '2012-01-01 00:00:00'
        AND f.name in( 'bug_status','op_sys')
    ORDER BY 
      a.bug_id
      ,f.name
      ,a.bug_when
    ) AS s
      ON b.bug_id = s.bug_id
      AND s.rnk=1
) AS T
  GROUP BY
    bug_id
    ,bug_status
    ,op_sys

我在这里省略了外部 select 中的 case 或 if 语句。即使这有效，您可能不会选择它，但它可能值得检查。

就像这样:

<%= row->old_status ?: row->bug_status %>

(对不起，如果我的 PHP 关闭了.. 并没有真正使用它)

看起来它应该工作？ http://sqlfiddle.com/#!2/eff8c/1