Neo4j Cypher查询优化：1000条Profile数据查询性能提升

我有一段自动生成的Cypher查询语句，在1000条Profile数据上执行耗时约8秒。经分析，Location与Employment相关的路径匹配部分是主要性能瓶颈。我已对查询初始部分进行两次重构优化，性能有所提升但仍有优化空间，现寻求进一步的优化方案。

原始查询 #

MATCH (dg:DecisionGroup {id: -2})-[rdgd:CONTAINS]->(childD:Decision:Profile ) 
MATCH (childD)-[:EMPLOYMENT_AS]->(root2:Employment ) 
WHERE root2.id IN ([1]) WITH DISTINCT childD, dg, rdgd  
MATCH path3=(root3:Location )-[:CONTAINS*0..]->(descendant3:Location) 
WHERE (descendant3.id IN ([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35]) OR root3.id IN ([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35])) 
UNWIND nodes(path3) AS pathNode3 WITH childD, dg, rdgd, COLLECT(DISTINCT pathNode3) AS pathNodes3 
MATCH (childD)-[:LOCATED_IN]->(pathNode3) WHERE pathNode3 IN pathNodes3 WITH DISTINCT childD, dg, rdgd  WHERE  (childD.`active` = true)  AND  (childD.`experienceMonths` >= 129)  AND ( (childD.`minSalaryUsd` <= 8883)  OR  (childD.`minHourlyRateUsd` <= 126) ) 
MATCH (childD)-[criterionRelationship8:HAS_VOTE_ON]->(c:Criterion {id: 2}) WHERE  (criterionRelationship8.`properties.experienceMonths` >= 1)  WITH DISTINCT childD, dg, rdgd  
MATCH (childD)-[criterionRelationship10:HAS_VOTE_ON]->(c:Criterion {id: 36}) WHERE  (criterionRelationship10.`avgVotesWeight` >= 1.0)  AND  (criterionRelationship10.`properties.experienceMonths` >= 1)  WITH DISTINCT childD, dg, rdgd  
MATCH (childD)-[criterionRelationship13:HAS_VOTE_ON]->(c:Criterion {id: 4}) WHERE  (criterionRelationship13.`properties.experienceMonths` >= 0)  WITH DISTINCT childD, dg, rdgd  
MATCH (childD)-[criterionRelationship15:HAS_VOTE_ON]->(c:Criterion {id: 22}) WHERE  (criterionRelationship15.`avgVotesWeight` >= 1.0)  AND  (criterionRelationship15.`properties.experienceMonths` >= 1)  WITH DISTINCT childD, dg, rdgd  
OPTIONAL MATCH (childD)-[ru:CREATED_BY]->(u:User)  WITH childD, u, ru, dg, rdgd  
OPTIONAL MATCH (childD)-[vg:HAS_VOTE_ON]->(c:Criterion) WHERE c.id IN [2, 36, 4, 22] WITH c, childD, u, ru, dg, rdgd, (vg.avgVotesWeight * (CASE WHEN c IS NOT NULL THEN coalesce({`22`:1.2236918603185925, `2`:2.9245935245152226, `36`:0.2288013749943646, `4`:3.9599506966378435}[toString(c.id)], 1.0) ELSE 1.0 END)) as weight, vg.totalVotes as totalVotes 
WITH childD, u, ru , dg, rdgd , toFloat(sum(weight)) as weight, toInteger(sum(totalVotes)) as totalVotes  
ORDER BY  weight  DESC , childD.createdAt DESC 
SKIP 0 LIMIT 20 
WITH * OPTIONAL MATCH (childD)-[rup:UPDATED_BY]->(up:User)  
RETURN rdgd, ru, u, rup, up, childD AS decision, weight, totalVotes, [ (c1)<-[vg1:HAS_VOTE_ON]-(childD) WHERE c1.id IN [2, 36, 4, 22] | {criterion: c1, relationship: vg1} ] AS weightedCriteria 

性能瓶颈段 #

MATCH (childD)-[:EMPLOYMENT_AS]->(root2:Employment ) 
WHERE root2.id IN ([1]) WITH DISTINCT childD, dg, rdgd  
MATCH path3=(root3:Location )-[:CONTAINS*0..]->(descendant3:Location) 
WHERE (descendant3.id IN ([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35]) OR root3.id IN ([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35])) 
UNWIND nodes(path3) AS pathNode3 WITH childD, dg, rdgd, COLLECT(DISTINCT pathNode3) AS pathNodes3 
MATCH (childD)-[:LOCATED_IN]->(pathNode3) WHERE pathNode3 IN pathNodes3 WITH DISTINCT childD, dg, rdgd

第一次优化后的查询 #

WITH [] as ceNodeList  MATCH (root2:Employment ) 
WHERE root2.id IN ([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]) 
WITH ceNodeList, root2, COLLECT(root2) AS listRoot2 
WITH apoc.coll.unionAll(ceNodeList, listRoot2) AS ceNodeList 
WITH apoc.coll.toSet(ceNodeList) as ceNodeList  
MATCH (root3:Location ) 
WHERE root3.id IN ([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73]) 
WITH ceNodeList, root3, COLLECT(root3) AS listRoot3 
OPTIONAL MATCH (root3)-[:CONTAINS*0..]->(descendant3:Location)
OPTIONAL MATCH (ascendant3:Location)-[:CONTAINS*0..]->(root3) 
WITH ceNodeList, listRoot3, COLLECT( DISTINCT ascendant3) AS listAscendant3, COLLECT( DISTINCT descendant3) AS listDescendant3
WITH listRoot3, listAscendant3, apoc.coll.unionAll(ceNodeList, apoc.coll.unionAll(listDescendant3, apoc.coll.unionAll(listRoot3, listAscendant3))) AS ceNodeList 
WITH apoc.coll.toSet(ceNodeList) as ceNodeList  
UNWIND ceNodeList AS ceNode 
WITH DISTINCT ceNode MATCH (dg:DecisionGroup {id: -2})-[rdgd:CONTAINS]->(childD:Decision:Profile ) -[:REQUIRES]->(ceNode) 
WITH DISTINCT childD, dg, rdgd, collect(ceNode) as ceNodes 
WITH childD, dg, rdgd, ceNodes, reduce(ceNodeLabels = [], n IN ceNodes | ceNodeLabels + labels(n)) as ceNodeLabels  
WHERE all(x IN ['Employment', 'Location'] 
WHERE x IN ceNodeLabels) WITH childD,  dg, rdgd return count(childD)

