优化用于推荐系统的Neo4j Cypher查询

我的Neo4j数据库包含约250万Article节点、50万NamedEntity节点及数千Trend节点，文章覆盖近两年发布时间。需求是基于一组输入的NamedEntityId，找出与Trend关联最佳的文章，当前查询包含完整打分逻辑，但执行缓慢（耗时几秒到数分钟）。通过PROFILE分析确认需要降低查询基数，但希望保留现有打分逻辑，且查询仅需返回30条结果。当前Cypher查询如下：

MATCH (t:Trend)--(x:NamedEntity)-[xv:OCCUR]-(a:Article)-[v:OCCUR]-(n:NamedEntity)
        WHERE n.id IN ["polski związek narciarski_orgName", "Polska_placeName_country", "Kamila Stoch_persName", "Kamila_persName_surname", "Stoch_persName_surname", "Innsbruck_placeName_settlement", "Bischofshofen_placeName_settlement", "niemiecki_placeName_country", "Oberstdorfie_placeName_settlement", "47_placeName_settlement", "Garmisch_placeName_settlement", "Partenkirchen.nTo_placeName_settlement", "Stoch_persName", "katowicki_placeName_settlement", "AWF.nTCS_orgName", "polski_placeName_country", "Polak_placeName_country", "Adam Małysz_persName", "Adam_persName_forename", "Małysz_persName_surname", "Kamil Stoch_persName", "Kamil_persName_forename", "Piotr Żyła_persName", "Piotr_persName_forename", "żyć_persName_surname", "Stoch_persName_addName", "Kaczmarski_persName", "Kaczmarski_persName_surname"] and t.date > date(datetime($currentDay) - duration({days: $daysDelta})) and a.publication_datetime > datetime($currentDay) - duration({days: $daysDelta})
        WITH a,t, collect(distinct n) as distinctLinkNes, collect(distinct x) as distinctTrendNes,
            sum(
                CASE
                    WHEN n.category in ['persName', 'orgName'] THEN 2*v.amount
                    WHEN n.category in ['persName_surname', 'persName_addName'] THEN 1.5*v.amount
                    WHEN n.category in ['date', 'time', 'persName_forename'] THEN 0.5*v.amount
                    ELSE 1.0*v.amount
                END) as linkSum,
            sum(
                CASE
                    WHEN x.category in ['persName', 'orgName'] THEN 2*xv.amount
                    WHEN x.category in ['persName_surname', 'persName_addName'] THEN 1.5*xv.amount
                    WHEN x.category in ['date', 'time', 'persName_forename'] THEN 0.5*xv.amount
                    ELSE 1.0*xv.amount
                END) as trendSum
        WITH a,t, linkSum, trendSum,
            reduce(total=0, ne in distinctLinkNes |
            total + 
            CASE
                WHEN ne.category in ['persName', 'orgName'] THEN 2
                WHEN ne.category in ['persName_surname', 'persName_addName'] THEN 1.5
                WHEN ne.category in ['date', 'time', 'persName_forename'] THEN 0.5
                ELSE 1.0
            END) as distinctLinkNesAmount,
            reduce(total=0, ne in distinctTrendNes |
            total + 
            CASE
                WHEN ne.category in ['persName', 'orgName'] THEN 2
                WHEN ne.category in ['persName_surname', 'persName_addName'] THEN 1.5
                WHEN ne.category in ['date', 'time', 'persName_forename'] THEN 0.5
                ELSE 1.0
            END) as distinctTrendNesAmount
        WITH a, t, distinctTrendNesAmount, trendSum, distinctLinkNesAmount, linkSum,
            (3*distinctTrendNesAmount + trendSum) * t.hits / 1000 as trendScore, 
            (3*distinctLinkNesAmount + linkSum) * ($daysDelta - duration.between(a.publication_datetime, date($currentDay)).days) as articleScore
        WITH a, t, distinctTrendNesAmount, trendSum, trendScore, distinctLinkNesAmount, linkSum, articleScore,
            (articleScore + trendScore) as score
        ORDER BY score DESC
        RETURN a as article, t, distinctTrendNesAmount, trendSum, trendScore, distinctLinkNesAmount, linkSum, articleScore, score
        LIMIT 30

其中daysDelta通常设为7-14天，以下是具体优化建议：

优化建议 #

1. 创建针对性复合索引，缩小初始匹配范围 #

• 对NamedEntity(id)创建唯一索引，加速输入ID的匹配：

  CREATE INDEX ne_id FOR (n:NamedEntity) ON (n.id);
  

• 对Trend(date)创建索引，快速过滤指定时间范围内的Trend：

  CREATE INDEX trend_date FOR (t:Trend) ON (t.date);
  

