主页 / dba / 问题 / 84620
Accepted
Toskan
Asked: 2014-12-09 09:34:52 +0800 CST

大型 SQL 在必须处理可能为 NULL 的条目时执行速度非常慢（290 行大约需要 2 秒）

  • 772
SELECT version_stamp, 
           pscdesc_id, 
           psc, 
           pscdesctext 
    FROM   pscdesc_history 
    WHERE  ( psc IN (SELECT psc 
                     FROM   pso_history AS c 
                     WHERE  ( c.project = 121 
                               OR c.project IS NULL ) 
                     AND EXISTS (SELECT * FROM   pso_history AS x 
                                        WHERE  x.project = 121 
                                        AND x.pso_id = c.pso_id )) 
              OR psc IS NULL ) 
              AND EXISTS (SELECT * FROM   pscdesc_history AS xyy 
                       WHERE  xyy.psc IN (SELECT psc 
                                          FROM   pso_history AS c 
                                          WHERE  ( c.project = 121 
                                                   OR c.project IS NULL ) 
                                                 AND EXISTS (SELECT * FROM   pso_history AS x 
                                                             WHERE  x.project = 121 
                                                                    AND 
                                                            x.pso_id = c.pso_id)) 
                       AND xyy.pscdesc_id = pscdesc_history.pscdesc_id )

也许在 prosa 我正在尝试做的事情：

表中的条目pscdesc_history表示更改。每个条目都是一个变化。

所以记录pscdesc_id可以改变，说pscdesctext被改变了。这给了我们一个新的条目，pscdesc_id但有一个新条目version_stamp，pscdesctext其余的可以是空的。这些条目（至关重要，因为我只想处理一个项目）属于哪个项目，我不知道。我得看看表pso_history。psc是第三个表的外键。

我为什么不加入？由于这是一个充满更改记录的表，因此实际的外键psc可能为空。这同样适用于pso_historyforgein 键project也可能为 null 的情况。

所以我必须获取所有与 121pscdesc_history的记录相关的记录或pso_history与pso_history.project121的记录相关的所有记录pso_history.project。

有什么你可以看到你认为我可以加快速度的地方吗？AND EXISTS用别的东西代替？

mysql performance

1 个回答

  1. Best Answer

    尽可能在任何地方使用 DISTINCT，我不明白为什么不加入而不是关联

                     SELECT psc 
                 FROM   pso_history AS c 
                 WHERE  ( c.project = 121 
                           OR c.project IS NULL ) 
                 AND EXISTS (SELECT * FROM   pso_history AS x 
                                    WHERE  x.project = 121 
                                    AND x.pso_id = c.pso_id )


                 SELECT DISTINCT c.psc
                 FROM pso_history AS c
                 JOIN pso_history AS x 
                 ON      ( c.project = 121 OR c.project IS NULL )
                    AND   x.project = 121 
                    AND   x.pso_id = c.pso_id

    整个查询将是

    SELECT  xyx.version_stamp, xyx.pscdesc_id, xyx.psc, xyx.pscdesctext 
FROM    pscdesc_history AS xyx
JOIN    pscdesc_history AS xyy
ON  (xyx.psc IN (SELECT DISTINCT c.psc
                 FROM pso_history AS c
                 JOIN pso_history AS X 
                 ON      ( c.project = 121 OR c.project IS NULL )
                    AND   x.project = 121 
                    AND   x.pso_id = c.pso_id) 
     OR xyx.psc IS NULL)
AND xyy.pscdesc_id = xyx.pscdesc_id
AND  xyy.psc IN ( SELECT DISTINCT c.psc
                 FROM pso_history AS c
                 JOIN pso_history AS X 
                 ON      ( c.project = 121 OR c.project IS NULL )
                    AND   x.project = 121 
                    AND   x.pso_id = c.pso_id)

    我认为这必须工作两倍，还要检查索引。

    • 1

相关问题