主页 / dba / 问题 / 317155
Accepted
GabrielNexT
Asked: 2022-09-21 11:36:35 +0800 CST

如何避免左连接中的条件并优化查询？

  • 772

我在工作中遇到了一点问题，ORM 正在生成一个非常奇怪的查询，我需要稍微优化它的结果。我写了一个更具可读性的查询版本，结果相似，但执行时间仍然相同。

原始查询
解释详细

这是当前格式化的查询，它返回正确的值，但执行大约需要 13 秒。

select count(*)
from reservation as r
         JOIN companyclient as cc ON r.companyclientid = cc.companyclientid
         JOIN reservationitem as ri ON r.reservationid = ri.reservationid
         LEFT JOIN billingaccount as bi
                   ON (r.reservationid = bi.reservationid AND cc.companyclientid = bi.companyclientid AND
                       bi.propertyid = '84'
                       AND NOT bi.isdeleted
                       AND bi.groupkey = bi.billingaccountid
                       AND bi.billingaccounttypeid = '3'
                       AND bi.reservationid IS NOT NULL
                       AND bi.statusid = '1')
WHERE r.propertyid = '84'
  AND NOT r.isdeleted
  AND r.companyclientid is not null
  AND ri.tenantid = '025aa64f-67fb-4c23-b975-2b0fc3f5d65a'
  AND NOT ri.isdeleted
  AND ri.reservationitemstatusid NOT IN (6, 3, 7, 8);

解释详细

这是我写的尝试优化的版本（从13秒到5秒），避开了left join里面的条件，但是结果和原来的查询不一样。第一个查询返回 29490，第二个查询返回 29397。

select count(*)
from reservation as r
         JOIN companyclient as cc ON r.companyclientid = cc.companyclientid
         JOIN reservationitem as ri ON r.reservationid = ri.reservationid
         LEFT JOIN billingaccount as bi
                   ON (r.reservationid = bi.reservationid AND cc.companyclientid = bi.companyclientid)
WHERE r.propertyid = '84'
  AND NOT r.isdeleted
  AND r.companyclientid is not null
  AND ri.tenantid = '025aa64f-67fb-4c23-b975-2b0fc3f5d65a'
  AND NOT ri.isdeleted
  AND ri.reservationitemstatusid NOT IN (6, 3, 7, 8)
  AND (bi is null or bi.propertyid = '84'
  AND NOT bi.isdeleted
  AND bi.groupkey = bi.billingaccountid
  AND bi.billingaccounttypeid = '3'
  AND bi.reservationid IS NOT NULL
  AND bi.statusid = '1')

解释详细

我的问题是，如何优化第一个查询，我尝试了一些方法，但没有取得多大成功。我知道计数是线性的，它的时间基于查询返回的大小，但我认为对于 30K 行查询来说太慢了。

在这种特定情况下，我需要项目总数来计算限制偏移分页中的页数。

提前感谢所有耐心阅读的人，我接受任何帮助。

示例中使用的表大小：

  • 预留：288549 行
  • companyclient: 50614行
  • 预留项：387820 行
  • 计费帐户：772521 行
postgresql postgresql-performance

