HQL查询性能优化求助：替代Union处理大Inbox表慢查询

先提个关键问题：原查询里的or和and优先级有问题，i.inboxStatus = 0 and i.moduleStatus = :currentStatus只会作用于第二个in条件，得给两个in的条件套上括号，不然逻辑完全不对，这也是性能差的潜在原因之一。

下面是针对Inbox表数据量大的优化手段：

1. 把笛卡尔积改成显式关联，别搞全表交叉 #

原查询from PrReq p, Inbox i是笛卡尔积，相当于把两个表所有行交叉匹配，中间结果量爆炸，这绝对是耗时超10分钟的核心原因。改成先筛选符合条件的Inbox记录，再和PrReq关联。

2. 拆分OR条件，用代码层面合并模拟Union #

HQL确实不支持Union，但OR条件会让数据库没法用索引，不如拆成两个独立查询，查完在代码里合并去重：

第一个查PrKdn关联的情况： #

select p from PrReq p
join Inbox i on i.documentNo in (select kdn.kdnId from PrKdn kdn where kdn.prReqId = p.id)
where i.inboxStatus = 0 
  and i.moduleStatus = :currentStatus 
  and p.recvDept.code = :dept 
  and p.organization = :org
order by p.billToDept, p.prId ASC, p.createDate desc

第二个查PrGrn关联的情况： #

select p from PrReq p
join Inbox i on i.documentNo in (select grn.grnId from PrGrn grn where grn.prReqId = p.id)
where i.inboxStatus = 0 
  and i.moduleStatus = :currentStatus 
  and p.recvDept.code = :dept 
  and p.organization = :org
order by p.billToDept, p.prId ASC, p.createDate desc

然后用HashSet这类集合存两个查询的结果，自动去重，再按要求排序就行（也可以让数据库先排好序再合并，减少代码里的排序开销）。

3. 删掉多余的DISTINCT #

原查询子查询里的distinct完全没用，in操作本身会自动去重，留着只会让数据库多做无用功，直接删掉。

4. 加索引是必须的 #

给Inbox表建联合索引：documentNo、inboxStatus、moduleStatus，这三个字段一起查，索引能直接覆盖查询条件，不用回表。
另外给PrKdn的prReqId和kdnId，PrGrn的prReqId和grnId建联合索引，PrReq的recvDept.code、organization、billToDept、prId、createDate也建联合索引，关联和排序都能快很多。

5. 用EXISTS替代IN，大数据量更高效 #

IN子查询在数据多的时候性能拉胯，因为会先把所有子查询结果查出来再匹配，EXISTS是逐条判断，更适合大数据量：

比如PrKdn关联的查询可以改成：

select p from PrReq p
where exists (
    select 1 from Inbox i
    where i.inboxStatus = 0 
      and i.moduleStatus = :currentStatus
      and exists (
          select 1 from PrKdn kdn 
          where kdn.prReqId = p.id 
          and kdn.kdnId = i.documentNo
      )
)
and p.recvDept.code = :dept 
and p.organization = :org
order by p.billToDept, p.prId ASC, p.createDate desc

这种写法数据库能更快利用索引做关联判断，不用生成大量中间结果。

6. 能分页就分页 #

如果业务允许，别一次性查所有结果，用setFirstResult和setMaxResults做分页，单次查询数据量小了，耗时会直接降下来。

内容的提问来源于stack exchange，提问作者William Harry