优化Cypher查询：将Trending类list按:a数量排序且耗时降至10ms内

我的目标是让查询返回数据耗时小于10ms，当前耗时在50ms以内。节点关系如下：

(l:list)<-[:IN_LIST]-(p:product)<-[:PRODUCT]-(a:a)

数据库中存在超过2000万的:a节点，这严重拖慢了查询速度。但我需要按:a节点的数量对:list进行排序，是否有快速获取size/count的方法？

初始查询及执行计划 #

查询语句 #

GRAPH.profile g "MATCH (l:list{kind: 'Trending'})
          MATCH (l)<-[:IN_LIST]-(p:product)
          WITH p, size((p)<-[:PRODUCT]-()) as count, l ORDER BY count DESC
          WITH count(l) as total, collect(l{.id})[0..20] as lists
          RETURN *"

执行计划 #

1) "Results | Records produced: 1, Execution time: 0.001034 ms"
2) "    Project | Records produced: 1, Execution time: 0.007407 ms"
3) "        Aggregate | Records produced: 1, Execution time: 0.331449 ms"
4) "            Sort | Records produced: 636, Execution time: 0.155567 ms"
5) "                Project | Records produced: 636, Execution time: 0.383724 ms"
6) "                    Apply | Records produced: 636, Execution time: 4.117716 ms"
7) "                        Conditional Traverse | (p:product)->(p:product) | Records produced: 636, Execution time: 0.409086 ms"
8) "                            Filter | Records produced: 39, Execution time: 0.050714 ms"
9) "                                Node By Label Scan | (p:list) | Records produced: 77, Execution time: 0.020350 ms"
10) "                        Aggregate | Records produced: 636, Execution time: 19.862072 ms"
11) "                            Conditional Traverse | (anon_0)-[anon_1:PRODUCT]->(anon_0) | Records produced: 46947, Execution time: 65.262939 ms"
12) "                                Argument | Records produced: 636, Execution time: 0.050361 ms"

更新后的查询及执行计划（耗时约30ms） #

查询语句 #

GRAPH.profile g "MATCH (l:list{kind: 'Trending'})
          MATCH (l)<-[:IN_LIST]-(p:product)<-[:PRODUCT]-(a)
          WITH l ORDER BY count(a) ASC
          RETURN count(l) as total, collect(l{.id})[0..20] as lists"

执行计划 #

1) "Results | Records produced: 1, Execution time: 0.001405 ms"
2) "    Aggregate | Records produced: 1, Execution time: 0.055607 ms"
3) "        Sort | Records produced: 20, Execution time: 0.007036 ms"
4) "            Aggregate | Records produced: 20, Execution time: 9.456037 ms"
5) "                Conditional Traverse | (a)->(a) | Records produced: 46947, Execution time: 19.134115 ms"
6) "                    Filter | Records produced: 39, Execution time: 0.057806 ms"
7) "                        Node By Label Scan | (l:list) | Records produced: 77, Execution time: 0.019137 ms"

优化方案（将耗时压至10ms内） #

1. 预计算并存储关联计数 #

由于:a节点数量巨大，实时计算count(a)或size()必然耗时。最有效的方式是预计算每个:product关联的:a节点数量，并将这个值存储为:product节点的属性（比如a_count）。

• 初始化批量计算：

  MATCH (p:product)<-[:PRODUCT]-(a:a)
  WITH p, count(a) as cnt
  SET p.a_count = cnt
  

• 实时维护：在创建/删除:PRODUCT关系时，通过业务逻辑或数据库触发器同步更新p.a_count（新增关系时p.a_count +=1，删除时p.a_count -=1）。

2. 基于预计算属性优化查询 #

使用预存的a_count属性，查询时无需遍历:a节点，直接基于属性求和排序：

GRAPH.profile g "MATCH (l:list{kind: 'Trending'})<-[:IN_LIST]-(p:product)
          WITH l, sum(p.a_count) as total_a
          ORDER BY total_a DESC
          RETURN count(l) as total, collect(l{.id})[0..20] as lists"

这个查询仅在:product和:list层级做聚合操作，完全避开了对2000万:a节点的遍历，耗时能大幅降低。

3. 索引优化 #

• 为:list节点的kind属性创建索引，加速初始节点查找：

  CREATE INDEX idx_list_kind FOR (l:list) ON (l.kind)
  

• 若:product的a_count频繁用于排序，可创建属性索引进一步优化排序效率：

  CREATE INDEX idx_product_acount FOR (p:product) ON (p.a_count)
  

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