Cypher 规划器索引提示
规划器提示用于影响规划器在构建查询执行计划时的决策。规划器提示在查询中使用 USING
关键字指定。
强制规划器行为是一项高级功能,应仅由经验丰富的开发人员和/或数据库管理员谨慎使用,因为它可能导致查询性能不佳。 |
执行查询时,Neo4j 需要决定在查询图的何处开始匹配。这通过查看 MATCH
子句和 WHERE
条件并利用这些信息查找有用的索引或其他起始点来完成。
然而,选定的索引可能并非总是最佳选择。有时有多个索引是可能的候选,而查询规划器从性能角度选择了次优的索引。此外,在某些情况下(尽管很少),根本不使用索引会更好。
可以通过 USING
关键字强制 Neo4j 使用特定的起始点。这称为提供规划器提示。
规划器提示有三种类型:
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 2025.05 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 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | s.born = $autoint_0 AND s:Scientist | 0 | 0 | 0 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (sc)<-[anon_0:RESEARCHED]-(s) | 0 | 0 | 0 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | i.year = $autoint_1 AND sc:Science | 0 | 0 | 0 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (p)-[i:INVENTED_BY]->(sc) | 0 | 0 | 0 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | p.born = $autoint_2 AND p:Pioneer | 0 | 0 | 2 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (c)<-[anon_1:LIVES_IN]-(p) | 1 | 1 | 3 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | c:City | 1 | 1 | 2 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (cc)<-[anon_2:PART_OF]-(c) | 1 | 1 | 2 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +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
索引提示
索引提示用于指定规划器应使用哪个索引作为起始点。当索引统计信息对于当前查询已知使用的特定值不准确时,这会很有益,因为不准确的统计信息会导致规划器选择非最优索引。索引提示在适用的 MATCH
子句之后提供。
可用的索引提示有:
提示 | 由计划满足 |
---|---|
|
|
|
|
|
|
|
|
为提示指定索引类型时,例如 RANGE
、TEXT
或 POINT
,只有当指定类型的索引可用时,该提示才能被满足。如果未指定索引类型,则任何索引类型都可以满足该提示。
使用提示绝不能改变查询结果。因此,只有当规划器知道使用指定类型的索引不会改变结果时,带有指定索引类型的提示才能被满足。更多详细信息请参阅索引的使用。 |
可以提供多个索引提示,但请记住,多个起始点将需要在查询计划的后期使用潜在成本较高的连接。
使用节点索引提示的查询
上述查询可以调整为选择不同的索引作为起始点。
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 2025.05 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 | | | | | | | +-----------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | cc.formed = $autoint_3 AND cc:Country | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (c)-[anon_2:PART_OF]->(cc) | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | c:City | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (p)-[anon_1:LIVES_IN]->(c) | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | s.born = $autoint_0 AND s:Scientist | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (sc)<-[anon_0:RESEARCHED]-(s) | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | i.year = $autoint_1 AND sc:Science | 0 | 0 | 2 | | | | | | | +-----------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (p)-[i:INVENTED_BY]->(sc) | 2 | 2 | 6 | | | | | | | +-----------------------------------------------------+----------------+------+---------+----------------+ | | | | +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
使用节点文本索引提示的查询
以下查询可以调整为选择文本索引。
PROFILE
MATCH (c:Country)
USING TEXT INDEX c:Country(name)
WHERE c.name = 'Country7'
RETURN *
Planner COST Runtime PIPELINED Runtime version 2025.05 Batch size 128 +-----------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +-----------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | c | 1 | 1 | 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
使用关系索引提示的查询
上述查询可以调整为选择关系索引作为起始点。
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 2025.05 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 | | | | | | | +---------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | cc.formed = $autoint_3 AND cc:Country | 0 | 0 | 0 | | | | | | | +---------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (c)-[anon_2:PART_OF]->(cc) | 0 | 0 | 0 | | | | | | | +---------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | c:City | 0 | 0 | 0 | | | | | | | +---------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (p)-[anon_1:LIVES_IN]->(c) | 0 | 0 | 0 | | | | | | | +---------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | s.born = $autoint_0 AND s:Scientist | 0 | 0 | 0 | | | | | | | +---------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (sc)<-[anon_0:RESEARCHED]-(s) | 0 | 0 | 0 | | | | | | | +---------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | p.born = $autoint_2 AND sc:Science AND p:Pioneer | 0 | 0 | 4 | | | | | | | +---------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +DirectedRelationshipIndexSeek | RANGE INDEX (p)-[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
使用关系文本索引提示的查询
以下查询可以调整为选择文本索引。
PROFILE
MATCH ()-[i:INVENTED_BY]->()
USING TEXT INDEX i:INVENTED_BY(location)
WHERE i.location = 'Location7'
RETURN *
Planner COST Runtime PIPELINED Runtime version 2025.05 Batch size 128 +--------------------------------+----------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +--------------------------------+----------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | i | 1 | 1 | 0 | | | | | | | +----------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +DirectedRelationshipIndexSeek | TEXT INDEX (anon_0)-[i:INVENTED_BY(location)]->(anon_1) WHERE location = $autostring_0 | 1 | 1 | 2 | 120 | 3/0 | 1.079 | Fused in Pipeline 0 | +--------------------------------+----------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 2, total allocated memory: 184
使用多个索引提示的查询
提供一个索引提示改变了查询的起始点,但计划仍然是线性的,意味着它只有一个起始点。如果我们再给规划器一个索引提示,我们就会强制它使用两个起始点,匹配的两端各一个。然后它将使用连接操作符连接这两个分支。
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 2025.05 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 | | | | +----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+ | | +NodeHashJoin | sc | 0 | 0 | 0 | 432 | | | In Pipeline 2 | | |\ +----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | | +Expand(All) | (s)-[anon_0:RESEARCHED]->(sc) | 1 | 0 | 0 | | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | | +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 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (p)-[i:INVENTED_BY]->(sc) | 0 | 0 | 0 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | p.born = $autoint_2 AND p:Pioneer | 0 | 0 | 2 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (c)<-[anon_1:LIVES_IN]-(p) | 1 | 1 | 3 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | c:City | 1 | 1 | 2 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (cc)<-[anon_2:PART_OF]-(c) | 1 | 1 | 2 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +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
使用带有析取的多索引提示的查询
如果查询的 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 2025.05 Batch size 128 +-----------------------+------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +-----------------------+------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | country | 1 | 1 | 0 | | | | | | | +------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Distinct | country | 1 | 1 | 0 | 224 | | | | | | +------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +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
。在这种情况下,索引提示会很有用。
扫描提示
如果你的查询匹配了索引的很大一部分,那么扫描标签或关系类型并过滤掉不匹配的行可能会更快。为此,你可以在适用于节点索引的 MATCH
子句后使用 USING SCAN variable:Label
,对于关系索引使用 USING SCAN variable:RELATIONSHIP_TYPE
。这将强制 Cypher 不使用可能已使用的索引,而是执行标签扫描/关系类型扫描。你可以使用相同的提示来强制在没有适用索引的情况下选择一个起始点。
提示标签扫描
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 2025.05 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 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | cc.formed = $autoint_3 AND cc:Country | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (c)-[anon_2:PART_OF]->(cc) | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | c:City | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (p)-[anon_1:LIVES_IN]->(c) | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | i.year = $autoint_1 AND p.born = $autoint_2 AND p:Pioneer | 0 | 0 | 1 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (sc)<-[i:INVENTED_BY]-(p) | 1 | 1 | 3 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | sc:Science | 1 | 1 | 2 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (s)-[anon_0:RESEARCHED]->(sc) | 1 | 1 | 2 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | s.born = $autoint_0 | 1 | 1 | 200 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +NodeByLabelScan | s:Scientist | 100 | 100 | 101 | 120 | 11/0 | 0.512 | Fused in Pipeline 0 | +------------------+-----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 309, total allocated memory: 216
提示关系类型扫描
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 2025.05 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 | | | | | | | +--------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | cc.formed = $autoint_3 AND cc:Country | 0 | 0 | 0 | | | | | | | +--------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (c)-[anon_2:PART_OF]->(cc) | 0 | 0 | 0 | | | | | | | +--------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | c:City | 0 | 0 | 0 | | | | | | | +--------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (p)-[anon_1:LIVES_IN]->(c) | 0 | 0 | 0 | | | | | | | +--------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | s.born = $autoint_0 AND s:Scientist | 0 | 0 | 0 | | | | | | | +--------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (sc)<-[anon_0:RESEARCHED]-(s) | 0 | 0 | 0 | | | | | | | +--------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | i.year = $autoint_1 AND p.born = $autoint_2 AND sc:Science AND p:Pioneer | 0 | 0 | 204 | | | | | | | +--------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +DirectedRelationshipTypeScan | (p)-[i:INVENTED_BY]->(sc) | 100 | 100 | 101 | 120 | 9/0 | 0.910 | Fused in Pipeline 0 | +-------------------------------+--------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 305, total allocated memory: 208
使用带有析取的多扫描提示的查询
如果查询的 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 2025.05 Batch size 128 +------------------------+--------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +------------------------+--------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | person | 180 | 200 | 0 | | | | | | | +--------------------------+----------------+------+---------+----------------+ | | | | +UnionNodeByLabelsScan | person:Pioneer|Scientist | 180 | 200 | 202 | 120 | 6/0 | 1.740 | Fused in Pipeline 0 | +------------------------+--------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 202, total allocated memory: 184
Cypher 通常会提供一个在没有提示的情况下对析取使用扫描的计划。然而,如果标签谓词看起来不具备很高的选择性,它可能会决定转而规划一个 AllNodeScan
,后跟一个 Filter
。在这种情况下,扫描提示会很有用。
连接提示
连接提示是最先进的提示类型,不用于查找查询执行计划的起始点,而是用于强制在指定点进行连接。这意味着计划中必须有多个起始点(叶子),以便查询能够连接从这些叶子向上延伸的两个分支。由于这种性质,连接以及随后的连接提示将强制规划器寻找额外的起始点,如果不再有好的起始点,则可能会选择一个非常差的起始点。这将对查询性能产生负面影响。在其他情况下,提示可能会强制规划器选择一个看似糟糕的起始点,但实际上却被证明是一个非常好的起始点。
提示单个节点的连接
在上面使用多个索引提示的示例中,我们看到规划器选择进行连接,但不是在 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 2025.05 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 | | | | +------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+ | | +NodeHashJoin | p | 0 | 0 | 0 | 432 | | | In Pipeline 2 | | |\ +------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | | +Filter | cache[p.born] = $autoint_2 | 1 | 0 | 0 | | | | | | | | +------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | | +Expand(All) | (c)<-[anon_1:LIVES_IN]-(p) | 1 | 0 | 0 | | | | | | | | +------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | | +Filter | c:City | 1 | 0 | 0 | | | | | | | | +------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | | +Expand(All) | (cc)<-[anon_2:PART_OF]-(c) | 1 | 0 | 0 | | | | | | | | +------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | | +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 | | | | | | | +------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (sc)<-[i:INVENTED_BY]-(p) | 1 | 1 | 3 | | | | | | | +------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | sc:Science | 1 | 1 | 2 | | | | | | | +------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (s)-[anon_0:RESEARCHED]->(sc) | 1 | 1 | 2 | | | | | | | +------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +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
提示 OPTIONAL MATCH 的连接
连接提示也可以用来强制规划器选择 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 2025.05 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
PROFILE
MATCH (s:Scientist {born: 1850})
OPTIONAL MATCH (s)-[:RESEARCHED]->(sc:Science)
USING JOIN ON s
RETURN *
现在规划器使用连接来解决 OPTIONAL MATCH
。
Planner COST Runtime PIPELINED Runtime version 2025.05 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 | | | | +-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+ | | +NodeLeftOuterHashJoin | s | 1 | 1 | 0 | 3112 | | 0.650 | In Pipeline 2 | | |\ +-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | | +Expand(All) | (sc)<-[anon_0:RESEARCHED]-(s) | 100 | 100 | 300 | | | | | | | | +-------------------------------------------------------+----------------+------+---------+----------------+ | | | | | +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