• 对Article(publication_datetime)创建索引，提前筛选近期文章：

  CREATE INDEX article_pub_dt FOR (a:Article) ON (a.publication_datetime);
  

• 若OCCUR关系的amount频繁用于计算，可创建关系属性索引：

  CREATE INDEX occur_amount FOR ()-[r:OCCUR]-() ON (r.amount);
  

2. 调整匹配顺序，提前过滤数据 #

原查询从Trend开始匹配，建议先从输入的NamedEntity入手，先筛选出关联的近期文章，再关联Trend，减少中间结果集：

// 先匹配输入的NamedEntity，关联近期文章
MATCH (n:NamedEntity)-[v:OCCUR]-(a:Article)
WHERE n.id IN $inputNes AND a.publication_datetime > datetime($currentDay) - duration({days: $daysDelta})
// 再关联Trend及对应的NamedEntity
MATCH (t:Trend)--(x:NamedEntity)-[xv:OCCUR]-(a)
WHERE t.date > date(datetime($currentDay) - duration({days: $daysDelta}))
// 后续打分逻辑保留
WITH a,t, collect(distinct n) as distinctLinkNes, collect(distinct x) as distinctTrendNes,
    sum(
        CASE
            WHEN n.category in ['persName', 'orgName'] THEN 2*v.amount
            WHEN n.category in ['persName_surname', 'persName_addName'] THEN 1.5*v.amount
            WHEN n.category in ['date', 'time', 'persName_forename'] THEN 0.5*v.amount
            ELSE 1.0*v.amount
        END) as linkSum,
    sum(
        CASE
            WHEN x.category in ['persName', 'orgName'] THEN 2*xv.amount
            WHEN x.category in ['persName_surname', 'persName_addName'] THEN 1.5*xv.amount
            WHEN x.category in ['date', 'time', 'persName_forename'] THEN 0.5*xv.amount
            ELSE 1.0*xv.amount
        END) as trendSum
// 后续逻辑不变...

3. 简化打分计算，避免重复判断 #

将重复的CASE判断逻辑提前存储为节点属性，减少查询时的计算开销：

• 给NamedEntity节点新增weight属性并初始化：

  MATCH (n:NamedEntity)
  SET n.weight = CASE
      WHEN n.category in ['persName', 'orgName'] THEN 2
      WHEN n.category in ['persName_surname', 'persName_addName'] THEN 1.5
      WHEN n.category in ['date', 'time', 'persName_forename'] THEN 0.5
      ELSE 1.0
  END
  

• 查询中直接使用weight属性代替重复CASE：

  sum(n.weight * v.amount) as linkSum,
  sum(x.weight * xv.amount) as trendSum,
  reduce(total=0, ne in distinctLinkNes | total + ne.weight) as distinctLinkNesAmount,
  reduce(total=0, ne in distinctTrendNes | total + ne.weight) as distinctTrendNesAmount
  

4. 提前聚合+预过滤，减少数据传递 #

因为最终只需要30条结果，可在中间阶段计算粗略得分并取Top N候选（比如100条），再进行完整打分，大幅减少计算量：

// 中间阶段预过滤示例
WITH a, t, linkSum, trendSum, distinctLinkNes, distinctTrendNes
// 计算粗略得分，提前筛选高潜力候选
WITH a, t, linkSum, trendSum, distinctLinkNes, distinctTrendNes,
     (reduce(total=0, ne in distinctLinkNes | total + ne.weight) + linkSum) + 
     (reduce(total=0, ne in distinctTrendNes | total + ne.weight) + trendSum) as roughScore
ORDER BY roughScore DESC
LIMIT 100
// 再执行完整打分逻辑
WITH a,t, linkSum, trendSum, distinctLinkNes, distinctTrendNes
// 后续完整打分计算...
ORDER BY score DESC
LIMIT 30

5. 避免笛卡尔积，及时去重 #

若一个Article关联多个输入NamedEntity或多个Trend，会产生笛卡尔积导致中间结果爆炸。在匹配后立即对a,t去重：

MATCH (n:NamedEntity)-[v:OCCUR]-(a:Article)
WHERE n.id IN $inputNes AND a.publication_datetime > datetime($currentDay) - duration({days: $daysDelta})
MATCH (t:Trend)--(x:NamedEntity)-[xv:OCCUR]-(a)
WHERE t.date > date(datetime($currentDay) - duration({days: $daysDelta}))
// 对Article和Trend组合去重，避免重复计算
WITH DISTINCT a,t, n,v,x,xv
// 再进行聚合逻辑...

内容的提问来源于stack exchange，提问作者Michał Bogusz