Neo4j 3.5中MATCH语句优化请求：查询返回数据量过大

原查询 #

MATCH (t1:Tel {TB:true})<-[:TelBIZ]-
  (:ACC {TB:true})-[or:REBIZ {movOre:t1.nuTel}]->
  (:ACC {TB:true})-[:TelBIZ]->(t2:Tel {TB:true,nuTel:or.movBene}) 
WHERE t1.nuTel<>t2.nuTel
RETURN t1, t2, COUNT(or) AS Ope, SUM(or.imp) AS Total, 
  10^3 as Precision, max(or.Txt) AS mRB

优化步骤 #

1. 添加针对性索引，消除全表扫描 #

Neo4j 3.5支持复合索引与关系属性索引，针对查询中过滤、关联字段创建索引，可大幅压缩初始匹配的数据量：

• 针对Tel节点的TB和nuTel创建复合索引：

  CREATE INDEX ON :Tel(TB, nuTel);
  

• 针对ACC节点的TB创建单属性索引：

  CREATE INDEX ON :ACC(TB);
  

• 针对REBIZ关系的movOre、movBene创建关系索引：

  CREATE INDEX ON :REBIZ(movOre, movBene);
  

2. 提前过滤条件，缩减中间结果集 #

将WHERE子句中的t1.nuTel<>t2.nuTel整合到MATCH的节点约束中，提前过滤不符合条件的节点，减少后续计算的处理量：

MATCH (t1:Tel {TB:true})<-[:TelBIZ]-(acc1:ACC {TB:true})-[or:REBIZ {movOre:t1.nuTel}]->(acc2:ACC {TB:true})-[:TelBIZ]->(t2:Tel {TB:true, nuTel:or.movBene, nuTel:<>t1.nuTel})
RETURN t1, t2, COUNT(or) AS Ope, SUM(or.imp) AS Total, 
  10^3 as Precision, max(or.Txt) AS mRB

3. 用DISTINCT避免重复统计 #

若同一对t1-t2被多条重复路径匹配（比如同一REBIZ关系被不同ACC节点关联匹配），可在聚合时使用DISTINCT确保每个关系只统计一次：

MATCH (t1:Tel {TB:true})<-[:TelBIZ]-(acc1:ACC {TB:true})-[or:REBIZ {movOre:t1.nuTel}]->(acc2:ACC {TB:true})-[:TelBIZ]->(t2:Tel {TB:true, nuTel:or.movBene}) 
WHERE t1.nuTel<>t2.nuTel
RETURN t1, t2, COUNT(DISTINCT or) AS Ope, SUM(DISTINCT or.imp) AS Total, 
  10^3 as Precision, max(or.Txt) AS mRB

注：是否使用DISTINCT需结合业务逻辑判断，若每个REBIZ关系对应唯一业务操作，则需保留以避免重复计数。

4. 用PROFILE分析执行计划定位瓶颈 #

在查询前添加PROFILE关键字，查看执行计划中的高成本步骤，针对性优化：

PROFILE
MATCH (t1:Tel {TB:true})<-[:TelBIZ]-
  (:ACC {TB:true})-[or:REBIZ {movOre:t1.nuTel}]->
  (:ACC {TB:true})-[:TelBIZ]->(t2:Tel {TB:true,nuTel:or.movBene}) 
WHERE t1.nuTel<>t2.nuTel
RETURN t1, t2, COUNT(or) AS Ope, SUM(or.imp) AS Total, 
  10^3 as Precision, max(or.Txt) AS mRB

重点关注：

• 是否存在AllNodesScan/AllRelationshipsScan（说明索引未生效）
• Rows列数值过大的步骤（说明中间结果集冗余）
• 高DbHits的操作（说明磁盘IO或内存占用过高）

5. 限制返回字段，减少数据传输 #

若t1、t2节点包含冗余属性，可只返回业务需要的字段，压缩结果集大小：

MATCH (t1:Tel {TB:true})<-[:TelBIZ]-(acc1:ACC {TB:true})-[or:REBIZ {movOre:t1.nuTel}]->(acc2:ACC {TB:true})-[:TelBIZ]->(t2:Tel {TB:true, nuTel:or.movBene}) 
WHERE t1.nuTel<>t2.nuTel
RETURN t1.nuTel AS TelOre, t2.nuTel AS TelBene, COUNT(or) AS Ope, SUM(or.imp) AS Total, 
  10^3 as Precision, max(or.Txt) AS mRB

内容的提问来源于stack exchange，提问作者Somnath Deb