我们做了一个原型项目来评估neo4j是否适用于我们的场景。但是,性能不如预期。所以我们想知道这是否是 Neo4j 的限制,或者我们应该如何改进它。
方案详细信息如下:
- 系统配置:CPU Intel-i5 2.3G双核,内存:16G,Neo4j版本:2.3.1
- graphdb 中只有一种节点标签和节点编号是 50。每个节点都有大约 20 个属性。索引是在主要属性"nodeid"上创建的;
- 关系类型为 80,但 graphdb 中的总关系数约为 6000(因为测试节点之间只有微小的差异),只有一个属性表示版本,所以没有索引;
- 原型目标是:从一个特定的节点开始,把所有有特定关系的节点(大约50个)挖出来作为网络。
通过浏览器进行密码查询的费用超过 10 秒。由于只有 50 个节点,这是否符合预期?下面是我们使用的密码查询命令:
MATCH
(startnode:MYNode {nodeid:"123456"})-[r1:REL1|:REL2......|:REL50 {version:1}]-
(target1:MYNode)-[r2:REL1|:REL2......|:REL50 {version:1}]-
(target2:MYNode)-[r3:REL1|:REL2......|:REL50 {version:1}]-(target3:MYNode)
WHERE target1.timestamp > 1449417600 AND target2.timestamp > 1449417600 AND target3.timestamp > 1449417600
RETURN
DISTINCT target2.nodeid as l_id,
target2.timestamp as l_ts,
type(r3) as r_type,
target3.nodeid as r_id,
target3.timestamp as r_ts
LIMIT 5000;
下面是配置文件输出:
Compiler CYPHER 2.3
Planner COST
Runtime INTERPRETED
+-----------------+----------------+-------+---------+---------------------------------------------+--------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Operator | Estimated Rows | Rows | DB Hits | Identifiers | Other
|
+-----------------+----------------+-------+---------+---------------------------------------------+--------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| +ProduceResults | 93 | 5000 | 0 | l_appts, l_id, r_appts, r_id, r_type | l_id, l_appts, r_type, r_id, r_appts
|
| | +----------------+-------+---------+---------------------------------------------+--------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| +Limit | 93 | 5000 | 0 | l_appts, l_id, r_appts, r_id, r_type | Literal(5000)
|
| | +----------------+-------+---------+---------------------------------------------+--------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| +Distinct | 93 | 5000 | 627064 | l_appts, l_id, r_appts, r_id, r_type | r_id, l_appts, r_appts, l_id, r_type
|
| | +----------------+-------+---------+---------------------------------------------+--------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| +Filter | 98 | 78383 | 313881 | r1, r2, r3, seed, target1, target2, target3 | Ands(r3.version == { AUTOINT3}, NOT(r2 == r3), NOT(r1 == r3), AndedPropertyComparablePredicates( target3,target3.STAN_APP_TIMESTAMP,target3.STAN_APP_TIMESTAMP < { AUTOINT9}, target3.STAN_APP_TIMESTAMP > { AUTOINT8}), target3:APPNode)
|
| | +----------------+-------+---------+---------------------------------------------+--------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| +Expand(All) | 977 | 78732 | 96630 | r1, r2, r3, seed, target1, target2, target3 | (target2)-[r3:ID__ID|:ID__ID_C1|:ID__ID_C2|:ID_C1__ID_C1|:ID_C1__ID_C2|:ID_C1__ID|:ID_C2__ID_C1|:ID_C2__ ID_C2|:ID_C2__ID|:CELLPHONE__CELLPHONE|:CELLPHONE__HOMEPHONE|:CELLPHONE__EMPPHONE|:CELLPHONE__C1PHONE|:CELLPHONE__C2PHONE|:HOMEPHONE__CELLPHONE|:HOMEPHONE__HOMEPHONE|:HOMEPHONE__EMPPHONE|:HOMEPHONE__C1PHON E|:HOMEPHONE__C2PHONE|:EMPPHONE__CELLPHONE|:EMPPHONE__HOMEPHONE|:EMPPHONE__EMPPHONE|:EMPPHONE__C1PHONE|:EMPPHONE__C2PHONE|:C1PHONE__CELLPHONE|:C1PHONE__HOMEPHONE|:C1PHONE__EMPPHONE|:C1PHONE__C1PHONE|:C1PHO NE__C2PHONE|:C2PHONE__CELLPHONE|:C2PHONE__HOMEPHONE|:C2PHONE__EMPPHONE|:C2PHONE__C1PHONE|:C2PHONE__C2PHONE|:EMAIL__EMAIL|:CARLICENSE__CARLICENSE|:EMPNAME__EMPNAME|:IPADDR__IPADDR|:MACADDR__MACADDR|:WIFIMAC __WIFIMAC|:HOMEADDR__HOMEADDR__P0|:EMPADDR__EMPADDR__P0|:HOMEADDR__EMPADDR__P0|:EMPADDR__HOMEADDR__P0|:C1ADDR__C1ADDR__P0|:C2ADDR__C2ADDR__P0|:C1ADDR__C2ADDR__P0|:C2ADDR__C1ADDR__P
0|:HOMEADDR__C1ADDR__P0|:HOMEADDR__C2ADDR__P0|:EMPADDR__C1ADDR__P0|:EMPADDR__C2ADDR__P0|:C1ADDR__HOMEADDR__P0|:C1ADDR__EMPADDR__P0|:C2ADDR__HOMEADDR__P0|:C2ADDR__EMPADDR__P0]-(target3) |
| | +----------------+-------+---------+---------------------------------------------+--------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| +Filter | 21 | 314 | 1260 | r1, r2, seed, target1, target2 | Ands(AndedPropertyComparablePredicates(target2,target2.STAN_APP_TIMESTAMP,target2.STAN_APP_TIMESTAMP < { AUTOINT7}, target2.STAN_APP_TIMESTAMP > { AUTOINT6}), target2:APPNode, r2.version == { AUTOINT2}, NOT(r1 == r2))
|
| | +----------------+-------+---------+---------------------------------------------+--------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| +Expand(All) | 212 | 315 | 429 | r1, r2, seed, target1, target2 | (target1)-[r2:ID__ID|:ID__ID_C1|:ID__ID_C2|:ID_C1__ID_C1|:ID_C1__ID_C2|:ID_C1__ID|:ID_C2__ID_C1|:ID_C2__ ID_C2|:ID_C2__ID|:CELLPHONE__CELLPHONE|:CELLPHONE__HOMEPHONE|:CELLPHONE__EMPPHONE|:CELLPHONE__C1PHONE|:CELLPHONE__C2PHONE|:HOMEPHONE__CELLPHONE|:HOMEPHONE__HOMEPHONE|:HOMEPHONE__EMPPHONE|:HOMEPHONE__C1PHON E|:HOMEPHONE__C2PHONE|:EMPPHONE__CELLPHONE|:EMPPHONE__HOMEPHONE|:EMPPHONE__EMPPHONE|:EMPPHONE__C1PHONE|:EMPPHONE__C2PHONE|:C1PHONE__CELLPHONE|:C1PHONE__HOMEPHONE|:C1PHONE__EMPPHONE|:C1PHONE__C1PHONE|:C1PHO NE__C2PHONE|:C2PHONE__CELLPHONE|:C2PHONE__HOMEPHONE|:C2PHONE__EMPPHONE|:C2PHONE__C1PHONE|:C2PHONE__C2PHONE|:EMAIL__EMAIL|:CARLICENSE__CARLICENSE|:EMPNAME__EMPNAME|:IPADDR__IPADDR|:MACADDR__MACADDR|:WIFIMAC __WIFIMAC|:HOMEADDR__HOMEADDR__P0|:EMPADDR__EMPADDR__P0|:HOMEADDR__EMPADDR__P0|:EMPADDR__HOMEADDR__P0|:C1ADDR__C1ADDR__P0|:C2ADDR__C2ADDR__P0|:C1ADDR__C2ADDR__P0|:C2ADDR__C1ADDR__P
0|:HOMEADDR__C1ADDR__P0|:HOMEADDR__C2ADDR__P0|:EMPADDR__C1ADDR__P0|:EMPADDR__C2ADDR__P0|:C1ADDR__HOMEADDR__P0|:C1ADDR__EMPADDR__P0|:C2ADDR__HOMEADDR__P0|:C2ADDR__EMPADDR__P0]-(target2) |
| | +----------------+-------+---------+---------------------------------------------+--------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| +Filter | 5 | 2 | 8 | r1, seed, target1 | Ands(r1.version == { AUTOINT1}, target1:APPNode, AndedPropertyComparablePredicates(target1,target1. STAN_APP_TIMESTAMP,target1.STAN_APP_TIMESTAMP > { AUTOINT4}, target1.STAN_APP_TIMESTAMP < { AUTOINT5}))
|
| | +----------------+-------+---------+---------------------------------------------+--------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| +Expand(All) | 46 | 2 | 59 | r1, seed, target1 | (seed)-[r1:ID__ID|:ID__ID_C1|:ID__ID_C2|:ID_C1__ID_C1|:ID_C1__ID_C2|:ID_C1__ID|:ID_C2__ID_C1|:ID_C2__ID_ C2|:ID_C2__ID|:CELLPHONE__CELLPHONE|:CELLPHONE__HOMEPHONE|:CELLPHONE__EMPPHONE|:CELLPHONE__C1PHONE|:CELLPHONE__C2PHONE|:HOMEPHONE__CELLPHONE|:HOMEPHONE__HOMEPHONE|:HOMEPHONE__EMPPHONE|:HOMEPHONE__C1PHONE|: HOMEPHONE__C2PHONE|:EMPPHONE__CELLPHONE|:EMPPHONE__HOMEPHONE|:EMPPHONE__EMPPHONE|:EMPPHONE__C1PHONE|:EMPPHONE__C2PHONE|:C1PHONE__CELLPHONE|:C1PHONE__HOMEPHONE|:C1PHONE__EMPPHONE|:C1PHONE__C1PHONE|:C1PHONE_ _C2PHONE|:C2PHONE__CELLPHONE|:C2PHONE__HOMEPHONE|:C2PHONE__EMPPHONE|:C2PHONE__C1PHONE|:C2PHONE__C2PHONE|:EMAIL__EMAIL|:CARLICENSE__CARLICENSE|:EMPNAME__EMPNAME|:IPADDR__IPADDR|:MACADDR__MACADDR|:WIFIMAC__W IFIMAC|:HOMEADDR__HOMEADDR__P0|:EMPADDR__EMPADDR__P0|:HOMEADDR__EMPADDR__P0|:EMPADDR__HOMEADDR__P0|:C1ADDR__C1ADDR__P0|:C2ADDR__C2ADDR__P0|:C1ADDR__C2ADDR__P0|:C2ADDR__C1ADDR__P0|:
HOMEADDR__C1ADDR__P0|:HOMEADDR__C2ADDR__P0|:EMPADDR__C1ADDR__P0|:EMPADDR__C2ADDR__P0|:C1ADDR__HOMEADDR__P0|:C1ADDR__EMPADDR__P0|:C2ADDR__HOMEADDR__P0|:C2ADDR__EMPADDR__P0]-(target1) |
| | +----------------+-------+---------+---------------------------------------------+--------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| +NodeIndexSeek | 1 | 1 | 2 | seed | :APPNode(APP_ID)
|
+-----------------+----------------+-------+---------+---------------------------------------------+--------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Total database accesses: 1039333
我试图重新创建你的数据库和语句。问题在于你跨越了一个巨大的格子,并且你正在以不同的方式进行后过滤。
如果您使用 WITH DISTINCT 减少两者之间的 WIP(因为无论如何您对不同的结果感兴趣),那么对我来说它会在几秒钟内完成。
MATCH (startnode:MYNode {nodeid:1})
-[r1:REL1|:REL2|:REL3|:REL4|:REL5|:REL6|:REL7|:REL8|:REL9|:REL10|:REL11|:REL12|:REL13|:REL14|:REL15|:REL16|:REL17|:REL18|:REL19|:REL20|:REL21|:REL22|:REL23|:REL24|:REL25|:REL26|:REL27|:REL28|:REL29|:REL30|:REL31|:REL32|:REL33|:REL34|:REL35|:REL36|:REL37|:REL38|:REL39|:REL40|:REL41|:REL42|:REL43|:REL44|:REL45|:REL46|:REL47|:REL48|:REL49|:REL50 {version:1}]-(target1:MYNode)
WHERE target1.timestamp > 1449417600 WITH distinct target1
MATCH (target1)-[r2:REL1|:REL2|:REL3|:REL4|:REL5|:REL6|:REL7|:REL8|:REL9|:REL10|:REL11|:REL12|:REL13|:REL14|:REL15|:REL16|:REL17|:REL18|:REL19|:REL20|:REL21|:REL22|:REL23|:REL24|:REL25|:REL26|:REL27|:REL28|:REL29|:REL30|:REL31|:REL32|:REL33|:REL34|:REL35|:REL36|:REL37|:REL38|:REL39|:REL40|:REL41|:REL42|:REL43|:REL44|:REL45|:REL46|:REL47|:REL48|:REL49|:REL50 {version:1}]-(target2:MYNode)
WHERE target2.timestamp > 1449417600 WITH distinct target2
MATCH (target2)-[r3:REL1|:REL2|:REL3|:REL4|:REL5|:REL6|:REL7|:REL8|:REL9|:REL10|:REL11|:REL12|:REL13|:REL14|:REL15|:REL16|:REL17|:REL18|:REL19|:REL20|:REL21|:REL22|:REL23|:REL24|:REL25|:REL26|:REL27|:REL28|:REL29|:REL30|:REL31|:REL32|:REL33|:REL34|:REL35|:REL36|:REL37|:REL38|:REL39|:REL40|:REL41|:REL42|:REL43|:REL44|:REL45|:REL46|:REL47|:REL48|:REL49|:REL50 {version:1}]-(target3:MYNode)
WHERE target3.timestamp > 1449417600
RETURN target2.nodeid as l_id, target2.timestamp as l_ts, type(r3) as r_type, target3.nodeid as r_id, target3.timestamp as r_ts
LIMIT 5000;