第二次优化后的查询 #

WITH [] as ceNodeList  
MATCH (root2:Location ) 
WHERE root2.id IN ([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100]) 
WITH ceNodeList, root2 
OPTIONAL MATCH (root2)-[:CONTAINS*0..]->(descendant2:Location) 
OPTIONAL MATCH (ascendant2:Location)-[:CONTAINS*0..]->(root2) 
WITH ceNodeList, COLLECT(root2) AS listRoot2, COLLECT( DISTINCT ascendant2) AS listAscendant2, COLLECT( DISTINCT descendant2) AS listDescendant2 
WITH apoc.coll.union(ceNodeList, apoc.coll.union(listDescendant2, apoc.coll.union(listRoot2, listAscendant2))) AS ceNodeList 
WITH ceNodeList  MATCH (root3:Employment ) 
WHERE root3.id IN ([101, 102, 103, 104, 105]) 
WITH ceNodeList, COLLECT(root3) AS listRoot3 
WITH apoc.coll.union(ceNodeList, listRoot3) AS ceNodeList 
WITH ceNodeList  
UNWIND ceNodeList as seNode 
WITH collect(seNode.id) as seNodeIds with apoc.coll.toSet(seNodeIds) as seNodeIds  
MATCH (dg:DecisionGroup {id: -2})-[rdgd:CONTAINS]->(childD:Profile ) -[:REQUIRES]->(ceNode) 
WHERE ceNode.id in seNodeIds 
WITH DISTINCT childD, dg, rdgd, collect(ceNode) as ceNodes 
WITH childD, dg, rdgd, ceNodes, reduce(ceNodeLabels = [], n IN ceNodes | ceNodeLabels + labels(n)) as ceNodeLabels  
WHERE all(x IN ['Employment', 'Location'] 
WHERE x IN ceNodeLabels) 
WITH childD,  dg, rdgd

进一步优化方案 #

1. 索引与约束强化 #

• 为核心节点的ID创建唯一约束，加速精准匹配：

  CREATE CONSTRAINT employment_id_unique FOR (e:Employment) REQUIRE e.id IS UNIQUE;
  CREATE CONSTRAINT location_id_unique FOR (l:Location) REQUIRE l.id IS UNIQUE;
  CREATE CONSTRAINT decision_group_id_unique FOR (dg:DecisionGroup) REQUIRE dg.id IS UNIQUE;
  

• 为Profile节点的过滤字段创建复合索引，提前过滤无效数据：

  CREATE INDEX profile_filter_idx FOR (p:Profile) ON (p.active, p.experienceMonths);
  

• 为高频使用的关系类型创建关系索引：

  CREATE INDEX rel_employment_as FOR ()-[r:EMPLOYMENT_AS]->();
  CREATE INDEX rel_located_in FOR ()-[r:LOCATED_IN]->();
  

2. 简化路径匹配逻辑 #

避免在每条Profile数据上重复计算Location路径，一次性预计算所有目标节点的上下层级：

// 预计算所有目标Location节点及其祖先/后代
WITH [1,2,...,35] AS targetLocationIds
MATCH (l:Location) WHERE l.id IN targetLocationIds
CALL apoc.path.subgraphNodes(l, {relationshipFilter: "CONTAINS>", labelFilter: "Location"}) YIELD node AS descNode
CALL apoc.path.subgraphNodes(l, {relationshipFilter: "<CONTAINS", labelFilter: "Location"}) YIELD node AS ascNode
WITH COLLECT(DISTINCT l) + COLLECT(DISTINCT descNode) + COLLECT(DISTINCT ascNode) AS allLocationNodes
// 匹配目标Employment节点
MATCH (e:Employment) WHERE e.id IN [1]
// 直接匹配符合条件的Profile，减少中间集
MATCH (dg:DecisionGroup {id: -2})-[rdgd:CONTAINS]->(childD:Decision:Profile)
WHERE childD.active = true 
  AND childD.experienceMonths >= 129 
  AND (childD.minSalaryUsd <= 8883 OR childD.minHourlyRateUsd <= 126)
  AND (childD)-[:EMPLOYMENT_AS]->(e)
  AND (childD)-[:LOCATED_IN]->(allLocationNodes)

3. 合并重复的关系匹配 #

将多次单独匹配HAS_VOTE_ON的逻辑合并为一次，减少遍历次数：

MATCH (childD)-[vg:HAS_VOTE_ON]->(c:Criterion)
WHERE c.id IN [2,36,4,22]
WITH childD, dg, rdgd, vg, c
WHERE 
  (c.id=2 AND vg.properties.experienceMonths >=1) OR
  (c.id=36 AND vg.avgVotesWeight >=1.0 AND vg.properties.experienceMonths >=1) OR
  (c.id=4 AND vg.properties.experienceMonths >=0) OR
  (c.id=22 AND vg.avgVotesWeight >=1.0 AND vg.properties.experienceMonths >=