基于网格为查询结果分配评分的性能优化方案咨询

问题场景 #

现有潜在客户数据表my_leads结构及示例数据如下：

另有评分规则表grid_score_master（约300种组合），结构及示例数据如下：

当前通过PLPGSQL函数fn_get_lead_score逐行计算评分，查询语句如下：

原函数定义 #

CREATE fn_get_lead_score(
    in_city_rank          CHARACTER VARYING, 
    in_category_rank      CHARACTER VARYING, 
    in_order_value        CHARACTER VARYING
)
RETURNS NUMERIC
IMMUTABLE
LANGUAGE PLPGSQL
AS $function$
DECLARE
    my_score NUMERIC;
BEGIN
    SELECT grid_score_to_assign INTO my_score
    FROM grid_score_master 
    WHERE grid_city_rank = in_city_rank
      AND grid_category_rank = in_category_rank
      AND grid_lead_value = in_order_value;
  
    RETURN my_score;
END;
$function$;

原查询语句 #

SELECT * 
FROM (SELECT lead_id, 
             lead_city, 
             lead_city_rank, 
             lead_category, 
             lead_category_rank, 
             lead_value, 
             fn_get_lead_score(lead_city_rank, lead_category_rank, lead_value) AS lead_score 
      FROM my_leads 
      WHERE user_id = 10) my_leads 
ORDER BY lead_score ASC 
LIMIT 50

当my_leads返回2-3百万条数据时，函数会被重复执行数百万次，导致查询耗时极长。

优化方案 #

1. 用JOIN替代标量函数调用 #

直接将两张表关联，避免逐行函数调用。数据库会优化关联逻辑，一次性匹配所有组合，大幅减少查询次数：

SELECT 
    ml.lead_id,
    ml.lead_city,
    ml.lead_city_rank,
    ml.lead_category,
    ml.lead_category_rank,
    ml.lead_value,
    gsm.grid_score_to_assign AS lead_score
FROM my_leads ml
LEFT JOIN grid_score_master gsm 
    ON ml.lead_city_rank = gsm.grid_city_rank
    AND ml.lead_category_rank = gsm.grid_category_rank
    AND ml.lead_value = gsm.grid_lead_value
WHERE ml.user_id = 10
ORDER BY lead_score ASC
LIMIT 50;

若所有潜在客户的参数组合都能在grid_score_master中找到匹配，可替换为INNER JOIN进一步提升性能。

2. 创建复合索引加速关联 #

在grid_score_master上创建复合索引，让数据库快速定位对应评分：

CREATE INDEX idx_grid_score_master_composite 
ON grid_score_master (grid_city_rank, grid_category_rank, grid_lead_value);

同时确保my_leads的user_id字段有索引，加速初始数据筛选：

CREATE INDEX idx_my_leads_user_id 
ON my_leads (user_id);

3. 预计算评分并缓存（可选） #

如果评分规则不频繁变动，可在my_leads中新增字段存储预计算的评分，通过定时任务更新：

-- 新增评分字段
ALTER TABLE my_leads ADD COLUMN lead_score NUMERIC;

-- 初始化评分
UPDATE my_leads ml
SET lead_score = gsm.grid_score_to_assign
FROM grid_score_master gsm
WHERE ml.lead_city_rank = gsm.grid_city_rank
  AND ml.lead_category_rank = gsm.grid_category_rank
  AND ml.lead_value = gsm.grid_lead_value;

-- 后续直接查询预计算字段
SELECT *
FROM my_leads
WHERE user_id = 10
ORDER BY lead_score ASC
LIMIT 50;

注意：若潜在客户的评级/价值或评分规则发生变化，需同步更新lead_score字段。

4. 改用轻量SQL函数（可选） #

若必须使用函数，改用SQL函数替代PLPGSQL函数，减少函数调用开销：

CREATE OR REPLACE FUNCTION fn_get_lead_score(
    in_city_rank CHARACTER VARYING, 
    in_category_rank CHARACTER VARYING, 
    in_order_value CHARACTER VARYING
)
RETURNS NUMERIC
IMMUTABLE
LANGUAGE SQL
AS $$
    SELECT grid_score_to_assign
    FROM grid_score_master 
    WHERE grid_city_rank = in_city_rank
      AND grid_category_rank = in_category_rank
      AND grid_lead_value = in_order_value;
$$;

此优化效果远不如直接使用JOIN显著。

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