Neo4j 多个可选匹配查询非常慢

Question

我是 neo4j 的新手，我很难优化返回大量节点/关系的查询。

以下查询:

MATCH (u:User)-[:CAN_ADMINISTER]->(cs:CustomerSite)
WHERE u.id="1234" WITH cs
MATCH r1=(cs)<-[:AFFECTS_SITE]-(t:Ticket)
WHERE not(t.status = "COMPLETE")
OPTIONAL MATCH r2=(t)-[:HAS_EVENTS]->(te:TicketEvent)
OPTIONAL MATCH r3=(t)-[:CREATED_BY]->(u:User)
OPTIONAL MATCH r4=(te)<-[:HAS_EVENTS]-(u2:User)
OPTIONAL MATCH r5=(t)-[:AFFECTS_SITE]->(cs)<-[:HAS_SITE]-(c:Customer)
RETURN r1, r2, r3, r4, r5

为生成约 7000 行的用户运行将近一分钟。我试图重组它但收效甚微。以下是当前的配置文件。

有什么建议可以帮到这里吗？

Answer 1

我强烈建议您根据需要从您的可选匹配中收集结果，并使用 WITH 分解您的查询并缩小您感兴趣的列的范围，以将您的行保留在子查询之间。正如问题评论中所解释的那样，MATCHES 和 OPTIONAL MATCHES 可以构建结果行，这可以使查询看起来像它们应该很快变得更昂贵。

例如，我将添加注释以内联分析原始查询:

MATCH (u:User)-[:CAN_ADMINISTER]->(cs:CustomerSite)
WHERE u.id="1234" WITH cs
MATCH r1=(cs)<-[:AFFECTS_SITE]-(t:Ticket)
WHERE not(t.status = "COMPLETE")
// we have 1 row per User at a CustomerSite
OPTIONAL MATCH r2=(t)-[:HAS_EVENTS]->(te:TicketEvent)
// now, 1 row per User @ CustomerSite per TicketEvent
OPTIONAL MATCH r3=(t)-[:CREATED_BY]->(u:User)
// the above OPTIONAL MATCH had to iterate over each User/CS/TE row instead of just each distinct TICKET
OPTIONAL MATCH r4=(te)<-[:HAS_EVENTS]-(u2:User)
// now, 1 row per User @ CustomerSite per User on each Ticket Event
OPTIONAL MATCH r5=(t)-[:AFFECTS_SITE]->(cs)<-[:HAS_SITE]-(c:Customer)
// now, 1 row per User @ CustomerSite per User on each Ticket Event per Customer at each Customer Site
RETURN r1, r2, r3, r4, r5

虽然它改变了返回数据的格式，但同时进行收集，并更好地排序您的可选匹配，应该会提高查询速度。这是您可以执行此操作的一种方法:

MATCH (u:User)-[:CAN_ADMINISTER]->(cs:CustomerSite)
WHERE u.id="1234" WITH cs
MATCH (cs)<-[:AFFECTS_SITE]-(t:Ticket)
WHERE not(t.status = "COMPLETE")
// should be 1 creator per ticket, so best to do this first
OPTIONAL MATCH (t)-[:CREATED_BY]->(creator:User)
OPTIONAL MATCH (cs)<-[:HAS_SITE]-(affectedCustomer:Customer)
// collection of affected customers for each ticket (and their creator) affecting a customer site
WITH cs, t, creator, COLLECT(affectedCustomer) as affectedCustomers
OPTIONAL MATCH (t)-[:HAS_EVENTS]->(te:TicketEvent)<-[:HAS_EVENTS]-(userOnEvent:User)
WITH cs, t, creator, affectedCustomers, te, COLLECT(userOnEvent) as usersOnEvent
RETURN cs, t, creator, affectedCustomers, COLLECT({ticketEvent:te, usersOnEvent:usersOnEvent}) as ticketEventsAndUsers

每一行都对应于客户站点的票证、票证的创建者、站点上受影响的客户集合，以及票证和事件的票证事件集合、该事件的用户。

试试看性能比较。如果它看起来更好，您将不得不更改解析返回数据的方式，但这不是一两个 for 循环无法处理的。