2 个回答

  1. 虽然这肯定是一个奇怪的查询，但使用该计划我们可以看到 billingaccount 上缺少一个索引，而无需了解整体查询的设计是什么。

    您不需要扫描和拒绝 7649 行来返回 84，因此过滤器中的某些列应该是索引的一部分。我们不知道过滤器的每个部分的选择性如何，但根据列名和常量，我猜您需要一个针对 companyclientid 和 propertyid 的多列索引。（并且可能还应该有 billingaccounttypeid 和 statusid ，它应该不会有太大的伤害并且可能会有所帮助）

    • 1
  2. Best Answer

    关于查询的一些想法。如果我们有表结构和索引，它会更有帮助（并且建议可能不同）：

    • 第二个查询不等于第一个查询，因此您得到不同的结果并不罕见。所以让我们坚持第一个查询：

        select count(*)
  from reservation as r
           JOIN companyclient as cc ON r.companyclientid = cc.companyclientid
           JOIN reservationitem as ri ON r.reservationid = ri.reservationid
           LEFT JOIN billingaccount as bi
                     ON (r.reservationid = bi.reservationid
                         AND cc.companyclientid = bi.companyclientid
                         AND bi.propertyid = '84'
                         AND NOT bi.isdeleted
                         AND bi.groupkey = bi.billingaccountid
                         AND bi.billingaccounttypeid = '3'
                         AND bi.reservationid IS NOT NULL
                         AND bi.statusid = '1')
  WHERE r.propertyid = '84'
    AND NOT r.isdeleted
    AND r.companyclientid is not null
    AND ri.tenantid = '025aa64f-67fb-4c23-b975-2b0fc3f5d65a'
    AND NOT ri.isdeleted
    AND ri.reservationitemstatusid NOT IN (6, 3, 7, 8);

    • 条件r.companyclientid is not null和bi.reservationid IS NOT NULL是多余的，因为这些列已经参与了连接条件，并且任何 NULL 值都不会通过连接条件。所以这两个可以安全地删除（但仍然检查你得到相同的结果）。

    • 条件（在cc.companyclientid = bi.companyclientid子查询内部）可以替换为，r.companyclientid = bi.companyclientid因为您在外部查询中有相等条件：r.companyclientid = cc.companyclientid。这在子查询中没有留下任何cc列，并且所有子查询的条件仅涉及 2 个表，bi并且它可能有助于 Postgres 的规划器获得更简单的计划（特别是如果和r上有合适的索引。billingaccountreservation

    • 当且仅当不可reservation. companyclientid为空并且有一个FOREIGN KEY约束REFERENCES companyclient (companyclientid)，那么我们可以完全删除 中的JOIN companyclient as cc ON r.companyclientid = cc.companyclientid任何行的因为reservation，外部保证只有一行companyclient并且计数不会受到影响。

    • 使用count(1)而不是count(*). 这只是某些版本中的一个小改进。

    如果以上所有都适用，查询将变为
    （并且不要忘记在每次修改后将结果与原始正确查询进行比较！）：

        select count(1)
    from reservation as r
             JOIN reservationitem as ri ON r.reservationid = ri.reservationid
             LEFT JOIN billingaccount as bi
                       ON (r.reservationid = bi.reservationid
                           AND r.companyclientid = bi.companyclientid
                           AND bi.propertyid = '84'
                           AND NOT bi.isdeleted
                           AND bi.groupkey = bi.billingaccountid
                           AND bi.billingaccounttypeid = '3'
                           AND bi.statusid = '1')
    WHERE r.propertyid = '84'
      AND NOT r.isdeleted
      AND ri.tenantid = '025aa64f-67fb-4c23-b975-2b0fc3f5d65a'
      AND NOT ri.isdeleted
      AND ri.reservationitemstatusid NOT IN (6, 3, 7, 8);

    现在让我们建议一些索引，特别是针对这个查询和每个表的索引：

    CREATE INDEX suggested_index_a ON
reservation
    (propertyid, reservationid, companyclientid)
WHERE (NOT isdeleted) ;

CREATE INDEX suggested_index_b ON
reservationitem
    (tenantid, reservationid) 
INCLUDE (reservationitemstatusid)
WHERE (NOT isdeleted) ;

CREATE INDEX suggested_index_c ON
billingaccount
    (propertyid, billingaccounttypeid, statusid,
     reservationid, companyclientid)
WHERE (groupkey = billingaccountid 
       AND NOT isdeleted) ;

    请尝试添加上述索引。请记住，任何索引都会消耗磁盘空间（以及使用时的内存空间），因此这是一种权衡。

    • 0

相关问题