merge 语句比先match 然后create要慢很多，差不多10倍左右？
发布于 5 年前 作者 feng1990liu 3679 次浏览 来自 问答

实验数据 先match 然后create 2000条数据需要45秒，而同样的数据merge需要5分钟？

8 回复

数据量千万左右用的MERGE：10000relationships/40s 数据量上亿之后：2018-11-01 19:31:11 Speed: 10000relationships/85s

UNWIMD {batch}
MERGE 节点
MERGE 节点
MERGE 关系

@crazyyanchao 连续这样merge是不是会遇到一个eager的问题，好像会有效率影响

1

所以你要避免eager ，可以参考https://markhneedham.com/blog/2014/10/23/neo4j-cypher-avoiding-the-eager/

谢谢大佬

在新版本中是不是已经解决这个问题了？@bingo 下面是我在neo4j-3.4.9版本的测试，执行这个命令没有发现EagerAggregation类似的提示步骤：

// 进入shell命令行
bin/neo4j-shell -path path/to/graph.db
// 执行导入
neo4j-sh (?)$ PROFILE USING PERIODIC COMMIT 1000
> LOAD CSV WITH HEADERS FROM "file:/u02/isi/zdr/graph/neo4j-community-3.4.9/import/customerDb.csv" AS row
> MERGE (employee:Employee {employeeId: row.EmployeeID})
> MERGE (order:Order {orderId: row.OrderID})
> MERGE (employee)-[:SOLD]->(order);
+-------------------+
| No data returned. |
+-------------------+
Nodes created: 839
Relationships created: 830
Properties set: 839
Labels added: 839
3220 ms

Compiler CYPHER 3.4

Planner COST

Runtime INTERPRETED

Runtime version 3.4

+------------------------------+----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| Operator                     | Estimated Rows | Rows   | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Variables                         | Other                                |
+------------------------------+----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| +ProduceResults              |              1 |      0 |       0 |               0 |                 0 |               0.0000 | anon[246], employee, order, row   |                                      |
| |                            +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| +EmptyResult                 |              1 |      0 |       0 |               0 |                 0 |               0.0000 | anon[246], employee, order, row   |                                      |
| |                            +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| +Apply                       |              1 |   2155 |       0 |               0 |                 0 |               0.0000 | row -- anon[246], employee, order |                                      |
| |\                           +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | +AntiConditionalApply      |              1 |   2155 |       0 |               0 |                 0 |               0.0000 | anon[246], employee, order        |                                      |
| | |\                         +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | | +MergeCreateRelationship |              1 |    830 |     831 |               0 |                 0 |               0.0000 | anon[246] -- employee, order      |                                      |
| | | |                        +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | | +Argument                |              1 |    830 |       0 |               0 |                 0 |               0.0000 | employee, order                   |                                      |
| | |                          +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | +AntiConditionalApply      |              1 |   2155 |       0 |               0 |                 0 |               0.0000 | anon[246], employee, order        |                                      |
| | |\                         +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | | +Optional                |              1 |    830 |       0 |               0 |                 0 |               0.0000 | anon[246], employee, order        |                                      |
| | | |                        +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | | +ActiveRead              |              0 |      0 |       0 |               0 |                 0 |               0.0000 | anon[246], employee, order        |                                      |
| | | |                        +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | | +Expand(Into)            |              0 |      0 |   11320 |               0 |                 0 |               0.0000 | anon[246] -- employee, order      | (employee)-[:SOLD]->(order)          |
| | | |                        +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | | +LockNodes               |              1 |      0 |       0 |               0 |                 0 |               0.0000 | employee, order                   | employee, order                      |
| | | |                        +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | | +Argument                |              1 |    830 |       0 |               0 |                 0 |               0.0000 | employee, order                   |                                      |
| | |                          +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | +Optional                  |              1 |   2155 |       0 |               0 |                 0 |               0.0000 | anon[246], employee, order        |                                      |
| | |                          +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | +ActiveRead                |              0 |   1325 |       0 |               0 |                 0 |               0.0000 | anon[246], employee, order        |                                      |
| | |                          +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | +Expand(Into)              |              0 |   1325 |   29360 |               0 |                 0 |               0.0000 | anon[246] -- employee, order      | (employee)-[:SOLD]->(order)          |
| | |                          +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | +Argument                  |              1 |   2155 |       0 |               0 |                 0 |               0.0000 | employee, order                   |                                      |
| |                            +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| +Apply                       |              1 |   2155 |       0 |               0 |                 0 |               0.0000 | employee -- order, row            |                                      |
| |\                           +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | +AntiConditionalApply      |              1 |   2155 |       0 |               0 |                 0 |               0.0000 | order, row                        |                                      |
| | |\                         +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | | +MergeCreateNode         |              1 |    830 |    3321 |               0 |                 0 |               0.0000 | order -- row                      |                                      |
| | | |                        +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | | +Argument                |              1 |    830 |       0 |               0 |                 0 |               0.0000 | row                               |                                      |
| | |                          +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | +Optional                  |              1 |   2155 |       0 |               0 |                 0 |               0.0000 | order, row                        |                                      |
| | |                          +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | +ActiveRead                |              0 |   1325 |       0 |               0 |                 0 |               0.0000 | order, row                        |                                      |
| | |                          +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | +Filter                    |              0 |   1325 | 1775680 |               0 |                 0 |               0.0000 | order, row                        | order.orderId = row.OrderID          |
| | |                          +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | +NodeByLabelScan           |              1 | 887840 |  889996 |               0 |                 0 |               0.0000 | order -- row                      | :Order                               |
| |                            +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| +Apply                       |              1 |   2155 |       0 |               0 |                 0 |               0.0000 | employee, row                     |                                      |
| |\                           +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | +AntiConditionalApply      |              1 |   2155 |       0 |               0 |                 0 |               0.0000 | employee, row                     |                                      |
| | |\                         +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | | +MergeCreateNode         |              1 |      9 |      37 |               0 |                 0 |               0.0000 | employee -- row                   |                                      |
| | | |                        +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | | +Argument                |              1 |      9 |       0 |               0 |                 0 |               0.0000 | row                               |                                      |
| | |                          +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | +Optional                  |              1 |   2155 |       0 |               0 |                 0 |               0.0000 | employee, row                     |                                      |
| | |                          +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | +ActiveRead                |              0 |   2146 |       0 |               0 |                 0 |               0.0000 | employee, row                     |                                      |
| | |                          +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | +Filter                    |              0 |   2146 |   38246 |               0 |                 0 |               0.0000 | employee, row                     | employee.employeeId = row.EmployeeID |
| | |                          +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| | +NodeByLabelScan           |              1 |  19123 |   21279 |               0 |                 0 |               0.0000 | employee -- row                   | :Employee                            |
| |                            +----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+
| +LoadCSV                     |              1 |   2155 |       0 |               0 |                 0 |               0.0000 | row                               |                                      |
+------------------------------+----------------+--------+---------+-----------------+-------------------+----------------------+-----------------------------------+--------------------------------------+

Total database accesses: 2770070

neo4j-sh (?)$

索引都加上了吗？ 我看你都是NodeByLabelScan 要是数据大了，没有索引，会失败的

@bingo 索引都加了 数据有5亿节点 15亿关系，在这个基础上添加的

@crazyyanchao 新增数据少没啥影响吧

回到顶部