如何高效从CSV在Neo4j中创建关系？现有方案性能待优化

问题背景 #

已在Neo4j中创建好所有节点，需要从含250万行的CSV文件批量创建关系。CSV每行包含两个节点的唯一ID、关系类型及若干属性。当前使用带PERIODIC COMMIT的LOAD CSV查询，手动筛选关系类型，但创建过程耗时极长，急需更优实现方式。

当前查询语句 #

:auto USING PERIODIC COMMIT 100 LOAD CSV WITH HEADERS FROM 'file:///filename.csv' as relationships
WITH relationships.uniqueid1 as uniqueid1, relationships.uniqueid2 as uniqueid2, relationships.extraproperty1 as extraproperty1, relationships.rela as rela... , relationships.extrapropertyN as extrapropertyN
WHERE relations.rela = "manager_relationship"
MATCH (a:Item {uniqueid: uniqueid1})
MATCH (b:Item {uniqueid: uniqueid2})
MERGE (b) - [rel: relationship_name {propertyvalue1: extraproperty1,...propertyvalueN: extrapropertyN }] -> (a)
RETURN count(rel)

优化建议 #

• 给节点唯一ID建唯一约束：MATCH节点时依赖uniqueid，必须给Item节点的uniqueid字段创建唯一约束，避免全表扫描，大幅提升节点查找速度。

  CREATE CONSTRAINT FOR (i:Item) REQUIRE i.uniqueid IS UNIQUE;
  

• 调大PERIODIC COMMIT批次大小：当前批次设为100太小，会导致频繁提交事务。根据服务器内存情况，可将批次调整为1000-5000（内存充足建议设5000），减少事务提交次数，提升整体效率。

• 提前过滤数据：把关系类型的筛选条件WHERE relationships.rela = "manager_relationship"移到LOAD CSV之后、WITH之前，更早过滤掉不需要的行，减少后续处理的数据量。调整后的语句示例：

  :auto USING PERIODIC COMMIT 5000
  LOAD CSV WITH HEADERS FROM 'file:///filename.csv' as relationships
  WHERE relationships.rela = "manager_relationship"
  WITH relationships.uniqueid1 as uniqueid1, relationships.uniqueid2 as uniqueid2, relationships.extraproperty1 as extraproperty1, ..., relationships.extrapropertyN as extrapropertyN
  MATCH (a:Item {uniqueid: uniqueid1})
  MATCH (b:Item {uniqueid: uniqueid2})
  MERGE (b)-[rel:manager_relationship {propertyvalue1: extraproperty1, ...}]->(a)
  RETURN count(rel)
  

• 拆分MERGE和属性设置：如果关系属性较多，MERGE时同时匹配所有属性会大幅降低速度。可以先MERGE仅包含关系类型和两端节点的关系框架，再用SET单独设置属性，这样MERGE的匹配逻辑更简单，效率更高：

  :auto USING PERIODIC COMMIT 5000
  LOAD CSV WITH HEADERS FROM 'file:///filename.csv' as relationships
  WHERE relationships.rela = "manager_relationship"
  WITH relationships.uniqueid1 as uniqueid1, relationships.uniqueid2 as uniqueid2, relationships.extraproperty1 as extraproperty1, ..., relationships.extrapropertyN as extrapropertyN
  MATCH (a:Item {uniqueid: uniqueid1})
  MATCH (b:Item {uniqueid: uniqueid2})
  MERGE (b)-[rel:manager_relationship]->(a)
  SET rel.propertyvalue1 = extraproperty1, rel.propertyvalue2 = extraproperty2, ...
  RETURN count(rel)
  

• 使用CALL { ... } IN TRANSACTIONS并行处理（Neo4j 4.x+适用）：替代PERIODIC COMMIT，支持利用多核CPU并行处理，对于大数据量场景效率提升明显：

  :auto
  LOAD CSV WITH HEADERS FROM 'file:///filename.csv' as relationships
  WHERE relationships.rela = "manager_relationship"
  WITH relationships.uniqueid1 as uniqueid1, relationships.uniqueid2 as uniqueid2, relationships.extraproperty1 as extraproperty1, ..., relationships.extrapropertyN as extrapropertyN
  CALL {
    WITH uniqueid1, uniqueid2, extraproperty1, ...
    MATCH (a:Item {uniqueid: uniqueid1})
    MATCH (b:Item {uniqueid: uniqueid2})
    MERGE (b)-[rel:manager_relationship]->(a)
    SET rel.propertyvalue1 = extraproperty1, ...
    RETURN count(rel) as cnt
  } IN TRANSACTIONS OF 5000 ROWS
  RETURN sum(cnt) as total_rels_created
  

• 预处理CSV文件：如果CSV包含多种关系类型，每次跑查询都要扫描全表筛选，效率很低。可以用脚本把原CSV按关系类型拆分，每个类型单独生成一个CSV文件，再分别执行LOAD CSV，减少每次处理的数据量和IO开销。

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