左连接优化

发布于 2024-12-16 11:23:24 字数 340 浏览 0 评论 0原文

我有一个类似于下面简化的数据库。我需要检索列：col8、col9、col10、col11、col12（我圈出的列

目前，我使用左联接来联接每个表，但这会导致查询花费很长时间（有很多分析时，最大的影响是写入 tmp 表

由于左连接，它仍然将数千条记录复制到 tmp 表。

，但 href="http://oberto.co.nz/demo/assets/db2.jpeg" rel="nofollow noreferrer">http://oberto.co.nz/demo/assets/db2.jpeg

可以这是否可以优化以仍然使用 pk 连接从每个表中检索圈出的列？

谢谢。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

舟遥客 2024-12-23 11:23:24

SELECT a.col12, b1.col8, c1.col9, d1.col10, e1.col11
FROM a
INNER JOIN (SELECT b.col8, b.col2, b.col3 FROM b 
            WHERE b.col2 = a.col2 GROUP BY b.col8) b1 
        ON (b1.col2 = a.col2)
INNER JOIN (SELECT c.col9, c.col3, c.col4 FROM c 
            WHERE c.col3 = b1.col3 GROUP BY c.col9) c1 
        ON (c1.col3 = b1.col3)
INNER JOIN (SELECT d.col10, d.col4 FROM d 
            WHERE d.col4 = c1.col4 GROUP BY d.col10) d1 
        ON (d1.col4 = c1.col4)
INNER JOIN (SELECT e.col11, e.col6 FROM e 
            WHERE e.col6 = a.col6 GROUP BY e.col11) e1 
        ON (e1.col6 = a.col6)

现在您将不再有重复的行。
您可能必须尝试使用 LEFT 而不是 INNER 连接。
如果您不需要子选择，您应该消除它，因为它会减慢速度。

SELECT a.col12, b1.col8, c1.col9, d1.col10, e1.col11
FROM a
INNER JOIN (SELECT b.col8, b.col2, b.col3 FROM b 
            WHERE b.col2 = a.col2 GROUP BY b.col8) b1 
        ON (b1.col2 = a.col2)
INNER JOIN (SELECT c.col9, c.col3, c.col4 FROM c 
            WHERE c.col3 = b1.col3 GROUP BY c.col9) c1 
        ON (c1.col3 = b1.col3)
INNER JOIN (SELECT d.col10, d.col4 FROM d 
            WHERE d.col4 = c1.col4 GROUP BY d.col10) d1 
        ON (d1.col4 = c1.col4)
INNER JOIN (SELECT e.col11, e.col6 FROM e 
            WHERE e.col6 = a.col6 GROUP BY e.col11) e1 
        ON (e1.col6 = a.col6)

Now you will have no more duplicate rows.
You may have to experiment with LEFT instead of INNER joins.
And if you don't need a subselect you should eliminate it, because it slows things down.

回复收藏 0 原文

日暮斜阳 2024-12-23 11:23:24

在当前最简单的形式中，我将查询为...

SELECT STRAIGHT_JOIN
      a.col1,
      a.col12, 
      b.col8, 
      c.col9, 
      d.col10, 
      e.col11
   FROM a
      left join b
         on a.col2 = b.col2
         left join c
            on b.col3 = c.col3
            left join d
               on c.col4 = d.col4
      left join e
         on a.col6 = e.col6

但是，一旦我读回其他标准、过滤器、条件、左/内连接“要求”，将进行调整，这可能有助于进一步优化。

in its current and simplest form, I would have the query as...

SELECT STRAIGHT_JOIN
      a.col1,
      a.col12, 
      b.col8, 
      c.col9, 
      d.col10, 
      e.col11
   FROM a
      left join b
         on a.col2 = b.col2
         left join c
            on b.col3 = c.col3
            left join d
               on c.col4 = d.col4
      left join e
         on a.col6 = e.col6

However, will adjust once I read back on other criteria, filters, conditions, left/inner join "requirements" that may help optimize further.

回复收藏 0 原文

~没有更多了~