Neo4j系列导航：
neo4j安装及简单实践
cypher语法基础
cypher插入语法
cypher插入语法
cypher查询语法
cypher通用语法
cypher函数语法
neo4j索引及调优
1.简介
计划器提示用于在为查询构建执行计划时影响计划器的决策。规划器提示在使用USING关键字的查询中指定。
强制计划器行为是一个高级特性，只有经验丰富的开发人员和/或数据库管理员才应该谨慎使用，因为它可能导致查询性能不佳。
当执行查询时，Neo4j需要决定在查询图中开始匹配的位置。这是通过查看MATCH子句和WHERE条件并使用该信息查找有用的索引或其他起点来完成的。
但是，所选的索引可能并不总是最佳选择。有时可能有多个候选索引，查询规划器从性能的角度选择次优的索引。此外，在某些情况下(尽管很少)，最好根本不使用索引。
可以通过USING关键字强制Neo4j使用特定的起点。这叫做给计划提示。
有三种类型的计划提示:
- Index hints：索引提示
- Scan hints：扫描提示。
- Join hints：连接提示。
PROFILE
MATCH
(s:Scientist {born: 1850})-[:RESEARCHED]->
(sc:Science)<-[i:INVENTED_BY {year: 560}]-
(p:Pioneer {born: 525})-[:LIVES_IN]->
(c:City)-[:PART_OF]->
(cc:Country {formed: 411})
RETURN *
上面的查询将在本页的一些示例中使用。在没有任何提示的情况下，只使用一个索引而不使用连接。
查询计划：
Planner COST
Runtime PIPELINED
Runtime version 5.18
Batch size 128
Operator Details Estimated Rows Rows DB Hits Memory (Bytes) Page Cache Hits/Misses Time (ms) Pipeline +ProduceResults c, cc, i, p, s, sc 0 0 0 6/1 0.506 Fused in Pipeline 0 +Filter s.born = $autoint_0 AND s:Scientist 0 0 0 6/1 0.506 Fused in Pipeline 0 +Expand(All) (sc)<-[anon_0:RESEARCHED]-(s) 0 0 0 6/1 0.506 Fused in Pipeline 0 +Filter i.year = $autoint_1 AND sc:Science 0 0 0 6/1 0.506 Fused in Pipeline 0 +Expand(All) §-[i:INVENTED_BY]->(sc) 0 0 0 6/1 0.506 Fused in Pipeline 0 +Filter p.born = $autoint_2 AND p:Pioneer 0 0 2 6/1 0.506 Fused in Pipeline 0 +Expand(All) ©<-[anon_1:LIVES_IN]-§ 1 1 3 6/1 0.506 Fused in Pipeline 0 +Filter c:City 1 1 2 6/1 0.506 Fused in Pipeline 0 +Expand(All) (cc)<-[anon_2:PART_OF]-© 1 1 2 6/1 0.506 Fused in Pipeline 0 +NodeIndexSeek RANGE INDEX cc:Country(formed) WHERE formed = $autoint_3 1 1 2 120 6/1 0.506 Fused in Pipeline 0 Total database accesses: 11, total allocated memory: 208
2.索引提示（Index hints）
索引提示用于指定计划器应使用哪个索引作为起点。在索引统计信息对于已知当前查询使用的特定值不准确的情况下，这可能是有益的，这将导致计划器选择非最优索引。在适用的MATCH子句之后提供索引提示。
可用的索引提示有:
| 提示 | 计划完成 |
|---|---|
USING [RANGE/TEXT/POINT] INDEX variable:Label(property) | NodeIndexScan, NodeIndexSeek |
USING [RANGE/TEXT/POINT] INDEX SEEK variable:Label(property) | NodeIndexSeek |
USING [RANGE/TEXT/POINT] INDEX variable:RELATIONSHIP_TYPE(property) | DirectedRelationshipIndexScan, UndirectedRelationshipIndexScan, DirectedRelationshipIndexSeek, UndirectedRelationshipIndexSeek |
USING [RANGE/TEXT/POINT] INDEX SEEK variable:RELATIONSHIP_TYPE(property) | DirectedRelationshipIndexSeek, UndirectedRelationshipIndexSeek |
当为提示指定索引类型时，例如RANGE、TEXT或POINT，该提示只能在指定类型的索引可用时才能实现。当没有指定索引类型时，提示可以由任何索引类型完成。
使用提示绝不能改变查询的结果。因此，只有当计划器知道使用指定类型的索引不会改变结果时，才可以实现带有指定索引类型的提示。
2.1.使用节点索引提示进行查询
实例：
可以对上面的查询进行调优，以选择不同的索引作为起点。
PROFILE
MATCH
(s:Scientist {born: 1850})-[:RESEARCHED]->
(sc:Science)<-[i:INVENTED_BY {year: 560}]-
(p:Pioneer {born: 525})-[:LIVES_IN]->
(c:City)-[:PART_OF]->
(cc:Country {formed: 411})
USING INDEX p:Pioneer(born)
RETURN *
查询计划：
Planner COST
Runtime PIPELINED
Runtime version 5.18
Batch size 128
Operator Details Estimated Rows Rows DB Hits Memory (Bytes) Page Cache Hits/Misses Time (ms) Pipeline +ProduceResults c, cc, i, p, s, sc 0 0 0 4/1 0.491 Fused in Pipeline 0 +Filter cc.formed = $autoint_3 AND cc:Country 0 0 0 4/1 0.491 Fused in Pipeline 0 +Expand(All) ©-[anon_2:PART_OF]->(cc) 0 0 0 4/1 0.491 Fused in Pipeline 0 +Filter c:City 0 0 0 4/1 0.491 Fused in Pipeline 0 +Expand(All) §-[anon_1:LIVES_IN]->© 0 0 0 4/1 0.491 Fused in Pipeline 0 +Filter s.born = $autoint_0 AND s:Scientist 0 0 0 4/1 0.491 Fused in Pipeline 0 +Expand(All) (sc)<-[anon_0:RESEARCHED]-(s) 0 0 0 4/1 0.491 Fused in Pipeline 0 +Filter i.year = $autoint_1 AND sc:Science 0 0 2 4/1 0.491 Fused in Pipeline 0 +Expand(All) §-[i:INVENTED_BY]->(sc) 2 2 6 4/1 0.491 Fused in Pipeline 0 +NodeIndexSeek RANGE INDEX p:Pioneer(born) WHERE born=$autoint_2 2 2 3 120 4/1 0.491 Fused in Pipeline 0 Total database accesses: 11, total allocated memory: 208
2.2.使用节点文本索引提示进行查询
实例：
下面的查询可以调优为选择一个文本索引。
PROFILE
MATCH (c:Country) USING TEXT INDEX c:Country(name) WHERE c.name = 'Country7' RETURN *
查询计划：
Planner COST
Runtime PIPELINED
Runtime version 5.18
Batch size 128
Operator Details Estimated Rows Rows DB Hits Memory (Bytes) Page Cache Hits/Misses Time (ms) Pipeline +ProduceResults c 1 1 0 2/0 0.949 Fused in Pipeline 0 +NodeIndexSeek TEXT INDEX c:Country(name) WHERE name = $autostring_0 1 1 2 120 2/0 0.949 Fused in Pipeline 0 Total database accesses: 2, total allocated memory: 184
2.3.使用关系索引提示进行查询
实例：
上面的查询可以调优为选择一个关系索引作为起点。
PROFILE
MATCH
(s:Scientist {born: 1850})-[:RESEARCHED]->
(sc:Science)<-[i:INVENTED_BY {year: 560}]-
(p:Pioneer {born: 525})-[:LIVES_IN]->
(c:City)-[:PART_OF]->
(cc:Country {formed: 411})
USING INDEX i:INVENTED_BY(year)
RETURN *
查询计划：
Planner COST
Runtime PIPELINED
Runtime version 5.18
Batch size 128
Operator Details Estimated Rows Rows DB Hits Memory (Bytes) Page Cache Hits/Misses Time (ms) Pipeline +ProduceResults c, cc, i, p, s, sc 0 0 0 5/1 0.461 Fused in Pipeline 0 +Filter cc.formed = $autoint_3 AND cc:Country 0 0 0 5/1 0.461 Fused in Pipeline 0 +Expand(All) ©-[anon_2:PART_OF]->(cc) 0 0 0 5/1 0.461 Fused in Pipeline 0 +Filter c:City 0 0 0 5/1 0.461 Fused in Pipeline 0 +Expand(All) §-[anon_1:LIVES_IN]->© 0 0 0 5/1 0.461 Fused in Pipeline 0 +Filter s.born = $autoint_0 AND s:Scientist 0 0 0 5/1 0.461 Fused in Pipeline 0 +Expand(All) (sc)<-[anon_0:RESEARCHED]-(s) 0 0 0 5/1 0.461 Fused in Pipeline 0 +Filter p.born = $autoint_2 AND sc:Science AND p:Pioneer 0 0 4 5/1 0.461 Fused in Pipeline 0 +DirectedRelationshipIndexSeek RANGE INDEX §-[i:INVENTED_BY(year)]->(sc) WHERE year = $autoint_1 2 2 3 120 5/1 0.461 Fused in Pipeline 0 Total database accesses: 7, total allocated memory: 208
2.4.使用关系文本索引提示进行查询
实例：
下面的查询可以调优为选择一个文本索引。
PROFILE
MATCH ()-[i:INVENTED_BY]->()
USING TEXT INDEX i:INVENTED_BY(location)
WHERE i.location = 'Location7'
RETURN *
查询计划：
Planner COST
Runtime PIPELINED
Runtime version 5.18
Batch size 128
Operator Details Estimated Rows Rows DB Hits Memory (Bytes) Page Cache Hits/Misses Time (ms) Pipeline +ProduceResults i 1 1 0 3/0 1.079 Fused in Pipeline 0 +DirectedRelationshipIndexSeek TEXT INDEX (anon_0)-[i:INVENTED_BY(location)]->(anon_1) WHERE location = $autostring_01 1 2 120 3/0 1.079 Fused in Pipeline 0 Total database accesses: 2, total allocated memory: 184
2.5.使用多个索引提示进行查询
提供一个索引提示改变了查询的起始点，但是计划仍然是线性的，这意味着它只有一个起始点。如果我们给计划器另一个索引提示，我们就迫使它使用两个起始点，在匹配的两端各有一个。然后，它将使用连接操作符连接这两个分支。
实例：
PROFILE
MATCH
(s:Scientist {born: 1850})-[:RESEARCHED]->
(sc:Science)<-[i:INVENTED_BY {year: 560}]-
(p:Pioneer {born: 525})-[:LIVES_IN]->
(c:City)-[:PART_OF]->
(cc:Country {formed: 411})
USING INDEX s:Scientist(born)
USING INDEX cc:Country(formed)
RETURN *
查询计划：
Planner COST
Runtime PIPELINED
Runtime version 5.18
Batch size 128
Operator Details Estimated Rows Rows DB Hits Memory (Bytes) Page Cache Hits/Misses Time (ms) Pipeline +ProduceResults c, cc, i, p, s, sc 0 0 0 0/0 0.000 In Pipeline 2 +NodeHashJoin sc 0 0 0 432 In Pipeline 2 +Expand(All) (s)-[anon_0:RESEARCHED]->(sc) 1 0 0 0/0 0.000 Fused in Pipeline 1 +NodeIndexSeek RANGE INDEX s:Scientist(born) WHERE born=$autoint_0 1 0 0 120 0/0 0.000 Fused in Pipeline 1 +Filter i.year = $autoint_1 AND sc:Science 0 0 0 7/0 0.494 Fused in Pipeline 0 +Expand(All) §-[i:INVENTED_BY]->(sc) 0 0 0 7/0 0.494 Fused in Pipeline 0 +Filter p.born = $autoint_2 AND p:Pioneer 0 0 2 7/0 0.494 Fused in Pipeline 0 +Expand(All) ©<-[anon_1:LIVES_IN]-§ 1 1 3 7/0 0.494 Fused in Pipeline 0 +Filter c:City 1 1 2 7/0 0.494 Fused in Pipeline 0 +Expand(All) (cc)<-[anon_2:PART_OF]-© 1 1 2 7/0 0.494 Fused in Pipeline 0 +NodeIndexSeek RANGE INDEX cc:Country(formed) WHERE formed=$autoint_3 1 1 2 120 7/0 0.494 Fused in Pipeline 0 Total database accesses: 11, total allocated memory: 768
2.6.使用带分离的多个索引提示进行查询
如果查询在WHERE子句中包含一个析取(OR)，那么提供多个索引提示也很有用。这确保了所有的暗示索引都被使用，并且结果随后用Union和Distinct连接在一起。
实例：
PROFILE
MATCH (country:Country)
USING INDEX country:Country(name)
USING INDEX country:Country(formed)
WHERE country.formed = 500 OR country.name STARTS WITH "A"
RETURN *
查询计划：
Planner COST
Runtime PIPELINED
Runtime version 5.18
Batch size 128
Operator Details Estimated Rows Rows DB Hits Memory (Bytes) Page Cache Hits/Misses Time (ms) Pipeline +ProduceResults country 1 1 0 1/0 0.213 Fused in Pipeline 2 +Distinct country 1 1 0 224 1/0 0.213 Fused in Pipeline 2 +Union 2 1 0 80 1/0 0.213 Fused in Pipeline 2 +NodeIndexSeek RANGE INDEX country:Country(formed) WHERE formed = $autoint_0 1 1 2 120 1/0 0.101 In Pipeline 1 +NodeIndexSeekByRange RANGE INDEX country:Country(name) WHERE name STARTS WITH $autostring_1 1 0 1 120 0/1 0.307 In Pipeline 0 Total database accesses: 3, total allocated memory: 320
Cypher®通常会提供一个计划，在没有提示的情况下使用所有索引进行分离。但是，如果谓词看起来不是很有选择性，它可能决定计划NodeByLabelScan。在这种情况下，索引提示可能很有用。
3.扫描提示（Scan hints）
如果您的查询匹配索引的大部分，那么扫描标签或关系类型并过滤掉不匹配的行可能会更快。 为此，您可以在适用的MATCH子句之后使用USING SCAN variable:Label用于节点索引，并使用USING SCAN variable:RELATIONSHIP_TYPE用于关系索引。这将迫使Cypher不使用本可以使用的索引，而是进行标签扫描/关系类型扫描。可以使用相同的提示来强制在不适用索引的情况下使用起点。
3.1.标签扫描提示
实例：
PROFILE
MATCH
(s:Scientist {born: 1850})-[:RESEARCHED]->
(sc:Science)<-[i:INVENTED_BY {year: 560}]-
(p:Pioneer {born: 525})-[:LIVES_IN]->
(c:City)-[:PART_OF]->
(cc:Country {formed: 411})
USING SCAN s:Scientist
RETURN *
查询计划：
Planner COST
Runtime PIPELINED
Runtime version 5.18
Batch size 128
Operator Details Estimated Rows Rows DB Hits Memory (Bytes) Page Cache Hits/Misses Time (ms) Pipeline +ProduceResults c, cc, i, p, s, sc 0 0 0 11/0 0.512 Fused in Pipeline 0 +Filter cc.formed = $autoint_3 AND cc:Country 0 0 0 11/0 0.512 Fused in Pipeline 0 +Expand(All) ©-[anon_2:PART_OF]->(cc) 0 0 0 11/0 0.512 Fused in Pipeline 0 +Filter c:City 0 0 0 11/0 0.512 Fused in Pipeline 0 +Expand(All) §-[anon_1:LIVES_IN]->© 0 0 0 11/0 0.512 Fused in Pipeline 0 +Filter i.year = $autoint_1 AND p.born = $autoint_2 AND p:Pioneer 0 0 1 11/0 0.512 Fused in Pipeline 0 +Expand(All) (sc)<-[i:INVENTED_BY]-§ 1 1 3 11/0 0.512 Fused in Pipeline 0 +Filter sc:Science 1 1 2 11/0 0.512 Fused in Pipeline 0 +Expand(All) (s)-[anon_0:RESEARCHED]->(sc) 1 1 2 11/0 0.512 Fused in Pipeline 0 +Filter s.born = $autoint_0 1 1 200 11/0 0.512 Fused in Pipeline 0 +NodeByLabelScan s:Scientist 100 100 101 120 11/0 0.512 Fused in Pipeline 0 Total database accesses: 309, total allocated memory: 216
3.2.关系类型扫描提示
实例：
PROFILE
MATCH
(s:Scientist {born: 1850})-[:RESEARCHED]->
(sc:Science)<-[i:INVENTED_BY {year: 560}]-
(p:Pioneer {born: 525})-[:LIVES_IN]->
(c:City)-[:PART_OF]->
(cc:Country {formed: 411})
USING SCAN i:INVENTED_BY
RETURN *
查询计划：
Planner COST
Runtime PIPELINED
Runtime version 5.18
Batch size 128
Operator Details Estimated Rows Rows DB Hits Memory (Bytes) Page Cache Hits/Misses Time (ms) Pipeline +ProduceResults c, cc, i, p, s, sc 0 0 0 9/0 0.910 Fused in Pipeline 0 +Filter cc.formed = $autoint_3 AND cc:Country 0 0 0 9/0 0.910 Fused in Pipeline 0 +Expand(All) ©-[anon_2:PART_OF]->(cc) 0 0 0 9/0 0.910 Fused in Pipeline 0 +Filter c:City 0 0 0 9/0 0.910 Fused in Pipeline 0 +Expand(All) §-[anon_1:LIVES_IN]->© 0 0 0 9/0 0.910 Fused in Pipeline 0 +Filter s.born = $autoint_0 AND s:Scientist 0 0 0 9/0 0.910 Fused in Pipeline 0 +Expand(All) (sc)<-[anon_0:RESEARCHED]-(s) 0 0 0 9/0 0.910 Fused in Pipeline 0 +Filter i.year = $autoint_1 AND p.born = $autoint_2 AND sc:Science AND p:Pioneer 0 0 204 9/0 0.910 Fused in Pipeline 0 +DirectedRelationshipTypeScan §-[i:INVENTED_BY]->(sc) 100 100 101 120 9/0 0.910 Fused in Pipeline 0 Total database accesses: 305, total allocated memory: 208
3.3.使用带分离的多个扫描提示进行查询
如果查询在WHERE子句中包含一个析取(OR)，那么提供多个扫描提示也很有用。这确保了所有涉及的标签谓词都由UnionNodeByLabelsScan解析。
实例：
PROFILE
MATCH (person)
USING SCAN person:Pioneer
USING SCAN person:Scientist
WHERE person:Pioneer OR person:Scientist
RETURN *
查询计划：
Planner COST
Runtime PIPELINED
Runtime version 5.18
Batch size 128
Operator Details Estimated Rows Rows DB Hits Memory (Bytes) Page Cache Hits/Misses Time (ms) Pipeline +ProduceResults person 180 200 0 6/0 1.740 Fused in Pipeline 0 +UnionNodeByLabelsScan person:Pioneer Scientist 180 200 202 120 6/0 1.740 Total database accesses: 202, total allocated memory: 184
Cypher通常会提供一个计划，使用扫描的分离没有提示。但是，如果标签谓词看起来不是很有选择性，它可能决定计划一个AllNodeScan，然后是一个Filter`。在这种情况下，扫描提示可能很有用。
4.连接提示（join hints）
连接提示是最高级的提示类型，不用于查找查询执行计划的起始点，而是用于强制在指定点进行连接。这意味着计划中必须有多个起始点(叶)，以便查询能够连接从这些叶上升的两个分支。由于这种性质，连接和随后的连接提示将迫使计划器寻找额外的起点，在没有更多好的起点的情况下，可能会选择一个非常糟糕的起点。这将对查询性能产生负面影响。在其他情况下，这个提示可能会迫使计划者选择一个看似糟糕的起点，但实际上却是一个非常好的起点。
4.1.提示单个节点上的连接
在上面使用多个索引提示的示例中，我们看到规划器选择执行连接，但不是在p节点上。通过在索引提示之外提供连接提示，我们可以强制连接发生在p节点上。
实例：
PROFILE
MATCH
(s:Scientist {born: 1850})-[:RESEARCHED]->
(sc:Science)<-[i:INVENTED_BY {year: 560}]-
(p:Pioneer {born: 525})-[:LIVES_IN]->
(c:City)-[:PART_OF]->
(cc:Country {formed: 411})
USING INDEX s:Scientist(born)
USING INDEX cc:Country(formed)
USING JOIN ON p
RETURN *
查询计划：
Planner COST
Runtime PIPELINED
Runtime version 5.18
Batch size 128
Operator Details Estimated Rows Rows DB Hits Memory (Bytes) Page Cache Hits/Misses Time (ms) Pipeline +ProduceResults c, cc, i, p, s, sc 0 0 0 0/0 0.000 In Pipeline 2 +NodeHashJoin p 0 0 0 432 In Pipeline 2 +Filter cache[p.born] = $autoint_2 1 0 0 0/0 0.000 Fused in Pipeline 1 +Expand(All) ©<-[anon_1:LIVES_IN]-§ 1 0 0 0/0 0.000 Fused in Pipeline 1 +Filter c:City 1 0 0 0/0 0.000 Fused in Pipeline 1 +Expand(All) (cc)<-[anon_2:PART_OF]-© 1 0 0 0/0 0.000 Fused in Pipeline 1 +NodeIndexSeek RANGE INDEX cc:Country(formed) WHERE formed = $autoint_3 1 0 0 120 0/0 0.000 Fused in Pipeline 1 +Filter i.year = $autoint_1 AND cache[p.born] = $autoint_2 AND p:Pioneer 0 0 1 6/1 0.515 Fused in Pipeline 0 +Expand(All) (sc)<-[i:INVENTED_BY]-§ 1 1 3 6/1 0.515 Fused in Pipeline 0 +Filter sc:Science 1 1 2 6/1 0.515 Fused in Pipeline 0 +Expand(All) (s)-[anon_0:RESEARCHED]->(sc) 1 1 2 6/1 0.515 Fused in Pipeline 0 +NodeIndexSeek RANGE INDEX s:Scientist(born) WHERE born = $autoint_0 1 1 2 120 6/1 0.515 Fused in Pipeline 0 Total database accesses: 10, total allocated memory: 768
4.2.提示单个节点上的连接
连接提示也可以用来强制计划器选择一个NodeLeftOuterHashJoin或NodeRightOuterHashJoin来解决一个OPTIONAL MATCH。在大多数情况下，计划器宁愿使用OptionalExpand。
实例：
PROFILE
MATCH (s:Scientist {born: 1850})
OPTIONAL MATCH (s)-[:RESEARCHED]->(sc:Science)
RETURN *
在没有任何提示的情况下，计划器没有使用连接来解决OPTIONAL MATCH。
查询计划：
Planner COST
Runtime PIPELINED
Runtime version 5.18
Batch size 128
Operator Details Estimated Rows Rows DB Hits Memory (Bytes) Page Cache Hits/Misses Time (ms) Pipeline +ProduceResults s, sc 1 1 0 +OptionalExpand(All) (s)-[anon_0:RESEARCHED]->(sc) WHERE sc:Science 1 1 4 +NodeIndexSeek RANGE INDEX s:Scientist(born) WHERE born = $autoint_0 1 1 2 120 6/0 0.560 Fused in Pipeline 0 Total database accesses: 6, total allocated memory: 184
现在计划器使用连接来解决OPTIONAL MATCH。
实例：
PROFILE
MATCH (s:Scientist {born: 1850})
OPTIONAL MATCH (s)-[:RESEARCHED]->(sc:Science)
USING JOIN ON s
RETURN *
查询计划：
Planner COST
Runtime PIPELINED
Runtime version 5.18
Batch size 128
Operator Details Estimated Rows Rows DB Hits Memory (Bytes) Page Cache Hits/Misses Time (ms) Pipeline +ProduceResults s, sc 1 1 0 2/0 0.213 In Pipeline 2 +NodeLeftOuterHashJoin s 1 1 0 3112 0.650 In Pipeline 2 +Expand(All) (sc)<-[anon_0:RESEARCHED]-(s) 100 100 300 4/0 0.786 Fused in Pipeline 1 +NodeByLabelScan sc:Science 100 100 101 120 4/0 0.786 Fused in Pipeline 1 +NodeIndexSeek RANGE INDEX s:Scientist(born) WHERE born = $autoint_0 1 1 2 120 1/0 0.214 In Pipeline 0 Total database accesses: 403, total allocated memory: 3192
扫一扫
3568
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
